FORECAST: Sunny Skies!

Forecasting can be a tricky business (just ask anyone whose job it is to predict the weather). The problem with coming up with an accurate Forecast is the fact that you are dealing with Historical Data which doesn’t necessarily correlate with the future.

Over time, however, much data is essentially Linear and can, therefore, be used to predict reasonably viable future outcomes.

The good news, of course, is that when calculating Forecasts using historical data in Excel, our favorite spreadsheet program has a Built-in Function for this purpose. The FORECAST function has the following syntax:

=FORECAST(X, Known_Y's, Known_X's)

X is the data point for which you want to predict a value.
Known_Y's is the dependent range of data.
Known_X's is the independent range of data.

Stated in Easier-to-Understand English:

=FORECAST(ValueToForecast, RangeY, RangeX)

ValueToForecast is the point in the future which you need to forecast.
RangeY is the list of values which contain the Historical Data to be used as the basis of the forecast, (Sales Figures are classic…).
RangeX are the intervals used when recording the Historical Data. Months, for instance (Just Be Sure to express the Months as a Number!)

Let’s say that you wish to forecast the Sales for October-December using the Example below. Here is what you should do:

1. In B11 put:
=FORECAST(A11,$B$2:B10,$A$2:A10)

2. Copy/Drag the above formula to B12:B13

Notes:
1. Many Excel users find this Slightly Counterintuitive, so you may wish to spend an extra minute examining the syntax example.
2. Remember that the underlying assumption for your Forecast is Linear Data. If your data does not fit this model, you may wish to explore other avenues.

FORECAST – Give it a try. You may find it says, Sunny Skies Ahead!