• Contents

Stock on Hand Reconciliation

Last updated 12/01/2021

How to reconcile your Stock On Hand value within Cin7 to the Stock On Hand Balance Sheet value in Xero.

Overview


At the end of each month, you'll need to reconcile the Stock on Hand value between Cin7 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 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 and Xero. 

 

Exporting Xero & Cin7 Transactions


  1. In Xero, export the inventory stock values for the month into a google sheet or a spreadsheet.
  2. Calculate the Net amount by deducting the credit value from the debit values.
  3. Copy and paste the reference numbers and the net amounts to a new sheet.
  4. For the reference numbers, use the 'Text to Columns' functionality in the Data tab to separate the value between the symbol '|'.
  5. Trim the reference numbers, starting with 'PO-'.
  6. Create a pivot table for the reference numbers (PO numbers) and the net amount.
  7. Export the Purchase Orders from Cin7 SOH Xero Reconciliation Report into google or a spreadsheet.

Identifying Transactions


 

  1. 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.  Compare your COGs value in Xero to the 'Xero QBO COGs breakdown report'. If it doesn’t match, reimport the COGs to Xero.
  • Modifications to transactions in Cin7 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.