More Predictive Analytics. Microsoft Excel

436 Pages·2016·16.53 MB·English

by  Carlberg C.

About This eBook
ePUB is an open, industry-standard format for eBooks. However, support of
ePUB and its many features varies across reading devices and applications. Use
your device or app settings to customize the presentation to your liking. Settings
that you can customize often include font, font size, single or double column,
landscape or portrait mode, and figures that you can click or tap to enlarge. For
additional information about the settings and features on your reading device or
app, visit the device manufacturer’s Web site.
Many titles include programming code or configuration examples. To
optimize the presentation of these elements, view the eBook in single-column,
landscape mode and adjust the font size to the smallest setting. In addition to
presenting code and configurations in the reflowable text format, we have
included images of the code that mimic the presentation found in the print book;
therefore, where the reflowable format may compromise the presentation of the
code listing, you will see a “Click here to view code image” link. Click the link
to view the print-fidelity code image. To return to the previous page viewed,
click the Back button on your device or app.
More Predictive
Analytics:
Microsoft Excel
Conrad Carlberg
800 E. 96th Street
Indianapolis, Indiana 46240
More Predictive Analytics: Microsoft Excel
Copyright © 2016 by Que Publishing
All rights reserved. No part of this book shall be reproduced, stored in a retrieval
system, or transmitted by any means, electronic, mechanical, photocopying,
recording, or otherwise, without written permission from the publisher. No
patent liability is assumed with respect to the use of the information contained
herein. Although every precaution has been taken in the preparation of this book,
the publisher and author assume no responsibility for errors or omissions. Nor is
any liability assumed for damages resulting from the use of the information
contained herein.
ISBN-13: 978-0-7897-5614-5
ISBN-10: 0-7897-5614-5
Library of Congress Control Number: 2015941441
Printed in the United States of America
First Printing: August 2015
Editor-in-Chief
Greg Wiegand Acquisitions Editor
Joan Murray Development Editor
Charlotte Kughen Managing Editor
Sandra Schroeder Project Editor
Seth Kerney Copy Editor
Cheri Clark Indexer
Ken Johnson
Proofreader
Megan Wade Technical Editor
Michael Turner Editorial Assistant
Cindy Teeters Cover Designer
Mark Shirar Compositor
Jake McFarland Trademarks
All terms mentioned in this book that are known to be trademarks or service
marks have been appropriately capitalized. Que Publishing cannot attest to the
accuracy of this information. Use of a term in this book should not be regarded
as affecting the validity of any trademark or service mark.
Warning and Disclaimer
Every effort has been made to make this book as complete and as accurate as
possible, but no warranty or fitness is implied. The information provided is on an
“as is” basis. The author and the publisher shall have neither liability nor
responsibility to any person or entity with respect to any loss or damages arising
from the information contained in this book.
Special Sales
For information about buying this title in bulk quantities, or for special sales
opportunities (which may include electronic versions; custom cover designs; and
content particular to your business, training goals, marketing focus, or branding
interests), please contact our corporate sales department at
[email protected] or [phone].
For government sales inquiries, please contact
[email protected].
For questions about sales outside the U.S., please contact
[email protected].
Contents at a Glance
Introduction
1 Smoothing and Its Alternatives
2 Diagnosing Trend and Seasonality
3 Working with Trended Time Series
4 Initializing Forecasts
5 Working with Seasonal Time Series
6 Names, Addresses, and Formulas
7 Multiplicative and Damped Trend Models
Index
Contents
Introduction
1 Smoothing and Its Alternatives
Regression in Forecasting
Curvilinear Relationships
Piecewise Regression
Polynomial Regression
TREND() and LINEST()
Using the Chart’s Trendline
Regression or Smoothing?
Comparing Smoothing with Regression with Forecasting Errors
Additional Rationales for Smoothing
Pushing the Forecast Horizon
2 Diagnosing Trend and Seasonality
Inside the Autocorrelation
The Magnitude and the Direction of a Correlation
The Definitional Formula for Pearson’s Correlation
Autocorrelation and Averages
Looking at Individual Cases
Testing for Trend in the Observations
Understanding the Meaning of ACFs
Interpreting the ACFs
Using the ACF Add-in
Testing All the ACFs at Once
Testing for Seasonality in a Time Series
Removing Trend
Independent Residuals
Analyzing the Regression with LINEST()
Applying the Models Comparison Approach
Time Series Diagnostics and the Analysis of Covariance
3 Working with Trended Time Series
Smoothing: The Basic Idea
The Smoothing Equation’s Error Correction Form
Using Excel’s Solver to Choose Alpha
The Equation’s Smoothing Form
About the Smoothing Approaches to Forecasting
A Time Series’ Pattern
Additive and Multiplicative Models
Initializing Values
Initializing Versus Validation
Terminology Problems
More Smoothing Terminology
Dealing with Trend: Holt’s Linear Exponential Smoothing
Dealing with Trend by Differencing
Forecasting the Differences
Using Holt’s Linear Exponential Smoothing
Forecasting a Level
Forecasting a Trend
Forecasting the Next Level and the Next Trend
Extending the Forecast
Optimizing the Constants
Going Beyond the Next-Step-Ahead Forecast
Validating the Results
4 Initializing Forecasts
Setting Initial Values
Getting an Initial Forecast with Backcasting
Getting an Initial Forecast with Optimization
Initial Forecast in Simple Exponential Smoothing
Initial Forecasts with Holt’s Method
Understanding the Relationship Between Holt’s Method and Linear