Excel Sales Forecasting For Dummies, 2nd Edition

Chapter 16

Fine-Tuning a Regression Forecast

IN THIS CHAPTER

Making use of multiple regression

Charting your regression trendline

Looking at your regression forecasts

Other chapters, particularly Chapter 11, look at using regression to forecast one variable, such as sales revenue, from a predictor variable, such as time period or number of sales reps. This sort of analysis is sometimes termed simple regression. Forecasting one variable from more than one predictor is possible and sometimes useful. You may try forecasting sales revenue from both time period and number of sales reps. This approach is termed multiple regression and this chapter shows you how to do it in Excel.

Perhaps the most valuable aspect of Excel charts to come along since the charts themselves were introduced is the trendline. Using the trendline, you can, in one step, display the relationship between your predictor variables and your forecast variable. The trendline can be linear or nonlinear, or it can represent a moving average. The trendline can visually inform you about the direction and strength of the relationship of the predictors to the forecast. You can also choose whether to show underlying information such as the R-squared value and the regression equation itself.

Taking a number that’s produced by a computer with no salt at all is tempting. Take it with a grain or even two. Apart from the usual warnings such as the venerable “garbage in, garbage out,” there are other pitfalls in your path. You’ll ...