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

please show excel and formulas for every step. thank you.

Answer 1

The completed Excel sheet is following:

1 DoorCo Costs (in $ 000) Slight Decline 20% Decline 40% Decline Worst Opportunity Loss Std Dev Best Stay in Ca 1000 900 840 840 1000 160 80.83 1200 800 1200 400 205.93 Move to V 915 800 6 Opportunity loss Slight Decline 20% Decline 40% Decline Max Regret Stay in Ca 40 40 10 Move to V 200 15 200 12 13 0.15 0.4 0.45 Slight Decline 20% Decline 40% Decline Expected Payoff 14 Stay in Ca 15 1000 900 840 1200 16 Move to V 915 800 906 17 18 19 20 21

EXCEL FORMULAS:

1 DoorCo Costs (in $000) Slight Dedline 20% Decline 40% Dedine Worst Opportunity Loss Std Dev Stay in Carmel 1000 900 -MIN(C4:E4) =MAX(C4:E4) :G4-F4 STDEV(CA:E4) STDEV(C5:E5) Move to Wisconsin 1200 MIN(CS:E5) 915 800 MAX(C5:E5) G5-F5 Opportunity loss Slight Dedline -CA-MIN(CSA:C$5)D4-MIN(DSA:DS5) I-С5-MIN(CS4:CS5) 20% Decline 40% Dedine Max Regret E4-MIN(ESA:ES5)MAX(C9:E9) in Carmel Move to wisconsin -D5-MIN(DS4:DS5) 10 -E5-MIN(ES4:ES5) -MAX(C10:E10 12 13 0.15 0.4 0.45 40% Decline Slight Dedline 20% Decline Expected Payoff SUMPRODUCTIC1S:15,SC$13:SES13) SUMPRODUCT(C16:E16,SC$13:SE$13) 15 Stay in Carmel 1000 900 Move to Wisconsin 1200 16 915 800 17 18 19 20 21

1. Aggressive strategy: Move to Wisconsin with payoff $ 800

2.. Conservative strategy: Stay in Carmel with payoff $ 1,000

3. Opportunity-loss strategy strategy: Stay in Carmel with maximum regret $ 40

4. Move to Wisconsin

It is more risky, because its Std Dev is higher.

5. The best expected value decision is: Stay in Carmel

with payoff $ 888