Complex? Inventory Forecasting

thesauce

New Member
Joined
Sep 2, 2017
Messages
5
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 :)


 
Last edited by a moderator:

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,813
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Please also note that we do not allow offering, or accepting, money for help on the forum.
 

thesauce

New Member
Joined
Sep 2, 2017
Messages
5
You likely have some in-depth that needs that real-professional help and support may be useful.
I must suggest that you check http://www.mrexcel.com/consult.html
an alternative would be https://powerpivotpro.com/a-new-kind-of-company/

Thank you for the suggestions. I went to call MrExcel consulting after seeing your post, but my schedule was too hectic today to have the time to properly explain my needs on the call, so I will try Monday.

I did emailed the second as well, just ask hourly rate, and the rates were $$$$. More than I was expecting, but thank you again for the help.
 

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,707
The more information you can share, sometimes just data layout and reasonable sample data, the more help can be provided. You might want to post directly in the PowerBI channel too.
Do it yourself? Mike Girvin offers tons on his YouTube channel, ExcelIsFun.
If you're using a POS or accounting application all ready, you may find a user group there with a solution.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,658
Messages
5,573,447
Members
412,529
Latest member
cTatch
Top