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:

Trial balance data from a client in Excel - MYOB AO

Below is a trial balance out of MYOB AO (using print trial balance - to Excel 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

Old-style Xero TB (for new-style Xero TB see here for instructions):

• Trial balance data may be imported directly from Xero by printing the trial balance to Excel importing as is – subheadings will be recognised data will be sorted by these, however Xero subheadings are not precise in terms of balance sheet items so extra sorting may be required
• Xero does not have the option to include prior year balances - may need to be added manually (if first year of job)
• A Xero trial balance in Excel can of course have a section column added, or specific subheadings inserted as per MYOB instructions

Trial balance data from a client in Excel/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

Using a downloadable CSV template for trial balance data

• If no suitable Excel/CSV trial balance is available from the client then it is best to use our downloadable template
• Select download import template file from the import trial balance dialogue box:
• The template has some basic instructions and an example line: 
• Type data in manually or copy and paste it into the template – then save the file for importing

Adding additional prior year's data

• From February 2022 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 Scedules.
• 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. 

Importing Excel/CSV template

• Go to import dialogue on the trial balance page and browse to Excel/CSV file previously saved using choose file button
• Ensure the current file is displayed next to choose file button then click add to complete import – in the example below the file is trial balance.xls: 

• The table at top of trial balance page should populate immediately - if not there is a problem with the trial balance import file - check headings and try again
• 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)

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

• The most common problem with importing is incorrect headings - double-check if not working - they must be on the same line and use wording as described
• If that doesn’t work try deleting extra lines to the side of the right-hand column - any odd (and sometimes hidden) characters may interfere with the import
• If the file is in Excel try saving as a CSV and importing – Excel is a more complex format and sometimes there can be hidden formatting that should go away of saved as CSV
• If there are missing lines this may be due to duplicated names or duplicated account codes - these may lead to overwriting of each other - all codes must be unique - in which case account names need not be unique - but if code column is not used then all account names must be unique

TIP: 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.