Description
There will be an excel worksheet attached where excel tools for forecasting.
Unformatted Attachment Preview
Problem 5 (45 points): Regression and correlation analysis
Bus and subway ridership for the summer months in London, England, is believed to be tied with heavily to the number
of tourists visiting the city. During the past 10 years, the following data have been obtained.
Answer the following questions based on your Excel outputs (5 pts each).
a. Use the Excel tool to create a scatter diagram.
b. Is a linear regression model reasonable? Answer the question based on your scatter diagram.
c. Develop a regression output using the Excel Regression tool.
d. Find the estimated regression equation based on your answer for c.
e. What is the expected ridership if 10 million tourists visit London in a year?
f. Interpret the coefficient for X (i.e., the slope of the regression line).
g. Is the model significant? Explain.
h. Find and interpret the correlation coefficient r.
2
i. Find and interpret the coefficient of determination r .
Note: You may organize and label your answers on another worksheet or use this sheet.
Year
No. of tourists
(summer month) (in millions)
7
1
2
2
3
6
4
4
5
14
6
15
7
16
8
12
9
14
20
10
Ridership
(in millions)
1.5
1.0
1.3
1.5
2.5
2.7
2.4
2.0
2.7
4.4
Problem 4 (10 points) Based on your results from Problems 1, 2, & 3, which forecasting method would you recommend? Explain.
(Note: You may copy and paste your results of MAD, MSE, and MAPE from 1, 2, & 3 here for an easier comparison.
Problem 3 (15 points): Exponential smoothing
a (6 pts) Forecast number of surgeries using exponential smoothing with a smoothing constant of 0.8 and assuming F1=A1.
b (9 pts) Compute MAD, MSE, and MAPE.
Demand for heart transplant surgery at Washington General Hospital has increased steadily in the past few years,
as shown below. The director of medical services wants to choose a forecast method to predict the demand in year 6
Year
1
2
3
4
5
6
Heart Transplants
45
50
52
56
58
?
Forecasts
|et|
et2
|et|/At
MAD
MSE
MAPE
Average
Problem 2 (15 points): Weighted moving average
a (6 pts): Forecast the number of surgeries using a 3-year weighted moving average, using weights of 0.7, 0.2, and 0.1, with the heaviest weights applied to
b (9 pts) Compute MAD, MSE, and MAPE.
Demand for heart transplant surgery at Washington General Hospital has increased steadily in the past few years,
as shown below. The director of medical services wants to choose a forecast method to predict the demand in year 6
Year
1
2
3
4
5
6
Heart Transplants
45
50
52
56
58
?
Forecasts
|et|
et
2
|et|/At
MAD
MSE
MAPE
Average
Problem 1 (15 points): Moving average
a (6 pts) Use a 2-year moving average to forecast the number of surgeries from year 3 to year 6.
b (9 pts) Compute MAD, MSE, and MAPE.
Demand for heart transplant surgery at Washington General Hospital has increased steadily in the past few years,
as shown below. The director of medical services wants to choose a forecast method to predict the demand in year 6
Year
1
2
3
4
5
6
Heart Transplants
45
50
52
56
58
?
Forecasts
|et|
et2
|et|/At
MAD
MSE
MAPE
Average
OM Excel Assignment 1
#
Topic
Points
1
Moving average
15
2
Weighted moving average
15
3
Exponential smoothing
15
4
Applications of MAD, MSE, & MAPE
10
5
Regression & correlation analysis
45
Total
100
Note: No late submission or redo assignments will be accepted for any reason/excuse!
Purchase answer to see full
attachment
