Trial balance import

Entering client draft trial balance data takes place at the planning stage of the engagement:

• This data is then inspected, sorted, grouped (see trial balance sorting and sub-totals) and tagged for further work in terms of risk/materiality
• The data feeds through to the lead schedule pages, plus to analytical review pages
• Journal entries may be created against this for audit adjustments and the adjusted amounts are displayed in the final column on the trial balance
• Note that trial balance data may be imported without budget and/or prior year and account codes may be omitted if not required, however, if omitting account codes ensure that the names of accounts are all unique otherwise the first use of the name will be replaced by the second and the trial balance will not balance.

Trial balance data may be entered manually line-by-line however it is much faster to upload directly from a file provided by the client as follows:

Importing the data 

The import tool offers multiple ways to upload your files, including selecting from your computer, dragging and dropping, or pulling directly from SharePoint. It intelligently detects common trial balance formats and suggests mappings, while also allowing for manual adjustments when needed. Additionally, the tool provides a detailed import report, helping you identify any skipped rows and ensuring data accuracy.

Watch the video below for a full walkthrough of the trial balance import tool in action. 

• Data may be cleared using the clear all button in the import dialog box and re-imported if required – but remember that clear all will also remove any prior year work, sub-headings or risk flagging so should not be used in second and subsequent year
• In most cases, if a later trial balance is say provided by the client a second import over the top will update the differences only and leave any sorting and subtotalling already done intact
• If a mistake is made in second-year import rather than using clear all it will be better to restore a duplicate from backup (see duplicate/restore function)

Trial balance data from a client in Excel - MYOB AO

Below is a trial balance out of MYOB AO (using print trial balance - to an .xlsx file): 

• To the standard trial balance in Excel add a column to the right with a heading called section then enter references to match the appropriate section code from the trial balance page (e.g. G for income)
• If no section column is used all accounts will be imported but need to be sorted into the correct group after importing
• When using the section column anything uncoded will not be skipped over and not imported – so may use this to skip headings, subtotals or irrelevant accounts or just delete these extra unwanted lines
• As an alternative to using a section column put sub-headings in trial balance corresponding exactly to trial balance section names - must be exact e.g. “payroll” for the I section (“wages” say will not be recognised)

Trial balance data from a client in Excel - Xero

Make sure that the option "Group by Account Type" is selected so that accounts are mapped into the correct section. 

There is no need to edit the Xero TB prior to import. Just import the raw Excel TB, however, now that we now allow up to three prior periods to be imported the Xero export may be used to extract these.

Go ahead and use the import tool (see above) matching the columns as required. 

For subsequent years just import the new year TB and if there have been no changes to account codes or names everything should just match up with the prior year and work seamlessly. 

Note:

There is a quirk with Xero trial balances due to their limitation with categorising items like salaries and wages. These will be added to the expenditure section by default. So the user will normally manually drag and drop this into the payroll section. However in the subsequent year the new wages and salaries will be put into the expenditure section again - meaning that there is a duplicated account. Work-arounds are as follows:

•  
  •  
    •  
      • One option is to clear and reloading the TB.
      • The downside is potentially losing any risks you have created, and prior years beyond the first.
      • A better work-around is to remove the headings from the second year import TB, so that the software would be forced to match on account numbers and names - a bit like having no 'section' column in a non-Xero import template.
      • The other option is just to manually edit the payroll line in the TB so that the two rows are combined, then delete the duplicate. If there is just one payroll line this is fairly easy to do and so is probably the most efficient way to handle this.

Trial balance data from a client in .xlsx/.csv – Other

• For other software that allows exporting a trial balance to Excel/CSV, create the trial balance and amend headings to the following– code, name (or account), current (or instead of current may have two columns called debit and credit), prior, budget

Adding additional prior year's data

• Data from prior years is retained in the database and on rollover, this data will also be available to view in the Trial Balance, Analytical Review and Lead Schedules.
• If uploading a trial balance, it is also possible to add two additional years into the import template (call the columns 'prior 2' and 'prior 3').
• To make the additional years display on a page (if not displaying automatically while templates are being updated), use the Customise button on the page where the TB data is displayed, and select the box called 'Show all prior year columns' - see screenshot below:

• Additional year data may also be added manually into the TB, for instance, if key amounts like sales, cost of sales and wages were required. This is done from the TB page by selecting the edit button on the line item:

• Note that pages with the extra year data will print as landscape in the final PDF, however, if the extra prior year columns are empty they will default back to portrait. 

Second and subsequent year trial balance import

• In second and subsequent years it is not necessary to import any of the prior columns as it will already be in there as a result of the rollover process – in this case, delete the whole column including heading
• In second and subsequent years do not use a section column as items with identical account codes and names will automatically match to prior year – any new items will be listed at top of trial balance for later sorting
• Another option for second-year imports is to download a copy of the prior year trial balance in CSV format and use this as the starting point for adding current year figures – this download is done by clicking the paper-clip icon next to the prior heading at the top of the trial balance page: 

Problems with trial balance import

Note that our version 3 importer no longer supports .xls files. Just save as .xlsx and they will work! 

If after following all these steps a file still fails to import then send a copy through to the support site - we will advise what is wrong and how to fix.