• From: India
  • Registered: 11/09/2009
  • Posts: 115

Topic: COGS and Inventory GL out of sync !!

Hi Guys,

Please try this scenario.

Decimal places for Prices/Amounts: 2 (It is preferred for Invoice Printing)

First Inventory adjustment for a new item - qty - 2650 for unit cost 5.46
First Inventory adjustment for a new item - qty - 800 for unit cost 5.48

This results in standard cost - 5.46
Inventory valuation as per report = 18,837
Inventory GL = 18,853
Difference = -16

If all of this inventory is sold, inventory GL will show 16 units remaining and COGS will be 18,837. As qty becomes higher difference amplifies.

In our live company, the difference is of 40,000.  roll

After analysis, it seems that the cost is rounded during averaging process.  It creates the COGS difference. The rounding is set to Prices/Amounts decimals set by user.

Since cost is used for internal COGS calculation, can rounding be avoided for cost ? It will result in proper cost and thus reduce the difference.

Also can we keep track of cost change history along with the reason of cost update like WO, GRN, Cost Update and transaction number, it helps to analyse.

Please help to resolve the issue. smile

Thanks,

Chaitanya
  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: COGS and Inventory GL out of sync !!

If you need more accuracy in the purchase pricing (average standard costs) you should set the initial purchase price in Items and Inventory to be with more than 2 decimals (f.i. 6). Then the internal calculation of average costs will be set to these decimals.

Look in the  under Purchasing, Purchasing Prices.

/Joe

  • From: India
  • Registered: 11/09/2009
  • Posts: 115

Re: COGS and Inventory GL out of sync !!

Hi Joe,

Thanks for the tip. I need your help to understand the following case.

There are 2 PO deliveries done.
On June 23, 2010 - Qty - 3437 For Price - 33.77335 (Delivery # 1)
On April 01, 2010 - Qty - 1213 For Price - 33.34450 (Delivery # 2)

Std cost after second entry updates to - 33.34450 (It results in cost depreciation causing the out of sync.)
By calculation it should be - 33.66148

After code view it seems that function update_average_material_cost in grn_db.inc considers get_qoh_on_date as of transaction date.

Is there any reason for get_qoh_on_date ?
Average cost affects all inventory irrespective of dates.

Please help to understand the issue.

Other point is rep204 (Outstanding GRN) uses Std Cost. It should be unit_price of PO.

Thanks,

Chaitanya
  • From: India
  • Registered: 11/09/2009
  • Posts: 115

Re: COGS and Inventory GL out of sync !!

Hello again,

Supplier Invoice also uses same policy in adjusting the cost difference.
Function updates the average cost considering get_qoh_on_date as of GRN date but the resulting cost affects overall inventory.

Should there be NO date filter for calculating qoh ?

In order to be more clear on cost diff. adjustment can we update memo like this

if ($amt != 0.0)
                {
                    $cost_diff = $mat_cost - $deliveries[1];
                    $memo = "Cost difference adjustment for ".$entered_grn->item_code.". ".$deliveries[0]." items delivered since ".$old_date.". The cost difference is ".$cost_diff;
                   
                    add_gl_trans($trans_type, $invoice_id, $date_,    $stock_gl_code["cogs_account"],
                        $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo,
                        $amt, null, null, null,
                        "The general ledger transaction could not be added for the price variance of the inventory item");

Thanks,

Chaitanya
  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: COGS and Inventory GL out of sync !!

The get_qoh_on_date is extremely important when calculating price-diff. If price changes from GRN to Supp Invoice all Deliveries must be recalculated and the dates are very important. The old calculations for qty are done on the former date and the new qty calculations are done on new date.
There are more examples on where these calculations are needed. It is a very complicated process.

You are right regarding pre704 and I will change your proposals into 2.3 (shipping soon in beta).

/Joe