Question

The DoorCo Corporation is a leading manufacturer of garage doors. All doors are manufactured in their plant in Carmel, Indiana, and shipped to distribution centers or major customers. DoorCo recently acquired another manufacturer of garage doors, Wisconsin Door, and is considering moving its wood-door operations to the Wisconsin plant. Key considerations in this decision are the transportation, labor, and production costs at the two plants. Complicating matters is the fact that marketing is predicting a decline in the demand for wood doors. The company developed three scenarios: 1. Demand falls slightly, with no noticeable effect on production. 2. Demand and production decline 20%. 3. Demand and production decline 40%. The following table shows the total costs under each decision and scenario. Slight Decline 20% Decline 40% Decline Stay in Carmel $1,000,000 $900,000 $840,000 Move to Wisconsin $1,200,000 $915,000 $800,000 What decision should DoorCo make using each strategy? 1. aggressive strategy: Select] with payoff [Select] 2. conservative strategy: (Select] with payoff [ Select] 3. opportunity-loss strategy: [Select] with maximum

You need to show your work in worksheet DoorCo.

Answer 1

The completed Excel sheet is following:

A B E F. G H — 1 2. с D Costs (in $000) Slight Decline 20% Decline 40% Decline 1000 900 840 1200 915 800 Best 1000 3 4 Stay In Carmel Move to Wisconsin Worst 840 800 Opportunity Loss Std Dev 200 80.82904 40 205.9328 1200 5 6 Opportunity loss 7 8 Stay In Carmel Move to Wisconsin Slight Decline 20% Decline 40% Decline 200 15 0 0 0 40 Max regret 200 40 9 10 11 12 13 14 15 16 17 18 19 0.15 0.4 0.45 Slight Decline 20% Decline 40% Decline Expected Payoff 1000 900 840 888 1200 915 800 906 Stay In Carmel Move to Wisconsin

EXCEL FORMULA:

А B с E F G — H 1 2 D Costs (in $000) 20% Decline 900 915 Best Slight Decline Stay In Carmel 1000 Move to Wisconsin 1200 40% Decline 840 3 =MAX(C3:E3) =MAX(C4:14) Worst Opportunity Loss =MIN(C3:E3) =F8 =MIN(C4:E4) =F9 Std Dev =STDEV(C3:E3) ESTDEV(C4:E4) 4 800 5 6 7 Opportunity loss Slight Decline 20% Decline 40% Decline Max regret Stay In Carmel =MAX(C$3:C$4)-C3 =MAX(D$3:D$4)-D3 =MAX(E$3:E$4)-E3 =MAX(C8:E8) Move to Wisconsin =MAX(C$3:0$4)-C4 =MAX(D$3:D$4)-D4 =MAX(E$3:E$4)-E4 =MAX(C9:E9) 8 9 0.4 20% Decline 10 11 12 13 14 15 16 17 18 0.15 Slight Decline Stay In Carmel 1000 Move to Wisconsin 1200 0.45 40% Decline 840 800 900 Expected Payoff =SUMPRODUCT(C14:E14,$C$12:$E$12) =SUMPRODUCT(C15:E15,$C$12:$E$12) 915

1) aggressive strategy: Move to Wisconsin with payoff  $ 1,200,000

2) conservative strategy: Stay in Carmel with payoff  $ 840,000

3) opportunity-loss strategy: Move to Wisconsin with maximum regret $ 40,000

4) Based on standard deviation, most risky decision is the one with high standard deviation. More risky decision is: Move to Wisconsin

5) The best expected value decision is  Move to Wisconsin with payoff  $ 906,000

6) Decision tree is following:

Slight Decline $1,000 $1,000 0.00 0.15 $900 Stay in Carmel $0 0.00 20% Decline $900 0.40 0.00 5888 $840 40% Decline $840 0.45 0.00 $1,200 $906 Slight Decline $1,200 0.15 0.15 Move to Wisconsin $915 20% Decline $915 $0 1.00 0.40 0.40 $906 40% Decline $800 $800 0.45 0.45