I'm trying to analyze some sales data at work and forecast demand.[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL] 33333]
[/COLOR]
I need help drafting an excel formula to do what I want.
Here are the important points:
1. Products are often out of stock, so I want to assume that any day with zero sales is an out of stock day, and EXCLUDE that day from the calculations.
When products are out of stock, sometimes we are completely out at both stores, and sometimes 1 store will have stock while the other doesn't, so I'd like to run the formula for each Store independently.
Every order has a row in the data, with the date and the qty sold for that order, along with where the sale occurred. There can be multiple orders per day for any given item.
I have a very large data set and would prefer not to modify the data, such as adding in dates with zero's, etc.
The sale dates in our real data also have timestamps so I think those may need to be stripped or otherwise planned for in the formula. We don't need those timestamps.
This is is simplified version of what the data looks like:
2. Products are sold individually, and in kits, so I have a second sheet that shows the qty of each item in the kit.
Sometimes we are out of stock on one component of a kit, so these kits will have their own calculation as to what days they are in stock.
Kits can include from 1 or many individual items, so I prefer to keep this data in a vertical table like this with only a few columns
From the above data:
Blue Widgets were in stock from Jan 3-5 and 16-19
Red Widgets were in stock from Jan 1-5 and on the 25th
When they were both in stock from the 3rd to the 5th, we sold a Kit that included both colors (Each kit contained 1 Blue and 2 Red Widgets)
If there is no sale of an item on a particular day, there is no row or in my data for that item & day combo.
3. I want to use "Trend Adjusted Exponential Smoothing" for the forecast (Assume that products are not seasonal), and I would like to be able to change the alpha & beta as needed.
So here is the output I envision:
To simplify the math temporary, I will just use averaging instead of exponential smoothing in this example:
If you sum up the individual sales of each color, and lookup the Qty sold in the Widget Kits you get the following:
Blue Widgets:
Store #1 , Blue Widgets had sales on 2 days and sold 8 units (4 units per day), plus an additional 30 units sold in the 30 Widget Kits over 3 days (10 units per day), for a total of 14 units per day.
Store #2 , Blue Widgets were in stock for 6 days and sold 73 units in those 6 days, or 12.17 units/day.
Red Widgets:
Store #1 , Red Widgets had sales on 4 days and sold 12 units (3 units per day), plus an additional 60 units sold in the 30 Widget Kits over 3 days (20 units per day), for a total of 23 units per day.
Store #2 , Red Widgets were in stock for 5 days and sold 36 units in those 5 days, or 7.2 units/day.
So assuming no trend, I would the formula to tell me:
Blue:
Store #1 : 14
Store #2 : 12.17
Total: 26.17
Red:
Store #1 : 23
Store #2 : 7.2
Total: 30.2
Once I have this forecast for the next period and for future periods, I believe then I could easily calculate how many of each item to order (based on what's currently on hand, the lead time for the new order, and how many days of stock I would like to have). [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL] 33333]
[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL] 33333]
[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL] 33333](Extra Credit Item #1 ) As mentioned earlier, I want to exclude days of no sales from the calculations, however if I wanted to chart my sales per day, it would be more visual to have those days of zero sales be on the graph, so I'm not sure if there is a way to show that without modifying the raw data.[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL] 33333](Extra Credit Item #2 ) For faster selling or more profitable items, I would like to have an additional buffer or safety stock level instead of just basing the order on the forecast and lead time. Is there a way to input a "[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL] 33333]percentage of certainty" formula that I would be in stock, and have excel calculate the additional inventory amount i need to order to reach that certainty?[/COLOR]
(Extra Credit Item #3 ) Sometimes we intentionally make a temporary price change will will affect the selling rate, but we would like to account for it in the forecast. For example, if something wasn't selling well and we drop the price below cost to liquidate it, I wouldn't want my system to tell me "order alot! These are selling fast." Conversely, If I knew we were running low on an item and it wouldn't be back in stock for a while, I might increase the price to maximize sales but the rate would slow down. I wouldn't my system to tell me "Don't order these, they are selling very slow lately"
Graciously looking forward to any help
[/COLOR]
I need help drafting an excel formula to do what I want.
Here are the important points:
1. Products are often out of stock, so I want to assume that any day with zero sales is an out of stock day, and EXCLUDE that day from the calculations.
When products are out of stock, sometimes we are completely out at both stores, and sometimes 1 store will have stock while the other doesn't, so I'd like to run the formula for each Store independently.
Every order has a row in the data, with the date and the qty sold for that order, along with where the sale occurred. There can be multiple orders per day for any given item.
I have a very large data set and would prefer not to modify the data, such as adding in dates with zero's, etc.
The sale dates in our real data also have timestamps so I think those may need to be stripped or otherwise planned for in the formula. We don't need those timestamps.
This is is simplified version of what the data looks like:
2. Products are sold individually, and in kits, so I have a second sheet that shows the qty of each item in the kit.
Sometimes we are out of stock on one component of a kit, so these kits will have their own calculation as to what days they are in stock.
Kits can include from 1 or many individual items, so I prefer to keep this data in a vertical table like this with only a few columns
From the above data:
Blue Widgets were in stock from Jan 3-5 and 16-19
Red Widgets were in stock from Jan 1-5 and on the 25th
When they were both in stock from the 3rd to the 5th, we sold a Kit that included both colors (Each kit contained 1 Blue and 2 Red Widgets)
If there is no sale of an item on a particular day, there is no row or in my data for that item & day combo.
3. I want to use "Trend Adjusted Exponential Smoothing" for the forecast (Assume that products are not seasonal), and I would like to be able to change the alpha & beta as needed.
So here is the output I envision:
To simplify the math temporary, I will just use averaging instead of exponential smoothing in this example:
If you sum up the individual sales of each color, and lookup the Qty sold in the Widget Kits you get the following:
Blue Widgets:
Store #1 , Blue Widgets had sales on 2 days and sold 8 units (4 units per day), plus an additional 30 units sold in the 30 Widget Kits over 3 days (10 units per day), for a total of 14 units per day.
Store #2 , Blue Widgets were in stock for 6 days and sold 73 units in those 6 days, or 12.17 units/day.
Red Widgets:
Store #1 , Red Widgets had sales on 4 days and sold 12 units (3 units per day), plus an additional 60 units sold in the 30 Widget Kits over 3 days (20 units per day), for a total of 23 units per day.
Store #2 , Red Widgets were in stock for 5 days and sold 36 units in those 5 days, or 7.2 units/day.
So assuming no trend, I would the formula to tell me:
Blue:
Store #1 : 14
Store #2 : 12.17
Total: 26.17
Red:
Store #1 : 23
Store #2 : 7.2
Total: 30.2
Once I have this forecast for the next period and for future periods, I believe then I could easily calculate how many of each item to order (based on what's currently on hand, the lead time for the new order, and how many days of stock I would like to have). [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL] 33333]
[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL] 33333]
[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL] 33333](Extra Credit Item #1 ) As mentioned earlier, I want to exclude days of no sales from the calculations, however if I wanted to chart my sales per day, it would be more visual to have those days of zero sales be on the graph, so I'm not sure if there is a way to show that without modifying the raw data.[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL] 33333](Extra Credit Item #2 ) For faster selling or more profitable items, I would like to have an additional buffer or safety stock level instead of just basing the order on the forecast and lead time. Is there a way to input a "[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL] 33333]percentage of certainty" formula that I would be in stock, and have excel calculate the additional inventory amount i need to order to reach that certainty?[/COLOR]
(Extra Credit Item #3 ) Sometimes we intentionally make a temporary price change will will affect the selling rate, but we would like to account for it in the forecast. For example, if something wasn't selling well and we drop the price below cost to liquidate it, I wouldn't want my system to tell me "order alot! These are selling fast." Conversely, If I knew we were running low on an item and it wouldn't be back in stock for a while, I might increase the price to maximize sales but the rate would slow down. I wouldn't my system to tell me "Don't order these, they are selling very slow lately"
Graciously looking forward to any help
Last edited by a moderator:
