Cashbook Accounting System - Advanced Version - With Sample Data
The version of the browser you are using is no longer supported. Please upgrade to a supported browser.Dismiss

View only
About and Using The Advanced AHBC Cashbook Accounting System
Created ByAdelaide Hills Business Centre
This spreadsheet has been created for Adelaide Hills Business Centre (AHBC) members or for us by purchase. The content of the sheet is shared with the AHBC should you need assistance or business advice. You may share this sheet with others. See copyright notice below.

Should you identify an error in this product, please let us know so that we may correct.
OverviewYour Cashbook system has been design for businesses that require a basic accounting system that managers upward of 3 banks accounts and depreciation assets. The system is based on bank account transactions (In / Out). This is referred to as a "Cashbook System" rather than an accrual account system whereby transactions are considered at time of creation and not when received at your bank account. You may also use a bank account that includes your own living expenses. Your system allows you to establish 3 bank accounts within this system.
Entry AreasThis sheet is not protected, so we advise that you should only enter data in the areas that are white in colour. Shaded areas normally contain calulations and should not be removed or edited. Some sheets have hidden Rows and Columns. Please keep hidden and do not edit as they contain lookup cross referance data.
Getting StartedAfter reading this sheet, get started by:-
1. Use the "Cashbook Accounting System - Advanced Version" spreadsheet with out sample data.
2. Enter detail on the sheet called "Settings".
3. Edit the list of codes on the sheet called "LookupCodes" See notes below on using codes 9000 to 9999
4. Add income, cost of goods, expense and other codes to the Profit and Loss reports. Sheet PandL
5. Add asset, liability and equity codes to the Balance Sheet.
6. Create the Depreciating Asset Schedule
7. Enter transaction from your bank statement as required.
8. Review other sheets as required to identify performance, equity and cashflow
About The TabsThe Spreadsheet contains 8 sections (Tabs) which are outlined below.
Introduction TabThis Page, help on using the Cashbook System. Wthe AHBC also runs Table Talks on how to get started. They run for only 90 minutes and you can details at
Settings TabStart here and enter your business details, the "Financial Year End", "Start Month" that your Cashbook start reporting from, GST Rate and Depreciation. If you are not registered for GST, set this to 0% (Zero Percent).
The "Financial Year End" is the year in which the year ends after 12 months. Eg if you start the Cashbook System on the 1st of July 2016 than the "Financial Year End" is 2017.
Turn "Current Year Status" to TRUE if the account are active otherwise FALSE. This ensure values do not change in the "DepreciationAsset" sheet once the financial year has ended.
Enter the Opening Balances for upwards of 3 bank type accounts and any GST balance based at the Start Month and Report Year.

The Opening Bank Balances are that show on your last bank statement leading up to the Reporting Year and Start Month.

The GST is the amount you are due to pay or receive from the ATO at the start of the new Reporting Year and Start Month.
PandL TabThe “Profit and Loss” report provides a summary of your business performance over time (Profitable or Not) from the Cashbook transactions cross referencing by Month and Code. An area is provided to separate business and private transactions.

To setup up your PandL, simply enter the codes in column A for Income, Cost Of Goods (Direct Cost) and Expenses (Overheads).

The PandL reports form part of your business tax return along with the Balance Sheet.

The PandL does not consider GST from your Cashbook transactions.

The Expense Item for Depreciating Assets is entered manually. The depreciation value at the end of each month should be entered. The value for entry is shown on the top row of the "DepreciatingAsset" sheet.
DepreciatingAsset TabDepreciation is a value given to the wear and tear of Assets that can not be expensed for tax purposes in the one year. For example a motor vehicle purchased at $12000 has a potential life of 5 years (20%) so a business expense $2400 per year or $200 per month. Depreciation can be complex and the ATO laws change regularly so your accountant should be able to advise you of the annual depreciation rate or visit or ATO website for assistance.

The sheet enable you to enter each of your assets owned by the business. Each asset is to have a date of purchase, purchasce value, any previous depreciation claimed up to the current year (WDV), any private use as a percentage, effective life in years (Set down by the ATO but can be set by you with justification, method of depreciation. (Straight for event rate Eg. property or Declining for rapid rate at start Eg. Equipment)

Currently (2017), Commerical Vehicles Eg. carry capability >1 tonne or can carry 9 or more passengers, can be claimed at 100% for business use and normal life is 8 years. Non commerical vehicle such as the family car have a number of special tax rules to identify business use. Eg log book etc. Talk to your accountant for assistance if this applies to you.

Depreciation is calculated daily. The top row shows the current non expensed (See PandL sheet) depreciation. It is good practice to enter the end of month deprecitaion as show in the PandL sheet.

When starting a new years spreadsheet, copy the current Closing WDV to the Opening WDV for the new year. (WDV = Writen Down Value)
BalanceSheet TabThe "Balance Sheet" indicates your position of equity (Net worth) based on current Cashbook transaction and Bank balances.
To setup your Balance Sheet, simply enter the codes in Column A for Assets and Liabilities accounts Eg Stock, Loans etc. Under Equity, enter any retain earnings from previous years.

The Balance Sheet includes asset values and claimed depreciation valudes from the "DepreciationAssets" Sheet. The depreceiption for the current years is based on the amont claimed in the "PandL" sheet.

Private drawings are show under equity so do not double up as a business an "Expense" in the "PandL" sheet.

When creating a new year, sum the "Previous" and "Current" retained earnings and enter as the new "Previous Retained Earnings" in the new year spreadsheet.

The Balance Sheet forms part of your tax return along with the PandL report.
The Balance Sheet not consider GST from your Cashbook transactions.
Cashbook TabThe cashbook is where you enter each of your bank transactions (Business and Private). In effect, it should mirror your bank statement monies received in and out of your bank. To get started, simply enter the date of your transaction, a description and the account Code from your Lookup table. Income values (Money Received) are entered in the light Green column and expenses in the Pink column. The GST will be calculated based on the percentage entered on the “Settings” sheet.

Note that some codes will produce different results in your Cashbook. These include codes 9000 to 9998 do not include GST by default, Code 9998 is useed for the transfer of funds between Bank#1 and Bank#2.and Code 9999 for GST transactions with the ATO.

Transactions not associated with a bank account can be entered by using the "Adjust" bank code. This enable adjustment to your PandL and BalanceSheet. Transaction will show with a purple shading.
Lookup Codes TabWhen you create sales or incur expenses, they need to be assigned to a Code. The Codes assist in presenting your financial records on your “Profit and Loss” (P&L) report. Codes must be a four digit text value from 1000 to 9999.

For best results, please keep the list in Code order.
- GST Except CodesNO GST - Transaction that have no GST, such as Bank Fees should used a Code within the range of 9000 - 9998. No GST are displayed for these codes.
- Bank Transfer CodesCode 9998 should be used for transferring of funds between Bank#1 and Bank#2. When transferring funds between Bank#1 and Bank#2, enter the two transactions lines as show on each bank statement. No in or our GST. All 9998 Cashbook entries are shaded Yellow.
- GST Transactions CodesCode 9999 should be used for GST payment or credits with the ATO. Code 9999 will show GST payable to the ATO in the Total Expense, GST Out and Net Out columns while GST credited back by the ATO will show in the Total Income and Net In Columns while GST In will remain as 0 (zero). All 9999 Cashbook entries are shaded Blue.
- Changing The GST amount.GST - You can manually enter the GST amount if the calculated GST is not correct. Simply overwrite the GST value in the darker Green and darker Pink GST Columns.
Cash Flow TabThe Cash Flow Statement provides an outlook of your bank account with opening and closing balance each month.
CopyrightTemplate is copyright to Adelaide Hills Business Centre. Data is owned by the User and the User may share this speadsheet and its content with others.
Main menu