This template enables users to compile a cumulative trial balance based on monthly account movements or to compile a monthly trial balance based on cumulative account movements. The template is especially useful when a user is required to consolidate cash flow forecast or budget data which usually consists of monthly balances for income statement accounts and cumulative balances for balance sheet accounts into a format which is consistent with the cumulative basis of trial balances as used in most accounting systems. In addition, the template also makes it easy to convert a cumulative trial balance into a trial balance which reflects monthly movements and to switch between cumulative and monthly trial balance calculations.
The following sheets are included in the template:
TBInput - enter or copy all the accounts that need to be included in the trial balance onto this sheet. A monthly or cumulative basis for the appropriate account balances can be specified and the sheet makes provision for opening balances and 12 monthly periods.
TB - the calculations on this sheet are automated and based on the contents of the TBInput sheet. Simply select the appropriate monthly or cumulative basis at the top of the sheet and all the account balances are calculated automatically.
Trial Balance Data
All the accounts and balances that need to be incorporated into the trial balance need to be entered or copied onto the TBInput sheet. The sheet contains the following user input columns:
Acc No - enter or copy all the account numbers that need to form part of the trial balance into this column. Account numbers can be in any format as long as each account number is unique.
Account Description - enter or copy the description of the accounts into this column.
Basis - select either the monthly or cumulative basis option from the list box in this column. If the balances that are specified for the account are monthly balances (monthly account movements), the monthly option should be selected. If the balances that are specified for the account are cumulative balances, select the cumulative option.
Opening - enter or copy the appropriate opening balance of the account into this column. Opening balances for trial balance purposes are usually only applicable to balance sheet accounts because the template covers a 12 month financial period and income statement balances are not carried over to subsequent financial years. The opening balances in this column represent the cumulative balance as at the end of the period which precedes the month 1 period.
Month 1 to 12 - enter or copy the monthly or cumulative balances at the end of each month during a 12 month period into these columns.
Note: Although the basis for income statement accounts that originate from a cash flow forecast or budget is usually monthly and the basis for balance sheet accounts is usually cumulative, you can specify any basis for any account in column C on the TBInput sheet.
Example: If your trial balance data consist of only monthly account balances (or movements), you can specify a monthly basis for all the accounts that are included on the TBInput sheet. Opening balances should however be included on a cumulative basis for all balance sheet accounts.
Example: If your trial balance data consist of only cumulative balances and you intend to use the template to compile a monthly trial balance, you can simply specify a cumulative basis for all accounts in column C of the TBInput sheet. Opening balance sheet balances should again be included on a cumulative basis.
Note: We do not recommend including any empty rows between rows that contain data on the TBInput sheet otherwise the trial balance on the TB sheet will also include the empty rows.
If you do not use the template for a full 12 month period, the opening balances at the end of the period before the first period for which a trial balance needs to be compiled should still be included in column D on the TBInput sheet. This principle will apply to both income statement & balance sheet accounts if the trial balance is compiled for a period consisting of less than 12 months. The columns relating to unused months on the TB sheet can then simply be hidden.
Note: The data validation feature which is used to create the list boxes in column C on the TBInput sheet has only been added to the first 1,000 rows on the sheet. If you include more than 1,000 accounts on this sheet, you can simply copy the list box from the first cell in column C which contains a list box and paste the list box into the required number of additional rows.
Trial Balance Report
The trial balance on the TB sheet is compiled automatically based on the data that is included on the TBInput sheet. The only user input that is required on this sheet is selecting the basis of the trial balance calculations in cell D2 (monthly or cumulative options are available from the list box in this cell).
If the monthly basis is selected, all the trial balance calculations will be based on monthly account movements and the opening balance column will contain nil values. If the cumulative basis is selected, the trial balance calculations will be based on cumulative balances regardless of the basis which is specified for individual accounts on the TBInput sheet. The opening balance column will also contain the opening balances that have been specified in column D on the TBInput sheet.
Note: The formulas on the TB sheet have only been included for the first 200 accounts. If you include more than 200 accounts on the TBInput sheet, simply copy the formulas in the last row that is displayed on the TB sheet into the required number of additional rows. There is no limit on the number of accounts that you can add to your trial balance - the sheet has only been limited to 200 rows in order to not slow down the template calculations unnecessarily for trial balances with fewer accounts!
The calculations on the TB sheet can be switched from a cumulative to a monthly basis by simply selecting the monthly option from the list box in cell D2. If the contents of cell D2 has been cleared, the calculations on the sheet defaults to cumulative calculations.
The TB sheet also contains subtotals in the row above the column headings. If all of the balances on the TBInput sheet have been entered correctly and the correct basis has been specified for each of the accounts, all these subtotals should contain nil values which indicates that the trial balance is in balance. If any of the subtotals do not contain nil values, the imbalances will be highlighted in red until the imbalances have been resolved.
Note: If you encounter an imbalance in any of the months on the TB sheet, we recommend that you review the data which has been included on the TBInput sheet starting with the first month which contains an imbalance. It may also be necessary to compare the balances on the TBInput sheet to the source from where the data has been entered or copied.
