It's much more fun, when you can discuss your problems with others...
You are not logged in. Please login or register.
FrontAccounting forum → Report Bugs here → Error in inventory valuation report
Yes, of course. Why not?
If the standard cost is manuelly changed in Items and Inventory tab, it should affect the standard cost for the stock moves all over. Otherwise, the GL is not syncronized with the Stock values.
/Joe
I re-tested the latest 2 fixes using the following test data:
1. I created a new inventory item on 9.9.14 (the day I created the item)
2. I purchased 1 unit for 100 + VAT on 9.9.14 (the first invoice in the pile to capture)
3. I purchased 1 unit for 50 + vat on 31.8.14 (the second invoice in the pile to capture)
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 9.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 see 1 unit x 75 unit cost = 75 total value
b) As at 9.9.14 I see 2 unit x 75 unit cost = 150 total value
Conclusion: The IVR qty is correct at both test dates, the IRV unit cost is wrong for past 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 9.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 9.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 125 125 0 0 0 2 100 200
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 take 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 9.9.14
2. I purchased 1 unit for 50 + vat on 31.8.14
3. I purchased 1 unit for 100 + VAT on 9.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 see 1 unit x 75 unit cost = 75 total value
b) As at 5.9.14 I see 2 unit x 75 unit cost = 150 total value
Conclusion: The IVR qty is correct at both test dates, the IRV unit cost is wrong at 31.8.14 and correct at 9.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 50 50 1 100 100 0 0 0 2 75 150
Conclusion: The CIMR qty and unit cost is correct at both test dates.
Hello again MarkAndrew,
When you are entereing the high value of 100, The fixed average material cost is set to 100, so when you enter the value at 31.08.14 at 50, the stock_moves standard_cost is calculated to 75. And when we try to calculate back the price we have no prior values to calculate from.
Therefore we get this error.
I am not able to fix this, due to the fact that the standard_cost on the stock_moves is stored based on the fixed value.
So now is the the question? Can we live with this? How often are your latest scenario done? This is the best I can do.
We cannot improve the CIMR and the IVR further based on costed values.
Do you Think we should go for this anyhow, or should be just leave the IVR to not be selectable on date values?
/JOe
Hi Joe
The first time tested we down loaded
items_trans_db.inc 3.21 kb,
Attachment icon rep301.php1 6.53 kb,
Attachment icon rep308.php1 8.26 kb
The second time we tested we down loaded
rep301.php1 7.41 kb
rep308.php1 9.4 kb
Should we have down loaded
items_trans_db.inc 3.21 kb
for the second test too?
Mark
No, this is not needed.
Joe
@MarkAndrew.
It just popped into my mind, that we can use the stock_moves price when handling receivals, and supp inv/credit notes.
If the price is belonging to a supplier that has a foreign currency, we can calculate the domestic value for the given date.
I will have a closer look at this later this evening.
This way wes could eliminate the problem in you last report.
Joe
Ok, MarkAndrew. here are two new report files, rep301.php1 and rep308.php1. Rename them to *.php and replace in the /reporting folder.
We are now using the price on the stock_moves where appropriate. This price has been converted to domestic values where needed.
I have tested this in your scenarios and it works.
Please give me a feed back. Maybe we after all can use the costed values.
/Joe
thx Joe
will download and test asap
Mark
Hi Joe!
It works.
The results were as expected for the same test data as before.
The Inventory Valuation Report reported correct average costs when run for past dates and the Costed Inventory Movement Report reflected correct average unit costs for opening stock, purchases and closing stock.
Thank you very much!
Mark
Thanks Mark
I will then push it to the HG repository to go to next minor release.
/Joe
That's fantastic thinking out of the box @joe as always and quick and clear testing and feedback by @MarkAndrew. Thanks.
Hello,
I have tested your bug fix with latest rep301 (Inventory Valuation Report) with $use_costed_values=1. It seems to work at least partially.
Item Cost in Inventory Valuation Report should also be calculated according to "received into" location unless you don't specify a location in the report.
To be able to reproduce the problem, just create two locations: A and B
Create one PO for 10x item P (cost 10$) received at location A and another one for same 10x item P (cost 90$) at location B.
Average item cost is 50$ for both locations A and B according to current algorithm. Inventory Valuation Report should show 100$ at location A and 900$ at B. Right now both have stock 500$.
I guess also that if all items P at location A are transferred to location B, average item cost at location B should then drop from 90$ to 50$. However Stock Valuation for both A and B location should stay the same at 1000$ with or without a transfer (considering a 0$ shipping cost between A and B).
If you wish to track the same item with vastly varying prices, then make it two differently coded items (stock_id). There is nothing wrong with the current algorithm's outcome as the item is still one of a kind.
This is and must be the same item with same stock_id. Price can be influenced by supplier discount, exchange rate change,...
For illustration, let's imagine a company such as Walmart. Store A (New-York) purchase T-shirt ABC from china at price 10$. A month later, store B (Los Angeles) purchase the same T-Shirt at 6$ (discount). A month later, T-shirt is back at 10$ from supplier. Since Los Angeles store still has a lot of these T-Shirt in stock and New-York is out of stock, New-York purchase from LA at 8$. So LA makes profit and reduces its stock and New-York make also more profit (as compared to purchasing from chinese supplier). These stores must really share the same stock_id. From a fiscal point of view as well as management and insurance point of view, you really need to know the exact acquisition cost for a specific location.
Since one location sells to another - treat them as different shops - each can make a profit from selling to the other. Alternatively use average costing and let the discount given to each customer vary. The exact acquisition cost will then be factored in the average cost. Anything specific can be treated as a different item for the FA internals with the same description - item_code and stock_id - you can also use manufacturing / assembly and kits. In the inevitable instance, make yourself an extension.
Hello,
I am having a similar issue in version 2.4.3; which files do I need to download to fix;
below is my scenario in Inventory Valuation Report
------------------------------------------------------------
Qty Unit Cost Total Cost
Opening 37 193 7141
Adjustment 37 0.01 0.37
Closing 74 193 14282
Expected Results
-------------------
Qty Unit Cost Total Cost
Opening 37 193 7141
Adjustment 37 0.01 0.37
Closing 74 96.505 7141.37
Thank you in advance,
Mugao
Check if the error still occurs in a FA 2.4.4+ (GitMaster) install.
Dear Apmuthu,
Thank you for your quick response.
I have tested using that installation, the issue is fixed, thanks.
I think I am bumped into similar issue. Currently using 2.4.3
Going to upgrade to 2.4.4 soon and see wether it resolves
IVR and CIMR are both very important, because at the moment that's how you get inventory valuation and they way it's setup not correct / accurate
Could a cron job be setup to run automatically on the last day of the period (EOM) to produce the IVR? That's how we do it with a different ERP
maybe create manual script first that re-evaluate the whole costing / valuation since $start_period untill $end_period or EOM that can be enacted manually somewhere in the Setup tab
after that's done, then naturally setting cron job of this script is already 2nd in nature
FrontAccounting forum → Report Bugs here → Error in inventory valuation report
Powered by PunBB, supported by Informer Technologies, Inc.
Currently installed 4 official extensions. Copyright © 2003–2009 PunBB.