In addition to what you’re reading right now, this product also comes with a free access-anywhere Cheat Sheet that tells you about Excel data analysis add-in tools, how to use forecasting functions, what you get out of the Excel LINEST function, and what to do when setting up your baseline in Excel. To get this Cheat Sheet, simply go to www.dummies.com and search for “Excel Sales Forecasting For Dummies Cheat Sheet” in the Search box.
I’ve also provided files for each chapter so that you can try out what I’m talking about in the leisure of your own home. You can find these files at www.dummies.com/go/excelsalesforecasting.
Are you looking for information about the basics of forecasting? Why it works? Why it’s not just a self-licking ice cream cone? Start at Chapter 1.
Do you want to know how to put your data together in a workbook? Head to Chapter 5 to find out more about baselines, and then check out the chapters on using tables in Excel.
If you’re already up on forecasting basics and tables, head for Chapter 8, where you’ll see how to use pivot tables to set up the baseline for your forecast.
And if you know all that stuff already, just go to Chapter 10 and start looking at how to manage your forecasts yourself, without relying on the various tools that take care of things for you. You’ll be glad you did.
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.
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