When I was attending the University of St. Thomas, one of the majors I pursued for a period of time was Finance. Then came the “Finance II Project”. This was some sort of financial analysis project of a public company. I remember two things about this project: my subject company was Kimberly Clark and I got so lost in the “forecasting” section it wasn’t even funny. I finished the project, received some marginal grade and promptly switched my major to Operations Management (Supply Chain) where things made sense.
Shortly after changing majors, I landed my first internship at ADC Telecommunications. I knew I was incredibly fortunate to work for this company as they were as close to APICS for operations as you could get. I also had some great mentors in the supply chain area, specifically in forecasting. [Note to interns and grads, find a solid mentor or two who you can use as a sounding board.] I was still a little tender from my Kimberly Clark project but I had to figure out what Supply Chain Forecasting was all about.
Let the Learning Begin!
I sat down with my forecasting mentor (who I hope is reading this and bless his patience) and he white-boarded (for I don’t know how long) trying to help me understand supply chain forecasting concepts. At the time, we were using SAP’s standard forecasting module and it had a plethora of fancy forecast algorithms you could use depending on your situation. In reality, forecasting month after month and changing models that I didn’t really understand in the first place didn’t give me a ton of confidence in the forecasts I was generating. Then if a number didn’t align, it was difficult to figure out what caused the numerical hiccup, let alone explain to the stakeholders what I was thinking.
Why Forecast?
But let’s hit ‘pause’ for a moment. Why do we forecast at all? Very simply put, we forecast because the time (lead time) it takes to produce our product is longer than our customers’ willingness to wait. If we don’t have product available in a reasonable amount of time, customers will go somewhere else. Think of it like this, if you go to Home Depot to buy a shovel and they tell you it will take 12 weeks to get it, you’re not going to wait, you’re going to Menards.
A Simple and Effective Model
Fast forward several years; I’ve left ADC and I’m at a different company, managing the forecasting group. We came up with a very simple, yet extremely effective model. It had three inputs: average historical sales over some period of time, a trend profile and a seasonality profile.
So how do we create a simple forecast? Let’s start with our old friend the “average.” This is one of the most basic forecasting models you can use. Pick a historical time horizon: 12-, 18-, 24- or 36-months back. In the organizations I’ve been with going beyond 2 years doesn’t give you any better data so 24 months is as far back as I will go. Averages by themselves can be a “good enough” forecast. But we can make them better in businesses that have seasonality, which many do.
After you’ve established the historical timeline, you need to add your trend profile. This sounds fancy, but it isn’t. A trend profile is useful in a company or product line that is growing or shrinking at more than ~5% per year. It is used to project the product’s upward, downward or flatward (not a word, I know) trend. The easiest way to calculate a trend profile is to do a straight year-over-year comparison. Let’s say we sold 1,500 units last year. You simply compare it to the previous year’s sales of 1,000 units and come up with a 50% increase. If you expect that trend to continue, you would add 50% to last year’s total coming up with 1500 x 1.5 = 2,250 units for the next 12 months. See? Forecasting is easy!
The final step is applying the seasonality profile. Just the term, “seasonality profile” sounds complicated. It is far from. A seasonal profile is just calculating the proportion of a particular month compared to the entire year – again, using historical data. Using the same numbers from the previous example, let’s say you sold 1,500 units in the last 12 months and specifically 150 units in January. January would have a seasonal factor of 150/1500 or 10%. If you sold 300 in February, February’s seasonal factor would be 300/1500 or 20%. If you sold 263 units in March, March’s seasonal profile would be 263/1500 or 17.5%. One note of caution: doing a seasonal profile at the material level may give you results that jump around more than you want. I would suggest to put your seasonal profiles at a higher level, like material group or product line and then “disaggregate” that forecast down to the material level. That’s another blog for another day.
An Example
So let’s put all of those concepts, averages, trends and seasonal profiles together and do a simple forecast. Based on the average of the last two years you sold 1,500 units per year. That’s your average. Your YOY growth was 50% so you believe you’re going to sell a total of 2250 units in the next 12 months (1500 x 1.5). You now apply your seasonal profiles and your forecasts going forward are calculated as:
- January: 2250 x 10%: 225
- February: 2250 x 20%: 450
- March: 2250 x 17.5% = 394
That’s it, you’ve created a very nice baseline forecast!
You’re using facts (last year’s performance) to project the future. If the trends and seasonality continue, you will have the materials in place for your customers. Customers will be happy and they will return to you to buy more products. Customers buy more products and your company grows. Your company grows and hopefully everyone gets a raise and a nice bonus.
Again, this example outlines a baseline. There are other factors such as projects (one-time big deals) that can influence these numbers and you can just add those on accordingly.
Happy Forecasting
I hope this has given you a little more confidence and/or understanding to calculating forecasts. I promise this is just as effective as using a fancy model that comes with an expensive ERP add on. The advantage? You know how this is calculated and you can explain it at any level in your organization.