This template enables users to record deposit and withdrawal transactions for multiple bank accounts and automatically produces a monthly cashbook report, bank reconciliation and a unique dashboard. The cashbook report can be compiled for any 12 month reporting period by simply entering the appropriate start date in a single input cell and the report can be viewed on an individual account or consolidated basis. The template also accommodates petty cash transactions and sales tax calculations.
Note: Refer to our unique accounting templates if you require a more comprehensive monthly accounting solution which includes an income statement, cash flow statement and balance sheet. Our accounting templates also automatically calculate debtors, creditors, cash and sales tax balances.
The cashbook template also facilitates performing a bank reconciliation for any bank account by simply entering or copying all cashbook transactions onto the Data sheet and entering the appropriate bank statement date for each cashbook transaction. The bank reconciliation on the Recon sheet is calculated automatically from the cashbook entries and can easily be rolled forward for the next monthly period or rolled back to display the bank reconciliation for any previous period.
The following sheets are included in this template:
Setup - this sheet enables users to enter the business name, add sales tax codes, edit sales tax percentages and to add additional bank account codes to the template. A list of the input error codes which can be encountered when entering data on the Data sheet is also included on this sheet.
Data - all cashbook transactions should be entered on this sheet or copied from an accounting program or bank statement export file. You can add additional columns in order to match the columns on this sheet to the format of any accounting or bank statement export file but note that all the existing columns should be retained. After recording all the appropriate cashbook entries on this sheet, the bank statement date of each transaction should be entered in column J.
Cashbook - the cashbook report on this sheet is automatically compiled based on the transactions entered on the Data sheet and the start date specified in cell D1. The report can be compiled for an individual bank account by selecting the appropriate bank code from the list box in cell D2 or a consolidated report for all bank accounts can be compiled by simply clearing the contents of the list box. You can also compile a report for only deposits or withdrawals by selecting the appropriate item from the list box in cell D3. The default accounts on this sheet can be amended and you can add as many accounts as required.
Recon - this sheet includes a bank reconciliation report which is automatically populated based on the cashbook transactions recorded on the Data sheet. All the calculations on this sheet are automated and the only user input required is entering the appropriate bank code in cell B4, the reconciliation date in cell B5 and the actual bank statement balance in cell B25. The sheet also includes daily balance and movement calculations which make finding reconciliation differences easier.
Dashboard - the unique cashbook dashboard on this sheet is automatically updated based on the bank code and dates selected in the filter section of the dashboard. You can select a single bank code or clear the contents of the list box to view calculations for all bank accounts. The dashboard also includes a unique interactive report where you can select the basis of balances or movements and view all transaction types or only cash inflows or outflows. The last section of the dashboard enables users to view monthly account movements for any account. Aside from the filter and report selections, no user input is required on this sheet and all workings are included below the dashboard.
Record Cashbook Transactions
All cashbook transactions should be entered or copied onto the Data sheet. All the columns with yellow column headings require user input while the columns with light blue column headings contain formulas which are automatically copied for each new transaction added to the table. The sheet includes the following columns:
Transaction Date - enter the cashbook transaction date in this column. This is the date a deposit is received or a payment is made. Note that all dates should be entered in accordance with your regional date settings.
Customer / Supplier - enter the name of the appropriate customer or supplier.
Reference - enter a transaction reference which will enable you to trace the transaction to its supporting documentation. For example: an invoice number, bank statement reference, receipt number or a reference to a debit order can be used.
Description - enter a brief description of the transaction which will enable you to easily determine the nature of the transaction.
Inclusive Amount - all deposits (cash inflows) should be entered as positive values and all withdrawals (cash outflows) should be entered as negative values. All transaction amounts should be entered inclusive of sales tax.
Tax 1 Code - select a tax code from the list box. All the sales tax codes which have been created on the Setup sheet will be available for selection and sales tax percentages are calculated based on the selected tax code. If your business is not registered for sales tax purposes, all transactions should be recorded by using the E tax code. The first sales tax code is for national or federal sales tax and should therefore be applicable in most countries.
Tax 2 Code - select a tax code from the list box. All the sales tax codes which have been created on the Setup sheet will be available for selection and sales tax percentages are calculated based on the selected tax code. If your business is not registered for sales tax purposes, all transactions should be recorded by using the E tax code. The second sales tax code is for state sales tax - if you do not need two sales tax codes, this column can be deleted.
Bank Code - select the appropriate bank account code from the list box in this column. The template includes 3 default bank account codes and a petty cash code but you can add additional bank accounts on the Setup sheet by inserting a new row anywhere between the first bank code and the end of list row, entering a new bank code in column A and entering a description of the new bank account in column B.
Account Number - select the appropriate account number from the list box. The list box in this column will include the account numbers and descriptions of all the accounts added to the Cashbook sheet. New accounts therefore need to be added to the Cashbook sheet before being available for selection.
Statement Date - review the actual bank account statement and enter the appropriate bank statement date for each of the cashbook transactions recorded on this sheet. Note that you should leave the statement date blank if a cashbook entry cannot be traced to the actual bank statement.
Trn Type - the transaction type is automatically assigned based on whether a positive or negative amount has been entered in column E. If a positive amount has been entered, the transaction type is "D" for deposits and if the amount is negative, the transaction type is "W" for withdrawal.
Cashbook Balance - this column includes a calculation of the cashbook balance for the appropriate bank account which is based on the transaction date entered in column A. For transactions on the same date, the sequence in which the transactions are included on the Data sheet determines which transactions are included first in the calculation.
Sales Tax 1 Amount - the sales tax amounts in this column are calculated based on the tax 1 code selected in column F and the sales tax percentages specified on the Setup sheet. The tax 1 calculations are applicable for national or federal sales tax and should therefore apply to most countries. If no sales tax should be calculated, the E tax code can be selected in column F.
Sales Tax 2 Amount - the sales tax amounts in this column are calculated based on the tax 2 code selected in column G and the sales tax percentages specified on the Setup sheet. The tax 2 calculations are applicable for state sales tax and may therefore not apply in all countries. If you do not need state sales tax calculations, you can delete this column.
Exclusive Amount - the amounts in this column deduct the sales tax amounts calculated in the previous two columns from the inclusive amounts in column F.
Cashbook Balance Date - the dates in this column are used to determine the cashbook balances in column L.
Error Code - the formula in this column displays an error code if an input error has occurred. The affected column heading will be highlighted in orange until the error is rectified. Refer to the error codes section of the instructions for guidance on how to resolve input errors.
Note: All the data on the Data sheet have been included in an Excel table. This feature is extremely useful when entering data in a table format because the formulas which are included in calculated columns (the columns with light blue column headings) are automatically copied when new rows are inserted into the table or when data is entered into the first blank row below the table. You can therefore enter a new transaction by simply entering a transaction date in the first blank cell in column A - the table will then automatically extend to include the new transaction.
Note: The list box in the Account Number column contains both the account number and the account description but only the appropriate account number should be selected when allocating transactions. Excel actually does not allow the inclusion of two columns in a list box, but we have implemented a work around in order to provide the account description together with the account number. However, because of the implementation of this feature, you will not be able to enter the account number into this column and all account numbers should therefore be selected from the list box or copied from an existing, similar transaction. You may also notice that all the cells in the Account Number column contain an error message that refers to a data validation error - this is to be expected because of the work around that we implemented and can safely be ignored.
Error Codes
The following error codes may result from inaccurate input on the Data sheet and will be displayed in the Error Code column. The heading of the affected input column will also be highlighted in orange:
- E1 - this error code means that the statement date entered in column J is before the transaction date in column A. Statement dates can never be before the transaction dates and the error can therefore be rectified by either amending the appropriate transaction date or the statement date.
- E2 - this error code means that the account number selected in column I is invalid. All the accounts added to the Cashbook sheet will be included in the list boxes in column I and the error can therefore be rectified by simply selecting a valid account number from the list box. New accounts must be created on the Cashbook sheet before being available for selection.
- E3 - this error code means that the bank account code selected in column H is invalid. All the bank account codes created on the Setup sheet will be included in the list boxes in column H and the error can therefore be rectified by simply selecting a valid bank code. New bank account codes must be created on the Setup sheet before being available for selection.
Note: Input errors may result in inaccurate cashbook and bank reconciliation calculations and it is therefore imperative that all errors are resolved before reviewing the cashbook report on the Cashbook sheet or the bank reconciliation on the Recon sheet.
Recording the Opening Cashbook Balances
The following procedure needs to be completed in order to record the opening cashbook balances of existing bank accounts:
- Enter a transaction on the Data sheet for the opening bank statement balance. The transaction date and the statement date of the opening balance should be the same. The transaction reference and description should indicate that the transaction relates to the opening bank statement balance and the transaction should be dated on the last day of the month before the first monthly period which needs to be included in the cashbook.
- Enter or copy all the outstanding deposits and withdrawals as at the last day of the month before the first month which needs to be included in the cashbook onto the Data sheet. These outstanding entries should include all transactions that need to be included in the opening cashbook balance but have not yet been included on the actual bank statement. The cashbook transaction dates should be entered in column A and the bank statement dates should be left blank until the transactions are included on the actual bank statement.
Example: If the first monthly period that you want to include in the cashbook is March, the actual bank statement balance as at 28 February should be entered as a transaction on the Data sheet. The transaction date and statement date should be 28 February. All outstanding deposits as at 28 February should be recorded by entering positive amounts in column F and the actual cashbook transaction dates in column A. All outstanding withdrawals as at 28 February should be entered by entering negative amounts in column F and entering the actual cashbook transaction dates in column A.
Example: If an outstanding cheque was issued on the 15th of February and the cheque has not been processed through the actual bank statement by the 28th of February, the cheque should be recorded by entering a negative amount in column F and entering the date of the cheque in column A (15th of February). The statement date should be left blank until the cheque number is included on the actual bank statement and only then will this date be entered in the Statement Date column (the statement date will therefore be a date after 28 February).
In order to check if the opening cashbook balance has been entered accurately, enter any subsequent date in cell B5 on the Recon sheet. The calculated bank statement balance should agree to the opening bank statement balance specified on the Data sheet, the opening cashbook balance should agree to the balance according to any previously used Cashbook (if applicable) and the outstanding deposit and withdrawal totals should agree to the sum of the transactions entered on the Data sheet (entries dated on or before the opening balance date which have not yet been included on the bank statement).
Note: This procedure should be repeated for all the bank accounts included in the template. Petty cash accounts typically have no outstanding items and only the balance of the cash on hand at the end of the month prior to the first month which is included in the template therefore needs to be specified.
Transfers between Bank Accounts
When funds are transferred between two bank accounts, the deposit and withdrawal entries both need to be recorded. The deposit entry should be recorded by selecting the bank account into which the funds are transferred and the withdrawal entry should be recorded by selecting the bank account from which the funds are transferred.
We recommend creating a cash transfer control account on the Cashbook sheet and selecting this account in column I on the Data sheet when recording transfers between bank accounts. By using a single account for bank transfers, you can therefore easily determine when only one of the entries has been recorded. We've created a default cash transfer control account (BS-TRF) on the Cashbook sheet but you can use another account if you are using an existing account structure.
Note: You can check whether the balance of the cash transfer control account is nil by clearing the contents of the bank account code list box on the Cashbook sheet in cell D2 in order to display the cashbook totals for all bank accounts. If a total is not nil, the transactions for the appropriate period should be investigated in order to determine why the allocations to the cash transfer control account do not result in a nil balance.
Note: If a bank account code is selected in cell D2, the total for the cash transfer control account will probably not be nil because only one side of the transfer would be taken into account. This total represents the total funds transferred from the selected bank account to any other bank account or the reimbursements of petty cash.
Petty Cash Transactions
Although petty cash does not actually represent an actual cashbook which has a bank statement, we have included a default bank code (PC) for petty cash in the template in order to facilitate also recording petty cash transactions. The statement dates entered in column J on the Data sheet should therefore be the same as the transaction dates entered in column A.
Creating Additional Bank Accounts
The template includes three default bank accounts and a petty cash account. The default bank codes and descriptions of these accounts are included on the Setup sheet. Additional bank accounts can be added to the template by simply inserting the required number of new rows anywhere between the first bank code and the end of list row, entering a new bank code in column A and entering a description of the bank account in column B.
As soon as the new bank account codes have been created, the codes will be available for selection from the bank code list boxes on the other sheets. Note that the default bank codes and descriptions of the bank accounts on the Setup sheet can also be amended if required.
Note: After adding additional bank accounts, you also need to copy the formulas at the bottom of the Cashbook sheet in order to include the closing balance calculations of all the bank accounts on the Cashbook sheet.
Removing Second Sales Tax Calculations
We have included two sales tax types in the template to accommodate calculating sales tax on a national / federal basis and a state basis. If you do not need both sales tax types, you can delete the second sales tax type. You should not however delete both as this may result in template calculation errors.
Note: If sales tax is not applicable to your business, we recommend using the "E" sales tax code when recording all entries. No sales tax will then be calculated as the sales tax percentage for this code is zero.
The second sales tax type can be removed by deleting the Tax 2 Code column (column G) and the Sales Tax 2 Amount (column N) on the Data sheet. You can then also delete the BS-TAX2 account row on the Cashbook sheet. Completing these two steps will remove the second sales tax type from the template.
Note: No new columns should be added between the exclusive amount and sales tax amount columns on the Data sheet otherwise it may affect some of the sales tax calculations in this template. Also, if you delete the columns for the second sales tax type, the Sales Tax 1 Amount column may display validation warnings but these can safely be ignored as it has no effect on the template calculations. If you do not want to see these validation warnings, just keep both sales tax types and just don't select any tax codes in the second sales tax code column.
Cashbook Report
The cashbook report on the Cashbook sheet is automatically compiled based on the transactions recorded on the Data sheet, the start date specified in cell D1 and the bank account code selected in cell D2. The report includes 31 default accounts but you can add additional accounts and customize the default account numbers and descriptions as required.
It is also not necessary to use the default account format which consists of a two letter code indicating income statement and balance sheet accounts and a 3 digit number indicating the account group and account number. You can specify any account number format by simply editing the account numbers in column A on the Cashbook sheet and entering the appropriate account descriptions in column B.
Additional accounts can be added by simply inserting a new row at the appropriate row location, entering the new account number and description and copying all the formulas in the columns with a light blue column heading from one of the existing rows.
You can also delete accounts if you wish to do so but care should be taken not to delete accounts to which entries have been allocated on the Data sheet because this may result in inaccurate cashbook calculations. If an account is deleted and entries have been allocated to the account, the appropriate rows on the Data sheet will contain an E2 error code in the Error Code column on the Data sheet. A new account allocation will then have to be selected in order to rectify the errors.
The 12-month reporting period included in the cashbook report is determined based on the start date entered in cell D1. After specifying the initial opening balances, the cashbook report can be rolled forward for an unlimited number of future periods by entering a new start date in cell D1. All the cashbook calculations are automatically adjusted based on the date specified.
The cashbook report can be compiled for a single bank account by simply selecting the appropriate bank account code from the list box in cell D2 or a consolidated cashbook report which includes all the bank accounts and the petty cash account can be compiled by simply clearing the contents of the list box in cell D2.
You also have the option of including all cashbook transactions (the default selection) or only including deposit or withdrawal type transactions by simply selecting the appropriate transaction type from the list box in cell D3 on the Cashbook sheet.
Note: The closing balances of all the default bank accounts at the end of each monthly period are included below the cashbook report. If you create additional bank accounts on the Setup sheet, the formulas which are used to calculate the closing balance will need to be copied to the appropriate number of additional rows in order to also include the closing balances of the additional bank accounts on the Cashbook sheet.
Performing a Bank Reconciliation
Bank Statement Dates
The automated bank reconciliation on the Recon sheet is compiled based on the bank statement dates entered in column J on the Data sheet. Users therefore need to obtain a copy or extract of the actual bank statement and enter the statement dates of all the cashbook entries included on the Data sheet. If a transaction does not appear on the actual bank statement, the statement date should be left blank.
We recommend that you sort all the entries for the appropriate bank account on the Data sheet in ascending order based on the transaction dates in column A in order to simplify the recording of the appropriate statement dates. Note that the dates can be entered or copied from the Transaction Date column (if the same dates apply) and should be recorded in accordance with your regional date settings.
We also recommend that you make a tick mark next to the entries on the actual bank statement to ensure that the statement dates of all entries have been recorded on the Data sheet. After entering the statement dates of all the entries that appear on the bank statement, open the Recon sheet in order to review the bank reconciliation.
Bank Reconciliation
Select the appropriate bank account code from the list box in cell B4 and enter the appropriate date on which the reconciliation needs to be performed in cell B5 - all the calculations on the Recon sheet will be updated automatically based on the reconciliation date which is specified.
Note: If you clear the contents of the bank account code list box in cell B4, all the calculations on the Recon sheet will be based on all the transactions included on the Data sheet and it may be difficult to reconcile any individual bank account to an actual bank statement. We therefore recommend performing bank account reconciliations on an individual account basis and only clearing the list box if you want to view a consolidated reconciliation for all the bank accounts and the petty cash account.
The total of all withdrawal and deposit entries for the selected bank account will be included under the Current Month Transactions section of the report. These totals include all the entries between the 1st day of the month and the date specified as the reconciliation date in cell B5. All withdrawals have a transaction type of "W" in column K on the Data sheet and all deposits have a transaction type of "D" in column K on the Data sheet.
Note: All the current months' entries are included in these totals regardless of whether the transactions appear on the bank statement (and therefore contain a statement date in column J on the Data sheet).
Note: Transactions dated (in the Transaction Date column on the Data sheet) after the reconciliation date are not included in the calculations on the Recon sheet. The Cashbook Closing Balance will be the net total of all deposits and withdrawals recorded on the Data sheet and dated on or before the reconciliation date specified in cell B5.
Similarly, the outstanding withdrawal and deposit totals include all the entries dated before the reconciliation date (in the Transaction Date column on the Data sheet) and with statement dates (in column J of the Data sheet) dated after the reconciliation date or blank (entries which are therefore not yet reflected on the actual bank statement).
The Calculated Bank Statement Balance is arrived at by deducting the outstanding deposits total from the Cashbook Closing Balance and adding the outstanding withdrawals to this balance. The calculation therefore reflects the cashbook balance before taking the entries that do not appear on the bank statement into account. This amount should therefore agree to the actual bank statement balance as at the reconciliation date.
The Actual Bank Statement Balance on the reconciliation date should be entered in cell B25 and cell B26 reflects the difference between the calculated and actual bank statement balances. Cell B27 will contain a green "ok" message if the two balances are equal and a red "error" message if the balances are not equal.
If an error is displayed, it means that the entries on the actual bank statement and the cashbook entries on the Data sheet do not reconcile. There could be a number of explanations for an error being encountered:
- An entry which is included on the Data sheet does not appear on the bank statement but a statement date has been entered in column J for the particular entry.
- An entry which is included on the Data sheet appears on the bank statement but a statement date has not been entered in column J for the particular entry.
- The statement date in column J has been entered incorrectly.
- An entry which appears on the bank statement has not been recorded on the Data sheet.
- The transaction amount differs between the Data sheet and the actual bank statement.
As you can see, there are a number of reasons why an unexplained difference can be encountered after performing a bank reconciliation. It is therefore absolutely essential that you take care while performing a bank reconciliation to ensure that all cashbook entries are reconciled accurately - it is a lot harder to find errors after performing a bank reconciliation and the exercise of finding errors can be quite time consuming.
Fortunately, we have designed this template in such a way that it should be reasonably easy to find an explanation for any unexplained differences that may occur! When you encounter an unexplained difference between the calculated and actual bank statement balances, we recommend that you complete the following steps in order to find an explanation for the error:
- Review the Error Code column on the Data sheet and ensure that there are no error codes reflected in this column. If any error codes are reflected in this column, the errors should be rectified before continuing with the bank reconciliation.
- Identify the first date on which there is a difference between the calculated and actual bank statement balances - refer to column L on the Recon sheet where we display the daily calculated bank statement balances and compare these amounts to the actual bank statement balances to find the first difference. If there are no differences, you may need to roll your recon back by entering the previous month end date in cell B5.
- Sort the entries on the Data sheet in ascending order by the statement date in column J. Review the entries on the Data sheet for all days where an unexplained difference exists and ensure that the amounts on the Data sheet and the actual bank statement agree, that the dates have been entered correctly in the Statement Date column, that all entries on the actual bank statement have been included on the Data sheet and that a statement date has been entered on the Data sheet for all the entries on the actual bank statement.
This procedure should enable you to correct all the errors that have been made while performing the bank reconciliation.
Note: The Recon sheet also includes daily account balance and movement calculations as well as daily outstanding deposit and withdrawal calculations and calculated bank statement balances. All of these calculations are automated and based on the recon date entered in cell B5. These calculations, especially the outstanding item calculations, make it easier to find items which affect the bank reconciliation calculations.
Dashboard
The cashbook dashboard on the Dashboard sheet is calculated automatically from the transactions entered on the Data sheet. All you need to do is select the appropriate bank account code and from and to dates in the filter section of the dashboard. All the calculations are then automatically updated.
The dashboard calculations and charts can be compiled for a single bank account code by simply selecting the appropriate bank code in the dashboard filter section or you can show the data for all bank codes by simply clearing the bank code selection (pressing delete on the keyboard).
If you do not specify a From date in the filter section, all cashbook transactions from the first date captured on the Data sheet will be included in the dashboard calculations. If you do not specify a To date in the dashboard filter section, all cashbook movements on or before the current system date will be included in the dashboard calculations.
Our unique cashbook dashboard also includes a comprehensive interactive cashbook movement & balance report section which consists of three charts - a 90-day daily chart, a 13-week weekly chart and a 12-month monthly chart displaying cashbook movements for the selected bank account code and selected date range.
The interactive cashbook report contains selections for the basis and type. The basis selection contains options for displaying balances or movements. The type selection contains options for all, inflows (deposits) or outflows (withdrawals) which refers to the transaction types to display.
Note: The default options for the interactive report selections is to display movement amounts for all transactions. This is therefore the settings which are applied when no selections are active.
Note: The daily report can be amended to include less than 90 days by simply specifying a new value in cell C71 on the Dashboard sheet. If the chart does not update automatically, just change one of the selections and it should update the new days setting. We do not recommend making changes to the weekly or monthly charts.
The last section of the dashboard contains an interactive account analysis chart. This monthly chart can be used to display the monthly movements for any account by simply selecting the appropriate account number. All the accounts which are included on the Cashbook sheet are available for selection and if you clear the selected account, the total monthly movement for all accounts is displayed (exclusive of sales tax).
Only the dashboard itself is included in the print range on the Dashboard sheet. The calculations below the dashboard which are used to compile the automated calculations will therefore not be printed.
