This is great analysis and documentation, thank you Paul. I agree and propose that: 1) for consistency, we implement the change you suggest (i.e. manage accountingQuantity far all COGS methods, COGS_INV_COST and COGS_AVG_COST that are currently not covered) 2) we add the documentation you have provided to our Wiki
Regards, Jacopo On Sat, Jan 7, 2017 at 4:05 AM, Paul Foxworthy <p...@cohsoft.com.au> wrote: > Hi all, > > I believe the treatment of accounting quantity for inventory items is > flawed. I'd like anyone with knowledge of this to review the following and > see if I have misunderstood anything. If I do understand correctly, I'll > create some Jira tickets in due course. > > 1. Why accounting quantity? > ------------------------------------------ > > When inventory is received or shipped, there's an accounting transaction > that debits the inventory on hand value and credits Cost of Goods Sold > (COGS). > > There's more than one way of deciding what value is added to COGS. > > First and most obvious is the actual cost of the inventory item. But > companies may prefer other strategies. OFBiz has the option of three > others: average cost, first-in-first-out or last-in-first-out. There's a > nice survey of *why* you might choose one of these at > http://www.dummies.com/business/operations-management/choosing-an- > accounting-method-for-the-cost-of-goods-sold-expense/, > http://www.dummies.com/business/accounting/the-fifo- > method-for-cost-of-goods-sold/, http://www.dummies.com/ > business/accounting/the-lifo-method-for-cost-of-goods-sold/ > > Note that if you choose anything other than inventory item cost, the money > amount transferred to the COGS account may be *different* to the cost price > of the inventory items being shipped. When you choose FIFO or LIFO, the > amount may have originated from a different inventory item, received at a > different time. > > 2. Current situation in OFBiz > ------------------------------------------ > > The cogsMethodId field in the PartyAcctgPreference entity is a enum with > four possible values:COGS_INV_COST, COGS_AVG_COST, COGS_FIFO, COGS_LIFO.. > > The accountingQuantity field in the InventoryItem entity and > accountingQuantityDiff in the InventoryItemDetail entity track the quantity > of an item still "live" for the purpose of inventory valuation and COGS. > > In the service createAcctgTransForShipmentReceipt implemented > in /applications/accounting/minilang/ledger/GeneralLedgerServices.xml > (http://svn.apache.org/viewvc/ofbiz/trunk/applications/ > accounting/minilang/ledger/GeneralLedgerServices.xml?view=markup#l1306) > > the accountingQuantity is always set to the same value as the quantity > received (i.e. the same as the quantityOnHand) for a newly received > inventory item regardless of the COGS method. > > When items are shipped, the service > createAcctgTransForSalesShipmentIssuance will only reduce the accounting > quantity if the COGS method is FIFO or LIFO (http://svn.apache.org/viewvc/ > ofbiz/trunk/applications/accounting/minilang/ledger/ > GeneralLedgerServices.xml?view=markup#l1127). I can't find anywhere else > where the accounting quantity is reduced. > > With FIFO, when an item is shipped, inventory items for the product with a > non-zero accounting quantity are found sorted from earliest to latest > received. The quantity of the item shipped must be decremented from the > accounting quantities, starting with the earliest. Similarly, with LIFO, > items are sorted from latest to earliest, and the latest item or items are > decremented. > > In other words, if you have chosen a COGS method of COGS_INV_COST or > COGS_AVG_COST, the accounting quantity is meaningless and in OFBiz as of > now, should be ignored. > > And yet, the Inventory Valuation report uses accounting quantity, > regardless of the the COGS method (http://svn.apache.org/viewvc/ > ofbiz/trunk/applications/accounting/widget/ReportFinancialSummaryForms. > xml?view=markup#l535). In other words, the Inventory Valuation report is > broken for COGS methods of COGS_INV_COST or COGS_AVG_COST. > > 3. What should happen > ---------------------------------- > > I would argue that the Inventory Valuation report, and anybody else who > cares, should always be able to trust the accounting quantity. For > COGS_INV_COST and COGS_AVG_COST, maintaining the accounting quantity is > simple - it should just match the quantity on hand. I would argue it should > still be there, so the Inventory Valuation report works for COGS_INV_COST. > > No matter what the COGS method, the total accounting quantity should always > be equal to the total QOH, regardless of the COGS method. > > In addition, the Inventory Valuation report needs to be redesigned for the > average cost method. If you've chosen the average cost method, there is no > point in listing different inventory items. All inventory items will have > the same average cost, so I don't see the point in listing them > individually. Rather, lines in the report should just have the product and > the average cost. > > Jira tickets https://issues.apache.org/jira/browse/OFBIZ-5314 and > https://issues.apache.org/jira/browse/OFBIZ-5283 have some relevance to > this problem. > > Cheers > > Paul Foxworthy > > -- > Coherent Software Australia Pty Ltd > PO Box 2773 > Cheltenham Vic 3192 > Australia > > Phone: +61 3 9585 6788 <+61%203%209585%206788> > Web: http://www.coherentsoftware.com.au/ > Email: i...@coherentsoftware.com.au >
