Peter Sagan is in charge of maintaining hospital supplies at Champs Hospital | Complete Solution

1.    Peter Sagan is in charge of maintaining hospital supplies at Champs Hospital. During the past
year the mean weekly demand for a special type of tubing was 250 packages of this tubing with a
standard deviation of 18 packages of tubing. The lead time for receiving this tubing from the supplier is
1.5 weeks. Peter would like to maintain a 95% service level and places an order for 750 packages every
time an order is placed.
a) How much safety stock should be used for a 95% service level?
b) If the weekly demand is 250 and there is a 1.5 week lead time - what is the reorder point (95%
service level)?
c) How much safety stock should be used for a 99% service level?
d) If the weekly demand is 250 and there is a 1.5 week lead time - what is the reorder point (99%
service level)?
e) If the carrying cost per year is $0.50/unit/year - what is the additional cost associate with the
99% service level compared to 95% service level (i.e. cost of safety stock at 99% level -cost of
safety stock at 95% service level)?

All answers should be entered using two decimal places unless otherwise specified. If both decimalplaces are zeros – then just enter the integer value. Percentages should be entered without “%” sign -3.45% should be entered as 3.45.

2.    Jennifer Kelly is a WCU student who has just finished her junior year summarizes her grade point average (GPA) for each of the last 9 semesters.
a) Compute the forecasts using the naïve forecasting method. What is the MAD?
b) Compute the forecast for Senior Year – Fall Semester using the naïve forecasting method.
c) Compute the forecasts using the two-period moving average. What is the MAPE?
d) Compute the forecast for Senior Year – Fall Semester using the two-period moving average.
e) Compute the forecasts using the three-period weighted moving average using weights of 0.4,
0.35, and 0.25. What is the MAD?
f) Compute the forecast for Senior Year – Fall Semester using the three-period weighted moving
average using weights of 0.4, 0.35, and 0.25.
g) Use Solver to determine the weights for the three-year weighted moving average that minimizes
the MAD? What is the MAD?
h) Compute the forecast for Senior Year – Fall Semester using the three-period weighted moving
average using weights determined by the Solver.
i) Compute the forecasts using exponential smoothing with a smoothing constant of 0.35 and a
forecast for First Year – Fall Semester of 2.41. What is the MSE?
j) Compute the forecast for Senior Year – Fall Semester using exponential smoothing with a
smoothing constant of 0.35 and a forecast for First Year – Fall Semester of 2.41.
k) Comparing one of the error measurements (MAD, MSE, or MAPE) – which is the best forecasting
method? Which is the worst forecasting method?
Year    Semester    GPA
First Year    Fall    2.41
    Winter    2.93
    Spring    3.11
Sophomore    Fall    3.05
    Winter    3.01
    Spring    2.94
Junior    Fall    3.3
    Winter    3.49
    Spring    3.25

 
3.           A regional bus service examined their routes for the past 15 weeks. The data in Worksheet A2             provides and record of the total miles drive in each week. 
a) Compute the forecasts using the four-period moving average. What is the MAPE? 
b) Compute the forecast for Week 16 using the four-period moving average. 
c) Compute the forecasts using exponential smoothing with a smoothing constant of 0.18 and a forecast for Week 1 of 22,500. What is the MAD? 
d) Compute the forecast for Week 16 using exponential smoothing with a smoothing constant of 0.18 and a forecast for Week 1 of 22,500. 
e) Use Solver to determine the value of the smoothing constant for the exponential smoothing method with forecast for Week 1 of 22,500 that minimizes MAD. What is the MAD?
 f) Compute the forecast for Week 16 using the exponential smoothing method using the smoothing constant with forecast for Week 1 of 22,500 determined by the Solver. 
g) Compute the forecasts using linear regression using Excel’s slope and intercept function. What is the MAD?
 h) Compute the forecast for Week 16 using linear regression.
 i) Comparing one of the error measurements (MAD, MSE, or MAPE) – which is the best forecasting method? Which is the worst forecasting method?
Week    Miles 
1                       21,000 
2                    25,000 
3         23,000 
4         27,000 
5         22,000 
6         20,000 
7         24,000 
8         22,000 
9         26,000 
10         24,000 
11         19,000 
12         26,000 
13         23,000 
14         24,000 
15         20,000