Analyzing eCommerce Sales and Forecasting Demand

[thesauce]

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.

 

[oldbrewer]

problem statement
assuming a 10 day accounting period how do I forecast widget sales
day1	day2	day3	day4	day5	day6	day7	day8	day9	day10
0	5	3	6	4	0	8	4	4	6
									40
									4
	you are selling on average 4 widgets per day
	assume you had 50 in stock prior to day 1
	remaining sock = 10
	quantity needed to meet next month's predicted sales = 30

<colgroup><col span="12"></colgroup><tbody>
</tbody>

 

[thesauce]

Thank you Oldbrewer, I do understand what you're saying, but I'm trying to exclude any days where I ran out of stock. So in your example you had 2 days with zero sales. IF those days I didnt have any qty on hand, then I would want to exclude those 2 days, and take the 40 units sold / 8 days, so my real potential would have been 5 units per day. That is what I'm having trouble figuring out; How to link my inventory report to the output of this formula, to exclude any days I had less than 5 on hand. AND the circular reference issue

problem statement
assuming a 10 day accounting period how do I forecast widget sales
day1	day2	day3	day4	day5	day6	day7	day8	day9	day10
0	5	3	6	4	0	8	4	4	6
									40
									4
	you are selling on average 4 widgets per day
	assume you had 50 in stock prior to day 1
	remaining sock = 10
	quantity needed to meet next month's predicted sales = 30

<tbody>
</tbody>

 

[oldbrewer]

if the demand for your product varies from zero to 8, a demand for zero items is normal and the day should be included. I kept my answer brief but obviously as you go to days 11 to 20 you now have more accurate data relating to demand. There is a cost to telling a customer you have zero stock as that customer may go elsewhere, and tell their friends your company is often out of stock. What is the cost and selling price of the widget - if it is 1 and 2 units your strategy re stock levels will be different to if it is 1 and 5. Does demand for the widget vary over the year ie more sales in Summer - that will affect your strategy also.

However if you wish to exclude zero sales days it is easy - I will post something in a few minutes

 

[oldbrewer]

day1	day2	day3	day4	day5	day6	day7	day8	day9	day10	day11	day12	day13	day14	day15	day16
3	5	2	0	7	6	0	4	6	8	3	0	7	6	0	5
									8	8	7	7	8	7	7
									5.125	5.125	5.142857	5.857143	5.875	5.714286	5.571429
								the first 8 obtained by
								=COUNTIF(A2:J2,"<>0")
								the 5.125 below it
								=SUM(A2:J2)/J3
								this now runs forward monitoring each 10 day period

<colgroup><col width="64" span="17" style="width:48pt"> </colgroup><tbody>
</tbody>