O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Excel Sales Forecasting For Dummies, 2nd Edition

Excel Sales Forecasting For Dummies, 2nd Edition

by Conrad Carlberg
Publisher: For Dummies
Release Date: July 2016
ISBN: 9781119291428
View table of contents
Start reading

Book Description

Choose, manage, and present data

Select the right forecasting method for your business

Use moving averages and predict seasonal sales

Create sales forecasts you can trust

You don't need magic, luck, or an advanced math degree to develop reliable sales forecasts; you just need Excel and this book! This guide explains how forecasting works and how to use the tools built into Excel. You'll learn how to choose your data, set up tables, chart your baseline, to create both basic and advanced forecasts you can really use.

Inside...

  • Prevent common issues
  • Why baselines matter
  • How to organize your data
  • Tips on setting up tables
  • Working with pivot charts
  • How to forecast seasonal sales revenue
  • Forecasting with regression

Table of Contents

    1. Cover
    2. Introduction
      1. About This Book
      2. Foolish Assumptions
      3. Icons Used in This Book
      4. Beyond the Book
      5. Where to Go from Here
    3. Part 1: Understanding Sales Forecasting and How Excel Can Help
      1. Chapter 1: A Forecasting Overview
        1. Understanding Excel Forecasts
        2. Getting the Data Ready
        3. Making Basic Forecasts
        4. Charting Your Data
        5. Forecasting with Advanced Tools
      2. Chapter 2: Forecasting: The Basic Issues
        1. Why Forecast?
        2. Talking the Talk: Basic Forecasting Lingo
        3. Understanding the Baseline
        4. Setting Up Your Forecast
        5. Using Your Revenue and Cost Data
      3. Chapter 3: Understanding Baselines
        1. Using Qualitative Data
        2. Recovering from Mistakes in Sales Forecasting
        3. Recognizing Trends and Seasons
      4. Chapter 4: Predicting the Future: Why Forecasting Works
        1. Understanding Trends
        2. Matchmaker, Matchmaker: Finding Relationships in the Data
    4. Part 2: Organizing the Data
      1. Chapter 5: Choosing Your Data: How to Get a Good Baseline
        1. Early to Bed: Getting Your Figures in Order
        2. Staying Inside the Lines: Why Time Periods Matter
        3. Spacing Time Periods Equally
      2. Chapter 6: Setting Up Tables in Excel
        1. Understanding Table Structures
        2. Creating a Table
        3. Filtering Lists
        4. Importing Data from a Database to an Excel Table
      3. Chapter 7: Working with Tables in Excel
        1. Turning Tables into Charts
        2. Using the Data Analysis Add-in with Tables
        3. Avoiding the Data Analysis Add-in’s Traps
    5. Part 3: Making a Basic Forecast
      1. Chapter 8: Summarizing Sales Data with Pivot Tables
        1. Understanding Pivot Tables
        2. Building the Pivot Table
        3. Grouping Records
        4. Avoiding Grief in Excel Pivot Tables
      2. Chapter 9: Charting Your Baseline: It’s a Good Idea
        1. Digging into a Baseline
        2. Making Your Data Dance with Pivot Charts
        3. Using Two Value Axes
      3. Chapter 10: Forecasting with Excel’s Data Analysis Add-in
        1. Installing Add-ins: Is the Add-in Even There?
        2. Using Moving Averages
        3. Using Exponential Smoothing
        4. Using the Regression Tool
      4. Chapter 11: Basing Forecasts on Regression
        1. Deciding to Use the Regression Tool
        2. Understanding the Data Analysis Add-in’s Regression Tool
        3. Using Multiple Regression
    6. Part 4: Making Advanced Forecasts
      1. Chapter 12: Entering the Formulas Yourself
        1. About Excel Formulas
        2. Using Insert Function
        3. Understanding Array Formulas
        4. Using the Regression Functions to Forecast
      2. Chapter 13: Using Moving Averages
        1. Choosing the Length of the Moving Average
        2. Reacting Quickly versus Modeling Noise
        3. Using the Data Analysis Add-in to Get Moving Averages
      3. Chapter 14: Changing Horses: From Moving Averages to Smoothing
        1. Losing Early Averages
        2. Understanding Correlation
        3. Understanding Autocorrelation
      4. Chapter 15: Smoothing: How You Profit from Your Mistakes
        1. Correcting Errors: The Idea Behind Smoothing
        2. Using the Smoothing Tool’s Formula
        3. Finding the Smoothing Constant
        4. Problems with Exponential Smoothing
      5. Chapter 16: Fine-Tuning a Regression Forecast
        1. Doing Multiple Regression
        2. Getting a Regression Trendline into a Chart
        3. Evaluating Regression Forecasts
      6. Chapter 17: Managing Trends
        1. Knowing Why You May Want to Remove the Trend from a Baseline
        2. Getting a Baseline to Stand Still
        3. And All the King’s Men: Putting a Baseline Together Again
      7. Chapter 18: Same Time Last Year: Forecasting Seasonal Sales
        1. Doing Simple Seasonal Exponential Smoothing
        2. Getting Farther into the Baseline
        3. Finishing the Forecast
    7. Part 5: The Part of Tens
      1. Chapter 19: Ten Fun Facts to Know and Tell about Array Formulas
        1. Entering Array Formulas
        2. Using the Shift Key
        3. Noticing the Curly Brackets
        4. Using INDEX to Extract a Value from an Array Formula’s Result
        5. A Quick Route to Unique Values
        6. Selecting the Range: LINEST
        7. Selecting the Range: TRANSPOSE
        8. Selecting a Range: TREND
        9. Editing an Array Formula
        10. Deleting Array Formulas
      2. Chapter 20: The Ten Best Excel Tools
        1. Cell Comments
        2. AutoComplete
        3. Macro Security
        4. The Customizable Toolbar
        5. Evaluate Formula
        6. Worksheet Protection
        7. Unique Records Only
        8. Using the Fill Handle
        9. Quick Data Summaries
        10. Help with Functions
    8. About the Author
    9. Advertisement Page
    10. Connect with Dummies
    11. End User License Agreement