This article outlines the process for customising templates with the opening balances feature to roll-over from the current year to the next year.
We highly recommend reading Customisations Using Named Ranges and Template Formulae and Coded Actions prior to attempting to customising your opening balances.
The opening balances code takes values from the relevant ranges and copies and pastes them into the new workpaper when performing the import opening balances function. Named ranges are the main component in setting up a template with roll-over capabilities - see the knowledge article on Customisations Using Named Ranges.
WARNING: Opening balances are pasted as values and therefore care needs to be taken when defining named ranges to not override any calculation in specific cells.
There are three main classes of named ranges used in the opening balance functions:
- Single cells
- Fixed list
- Dynamic list
1. Single cells
This function copies the value of a single cell from the opening balance workpaper into a new workpaper.
When to use: When a single closing balance is needed as an opening balance next year (i.e. depreciation calculations)
- 'SingleOB': Opening balance value (that is rolled forward from the previous workpaper)
- 'SingleCB': Closing balance value (to roll forward to the new workpaper)
For example:
Below is a quick video on how to use the single cell rollover function:
2. Fixed list
The fixed list formula copies information from last year's closing balance to this year's opening balance by matching the text descriptions from one period to another. It is intended to be used for lists of data that have unique rows with a lot of data.
When to use: When a list of static items which may change row number have closing balances and are needed as an opening balance next year (i.e. Income Tax Reconciliation)
The fixed list named range has additional variables. It should be labelled as 'FixedList.1_2_3'. When there are multiple, they should be labelled as:
- 'FixedList_1.1_2_3'
- 'FixedList_2.1_2_3' etc.
The string variable '1_2_3' requires you to input three variables, which specify which columns the opening balance code looks for when rolling over, as follows:
- Variable 1: The column in the range which identifies the row which is to be copied from period to period
- Variable 2: The column in the range which represents the closing balance data (to roll forward to the new workpaper)
- Variable 3: The column in the range which represents the opening balance data (that is rolled forward from the previous workpaper)
Note: This rollover function is case and character specific to the first variable.
In the example below the data from the column labelled "Current Year" will be rolled over into the "Last Year" column (note the named range: 'FixedList.1_3_4').
- Variable 1: Column C, which is the list of items to be matched. Even though it is not the first column in the worksheet, it is the first column in our named range. The text in these cells is what will identify the row to roll over to the correct row, even if additional rows have been added.
- Variable 2: Column E, the 3rd column. This is the current year balance that is to be rolled over into column F next year.
- Variable 3: Column F, the 4th column. This is the column you want the information from the prior year's column E to populate in.
Below is a quick video on how to use the fixed list rollover function:
3. Dynamic list
The dynamic list opening balance function is designed to be used with tables that could have a varying number of rows (or records).
When to use: When a custom list of items have closing balances that are needed as an opening balances next year (i.e. a list of loans or dividends, etc)
The following three named ranges are required:
- 'DynamicList': Copies the list from the opening worksheet into the same location in the new worksheet
- 'DynamicCB': Is a list of closing balance data (to roll forward to the new workpaper)
- 'DynamicOB': Is a list of opening balance data (that is rolled forward from the previous workpaper)
Note: 'DynamicList', 'DynamicCB' and 'DynamicOB' must have the same number of rows and cannot reference the same columns.
Below is a quick video on how to use the dynamic list rollover function:
Template customisation service
If you would like customisations made to your firm's workpapers, but don't feel confident doing them yourself, please refer to Business Fitness Customisation Service for more information on our team making the changes for you.