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:

  1. Go to Settings. In the Opening Balances section, Select Customer or Supplier.
  2. Check the Accounts Start Date is correct.

If it isn’t, select the date. Change it as required and select Save.

  1. Select New Opening Balance, then complete the details.
    Show the New Opening Balance details guide

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.

  1. 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.
  1. 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.

  • create or import your customer or supplier records

  • 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

  1. Go to Settings.
  2. In the Opening Balances section, select Customers or Suppliers.
  3. Select New Opening Balance and then select Import Opening Balances.
  4. Select the hyperlink to download the template CSV file.
  5. 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.

Note:

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

Not VAT registered, Standard VAT and Flat Rate Invoice Based

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.

VAT Cash Accounting and Flat Rate Cash Based

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:

* Standard
* Lower Rate
* Zero Rated
* Exempt
* No VAT

If you have VAT registered, EU customers or customers outside 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 you 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 (opens in a new window or tab) 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.

Supplier opening balances file format

Not VAT registered, Standard VAT and Flat Rate Invoice Based

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.

VAT Cash Accounting and Flat Rate Cash Based

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:

* Standard
* Lower Rate
* Zero Rated
* Exempt
* No VAT

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

  1. Go to Settings.
  2. In the Opening Balances section, select Customers or Suppliers.
  3. Select New Opening Balance and then select Import Opening Balances.
  4. Select Choose File. Browse to your opening balances CSV file and then click Open.
  5. Select Upload.
  6. When prompted that the upload was successful, select OK.

VAT considerations after importing

If you use the VAT Cash Accounting or Flat Rate Cash Based VAT schemes 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 when you 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.

Note:

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.