Excel’s Forecast
Function is not a complete inventory forecasting system. Forecasting in
inventory
management generally involves removing noise from demand, then
calculating and incorporating trends, seasonality, and events. The Forecast
Function is not going to do all these things for you (technically it could, but
there are better ways to accomplish some of these). But it is a neat little
function that’s easy to use, and it can certainly be a part of your forecasting
system.
According to Microsoft Help
on the Forecast function, “The FORECAST(x, known_y's,known_x's)
function returns the predicted value of the dependent variable (represented in
the data by known_y's) for the specific value, x, of the independent variable (represented
in the data by known_x's) by using a best fit (least squares) linear regression
to predict y values from x values.”
So what exactly does this mean?
Linear
regression is a form of regression
analysis and can be used to calculate a mathematical
relationship between two (or more) sets of data. In forecasting, you would use
this if you thought one set of data could be used to predict another set of
data. For example, if you sold building supplies, you may find that changes in
interest rates can be used to predict sales of your products. This is a classic
example of using regression to calculate a relationship between an external
variable (interest rates) and an internal variable (your sales). However, as we
will see later, you can also use regression to calculate a relationship within
the same set of data.
A typical approach
towards regression analysis involves using regression to determine the
mathematical relationship, but also to help give you an idea of how valid that
relationship is (that’s the analysis part). The Forecast Function skips the
analysis, and just calculates a relationship and automatically applies it to
your output. This makes things easier for the user, but it assumes your
relationship is valid. So essentially, the Forecast function uses linear
regression to predict a value based on a relationship between two sets of data.
Let's see some examples.
In Figure 1A, we have
a spreadsheet that includes the average interest rate over the previous 4 years
and unit sales during that same 4-year period. We also show a predicted
interest rate for the 5th year. We can see in the example that our unit sales
go up as interest rates come down, and go down as interest rates go up. Just
looking at the example, we can probably guess that our sales for year 5 would
be somewhere between 5,000 and 6,000 based on the observed relationship between
interest rates and sales during the previous periods. We can use the Forecast
Function to more precisely quantify this relationship and apply it to the 5th
year.
In Figure 1B, you can
see the Forecast Function being applied. In this case, the formula in cell F4
is =FORECAST(F2,B3:E3,B2:E2) . What we have within the parenthesis is known as
an “argument”. An argument is really just a means of passing parameters on to
the function being used (in this case, the Forecast function ). Each parameter
is separated by a comma. In order for the Forecast Function to work, it needs
to know the value we are using to predict our output (our year 5 sales). In our
case, the parameter (our Year 5 interest rate) is in cell F2, so the first
element of our argument is F2. Next, it needs to know where it can find the
existing values it will use to determine the relationship to apply to F2. First
we need to enter the cells that represent the values of our dependent variable.
In our case, this would be our units sold during the previous 4 years,
therefore we enter B3:E3. Then we need to enter the cells that represent the
values of our predictor variable. In our case, this would be the interest rates
during the previous 4 years, therefore we enter B2:E2) .
The Forecast Function
can now compare the units sold during years 1 through 4 to the interest rates
in those same years, and then apply that relationship to our predicted Year 5
interest rate to get our forecasted sales for Year 5 of 5,654 units.
Figure 1B
In the previous
example, we can look at the graphs to help try to visualize the relationship.
At first glance, it may not look so obvious because we have an inverse
relationship (sales go UP as interest rates go DOWN), but if you mentally
flipped one of the graphs, you would see a very clear relationship. That’s one
of the cool things about the Forecast Function (and regression analysis). It
can easily deal with an inverse relationship.
Now let’s look at another example. In Figure 2A, we see a new set of data. In this example, our interest rates went up and down over the previous 4 years, yet our unit sales showed a consistent upward trend. While it is possible that interest rates had some impact on our sales in this example, it is obvious that there are much more significant factors at play here. By using our forecast function with this data, we return a forecast of 7,118 units for Year 5. I think most of us would look at our sales trend and agree it’s far more likely our sales for Year 5 would be 9,000 units. As I previously mentioned, the Forecast Function assumes the relationship is valid, therefore it produces output based on the best fit it can make out of the data given to it. In other words, if we tell it there is a relationship, it believes us and produces the output accordingly without giving us an error message or any signal that would imply the relationship is very poor. So, be careful what you ask for.
Figure 2A
The previous examples
covered the classic application of regression to forecasting. While all this
sounds pretty slick, this classic application of regression is not as useful as
you might think (you can check out my book for more information on
regression and why it may not be a good choice for your forecasting needs).
But now let’s use the Forecast Function to simply identify trend within a given
set of data. Let’s start by looking at Figure 3A. Here we have demand with a
very obvious trend. Most of us should be able to look at this data and feel
comfortable predicting that the demand in Period 7 will likely be 60 units.
Yet, if you ran this data through the typical forecasting calculations used in
inventory management, you may be surprised at just how poor many of these
calculations are at accounting for trend.
Figure 3A
Since the Forecast
Function requires us to input a dependent variable and a predictor variable,
how do we go about using the Forecast Function if we only have one set of data?
Well, while it is technically true that we have a single set of data (our demand
history), we actually do have a relationship going on within this set of data.
In this case, our relationship is time-based. Therefore, we can use each
period’s demand as a predictor variable for the following period’s demand. So
we just need to tell the Forecast Function to use the demand in Periods 1
through 5 as the existing data for the predictor variable, and use demand in
Periods 2 through 6 as the existing data for the dependent variable. Then tell
it to apply this relationship to the demand in Period 6 to calculate our
forecast for Period 7.
Figure 3B
You can see in Figure
3B, our formula in Cell I3 is =FORECAST(H2,C2:H2,B2:G2) , and it does return a
forecast of 60 units. Obviously this example is not realistic since the demand
is way too neat (no noise). So let’s look at Figure 3C where we apply this same
calculation to some more realistic data.
Figure 3C
I just want to
restate, that while the Forecast Function is useful, it is not a forecasting
system. I typically prefer to have a little more control over exactly how I
apply and extend trends to my forecast. In addition, you would want to first
remove any other elements of your demand that are not related to your base
demand and trend. For example, you would want to remove any effects of
seasonality or events (such as promotions) from your demand before applying the
Forecast Function. You would then apply your seasonality index and any event indexes
to the output of the Forecast Function.
No comments:
Post a Comment