Enter or import customer or supplier opening balances
This is step 4 of 7 to set up your clients with Sage Accounting.
On this page learn how to:
- Enter customer or supplier opening balances
- Import customer or supplier opening balances
Enter customer or supplier opening balances
Follow these steps to manually enter customer or supplier opening balances.
Before you begin:
-
check your VAT Scheme and Accounts Start Date are correct by selecting Settings then Accounting Dates & VAT.
-
gather a list of any outstanding customer or supplier invoices and credit notes
- Go to Settings. In the Opening Balances section, Select Customer or Supplier.
- Check the Accounts Start Date is correct.
If it isn’t, select the date. Change it as required and select Save.
- Select New Opening Balance, then complete the details.
Show the New Opening Balance details guide
| Type | To record an invoice, choose Inv; or to record a credit note, choose Crn. |
| Date | Enter the date of the invoice or credit note. This date must be before the Accounts Start Date entered in Accounting Dates & VAT. |
| Customer | Choose the relevant customer or supplier. |
| Reference | Enter a reference for the opening balance. For example, the invoice or credit note number. |
| Details | If required, enter any additional information, such as, what the invoice or credit note was for. |
| Net | This option only appears if you use the VAT Cash Accounting scheme. Enter the net value of the transaction. |
| VAT Rate | This option only appears if you use the VAT Cash Accounting scheme. Choose the appropriate VAT Rate. |
| Total |
If you use the Standard or Flat Rate VAT scheme, enter the gross value of the invoice or credit note. If you use the VAT Cash Accounting scheme, this value automatically calculates from the net amount and VAT rate. |
If you’ve any outstanding payments on account for this customer or supplier, you should enter these as credit notes which you can then allocate to an invoice (opens in a new window or tab) at a later date.
- If you’ve enabled multi-currency transactions (opens in a new window or tab), and the customer or supplier is based overseas, enter the details.
Show the currency field guide
| Total (currency symbol) | Enter the amount in the customer or suppliers' base currency. |
| Exchange rate | If you use live exchange rates, this is the latest rate. If required, you can change this to the rate used for the original transaction. The total value in your base currency automatically updates based on the foreign value and the exchange rate. |
- Select Save, then repeat these steps until you’ve entered all outstanding invoices and credit notes for each of your customers or suppliers.
Import customer or supplier opening balances
Follow these steps to import customer or supplier opening balances. This is a useful time saver when you have lots of records to enter.
Before you begin you must:
-
set a VAT Scheme and Accounts Start Date by selecting Settings then Accounting Dates & VAT.
-
ensure that the file contains only customers or suppliers opening balances. To import both, you need two separate files
-
save the file in comma separated value (CSV) format
-
ensure that the file uses the same column headings and formatting as in the customers or suppliers opening balances template file
You can enter bank or ledger account opening balances manually, see 1. Enter opening balances from your trial balance for help on this.
Step 1: Download the CSV template file
- Go to Settings.
- In the Opening Balances section, select Customers or Suppliers.
- Select New Opening Balance and then select Import Opening Balances.
- Select the hyperlink to download the template CSV file.
- Go to the downloads folder on your computer to find the file.
Step 2: Prepare the opening balances file
If you already have a file, compare it to the template file before importing your opening balances. The column headings and data format in your file must match the template.
If you don't have a file, you can add your data to the template file. Leave the column headings as-is and then replace the sample data with your data, using the same format.
The maximum file size you can import is 0.5MB. If your file is larger, split it into another file.
When preparing your file, use the tables below to ensure that your file format is correct. Use the table that applies to your VAT scheme. Remember that the header row in your CSV file must use the same headings as shown in the Field Name column.
Customer opening balances file format
Invoice Basis
| Column | Field Name | Type | Compulsory | Notes |
|---|---|---|---|---|
| A | Type | Text | Yes | To enter an invoice, enter Inv or for a credit note, enter Crn. If you have an outstanding payment on account, enter this using Crn. This imports as a credit note which you can allocate to an invoice or refund at a later date. |
| B | Date | Numbers | Yes | Must be in the format dd/mm/yyyy. |
| C | Customer Name | Text | Yes | Enter the customer’s name as it appears on their record. |
| D | Customer Reference | Text and numbers | Yes | Enter the customer’s unique reference as it appears in their record. |
| E | Reference | Text and numbers | Yes | Enter a reference of your choice, for example, an invoice number. |
| F | Details | Text and numbers | No | If required, enter any additional details up to a maximum of 25 characters. |
| G | Total | Number plus 2 decimal places | No | Enter the gross value of the transaction. |
Cash Basis
| Column | Field Name | Type | Compulsory | Notes |
|---|---|---|---|---|
| A | Type | Text | Yes | To enter an invoice, enter Inv or for a credit note, enter Crn. If you have an outstanding payment on account, enter this using Crn. This imports as a credit note which you can allocate to an invoice or refund at a later date. |
| B | Date | Numbers | Yes | Must be in the format dd/mm/yyyy. |
| C | Customer Name | Text | Yes | Enter the customer’s name as it appears on their record. |
| D | Customer Reference | Text and numbers | Yes | Enter the customer’s unique reference as it appears in their record. |
| E | Reference | Text and numbers | Yes | Enter a reference of your choice, for example, an invoice number. |
| F | Details | Text and numbers | No | If required, enter any additional details up to a maximum of 25 characters. |
| G | Net | Number plus 2 decimal places | No | Enter the net value of the transaction. |
| H | VAT Rate | Text | No |
Enter one of the following VAT rates: |
| I | VAT | Number plus 2 decimal places | No | Enter the VAT element of the transaction. |
| J | Total | Number plus 2 decimal places | No | Enter the total gross value of the transaction. The net and VAT amounts must equal the total value otherwise the import will fail. |
Supplier opening balances file format
Invoice Basis
| Column | Field name | Type | Compulsory | Notes |
| A | Type | Text | Yes | To enter an invoice, enter Inv or for a credit note, enter Crn. If you have an outstanding payment on account, enter this using Crn. This imports as a credit note which you can allocate to an invoice or refund at a later date. |
| B | Date | Numbers | Yes | Must be in the format dd/mm/yyyy. |
| C | Customer Name | Text | Yes | Enter the supplier’s name as it appears on their record. |
| D | Customer Reference | Text and numbers | Yes | Enter the supplier’s unique reference as it appears in their record. |
| E | Reference | Text and numbers | Yes | Enter a reference of your choice, for example an invoice number. |
| F | Details | Text and numbers | No | If required, enter any additional details up to a maximum of 25 characters. |
| G | Total | Number plus 2 decimal places | No | Enter the gross value of the transaction. |
Cash Basis
| Column | Field Name | Type | Compulsory | Notes |
|---|---|---|---|---|
| A | Type | Text | Yes | To enter an invoice, enter Inv or for a credit note, enter Crn. If you have an outstanding payment on account, enter this using Crn. This imports as a credit note which you can allocate to an invoice or refund at a later date. |
| B | Date | Numbers | Yes | Must be in the format dd/mm/yyyy. |
| C | Customer Name | Text | Yes | Enter the supplier’s name as it appears on their record. |
| D | Customer Reference | Text and numbers | Yes | Enter the supplier’s unique reference as it appears in their record. |
| E | Reference | Text and numbers | Yes | Enter a reference of your choice, for example an invoice number. |
| F | Details | Text and numbers | No | If required, enter any additional details up to a maximum of 25 characters. |
| G | Net | Number plus 2 decimal places | No | Enter the net value of the transaction. |
| H | VAT Rate | Text | No |
Enter one of the following VAT rates: If you have any VAT registered EU suppliers or suppliers outside of the EU, you should enter No VAT, and then enter the gross value of the transaction in the Net and Total columns. This is because VAT on these transactions is accounted for when you create the invoice, not when receive or make the payment. If you haven’t yet accounted for the VAT in your previous system, you can manually adjust your next VAT return to include the necessary values. |
| I | VAT | Number plus 2 decimal places | No | Enter the VAT element of the transaction. |
| J | Total | Number plus 2 decimal places | No | Enter the total gross value of the transaction. The net and VAT amounts must equal the total value otherwise the import will fail. |
Step 3: Import the customer or supplier opening balances file
- Go to Settings.
- In the Opening Balances section, select Customers or Suppliers.
- Select New Opening Balance and then select Import Opening Balances.
- Select Choose File. Browse to your opening balances CSV file and then click Open.
- Select Upload.
- When prompted that the upload was successful, select OK.
VAT considerations after importing
If you use the Cash Basis VAT Scheme and you have any VAT registered EU customers outside the EU, the VAT rate defaults to No VAT and the gross value appears in the Net and Total columns. This is because VAT on these transactions is accounted for when you create the invoice not receive or make the payment.
If you haven't yet accounted for the VAT in your previous system, you need to manually adjust your next VAT Return to include the necessary values.
If you’re importing from Sage Accounts, the VAT rate appears based on the tax code you used for each transaction. If for any reason you amended the VAT rate percentage in Sage Accounts, you can click the transaction to manually amend the net and VAT amounts then Save.
If you amend any of the values, it will adjust the Trade Debtors control account balance. This will create a difference in the Trial Balance report from Sage Accounts. When entering your nominal opening balances, you'll need to decide where to post the difference. For example, you may want to post it to Corrections.