Hi team, I am working on a custom COGS report for a client. I have created a report using the formula:
Opening Value + Purchase Value – Closing Value = COGS
When I take the values from the system and compare them to the Balance Sheet:
If I match the Closing Value with the Balance Sheet, the COGS does not match.
If I adjust to match COGS, the Closing Value does not match.
I need guidance on what I might be doing wrong and how to correctly calculate item-wise COGS in ERPNext so that both COGS and Closing Value reconcile.
Steps to Replicate the issue:
Create a custom COGS report in ERPNext.
Pull Opening Value and Purchase Value from Stock Ledger Entry.
Compare Closing Value with Balance Sheet Closing Stock.
Observe mismatch between Closing Value and COGS when trying to reconcile. Current behavior:
Closing Value matches Balance Sheet only when COGS calculation is off, and vice versa.
Expected behavior:
Both Closing Value and COGS should match with the Balance Sheet and P&L reports when using the custom report.
You assumptions are little complicated for a Perpetual System. The formula which you have mentioned above is suitable for Periodic Inventory.
For Perpetual inventory you can just simply consider COGS=∑(Qty Sold×Cost per Unit at Time of Sale)
Where Cost Per Unit at Time of Sale = Purchase + Landed Cost + Manufacturing Cost (Consider FIFO, LIFO, MA too)
Since Perpetual is a real time system you can’t wait for Closing stock
However, if I want to cross-check the COGS from a Periodic Inventory perspective, which values should I consider so that I can verify whether the perpetual inventory calculation is accurate?
Already Included Direct Expenses In the Purchase Value.FYI purchase value is calculated from Stock Ledger Entry ==> sum of Stock value difference field when voucher type is Purchase Receipt.
In simple terms Closing stock in Perpetual inventory is calculated on real time basis when a good is Sold through DN/SI. at this stage COGS is booked and Closing balance changes (Called Stock in Hand as per perpetual system). Also COGS is part of P/L and SIH is part of B/S. In ideal scenario COGS will never be equal to SIH or Closing Stock. unless buying and selling pattern are same which is very rare. So close your books in Periodic Inventory as of the same date when the sales was booked in Perpetual Inventory you will get your answer.
