## Assignment 6

- Workbook/Data Set for Problems 1 and 2 -- download and complete, then submit with results for both problems.

## Problem 1 (15 Points) - Forecasting with Delphi

The senior manager at

*Wizbang*, a chain of appliance stores, needs to get an estimate of the number of Google Home devices they will likely sell during the holiday season. It is a new product for them and therefore they do not have historical sales information they can use to build a quantitative model. Patricia proposed that she run a Delphi session to get an estimate. To start, she scheduled a meeting with all of the sales reps in their four stores and asked them all to come up with a demand estimate. Unfortunately, there was little consensus so she wanted to run a second session that would provide a range rather than a single number. Also, the 47 sales reps was a bit much to handle, so she scheduled a second session of only the 7 senior sales leads. Furthermore, Patricia decided to run a wide-band Delphi session. She got her first round of private estimates and they had a good discussion after that round where a lot of useful information was uncovered; in addition, several new assumptions that different leads made were exposed. That led to a more narrow set of estimate for the second round. Her results can be found in the worksheet "*Delphi*" in the workbook -- which also contains the data set for Problem (2).- Calculate the 95% prediction interval rounded to the nearest whole number and place the results in cells B15:B16. Use the same workbook for Problem (2).

## Problem 2 (85 Points) - Forecasting with Predictive Analytics

*Wizbang*is a rapidly expanding chain of appliance stores. Their eCommerce department is trying to determine how many customers will visit their webstore weekly so they can staff customer service.

- (35 Points)
**Weighted Moving Average (WMA)**: In cell B52 of the "*Analysis*" worksheet, create a forecast for time period 49 using a weighted moving average (WMA) model of the prior 3 time periods with weights of 4 for the most recent time period, 2 for the one before that and 1 for the other. In column D, calculate the absolute deviation and then calculate the MAD for your WMA forecast. Provide the MAD in cell E52. - (10 Points)
**Linear Regression Trend Line (LRTL)**: Create a new worksheet tab called "*Trend Analysis*" and create a line graph in that worksheet. The scatter plot should have the time period on the x-axis and the visits on the y-axis. On the line graph show the value for the next time period and the linear regression equation and its R-Squared. - (25 Points) Using the SLOPE and INTERCEPT functions calculate the forecast for the next time period (period 49) and add the forecast to cell B53 in the worksheet "
*Analysis*". In column E, calculate the absolute deviation and then calculate the MAD for your LRTL forecast. Provide the MAD in cell E53 in the worksheet "*Analysis*". - (10 Points) Calculate the R-Squared for the LRTL model and place the value into cell E55. Is it sufficient? Elaborate on the R-Squared in the merged cell A56.
- (5 Points) Using the two forecasts for time period 49, calculate the average forecast for time period 49 and place that value in cell D60.
- Put your name in merged cells D62:F62

## Grading Rubric & Submission Instructions

- Submit the completed workbook with the results in the correct cells.
- Correct use of modeling methods and correct forecasts
- Flexible Google Model

*Total Number of Earnable Points*: 100

*Approximate Time to Complete*: 3.0 hours

*Due Date*: see Content or Blackboard