On-Hand Inventory Liability: Getting Retail Price and On-Hand Inventory Together in One Report

Reports
1

On-Hand Inventory Liability Spreadsheet:

There is not currently a report that shows both On-Hand Inventory and Retail Price. The best way I’ve found to get that information on one report is to combine the Product Export spreadsheet (accessed from Settings > Import/Export > Product Exporter > (I recommend changing the Format of the report to Advanced as opposed to Regular because the former includes things like COGS).

Next, we need some current inventory numbers. Those can be found from: Reports > (hover over) Inventory > Inventory Summary.

We’ll use Google Sheets (free for everyone) to create a spreadsheet with three tabs:

  • Dashboard
  • InventorySummary
  • ProductExporter

Here’s a sample version of what I’m talking about here

The magic happens on the Dashboard sheet. The Columns on the Dashboard in my example are:

  • Column A: Google Sheets has an awesome formula called UNIQUE. Just enter in A1: =UNIQUE([select Column B from the InventorySummary sheet]) and Sheets will just return the set of unique values from that sheet. I like doing it this way because it controls for multiple Inventory Pools. If you were to just pull all of the SKUs off of the ProductExporter, you’d pull in all of the SKUs that you have even though many of them may not use inventory and that’s the whole point of this thing.
  • Column B: Total Inventory here is just calculated by using a =SUMIF formula. In cell B2, for example, you have a SUMIF formula that says in English: go look at the InventorySummary sheet and sum all of the times this SKU in cell B1 shows up.
  • Column C: This is the really fun one: Proper use of =INDEX(MATCH()) will make people believe you have magical powers. In English, this formula says: Ok, take the SKU in Column A and go find that SKU on the ProductExporter sheet. When you get there and you find the SKU, march down the row and retrieve this other piece of information I need. In the case of Column C, that information is the Retail Price.
  • Column D: Extended Retail Price is pretty easy to grasp. This is just the inventory sum multiplied by the price.
  • Column E: This is the same a Column C except this time, the formula has orders to go find and retrieve the COGS.
  • Column F: Extended COGS is the same as Extended Retail Price except for the obvious: It uses COGS as opposed to Retail Price.

Please let me know what other kinds of non-standard figures or metrics you guys calculate on a regular basis (or want to calculate on a regular basis) and I’ll do a write-up on it. This one was just meant to test the waters and subtly introduce a couple of powerful formulas.

Thanks!
:relaxed:

1 Like
2

Maybe I’m missing something here, but it seems it would be much faster and easier to use a LOOKUP function. Take the InventorySummary sheet and have it VLOOKUP the Cost and Price from the ProductExporter. Then simply summarize in a pivot table.

https://dl.dropboxusercontent.com/u/51198544/On-Hand%20Inventory%20Liability-Easier.xlsx

You could build a Dashboard function that would then just allow you to drop in the two exports every time you need the information.

1 Like
3

Thanks for the note, @EdFarmCollective!

To defend my formula choice: Here’s an article I like. Here’s another one. Why not one more.

To the rest of the people: You’ll be fine either way. Like I said, this was just a taste of “derived” reports.

4

I think my point is you introduced a ton of complexity without any benefit. In my example, you can use VLOOKUP or INDEX interchangeably, and it’s still significantly fewer steps.

Game recognize game, tho.

5

So the Product Exporter doesn’t include a product’s Sub-SKUs. Any solutions here? I find it remarkably difficult to get a list of all of my products and Sub-SKUs with any useful data.

6

I still can’t believe that we can’t just generate a simple price list - Product- Current Inventory - Retail Price. Why is that so hard?