This template enables users to control the expenses paid through any petty cash or cash float system. All petty cash expenses and reimbursements can be recorded by entering the appropriate transactions details and a monthly petty cash report is automatically produced. The monthly report includes a 12 month summary of expenses by account and also calculates the petty cash or cash float balance at the end of each monthly period. You can add as many accounts as required and the report can be rolled forward for subsequent periods by simply amending the start date of the report.
The following sheets are included in this template:
Setup - this sheet enables users to enter their business name and set up sales tax percentages. The business name is used as a heading on the other sheets and the sales tax percentages are used in all sales tax calculations.
Data - all petty cash expenses and reimbursements should be recorded on this sheet.
Report - the 12 month petty cash report on this sheet is automatically compiled based on the transactions entered on the Data sheet and the start date specified in cell D2. The report includes 9 default accounts but you can add as many accounts as required by simply inserting the appropriate number of new rows, entering the account number & description and copying the existing formulas from all the columns with light blue column headings. No other user input is required on this sheet.
Journal - this sheet includes a general ledger journal summary which enables users to easily record petty cash or cash float expenses in any accounting system. The report is automatically calculated based on the journal number entered in cell B4 - the journal number entered in this cell should be the same as the journal number which was entered in column B when recording transactions on the Data sheet.
Setup
The business name and sales tax percentages can be entered on the Setup sheet. The business name is used as a heading on all the other sheets and the sales tax percentages are used to calculate the inclusive, exclusive and sales tax amounts.
The sales tax codes in the sales tax section on the Setup sheet are automatically included in the tax code list boxes on the Data sheet. There are two sales tax code types - one for national or federal sales tax and one for state sales tax. The codes created on the Setup sheet are included in both sales tax code list boxes and the sales tax percentages are automatically applied based on the sales tax codes selected for each petty cash transaction.
Note: You can add an unlimited number of additional sales tax codes by inserting a new row above the end of list entry, entering the new sales tax code and specifying the appropriate sales tax percentage. If sales tax percentages change, you can therefore simply create a new sales tax code with the new sales tax percentage and use the new code for all transactions after the effective date of the rate change.
Record Petty Cash Transactions
All petty cash or cash float transactions should be entered on the Data sheet. The columns with a yellow column heading require user input while the columns with a light blue column heading contain formulas which are automatically copied for all new transactions entered. The Data sheet includes the following columns:
Transaction Date - enter the petty cash transaction date. Note that all dates should be entered in accordance with the regional date settings.
Journal Number - enter a general ledger journal number. This journal number is used to group transactions together when calculating expense account totals for the purpose of recording petty cash expenses in your primary accounting system. The account totals on the Journal sheet are automatically calculated based on the journal number specified in this column.
Voucher Number - if you are using a petty cash or cash float voucher system, the voucher number should be entered in this column. If you don’t use a voucher system, we recommend that you enter a transaction number in an ascending numerical sequence in this column.
Supplier - enter the name of the appropriate supplier in this column.
Reference - enter a transaction reference in this column which will enable you to trace the petty cash transaction to its supporting documentation. For example an invoice number or a cash receipt number.
Description - enter a description of the transaction which will enable you to easily determine the nature of the transaction.
Inclusive Amount - all petty cash expenses should be recorded as negative values and all petty cash reimbursements should be recorded as positive values. All the amounts in this column should include sales tax.
Account - 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 which have been added to the Report sheet. New accounts will therefore have to be added to the Report sheet before being available for selection.
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.
Sales Tax 1 Amount - the sales tax amounts in this column are calculated based on the tax 1 code selected in column I 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 I.
Sales Tax 2 Amount - the sales tax amounts in this column are calculated based on the tax 2 code selected in column J 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 formula in this column deducts the sales tax amounts calculated in the previous two columns from the inclusive amount entered in column G.
Error Code - the formula in this column displays an error code if invalid transaction data is entered. Refer to the Error Codes section of these instructions for more information about the error codes that may be encountered.
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.
Note: The petty cash reimbursement transactions recorded on the Data sheet should all be allocated to the default "BANK" account by selecting this default account from the list box in column H. The journal number and voucher number for reimbursement transactions should always be nil and the supplier, transaction reference and description should all refer to a reimbursement type transaction.
Note: All the column headings on the Data sheet contain a filter selection arrow. This useful feature enables you to filter the transactions that are included on this sheet according to requirements. For example, the date filter options can be used to display a complete list of transactions that are dated between the first and last day of any particular month. The totals above the column heading row will also only include the filtered transactions that are included on the sheet.
Error Codes
The following error codes may result from inaccurate input on the Data sheet and will be displayed in the Error Code column and highlighted in orange. The heading of the affected input column will also be highlighted in orange:
- E1 - this error code means that a positive value has been entered in column G for a petty cash transaction or that a negative value has been entered for a reimbursement transaction. All petty cash expenses must be entered as negative values and all reimbursement transactions must be entered as positive values and allocated to the default "BANK" account.
- E2 - this error code means that the account number selected in column H is invalid. All the accounts added to the Report sheet will be included in the list boxes in column H 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 Report sheet before being available for selection.
Note: Input errors may result in inaccurate petty cash calculations and it is therefore imperative that all errors are resolved before reviewing the petty cash report on the Report sheet.
Recording the Opening Balance
The initial petty cash or cash float opening balance needs to be recorded by entering an opening balance transaction on the Data sheet. The transaction date should be the month end date that precedes the initial template start date that is specified in cell D2 on the Report sheet. The voucher and journal number for this transaction should be nil; the supplier, reference and description should refer to the opening balance; the amount should be the opening balance amount; the "BANK" account should be selected as the account number and a tax code of "E" should be selected from the list box in the tax code column.
Example: If the first monthly period that you want to include in the petty cash report is March, the opening balance as at 28 February should be entered as an opening balance transaction on the Data sheet. The transaction date should be 28 February and the opening balance amount should be entered in the Inclusive Amount column. All the opening balances for subsequent periods will be calculated automatically after you amend the start date in cell D2 on the Report 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 J) and the Sales Tax 2 Amount (column L) on the Data sheet. You can then also delete the TAX2 account row on the Report sheet and the BS-610 row on the Journal sheet. Completing these three 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.
Petty Cash Report
The petty cash expense report on the Report sheet is automatically compiled based on the transactions recorded on the Data sheet and the start date specified in cell D2 on the Report sheet. The report includes 9 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 accounts) and a 3 digit number for the account number. You can specify any account number format by simply editing the account numbers in column A on the Report sheet and entering the appropriate account descriptions in column B.
Additional accounts can be added by simply inserting a new row anywhere above the "BANK" account, entering the new account number in column A and the account description in column B and copying all the formulas in the columns with a light blue column heading. 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 an inaccurate petty cash expense report and balance calculation. If an account is deleted and entries have been allocated to the account, an error code of E2 will be displayed in the Error code column and the codes will only be removed once the appropriate transactions have been allocated to a valid account.
Note: The default "BANK" account refers to the appropriate bank account which is used to reimburse the petty cash or cash float. This default account has been included in the template in order to record the petty cash reimbursements and to calculate an accurate petty cash balance. The default account should not be amended or deleted otherwise it may result in inaccurate petty cash balance calculations. The "BANK" account should therefore be selected from the list box in column H on the Data sheet for all the petty cash or cash float reimbursement transactions which are entered on the Data sheet.
Note: The default "TAX1" and "TAX2" account refer to the appropriate sales tax accounts to which the sales tax on all petty cash transactions should be allocated. This account should not be selected when allocating transactions because sales tax liabilities are typically not settled through petty cash or cash floats.
The 12 month reporting period which is included on the Report sheet is determined based on the start date entered in cell D2. After specifying the initial opening balance, the petty cash expense report can be rolled forward for an unlimited number of future periods by simply entering a new start date in cell D2. All the calculations on the Report sheet are automatically adjusted based on the date that is specified.
General Ledger Journal
This template enables users to control any petty cash or cash float system independently from the primary accounting system but it will also be necessary to record petty cash expense totals in the primary accounting system. A general ledger journal is commonly used to record petty cash or cash float expenses and we have therefore included the Journal sheet in order to facilitate an automated calculation of the appropriate journal entries.
The calculations on the journal sheet are automated and based on the journal number which is specified in cell B4. The journal number entered in this cell should be the same as the journal number entered in column B on the Data sheet. The calculation of the journal entries effectively groups all the entries on the Data sheet that have been assigned the same journal number together in order to reflect one total per expense account for all of these entries.
This effectively means that the journal number which is entered in column B on the Data sheet determines which entries are grouped together and for which totals per expense account are calculated on the Journal sheet. After recording the appropriate general ledger journal in the primary accounting system, you can refer back to the journal calculation by simply entering the appropriate journal number in cell B4. The individual transactions that make up the journal entries on the Journal sheet can be listed on the Data sheet by using the filter feature and simply selecting the appropriate journal number.
Note: Only the first 20 accounts that are included on the Report sheet will be listed on the Journal sheet. If you require more than 20 expense accounts to be included in your journal entries, simply copy the formulas in one of the existing rows to the appropriate number of new rows. There is no limit on the number of additional accounts that you can include in the journal calculations on the Journal sheet.
Aside from entering a journal number in cell B4, the only other user input that is required on the Journal sheet is entering the appropriate petty cash control and sales tax control accounts in cells A7 to A9. This input is only required if you need to record petty cash expenses in a primary accounting system and the account numbers which are used for the petty cash control and sales tax control accounts in the primary accounting system should therefore be entered in these input cells.
