Wednesday, May 8, 2013
FORECAST: Sunny Skies!
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:
2. Copy/Drag the above formula to B12:B13
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!