If you use Excel as much as I do, you know that forecasting models can be complex and take a fair amount of time to set up. But if you need a quick forecast model, here is a neat trick: use the FORECAST function.
This FORECAST function only works if you have a time series. For example, let’s say you have monthly revenue numbers for the part of the year and you want to forecast the remaining. Assuming revenue is fairly stable over time, you can use the FORECAST function formula without having to calculate the statistical formula.
Example:
Microsoft has a guide on the the 5 different types of Excel forecast functions.
An advanced version of this function is the Forecast Sheet function which gives a confidence interval table and a pretty graph.
A few words of caution, using linear regression to do a forecast implies the past can predict the future and depending on the correlation of the data to time, it can have varying degrees of results. Also, if you have a limited data sent (less than 15 data points), then you also lose correlation. I recommend using this trick only as an improvement over doing a run rate based forecast or % growth forecast.*
That said, this trick can be an easy way to impress your boss and coworkers by saying you used a linear regression forecast model 🙂
* For you stat people, yes I know this trick violates most rules of statistical significance, p-values, and other principles of stats if the sample size is too small or if there is not a high enough correlation. It’s a quick trick to be used in a pinch that’s an improvement over a run rate.
Rhett Weller 