Archives: Solution Manual

Forecasting Causal regression analysis Input Data Forecast Error Analysis Period Dep Variable (or) (Y) Indep Variable (or) (X) Forecast Error Absolute error Squared error Absolute % error Year 1 41 4000 39.610 1.390 1.390 1.933 3.39% Year 2 38 3500 38.327 -0.327 0.327 0.107 0.86% Problem 11-37 Year 3 36 1800 33.967 2.033 2.033 4.131 5.65% Year 4 43 2800 36.532 6.468 6.468

Forecasting Multiple regression Input Data Forecasts and Error Analysis Y x 1 x 2 Forecast Error Absolute error Squared error Absolute % error Student 1 3.700 690.000 34.000 3.798 -0.098 0.098 0.010 2.64% Student 2 3.000 610.000 29.000 3.474 -0.474 0.474 0.225 15.81% Problem 11-36 Student 3 3.250 480.000 24.000 3.056 0.194 0.194 0.038 5.97% Student 4 4.000 740.000 39.000 4.065

Forecasting Causal regression analysis Input Data Forecast Error Analysis Period GPA CBAT Forecast Error Absolute error Squared error Absolute % error Student 1 3.70 690 3.737 -0.037 0.037 0.001 0.99% Student 2 3.00 610 3.537 -0.537 0.537 0.289 17.91% Student 3 3.25 480 3.214 0.036 0.036 0.001 1.11% Slope 0.002 SE 0.290 Predicted GPA 3.587 630 Correlation 0.663 r-squared 0.439 Lower 90% intvl 3.22 Upper

Forecasting Multiple regression Input Data Forecasts and Error Analysis Y x 1 x 2 Forecast Error Absolute error Squared error Absolute % error Car 8 $7,900.00 73,100 68822.383 -922.383 922.383 850790.303 11.68% Car 9 $6,200.00 77,500 87634.869 -1434.869 1434.869 2058849.257 23.14% Car 10 $7,500.00 85,700 96805.330 694.670 694.670 482566.403 09.26% Average 1187.537 1765683.169 12.50% Regression Line MAD MSE MAPE Car 6 $10,900.00

Forecasting Causal regression analysis Input Data Forecast Error Analysis Period Dep Variable (or) (Y) Indep Variable (or) (X) Forecast Error Absolute error Squared error Absolute % error SE 1532.393 Forecast 10861.754 46700 Correlation -0.850 r-squared 0.722 Lower 95% interval $8,341.19 Upper 95% interval $13,382.32 Problem 11-33 Car 1 14200 11600 14214.718 -14.718 14.718 216.606 0.10% Car 2 14000 22800 13144.826 855.174 855.174 731322.727 6.11% Car 3 10500

Forecasting Causal regression analysis Input Data Forecast Error Analysis Student Dep Variable (or) (Y) Indep Variable (or) (X) Forecast Error Absolute error Squared error Absolute % error D 3.55 730 3.469 0.081 0.081 0.007 2.27% F 3.98 430 2.614 1.366 1.366 1.865 34.31% H 2.63 520 2.871 -0.241 0.241 0.058 9.16% J 2.09 540 2.928 -0.838 0.838 0.702 40.09% L 4.00 750 3.526

Forecasting Causal regression analysis Input Data Forecast Error Analysis Period Dep Variable (or) (Y) Indep Variable (or) (X) Forecast Error Absolute error Squared error Absolute % error Year 1 0.29 2.34 0.357 -0.067 0.067 0.004 23.11% Problem 11-31 Year 2 0.14 1.70 0.133 0.007 0.007 0.000 5.29% Year 3 0.19 1.89 0.199 -0.009 0.009 0.000 4.85% Year 4 0.16 1.94 0.217 -0.057 0.057

Sales Seasonal index Seasonalized sales $130,000 1.25 $162,500 Problem 11-30 $150,000 0.95 $142,500 $170,000 0.75 $127,500 $190,000 1.05 $199,500

Season Demand Average demand Ratio Season index Year 3 demand Fall Y1 125 175.0 0.71 0.86 192.9 Winter Y1 225 175.0 1.29 1.43 321.4 Problem 11-29 Spring Y1 75 175.0 0.43 0.47 106.1 Summer Y1 210 175.0 1.20 1.24 279.6 Fall Y2 175 175.0 1.00 Winter Y2 275 175.0 1.57 Spring Y3 90 175.0 0.51 Summer Y4 225 175.0 1.29

Forecasting Causal regression analysis Input Data Forecast Error Analysis Period Dep Variable (or) (Y) Indep Variable (or) (X) Forecast Error Absolute error Squared error Absolute % error Year 1 30 67.3 27.003 2.997 2.997 8.982 9.99% Year 2 27 70.6 28.802 -1.802 1.802 3.246 6.67% Problem 11-28 Year 3 34 82.4 35.233 -1.233 1.233 1.519 3.63% Year 4 35 84.7 36.486 -1.486 1.486

Forecasting Linear trend analysis Input Data Forecast Error Analysis Period Actual value (or) Y Period number (or) X Forecast Error Absolute error Squared error Absolute % error Year 1 30 1 27.018 2.982 2.982 8.891 9.94% Year 2 27 2 30.303 -3.303 3.303 10.910 12.23% Problem 11-27 Year 3 34 3 33.588 0.412 0.412 0.170 1.21% Year 4 35 4 36.873 -1.873 1.873

Forecasting Linear trend analysis Input Data Forecast Error Analysis Period Actual value (or) Y Period number (or) X Forecast Error Absolute error Squared error Absolute % error Year 1 14 1 12.026 1.974 1.974 3.898 14.10% Year 2 9 2 13.763 -4.763 4.763 22.690 52.93% Problem 11-26 (a) Year 3 12 3 15.501 -3.501 3.501 12.258 29.18% Year 4 14 4 17.239 -3.239

Forecasting Exponential smoothing Input Data Forecast Error Analysis Period Actual value Forecast Error Absolute error Squared error Absolute % error Month 1 83.0 78.000 Month 2 81.5 79.000 2.500 2.500 6.250 3.07% Problem 11-25(a) Month 3 77.8 79.500 -1.700 1.700 2.890 2.19% Month 4 84.7 79.160 5.540 5.540 30.692 6.54% Month 5 84.3 80.268 4.032 4.032 16.257 4.78% Month 6 85.8 81.074

Forecasting Exponential smoothing Input Data Forecast Error Analysis Period Actual value Forecast Error Absolute error Squared error Absolute % error Week 1 21 22.000 Week 2 25 21.700 3.300 3.300 10.890 13.20% Week 3 23 22.690 0.310 0.310 0.096 1.35% Problem 11-24 Week 4 27 22.783 4.217 4.217 17.783 15.62% Week 5 22 24.048 -2.048 2.048 4.195 9.31% Week 6 20 23.434 -3.434 3.434

Forecasting Exponential smoothing Input Data Forecast Error Analysis Period Actual value Forecast Error Absolute error Squared error Absolute % error Month 17 55 32.640 7.360 7.360 54.175 18.40% Month 18 40 33.744 21.256 21.256 451.830 38.65% Month 19 35 36.932 -1.932 1.932 3.733 5.52% Month 20 60 36.642 -11.642 11.642 135.544 46.57% Month 21 75 34.896 20.104 20.104 404.172 36.55% Month

Forecasting 3 period moving average Input Data Forecast Error Analysis Period Actual Value Forecast Error Absolute error Squared error Absolute % error Month 1 9 Month 2 12 Month 3 14 Month 4 811.667 -3.667 3.667 13.444 45.83% Month 5 13 11.333 1.667 1.667 2.778 12.82% Problem 11-22(a) Month 6 15 11.667 3.333 3.333 11.111 22.22% Month 7 912.000 -3.000 3.000 9.000

Forecasting Causal regression analysis Input Data Forecast Error Analysis Period Dep Variable (or) (Y) Indep Variable (or) (X) Forecast Error Absolute error Squared error Absolute % error Month 1 3 4 4.238 -1.238 1.238 1.533 41.27% Month 2 6 5 5.210 0.790 0.790 0.625 13.17% Problem 11-21 Month 3 7 8 8.124 -1.124 1.124 1.263 16.05% Month 4 5 7 7.152 -2.152 2.152

Forecasting 3 period weighted moving average Input Data Forecast Error Analysis Period Actual value Weights Forecast Error Absolute error Squared error Absolute % error Week 1 22 1 Week 2 26 3 Problem 11-20(a) Week 3 23 5 Week 4 27 23.889 3.111 3.111 9.679 11.52% Week 5 21 25.556 -4.556 4.556 20.753 21.69% Week 6 25 23.222 1.778 1.778 3.160

Forecasting Exponential smoothing Input Data Forecast Error Analysis Period Actual value Forecast Error Absolute error Squared error Absolute % error Year 1 480 440.000 Problem 11-19 (a) Year 2 525 454.000 71.000 71.000 5041.000 13.52% Year 3 548 478.850 69.150 69.150 4781.723 12.62% Year 4 593 503.053 89.948 89.948 8090.553 15.17% Year 5 614 534.534 79.466 79.466 6314.825 12.94% Average 77.391 6057.025

Forecasting 3 period weighted moving average Input Data Forecast Error Analysis Period Actual value Weights Forecast Error Absolute error Squared error Absolute % error Week 1 22 0.000 Week 2 27 0.417 Problem 11-18(a) Week 3 30 0.583 Week 4 21 28.750 -7.750 7.750 60.063 36.90% Week 5 33 24.750 8.250 8.250 68.063 25.00% Week 6 28 28.000 0.000 0.000 0.000

Forecasting 2 period moving average Input Data Forecast Error Analysis Period Actual Value Forecast Error Absolute error Squared error Absolute % error Week 1 22 Week 2 27 Week 3 30 24.500 5.500 5.500 30.250 18.33% Week 4 21 28.500 -7.500 7.500 56.250 35.71% Problem 11-17 Week 5 33 25.500 7.500 7.500 56.250 22.73% Week 6 28 27.000 1.000 1.000 1.000

Forecasting Exponential smoothing Input Data Forecast Error Analysis Period Actual value Forecast Error Absolute error Squared error Absolute % error Day 1 622 622.000 Day 2 418 622.000 -204.000 204.000 41616.000 48.80% Problem 11-16(a) Day 3 608 564.477 43.523 43.523 1894.278 7.16% Day 4 752 576.749 175.251 175.251 30712.820 23.30% Day 5 588 626.166 -38.166 38.166 1456.639 6.49% Day 6 656 615.404

Forecasting 2 period moving average Input Data Forecast Error Analysis Period Actual Value Forecast Error Absolute error Squared error Absolute % error Day 1 622 Day 2 418 Problem 11-15 Day 3 608 520.000 88.000 88.000 7744.000 14.47% Day 4 752 513.000 239.000 239.000 57121.000 31.78% Day 5 588 680.000 -92.000 92.000 8464.000 15.65% Day 6 656 670.000 -14.000 14.000 196.000

Forecasting 3 period moving average Input Data Forecast Error Analysis Period Actual Value Forecast Error Absolute error Squared error Absolute % error Semester 1 2.4 Semester 2 2.9 Semester 3 3.1 Problem 11-14(a) Semester 4 3.2 2.800 0.400 0.400 0.160 12.50% Semester 5 3 3.067 -0.067 0.067 0.004 2.22% Semester 6 2.9 3.100 -0.200 0.200 0.040 6.90% Semester 7 2.8 3.033 -0.233

Forecasting 2 period moving average Input Data Forecast Error Analysis Period Actual Value Forecast Error Absolute error Squared error Absolute % error Period 1 4 Period 2 8 Problem 11-13(a) Period 3 6 6.000 0.000 0.000 0.000 0.00% Period 4 6 7.000 -1.000 1.000 1.000 16.67% Period 5 9 6.000 3.000 3.000 9.000 33.33% Period 6 8 7.500 0.500 0.500 0.250

2. Select the items or quantities that are to be forecasted. 4. Select the forecasting model. 6. Validate the forecasting model. 8. Implement the results. account any factors that may influence the quantity being forecasted. Causal models use historical data as well. Time-series models use only historical data. useful when

Forecasting Causal regression analysis Input Data Forecast Error Analysis Period Dep Variable (or) (Y) Indep Variable (or) (X) Forecast Error Absolute error Squared error Absolute % error 1996 68 22 67.122 0.878 0.878 0.772 1.29% 1997 75 25 76.081 -1.081 1.081 1.169 1.44% Internet Case: Kwik Lube (#1) 1998 75 24 73.095 1.905 1.905 3.631 2.54% 1999 78 26 79.068 -1.068 1.068 1.140

1999 78 26 2000 99 33 2001 104 35 2002 120 39 2003 133 44 Average Kwik Lube sales = 1.419 + 2.986 × (Average industry sales) Forecasted average Kwik Lube sales for 2004 and 2005 can be

Forecasting Multiplicative decomposition 4 seasons Input Data Seasonal Index Computation Forecast Error analysis Period Actual value (Y) Time period (X) Centered average Seasonal ratio Seasonal index Unseason alized value Unseason alized Forecast Seasonali zed Forecast Error Absolute error Squared error Absolute % error Quarter 28 94 28 90.000 1.044 1.020 92.188 86.636 88.339 5.661 5.661 32.043 6.02% Quarter 29 93 29 90.000 1.033 1.052 88.438 87.868 92.400 0.600 0.600

an indicator variable for three of the four seasons. We do not include a variable for the fourth season since this would make the columns linearly dependent. The resulting equation (see file Ch11 Internet Case Human Resources.XLS, sheet Regression) is: Average participants = 54.028

Forecasting Causal regression analysis Input Data Forecast Error Analysis Period Dep Variable (or) (Y) Indep Variable (or) (X) Forecast Error Absolute error Squared error Absolute % error 1996 53353 1 53549.709 -196.709 196.709 38694.466 0.37% Internet Case: Akron Zoological Park (#1) 1997 61417 2 62747.018 -1330.018 1330.018 1768948.364 2.17% 1998 63853 3 71944.327 -8091.327 8091.327 65469577.034 12.67% 1999 63034 4 81141.636 -18107.636 18107.636

where 1996 = year 1, 1997 = year 2, and so on. R2 = 77.6%. MAPE = 9.94%. The forecasts for 2006 (year 11) and 2007 (year 12) are 145,523 and 154,720, respectively. Using a weighted average of $2.875 to represent gate receipts per person, revenues for 2006 and

Forecasting Multiplicative decomposition 5 seasons Input Data Seasonal Index Computation Forecast Error analysis Game 9 25300 9 35340.000 0.716 0.747 33860.962 36526.391 27291.537 -1991.537 1991.537 3966218.761 7.87% Game 10 36200 10 35340.000 1.024 1.015 35652.064 36998.787 37567.421 -1367.421 1367.421 1869840.031 3.78% Game 11 35900 11 37400.000 0.960 1.025 35019.788 37471.184 38413.011 -2513.011 2513.011 6315226.481

Forecasting Causal regression analysis Input Data Forecast Error Analysis Period Dep Variable (or) (Y) Indep Variable (or) (X) Forecast Error Absolute error Squared error Absolute % error Year 1 51.8 6512 53.020 -1.220 1.220 1.489 2.36% North-South Airline Year 2 54.92 8404 57.937 -3.017 3.017 9.103 5.49% Year 3 69.7 11077 64.884 4.816 4.816 23.197 6.91% Year 4 68.9 11717 66.547 2.353 2.353

Prob(>300K) 62.00% Average $347,249 Initial employees = 1000 1 $333,555 Monthly premium = $100 2 $538,318 Prob of major claim = 0.10 to 0.15 D Uniform 3 -$9,367 12 $824,977 13 $412,693 14 $330,392 15 $890,055 16 $116,103 17 $754,309 18 $590,850 19 $393,652 20 $192,881 21 $484,136 22 $252,467 23 $115,872 24 $219,318 25 $315,590 26 -$90,338 27 $250,424 28 -$59,195 29 -$60,173 30 $441,419 31 $124,603 32 $471,183 33 $492,338 34 $401,380 35 $178,737 36 $415,293 37 $97,389 38

3 2.0% $61,200.00 $6,120.00 11.33% $13,630.85 $19,750.85 Max raise % 8 2 $1,309,763 5 1.0% $64,902.60 $6,490.26 -2.93% $26,422.58 $32,912.84 Min return % -5 4 $880,822 7 8.0% $74,300.50 $7,430.05 10.03% $43,625.27 $51,055.32 6 $1,341,662 9 5.0% $81,916.30 $8,191.63 -1.64% $67,351.19 $75,542.82 8 $876,945 11 4.0% $86,896.81 $8,689.68 8.75% $94,657.19 $103,346.87 10 $1,075,667 13 8.0%

H A D C F B I G E J Start Data Table Calculations set to Manual. Press F9 to recalculate simulation values. Run Time Activity Immed. pred. Minimu m time Maximum time Activity time Start time Finish time 16 54.00 18 58.00 20 44.00 22 62.00 24 56.00 26 51.00 28 51.00 30 47.00 32 46.00 34 41.00 36 59.00 38 56.00 40 63.00 42 49.00 44 49.00 46 50.00 48 49.00 50 58.00 52 47.00 C A, B 3 8 5.0 17.0 22.0 4 55.00 D C 5

C I G E B H F A D Data Table Calculations set to Manual. Activity Immed. pred. Optimistic time (a) Most likely time (m) Pessimistic time (b) Expected time Std Dev Activity time Start time Finish time Run Compl Time Based on 3000 replications: 14 51.34 Project completion time = 52.57 15 49.69 Prob (>= 50 days) = 0.7750 16 53.59 17 53.30 19 48.36 21 50.98 23 49.68 25 54.59 27 60.79 29 51.49 31 50.87 33 48.37 35 45.22 37 48.62 39 50.67 41 55.32 43

F I A D K C B H G E Start J L End Data Table Calculations set to Manual. Press F9 to recalculate simulation values. Run Time Activity Immed. pred. Mean Std dev Activity time Start time Finish time F B, D, E 10.00 0.33 10.03 33.03 43.06 Prob (>= 75 days) = 0.0350 7 64.04 G B, D, E 7.33 0.67 6.06 33.03 39.09 8 72.39 H F 15.00 0.33 15.21 43.06

B I E C D H F A G Data Table Calculations set to Manual. Activity Immed. pred. Optimistic time (a) Most likely time (m) Pessimistic time (b) Expected time Std Dev Activity time Start time Finish time Run Compl Time A—3 6 8 5.83 0.83 6.15 0.00 6.15 1 35.11 10 33.53 Based on 1 replication: 11 34.25 Project completion time = 35.11 12 33.91 13 35.51 Based on 3000 replications: 14 36.21 Project completion time =

A H F K C B I D Start E G J End Data Table Calculations set to Manual. Press F9 to recalculate simulation values. Run Profit Activity Immed. pred. Mean Std dev Activity time Start time Finish time H F 6.00 1.00 5.31 25.36 30.67 9 33.78 K H, I 7.33 1.33 4.96 30.67 35.63 12 35.58 End J, K 0.00 0.00 0.00 35.63 35.63 13 34.93 14 39.63 16 33.69 18 37.97 20 34.97 22 34.88 24 38.92 26

Data Table (Calculations set to Manual. Press F9 to recalculate). Custo- mer Time between arrivals Arrival time Start service Service time End service Wait time Queue length RN intvl LL Arrival Proba- bility Run Queue Length Prob (Wait>=3) 0 0 0 0 0 0 0 0 1 2.73 0.80 35 5116 128 1129 12 436 1.45 0.61 36 3119 129 4133 10 437 6.68 0.97 37 4123 133 3136 10

Data Table Calculations set to Manual. Press F9 to recalculate. Custo- mer # Time between arrivals Arrival time Start service Service time End service Wait time Queue length Run Total cost per day 0 0.00 0.00 0.00 0.00 0.00 0.00 0 Arrival Distribution (Exponential) 1 $1,307.20 4 23.18 54.86 54.86 7.30 62.16 0.00 0 Mean (minutes) 9 5 $1,131.20 6 12.25 67.46 69.02 7.09

Data Table (Calculations set to Manual. Press F9 to recalculate). Unit cost $12 Run Profit 31 $287,100 32 $177,820 33 $201,240 34 $232,100 35 $219,980 36 $208,980 37 $226,350 38 $102,460 39 $219,990 40 $194,610 41 $247,370 42 $265,430 23 $196,680 24 $249,550 25 $299,560 26 $239,600 27 $239,430 28 $222,090 29 $228,500 30 $272,430 Problem 10-42 Original price $30 1 $180,650 Sale price $20 2 $228,250 Order quantity 17,000 3 $234,990 4 $207,710 5 $229,080 Mean Std

Data Table Calculations set to Manual. Press F9 to recalculate. Fail # Time between failures Actual failure time Begin maint Wait for maint Maint time End maint Time in system Begin maint Wait for maint Maint time End maint Time in system Run Annual downtime savings 4 23.63 178.27 180.99 2.72 32.20 213.19 34.91 178.27 0.00 16.46 194.73 16.46 Time between failures (hrs) 30 4 $19,998.24 5

Data Table (Calculations set to Manual. Press F9 to recalculate). Time Between Arrival Begin Wait for Service End Time in Run Wait P(T>=3) Customer 29 486 86 0 3 89 331 2.67 0.335 30 187 89 2 4 93 632 1.73 0.295 31 592 93 1 2 95 333 3.25 0.445 32 193 95

Problem 10-39 Data Table Calculations set to Manual. Press F9 to recalculate. Demand 153 12 $110.00 23.53% Sales 153 13 $170.00 0.00% 14 $170.00 0.00% Revenue $229.50 15 $144.50 10.00% Cost $85.00 16 $102.50 26.47% Number of pretzels available 170 Run Profit %unsold Cost per pretzel $0.50 1 $144.50 10.00% 10 $111.50 22.94% Blowout?

Data Table (Calculations set to Manual. Press F9 to recalculate). Time Between Arrival Begin Wait for Service End Time in Run Wait System Utilization Customer 37 4108 109 1 3 112 439 3.46 6.03 0.915 38 3111 112 1 4 116 540 1.49 4.00 0.839 39 1112 116 4 3 119 741 6.16

Data Table Calculations set to Manual. Press F9 to recalculate. House Home? Donate? Type? $15? $25? $35? Other $ Run Total 27 Yes Donate $25 $0 $25 $0 $0 27 $60 28 Yes Decline N/A $0 $0 $0 $0 28 $250 29 No N/A N/A $0 $0 $0 $0 29 $240 30 Yes Decline N/A

Data Table Calculations set to Manual. Press F9 to recalculate. Probability of residential 0.70 No. of projects: 14 Run Profit Resid revenue (Normal) 1 $10,323 Mean $500 Project Type Revenue 2 $6,312 Standard deviation $50 1 Resid $508 3 $6,712 10 0.15 0.00 8 Resid $440 10 $12,665 12 0.20 0.35 10 Resid