Topic: Update Material Cost Function

I have a suggestion that the function update_material_cost in manufacturing/includes/db/work_order_costing_db.inc shall be optimized or replaced with the function update_average_material_cost in purchasing/includes/db/grn_db.inc.

The former function is not calculating correct average cost with the logic defined at Line # 107

if ($qoh == 0 && $avg_cost != 0)
            $avg_cost = ($avg_cost + ($unit_cost*$qty_new)/$qty)/2;
www.boxygen.pk

Re: Update Material Cost Function

How wrong it was $boxygen ?
I'm the one who created these lines

Phuong

Re: Update Material Cost Function

@notrinos

yes I just checked the history of this file. I found that earlier it was

if ($qty > 0 && ($qoh != -$qty))
        $avg_cost = ($avg_cost*($qoh+$qty_delayed)+$unit_cost*$qty_new)/($qoh+$qty);

And I think this was perfect because this was taking the Weighted Average of the Item Produced

Later this was changed to

if ($qty > 0 && ($qoh != -$qty)) {
        if ($qoh == 0 && $avg_cost != 0)
            $avg_cost = ($avg_cost + ($unit_cost*$qty_new)/$qty)/2;
        else
            $avg_cost = ($avg_cost*($qoh+$qty_delayed)+$unit_cost*$qty_new)/($qoh+$qty);
    }

Here I am unable to understand that why we need to take Simple Average for condition

if ($qoh == 0 && $avg_cost != 0)

Assume following transactions
1. Item A was produced qty=300 with cost allocated after WO process = 50
2. Item A was sold qty = 300 @ 200 price
3. Item A was again produced qty = 400 and cost allocated after WO Process = 60

Now as per your revised condition the Average Material Cost calculation is taking the weightage of Cost 50 (Existing Avg Cost)

hence New $avg_cost will be calculated as 55

While the Old working will calculate the Weighted Average, that will Nullify the Weightage of 50 (Existing Avg Cost) and New $avg_cost will be calculated as 60, that is required.

www.boxygen.pk

Re: Update Material Cost Function

Yes, isn't avg cost 55 more accurate ?
Things are explained here: http://frontaccounting.com/punbb/viewtopic.php?id=7568

Phuong

Re: Update Material Cost Function

Not at all @notrinos

I have gone through though the above post. I don't know how to fix the  dashboard issue but this is violating standard Accounting Principles

The material cost shows the per unit average cost of the item A at any given point in time in the system.

After the step 3 the system will pass gl transaction as below

Inventory.              24000
        Wip.                          24000

While inventory valuation report will show the value of stock as 22000

Secondly if we sell all 400 in Step 4 the gl transaction will record as below

Cost of Goods Sold.          22000
        Inventory.                             22000

Hence the inventory ledger will be left with 2000 not legitimate, the profits are over valued.

@joe, @itronics, @notrinos please think over it and get it rolled back to original one.

www.boxygen.pk

Re: Update Material Cost Function

Yes, serious problem! will check right now

Phuong

Re: Update Material Cost Function

Hello @boxygen

I found the cause of this issue is not the new calculating method, it gives more accurate average cost than the old method.
The problem is when updated this new method for manufactured items cost we did not update the same method for component/purchased items cost so that system calculates wrong inventory values.

I guess the solution would be: lines 66, 67 of grn_db.inc needs to be replaced with

if ($qoh + $qty > 0) {
            if($qoh == 0 && $material_cost != 0)
                $material_cost = ($material_cost + $price_in_home_currency)/2;
            else
                $material_cost = ($qoh * $material_cost + $qty * $price_in_home_currency) /    ($qoh + $qty);
        }

Will take a closer investigate tomorrow

Phuong

Re: Update Material Cost Function

@notrinos please do have a closer look.

Your First para in the above post means that 55 is more accurate cost of the Item A than 60. Where will you then place the wrong Reflections in GL Transactions and Inventory Account. as shown in this image

I am unable to understand that why there is a special handling for a condition $qoh==0 in both files grn_db.inc and work_order_costing_db.inc. This special handling is not necessary at all.

For this special condition we are taking a Non Weighted Average while if $qoh is either +ve or -ve we are taking Weighted Average.

For Calculating Average Material Cost using Non Weighted Average formula is a violation of Cost Accounting Practices irrespective of $qoh value.

I hope my explanation will help you to investigate it correctly.

Post's attachments

gltrans_inv.png 53.2 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.
www.boxygen.pk

9 (edited by notrinos 01/09/2019 04:49:24 pm)

Re: Update Material Cost Function

Yes @boxygen
Problem is not solved with new method applying to grn_db.inc
Thank you for correcting my serious mistake.
I have to find another way to get this cost value.

@joe THIS COMMIT
needs to be rolled back.

Phuong

Re: Update Material Cost Function

My pleasure. It's our product smile

www.boxygen.pk

Re: Update Material Cost Function

Ok I will roll it back in a while.

Joe

Re: Update Material Cost Function

Fixed and rolled back to repo.

New file can be downloaded here.

/Joe

Re: Update Material Cost Function

@joe
Nothing changed in the rollback

code block from line 106 - 111 needs to be replaced with:

if ($qty > 0 && ($qoh != -$qty))
        $avg_cost = ($avg_cost*($qoh+$qty_delayed)+$unit_cost*$qty_new)/($qoh+$qty);
Phuong

Re: Update Material Cost Function

Committed and new file here.

Joe

15 (edited by aleifuuwork 07/15/2019 12:21:55 pm)

Re: Update Material Cost Function

Following may sounds silly but I hope you can help clarify how FA costing works since this thread just found an issue with weighted average cost

Ok everyone, in attempt to understand how FA costing and profit/sales recognition method works let's consider following 3 consecutive work orders dated 8, 9, and 10 July 2019 ( Monday to Wednesday )

date 2019/07/08 is the very first day doughnut production hence there will be no previous average_cost

date    item-name    qty_in    price        stock    stock_value    average_cost
2019/07/08    doughnut    50    1.23        50    61.50    1.23
2019/07/09    doughnut    50    1.25        100    124.00    1.24
2019/07/10    doughnut    50    1.27        150    187.50    1.25

In my case, my admin John is responsible to enter WO transactions and Mary is responsible to enter SalesOrder transactions. John usually enters and completes WO transactions day by day while Mary only works on weekend and complete all pending SO by weekend

My question is

A. If the first SO dated 2019/07/09 for 50 item.
    these 50 item's cost would be 50*1.23 or 50*1.25 ( latest entry average_cost ) ?

B. If the first SO dated 2019/07/12 for 50 item.
    these 50 item's cost would be 50*1.23 or 50*1.25 ( latest entry average_cost ) ?


For A and B, I believe the answer supposed to be 50*1.23 isn't it ?

But if anytime you need to do Inventory Adjustment for 50 item on or before 2019/07/10, the value would have been 50*1.25 ( by the latest entry average_cost ), is it not ?


Thanks for your time