Excel® Sales Forecasting For Dummies®
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)
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.
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.
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.
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:
