Smarter Opening Balances Guide - GB
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.
- Opening Trial Balance
- Open customer invoices and credit memos (Receivables/Debtors)
- Open vendor invoices and credit memos (Payables/Creditors)
- Open bank account balance (Bank)
- Open Inventory Quantity and Costs (Inventory)
- Open VAT Return (VAT)
- Fixed Assets Opening Balance (Fixed Assets)
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.
06 – VAT OB
Use this only if there is a VAT Return to be submitted from Business Central at opening balance date.
Get your outstanding VAT figures from legacy system and populate into Box 1 | Box 4 | Box 6 and Box 7 only.
Box 6 and Box 7 figures must be greater than Box 1 and Box 4 respectively.
Amount must be in LCY only
07 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.
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 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 open invoices and credit memo per customer in total matches the GL Account total.
Note: If Dimensions are enabled, 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.
Vendor Journal
The steps here are the same as Customer Journals. The only difference is the signage where minus (-) represents invoice and refunds instead.
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. Fill in Amount.
Optionally export to excel and follow steps above.
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.
Once there are no differences, create and post journals.
VAT Journal
Select VAT journal cue.
Only enable this page when you have open VAT return to submit from Business Central.
Once enabled, fill in the values as follows:
Box 1: This will be the VAT value found in Sales VAT account.
Box 4 - This will be the VAT value found in Purchase VAT account.
Box 6 - This will be the Net of VAT value for Sales
Box 7 - This will be the Net of VAT value for Purchases
Once there are no differences, create and post journals.
Smarter – G/L Open Balances
Once all subledger journals have been posted, Amount for the relevant GL Accounts will disappear and will appear in Balance,
For all other GL Accounts that are not related to subledgers, select Create and Post Journal
Once posted, amount will disappear and shown in Balance.
