Follow these steps to determine the cause of the variance and enable you take remedial action and ensure Inventory balances:
- Ensure Inventory has been closed for the period you are reconciling
- Obtain Trial Balance figures for inventory: General Ledger - Reports - Transactions - Periodic - Trial Balance
- Review the posting types by account to determine the transaction types posted to the inventory ledgers: General Ledger - Reports - Reconciliation - Inventory - Item Posting by Account
- Run the ledger transactions list to determine postings not captured by the Item Posting by Account report: General Ledger - Reports - Transactions - Ledger Transaction list
- Download this report to Excel and auto-filter by posting to ensure that each posting type is captured
- Postings not captured could include manual journals, service transactions, blank posting type (probably a bug)
- Obtain the inventory sub-ledger figures from the inventory value by item group report. Tie the financial value of each item group to the Trial Balance: Inventory Management - Reports - Status - Inventory Value - Inventory Value by item group
- Research the variances
- Take remedial actions on a case by case basis
- If you identify manual journal entries into the inventory account, create a reversing journal entry to correct this. In addition, ensure controls around posting to the Inventory account are in place such as using the "locked in journal" functionality in the chart of accounts or setting up validations for posting type also in the chart of accounts.
- If you identify service transactions in ledger, then the service item is not set up appropriately. You need to finalize all transactions with the service item, create a new service item that is set up with the right item type, inventory model group, item group and dimension group and create a journal entry to reverse the service item posting.
- Maintain back up of your work for reference to your auditors.
- You can also run the Inventory Reconciliation Report to assist in your analysis: General Ledger - Reports - Reconciliation - Inventory - Inventory. Click show differences to determine reasons for variance
I have an Excel template which I have designed to properly capture all the details of the inventory reconciliation and would be glad to provide that. Feel free to contact me.
Good luck as you reconcile inventory and let me know if this post has been useful to you!
Could you please send me the excel template to santu.ghosh@in.pwc.com
ReplyDeleteplease send me the templete at saad075@hotmail.com
ReplyDeletePlease can you sedn me the template on sreekanthxlnc@hotmail.com
ReplyDeleteappreciate if you could send it to anis@lshholding.com
ReplyDeletecan you please send me that excel file at deepankar.sahdev@gmail.com...thanks
ReplyDeletePlease send me your template at robert.mohammed@ansamcal.com
ReplyDeleteRegards,
Robert M.
Great job!!i would greatly appreciate it if you could send me your template at krib4u@yahoo.com.
ReplyDeleteThanks a lot
Could you please send the template? rraburn@microbeef.com
ReplyDeleteplease send the tempalate to nsarswat@gmail.com
ReplyDeletePlease send template to dmc321@gmail.com
ReplyDeleteExcellent! Could you please send me the inventory reconciliation template to sriram.coolram@gmail.com? It would be of great help!
ReplyDeleteRegards,
Sriram
We have issues with reconciliation as well. Could you please send me a copy of your Excel template to fneijts@gmail.com
ReplyDeleteThank you,
Frank
Please send Excel template at alijawadk@gmail.com.
ReplyDeleteThank You
Ali
Good knowledge base.Can you please send me excel template at munim27@gmail.com
ReplyDeletePlease send me a copy of your Excel template at kmeinke@sparton.com.
ReplyDeleteThank you,
Kelly
Please send me a copy of your Excel template. Thanks,
ReplyDeletecteska@artisansinc.com
This comment has been removed by the author.
ReplyDeleteThis comment has been removed by the author.
ReplyDeletePlease, send the reconcilation template to me udhayanand.avb@gmail.com
ReplyDeletePlease send the excel template.
ReplyDeletesanjaybajaj9831@gmail.com
jakpayen@aol.com
ReplyDeleteThanks
Hi Joan,
ReplyDeleteThanks for sharing very informative data. I request you to kindly send that excel template for inventory reconciliation.
In this regards your cooperation will be highly appreciable.
Once again thanks in advance for sharing information my email id is : yusufamin25@gmail.com
Thanks
Hi,
ReplyDeleteWe face the same problem between Inventory and Trial Balance, I wish you can help me with dropping the Template to my mail @ jun251111@gmail.com
Rgds.
Hi,
ReplyDeleteWe are facing same issue, we are not able to match Inventory with GL. Kindly send excel template.
Thank you very much and much appreciated.
Regards,
Hi,
ReplyDeleteCould you please me the Excel file ?
mihaicym@gmail.com
Many thanks !
Please kindly send the excel template to ed_ban@yahoo.com
ReplyDeleteCould you please send me the Excel template to 263387c@gmail.com? Thank you!
ReplyDeletePlease kindly share the excel template to sam.sanjiv@outlook.com may thanks
ReplyDeleteHi Joan,
ReplyDeleteThe 13th point you have mentioned, as per it the Gl and Inventory balance is not matching. Its showing a discrepancy of -1941.
Can you please suggest me to solve this issue.
email : amohammed@aldrees.com
Hi,
ReplyDeleteCould u please send me the excel template?
Email : djie,wu@gmail.com
and thank you for the information here!
Hi Joan,
ReplyDeleteThanks for sharing very informative data. I'll appreciate kindly if you send that excel template for inventory reconciliation. My email address is psantana@rocksolid.com
Hi Joan ,
ReplyDeleteCan you please send me the Excel template? mihaicym@gmail.com
Thanks !
can you please send me that excel file at juntakmail@gmail.com ,,,thanks
ReplyDeletecan you please send me the excel template. mekevy@gmail.com....thank you!
ReplyDeletePlease send the template dbj000777@rediffmail.com
ReplyDeleteHi Joan, please could you send me the template at shyam.jandhyala@gmail.com
ReplyDeletecan you send the template prasadskln@gmail.com
ReplyDeleteCould you please send me the template to jeff.dembosky@gmail.com? Thank you!
ReplyDeleteplease send to mks81@live.co.uk
ReplyDeletePlease help me with your template my mail: pmarthap@hotmail.com
ReplyDeleteI did not find the reports is step 3 & 13:
ReplyDelete1) General Ledger - Reports - Reconciliation - Inventory - Item Posting by Account
2) General Ledger - Reports - Reconciliation - Inventory - Inventory
Please send me the Excel template bamarzouk@gmail.com