AccountingQuantity, COGS method and inventory valuation

[Paul Foxworthy]

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: [phone] <+61%203%209585%206788>
Web: http://www.coherentsoftware.com.au/
Email: [hidden email]

[taher]

Hi Paul,

Very detailed analysis. I learned a few things from that so thank you!

So your bottom line conclusion aside from changing the service design is to
have matching figures between accountingQuantity and QOH. And I think the
way you want to achieve this is by changing the implementation in
createAcctgTransForShipmentReceipt right?

I'm not an expert in this area but your proposition sounds reasonable both
in the accountingQuantity field and changing the report design to aggregate
on average value.

Thank you again for this info!

Cheers,

Taher Alkhateeb

On Sat, Jan 7, 2017 at 6:05 AM, Paul Foxworthy <[hidden email]> 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: [phone] <+61%203%209585%206788>
> Web: http://www.coherentsoftware.com.au/
> Email: [hidden email]
>

[Paul Foxworthy]

Hi Taher,

On 7 January 2017 at 18:06, Taher Alkhateeb <[hidden email]>
wrote:


> So your bottom line conclusion aside from changing the service design is to
> have matching figures between accountingQuantity and QOH. And I think the
> way you want to achieve this is by changing the implementation in
> createAcctgTransForShipmentReceipt right?
>

Yes, when the COGS method is COGS_INV_COST or COGS_AVG_COST. That service
is already doing the right thing for COGS methods of FIFO and LIFO.

I think for a given product, the total quantity on hand and total
accounting quantity should always be the same. That should be an invariant.
The different COGS methods just vary the distribution of the accounting
quantities across different inventory items for that product.

Cheers

Paul Foxworhy

--
Coherent Software Australia Pty Ltd
PO Box 2773
Cheltenham Vic 3192
Australia

Phone: [phone]
Web: http://www.coherentsoftware.com.au/
Email: [hidden email]

[Jacopo Cappellato-5]

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 <[hidden email]> 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: [phone] <+61%203%209585%206788>
> Web: http://www.coherentsoftware.com.au/
> Email: [hidden email]
>