Excel Data Analysis: Forecasting

Introduction

• Welcome
• Who is this course for?
• What you should know before watching this course
• Using the exercise files
• Using the challenges

1. Visually Displaying Your Time-Series Data

• What is time-series data?
• Plotting a time series
• Understanding level in a time series
• Understanding trend in a time series
• Understanding seasonality in a time series
• Understanding noise in a time series
• Creating a moving average chart
• Challenge: Analyze time-series data for airline miles
• Solution: Analyze time-series data for airline miles

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

• Exploring why some forecasts are better than others
• Computing the mean absolute deviation (MAD)
• Computing the mean absolute percentage error (MAPE)
• Calculating the sum of squared errors (SSE)
• Computing forecast bias
• Advanced forecast bias: Determining significance
• Challenge: Compute MAD, MAPE, and SSE for an NFL game
• Solution: Compute MAD, MAPE, and SSE for an NFL game

3. Using a Trendline for Forecasting

• Fitting a linear trend curve
• Interpreting the trendline
• Interpreting the R-squared value
• Computing standard error of the regression and outliers
• Exploring autocorrelation
• Challenge: Create a trendline to analyze R squared and outliers
• Solution: Create a trendline to analyze R squared and outliers

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

• When does a linear trend fail?
• Creating an exponential trend curve
• Computing compound annual growth rate (CAGR)
• Challenge: Fit an exponential growth curve, estimate CAGR, and forecast revenue
• Solution: Fit an exponential growth curve, estimate CAGR, and forecast revenue

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

• What is a seasonal index?
• Introducing the ratio-to-moving-average method
• Computing the centered moving average
• Calculating seasonal indices
• Estimating a series trend
• Forecasting sales
• Forecasting if the series trend is changing
• Challenge: Predicting future quarterly sales
• Solution: Predicting future quarterly sales

6. Forecasting with Multiple Regressions

• What is multiple regression?
• Preparing data for multiple regression
• Running a multiple linear regression
• Finding the multiple-regression equation and testing for significance
• How good is the fit of the trendline?
• Making forecasts from a multiple-regression equation
• Validating a multiple-regression equation using the TREND function
• Interpreting regression coefficients
• Challenge: Regression analysis of Amazon.com revenue
• Solution: Regression analysis of Amazon.com revenue

Conclusion

• Next steps