When filling the Excel template to create an Excel organisation, the data entered in the cells must follow a specific format.
Historical data format
The template used to import data to an Excel organisation is split into tabs for the Profit and Loss and the Balance Sheet.
Depending on where the account in question sits will determine what format the data should be added to the template as.
📎NOTE: It doesn't matter if the data is being uploaded in monthly, quarterly or annually periods, the format will always be the same.
Profit & Loss data format
Accounts in the Profit and Loss tab will need to be uploaded as transactional period movement.
Income
Cost of Sales
Expenses
Other Expenses
Other Income
All figures will need to be uploaded as positives.
EXAMPLE: If an Income account has a monthly transactional total of £100, the figure added to the spreadsheet will be 100. If it was a negative figure, such as for a refund, the figure to add would be -100.
If an Expense account has a monthly transactional total of £50, it will also be added as a positive, so the figure added to the spreadsheet will be 50. If it was a positive figure, the figure added to the spreadsheet would be -50.
Balance Sheet data format
Accounts in the Balance Sheet tab will need to be uploaded as cumulative period movements:
Bank
Fixed Assets
Current Assets
Non-Current Assets
Current Liabilities
Non-Current Liabilities
Equity
All figures will need to be uploaded as positives.
EXAMPLE: If a bank account had a balance of £500 at the end of January, the figure added to the spreadsheet would be 500. However, if the figure was a negative, the figure would be uploaded as -500.
If a liability account such as a loan had a balance of £300, it would also be added as a positive. So the figure added to the spreadsheet will be 300. If it was a positive liability, the figure added to the spreadsheet would be -300.
📌TIP: You can add switch rules in the chart of accounts to switch a negative asset to a liability.
Period headers format
You should not amend the period headers in row 5 of the Profit & Loss tab, or row 7 in the Balance Sheet tab. The periods are created automatically based on the information set in cells B2-B6 in the Profit and Loss tab.
For more information, visit our guide, Complete an Excel organisation import template.
Drop-downs format
Some data must be selected via drop-down lists and the data in the following cells or rows can’t be entered manually:
Profit & Loss tab
B3 - Start month
B4 - Period
B5 - Currency
Balance sheet tab
B1 - VAT scheme
B2 - VAT frequency
Column D - System accounts