Topic: Error in inventory valuation report

FA 2.3.19 reports following wrong result in the inventory valuation report (IVR)

1. The IVR seems to use the latest ave unit price to value stock.
2. So if, today (12 August) I want to run the 31 July 2014 IVR (which quite a normal thing to want to do), the report will be generated using 31 July quantities but today's unit cost.
3. This means that the IVR cannot be run for any day other than "today" - or it will be wrong if there have been any changes to inventory unit cost between the reporting date and "today."
4. The report needs to select the average cost as at the reporting date, not the actual date it is run.

I'm not a programmer and can't correct this in the code.
But I am an accountant and this is a really big flaw in FA's reporting.
Can this be fixed for the good of the whole FA community?

Re: Error in inventory valuation report

FA is using the average material cost when calculating the cost prices.

The item cost price can be manuelly adjusted in the Items and Inventory tab. These Changes will not affect the stock movements and therefore will show the wrong results when using the stock movement cost prices.

This is the fact for report:

Inventory Valuation report,

The reports, Inventory Sales Report, GRN Valuation report, Inventory Purchasing report, Costed Inventory Movements, Inventory Sales report shows the correct cost price values for the stock movements.

/Joe

Re: Error in inventory valuation report

Hi Joe
thanks for explanation.
The IVR is not reliable for looking at stock values at past dates.
I understand that prices can be adjusted as you say, but that's not the solution or the point.
Nor is the stock movement from the various number of places you mention.

The point is that if I want to see a list in support of my general ledger stock value as at (for example) 30 June 2014 there is no reliable way of doing so if my unit prices have changed (by virtue of increased supplier costs, differing exchange rates etc) between 30 June 2014 and the day I run the report.

The only time the IVL is guaranteed to be accurate is if it is run for "todays" date.  This is a problem. 

Like the Costed Inventory Movement Report, the Inventory Valuation Report is not always reliable because FA does not link inventory unit costs with dates.  From what I see, it uses latest cost regardless of date of the report I want.

4 (edited by Harry 09/05/2014 06:49:42 am)

Re: Error in inventory valuation report

The reports, Inventory Sales Report, GRN Valuation report, Inventory Purchasing report, Costed Inventory Movements, Inventory Sales report shows the correct cost price values for the stock movements.



_____________
http://www.univie.ac.at/   
https://www.iseb.co.uk/   
http://www.free-braindumps.net/HP2-E58-pdf.html   
http://www.caltech.edu/

Re: Error in inventory valuation report

The Inventory Valuation Report (IVR)is not reliable for looking at stock values at past dates.

If I want to see a list in support of my general ledger stock value as at (for example) 30 June 2014 there is no reliable way of doing so if my unit prices have changed (by virtue of increased supplier costs, differing exchange rates etc) between 30 June 2014 and the day I run the report.
The only time the IVL is guaranteed to be accurate is if it is run for "todays" date.  This is a problem, particularly for audits, as a IVR generated "today" for a past date may not balance to the GL inventory control account as IVR uses latest average unit cost on the date the IVR is run, not the historical average unit cost applicable at the reporting date.

This is a big limitation to using Front, which is a good product.

Re: Error in inventory valuation report

Does this commit fix the issue?

7 (edited by MarkAndrew 09/03/2014 04:30:32 am)

Re: Error in inventory valuation report

Thanks Apmuthu

Re: Error in inventory valuation report

Hi Apmuthu,
The fix you provide is for the "Costed Inventory Movement Report" (rep308) but my query was for "Inventory Valuation Report" (rep301). So your commit does not fix the issue with rep301

Re: Error in inventory valuation report

Then Inventory Valuation Report must be right as on the day of preparing the report only. Like Joe stated, "The item cost price can be manually adjusted in the Items and Inventory tab" temporarily for a different past dated report!

Re: Error in inventory valuation report

Hi Apmuthu

With all due respect, that's not a solution at all!

1. It's essential that the Inventory Valuation Report is able to be run accurately at any date a user chooses - otherwise this severely limits the usefulness of FA to accountants - who are more often than not reporting on past performance - especially in the context of an audit.

2. If I update unit costs via unit cost adjustments like you suggest, the GL will be adjusted which will make the IVL right but the GL wrong.

3. Then I will have to go and reverse all my temporary adjustments - that is after finding out what I need to adjust in the first place.  That's a big waste of time! 

This is a big flaw! For the benefit of all users it needs to be fixed - which I would gladly attempt to do if I knew how.  Not fixing this puts FA, as an accounting solution, in the "B team" for sure. 

Mark

Re: Error in inventory valuation report

But you can use the costed invnetory valuation report, right?

Are you saying, that this report should work just like the costed inventory report?

I am not sure if this is right. Somebody in the audience has earlier asked for the change as it is now.

Joe

Re: Error in inventory valuation report

Maybe we should take away the date selector from Inventory valuation Report, and force users to use the Costed Inventory valuation Report for selecting periods. What do you say guys?

Joe

13 (edited by MarkAndrew 09/03/2014 07:05:48 pm)

Re: Error in inventory valuation report

Hi Joe

The Costed Inventory Movement Report (CIMR) does not work properly either.
At least it did not last time I checked - it values closing stock at the latest average cost and not the historic average cost at the date the report is run for.  This is the same problem the Inventory Valuation Report (IVR) has.

Both reports need to be able to run at any date the user needs and both reports should value stock at the historic average costs for the chosen date.

The CIMR, if it was correct, could be substituted for the IVR, but it would be a pity to loose the IVR. 

Preventing the user from running the IVR for historical dates is not a good solution for Front.  Fixing both the IVR and CIMR to value stock correctly at any date the user needs is a good solution.


Mark

Re: Error in inventory valuation report

Well, the problem here is that you are allowed to change the standard costs at any time, which changes the GL as well, but does not change anything in the stock moves.
This will give an incorrect value in the IVR compared to the GL if taken before todays date and the standard cost has been changed manuelly.

The CIVR should give the correct costed value over all periods. If not, please download the final file here. However if you have changed the standard cost manuelly, it will show an incorrect value.

/joe

Re: Error in inventory valuation report

I Think I have found a solution to the manuel change of standard cost.
If a change is made to the item, then when saving the cost update, the stock_moves get the standard_cost updated for the difference and where the stock moves are <= the date.
This should eliminate the difference from the IVR and the GL when taken Before todays date.

I will upload three files here when fixing this and please download the files and help me testing this. The files will be /inventory/includes/db/items_trans_db.inc, /reporting rep301.php and &/reporting rep308.php.

Especially Apmuthu and MarkAndrew.

Thanks in advance.

/Joe

Re: Error in inventory valuation report

Thanks Joe
Will wait for updates.
Appreciated.
Mark

Re: Error in inventory valuation report

Hello again.

3 files are uploaded for testing. Download them and please rename the files rep301.php1 and rep308.php1 to *.php. Upload the two files to /reporting folder and /inventory/includes/db/items_trans_db.inc to /inventory/includes/db folder.

For backward compatibility there is now a flag in config.php (config.default.php) called $use_costed_values. Please enter this variable in the file config.php and set the value to 1. Default value will be 0 for backward compatibility. Select 1 for costed values.
This flag is related to the report, Inventory Valuation Report.

F.i. about line 176:
/* Show average costed values instead of fixed standard cost. Default value 0 */
$use_costed_values = 1;

The fix for changing the manuelly cost update will first work from now, so eventually old cost updates will not influence.

Please test and report back.

/Joe

Post's attachments

items_trans_db.inc 3.2 kb, 8 downloads since 2014-09-04 

rep301.php1 6.5 kb, 4 downloads since 2014-09-04 

rep308.php1 8.3 kb, 3 downloads since 2014-09-04 

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

Re: Error in inventory valuation report

Hi Joe

We installed your fixes..

Attachment icon items_trans_db.inc 3.21 kb, 1 downloads since 2014-09-04
Attachment icon rep301.php1 6.53 kb, 1 downloads since 2014-09-04
Attachment icon rep308.php1 8.26 kb, 1 downloads since 2014-09-04

I tested them using the following test data:

1. I created a new inventory item on 5.9.14
2. I purchased 1 unit for 100 + VAT on 5.9.14
3. I purchased 1 unit for 50 + vat on 31.8.14

The results showed errors as documented below.

......................................................................................................................
A)The results I expect to see in the Inventory Valuation Report (IVR) are:
   a) As at 31.8.14 I should see 1 unit x 50 unit cost = 50 total value
   b) As at 5.9.14 I should see 2 unit x 75 unit cost = 150 total value

   The results I actually see in the Inventory Valuation Report (IVR) are:
   a) As at 31.8.14 I should see 1 unit x 75 unit cost = 75 total value
   b) As at 5.9.14 I should see 2 unit x 87.50 unit cost = 175 total value

   Conclusion: The IVR qty is correct at both test dates, the IRV unit cost is wrong at both test dates
..............................................................................................................................................
B)The results I expect to see in the Costed Inventory Movement Report (CIMR) are:
   a) As at 31.8.14 I should see

        Open Stock                  Stock In                     Delivery                       Closing Stock
       Qty   Rate  Value           Qty   Rate  Value         Qty   Rate  Value         Qty   Rate  Value
        0       0        0               1      50       50             0     0        0              1      50      50


    b) As at 5.9.14 I should see

        Open Stock                  Stock In                     Delivery                       Closing Stock
       Qty   Rate  Value           Qty   Rate  Value         Qty   Rate  Value         Qty   Rate  Value
        1       50      50              1      100     100             0     0        0             2      75      150

   The results I actually see in the Costed Inventory Movement Account are:
   a) As at 31.8.14 

        Open Stock                  Stock In                     Delivery                       Closing Stock
       Qty   Rate  Value           Qty   Rate  Value         Qty   Rate  Value         Qty   Rate  Value
        0       0        0               1      75       75             0     0        0              1      75      75


    b) As at 5.9.14

        Open Stock                  Stock In                     Delivery                       Closing Stock
       Qty   Rate  Value           Qty   Rate  Value         Qty   Rate  Value         Qty   Rate  Value
        1       75      75              1      100     100             0     0        0             2     87.5    175

     Conclusion:  The CIMR qty is correct at both test dates, the IRV unit cost is wrong at both test dates.
                       
Issue to Resolve:
The IVR + CIMR do not respect the user's reporting date when costing opening stock. They takes latest average cost                                   
The calculation of average cost by FA for the data used is wrong.

------------------------------------------------------------------------------------------------------------------------------------
I then tested them using the following test data:

1. I created a new inventory item on 5.9.14
2. I purchased 1 unit for 50 + vat on 31.8.14
3. I purchased 1 unit for 100 + VAT on 5.9.14 (the date order is different to the first set of test data)
....................................................................................................................................................................
A)The results I expect to see in the Inventory Valuation Report (IVR) are:
   a) As at 31.8.14 I should see 1 unit x 50 unit cost = 50 total value
   b) As at 5.9.14 I should see 2 unit x 75 unit cost = 150 total value

   The results I actually see in the Inventory Valuation Report (IVR) are:
   a) As at 31.8.14 I should see 1 unit x 50 unit cost = 50 total value
   b) As at 5.9.14 I should see 2 unit x 62.50 unit cost = 125 total value

   Conclusion: The IVR qty is correct at both test dates, the IRV unit cost is correct at 31.8.14, but wrong at 5.9.14
....................................................................................................................................................................
B)The results I expect to see in the Costed Inventory Movement Report (CIMR) are:
   a) As at 31.8.14 I should see

        Open Stock                  Stock In                     Delivery                       Closing Stock
       Qty   Rate  Value           Qty   Rate  Value         Qty   Rate  Value         Qty   Rate  Value
        0       0        0               1      50       50             0     0        0              1      50      50


    b) As at 5.9.14 I should see

        Open Stock                  Stock In                     Delivery                       Closing Stock
       Qty   Rate  Value           Qty   Rate  Value         Qty   Rate  Value         Qty   Rate  Value
        1       50      50              1      100     100             0     0        0             2      75      150

   The results I actually see in the Costed Inventory Movement Account are:
   a) As at 31.8.14 

        Open Stock                  Stock In                     Delivery                       Closing Stock
       Qty   Rate  Value           Qty   Rate  Value         Qty   Rate  Value         Qty   Rate  Value
        0       0        0               1      50       50             0     0        0              1      50      50


    b) As at 5.9.14

        Open Stock                  Stock In                     Delivery                       Closing Stock
       Qty   Rate  Value           Qty   Rate  Value         Qty   Rate  Value         Qty   Rate    Value
        1       75      75              1      75     75             0     0        0                2     62.50    125

     Conclusion:  The CIMR qty is correct at both test dates, the IRV unit cost is correct and 31.8.14 but wrong at 9.5.14.
...............................................................................................................................................................

Re: Error in inventory valuation report

Ok, I will have a look at this asap.

Joe

20 (edited by apmuthu 09/05/2014 06:05:45 pm)

Re: Error in inventory valuation report

If and when this is fixed, must it be made out with a flag in the config.php to suit each users need or is MarkAndrews requirement universal - accountants please....

How can you create an item on 5th Sep 2014 (later date) and then expect to purchase it on 31st Aug 2014 (prior date)?

The line 67 in the new includes/db/items_trans_db.inc :

change_stock_moves_std_cost($stock_id, $date_, $new_cost - $last_cost)

is wrong since the difference in cost should be calculated by taking the weighted value of the old cost based on its prior stock value and not merely the difference in cost between previous average cost and current cost as is done above.

The correct calculation should be:

QOH = ( PrevQOH + PurchaseQty ) ;
Avg Cost = ( (PrevAvgCost * PrevQOH) + (PurchaseQty * PurchasePrice) ) / QOH ;

Re: Error in inventory valuation report

I know the algorithm for calculating the avererage cost, but this function should only regulate the stock moves standard cost in respect to what was booked in the GL when changing the standard cost in Items and Inventory tab.

/Joe

Re: Error in inventory valuation report

BTW, it turns out to be a Little more complicated changing the Costed Inventory Valuation Report.

I am woeking on it and will upload fresh files during the weekend for testing.

/Joe

Re: Error in inventory valuation report

Ok, MarkAndrew. here are two new report files, rep301.php1 and rep308.php1. Rename them to *.php and replace in the /reporting folder.

This work has been extremely difficult, due to the way the standard_cost is saved on the stock_moves. It turned out to be the average material cost after the stock move.
I had to calculate back the actual price, which was fairly simply, but due to various bugs over time my test data is not very good any more. But the algorithms should be ok.

Then add all the actual prices and divide with the counter should now give the average standard_cost over the time selected.

I hope this works and remember the old way with calculating the Inventory Valuation Report by today are still working smile

Please report (positively) back smile

/Joe

Post's attachments

rep301.php1 7.4 kb, 5 downloads since 2014-09-06 

rep308.php1 9.4 kb, 4 downloads since 2014-09-06 

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

Re: Error in inventory valuation report

Thanks Joe
Will down load and test asap and report back to you as you ask.

Re: Error in inventory valuation report

Is there no change in the items_trans_db.inc file or should the patched one in the earlier post be used?