This template enables users to control the expenses that are 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:
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 that are entered on the Data sheet and the start date that is 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 a light blue column heading. 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 that is entered in cell B4 - the journal number that is entered in this cell should be the same as the journal number that is entered in column B when recording transactions on the Data sheet.

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 that are automatically copied for all new transactions that are 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 that are specified in the System Control Panel.
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 that is 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 that 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 that 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 that 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 Code - select a tax code of "A" if the transaction is subject to sales tax and a tax code of "E" if no sales tax is applicable to the transaction. Note that the appropriate sales tax percentage should be entered in cell I2.
Sales Tax Amount - the formula in this column calculates the sales tax amount based on the tax code that is selected in the previous column and the sales tax percentage that is specified in cell I2. If a sales tax code of "E" is selected in the previous column, the sales tax amount will be nil.
Exclusive Amount - the formula in this column deducts the sales tax amount that is calculated in the previous column from the inclusive amount that is 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 that are 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 that has been selected in column H is invalid. All the accounts that have been entered on 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.

Petty Cash Report

The petty cash expense report on the Report sheet is automatically compiled based on the transactions that are recorded on the Data sheet and the start date that is 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 that 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 that are entered on the Data sheet.

Note: The default "TAX" account refers to the appropriate sales tax account 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 that is included on the Report sheet is determined based on the start date that is 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 that is specified in cell B4. The journal number that is entered in this cell should be the same as the journal number that has been 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 that 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 and A8. This input is only required if you need to record petty cash expenses in a primary accounting system and the account numbers that 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.