Forecasting

Forecasting is a vital function in nearly every business. Are your company regional sales going to rise, fall, or remain about the same? Are your office expenses going to track with corporate budgets? Are your losses going to be manageable in the next fiscal year? All such considerations are essential to commercial survival.

Forecasting can, of course, be a bit tricky, (just ask meteorologists…), since you are dealing with Historical Data which doesn’t necessarily correlate with the future. Over a significant amount of time, however, much data is essentially Linear and can, therefore, be used to predict reasonably viable future outcomes. 

Since there is validity in much historical data, Microsoft has created the ingeniously-named, “FORECAST” function as a built-in tool that can calculate linear forecasts. The syntax for this function is as follows:

=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!)

Stated in Easier-to-Understand English:

=FORECAST(ValueToForecast, RangeY, RangeX)

For Example, let’s say that you wish to Forecast the Sales for the 4th quarter

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 

A Couple of Notes: 
1. Many Excel users find this syntax somewhat Counter-intuitive, so you may wish to spend an extra minute examining the example. 
2. Remember that the underlying assumption for your Forecast is Linear Data. If your data does not fit this model, you will want to explore other potential solutions. 

FORECAST: Limited in its effectiveness, but if used correctly it can be a useful tool in a great many business applications.