Introduction

The following details the steps required to ensure you get the best experience out of our Smarter Opening Balances app.

Closing balances from previous systems become opening balances in Business Central.

Before starting, please make sure you have the following information ready to input or import:

1. Opening Trial Balance – Closing Trial Balance from Legacy System
2. Open and Historical customer invoices and credit memos (Receivables/Debtors)
3. Open and Historical vendor invoices and credit memos (Payables/Creditors)
4. Bank account balance (Bank) and Historical Transactions
5. Open Inventory Quantity and Costs (Inventory)
6. Open VAT Return (VAT)
7. Fixed Assets Opening Balance (Fixed Assets)
8. General Ledger Historical Transactions

Customers, vendors, Bank, Inventory, VAT and Fixed Assets have their own subledger as well as the total represented by their respective GL (nominal) accounts.

When these subledger transactions are posted, they will impact their respective control accounts.

Excel Template

There is an excel spreadsheet that accompanies this guide.

01-Trial Balance

Extract Trial Balance at closing balance date from existing system.

No: BC GL account. Smarter Opening Balance app will automatically populate the No from Chart of Accounts. Copy values from Edit in Excel spreadsheet and paste into this spreadsheet.

Name: BC GL Name. Smarter Opening Balance app will automatically populate the description from Chart of Accounts. Copy values from Edit in Excel spreadsheet and paste into this spreadsheet.

Debit: This is debit amount from legacy trial balance

Credit: This is credit amount from legacy trial balance

(Note: if Legacy trial balance only show 1 column for debit and credits, then fill in column F – OB Amount. Credit amount will be a minus (-) figure)

OB Amount Formula: Where Debit and Credit amount columns are filled in , OB Amount Formula will fill in a single figure here.

OB Amount: Copy and paste special from OB Amount formula or from legacy TB.

02-Customer OB

The following columns are shown in excel template worksheet.

This worksheet is for any outstanding invoices or credit notes with customers (debtors). Insert minus (-) sign for credit memos amount.

Customer Code: Business Central customer reference number code.

Document Type: This is a drop down list

Document No: This is a free text field up to a maximum of 20 characters. This can be user defined or from previous systems.

Posting Date: Transaction date posted in previous system.

Ext Document No: This is a free text field up to a maximum of 30 characters. This can be user defined or from previous systems. This is optional

Description: This is a free text field up to a maximum of 80 characters. This can be user defined or from previous systems.

Currency Code: Select from drop down list for foreign currency. For local currency (example GBP), leave blank. If relevant currency codes are not found on this list, please reach out to your Microsoft Partner for further assistance.

Amount: This is the amount for the transaction in currency selected above.

Amount LCY: This is the amount in local currency. Where Amount column is in foreign currency, then fill in the local currency equivalent amount. Otherwise, where Amount column is in local currency, then fill in with the same amount.

Global Dimension 1: This is optional and to be filled in accordingly if Global Dimension 1 is setup and defined with Dimension values

Global Dimension 2: This is optional and to be filled in accordingly if Global Dimension 2 is setup and defined with Dimension values

Shortcut Dimensions: These are optional and to be filled in accordingly if Shortcut Dimension(s) are setup and defined with Dimension values. Add in more Shortcut Dimensions columns if there are more than what has been provided on spreadsheet.

03-Vendor OB

The following columns are shown in excel template worksheet.

This worksheet is for any outstanding invoices or credit notes with suppliers (creditors). Insert minus (-) sign for invoice amount.

Vendor Code: Business Central vendor reference number code.

Document Type: This is a drop down list

Document No: This is a free text field up to a maximum of 20 characters. This can be user defined or from previous systems.

Posting Date: Transaction date posted in previous system.

Ext Document No: This is a free text field up to a maximum of 30 characters. This can be user defined or from previous systems.

Description: This is a free text field up to a maximum of 80 characters. This can be user defined or from previous systems.

Currency Code: Select from drop down list for foreign currency. For local currency (example GBP), leave blank. If relevant currency codes are not found on this list, please reach out to your Microsoft Partner for further assistance.

Amount: This is the amount for the transaction in currency selected above.

Amount LCY: This is the amount in local currency. Where Amount column is in foreign currency, then fill in the local currency equivalent amount. Otherwise, where Amount column is in local currency, then fill in with the same amount.

Global Dimension 1: This is optional and to be filled in accordingly if Global Dimension 1 is setup and defined with Dimension values

Global Dimension 2: This is optional and to be filled in accordingly if Global Dimension 2 is setup and defined with Dimension values

Shortcut Dimensions: These are optional and to be filled in accordingly if Shortcut Dimension(s) are setup and defined with Dimension values. Add in more Shortcut Dimensions columns if there are more than what has been provided on spreadsheet.

04-Item OB

The following columns are shown in excel template worksheet.

This worksheet is for inventory count as at opening balance date.

Item No: Business Central item reference number code. Smarter Opening Balance app will automatically populate the No from Item. Copy values from Edit in Excel spreadsheet and paste into this spreadsheet.

Item Description: Self explanatory. Smarter Opening Balance app will automatically populate the description from Item. Copy values from Edit in Excel spreadsheet and paste into this spreadsheet.

Unit of Measure Code: Self explanatory. Smarter Opening Balance app will automatically populate the unit of measure from Item. Copy values from Edit in Excel spreadsheet and paste into this spreadsheet.

Quantity: Item quantity at opening balance date

Unit Cost: Self Explanatory. Up to maximum of 5 decimal places.

Locations: Must be set up as Location Code in Business Central first.

Bins: Must be set up as Bin Code against Location in Business Central first.

Global Dimension 1: This is optional and to be filled in accordingly if Global Dimension 1 is setup and defined with Dimension values

Global Dimension 2: This is optional and to be filled in accordingly if Global Dimension 2 is setup and defined with Dimension values

Shortcut Dimensions: These are optional and to be filled in accordingly if Shortcut Dimension(s) are setup and defined with Dimension values. Add in more Shortcut Dimensions columns if there are more than what has been provided on spreadsheet.

05-Bank OB

Bank Code: Business Central bank reference number code. Smarter Opening Balance app will automatically populate the Bank Code from Bank Accounts. Copy values from Edit in Excel spreadsheet and paste into this spreadsheet.

Bank Name: Self explanatory. Smarter Opening Balance app will automatically populate the Bank Name from Bank Accounts. Copy values from Edit in Excel spreadsheet and paste into this spreadsheet

Currency Code: Select from drop down list for foreign currency. For local currency (example GBP), leave blank. If relevant currency codes are not found on this list, please reach out to your Microsoft Partner for further assistance.

Amount: This is the amount for the transaction in currency selected above.

Amount LCY: This is the amount in local currency. Where Amount column is in foreign currency, then fill in the local currency equivalent amount. Otherwise, where Amount column is in local currency, then fill in with the same amount.

05A-Bank Transactions

This worksheet is for any historical or unreconciled transactions per bank account. Insert minus (-) sign for credit amount.

Posting Date: Transaction date posted in previous system.

Document Type: This is Optional and can be left blank. Alternatively, fill in Payment or Refund.

Document No: This is a free text field up to a maximum of 20 characters. This can be user defined or from previous systems.

Ext Document No: This is a free text field up to a maximum of 30 characters. This can be user defined or from previous systems.

Description: This is a free text field up to a maximum of 80 characters. This can be user defined or from previous systems.

Currency Code: Select from drop down list for foreign currency. For local currency (example GBP), leave blank. If relevant currency codes are not found on this list, please reach out to your Microsoft Partner for further assistance.

Amount: This is the amount for the transaction in currency selected above.

Amount LCY: This is the amount in local currency. Where Amount column is in foreign currency, then fill in the local currency equivalent amount. Otherwise, where Amount column is in local currency, then fill in with the same amount.

06 Fixed Assets OB

FA No: Business Central fixed asset reference number code. Smarter Opening Balance app will automatically populate the Fixed Asset No from Fixed Assets. Copy values from Edit in Excel spreadsheet and paste into this spreadsheet

FA Name: Self explanatory. Smarter Opening Balance app will automatically populate the Fixed Asset Name from Fixed Assets. Copy values from Edit in Excel spreadsheet and paste into this spreadsheet

Acquisition Cost Date: This is a date field for when the fixed asset was first acquired. Note that the date format must be YYYY/MM/DD when using Edit in Excel.

Acquisition Cost Amount: LCY Amount for when the fixed asset was first acquired

Depreciation Posting Date: This is a date field for when the fixed asset was first depreciated. Note that the date format must be YYYY/MM/DD when using Edit in Excel

Accumulated Depreciation Amount: LCY Amount from legacy system for the fixed asset

Salvage (Residual) Value: LCY Amount for residual value – only for straight line depreciation method. This is an optional field

Net Book Value: This is to be left blank as Smarter Opening Balance app will automatically calculate the value.

08 GL Transactions

This worksheet is for any historical transactions for GL account(s).

Insert minus (-) sign for credit amount.

Entry No: This is a running number from 1. Simply drag down to last row and this will autopopulate running numbers. This is to be used when using Edit in Excel mode.

Posting Date: Transaction date posted in previous system. Note that date format must be in YYYY/MM/DD when using Edit in Excel.

Account No: This is the GL Account number from Business Central

Document Type: This is Optional and can be left blank. Alternatively, fill in from drop down list.

Document No: This is a free text field up to a maximum of 20 characters. This can be user defined or from previous systems.

Ext Document No: This is a free text field up to a maximum of 30 characters. This can be user defined or from previous systems.

Description: This is a free text field up to a maximum of 80 characters. This can be user defined or from previous systems.

Currency Code: Select from drop down list for foreign currency. For local currency (example GBP), leave blank. If relevant currency codes are not found on this list, please reach out to your Microsoft Partner for further assistance.

Amount: This is the amount for the transaction in currency selected above.

Amount LCY: This is the amount in local currency. Where Amount column is in foreign currency, then fill in the local currency equivalent amount. Otherwise, where Amount column is in local currency, then fill in with the same amount.

Global Dimension 1: This is optional and to be filled in accordingly if Global Dimension 1 is setup and defined with Dimension values

Global Dimension 2: This is optional and to be filled in accordingly if Global Dimension 2 is setup and defined with Dimension values

Shortcut Dimensions: These are optional and to be filled in accordingly if Shortcut Dimension(s) are setup and defined with Dimension values. Add in more Shortcut Dimensions columns if there are more than what has been provided on spreadsheet.

Business Central

Smarter Opening Balances

This section guides through the use of Smarter Opening Balances app in Business Central.

Make sure you are in the correct company in Business Central.

Search and select Smarter Opening Balances

App Registration

Register the App. Click on Smarter App Registration and the following page will pop up: Fill in the License Key and select App Registration. This will change to show Application is Registered.

Basic Setup

Once Registered, select Basic Setup

A new page will open

Opening Balancing Account: When subledger balances are brought in, the balancing side is posted to this account. Create a new balancing Balance Sheet GL account.

Transaction Date for opening balances: This is the opening balance date.

Note: This is not the Financial/Fiscal start date.

Run Validation

On the factbox, click on Run Data Validation.

Message should show No errors found. If there are error or warning messages, please fix any errors and heed warnings. Please get in touch with admin@smarterd365.co.uk if further assistance is required.

Once Validated, this will show:

General

Recommendation

We recommend using Smarter Opening Balances app in a sandbox environment before using in production environment.

It is best practice to review data and reconcile GL accounts totals against legacy (previous) systems trial balance before deploying to a production environment.

Cue Colour

Cues will show the following colours:

Red = Differences between subledger journals and GL Accounts

Amber = No differences found. Journals not posted

Green = Journals posted

Conditions

Once a cue is posted, there is no longer the opportunity to post again. Either leave all postings to last and only post when customer has confirmed or create a new company and go through Smarter Opening Balances steps again.

Posted Cues

When a cue that is green is selected, this will open the posted entries for review.

Opening Balance from Legacy Systems

Using the front page, start filling in the Amount column that has been extracted from your legacy system Closing Balance Trial Balance.

Optionally, export to excel

Select icon and select Edit in Excel.

This will download to Excel. Open the file

Select Enable Editing

Login to the Data Connector on the right hand pane.

This will auto populate with data from Business Central

Fill in the Amount column – column H (TB Closing Balance from Legacy system). Note: Credit amount is represented by a minus (-). Click on Publish when all Amounts have been filled in. This will import into Business Central

Return back to Business Central. Select Functions – Refresh List and this will update with published figures from Excel.

Subledger Balances

At the bottom of the page, there are cues for uploading/inputting subledger opening balances.

Customer Journal

Select Customer Journal cue and this will pop out a new page:

Start inputting each customer historical and open invoices, credit memos, payments or refunds from legacy systems.

Note: Minus (-) value represents credit memos and payments.

Fill in the document type from the drop down list. This is optional and can be left blank.

Once all lines are filled in, the system will check the total balance here to the GL Account.

Example: In this example, The total in this page does not match the total balance against the GL Account.

Make sure all invoices and credit memo per customer in total matches the GL Account total.

Note: If Dimensions are enabled, the Dimensions will appear and Dimension values can be utilised here.

Optionally – you can check totals by customer by selecting Total per Customer

Once there are no differences, select Create and Post Journal

Optionally export to excel and follow steps above.

Note: Any transactions that require applications (matching) because they are no longer open will require manual retrospective application through Customer Ledger Entries table.

Vendor Journal

The steps here are the same as Customer Journals. The only difference is the signage where minus (-) represents invoice and refunds instead.

Note: Any transactions that require applications (matching) because they are no longer open will require manual retrospective application through Vendor Ledger Entries table.

Item Journal

Select Item journal cue and click on Edit list.

The list here shows all items created.

Start filling in (optionally export to excel) items. Fill in appropriate locations and/or bins where these have been enabled and set up.

Once there are no differences, create and post journals.

Bank Journal

Select Bank journal cue and click on Edit list.

Highlight Bank Account and select 0.00 under Amount column. This will show a new page:

Note: if the bank account is in a foreign currency, fill in Amount in foreign currency and amend the Amount (LCY) figure to its corresponding local currency amount that is from your legacy system.

Fill in (alternatively, use Edit in Excel) and copy and paste from 05A -Bank Transactions Worksheet

Start inputting each bank transaction payments or refunds from legacy systems.

Note: Minus (-) value represents credit.

Fill in the document type from the drop down list. This is optional and can be left blank.

Once all historical transactions are filled in per bank account, return to main Bank Opening Balances page and hit Refresh Page. This will now show each bank account totals from transactions uploaded.

Once there are no differences, create and post journals.

To close all reconciled posted bank ledger entries, on the main page, select Bank Completed cue and this will pop out a message. Select Navigate to Bank Ledger Entries

Simply untick any transactions that are already reconciled from legacy systems.

This will mean that when doing your first bank reconciliation in BC, you will only get unreconciled transactions to reconcile once bank statement is received.

Smarter – Subledger Balances

Once all subledger journals have been posted, Amount for the relevant GL Accounts will disappear and will appear in Balance on Smarter Opening Balances main page.

Smarter – G/L Open Balances

The following details walk through steps on how to bring in historical transactions for GL Accounts that are not related to subledgers.

Select Functions – Batch load via Excel

This opens a new page where you can either fill in manually or Edit in Excel – copy and paste from 08-GL Transactions Worksheet

Start inputting each historical transaction from legacy systems by GL Accounts

Note: Minus (-) value represents credit.

Fill in the document type from the drop down list. This is optional and can be left blank.

Once all lines are filled in, the system will check the total balance here to each GL Account and show in Amount Analysis.

Scroll down and make sure all Amounts equal to Amount Analysis. Resolve any difference before posting

Note: Where there are GL accounts that do NOT require transactions, there is no need to fill in transactions for these GL Accounts in 08-GL Transactions Worksheet

When ready to post GL transactions, select Create and Post Journal

Once posted, amount will disappear and shown in Balance.