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.
Currently, custom reports encompass only general ledger transactions. So you are wasting your time there. Many users hope this will change soon.
You can really only create what you want through exports and manipulation in a spreadsheet, as you are doing. However, consider exporting the Inventory Items tab listing directly, especially if you are using goods receipts and delivery notes. You will have options of using quantities on hand or quantities owned.
Or, you could export the Inventory Value Summary and multiply the Closing Balance column by some inflation factor. This approach, of course, assumes current sales values would be similarly scaled from original cost values. That may or may not be true, depending on how you determine your selling prices.
Thank you, this is much quicker than what I was trying. I don’t know why I didn’t think of this. It does raise another instance of a situation in which I wish I could filter my view, for example in this case to show only items that have a Qty on hand greater than 0, but at least I can sort it and then delete the rows I don’t need in the spreadsheet, then resort by the item code or other criteria.
I hope future improvements to the custom reports will enable me to do this entirely within Manager, but for now this method works well enough. Learning some more spreadsheet skills will help even further. Thanks @Tut.
By the way, this wouldn’t work in my case, as I have made multiple stock purchases as the inflation has been causing the prices to rise. So items purchased only in the most recent order will have reasonably accurate values in the report, but the further back in time they were purchased, the less useful those figures will be. And there are probably some items that have been purchased multiple times at changing prices.
You can also sort in the tab first, then just drag over the rows you want, copy and paste, bypassing the Export button entirely. Or sort, then Export, but only drag over and copy the relevant rows from the Export window. Lots of options.