Sales Ledger Opening Balances Template

Before you start

• Understanding the Configuration Worksheet: Configuration Worksheet.
• Understanding Excel templates: Excel Template Data Migration.
• Understanding how financial opening balance imports work: Bevica Select - Finance Data Migration Overview.
• Customers must exist.

Create a manual journal

If you would prefer to create the opening sales ledger balance journal manually rather than importing a template, navigate to the General Journal and open the ZZ-DM journal batch. Populate the columns listed below except for:

• Journal Template Name
• Journal Batch Name
• Line No.

Template

The template is a pre-formatted spreadsheet with the fields you need to create your opening sales ledger transactions within Bevica.

You will need a list of open sales ledger transactions from your old finance system. Only bring across open transactions, e.g., invoices, payments, credits, etc., that have not yet been matched off. If an invoice has been part paid, you need to bring across the remaining amount, not the original amount.

The total of the open transaction amounts must equal the sales ledger control account in your old system.

The template has the following fields:

Field ID/ Field Name	Related Table	FieldType	Note
1 Journal Template Name	(82) Gen. Journal Template	Code 20 (Mandatory)	Enter GENERAL
51 Journal Batch Name	(232) Gen. Journal Batch	Code 20 (Mandatory)	Enter ZZ-DM
2 Line No.		Integer (Mandatory)	Must be a unique number per line. Line numbers normally start with 10000 and increment by 10000.
5 Posting Date		Date (Mandatory)	Use either the original transaction Posting Date or set all Posting Dates to the day before go-live. When validating your data migration, run an aged debt report and customer statements to ensure the migrated data appears correctly.
76 Document Date		Date (Mandatory)	Either use the Document Date of the original transaction. The Due Date is calculated based on the Document Date and the customer's payment terms.
6 Document Type		Option (Mandatory)	Invoice; Credit Memo; Payment; Refund
7 Document No.		Code 20 (Mandatory)	The transaction reference, e.g., invoice number.
77 External Document No.		Code 35 (Optional)	Populate this if you have another reference to bring across.
3 Account Type		Option (Mandatory)	Enter Customer
4 Account No.	(18) Customer	Code 20 (Mandatory)	The customer number.
8 Description		Text 100 (Mandatory)	Enter a relevant description.
12 Currency Code	(4) Currency	Code 10 (Optional)	Only populate this field if the transaction is in a foreign currency, e.g. EUR, USD.
13 Amount		Decimal (Mandatory)	Enter the outstanding amount of the transaction. Must be a debit amount (positive) for invoices and refunds, and a credit amount (negative) for credit memos and payments. If it is a foreign currency transaction this is the amount in the foreign currency.
14 Amount (LCY)		Decimal (Optional)	Leave blank if the transaction is in local currency. Populate it with the local currency amount if the transaction is in foreign currency. The system will then work out the exchange rate based on the 'Amount' and 'Amount (LCY)' values.
11 Bal. Account Type		Option (Mandatory)	Enter G/L Account
63 Bal. Account No.	(15) G/L Account	Code 20 (Mandatory)	Enter ZZ9910
38 Due Date		Date (Optional)	The Due Date is calculated based on the Document Date and the customer's payment terms. This may be different from the due date on the transaction in your old system therefore you may prefer to enter the Due Date and not use the defaulted date.
Dimensions		(Optional)	Enter the relevant dimensions. Any value entered here must already exist in its related table.

Upload the journal

Once the template has been applied, navigate to the General Journal ZZ-DM batch and check that the journal holds the same information as the data migration template used.

• Ensure all columns you have imported data into are visible (with the exception of Journal Template Name, Journal Batch Name, and Line No.).
• Have all the lines been imported?
• Select a number of lines at random, including some foreign currency ones if you have any. Are they all correct and are the Amount and Amount (LCY) values both correct?
• Export the journal lines to Excel and make sure the details look correct, including dates and values.
• Select Process then Reconcile. Check the Net Change in Jnl. value against account ZZ9910. This should match the total of your sales ledger control account in your old system. If this doesn't match, but the total of the file you uploaded does match, then something isn't correct with the journal lines. Try checking lines one by one to see where the issue lies, especially foreign currency lines.
• Select Post/Print then Preview Posting. Check the entries that will be posted look correct.

Post the journal

Before posting any opening balances, it is a good idea to copy the company as a backup, so you have a point to return to if there are problems once the General Journal has been posted. See Bevica Select - Creating new Bevica companies for information on how to do this.

Reconcile

Once posted, the entries need to be reconciled with the values from your old system. The following should be checked:

• Balance on customer accounts. Check that the figures on your old system and Bevica match. On the Customer List page, you will see the Balance (LCY) field which will be the total for that customer in your local currency.
• Run the Aged Accounts Receivable report and check that the totals and ageing columns are correct. Note these will only match your old system if you have brought across the same due dates.
• Check the General Ledger 'Sales Ledger' balance on ZZ9910 and 2310.

Another trusted user, rather than the person who created and posted the opening balance, should perform these checks. They should do this using their own data source from your previous system and not rely on the migration files you used in case there were errors on those.