I would like to be able to create a report to determine the value of inventory I am holding on a given date. The built-in Inventory Value Summary report is almost perfect, but my major problem is that we have experienced huge inflation since much of my inventory was purchased, and so in many cases the purchase value of the inventory is meaningless for this purpose. I would like to be able to see the value as calculated from the sales prices, even if I then have to multiply this by a fraction to get a figure that I feel represents the value before I sell it.
The report I am imagining has the following columns:
- Item code
- Item name
- Quantity on hand
- Unit sales price
- Total resale value of stock on hand (ie the result of column 3 × column 4)
I don’t mind if I need to copy the report to a spreadsheet to perform a few calculations, like the one in column 5.
Is there any way of creating such a report, either with the built-in reports or custom reports? I have spent some time trying, but my efforts in custom reports seem to only return items with movement within the specified date range, and I haven’t worked out how to access the quantity on hand value.
The closest I have managed so far is to export the Inventory Quantity Summary and Inventory Price List reports to a spreadsheet and match up the rows and add columns for calculations. But the list of inventory items in the two reports isn’t an exact match, as the former includes items that we haven’t received yet, and the latter includes items that have sold out. So it takes some effort to match everything up to the point where I can perform the calculations I want.
Any suggestions that would get me closer would be appreciated.