Outlook: No Cloudy Skies!

No we’re not talking about Microsoft Outlook here, nor are we dreaming of plans for the weekend.  What we are talking about is taking a refreshed look at Forecasting. 

As we discussed back in May of 2013, Forecasting can be very challenging.  Is it going to be cloudy, sunny, a mix?  Are our sales in a particular region going to rise, fall, or remain about the same?  Unlike such esoteric fields such as theoretical physics, the potential problem with most forecasting is the fact that we are dealing with historical data which Doesn’t Necessarily correlate with future outcomes.

Historical data can be valid, however, and happily, Excel has a built-in function that is cleverly named, “FORECAST” (where do the people at Microsoft come up with these ingenious names?!?) that can calculate linear forecasts.  The syntax for this function (stated simply) 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!)

With reference to the example below (and assuming no seasonality…), let’s say that you wish to forecast the Customer Service Calls for July. Here is what you should do:

 

1.  In cell C8 simple put:  =FORECAST(B8,C2:C7,B2:B7)

2.  Press Enter
3.  Bamm! Instant Forecast! (You should get approximately 1,403…)

A Couple of Worthwhile Notes:

1. The syntax of FORECAST can strike some users as slightly counterintuitive, so you may want to be patient with yourself while getting used to it.
2. The underlying assumption for your Forecast is, of course, Linear Data. If your data does not fit this model, (Seasonality or other factors) you may wish to explore other solutions.

The FORECAST function – Outlook: No more cloudy skies ahead!