This template enables users to record year end adjusting journals against any trial balance and automatically calculates all the final trial balance amounts. The template can be based on any account numbering convention and can contain an unlimited number of accounts and an unlimited number of journal entries. User input is limited to copying the initial trial balance amounts and entering or copying all the year end adjusting journal entries.
The following sheets are included in the template:
TB - copy the initial trial balance account numbers, descriptions and amounts into the first three columns and the final trial balance amounts are calculated based on the journal entries that are recorded on the Journals sheet.
Journals - enter or copy all the year end adjustment journal entries into the first four columns on this sheet. All debit entries need to be entered as positive values and all credit entries need to be entered as negative values.
Trial Balance
A complete initial trial balance should be copied onto the TB sheet. Only the columns with yellow column headings require user input. The columns with light blue column headings contain formulas which are automatically copied for all new accounts that are added to the sheet. The TB sheet contains the following columns:
Acc No - copy the general ledger account numbers into this column. The template accommodates using account numbers based on any account number convention and the number of accounts that can be added to the sheet is not limited.
Account Description - copy an account description for each account number into this column.
TB - copy the initial trial balance amount of each account into this column. The total of all the account balances should equal nil. If the total does not equal nil, it indicates that the trial balance does not balance and the total in cell C4 will therefore be highlighted in red.
Adjustments - Debit - the total of all the debit journal entries which have been recorded against the appropriate account on the Journals sheet will be reflected in this column.
Adjustments - Credit - the total of all the credit journal entries which have been recorded against the appropriate account on the Journals sheet will be reflected in this column.
Final TB - the final account balance after all adjusting journal entries will be reflected in this column.
Note: All the columns on the TB sheet have been included in an Excel table. This feature is useful when entering data in a table format because the formulas that are included in calculated columns (the columns with light blue column headings) are automatically copied when new rows are added to the table or when data is entered into the first blank row below the table. You can therefore add a new account to the table by simply entering or copying an account number in column A - the table will then automatically be extended to include the new account number.
The TB sheet contains totals in the row above the column headings which indicate if the amounts in the appropriate columns balance. The total of the TB amounts in column C and the final TB amounts in column F should always total a nil value in order for the trial balance to be in balance. The sum of the total of the debit adjustment and credit adjustment amounts in columns D and E should also equal a nil value if all the journal entries that have been recorded on the Journals sheet are in balance. If any of the totals on the TB sheet does not equal a nil value, the appropriate total will be highlighted in red.
Note: If you apply a filter to the TB sheet, the total calculations will only include the cells that are still visible on the sheet. The total of the filtered cells may not equal a nil value but will still be highlighted in red. This formatting can therefore be ignored when a filter has been applied to the sheet.
Journal Entries
All the year end adjusting journal entries need to be recorded on the Journals sheet. Only the columns with yellow column headings require user input. The columns with light blue column headings contain formulas which should be copied for all new entries that are added to the sheet. The Journals sheet contains the following columns:
Jnl No - a unique journal number should be entered for each journal entry that is added to the sheet. The journal number can be in any format but it is imperative that the journal number is repeated for all entries that form part of each journal. We recommend using a combination of letters and numbers (for example "J1") in order to ensure that the error checking features function correctly.
Acc No - select the appropriate account number to which the journal should be allocated from the list box in this column. All the account numbers that have been added to the TB sheet will be included in the list boxes in this column.
Journal Description - enter a description for the journal into this column. The description should enable users to determine what the purpose of each journal entry is and can be the same for all the rows that form part of the same journal or you can enter a unique description into each line of the journal entry.
Amount - enter the journal amount in this column. Positive amounts are deemed to be debit entries and negative amounts are deemed to be credit entries. The total of all the entries that form part of the same journal number should be nil.
Account Description - the formula in this column displays the account description of the account that has been selected in column B. The account description has been included on the sheet in order to enable users to check whether the correct account has been selected.
Type - the formula in this column indicates whether the journal entry is a debit or a credit. All positive amounts are deemed to be debit entries and all negative amounts are deemed to be credit entries.
Error Code - this column will contain an error code if there is a problem with the journal entry which has been entered in the appropriate row. Refer to the Error Codes section of the instructions for more detail on how to resolve the errors that may be reflected in this column.
Note: All the columns on the Journals sheet have been included in an Excel table. This feature is useful when entering data in a table format because the formulas that are included in calculated columns (the columns with light blue column headings) are automatically copied when new rows are added to the table or when data is entered into the first blank row below the table. You can therefore add a new journal entry to the table by simply entering a journal number into column A - the table will then automatically be extended to include the new journal entry.
All individual journal entries should total a nil value and the total of all the journal entries that are added to the Journals sheet should also total a nil value. If an individual journal entry does not total a nil value, an error code will be displayed next to all the entries that form part of the appropriate journal number. It is therefore imperative that a unique journal number is entered for all year end adjustment journals.
If the total of all the journal entries that are added to the Journals sheet does not equal nil, the total will be reflected in cell D3 and highlighted in red. This formatting will be removed automatically when the problem has been rectified and a nil value is displayed in this cell.
Note: If you apply a filter to the Journals sheet, the total in cell D3 will only include the cells that are still visible on the sheet. The total of the filtered cells may not equal a nil value and will therefore be highlighted in red. This formatting can therefore be ignored when a filter has been applied to the sheet.
Error Codes
The following error codes may result from inaccurate input on the Journals sheet and will be displayed in the Error Code column. The heading of the affected input column will also be highlighted in red:
- E1 - this error code means that the account number that has been selected in column B is invalid. All the accounts that have been added to the TB sheet will be included in the list boxes in column B and the error can therefore be rectified by simply selecting a valid account number from the list box. New accounts must be added to the TB sheet before being available for selection.
- E2 - this error code means that total amount for the appropriate journal number does not equal a nil value. The error codes will be reflected next to all the entries that form part of the appropriate journal number. The error can be rectified by editing the journal amounts in column D or adding / deleting entries in order for the journal total to equal a nil value.
