This knowledge article outlines the process for customising templates with the opening balances feature to roll-over from the current year to the next year.
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 Using Named Ranges - Customisations.
Important: Opening Balances are pasted as values and therefore care needs to be taken when defining names ranges to not override any calculation in specific cells.
There are three main classes of named ranges used in the Opening Balance functions:
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 Calcs)
SingleOB: Opening balance value (that is rolled forward from the previous workpaper)
SingleCB: Closing balance value (to roll forward to the new workpaper)
Below is a quick video on how to use the single cell rollover function:
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 Rec)
The fixed list named ranges has additional variables. It should be labelled as FixedList.1_2_3.
When there are multiple, they should be labelled as:
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 we copy from period to period
Variable 2: The column in the range which represents closing balance data (to roll forward to the new workpaper)
Variable 3: The column in the range which represents 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 is the 3rd column. This is the current year balance that to be rolled over into column F next year.
Variable 3: Column F is the 4th column, which is last year's balance. This is the column we 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:
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 and are needed as an opening balance next year (i.e. 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 & 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:
HowNow 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.