Excel Sales Forecasting For Dummies E-Book

Conrad Carlberg

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 499

Veröffentlichungsjahr: 2011

Beschreibung

When they first told you that forecasting sales would be part ofyour job, did you feel just the tiniest bit of panic? Did youmomentarily consider consulting the Yellow Pages for listings of"Psychics" or "Tea Leaf Readers"? Well,fear not. Excel Sales Forecasting For Dummies can help youpredict the future without incense or a crystal ball. Excel Sales Forecasting For Dummies shows you how to usethe number one workbook program, Microsoft Excel, to predict trendsand future sales based on something not quite soethereal--numbers. You use data about the past to forecast thefuture. Excel provides all sorts of tools to help you do that, andthis book shows you how to use them. From recognizing why forecasting is a good idea to making senseof exponential smoothing, Excel Sales Forecasting ForDummies has you covered. If you have a basic grasp of how touse Excel, you'll be ready to discover how to * Select and use the right forecasting method for yourbusiness * Choose and arrange data in lists, then manage that data withpivot tables * Filter lists and turn them into charts that illustratewhat's going on * Find relationships in your data * Use Excel's Analysis Toolpak feature to create forecastsautomatically, or venture into advanced forecasts using worksheetfunctions * Gain more control over your forecasting and target specifictypes of predictions * Use moving averages and predict seasonal sales Written by Conrad Carlberg, a nationally recognized expert onExcel who also has experience in sales and marketing, this friendlyguide gets you up and running quickly and easily. You'll soonbe setting up a baseline you can chart and label, summarizing datawith pivot tables, making forecasts based on regression,understanding correlation, and discovering how smoothing lets usprofit from our mistakes. You'll find your confidence in yourability to make sales predictions has soared right off thechart.

Leseprobe

Excel Sales Forecasting For Dummies®

by Conrad Carlberg

Excel Sales Forecasting For Dummies®

Published byWiley Publishing, Inc.111 River St.Hoboken, NJ 07030-5774www.wiley.com

Copyright © 2005 by Wiley Publishing, Inc., Indianapolis, Indiana

Published by Wiley Publishing, Inc., Indianapolis, Indiana

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 either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone]. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, [phone], fax [phone], e-mail: [email protected].

Trademarks: Wiley, the Wiley Publishing logo, For Dummies, the Dummies Man logo, A Reference for the Rest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates in the United States and other countries, and may not be used without written permission. All other trademarks are the property of their respective owners. Wiley Publishing, 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 [phone].

For technical support, please visit www.wiley.com/techsupport.

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: 2005920365

ISBN: 0-7645-7593-7

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

1O/QZ/QT/QV/IN

About the Author

Conrad Carlberg is the author of more than ten books about Microsoft Excel. As a multi-time recipient of Microsoft’s MVP designation for Excel, he is a nationally recognized expert on that application.

Carlberg’s Ph.D. in statistics involves work in forecasting, as does his work in telecommunications and the health-care industry. He used the techniques in this book to reduce a crushing $24 million inventory owned by a Baby Bell to under $10 million in 18 months. The carrying costs for $24 million in equipment are significant. The point: This forecasting stuff works.

As preparation for starting his consultancy, Carlberg spent two years as a sales engineer for a Fortune 500 company. He lives near San Diego, where he tries his best to keep from crashing into other sailboats.

Dedication

For Joe Frazier, Mike Kobluk, and Chad Mitchell: Show me a pretty little number.

Author’s Acknowledgments

I want to thank Kathy Ivens, who suggested my name to Wiley for this book and who has been the best coauthor one could hope for on our prior books; Tom Heine, the acquisitions editor who had the idea for this one; Elizabeth Kuball, the development editor, copy editor, project manager, and coach who has brought the book about; and, of course, the technical editor for this book — ladies and gentlemen, give it up for the Excel Trickster, Bob Umlas.

Publisher’s Acknowledgments

We’re proud of this book; please send us your comments through our online registration form located at www.dummies.com/register/.

Some of the people who helped bring this book to market include the following:

Acquisitions, Editorial, and Media Development

Project Editor: Elizabeth Kuball

Acquisitions Editor: Tom Heine

Technical Editor: Bob Umlas

Editorial Manager: Robyn Siesky

Media Development Supervisor: Richard Graves

Editorial Assistant: Adrienne Porter

Cartoons: Rich Tennant (www.the5thwave.com)

Composition Services

Project Coordinator: Maridee Ennis

Layout and Graphics: Andrea Dahl, Joyce Haughey, Clint Lahnen, Barry Offringa, Lynsey Osborn, Melanee Prendergast, Heather Ryan

Proofreaders: Leeann Harney, Jessica Kramer Carl William Pierce, TECHBOOKS Production Services

Indexer: TECHBOOKS Production Services

Publishing and Editorial for Technology Publishing

Richard Swadley, Vice President and Executive Group Publisher

Barry Pruett, Vice President and Publisher, Visual/Web Graphics

Andy Cummings, Vice President and Publisher, Technology Dummies

Mary Bednarek, Executive Acquisitions Director, Technology Dummies

Mary C. Corder, Editorial Director, Technology Dummies

Publishing for Consumer Dummies

Diane Graves Steele, Vice President and Publisher

Joyce Pepple, Acquisitions Director

Composition Services

Gerry Fahey, Vice President of Production Services

Debbie Stailey, Director of Composition Services

Contents

Title

Introduction

About This Book

Foolish Assumptions

How to Use This Book

How This Book Is Organized

Icons Used in This Book

Where to Go from Here

Part I : 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 II : 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 Lists in Excel

Understanding List Structures

The List Command

Filtering Lists

Importing Data from a Database to an Excel List

Chapter 7: Working with Lists in Excel

Turning Lists into Charts

Using the Analysis ToolPak with Lists

Avoiding the Analysis ToolPak’s Traps

Part III : 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 Analysis ToolPak

Installing Add-Ins

Using Moving Averages

Using Exponential Smoothing

Using the Regression Tool

Chapter 11: Basing Forecasts on Regression

Deciding to Use the Regression Tool

Understanding the Analysis ToolPak’s Regression Tool

Using Multiple Regression

Part IV : Making Advanced Forecasts

Chapter 12: Entering the Formulas Yourself

About Excel Formulas

Using Insert Function

Understanding Array Formulas

Using the Regression Functions

Chapter 13: Using Moving Averages

Choosing the Length of the Moving Average

Reacting Quickly versus Modeling Noise

Using the Analysis ToolPak to Get Moving Averages

Chapter 14: Evaluating a Moving Average

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 V : The Part of Tens

Chapter 19: Ten Pivot Table Troubles — and How to Overcome Them

Importing External Data

Saving the Password

Refreshing the Data when the File Opens

Drilling to Details

Formatting Fields

Pivot Tables: Things That Go Bump in the Day

Naming Fields

Calculating Fields

Calculating Items

Subtotaling Inner Fields

Managing the Data Cache

Chapter 20: The Ten Best Excel Tools

Cell Comments

AutoComplete

Macro Security

The Customizable Toolbar

Evaluate Formula

Worksheet Protection

Unique Records Only

List Management

Quick Data Summaries

Help with Functions

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 your job isn’t 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 workbook 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 I, which talks about the context for a forecast.

You can hop around the chapters in this book, as you can in all books that have the guy with the triangle head on the cover. 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 2005; 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 2003 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.

Oh yeah, I’m also assuming you have Excel on your computer. I’m not assuming you have the latest version. Little in this book requires you to have anything more recent than Excel 95.

How to Use This Book

Do you have a couple of variables at hand? They might be something such as the number of cable subscribers in your region, and your revenues from selling cable services. If so, try looking at the chapters on forecasting by regression. If you know how many subscribers you have, then you’re probably well on your way to forecasting revenues using the regression approach.

Do you have just one variable? If your company sells, say, sporting goods, you may not know how many customers you have, but you very likely know how much revenue you’ve recognized over the past few years. Then you may want to adopt a moving-average approach, maybe by tracking monthly sales dollars. Chapter 13 is probably the right place to start.

And, even with just one variable, you can do something called exponential smoothing. Don’t worry, it’s a lot easier than it sounds, and Excel has a tool that handles it for you if you want. It’s a way of using bad forecasts that happened earlier in order to improve your next forecast. Visit Chapter 15 for more.

How you use this book depends on the sort of data you have, and you know that much better than I do.

How This Book Is Organized

All Gaul was divided into three parts. This book is divided into five.

Part I: Understanding Sales Forecasting and How Excel Can Help

I used to have a client who didn’t believe in forecasting. He couldn’t see why looking at the past could help you understand the future. The chapters in this part of the book explain what I couldn’t get my client to understand. (I wish I’d written this book earlier — the client I mentioned has been, well, reassigned.) You also see in this part why Excel is a fine application to use for making your forecasts.

Part II: Organizing the Data

You need to know how to put your existing sales data together in order to get a good forecast (and how to defend that forecast when your management puts you on the griddle). In Excel, the basis for doing that is a list. That’s kind of a rudimentary term, I know, but lists are really important in Excel. In this part, you also see how to choose and arrange your data.

Part III: Making a Basic Forecast

You can’t forecast without knowing what’s already happened. And you can’t forecast sensibly without getting your data together. Lists, discussed in Part II, are the fundamentals, but pivot tables are the way to manage the data to prepare for a basic forecast. This part shows you how to get your existing sales data together from lists so you can make sensible forecasts.

Here you also get an introduction to the Analysis ToolPak, which you get as a free gift (another redundancy) when you buy Microsoft Excel. You see how you can use the Analysis ToolPak to make your forecast for you. And in this part, I try to convince you to put your data into charts so you can see what’s going on.

Part IV: Making Advanced Forecasts

Advanced forecasts are a little more complicated when it comes to the math and the functions you use, but they can shed more light on what’s going on in your sales flow. This section walks you through how to do your forecasts using worksheet functions rather than the Analysis ToolPak. As a result, you have more control over what’s happening. And more control is better control, if you’re willing to take the time to use it.

Part V: The Part of Tens

Part V was fun for me to write. I’ve been using Excel since the early 1990s and I’ve written a lot about it, both in online newsgroups and in books, and Part V gives me a chance both to extol its virtues and rant about its faults. Very satisfying emotionally. The Part of Tens talks about my favorite stuff and my pet peeves. Because pivot tables are so important to doing forecasts, it also talks about troubleshooting pivot table problems. And I get to bring my favorite Excel tools to your attention.

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.

Where to Go from Here

Are you looking for information about the basics of forecasting? Why it works? Why it’s not just an exercise in numeric analysis? 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 lists in Excel.

If you’re already up on forecasting basics and lists, 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 I

Understanding Sales Forecasting and How Excel Can Help

In this part . . .

In Part I, I talk about why forecasting sales can help your business in ways that seem to have little to do with sales. Part I 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, all three of these options are less than desirable. If you want to do better more often than you do worse, you need to take advantage of some better options. Lucky for you, there are different ways to forecast, ways that have proven their accuracy over and over. They’re a little more time consuming than guessing, 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 and know 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 last year, where the market futures wound up last month, what the temperature was today).