Excel Sales Forecasting For Dummies Cheat Sheet - dummies
Cheat Sheet

Excel Sales Forecasting For Dummies Cheat Sheet

From Excel Sales Forecasting For Dummies, 2nd Edition

By Conrad Carlberg

When you start to learn forecasting, it’s often a good idea to lean on the Excel tools in the Data Analysis add-in. But their reach is pretty limited and before too long you’re likely to find yourself taking advantage of Excel’s worksheet functions directly. When you find yourself using all the inferential statistics that come along with the LINEST function, you’ll know that it’s time to lay out your baseline for a formal forecast.

6 Excel Data Analysis Add-in Tools

The Data Analysis add-in, formerly known as the Analysis ToolPak, enters formulas on your behalf so that you can concentrate on what’s going on with your data. It has three different tools that are directly useful in forecasting — Moving Average, Exponential Smoothing, and Regression — as well as several others that can be of help. Here’s a list of some of the tools that are part of the Data Analysis add-in.

Tool What It Does
ANOVA There are actually three different ANOVA tools. None is specifically useful for forecasting, but each of the tools can help you understand the data set that underlies your forecast. The ANOVA tools help you distinguish among samples — for example, do people who live in Tennessee like a particular brand of car better than those who live in Vermont?
Correlation This tool is an important one, regardless of the method you use to create a forecast. If you have more than one variable, it can tell you how strongly the two variables are related (plus or minus 1.0 is strong, 0.0 means no relationship). If you have only one variable, it can tell you how strongly one time period is related to another.
Descriptive Statistics Use the Descriptive Statistics tool to get a handle on things like the average and the standard deviation of your data. Understanding these basic statistics is important so you know what’s going on with your forecasts.
Exponential Smoothing This tool’s name sounds ominous and intimidating, which the tool is not. When you have just one variable — something such as sales revenue or unit sales — you look to a previous actual value to predict the next one (maybe the previous month, or the same month in the previous year). All this tool does is adjust the next forecast by using the error in the prior forecast.
Moving Average A moving average shows the average of results over time. The first one might be the average for January, February, and March; the second would then be the average for February, March, and April; and so on. This method of forecasting tends to focus on the signal (what’s really going on in the baseline) and to minimize the noise (random fluctuations in the baseline).
Regression Regression is closely related to correlation. Use this tool to forecast one variable (such as sales) from another (such as date or advertising). It gives you a couple of numbers to use in an equation, like Sales = 50000 + (10 * Date).

4 Excel Forecasting Functions

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.

What You Get Out of the Excel LINEST Function for Sales Forecasting

Excel’s LINEST function is a handy tool for sales forecasting. Knowing what you can do with it will make your forecasting endeavors easy work. Here is a quick rundown on Excel’s LINEST function, row by row:

  Column 1 Column 2
Row 1 The coefficient you multiply times the X values The intercept
Row 2 The standard error of the coefficient The standard error of the intercept
Row 3 The R-squared value, or coefficient of determination The standard error of estimate
Row 4 The F-ratio The residual degrees of freedom
Row 5 The sum of squares for the regression The sum of squares for the residual

Setting Up Your Sales Forecasting Baseline in Excel

It is a good idea to set up your sales forecasting baseline in Excel. This table gives you instructions for dealing with issues that might arise as you set up your Excel baseline:

The Issue How to Deal with the Issue
Order Put your historical data in chronological order, earliest to latest.
Time periods Use time periods of approximately equal length: all weeks, all months, all quarters, or all years.
Same location in time If you’re sampling, then sample from the same place. Don’t take January 1, February 15, March 21. Instead, use January 1, February 1, March 1, and so on.
Missing data Missing data is not allowed. If you have every month except, say, June, find out what June’s sales were. If you can’t, get the best estimate possible — or start your forecasting with July.