Excel Data Analysis: Forecasting

Course details

Professor Wayne Winston has taught advanced forecasting techniques to Fortune 500 companies for more than twenty years. In this course, he shows how to use Excel's data-analysis tools—including charts, formulas, and functions—to create accurate and insightful forecasts. Learn how to display time-series data visually; make sure your forecasts are accurate, by computing for errors and bias; use trendlines to identify trends and outlier data; model growth; account for seasonality; and identify unknown variables, with multiple regression analysis. A series of practice challenges along the way helps you test your skills and compare your work to Wayne's solutions.

Learning objectives
Show time-series data by plotting and displaying information.
Devise a moving average chart.
Recognize how to account for errors and bias.
Interpret and utilize trendlines.
Determine how to model exponential growth.
Compute the compound annual growth rate.
Analyze the impact of seasonality.
Identify the ratio-to-moving average method.

Concepts

0. Introduction

01 - Welcome
02 - Who is this course for
03 - What you should know before watching this course
04 - Using the exercise files
05 - Using the challenges

1. Visually Displaying Your Time-Series Data

06 - What is time-series data
07 - Plotting a time series
08 - Understanding level in a time series
09 - Understanding trend in a time series
10 - Understanding seasonality in a time series
11 - Understanding noise in a time series
12 - Creating a moving average chart
13 - Challenge - Analyze time-series data for airline miles
14 - Solution - Analyze time-series data for airline miles

2. How Good Are Your Forecasts Errors, Accuracy, and Bias

15 - Exploring why some forecasts are better than others
16 - Computing the mean absolute deviation (MAD)
17 - Computing the mean absolute percentage error (MAPE)
18 - Calculating the sum of squared errors (SSE)
19 - Computing forecast bias
20 - Advanced forecast bias - Determining significance
21 - Challenge - Compute MAD, MAPE, and SSE for an NFL game
22 - Solution - Compute MAD, MAPE, and SSE for an NFL game

3. Using a Trendline for Forecasting

23 - Fitting a linear trend curve
24 - Interpreting the trendline
25 - Interpreting the R-squared value
26 - Computing standard error of the regression and outliers
27 - Exploring autocorrelation
28 - Challenge - Create a trendline to analyze R squared and outliers
29 - Solution - Create a trendline to analyze R squared and outliers

4. Modeling Exponential Growth and Compound Annual Growth Rate (CAGR)

30 - When does a linear trend fail
31 - Creating an exponential trend curve
32 - Computing compound annual growth rate (CAGR)
33 - Challenge - Fit an exponential growth curve, estimate CAGR, and forecast revenue
34 - Solution - Fit an exponential growth curve, estimate CAGR, and forecast revenue

5. Seasonality and the Ratio-to-Moving-Average Method

35 - What is a seasonal index
36 - Introducing the ratio-to-moving-average method
37 - Computing the centered moving average
38 - Calculating seasonal indices
39 - Estimating a series trend
40 - Forecasting sales
41 - Forecasting if the series trend is changing
42 - Challenge - Predicting future quarterly sales
43 - Solution - Predicting future quarterly sales

6. Forecasting with Multiple Regressions

44 - What is multiple regression
45 - Preparing data for multiple regression
46 - Running a multiple linear regression
47 - Finding the multiple-regression equation and testing for significance
48 - How good is the fit of the trendline
49 - Making forecasts from a multiple-regression equation
50 - Validating a multiple-regression equation using the TREND function
51 - Interpreting regression coefficients
52 - Challenge - Regression analysis of Amazon.com revenue
53 - Solution - Regression analysis of Amazon.com revenue

Conclusion

54 - Next steps