By Conrad Carlberg

Part of Excel Sales Forecasting For Dummies Cheat Sheet

Excel has many great tools for sales forecasting. Knowing the following functions is helpful to get your data in order. Check out the following handy forecasting functions.

Function What It Does
CORREL The worksheet version of the Data Analysis add-in’s Correlation tool. The difference is that CORREL recalculates when the input data changes, and the Correlation tool doesn’t. Example: =CORREL(A1:A50, B1:B50). Also, CORREL gives you only one correlation, but the Correlation tool can give you a whole matrix of correlations.
LINEST You can use this function rather than the Data Analysis add-in’s Regression tool. (The function’s name is an abbreviation of linear estimate.) For simple regression, select a range of two columns and five rows. You need to array-enter this function. Type, for example, =LINEST(A1:A50, B1:B50,,TRUE) and then press Ctrl+Shift+Enter.
TREND This function is handy because it gives you forecast values directly, whereas LINEST gives you an equation that you have to use to get the forecast. For example, use =TREND(A1:A50,B1:B50,B51) where you’re forecasting a new value on the basis of what’s in B51.
FORECAST The FORECAST function is similar to the TREND function. The syntax is a little different. For example, use =FORECAST(B51,A1:A50,B1:B50) where you’re forecasting a new value on the basis of the value in B51. Also, FORECAST handles only one predictor, but TREND can handle multiple predictors.