Average of Multiple Date Ranges

[felixfelix]

Thanks to anyone who can provide insight with this question. Until last week I was oblivious to SUMIFS and VLOOKUPs, but I feel that I am slowly figuring out the true beauty of Excel.


I am attempting to figure out the average number of days that it takes for a product to sell for a wide range of products with varying inventory levels.


I have a table that has reference date for when these items were taken into inventory, and I have a table that lists the dates when the items were sold. The SKU is a unique identifier for each product, and is referenced on the same row as both the reference date in Table 1 and the transactions list in Table 2.


I have multiple SKUs each with a different number of inventory items. I am having difficulty figuring out how to calculate the average number of days it takes for an item to sell, granted that each SKU has different transaction dates and inventory counts.


Thoughts?

 

[Mike86]

Since you can only calculate the average days-to-sell after the entire receipt of a given SKU is sold, it'll be a bit tricky.


Do you track the sales of each SKU receipt, or only sales from each SKU? As an example, you get 10 apples today (batch 1), sell 5 apples tomorrow (batch 1), receive another 10 apples the next day (batch 2). Your SKU has 15 apples total with 5 in batch 1 and 10 in batch 2. If you're tracking the individual batches, it's doable.

 

[Hui]

Felix


Firstly, Welcome to the Chandoo.org Forums


Can you please post your data somewhere so we can have a better look at the issues?

 

[felixfelix]

Mike86/Hui,


You can download a scaled down worksheet from Dropbox that gives you a general idea here: http://db.tt/joNPM79P. To answer your question Mike86, I am doing this on a First In, First Out inventory basis, so essentially it will be done in individual batches (another reason for this is that my inventory cost for each batch will differ a bit).

 

[Mike86]

FelixFelix:


SKU usually means Stock Keeping Unit and indicates a certain model/style item. It's a little strange to use the term item-wise on a FIFO system, but OK.


The dl'd spreadsheet only had two tabs, Inventory Calculated and Transactions Report. Based on the Inventory tab display, I'm thinking there are some functions and calculations that didn't make it over. It looks like you are getting the number of orders for each item from "Transactions" and subtracting that from the inventory.


The Transactions tab is pretty straightforward, I'm assuming you're using data validation to select an order versus a receipt. However, there is no column for how many items are in the transaction. How do you get multiple items into inventory or handle an order for more than one?


To do the average days calculation you need to have a couple of dynamic ranges:


TransDates=OFFSET('Transactions Report'!$A$2,0,0,COUNTA('Transactions Report'!$A:$A)-1)


SKUEntries=OFFSET('Transactions Report'!$C$2,0,0,COUNTA('Transactions Report'!$C:$C)-1)


Then, check for inventory to hit zero and use an array function in Col F on the Inventory tab to get the last transaction date:


=IF(D3=0,LARGE(TransDates*(SKUEntries=A3),1),"")


The average days/unit is:


=(F3-E3)/B3


Since it's unlikely sales will follow a normal distribution, this may not be all that informative. You might try the Median function as well.

 

[felixfelix]

Thanks Mike. In the cleansing of the document to make it acceptable to post for the world to see I pulled too much off. In the example I was assuming that 1 each of the inventory units were sold. I will work on this a bit and post back if I make any headway.