Demand Forecast Basics: Trends2 min read

Excel has proven to be one of the most powerful and lasting data analysis tools, I have 3 tips to improve your forecasts in excel.

1 – Trend-lines

Who doesn’t love slope-intercept formulas? *fixes glasses*

Whether you enjoyed high school math or not, this form of problem solving is the foundation of predicting future demand of certain products you sell.

Don’t worry though! If you have a basic understanding of excel we can figure this out together

First we’ll start with our data-set. For this example we’ll look at the NBA’s pace of play over the past 15 seasons:

After this, you’re going to want to create a scatter-plot of the data and add a trend-line to the chart:

Here’s the key part of the process, you’re going to want to get the equation for that trend-line which is available in the formatting window:

In the case we go with a linear trend we get y = 0.5364x – 985.23. (The Warriors/Rockets style of play has skewed the data, but that’s a whole other conversation.)

From here, you can sub x with the year to project the amount of possessions per game (forecast).

2 – Seasonality Index

Not all months are passed the same way.

As you can see above, Best Buy (BBY) reports much higher revenue after the 4th quarters

But to have this index effect the data we’ve already started. We will apply a relaltive value of 1 (binary) as we still adhere to the trend-line.

quarter normalized index

1 – 1.3955

2 – 0.8497

3 – 0.8610

4 – 0.8939

of total 1.0000

This tread-line is fairly flat as BBY may be at it’s market saturation…

3-Bias?

Bias compares our formula to real data as sales are processed and improves our excel forecasts. Unless there are unforeseen events, we should just be able to tweak the ratio on the bias after the 2nd-3rd month of the annual plan.