My main goal is to account for correct inventory value after each inventory purchase. Every time I purchase items, the average price of TOTAL inventory changes, but every time the sale is made, only Inventory Q-ty goes down and price stays the same until I purchase items again and it recalculates. I have done some explanation for you by using T Accounts in my Excel file:
The main idea here is to create a column, where price of inventory updates every time the purchase is made, so whenever I sell inventory, I can keep track of the amount of Inventory Sold, Sales Amount and Revenue/Profit that is created in between. Here is a snippet of how I envision that:
Please help me creating a measure/variable for that. I have gone through a similar question on your forum, but the guy had the value of the inventory hardcoded and not suitable for my application.
Wait @mspanic, I think I found a mistake in this logic. It is not that simple just to divide Cum. Purchase by Cum. Weight. If you look carefully into May transactions, you can see that by the end of May 1, we have 0.44 t left at the price of 1821.73 (801.56 PLN). Then on May 4 we purchase 20.4 t @ 2000/t (40800 PLN). Eventually, our New END Inventory PLN should be 40,800 + 801,56 = 41,601,56 PLN, which gives us the New Perpetual Average Unit Cost @ 1996.23 PLN, not 1881.31 PLN.
The idea here is to write a code that would take END Inventory value after each SELL to substitute the previous maximum (40.64 in this case) in the “Cumulative Weight (t)” column.
Also “New END Inventory (PLN)” must be a sum of previous value and “Purchase GLOWKA (PLN)”.
Hi @pravdon1, thank you for checking and additional notes.
Sorry I was stuck with my new project (just started) and get out of time.
I manage to update Cumulative weight(t) columns as you mentioned in the previous post and add Indicator Inventory Entry Type
1- BUY
2- SELL
3- SWITH (First BUY after SWITCH)
Also add a cumulative columns with reset at switch. It need additional updates, but I am posting as it is.
I put new measures in folder New Additional Inventory Measures
Due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.
