The Regression Tool in the Excel Data Analysis Add-In

By Conrad Carlberg

The term regression doesn’t sound as bad as exponential smoothing, but it is more complicated, at least in terms of the math. And that’s why the Regression tool in the Data Analysis add-in is convenient. The add-in takes responsibility for the math, just as it does with moving averages and exponential smoothing.

You still have to give a good baseline to the tools in the Data Analysis add-in to get accurate results.

Here’s a quick look at forecasting with regression.

The idea behind regression is that one variable has a relationship with another variable. When you’re a kid, for example, your height tends to have a relationship to your age. So if you want to forecast how tall you’ll be next year — at least, until you quit growing — you can check how old you’ll be next year.

Of course, people differ. When they’re 15 years old, some people are 5 feet tall, some are 6 feet tall. On average, though, you can forecast with some confidence how tall someone will be at age 15. (And you can almost certainly forecast that a newborn kidlet is going to be under 2 feet tall.)

The same holds true with sales forecasting. Suppose your company sells consumer products. It’s a good bet that the more advertising you do, the more you’ll sell. At least it’s worth checking out whether there’s a relationship between the size of your advertising budget and the size of your sales revenue. If you find that there’s a dependable relationship — and if you know how much your company is willing to spend on advertising — you’re in a good position to forecast your sales.

Or suppose your company markets a specialty product, such as fire doors. (A fire door is one that’s supposed to be resistant to fire for some period of time, and there are a lot of them in office buildings.) Unlike consumer products, something such as a fire door doesn’t have to be a particular off-the-shelf color or have a fresher-than-fresh aroma. If you’re buying fire doors, you want to get the ones that meet the specs and are the cheapest.

So if you’re selling fire doors, as long as your product meets the specs, you’d want to have a look at the relationship between the price of fire doors and how many are sold. Then you check with your marketing department to find out how much they want you to charge per door, and you can make your forecast accordingly.

The point is that more often than not you can find a dependable relationship between one variable (advertising dollars or unit price) and another (usually, sales revenue or units sold).

You use Excel’s tools to quantify that relationship. In the case of regression forecasts, you give Excel a couple of baselines:

  • Historical advertising expenses and historical sales revenues
  • How much you charged per fire door and how many doors you sold, for example

If you give Excel good baselines, it will come back to you with a formula.

  • Excel will give you a number to multiply times how much you expect to spend on advertising, and the result will be your expected sales revenue.
  • Or, for example, Excel will give you a number to multiply times the unit cost per door, and the result will be the number of doors you can expect to sell.

It’s just a touch more complicated than that. Excel also gives you a number, called a constant, that you need to add to the result of the multiplication. But, you can get Excel to do that for you.