Excel Sales Forecasting For Dummies

Conrad Carlberg

• Publisher: For Dummies
• Year: 2016
• Print ISBN:
  9781119291428
• Edition: 2
• eBook ISBN:
  9781119291435
• Topic: Computer Science
• Subtopic: Desktop Applications
• Index: Computer Science

Excel at predicting sales and forecasting trends using Microsoft Excel!

If you're a sales or marketing professional, you know that forecasting sales is one of the biggest challenges you face on the job. Unlike other books on the subject, Excel Sales Forecasting For Dummies, 2^nd Edition leaves arcane business school terms and complex algebraic equations at the door, focusing instead on what you can do right now to utilize the world's most popular spreadsheet program to produce forecasts you can rely on.

Loaded with confidence boosters for anyone who succumbs to sweaty palms when sales predictions are mentioned, this trusted guide show you how to use the many tools Excel provides to arrange your past data, set up lists and pivot tables, use moving averages, and so much more. Before you know it, you'll become a forecaster par excellence—even if numbers aren't your jam.

• Choose the right forecasting method
• Find relationships in your data
• Predict seasonal sales
• Filter lists or turn them into charts

Consider this guide your crystal ball—and start predicting the future with confidence and ease!

Part 1

Understanding Sales Forecasting and How Excel Can Help

IN THIS PART …

In Part 1, I talk about why forecasting sales can help your business in ways that seem to have little to do with sales. Part 1 also tells you why forecasting isn’t simply a matter of using formulas to crunch numbers. But, face it, some numbers have to be crunched, and here you find an introduction to baselines — which are the basis for the number-crunching. I try to convince you that forecasting really does work, and I back up that claim by showing you how.

Chapter 1

A Forecasting Overview

IN THIS CHAPTER

Knowing the different methods of forecasting

Arranging your data in an order Excel can use

Getting acquainted with the Analysis ToolPak

Going it alone

A sales forecast is like a weather forecast: It’s an educated guess at what the future will bring. You can forecast all sorts of things — poppy-seed sales, stock market futures, the weather — in all sorts of ways: You can make your own best guess; you can compile and composite other people’s guesses; or you can forecast on the basis of wishful thinking.

Unfortunately, none of these options is truly acceptable. If you want to make better forecasts, you need to take advantage of some better options. And there are different ways to forecast, ways that have proven their accuracy over and over. They take a little more time to prepare than guessing does, but in the long run I’ve spent more time explaining bad guesses than doing the forecasts right in the first place.

Microsoft Excel was originally developed as a spreadsheet application, suited to figuring payment amounts, interest rates, account balances, and so on. But as Microsoft added more and more functions — for example, AVERAGE and TREND and inventory-management stuff — Excel became more of a multipurpose analyst than a single-purpose calculator.

Excel has the tools you need to make forecasts, whether you want to prepare something quick and dirty (and who doesn’t from time to time?) or something sophisticated enough for a boardroom presentation.

The tools are there. You just need to know which tool to choose for which situation and, of course, how to use it. You need to know how to arrange data for the tool. And you need to know how to interpret what the tool tells you — whether that tool’s a basic one or something more advanced.

Understanding Excel Forecasts

If you want to forecast the future — next quarter’s sales, for example — you need to get a handle on what’s happened in the past. So you always start with what’s called a baseline (that is, past history — how many poppy seeds a company sold during each of the last ten years, where the market futures wound up each of the last 12 months, what the daily high temperature was year-to-date).

Unless you’re going to just roll the dice and make a guess, you need a baseline for a forecast. Today follows yesterday. What happens tomorrow generally follows the pattern of what happened today, last week, last month, last quarter, last year. If you look at what’s already happened, you’re taking a solid step toward forecasting what’s going to happen next. (Part 1 of this book talks about forecast baselines and why they work.)

An Excel forecast isn’t any different from forecasts you make with a specialized forecasting program. But Excel is particularly useful for making sales forecasts, for a variety of reasons:

• You often have sales history recorded in an Excel worksheet. When you already keep your sales history in Excel, basing your forecast on the existing sales history is easy — you’ve already got your hands on it.
• Excel’s charting features make it much easier to visualize what’s going on in your sales history and how that history defines your forecasts.
• Excel has tools (found in what’s called the Data Analysis add-in) that make generating forecasts easier. You still have to know what you’re doing and what the tools are doing — you don’t want to just jam the numbers through some analysis tool and take the result at face value, without understanding what the tool’s up to. But that’s what this book is here for.
• You can take more control over how the forecast is created by skipping the Data Analysis add-in’s forecasting tools and entering the formulas yourself. As you get more experience with forecasting, you’ll probably find yourself doing that more and more.

You can choose from several different forecasting methods, and it’s here that judgment begins. The three most frequently used methods, in no special order, are moving averages, exponential smoothing, and regression.

Method #1: Moving averages

Moving averages may be your best choice if you have no source of information other than sales history — but you do need to know your baseline sales history. Later in this chapter, I show you more of the logic behind using moving averages. The underlying idea is that market forces push your sales up or down. By averaging your sales results from month to month, quarter to quarter, or year to year, you can get a better idea of the longer-term trend that’s influencing your sales results.

For example, you find the average sales results of the last three months of last year — October, November, and December. Then you find the average of the next three-month period — November, December, and January (and then December, January, and February; and so on). Now you’re getting an idea of the general direction that your sales are taking. The averaging process evens out the bumps you get from discouraging economic news or temporary boomlets.

Method #2: Exponential smoothing

Exponential smoothing is closely related to moving averages. Just as with moving averages, exponential smoothing uses past history to forecast the future. You use what happened last week, last month, and last year to forecast what will happen next week, next month, or next year.

The difference is that when you use smoothing, you take into account how bad your previous forecast was — that is, you admit that the forecast was a little screwed up. (Get used to that — it happens.) The nice thing about exponential smoothing is that you take the error in your last forecast and use that error, so you hope, to improve your next forecast.

If your last forecast was too low, exponential smoothing kicks your next forecast up. If your last forecast was too high, exponential smoothing kicks the next one down.

The basic idea is that exponential smoothing corrects your next forecast in a way that would have made your prior forecast a better one. That’s a good idea, and it usually works well.

Method #3: Regression

When you use regression to make a forecast, you’re relying on one variable to predict another. For example, when the Federal Reserve raises short-term interest rates, you might rely on that variable to forecast what’s going to happen to bond prices or the cost of mortgages. In contrast to moving averages or exponential smoothing, regression relies on a different variable to tell you what’s likely to happen next — something other than your own sales history.

Getting the Data Ready

Which method of forecasting you use does make a difference, but regardless of your choice, in Excel you have to set up your baseline data in a particular way. Excel prefers it if your data is in the form of a table. In Part 2, I fill you in on how to arrange your data so that it best feeds your forecasts, but following is a quick overview.

Using tables

There’s nothing mysterious about an Excel table. A table is something very much like a database. Your Excel worksheet has columns and rows, and if you put a table there, you just need to manage three requirements:

• Keep different variables in different columns. For example, you can put sales dates in one column, sales amounts in another column, sales reps’ names in another, product lines in yet another.
• Keep different records in different rows. When it comes to recording sales information, keep different sales records in different rows. Put information about a sale that was made on January 15 in one row, and information about a sale made on January 16 in a different row.
• Put the names of the variables in the table’s fir...

Table of contents

• Cover
• Title Page
• Table of Contents
• Introduction
• Part 1: Understanding Sales Forecasting and How Excel Can Help
• Part 2: Organizing the Data
• Part 3: Making a Basic Forecast
• Part 4: Making Advanced Forecasts
• Part 5: The Part of Tens
• About the Author
• Advertisement Page
• Connect with Dummies
• End User License Agreement