Go to Consulting Services Pages



Your source for information on
Inventory Management and Warehouse Operations.

InventoryOps.com is provided as a free service by Inventory Operations Consulting LLC.


Using the FORECAST Function in Excel (and Open Office Calc)

By Dave Piasecki

Let me start out by saying that 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.

Figure 1A

Figure 1A

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

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

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

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

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

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.

You can also play around with your inputs to get a specific desired result. For example, you may want to try first smoothing your demand history (through a moving average, weighted moving average, or exponential smoothing), and using that is the predictor variable instead of the raw demand. For more information of Forecasting, check out my book Inventory Management Explained.

Using the Forecast Function in Open Office Calc.

For users of Openoffice.org Calc , the Forecast function works pretty much the same as in Excel. However, there is a slight difference in the syntax used in Calc. Wherever you would use a comma in an argument in an Excel Function, you would instead use a semicolon in Calc.

So, instead of the Excel Formula

=FORECAST(F2,B3:E3,B2:E2)

You would enter

=FORECAST(F2;B3:E3;B2:E2)

Go to Articles Page for more articles by Dave Piasecki.

Dave Piasecki, is owner/operator of Inventory Operations Consulting LLC, a consulting firm providing services related to inventory management, material handling, and warehouse operations. He has over 25 years experience in operations management and can be reached through his website (http://www.inventoryops.com), where he maintains additional relevant information.


My Business

Inventory Operations Consulting

Inventory Operations Consulting LLC provides Fast, Affordable, Expert assistance with Inventory Management and Warehouse Operations.

E-consulting options are available.



My Books

Inventory Accuracy Inventory Management Explained





Sponsored Links