Accounts import (chart of accounts / opening trial balance)

The accounts import allows you to import a trial balance to post opening account balances taken from your old system. You can use the same import to build your chart of accounts. Both use the same spreadsheet format and can be performed at the same time.

Building a chart of accounts

You should decide on your chart of account structure early on as building your chart of accounts with the import will remove all existing accounting entries. If you do not want to lose accounting data you will need to build the chart of accounts manually.

Importing opening balances

Importing balances on their own won't reset any existing accounting, so you can begin using Brightpearl even before you've completed the migration from another system. However, when you do come to import your opening balances, you will need all of the nominal codes to already exist in Brightpearl unless you are building the chart of accounts at the same time. Remember though that importing a chart of accounts will reset all your existing accounting data, so you may need to add nominal codes manually instead.

Importing debtors and creditors (receivables and payables)

Two additional imports can be done to breakdown your accounts receivable (debtors) and payable (creditors) figures to the relevant customer and vendor accounts. Note that the accounts import cannot be used to import historical transaction details.

Depending on the Tax/VAT scheme you are using you will need to follow different steps to ensure your account balances are properly generated and you account for all tax.

It can be easier to switch between Brightpearl and your previous accounting system at the end of a tax/VAT period, but it is not necessary.

Related how to guides - Accounts Import (Chart of Accounts / Opening Trial Balance)

How to import opening balances (Sales Tax)

Migrating to Brightpearl at the end of a tax period will mean all transactions entered in the current tax period are reported via your old accounting system.

When you import your account balances to Brightpearl, future invoices (i.e. entered in your last system but are dated after your last reconciled tax period from your previous system) will not be represented in your next tax report in Brightpearl. Brightpearl produces the Sales tax report based on sales and purchase invoice journals (types SI and PI) and not from imported or manual journals (type JJ). For example, if your tax periods are:

Tax period 1 - Jan to Mar

Tax period 2 - Apr to Jun

There are transactions entered into the previous accounting system and imported into Brightpearl and transactions entered directly into Brightpearl from sales and purchase invoices :

accounting import sales tax

Although the tax for period 2 totals $128.40 only $86.76 will show on your Sales tax report in Brightpearl. INV 4 was entered in the previous system, but falls into period 2, but due to the migration to Brightpearl it will not automatically be included on any Sales tax report. This means you will need to manually account for it when you declare tax.

We recommend that you run a Sales tax report on both your old system and Brightpearl for your next period, to ensure you account for all transactions. Using this example the following would be produced for period 2:

accounting import sales tax consideration

Note: This is not applicable if no transactions are exported from your previous system that are dated after your last tax period.

To correctly import accounts and balances when using Sales Tax you must do the following:

  1. Reconcile and close your last tax period in your previous system
  2. Export the Trial Balance and outstanding (unpaid) accounts receivable and accounts payable from your previous system, saving them each as CSV files
  3. Format the spreadsheets to correctly match Brightpearl's import routine
  4. Import the trial balance file to Brightpearl
  5. Import your accounts receivable file to Brightpearl
  6. Import your accounts payable file to Brightpearl

1. Reconcile and close your tax period in your previous system

We recommend you migrate over to Brightpearl at the end of a tax period to ensure as much as possible is accounted for by the system.

2. Export the trial balance, accounts receivable and payable from your previous system

Please refer to your previous system instructions for exporting your trial balance, accounts receivable and payable details.

3. Format the spreadsheets to correct match Brightpearl's import routine

The files must be saved as CSV files in order for Brightpearl to read it. Each must also have specific columns.

The Trial Balance will need to have the following columns, and in this order:

  1. Nominal Code
  2. Name
  3. Debit
  4. Credit
  5. Is Bank Account (enter yes or leave blank)
  6. Is Expense Account (enter yes or leave blank)
  7. Tax Code (optional, e.g. T20)
  8. Map Code (optional. Use this field for the previous nominal code from your old system)
  9. Description (optional text description of the account)

Note: When you import the file you should remove the column headings (in red). The maximum number of lines you can import is 9999.

Required Codes

Brightpearl requires a certain minimum set of codes to operate. If your imported file does not include these accounts, then Brightpearl will add them for you. The following are the required codes:

  • 1001 Stock (asset) account
  • 1100 Accounts receivable control account
  • 2100 Accounts payable control account
  • 2200 Sales tax account
  • 2201 Purchase tax account
  • 2202 Tax liability account
  • 3200 Retained earnings
  • 4000 Sales
  • 5000 Purchases
  • 9997 Opening balance (debtors)
  • 9998 Opening balance (creditors)
  • 9999 Suspense account

accounting originals

The Accounts Receivable List will need to have the following columns, and in this order:

  1. Customer code
  2. Company
  3. Contact name
  4. Telephone
  5. Type
  6. Date (YYYY-MM-DD)
  7. Inv.Ref
  8. Details
  9. Amount

accounting originals

The Accounts Payable List will need to have the following columns, and in this order:

  1. Vendor code
  2. Company
  3. Contact name
  4. Telephone
  5. type
  6. Date (YYYY-MM-DD)
  7. Inv.Ref
  8. Details
  9. Amount

accounting originals

The customer/vendor code is the unique reference that should match the code as per your main Customer and Vendor imports.

If you choose to create Customers or Vendors at the same time as importing the opening balances, then the details from the file will be used. If you add a unique code in your accounts receivable or payable files, then you can use this same code to update the contact information with more fields later using the Contact Import.

The date format MUST be YYYY-MM-DD.

Each open invoice must have a unique reference number for that contact (you can re-use the same invoice number for multiple customers, or for customers and vendors - but we don't recommend it).

Don't include a currency symbol for the amount; and remember that all imports are in your base currency.

Transaction types

Use these in the "type" column to define where the transaction is placed in your system.

SI  - Sales Invoice

SC  - Sales Credit

SR  - Sales Receipt, also used for Payment on Account from customers

PI  - Purchase Invoice

PC  - Purchase Credit

PP  - Purchase Payment, also used for Payment on Account to vendors

4. Import the Trial Balance

  1. Go to Settings > Data/Import > Import accounts .
  2. Click Import Chart of Accounts .
  3. Browse for the file to import.
  4. Mark the checkbox to specify that you want to delete and rebuild your chart of accounts and all existing accounting data.
  5. If you want to hide numeric codes throughout Brightpearl (and therefore select codes based on the name) you can tick the available checkbox; you can change this later at Settings > Company > Accounting: Nominal codes).
  6. Mark the available checkbox if you also want to create opening balance trial balance journals. If you leave this unchecked it will only create the nominal codes and no balances.
  7. Click the Upload Chart of Accounts (CSV) button.

When you import your Trial Balance a single figure is posted for the total balance of outstanding receivables and another for total payables. You will need to import your receivables and payables to ensure the figure is broken down into the Net and Tax amounts and attached to each contact record. This will ensure customer statements and your customer and vendor debt reports are accurate.

5. Import outstanding accounts receivable

This import will generate the accounting entries to attach the single debtor figure imported from the trial balance to each customer, ensuring your customer statements and customer debt report are correct.

  1. Go to Settings > Data/Import > Import Accounts .
  2. Click Import accounts receivable .
  3. Browse for the file to import.
  4. Specify whether to add balances and invoices. If you say no you will only import the basic contact data.
  5. Select the date to use for open invoices, or whether to use the date in the file per item.
  6. Click the Upload CSV button.

When it comes to doing your next Sales tax report these transactions will NOT be included and must be manually added onto the Brightpearl Sales tax report figure. You should run a tax report in your old system.

6. Import outstanding accounts payable

This import will generate the accounting entries to attach the single debtor figure imported from the trial balance to each customer. This will ensure your vendor debt report is correct.

  1. Go to Settings > Data/Import > Import Accounts .
  2. Click Import accounts payable .
  3. Browse for the file to import.
  4. Specify whether to add balances and invoices. If you say no you will only import the basic contact data.
  5. Select the date to use for open invoices, or whether to use the date in the file per item.
  6. Click the Upload CSV button.

When it comes to doing your next Sales tax report these transactions will NOT be included and must be manually added onto the Brightpearl Sales tax report figure. You should run a tax report report in your old system.

How to import opening balances (Cash VAT scheme)

With this scheme you do not need to pay VAT until your customer has paid you. If your customer never pays you, you never have to pay the VAT. By migrating to Brightpearl at the end of a VAT period all payments recorded in period 1 in your previous accounting system will be reported via the VAT return on your previous accounting system.

When you import your account balances, invoices entered into your previous system that are not fully paid will need to be manually generated as Quick Invoices and Bills in Brightpearl in order to generate the correct accounting entries with the correct VAT codes. This is so that when the payment is receipted the VAT is recognized correctly; the VAT return reports only on payments made against invoices (journal type SI and PI) and not imported or manual journals (type JJ).

To correctly import accounts and balances when using the Cash VAT scheme you must do the following:

  1. Reconcile and close your VAT period in your previous system
  2. Export the Trial Balance from your previous system, saving the file as a CSV file
  3. Format the spreadsheet to correctly match Brightpearl's import routine
  4. Import the Trial Balance file to Brightpearl
  5. Manually post each of your outstanding debtors and creditors invoices into Brightpearl
  6. Journal the VAT amounts from opening debtor & opening creditor nominal codes against sales VAT & purchase VAT nominal codes

1. Reconcile and close you VAT period in you previous system

We recommend you migrate over to Brightpearl at the end of a VAT period to ensure as much as possible is account for by the system.

2. Export the Trial Balance from your previous system, saving the file as a CSV file

Please refer to your previous system instructions for exporting your trial balance, debtors and creditors details.

3. Format the spreadsheet to correctly match Brightpearl's import routine

The files must be saved as CSV files in order for Brightpearl to read it. Each must also have specific columns.

The Trial Balance will need to have the following columns, and in this order:

  1. Nominal Code
  2. Name
  3. Debit
  4. Credit
  5. Is Bank Account (enter yes or leave blank)
  6. Is Expense Account (enter yes or leave blank)
  7. Tax Code (optional, e.g. T20)
  8. Map Code (optional. Use this field for the previous nominal code from your old system)
  9. Description (optional text description of the account)

Note: When you import the file you should remove the column headings (in red). The maximum number of lines you can import is 9999.

accoungint originals

4. Import the Trial Balance file into Brightpearl

  1. Go to Settings > Data/Import > Import accounts .
  2. Click Import Chart of Accounts .
  3. Browse for the file to import.
  4. Tick the checkbox to specify that you want to delete and rebuild your chart of accounts and all existing accounting data.
  5. If you want to hide numeric codes throughout Brightpearl (and therefore select codes based on the name) you can tick the available checkbox; you can change this later at Settings > Company > Accounting: Nominal codes).
  6. Tick the available checkbox if you also want to create opening balance trial balance journals. If you leave this unchecked it will only create the nominal codes and no balances.
  7. Click the  Upload Chart of Accounts (CSV)  button.

5. Manually post each of your outstanding (unpaid) debtors and creditors into Brightpearl

When you import your Trial Balance a single figure is posted for the total balance of outstanding debtors and another for total creditors. You will need to manually post your outstanding debtors and creditors to ensure the figure is broken down into the individual invoice amounts and attached to each contact record. This will ensure customer statements and your customer and supplier debt reports are accurate.

You will need to get a list of all the outstanding customer debt and supplier debt from your old system and then post Quick Invoices into Brightpearl. You will need the following data for each invoice (customer and supplier):

  • Customer/Supplier name
  • Invoice date
  • Due date
  • Invoice reference
  • Net amount
  • Tax amount
  • Tax rate
  • Total amount

For each outstanding customer invoice post a Quick Invoice:

  1. Go to Customers > Quick Invoice .
  2. Search for the customer (you should import your contacts first).
  3. Select the invoice date and due date.
  4. Enter the invoice reference.
  5. (Optional) Enter any further details.
  6. Enter the Net Amount.
  7. Select the correct Tax Code to calculate the Tax and Total values; check these are the same as the original invoice.
  8. Select the nominal code 9997 (Opening Debtor Balance) .
  9. Click the Submit button.

Each invoice posted will create a journal to move the net invoice amount from the Debtor Suspense code (9997) to the Debtors Control code (1100) and create a VAT entry in the Sales VAT code (2200).

For each outstanding supplier invoice post a Bill:

  1. Got to Suppliers > Enter a Bill .
  2. Search for the supplier (you should import your contacts first).
  3. Select the invoice date and due date.
  4. Enter the invoice reference.
  5. (Optional) Enter any further details.
  6. Enter the Net Amount.
  7. Select the correct Tax Code to calculate the Tax and Total values; check these are the same as the original invoice.
  8. Select the nominal code 9998 (Opening Creditor Balance) .
  9. Click the Submit button.

Each invoice posted will create a journal to move the net invoice amount from the Creditor Suspense code (9998) to the Creditors Control code (2100) and create a VAT entry in the Purchase VAT code (2201).

6. Journal the VAT amounts from opening debtor & opening creditor nominal codes against sales VAT & purchase VAT nominal codes

Note : The debtor and creditor import cannot be used when you are on the cash VAT scheme; it will not account for tax correctly.

Since the VAT balances would have been imported as part of the original trial balance, and a VAT figure is also created through the Quick Invoices/Bills, the VAT has been duplicated. However, the remaining balance in the suspense accounts is also that of the VAT! This can therefore can be matched to the duplicated VAT amount to leave your accounts completely balanced and correct.

You will need to find the balance of nominal codes 9997 (Debtors Opening balance) and 9998 (Opening Creditors balance). Go to Accounting > Chart of Accounts and find the balance for each of these accounts. Then enter a manual journal:

  1. Go to Accounting > Enter Journal .
  2. Leave the date as today's date.
  3. Enter details to explain the reason for the journal.
  4. On the first line select the nominal code 9997 (Opening Debtor balance) with a credit for the full amount left in the account.
  5. On the second line select the nominal code 2200 (Sales VAT) with a debit for the same amount.
  6. Use the tax code T9 for both lines.
  7. Click the Save journal button.
  8. Post a second journal with the first line using nominal code 9998 (Opening Creditor balance) with a debit for the full amount left in the account.
  9. On the second line select the nominal code 2201 (Purchase VAT) with a credit for the same amount.
  10. Use the tax code T9 for both lines.
  11. Click the Save journal button.

You accounting should now be correct, leaving you ready to carry on business as usual!

How to import opening balances (Standard VAT scheme)

Using standard VAT accounting, you pay VAT on your sales whether or not your customer has paid you. By migrating to Brightpearl at the end of a VAT period all transactions entered in the current VAT period are reported via the VAT return on previous accounting system.

When you import your account balances, future invoices (i.e. entered in your last system but are dated after your last reconciled VAT period from your previous system) will not be represented in your next VAT return in Brightpearl. Brightpearl produces the VAT return based on sales and purchase invoice journals (types SI and PI) and not from imported or manual journals (type JJ). For example, if your VAT periods are:

VAT period 1 - Jan to Mar

VAT period 2 - Apr to Jun

There are transactions entered into the previous accounting system and imported into Brightpearl and transactions entered directly into Brightpearl from sales and purchase invoices :

accounting originals

Although the VAT for period 2 totals £280 only £200 will show on your VAT return produced in Brightpearl. INV 4 was entered in the previous system, but falls into period 2, but due to the migration to Brightpearl it will not automatically be included on any VAT return. This means you will need to manually account for it when you fill out your VAT return.

We recommend that you run a VAT return on both your old system and Brightpearl for your next VAT period, to ensure you account for all transactions. Using this example the following would be produced for period 2:

accounting originals

Note: This is not applicable if no transactions are exported from your previous system that are dated after your last reconciled VAT period.

To correctly import accounts and balances when using the Standard VAT scheme you must do the following:

  1. Reconcile and close your VAT period in your previous system
  2. Export the Trial Balance and outstanding (unpaid) debtors and creditors from your previous system, saving them each as CSV files
  3. Format the spreadsheets to correctly match Brightpearl's import routine
  4. Import the trial balance file to Brightpearl
  5. Import your debtors file to Brightpearl
  6. Import your creditors file to Brightpearl

1. Reconcile and close your VAT period in your previous system

We recommend you migrate over to Brightpearl at the end of a VAT period to ensure as much as possible is accounted for by the system.

2. Export the trial balance, debtors and creditors from your previous system

Please refer to your previous system instructions for exporting your trial balance, debtors and creditors details.

3. Format the spreadsheets to correct match Brightpearl's import routine

The files must be saved as CSV files in order for Brightpearl to read it. Each must also have specific columns.

The Trial Balance will need to have the following columns, and in this order:

  1. Nominal Code
  2. Name
  3. Debit
  4. Credit
  5. Is Bank Account (enter yes or leave blank)
  6. Is Expense Account (enter yes or leave blank)
  7. Tax Code (optional, e.g. T20)
  8. Map Code (optional. Use this field for the previous nominal code from your old system)
  9. Description (optional text description of the account)

Note: When you import the file you should remove the column headings (in red). The maximum number of lines you can import is 9999.

Required Codes

Brightpearl requires a certain minimum set of codes to operate. If your imported file does not include these accounts, then Brightpearl will add them for you. The following are the required codes:

  • 1001 Stock (asset) account
  • 1100 Debtors control account
  • 2100 Creditors control account
  • 2200 Sales tax account
  • 2201 Purchase tax account
  • 2202 VAT liability account
  • 3200 Retained earnings
  • 4000 Sales
  • 5000 Purchases
  • 9997 Opening balance (debtors)
  • 9998 Opening balance (creditors)
  • 9999 Suspense account

accounting originals

The Debtors List will need to have the following columns, and in this order:

  1. Customer code
  2. Company
  3. Contact name
  4. Telephone
  5. Type
  6. Date (YYYY-MM-DD)
  7. Inv.Ref
  8. Details
  9. Amount

accounting originals

The Creditors List will need to have the following columns, and in this order:

  1. Supplier code
  2. Company
  3. Contact name
  4. Telephone
  5. type
  6. Date (YYYY-MM-DD)
  7. Inv.Ref
  8. Details
  9. Amount

accounting originals

The customer/supplier code is the unique reference that should match the code as per your main Customer and Supplier imports.

If you choose to create Customers or Suppliers at the same time as importing the opening balances, then the details from the file will be used. If you add a unique code in your Debtors or Creditors files, then you can use this same code to update the contact information with more fields later using the Contact Import.

The date format MUST be YYYY-MM-DD.

Each open invoice must have a unique reference number for that contact (you can re-use the same invoice number for multiple customers, or for customers and suppliers - but we don't recommend it).

Don't include a currency symbol for the amount; and remember that all imports are in your base currency.

Transaction types

Use these in the "type" column to define where the transaction is placed in your system.

SI  - Sales Invoice

SC  - Sales Credit

SR  - Sales Receipt, also used for Payment on Account from customers

PI  - Purchase Invoice

PC  - Purchase Credit

PP  - Purchase Payment, also used for Payment on Account to suppliers

4. Import the Trial Balance

  1. Go to Settings > Data/Import > Import accounts .
  2. Click Import Chart of Accounts .
  3. Browse for the file to import.
  4. Tick the checkbox to specify that you want to delete and rebuild your chart of accounts and all existing accounting data.
  5. If you want to hide numeric codes throughout Brightpearl (and therefore select codes based on the name) you can tick the available checkbox; you can change this later at Settings > Company > Accounting: Nominal codes).
  6. Tick the available checkbox if you also want to create opening balance trial balance journals. If you leave this unchecked it will only create the nominal codes and no balances.
  7. Click the Upload Chart of Accounts (CSV) button.

When you import your trial balance a single figure is posted for your total accounts receivable (debtors) and another for your total accounts payable (creditors). These figures are the total amount owed, which therefore also includes tax amounts. The figures are not posted into the Debtor Control (1100) and Creditor Control (2100) accounts, as might be expected, because you then need to import your debtors and creditors details to separate the Net and Tax amounts. Instead these figures are posted to suspense accounts - Opening Debtor balance (9997) and Suspense Account (9998) . When you import your accounts receivable (debtors) and accounts payable (creditors) the balances in the suspense accounts are gradually split out into the Debtor/Creditor Control accounts and VAT accounts as well as attaching it to the relevant contact, applying the correct tax code in the process.

5. Import outstanding accounts receivable/debtors

This import will generate the accounting entries to attach the single debtor figure imported from the trial balance to each customer, ensuring your customer statements and customer debt report are correct. 

  1. Go to Settings > Data/Import > Import Accounts .
  2. Click Import accounts receivable/debtors .
  3. Browse for the file to import.
  4. Specify whether to add balances and invoices. If you say no you will only import the basic contact data.
  5. Select the date to use for open invoices, or whether to use the date in the file per item.
  6. Click the Upload CSV button.

When it comes to doing your next VAT Return these transactions will NOT be included and must be manually added onto the Brightpearl VAT return figure. You should run a VAT report in your old system.

6. Import outstanding accounts payable/creditors

This import will generate the accounting entries to attach the single debtor figure imported from the trial balance to each customer. This will ensure your supplier debt report is correct.

  1. Go to Settings > Data/Import > Import Accounts .
  2. Click Import accounts payable/creditors .
  3. Browse for the file to import.
  4. Specify whether to add balances and invoices. If you say no you will only import the basic contact data.
  5. Select the date to use for open invoices, or whether to use the date in the file per item.
  6. Click the Upload CSV button.

When it comes to doing your next VAT Return these transactions will NOT be included and must be manually added onto the Brightpearl VAT return figure. You should run a VAT report in your old system.

How to import a chart of accounts

You will need all your nominal codes listed in a CSV file using the following columns:

  1. Nominal Code (must follow Brightpearl nominal code structure)
  2. Name
  3. Debit (not required for building a chart of accounts)
  4. Credit  (not required for building a chart of accounts)
  5. Is Bank Account (enter yes or leave blank)
  6. Is Expense Account (enter yes or leave blank)
  7. Tax Code (optional, e.g. T20)
  8. Map Code (optional. Use this field for the previous nominal code from your old system)
  9. Description (optional text description of the account)

Note: When you import the file you should remove the column headings (in red). The maximum number of lines you can import is 9999.

accounting originals

  1. Go to Settings > Data/Import > Import accounts .
  2. Click Import Chart of Accounts .
  3. Browse for the file to import.
  4. Tick the checkbox to specify that you want to delete and rebuild your chart of accounts and all existing accounting data.
  5. If you want to hide numeric codes throughout Brightpearl (and therefore select codes based on the name) you can tick the available checkbox (you can change this later at Settings > Company > Accounting: Nominal codes).
  6. Tick the available checkbox if you also want to create opening balance trial balance journals. If you leave this unchecked it will only create the nominal codes and no balances.
  7. Click the  Upload Chart of Accounts (CSV)  button.
 
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.