Excel Sales Forecasting For Dummies - Conrad Carlberg - ebook

Excel Sales Forecasting For Dummies ebook

Conrad Carlberg

0,0
94,99 zł

Opis

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, 2nd 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!

Ebooka przeczytasz w aplikacjach Legimi na:

Androidzie
iOS
czytnikach certyfikowanych
przez Legimi
Windows
10
Windows
Phone

Liczba stron: 531




Excel® Sales Forecasting For Dummies®, 2nd Edition

Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.com

Copyright © 2016 by John Wiley & Sons, Inc., Hoboken, New Jersey

Published simultaneously in Canada

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without the prior written permission of the Publisher. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions.

Trademarks: Wiley, For Dummies, the Dummies Man logo, Dummies.com, Making Everything Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and may not be used without written permission. Excel is a registered trademark of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ.

For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002. For technical support, please visit https://hub.wiley.com/community/support/dummies.

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.

Library of Congress Control Number: 2016942855

ISBN: 978-1-119-29142-8

ISBN 978-1-119-29143-5 (ePub); ISBN 978-1-119-29144-2 (ePDF)

Excel® Sales Forecasting For Dummies®

To view this book's Cheat Sheet, simply go to www.dummies.com and search for “Excel Sales Forecasting For Dummies Cheat Sheet” in the Search box.

Table of Contents

Cover

Introduction

About This Book

Foolish Assumptions

Icons Used in This Book

Beyond the Book

Where to Go from Here

Part 1: Understanding Sales Forecasting and How Excel Can Help

Chapter 1: A Forecasting Overview

Understanding Excel Forecasts

Getting the Data Ready

Making Basic Forecasts

Charting Your Data

Forecasting with Advanced Tools

Chapter 2: Forecasting: The Basic Issues

Why Forecast?

Talking the Talk: Basic Forecasting Lingo

Understanding the Baseline

Setting Up Your Forecast

Using Your Revenue and Cost Data

Chapter 3: Understanding Baselines

Using Qualitative Data

Recovering from Mistakes in Sales Forecasting

Recognizing Trends and Seasons

Chapter 4: Predicting the Future: Why Forecasting Works

Understanding Trends

Matchmaker, Matchmaker: Finding Relationships in the Data

Part 2: Organizing the Data

Chapter 5: Choosing Your Data: How to Get a Good Baseline

Early to Bed: Getting Your Figures in Order

Staying Inside the Lines: Why Time Periods Matter

Spacing Time Periods Equally

Chapter 6: Setting Up Tables in Excel

Understanding Table Structures

Creating a Table

Filtering Lists

Importing Data from a Database to an Excel Table

Chapter 7: Working with Tables in Excel

Turning Tables into Charts

Using the Data Analysis Add-in with Tables

Avoiding the Data Analysis Add-in’s Traps

Part 3: Making a Basic Forecast

Chapter 8: Summarizing Sales Data with Pivot Tables

Understanding Pivot Tables

Building the Pivot Table

Grouping Records

Avoiding Grief in Excel Pivot Tables

Chapter 9: Charting Your Baseline: It’s a Good Idea

Digging into a Baseline

Making Your Data Dance with Pivot Charts

Using Two Value Axes

Chapter 10: Forecasting with Excel’s Data Analysis Add-in

Installing Add-ins: Is the Add-in Even There?

Using Moving Averages

Using Exponential Smoothing

Using the Regression Tool

Chapter 11: Basing Forecasts on Regression

Deciding to Use the Regression Tool

Understanding the Data Analysis Add-in’s Regression Tool

Using Multiple Regression

Part 4: Making Advanced Forecasts

Chapter 12: Entering the Formulas Yourself

About Excel Formulas

Using Insert Function

Understanding Array Formulas

Using the Regression Functions to Forecast

Chapter 13: Using Moving Averages

Choosing the Length of the Moving Average

Reacting Quickly versus Modeling Noise

Using the Data Analysis Add-in to Get Moving Averages

Chapter 14: Changing Horses: From Moving Averages to Smoothing

Losing Early Averages

Understanding Correlation

Understanding Autocorrelation

Chapter 15: Smoothing: How You Profit from Your Mistakes

Correcting Errors: The Idea Behind Smoothing

Using the Smoothing Tool’s Formula

Finding the Smoothing Constant

Problems with Exponential Smoothing

Chapter 16: Fine-Tuning a Regression Forecast

Doing Multiple Regression

Getting a Regression Trendline into a Chart

Evaluating Regression Forecasts

Chapter 17: Managing Trends

Knowing Why You May Want to Remove the Trend from a Baseline

Getting a Baseline to Stand Still

And All the King’s Men: Putting a Baseline Together Again

Chapter 18: Same Time Last Year: Forecasting Seasonal Sales

Doing Simple Seasonal Exponential Smoothing

Getting Farther into the Baseline

Finishing the Forecast

Part 5: The Part of Tens

Chapter 19: Ten Fun Facts to Know and Tell about Array Formulas

Entering Array Formulas

Using the Shift Key

Noticing the Curly Brackets

Using INDEX to Extract a Value from an Array Formula’s Result

A Quick Route to Unique Values

Selecting the Range: LINEST

Selecting the Range: TRANSPOSE

Selecting a Range: TREND

Editing an Array Formula

Deleting Array Formulas

Chapter 20: The Ten Best Excel Tools

Cell Comments

AutoComplete

Macro Security

The Customizable Toolbar

Evaluate Formula

Worksheet Protection

Unique Records Only

Using the Fill Handle

Quick Data Summaries

Help with Functions

About the Author

Advertisement Page

Connect with Dummies

End User License Agreement

Guide

Cover

Table of Contents

Begin Reading

Pages

iii

iv

vii

viii

ix

x

xi

xii

1

2

3

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

41

42

43

44

45

46

47

48

49

50

51

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

393

394

395

396

Introduction

You wouldn’t have pulled this book off the shelf if you didn’t need to forecast sales. And I’m sure that you’re not Nostradamus. Your office isn’t filled with the smell of incense and it’s not your job to predict the date that the world will come to an end.

But someone — perhaps you — wants you to forecast sales, and you find out how to do that here, using the best general-purpose analysis program around, Microsoft Excel.

About This Book

This book concentrates on using numbers to forecast sales. If you’re a salesperson, or a sales manager, or someone yet higher up the org chart, you’ve run into forecasts that are based not on numbers but on guesses, sales quotas, wishful thinking, and Scotch.

I get away from that kind of thing here. I use numbers instead. Fortunately, you don’t need to be a math major to use Excel for your forecasting. Excel has a passel of tools that will do it on your behalf. Some of them are even easy to use, as you’ll see.

That said, it’s not all about numbers. You still need to understand your products, your company, and your market before you can make a sensible sales forecast, and I have to trust you on that. I hope I can. I think I can. Otherwise, start with Part 1, which talks about the context for a forecast.

You can hop around the chapters in this book, as you can in all books that feature the guy with a pool ball rack for a head. There are three basic approaches to forecasting with numbers — moving averages, smoothing, and regression — and you really don’t have to know much about one to understand another. It helps to know all three, but you don’t really need to.

Foolish Assumptions

The phrase foolish assumptions is, of course, redundant. But here are the assumptions I’m making:

I’m assuming that you know the basics of how to use Excel. Entering numbers into a worksheet, like numbers that show how much you sold in August 2015; entering formulas in worksheet cells; saving workbooks; using menus; that sort of thing.

If you haven’t ever used Excel before, don’t start here. Do buy this book, but also buy Excel 2013 For Dummies by Greg Harvey (published by Wiley), and dip into that one first.

I’m assuming that you have access to information on your company’s sales history, and the more the better.

The only way to forecast what’s about to happen is to know what’s happened earlier. Doesn’t really matter where that information is — it can be in a database, or in an Excel workbook, or even in a simple text file. As long as you can get your hands on it, you can make a forecast. And I talk about how you can get Excel’s “hands” on it.

I’m assuming you don’t have a phobia about numbers.

You don’t have to be some kind of egghead to make good forecasts. But you can’t be afraid of numbers, and I really doubt that you are. Except maybe your quarterly sales quota.

Of course, I’m also assuming you have Excel on your computer.

I’m

not

assuming you have the latest version. But the Excel user interface changed so drastically in 2007 that I have to assume your version uses the Ribbon rather than the original menu structure. Even so, very little of the information in this book has to do with the user interface. Mostly, it’s about setting up your sales history, letting yourself be guided by Excel’s Data Analysis add-in, and finally working with worksheet formulas, charts, and other tools to get where you’re headed on your own.

Icons Used in This Book

In the margins of this book, you find icons — little pictures that are designed to draw your attention to particular kinds of information. Here’s what the icons mean:

Anything marked with this icon will make things easier for you, save you time, get you home in time for dinner. You get some of what I’ve distilled from all my years browsing those blasted newsgroups.

Not a lot of warnings in this book, but there are a few. These tell you what to expect if you do something that Microsoft hasn’t sufficiently protected you against. And there are some of those.

A string around your finger. There are some things to keep in mind when you’re doing your forecasts, and it’s usually easier to remember them than to have to look them up over and over. I do want you to read this book over and over, as I do with murder mysteries, but you’ll get your work done faster if you remember this stuff.

Speaking of stuff, anything marked with this icon is stuff you can probably ignore — but if you’re having trouble getting to sleep you may want to read these. I don’t get into heavy-duty mathematical issues here, but you see some special things about how Excel prepares your forecasts. Sleep tight.

Beyond the Book

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.

Where to Go from Here

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.

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 first row.

For example, you might put “Sales Date” in column A, “Revenue” in column B, “Sales Rep” in column C, and “Product” in column D.

Figure 1-1 shows a typical Excel table.

FIGURE 1-1: You don’t have to keep the records in date order — you can handle that later.

Why bother with tables? Because many Excel tools, including the ones you use to make forecasts, rely on tables. Charts — which help you visualize what’s going on with your sales — rely on tables. Pivot tables — which are the most powerful way you have for summarizing your sales results in Excel — rely heavily on tables. The Data Analysis add-in — a very useful way of making forecasts — relies on tables, too.

For years, Excel depended on an informal arrangement of data called a list. A list looked a lot like a table does now, with field names in its first row, followed by records. But a list did not have built-in properties such as record counts or filters or total rows or even a name. You had to take special steps to identify the number of rows and columns the list occupied.

In Excel 2007, Microsoft added tables as a new feature, and tables have all those things that lists lack. One aspect of tables is especially useful for sales forecasting. As time passes and you get more information about sales figures, you want to add the new data to your baseline. Using lists, you had to define what’s called a dynamic range name to accommodate the new data. With tables, all you need to do is provide a new record, usually in a new row at the end of the table. When you do so, the table is automatically extended to capture the new data. Anything in the workbook — charts, formulas, whatever — is also automatically updated to reflect the new information. Tables are a major improvement over lists and this book makes extensive use of them.

You find a lot more about creating and using tables in Chapter 6. In the meantime, just keep in mind that a table has different variables in different columns, and different records in different rows.

Ordering your data

“Ordering your data” may sound a little like “coloring inside the lines.” The deal is that you have to tell Excel how much you sold in 1999, and then how much in 2000, and in 2001, and so on. If you’re going to do that, you have to put the data in chronological order.

The very best way to put your data in chronological order in Excel is by way of pivot tables. A pivot table takes individual records that are in an Excel table (or in an external database) and combines the records in ways that you control. You may have a table showing a year’s worth of sales, including the name of the sales rep, the product sold, the date of sale, and the sales revenue. If so, you can very quickly create a pivot table that totals sales revenue by sales rep and by product across quarters. Using pivot tables, you can summarize tens of thousands of records, quite literally within seconds. If you haven’t used pivot tables before, this book not only introduces the subject but also makes you dream about them in the middle of the night.

Three particularly wonderful things about pivot tables:

They can accumulate for you all your sales data — or, for that matter, your data on the solar wind, but this book is about sales forecasting.

If you gather information on a sale-by-sale basis, and you then want to know how much your reps sold on a given day, in a given week, and so on, a pivot table is the best way to do so.

You can use a pivot table as the basis for your next forecast, which saves you a bunch of time.

They have a unique way of helping you group your historical data — by day, by week, by month, by quarter, by year, you name it.

Chapter 8

gives you the details, as well as much more information on pivot tables, including troubleshooting some common problems.

Making Basic Forecasts

Part 3 gets into the business of making actual forecasts, ones that are based on historical data (that is, what’s gone on before). You see how to use the Data Analysis add-in to make forecasts that you can back up with actuals — given that you’ve looked at Part 2 and set up your actuals correctly. (Your actuals are the actual sales results that show up in the company’s accounting records — say, when the company recognizes the revenue.)

The Data Analysis add-in is a gizmo that has shipped with Excel ever since 1995. It includes a convenient way to make forecasts, as well as to do general data analysis. The three principal tools that the Data Analysis add-in gives you to make forecasts are:

Moving Averages

Exponential Smoothing

Regression

Those are the three principal forecasting methods, and they form the basis for the more-advanced techniques and models. So it’s no coincidence that these tools have the same names as the forecasting methods mentioned earlier in this chapter.

The Data Analysis add-in is an add-in. An add-in does tasks, like forecasting, on your behalf. An add-in is much like the other tools that are a part of Excel — the difference is that you can choose whether to install an add-in. For example, you can’t choose whether the Goal Seek tool (under What-If Analysis on the Ribbon’s Data tab) is available to you. If you decide to install Excel on your computer, Goal Seek is just part of the package. Add-ins are different. You can decide whether to install them. When you’re installing Excel — and in most cases this means when you’re installing Microsoft Office — you get to decide which add-ins you want to use.

The following sections offer a brief introduction to the three Data Analysis tools.

Given a good baseline, the Data Analysis can turn a forecast back to you. And then you’re responsible for evaluating the forecast, for deciding whether it’s a credible one, for thinking the forecast over in terms of what you know about your business model. After all, Excel just calculates — you’re expected to do the thinking.

Putting moving averages to work for you

You may already be familiar with moving averages. They have two main characteristics, as the name makes clear:

They move.

More specifically, they move over time. The first moving average may involve Monday, Tuesday, and Wednesday; in that case, the second moving average would involve Tuesday, Wednesday, and Thursday; the third Wednesday, Thursday, and Friday, and so on.

They’re averages.

The first moving average may be the average of Monday’s, Tuesday’s, and Wednesday’s sales. Then the second moving average would be the average of Tuesday’s, Wednesday’s, and Thursday’s sales, and so on.

The basic idea, as with all forecasting methods, is that something regular and predictable is going on — often called the signal. Sales of ski boots regularly rise during the fall and winter, and predictably fall during the spring and summer. Beer sales regularly rise on NFL Sundays and predictably fall on other days of the week.

But something else is going on, something irregular and unpredictable — often called noise. If a local sporting goods store has a sale on, discounting ski boots from May through July, you and your friends may buy new boots during the spring and summer, even though the regular sales pattern (the signal) says that people buy boots during the fall and winter. As a forecaster, you typically can’t predict this special sale. It’s random and tends to depend on things like overstock. It’s noise.

Let’s say you run a liquor store, and a Thursday night college football game that looked like it would be the Boring Game of the Week when you were scheduling your purchases in September has suddenly in November turned into one with championship implications. You may be caught short if you scheduled your purchases to arrive at your store the following Saturday, when the signal in the baseline leads you to expect your sales to peak. That’s noise — the difference between what you predict and what actually happens. By definition, noise is unpredictable, and for a forecaster it’s a pain.

If the noise is random, it averages out. Some months, sporting goods stores will be discounting ski boots for less than the cost of an arthroscopy. Some months, a new and really cool model will come out, and the stores will take every possible advantage. The peaks and valleys even out. Some weeks there will be an extra football game or two and you’ll sell (and therefore need) more bottles of beer. Some weeks there’ll be a dry spell from Monday through Friday, you won’t need so much beer, and you won’t want to bear the carrying costs of beer you’re not going to sell for a while.

The idea is that the noise averages out, and that what moving averages show you is the signal. To misquote Johnny Mercer, if you accentuate the signal and eliminate the noise, you latch on to a pretty good forecast.

So with moving averages, you take account of the signal — the fact that you sell more ski boots during certain months and fewer during other months, or that you sell more beer on weekends than on weekdays. At the same time you want to let the random noises — also termed errors — cancel one another out. You do that by averaging what’s already happened in two, three, four, or more previous consecutive time periods. The signal in those time periods is emphasized by the averaging, and that averaging also tends to minimize the noise.

Suppose you decide to base your moving averages on two-month records. That is, you’ll average January and February, and then February and March, and then March and April, and so on. In that case you’re getting a handle on the signal by averaging two consecutive months and reducing the noise at the same time. Then, if you want to forecast what will happen in May, you hope to be able to use the signal — that is, the average of what’s happened in March and April.

Figure 1-2 shows an example of the monthly sales results and of the two-month moving average.

FIGURE 1-2: The moving average shows the general direction of the sales (the signal), and deemphasizes the random variations (the noise).

Chapter 14 goes into more detail about using moving averages for forecasting.

Making sense of exponential smoothing

I know, the term exponential smoothing sounds intimidating and pretentious. I guess it’s both — although I promise I’m not responsible for it. (If you really want, you can find out why it’s called that in Chapter 15.) In any event, don’t worry about what it’s called — it’s just a kind of self-correcting moving average.

Suppose that in June, you forecast $100,000 in sales for July. When the July sales results are in, you find that your July forecast of $100,000 was $25,000 too low — you actually made $125,000 in sales. Now you need to forecast your sales for August. The idea behind this approach to forecasting is to adjust your August forecast in a way that would have made the July forecast more accurate. That is, because your July forecast was too low, you increase your August forecast above what it would have been otherwise.

More generally:

If your most recent forecast turned out to be an underestimate, you adjust your next forecast upward.

If your most recent forecast turned out to be an overestimate, you adjust your next forecast downward.

You don’t make these adjustments just by guessing. There are formulas that help out, and the Data Analysis add-in’s Exponential Smoothing tool can enter the formulas for you. Or you can roll your own formulas if you want. Turn to Chapter 15 to see how to do that.

Figure 1-3 shows what you would forecast if your prior forecast (for July) was too low — then you boost your forecast for August.

FIGURE 1-3: Here’s what happens if your forecast for July was an underestimate. Notice that the August forecast is kicked up.

And if your prior, July forecast was too high, you cool your jets a little bit in your August forecast, as shown in Figure 1-4.

FIGURE 1-4: Your forecast for March 2015 was too high, so exponential smoothing makes you back off your forecast for April 2015.

Using regression to get what you want

The term regression doesn’t sound as bad as exponential smoothing, but it is — I admit — 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. Remember: 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. (You can find a more detailed look in Chapter 11.)

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. To continue the examples used so far in this section:

Historical advertising expenses and historical sales revenues

How much you charged per fire door and how many doors you sold

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, 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 as Chapter 11 shows, you can get Excel to do that for you.

Charting Your Data

I’ve been doing this stuff for a long time, and I can’t tell you how critical it is to chart your baseline and your forecast. Being able to visualize what’s going on is important for several reasons.

Using Excel’s charts, you can see how your actuals are doing (see Figure 1-5). And by charting your actuals, you can see how well your sales forecasts do against the actual sales results. Figure 1-6 shows a forecast that’s based on moving averages, against the monthly actuals.

FIGURE 1-5: An Excel chart makes it much easier to see how your sales are doing.

FIGURE 1-6: Notice how the moving average lags behind the actual results.

By charting your baseline and your forecasts, you can:

See how your actual results are doing.

A chart is almost always more revealing than a table of numbers.

See how well your forecasts predict actual results.

Your eye is a good gauge of the quality of your forecasts.

See how well a different variable — advertising dollars or the Consumer Price Index — predicts the sales of your product.

Yes, an R squared or some other summary statistic can give you a concise estimate of how well your forecasts are working. But there’s nothing, nothing, like a chart to tell you if you’re forecasting results or if you’re forecasting junk. Chapter 9 shows you how to set up charts with Excel.

Forecasting with Advanced Tools

There’s a lot to be said for using the Data Analysis add-in to create your forecasts. The add-in’s tools are quick, they do the heavy lifting for you, and they’re reasonably comprehensive, taking care of the math and some of the charting.

But there’s nothing like doing it yourself. When you wave goodbye to the Data Analysis add-in, you establish and maintain control over what’s going on with the forecast. If you have formulas in your worksheet cells — formulas that support your forecasts — you can change those formulas as your forecasting needs change. And you can change — or add to — the baseline and immediately see what the effect doing so has on your forecast. That’s because the formulas are live: They react to changes in their inputs.

When the add-in’s tools give you not formulas but static values instead, you can’t easily experiment with the forecasts or see the effect of modifying the baseline. And the add-in’s Regression tool gives you just the static values. The Exponential Smoothing tool is a little better, but it mixes formulas with static values. And the Moving Averages tool forces you to start from scratch if you want to change the number of records in the baseline that make up a moving average.

Suppose that you have the number 3 in cell A1 and the number 5 in cell A2. In cell A3 you can enter the sum of those two numbers, 8. But if you now change the number 3 in cell A1 to, say, 103, you still have 8 in A3. It’s a constant — a number, not a formula. It doesn’t react to what’s in cell A1 or A2: You’re still going to see the number 8 in cell A3.

On the other hand, suppose you have this in cell A3:

=A1 + A2

That’s a formula, not a constant, and it tells Excel to add whatever’s in A1 to whatever’s in A2. So if you change what’s in A1, or what’s in A2, Excel recalculates the result and shows it — in this example — in A3.

The point to keep in mind is that the add-in’s regression tool gives you numbers, not formulas. It calculates your forecast, and the underlying figures, and writes numbers onto your worksheet. That means, regardless of how you change the numbers in your baseline, you’re still going to be looking at the same forecast as offered by the Regression tool.

But — and it’s a big one — if you make the forecast yourself instead of relying on the add-in’s tool, you can enter the formulas that the add-in denies you. Why is this important? By entering the formulas yourself, you have more control over what’s going on with the forecast.

Relying on the add-in, which isn’t a bad toolbox, and is one that you can generally trust, is perfectly okay. However, if you enter formulas, ones that react to changes in your baseline, you can make a change in the baseline and see what happens to the forecast. You can change this month’s result from $100,000 to $75,000 and see whether your forecast for next month changes substantially. You can’t do that with the add-in’s Regression tool unless you start all over again, because it doesn’t give you formulas. To a smaller degree, the same is true of the Exponential Smoothing tool.

But the more important reason, the reason for you to consider entering the formulas yourself, is that you’re relying on your own knowledge of how and why forecasting works. In Part 4, I show you how to use functions like LINEST and TREND to do your regression-based forecasts. You also see how to use array formulas to get the most out of those Excel functions.

You don’t need to enter all the formulas yourself to make good forecasts. The add-in includes reasonably good tools. But if you do enter the formulas yourself, not only can you be more confident that you know what’s going on with your forecast, but you can also exercise more control over what your forecast says is going to happen. In a business as tricky and trappy as forecasting, the more control you have, the better.

Chapter 2

Forecasting: The Basic Issues

IN THIS CHAPTER

Knowing why you need to forecast

Understanding the language of forecasting

Seeing what Excel can do for you

Unless you really enjoy playing with numbers, you need a good reason to bother with forecasting sales. In this chapter, I tell you some of the business reasons to forecast, beyond the fact that your Vice President of Sales makes you do it.

Like all specialties, forecasting uses terms that are unfamiliar to those who haven’t yet been inducted into the secret society. This chapter introduces you to some of the important sales forecasting terminology.

If you’re going to make a credible forecast, you need access to an archive of historical data that isn’t necessarily easy to access. You’ll often find it right there in an Excel workbook, but sometimes it isn’t there; instead, it’s in your company’s accounting database, and someone will have to exhume it. In this chapter, you see some of the reasons to put yourself or your assistant through that task.

Excel offers several methods of forecasting. Each method works best — and some work only — if you set up a baseline using what Excel terms a table. Depending on the method you choose, that table may occupy only one column, or two (or more) columns. This chapter gives you an overview of those forecasting methods, along with a brief explanation of why you might use just one column of data for your baseline, or two or more columns, depending on your choice of forecasting method.

Excel is an ideal general-purpose analysis program to use for forecasting, in part because it has functions and tools that are intended to help you make your forecasts, and in part because you often store the necessary data in Excel anyway — so, it’s right there, ready for you to use. In this chapter, you find out what’s so great about using Excel to create your forecasts, and you find some groundwork on how best to put it to use in your own situation.

Why Forecast?

People tend to think of the process of sales forecasting as a knee-jerk response to a frantic call for reassurance from some nervous, jumpy, excitable VP who’s worried about having to dust off the résumé. And often, you have some reason to believe that’s exactly what’s going on.

But there are plenty of more productive reasons to go to the trouble of gathering up baseline data, getting it into the right shape to support a credible forecast, do the analysis, and then interpret it than just responding to a VP who’s afraid the job is on the line. Here are a few of those reasons.

To plan sales strategies

If you can use sales forecasts to get a handle on either future revenues, or unit sales, or both, you can help groups like Marketing, Product Management, and Production make decisions about activities such as promotion, pricing, and purchasing — each of which influences your company’s sales results as well as its net income.

Suppose you take a look at quarterly sales results over a period of several years, and you see that during that time the sales of a particular product have been gently declining. (If the decline had been steep, you wouldn’t have to look at a baseline — everyone from the sales force to the CEO would have been rattling your cage.) Your forecast indicates that the decline is likely to continue. Is the market for the product disappearing? That depends. You need to ask and answer some other questions first.

Is the product a commodity?

Some business analysts sneer at commodities — they’re not very glamorous, after all — but commodities can be very profitable products if you dominate the market. If you don’t dominate the market, maybe you shouldn’t be in the market for that commodity. So, have your competitors been cutting into your market share, or is the total size of the market shrinking? If the problem is the competition, maybe you want to do something to take back your share, even if that requires putting more resources into the product line — such as retooling its manufacture, putting more dollars into promotions, or cutting the price. But if the total market itself is shrinking, it may just be time to bail out.

How old is the product?

Products do have life cycles. When products are bright and shiny, the sales revenues can grow sharply over a fairly short time frame. When products reach maturity, the sales usually flatten out. And then, as newer, better, fancier products arrive, the sales start to drop. Think streaming video versus DVD. Get Marketing and Product Management to assess whether the product is getting long in the tooth. If it is, it may be time to get out. Or, it may be smart to spruce up the product and differentiate it from the competition’s versions, in order to squeeze some more profitable revenue out of it before you give up on it. Forecasting can inform that kind of decision, although it can’t make it for you.

How will Sales support the product?

If your company decides that it’s not yet time to abandon the product, Sales Management needs to make some decisions about how to allocate its resources — that is, its sales reps. One way to do that, of course, is to take the product out of some reps’ bags and replace it with another, more robust product. (Keep in mind that some reps

prefer

older products because they can use familiar sales strategies.)

Is it possible that the decline in sales is due more to large-scale economic conditions than to problems with the product itself?

If so, you may decide to hang in and wait for the economy, consumer confidence, or the index of leading economic indicators to improve, instead of making a drastic decision to drop a product line.

There’s at least one good aspect to a product that’s entering the final stage of its life cycle: You very likely have lots of historical data on its sales figures. And in general, the more historical data you have to base a forecast on, the more confidence you can place in that forecast.

To size inventories

During the late 1980s, I worked for a Baby Bell — one of the companies that was spun off by the AT&T breakup. For a couple of years, I was in charge of managing resale equipment inventories at that Baby Bell.

My staff and I reduced the size of the equipment intended for sale to customers from a grotesque $24 million to a more reasonable $9 million in 18 months, without resorting to write-downs. We did it by forecasting sales by product line. This helped us tell which products we could expect to have high turns ratios (the speed with which the product line would sell) and we’d buy those in quantities that increased our discounts from our suppliers.

Until we were almost out of them, we refused to buy any products that our forecasts indicated would have low turns ratios. It didn’t matter how piteous the pleadings of the sales managers who wanted them on hand for fast delivery just in case a customer decided to buy one and wanted it installed right now. (Getting a huge PBX out of warehouse storage in West Eyesocket, Connecticut, and shipping it to Broken Pelvis, Montana, can take longer than you may think. For one thing, you may have to pressure Connecticut’s Regional VP into letting go of it. Today, VoIP software is rapidly replacing big electronic switches, but the principle remains the same: Expensive stuff can be hard to move.)

Plus, the annual carrying costs for equipment inventory in the late 1980s averaged around 15 percent of the cost of the equipment, including storage, cost of money, obsolescence, and so on. So by reducing the total inventory cost by $15 million, we saved the company $2.25 million each year. (That savings actually covered the cost of our salaries, by the way, with plenty left over.)

Simply reducing the size of inventory isn’t the end of the story, though. Sales forecasting helps you plan just-in-time (JIT) inventory management, so you can time your purchases to correspond to when sales need to be fulfilled. The less time inventory spends in the warehouse, the less money you’re paying to let it just sit there waiting to be sold.

Talking the Talk: Basic Forecasting Lingo

You need to get a handle on the specialized terminology used in forecasting for a couple very practical reasons. One is that you may be asked to explain your forecasts to your boss or in a meeting of, for example, sales managers. In those situations you want to say things like, “We decided to use regression on the baseline because it turned out to be more accurate.” You don’t want to find yourself saying “Jeff found a formula in a book he has, and we used it on these numbers here. Seems to work okay.”

Another good reason is that Excel uses many of these terms, as do other programs, and figuring out what’s going on is a lot easier if you know what the terms mean. Okay, deep breath.

Autoregressive integrated moving averages (ARIMA)

I mention autoregressive integrated moving averages (ARIMA) here not because this book is going to use it or even talk much about it. But if you’re going to do forecasting, some smart aleck will eventually ask you if you used ARIMA, and you should know how to reply. ARIMA is in part a forecasting method, and also a way of evaluating your baseline so that you can get quantitative evidence that supports using a regression approach, a moving-average approach, or a combination of both. Unless you really take to this forecasting stuff, you’ll usually do just fine without it, even though it’s an excellent, if complex, diagnostic tool.

By the way, your answer to the smart aleck should be, “No. I’ve been working with this baseline for so long now that I know I get my best results with exponential smoothing. Which, as you know, is one of the forms that ARIMA can take.”

Baseline

A baseline is a sequence of data arranged in chronological order. In terms of this book’s basic topic, the forecasting of sales, some examples of baselines include total monthly revenues from January 2010 through December 2015, number of units sold weekly from January 1, 2015, through December 31, 2016, and total quarterly revenues from Q1 2007 through Q4 2016. Data arranged like this is sometimes called a time series, but in this book I use the term baseline.

Correlation

A correlation