| Article Objective: To assist users in navigating the Active Platform to import a trial balance using Excel when the user does not have direct access to the clients accounting software or if the software is not listed as one of the integrated cloud sources. If you have not yet created your Workpaper binder, see Knowledge Article: How to create a binder. Context: A source is where the financial information is being imported from into Active Workpapers. Linking a source to a binder can be performed during binder setup using the setup wizard or through the settings & tools menu once a binder has been created. Please refer to the Glossary for definitions of key terms used in this article. TABLE OF CONTENTS |
Connect to Excel source
After you have created your binder and are at the stage of connecting a source, select Excel and give your dataset a name, then click Add Source.
Financial data configuration and calculation columns
Once connected, you will be navigated to the financial data configuration and calculation columns page where you can decide on what year(s) to import from.
The current financial year and the prior financial year will automatically be pre-filled for you. You can manually override this if necessary. When reviewing the pre-filled information, ensure the 'Primary Column' checkbox is ticked for the current period.
Click Add Normal Column on the '+ Add Column' dropdown to add more comparative year datasets to your binder.
You can also add calculation columns, which can calculate a variance, percent variance or percent of sales to compare your current and prior year datasets. Click the Add Calculation Column on the '+ Add Column' dropdown to do so.
Finalise binder details
The last step of the setup wizard is verifying and confirming the binder details. Some of the fields in this page are pre-populated with what has been selected at the beginning of the creation process. You can manually override this, if applicable.
Additionally, fill out the remaining sections which require detail where applicable, including Accounting Method, Depreciation Method, Entity Type and Lead Schedules.
Once you have confirmed the details on this page, click Complete to finalise the binder setup process.
Importing trial balance
Once the binder has been set up, you will be prompted to upload your Excel trial balance file.
You will be presented with two options:
Option 1: Upload file (most commonly used)
Using this option, you can drag and drop, or select Upload File to insert a .xlsx or .csv file stored locally in your computer or in any other document management system.
Option 2: Create new journal
Using this option, to import a trial balance you can create a new journal by clicking + Add Journal, rather than uploading an Excel document. You would normally only choose this option if your trial balance is relatively small.
Trial balance data
To ensure the right data is captured in your binder, ensure your data file includes the following columns:
1. Acc. No.
2. Acc. Name *
3. Acc. Classification
4. Debit *
5. Credit
6. Quantity
7. Memo
*These columns are mandatory for importing your data. Where the data exists in only one column (i.e. debit and credit in the same column, ensure credit values are shown as – negatives).
Trial balance formatting
When formatting the trial balance document, consider the following note:
- Active Workpapers does not mind the use of blank lines or header accounts, however, it will assume that every line with a value in the debit or credit column is a valid account that needs to be imported
- In the Excel import process, it will automatically link the following text to account classifications as shown below:
Classification | Valid text strings for account classification |
Asset | "Asset", "Assets" or "A" |
Liability | "Liability", "Liabilities" or "L" |
Equity | "Equity", "Capital" or "E" |
Revenue | "Revenue", "Income", or "R" |
Cost of goods sold | "COGS", "Cost", "Cost of Goods Sold", "Direct Cost/s" or "C" |
Expense | "Expense", "Expenses" or "X" |
Other | All other text strings, including "O" |
Final steps
For this example, we will choose to proceed with Option 1 as this will be relevant for most cases.
Once you have uploaded your Excel file, you will be presented with a screen called 'Import Excel Ledger Data'.
Select header row
Select your header row as prompted. This should usually pre-populate, however if it has not or it has pre-populated incorrectly, you are able to manually correct this.
Preview
Next, review the preview. This outlines which data sections are linked to which column in the document. This should mostly pre-populate, however if it has not or it has pre-populated incorrectly, you are able to manually correct this.
Once all information is reviewed and amended accordingly, click Proceed.
After clicking Proceed, the information in the Excel document will be converted to an 'Imported Trial Balance' journal.
If you use an established account numbering system, you can automate this process by selecting the numbering system used in the Auto Classify dropdown menu. In this case, we will select MYOB.
Once selected, it will automatically change the account classifications based on the account numbering system.
Finally, ensure the journal balances and on the provision it does, click Add and Close.
Reimporting current year date
To reimport current year data (which overrides the existing import), navigate to the index and select Reimport.
Once you select Reimport, the 'Import Excel Ledger Data' pop-up will appear on the screen.
Repeat the 'Final steps' section to upload data.
Uploading prior or subsequent year data
To import prior or subsequent year data, navigate to the index and select Reimport.
Once you select Reimport, the 'Import Excel Ledger Data' pop-up will appear on the screen.
Repeat the 'Final steps' section to upload data, however make sure, before you add the journal you change the column year to be the appropriate financial year you are importing for.
