Question

Ford is going to produce a new vehicle, the Bronco, and wants to determine the amount of annual capacity it should build. Ford's goal is to maximize the profit from this vehicle over the next 5 years. The average vehicle will sell for $39,000 and incur a variable production cost of $26,000. Building one unit of annual capacity ill cost $2,000. Each unit of capacity will also cost $1,000 per year to maintain, even if capacity is unused. Demand for the Bronco is unknown, but marketing estimates the distribution of annual demand to be 40,000 units with 25% probability. 90,000 units with 50% probability, and 130,000 units with 25% probability. Assume that the number of units sold during a year is the minimum of capacity and annual demand. What capacity level should Ford choose? tWih a peodato paciy 10 au te) h podeaaci d 900 us yVu u 3) (5 points) With a production capacity of 130,000 annual units, what is the Expected Monetary Value? (Enter the full numeric value) 4) (5 points) Ford is unsure about the probability of demand for the Bronco. Run a sensitivity analysis on the probability of demand being 90,000, varying the probability from 30% to 70% by increments of 10%. The probability of demand being 40,000 and 130,000 units should make up the remaining probability of demand and are expected to be equal. Identify the maximum EMV of the decision tree model for each probability of demand being 90,000 Probability Demand (90,000) Decision 30% 40% 50% 70%

Answer 1

1)

With a production capacity of 40,000 annual units,

Total cost of building and maintaining capacity over 5 years = 40000*(2000+5*1000) = $ 280,000,000

Expected Annual Sales = .25*MIN(40000,40000)+.5*MIN(40000,90000)+.25*MIN(40000,130000) = 40,000 units

Expected gross profit over 5 years = 5*40000*(39000-26000) = $ 2,600,000,000

Expected net profit over years = 2600000000 - 280000000 = $ 2,320,000,000

2)

With a production capacity of 90,000 annual units,

Total cost of building and maintaining capacity over 5 years = 90000*(2000+5*1000) = $ 630,000,000

Expected Annual Sales = .25*MIN(90000,40000)+.5*MIN(90000,90000)+.25*MIN(90000,130000) = 77,500 units

Expected gross profit over 5 years = 5*77500*(39000-26000) = $ 5,037,500,000

Expected net profit over years = 5037500000 - 630000000 = $ 4,407,500,000

3)

With a production capacity of 130,000 annual units,

Total cost of building and maintaining capacity over 5 years = 130000*(2000+5*1000) = $ 910,000,000

Expected Annual Sales = .25*MIN(130000,40000)+.5*MIN(130000,90000)+.25*MIN(130000,130000) = 87,500 units

Expected gross profit over 5 years = 5*87500*(39000-26000) = $ 5,687,500,000

Expected net profit over years = 5037500000 - 630000000 = $ 4,777,500,000

4)

Sensitivity analysis is following:

fx INDEX(SD$3:$F$3,1,MATCH(MAX(D4:F4),D4:F4,0)) Probability Demand EMV of Production capacit Decision 3 90,000 40,000 130,000 40,000 90,000 130,000 35% 41 30% 35% | 2,320,000,000|4,082,500,000|4,712,500,000 130,000 51 40% 30% 30% | 2,320,000,000 | 4,245,000,000 | 4,745,000,000 | 130,000 6 5096 25% 25% | 2,320,000,000 | 4,407,500,000 | 4,777,500,000 | 130,000 7 | 60% 20% 20% 2,320,000,000 | 4,570,000,000 | 4,810,000,000 | 130,000 15% 81 7096 15% | 2,320,000,000 | 4,732,500,000 | 4,842,500,000 | 130,000 12 13

FORMULAS:

D4 =(MIN(D$3,$A$3)*$A4+MIN(D$3,$B$3)*$B4+MIN(D$3,$C$3)*$C4)*(39000-26000)*5-D$3*(2000+5*1000) copy to D4:F8

G4 =INDEX($D$3:$F$3,1,MATCH(MAX(D4:F4),D4:F4,0)) copy to G4:G8