Accounts - opening balances

Article Objective: To assist users in navigating Active Ledger & Reporting to bring in, edit and roll over opening balances, as well as reconciling opening balances with the practice ledger file.

Context: There are several ways in which opening balances can be brought into or edited in Active Ledger. This allows full flexibility if data manipulation is required, or if you are wanting to roll over opening balances from other files.

Please refer to the Glossary for definitions of key terms used in this article.

TABLE OF CONTENTS

Overview
- Why do we need opening balances?
- How are opening balances calculated?
Directly imported from the client file
Imported from Excel
Roll over from a previous dataset
- Using the prior year's dataset with 'opening balance accounts'
Using an opening balance adjustment journal
How to correct opening balances to reconcile with practice ledger file
- Example

Overview

Why do we need opening balances?

Certain reports and calculations require the opening balance of any given account. In Active Ledger, the following reports require opening balances:

Statement of Changes in Equity
Statement of Cashflows

In addition, calculations such as debtors days, creditors days and inventory days also require the use of opening balances.

How are opening balances calculated?

Opening balances in any given dataset are calculated from journals which are specially marked as 'Opening Balance' journals. There are two kinds of opening balance journals:

1. Imported opening balance journals

These represent opening balances that have been imported from another system or dataset. They can be created in the following three ways:

An import from the client file (via API)
An import via Excel
A roll over from a previous dataset

2. Opening balance adjustment journals

Opening balance adjustment journals can be entered directly in Active Ledger in order to create and/or adjust the values of opening balance journals. They work like any other journal except that all entries count towards the opening balance of that particular account.

Directly imported from the client file

The easiest way to bring in opening balances is to simply use the opening balance journal that is imported when you connect directly to the client's file. This method works best if you know that the file is clean and contains the correct data.

Imported from Excel

Generally, when using the Excel add-in to import data, a whole trial balance is imported to create a new dataset. However, it is also possible to use this function to create just an opening balance journal. To do this, first prepare your data using the three to six column method as normal. For more information on how to use the Excel add-in to import data see the knowledge article: Datasets - Excel trial balance import.

When the journal dialogue appears, you will need to change the Journal Type from 'Imported Trial Balance' (the default) to 'Imported Opening Balance' using the dropdown menu. You will get a notification that the import will override any existing import journal for the same period. This ensures that there are no double ups when importing several times or from multiples sources.

Roll over from a previous dataset

If you are aware that the client's prior year closing balances in their file are incorrect, you can choose to use a prior year's dataset when creating the current year dataset. To do this, select the Roll over opening balances checkbox when adding a new dataset. This will bring up an Opening Balance Dataset dropdown to select the relevant dataset to use. From here you are also able to refresh the opening balances if any changes occur in the selected dataset.

In this case, instead of seeing the opening balances as an imported journal, they will appear as a separate journal marked not for export, with the amounts coming from the closing balances of the dataset chosen rather than from the client's file.

Using the prior year's dataset with 'opening balance accounts'

As an added bonus, this method can also be used if you have tagged specific accounts as Opening Balance Accounts in the chart of accounts. This ensures that any account balances sitting under the same header, will be moved to those tagged accounts in subsequent years. This is particularly useful for beneficiary/partner accounts.

To ensure that the balances are rolled into the correct accounts in subsequent years, make sure that your accounts are set up correctly in the chart of accounts. The example below shows a typical set up for a beneficiary.

Note: All beneficiary accounts associated with Jenny Johnston sit under the header 'UPE - Jenny Johnston'. This is the same for Joe Johnston and would be for any other beneficiaries.

The account 'Opening Balance - Jenny Johnston' is tagged as an opening balance account. This tells Active Ledger to move the balances of the 'Drawings - Jenny Johnston' and 'Share of Profit - Jenny Johnston' accounts to this account when creating next year's dataset.

An example is shown below of the resulting balance sheet after creating the new dataset.

Using an opening balance adjustment journal

A full opening balance journal can be prepared in Active Ledger using the journals screen. However, this method can also be used to make adjustments to already imported opening balances. This is particularly useful if you just need to make a small change to your client's data.

To create an adjustment journal, navigate to the Journals tab in the Datasets screen and create a new journal. In the journal type, dropdown menu select Opening Balance Adjustment Journal. Fill in and save your journal as normal.

Note: Unlike the three methods described above, in this method the 'Opening Balance Adjustment Journal' will be ADDED to any opening balances that already exist in the file. It will NOT replace any opening balances.

How to correct opening balances to reconcile with practice ledger file

Active Ledger & Reporting relies on the client's accounting file being the source of truth. You may find that when you connect your client's source file (such as Xero, MYOB or QBO) to a dataset, that your closing balances per your clients accounts as at the last finalised year may not agree to what you have in your comparative year dataset from your practice ledger (where the financial statements are issued from). This would occur due to not having pushed balancing/alignment entries to the client file in the past, or if the client has backdated transactions to previous financial years.

Example

The client has a MYOB Accountright Live accounting file. The last finalised year in your practice ledger, XPA, is 30 June 2022. You notice that the balances that your client has in their accounting file as at 30 June 2022 do not agree to what you have in your practice ledger file. You remember that you have never performed balancing/alignment journals within your clients file to align it to your practice ledger. When you have added your 2023 MYOB dataset to Active Ledger & Reporting, your opening account balances do not agree to the closing balances you had within your XPA comparative dataset.

What do you need to do to set yourself up for success within Active Ledger & Reporting?

You will first need to have added datasets to your clients Active Ledger & Reporting file. If you have not yet added datasets, see this knowledge article: Datasets - overview.

To align your 2023 MYOB dataset opening balances with the 2022 XPA dataset closing balances, there are a few options:

Option 1 (Recommended)

Perform an alignment entry in the clients accounting file as at the end of the last finalised year, in this case 30 June 2022. Take the trial balance from XPA and MYOB as at 30 June 2022 in Excel, compare the two and prepare a balancing journal in the MYOB account names and numbers. Post this balancing journal into the MYOB file as at 30 June 2022. Once done, come back to Active Ledger & Reporting and press Load Data on the dataset.

Option 2

Perform an alignment entry in the Active Ledger & Reporting file for the client as an 'Opening Balance Adjustment Journal'. Take the trial balance from XPA and MYOB as at 30 June 2022, compare the two and prepare a balancing journal in the MYOB account names and numbers.

See how to create this journal under the 'Using an opening balance adjustment journal' section above.

Ensure that the Export Journal to Client File button is ticked to ensure that this adjustment journal will get sent back to the client file with the other balancing journals for the 30 June 2023 year.

Your opening balance adjustment journal is now within Active Ledger & Reporting and your opening balances within the 2023 MYOB dataset will agree to the closing balances per the 2022 XPA dataset.

Option 3 (only where the client will not allow any alignment journals to their accounting file)

See how to create this journal under the 'Using an opening balance adjustment journal' section above.

Ensure that the Export Journal to Client File button is NOT ticked to ensure that this adjustment journal does not get sent back to the client file with the other balancing journals for the 30 June 2023 year.

Your opening balance adjustment journal is now within Active Ledger & Reporting and your opening balances within the 2023 MYOB dataset will agree to the closing balances per the 2022 XPA dataset.

When you create your 2024 MYOB dataset (and every future year MYOB dataset) within Active Ledger & Reporting, ensure to tick the Roll over opening balances checkbox and rollover from the last completed MYOB dataset in Active, in this example you would roll over from 2023.

This will mean that your 2024 MYOB Dataset will use the closing balances from your 2023 MYOB dataset, rather than the closing balances from the MYOB file itself.