In: Accounting
Decision Making under uncertainty
Hello, I would like to know the steps by steps on how to solve this on excel decision tree, please do not just answer the final result. Thank you
Carlisle Tire and Rubber, Inc., is considering expanding production to meet potential increases
in the demand for one of its tire products. Carlisle’s alternatives are to construct a new plant, expand the existing plant, or do nothing in the short run. The market for this particular tire product may expand, remain stable, or contract. Carlisle’s marketing department estimates the probabilities of these market outcomes to be 0.25, 0.35, and 0.40, respectively. The file P06_31.xlsx contains Carlisle’s payoffs and costs for the various combinations of decisions and outcomes.
a. Use Precision Tree to identify the strategy that maximizes this tire manufacturer’s expected profit.
b. Perform a sensitivity analysis on the optimal decision, letting each of the monetary inputs vary
one at a time plus or minus 10% from its base value and summarize your findings. Which of the
inputs appear to have the largest effect on the best solution
Tire plant decisions | |||
Decision\outcome | Expand | Remain stable | Contract |
Construct a new plant | $400,000 | -$100,000 | -$200,000 |
Expand existing plant | $250,000 | -$50,000 | -$75,000 |
Do nothing | $50,000 | $0 | -$30,000 |
Expected profit in case decision is
Construct new plant=probability in case market expands*Profit in case market expands+probability in case market remains stable*profit in case market remains stable+probability in case market contracts*Profit in case market contracts = (0.25*400,000)+(0.35*-100,000)+(0.4*-200,000) = -$15,000 |
Expected profit in case decision is-
Expand existing plant=probability in case market expands*Profit in case market expands+probability in case market remains stable*profit in case market remains stable+probability in case market contracts*Profit in case market contracts = (0.25*250,000)+(0.35*-50,000)+(0.4*-75,000) = $15,000
Expected profit in case decision is -
Do nothing=probability in case market expands*Profit in case market expands+probability in case market remains stable*profit in case market remains stable+probability in case market contracts*Profit in case market contracts = (0.25*50,000)+(0.35*0)+(0.4*-35,000) = $500
Maximum expected profit = max (-15,000 , 15,000, 500) = $15,000
Maximum expected profit is observed in case of " Expand existing plant".
Thus the company should expand its existing plant.
The table is shown below:
Market outcome |
||||||
Expands |
Stable |
Contracts |
Expected profit |
|||
Probability of market outcome |
0.25 |
0.35 |
0.4 |
|||
Decision |
Construct new plant |
$400,000 |
-$100,000 |
-$200,000 |
-$15,000.00 |
|
Expand existing plant |
$250,000 |
-$50,000 |
-$75,000 |
$15,000.00 |
||
Do nothing |
$50,000 |
$0 |
-$30,000 |
$500.0 |
b.
If $400,000 is increased by 10% then:
Market outcome |
||||||
Expands |
Stable |
Contracts |
Expected profit |
|||
Probability of market outcome |
0.25 |
0.35 |
0.4 |
|||
Decision |
Construct new plant |
$440,000 |
-$100,000 |
-$200,000 |
-$5,000.00 |
|
Expand existing plant |
$250,000 |
-$50,000 |
-$75,000 |
$15,000.00 |
||
Do nothing |
$50,000 |
$0 |
-$30,000 |
$500.0 |
This has no impact on the best solution and it remains at $15,000.
Similarly if -$100,000 or -$200,000 is increased or decreased by 10% there will be no impact on the best solution.
Any value in row corresponding to "expand existing plant" while have a direct bearing on the best output. If 250,000 is expanded by 10% the best solution will increase:
Market outcome |
||||||
Expands |
Stable |
Contracts |
Expected profit |
|||
Probability of market outcome |
0.25 |
0.35 |
0.4 |
|||
Decision |
Construct new plant |
$400,000 |
-$100,000 |
-$200,000 |
-$15,000.00 |
|
Expand existing plant |
$275,000 |
-$50,000 |
-$75,000 |
$21,250.00 |
||
Do nothing |
$50,000 |
$0 |
-$30,000 |
$500.00 |
The % increase is (21250-15000)/15000 = 41.67%
This is the maximum impact on the best solution. Thus best solution changes the most when the input of $250,000 is changed by 10%.