Join Conrad Carlberg for an in-depth discussion in this video The Excel Forecast Sheet, part of Business Analytics: Forecasting with Seasonal Baseline Smoothing.
- [Instructor] I'm in exercise file 02_02_Begin…in the sheet named Data Forecast Sheet.…If you have used Microsoft Excel 2016,…you might have noticed a new feature…on the ribbon's Data tab called Forecast Sheet.…The available documentation for the forecast sheet…does not specify how the forecasts are created,…but the names of the functions…and some of the vocabulary used in the documentation…indicates that the intent is to duplicate the approaches…that are usually termed simple exponential smoothing,…Holt's method for forecasting trended baselines,…and and Holt winters or seasonal smoothing,…as discussed in the present course.…
Here's a brief look at how to use the forecast sheet.…I'll use this data which comes…from a generally accessible website…so that you can duplicate the analysis if you wish to do so…using Excel formulas or the Excel forecast sheet or R.…To use the forecast sheet,…put time period identifiers in one column,…such as column A here.…In a column adjacent…and to the right of the time period identifiers,…
- Identify what distinguishes seasonality from a trend or a cycle.
- Explore how to use absolute and relative references in defined names, and recall that absolute reference always remain static while relative references change depending on precedent.
- Identify seasonality in a baseline by examining autocorrelation functions in a correlogram.
- Explore how to initialize seasonal effects in a baseline.
- Forecast the current level of the baseline and the current seasonal effect from prior observations, forecasts, and smoothing constants.
- Quantify a measure of the aggregate error in a forecast, and minimize it using Solver.
- Establish a baseline in a data object and forecast from that baseline in R.
- Compare Excel and R results.