Inventory Opening Balances Template
Before you start
Understanding the Configuration Worksheet: Configuration Worksheet.
Understanding Excel templates: Excel Template Data Migration.
You have prepared your data as per Bevica Select - Finance Data Migration Overview.
Understanding how Sales Prices work. See Sales Prices and Discounting - New Pricing Experience.
Item must exist.
Inventory Posting Setup must be setup.
Paid Reserves are imported using a different process.
Prepare your data
Preparation of the data to be migrated is key. In addition to the data preparation list in Bevica Select - Finance Data Migration Overview, you need to:
Have agreed internally how to establish a cost for each line stock line
Have agreed internally how to establish the correct quantity for each line being receipted
You will need to establish an inventory cost and quantity per:
Item / Location / Variant (Duty Status) / Unit of Measure (& optionally Rotation No. )
General posting setup
There is a special data migration General Ledger Account called Data Migration - Inventory Suspense (G/L Account No. ZZ9950).
When posting the Item Journal, the Balance Sheet 'stock' account will come from the Inventory Posting Setup. The Income Statement balancing entry will come from the General Posting Setup using a blank General Business Posting Group and the General Product Posting Group from the Item(s). For posting this migration journal the Inventory Adjustment Account for this combination should be temporarily changed to the suspense G/L account ZZ9950.
When you bring over your opening balances for the General Ledger later, then you will migrate your 'stock' value into this suspense account which should net to 0. See Bevica Select - Trial Balance Opening Balances Template
Remember to switch the Inventory Adjustment Account back to 7170 once the opening inventory has been posted and reconciled.
Inventory open items template
The template is a pre-formatted spreadsheet with the fields you need to create your opening inventory balances within Bevica.
The template has the following fields:
1 Journal Template Name
(82) Item Journal Template
Code 20 (Mandatory)
Set to ITEM. Any value entered here must already exist in its related table.
41 Journal Batch Name
(233) Item Journal Batch
Code 20 (Mandatory)
Set to Z_DATA MIG. Any value entered here must already exist in its related table.
2 Line No.
Integer (Mandatory)
See note at end.
5 Entry Type
Option (Mandatory)
Set to Positive Adjmt. Note that the full stop needs to be included.
6 Document No.
Code 20 (Mandatory)
See note at end.
3 Item No.
(27) Item
Code 20 (Mandatory)
Any value entered here must already exist in its related table.
4 Posting Date
Date (Mandatory)
Enter a date here, typically the day before the go-live date.
9 Location Code
(14) Location
Code 10 (Mandatory)
Any value entered here must already exist in its related table.
5402 Variant Code
(5401) Item Variant
Code 10 (Mandatory)
See note at end.
13 Quantity
Integer (Mandatory)
Enter a quantity (no decimal places) in the unit of measure.
5407 Unit of Measure Code
(5404) Item Unit of Measure
Code 10 (Mandatory)
Relates to the quantity field above. Any value entered here must already exist in its related table.
16 Unit Amount
Decimal (Mandatory)
See note at end.
60 Document Date
Date
Enter a date here, typically the day before the go-live date.
70560 TVT Rotation No.
Code 50
Enter a Rotation No. / Lot No. reference if necessary.
70562 TVT Rotation Ref.
Code 50
TVT Rotation No. is mandatory if a value is entered here.
Line No. Must be a unique number per line. Line numbers normally start with 10000 and increment by 10000.
Document No. Enter a reference that means something to your company. This may be used later to reconcile this line to a legacy system.
Variant Code For duty controlled items enter either DUTY FREE or DUTY PAID, otherwise can be left blank. Any value entered here must already exist in its related table.
Unit Amount Enter the cost for one unit of the unit of measure. e.g. £10.00 / 75cl or £60.00 / 6x75cl. This is in the base currency of your Bevica company, not the currency you bought the stock in.
Upload the journal
Once the template has been applied, navigate to the Item Journal 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.
Have all the lines been imported?
Select a number of lines at random, are they all correct and Line Amount calculated correctly.
Export the journal lines to Excel and make sure the total quantities and cost amounts are what you expect.
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 Item 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 and Bevica. The following should be checked:
Quantity by Item (by Location, Variant, Unit of Measure)
Value by Item (by Location, Variant, Unit of Measure)
General Ledger 'Stock' balance on ZZ9950 and 2120.
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.
Last updated
Was this helpful?