Exploring Excel Functions 8: Predicting the Future

• By Peter Aitken
• Jun 30, 2006

Like this article? We recommend
Easy Microsoft Excel 2003, 2nd Edition

No one can predict the future, but that does not stop a lot of people from making guesses. In some situations, particularly when you have reason to believe that the future will to some degree reflect the past, you might even be able to make an informed guess. Excel provides you with several functions that can help with your predictions, and we’ll look at two of them in this article.

Predicting with FORECAST()

The FORECAST() function forecasts unknown data based on existing data. FORECAST() works with data that comes in pairs, usually called X,Y pairs. Lots of data comes this way. For example, if you have height and weight measurements from a bunch of people, you have a set of X,Y pairs, with X as height and Y as weight. Likewise, if you ask people about their educational level and annual income, you also have X,Y data pairs.

How does FORECAST() work? It requires that you have a set of known X,Y data pairs. Then, given an X value for which you do not have the Y, it predicts the Y value based on the known data.

There is one restriction, however. FORECAST() is designed to work with linear data. This means that if you plot the known X,Y values on an XY (Scatter) chart, the points will fall approximately on a straight line. This is not a serious restriction because a lot of data is linear.

Here is the syntax for FORECAST():

=FORECAST(x, known_y_values, known_x_values)

• X is the X value for which you want to predict the Y value.
• Known_y_values is a range containing the known Y data values.
• Known_x_values is a range containing the known X data values.

You have probably figured out that the known_y_values and known_x_values ranges must each contain the same number of values, and they must match up—that is, the first X value goes with the first Y value, and so on.

The function returns the Y value that is predicted for the specified X value, based on the known X and Y data. Let’s look at an example.

Suppose that your company has been keeping track of the amount of sales each month and how it relates to the amount that is spent on advertising. The data and an XY plot of the data are shown in Figure 1. The plotted points fall fairly well along a straight line, so you know that this data is appropriate for use with the FORECAST() function.

Figure 1 Linear data that can be used with the FORECAST() function

Your boss wants to know the amount of sales that can be expected if the advertising budget is increased to $2,500. The FORECAST() function is just what you need. Here’s what you need to do:

1. Enter the projected advertising budget, 2500, in a cell. I used A9 in this example to facilitate plotting, but you can use any other cell if you prefer.
2. Enter the following formula in the cell where you want the result displayed (I used C9):

=FORECAST(B9,C3:C8,B3:B8).

The finished worksheet is shown in Figure 2, with the new data point included in the chart. You can see that the prediction is that with $2,500 of advertising spending you can expect $228,768 in sales.

Figure 2 The result of the FORECAST() function is displayed in cell C9 and on the chart.