How to import customer or supplier opening balances in Sage Accounting.
Description
You can import customer or supplier opening balances instead of manually entering them.
Resolution
Enter customer or supplier opening balances
Follow these steps to manually enter customer or supplier opening balances.
- Go to Settings. In the Opening Balances section, Select Customer or Supplier.
- Check the Accounts Start Date is correct.
- Select New Opening Balance, then complete the details.
See required details>| 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. |
| 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. |
TIP: If you’ve got any outstanding payments on account for customer's or supplier's, you will enter these as credit notes, which you can then allocate to an invoice> later.
- If you’ve enabled multi-currency transactions> and the customer or supplier are overseas, enter the details.
See required details>| Total (currency symbol) | Enter the amount in the customer or supplier's 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 invoices and credit notes for 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.
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
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.
See Customer template>
Customer opening balances file format
Not VAT registered, Standard VAT and Flat Rate Invoice Based
Invoice Basis
| Column | Field Name | Type | Mandatory | 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
Cash Basis
| Column | Field Name | Type | Mandatory
| 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, 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. |
See Supplier template>
Supplier opening balances file format
Not VAT registered, Standard VAT and Flat Rate Invoice Based
Invoice Basis
| Column | Field name | Type | Mandatory
| 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
Cash Basis
| Column | Field Name | Type | Mandatory | 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 your account, enter this using Crn. This imports as a credit note, which you can allocate to an invoice or refund later. |
| 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 more 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, 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 are 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 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.
Africa
Asia
Australia
Canada
France
Ireland
Kenya
Nigeria
Portugal
South Africa
Spain
United Arab Emirates
United States
Germany