We sell various products online, and I'm trying to build a formula to tell us what quantity of each item to order. However, things aren't always that easy!
First, I need to figure out the demand, or how many pieces per day we can sell. Lets call this the "Potential Daily Sales Rate"
Ideally, I'd like to predict the future, but initially, lets just discuss the prior sales history, and how to account for days I run of stock.
I'll call this PROBLEM #1
We have sales data from our backend software, which does provide sales for each prior period, but products are very often out of stock, so I can't just take an average.
For example, lets say on the first day of the month we receive 100 pieces of a widget, and they sell out completely the first day. Then at the end of the month I go to reorder this item, and I want to have 30 days worth of stock on hand. I want the formula to tell me to order 3,000 pieces, and not to tell me 100 pieces, even though I actually only sold 100 in the last month.
What I would like to do is have the formula tell me; You sold 100pc in the last 30 days, and you were in stock for only 1 day, so:
total sales (100) / in stock days (1) = Potential Daily Sales Rate (100)
However, nothing in my data shows me how many days I'm out of stock, and I can't just look for hiccups in the sales rate, since sometimes sales can fluctuate wildly for many other reasons.
So here is what I think could be a potential solution:
I could take an export / snapshot of inventory every day, then compare my daily sales rate to how many pieces I have on hand each day, and any day that I have less on hand than my Potential Daily Sales Rate I could consider to be an out of stock day.
However, this sounds like a circular reference since I don't the have the Potential Daily Sales Rate yet, and I'm not sure how to actually build this spreadsheet and write the lookup formulas.
So problem # 1 is getting the "Potential Daily Sales Rate" if the products were always in stock.
Then PROBLEM #2 would be how best to adjust that sales rate for trends. For this example, lets assume that products were always in stock in the prior periods, and they are not seasonal items.
If sales for the prior periods were 10, 20, 30, 40, 50, and 60, then it would be a fair assumption to say that trend is likely to continue, and I would like some formula to tell me to consider buying maybe 70 or more for the next period.
However, if sales were 10, 40, 20, 30, 60, 40 then they appear to be more volatile / random and maybe I should order somewhere around 45-50 (taking some kind of average of prior sales but weighing the last few periods more heavily).
Even better, would be in addition to accounting for trends, would be some way that I could provide a percentage of certainty that I would be in stock, and have excel calculate the amount i need to have on hand to reach that certainty.
(As a side note, we do eventually want to do all this in Power BI, but for now an Excel solution would be fine)
Is all this as difficult to do as it seems to me?
Any and all help or advice appreciated.
First, I need to figure out the demand, or how many pieces per day we can sell. Lets call this the "Potential Daily Sales Rate"
Ideally, I'd like to predict the future, but initially, lets just discuss the prior sales history, and how to account for days I run of stock.
I'll call this PROBLEM #1
We have sales data from our backend software, which does provide sales for each prior period, but products are very often out of stock, so I can't just take an average.
For example, lets say on the first day of the month we receive 100 pieces of a widget, and they sell out completely the first day. Then at the end of the month I go to reorder this item, and I want to have 30 days worth of stock on hand. I want the formula to tell me to order 3,000 pieces, and not to tell me 100 pieces, even though I actually only sold 100 in the last month.
What I would like to do is have the formula tell me; You sold 100pc in the last 30 days, and you were in stock for only 1 day, so:
total sales (100) / in stock days (1) = Potential Daily Sales Rate (100)
However, nothing in my data shows me how many days I'm out of stock, and I can't just look for hiccups in the sales rate, since sometimes sales can fluctuate wildly for many other reasons.
So here is what I think could be a potential solution:
I could take an export / snapshot of inventory every day, then compare my daily sales rate to how many pieces I have on hand each day, and any day that I have less on hand than my Potential Daily Sales Rate I could consider to be an out of stock day.
However, this sounds like a circular reference since I don't the have the Potential Daily Sales Rate yet, and I'm not sure how to actually build this spreadsheet and write the lookup formulas.
So problem # 1 is getting the "Potential Daily Sales Rate" if the products were always in stock.
Then PROBLEM #2 would be how best to adjust that sales rate for trends. For this example, lets assume that products were always in stock in the prior periods, and they are not seasonal items.
If sales for the prior periods were 10, 20, 30, 40, 50, and 60, then it would be a fair assumption to say that trend is likely to continue, and I would like some formula to tell me to consider buying maybe 70 or more for the next period.
However, if sales were 10, 40, 20, 30, 60, 40 then they appear to be more volatile / random and maybe I should order somewhere around 45-50 (taking some kind of average of prior sales but weighing the last few periods more heavily).
Even better, would be in addition to accounting for trends, would be some way that I could provide a percentage of certainty that I would be in stock, and have excel calculate the amount i need to have on hand to reach that certainty.
(As a side note, we do eventually want to do all this in Power BI, but for now an Excel solution would be fine)
Is all this as difficult to do as it seems to me?
Any and all help or advice appreciated.
