MSDA 628 Supply Chain Business Analytics
Module 7 Assignment
General requirement
- Make sure that each forecasting model is on its own worksheet.
- Your Excel model must be executable. If the excel model is ran, it should generate the same results you submit. That means the spreadsheet has correct embedded formulae and Solver dialog boxes have all the inputs. If ‘Solve’ is clicked and your spreadsheet does not produce the reported results, a zero score will be assigned to that problem.
- You should use lecture examples as templates to solve the assignment problems.
- Name the Excel file as “Homework 7_Last Name.xlsx”.
- Please contact the instructor if you have any questions regarding the assignment.
The quarterly revenue data for a popular restaurant in the past six years are provided in the data file “Module 7 Assignment Data.xlsx” with a scatter plot for visualization. Forecast of revenue for the next year is needed to help determine the value of the restaurant.
1. Use the following methods to forecast for years 1-6:
- Three-Period Moving Average (MA(3))
- Three-Period Weighted Moving Average (WMA) using optimal weights that minimize MAD at the end of period 24.
- Exponential Smoothing (ES) using optimal α that minimizes MAD at the end of period 24.
- Holt’s Method using optimal α and β that minimize MAD at the end of period 24.
2. Evaluate the forecasting accuracy for each forecasting method and make a recommendation of which forecasting method the restaurant should use and why.
3. Use the preferred method selected in (2) to forecast the quarterly revenues for years 7 (i.e., periods 25-28).