Stock on hand reconciliation
Overview
At the end of each month, you'll need to reconcile the Stock on Hand value between Cin7 Omni and the Stock on Hand value on your Balance Sheet (in Xero). The ultimate goal is to ensure the Stock on Hand value between Cin7 Omni and Xero match. You will find documented below detailed steps on how to identify transactions that are a contributing factor to the discrepancy. This process will require you to use Excel (we recommend Google sheets). In addition, you'll need export reports from both Cin7 Omni and Xero.
Export Xero & Cin7 Omni transactions
In Xero, export the inventory stock values for the month into a google sheet or a spreadsheet.
Calculate the Net amount by deducting the credit value from the debit values.
Copy and paste the reference numbers and the net amounts to a new sheet.
For the reference numbers, use the 'Text to Columns' functionality in the Data tab to separate the value between the symbol '|'.
Trim the reference numbers, starting with 'PO-'.
Create a pivot table for the reference numbers (PO numbers) and the net amount.
Export the Purchase Orders from Cin7 Omni SOH Xero Reconciliation Report into google or a spreadsheet.
Identify transactions
-
Do a v-lookup between both the excel sheets to identify the following;
Potential variances with Purchase Order values.
Purchase Orders that exist in one system but not the other
Common causes for stock on hand discrepancies
Exchange Rate differences between the two systems. Make sure you are using the 'Final Payment NZD' functionality. (use this help article)
The latest COGS values were not imported to Xero from Cin7 Omni. Compare your COGs value in Xero to the COGS breakdown report. If it doesn’t match, reimport the COGs to Xero.
Modifications to transactions in Cin7 Omni were not re-imported to Xero.
Manual Journals were created directly into the Xero SOH account.
Transactions are voided in one system but not the other.