Assignment 4 - Answers

Assignment 4 Question 1: #21, Page 1040 NOTES, PART (A): First, calculate the number of ‘bad’ cartons for each firm and the resulting loss of Market Share using the following formulas B C Actual Bad Cartons 15 Firm A 10 =ROUND(RiskBinomial(C4,C9),0) 16 Firm B 12 =ROUND(RiskBinomial(C5,C10),0) 17 Firm C 17 =ROUND(RiskBinomial(C6,C11),0) Market Share Loss 20 Firm A 90 =RiskOutput("Market Loss: A")+C4-ROUND(C15,0) 21 Firm B 88 =RiskOutput("Market Loss: B")+C5-ROUND(C16,0) 22 Firm C 83 =RiskOutput("Market Loss: C")+C6-ROUND(C17,0)

Next, calculate the number of families moving from one firm to another and the resulting new Market Share. Note that C27, C29 and C31 were not calculated using the RiskBinomial function, as the maximum number of Families Switching can be no greater than the number lost ( [A to B] + [A to C] = [Market Share Loss for A]).

26 27 28 29 30 31

B C Families Switching Firms A to B 4 A to C 6 B to A 7 B to C 5 C to A 10 C to B 7

=ROUND(RiskBinomial(C15,C21/(C21+C22)),0) =C15-C26 =ROUND(RiskBinomial(C16,C20/(C20+C22)),0) =C16-C28 =ROUND(RiskBinomial(C17,C20/(C20+C21)),0) =C17-C30

New Market Share 34 Firm A 107 =RiskOutput("New Share: A")+C20+C28+C30 35 Firm B 99 =RiskOutput("New Share: B")+C21+C26+C31 36 Firm C 94 =RiskOutput("New Share: C")+C22+C27+C29

Next, create a table to run these weekly Market Share changes for a year (52 rows total, with Market Share from previous week used as inputs for Market Share of current week).

Week Loss A Loss B Loss C Adj A Adj B Adj C A->B A->C B->A B->C C->A C->B New A New B New C 0 0 0 100 100 100 0 0 0 0 0 0 100 100 100 1 11 16 25 87 88 76 6 3 9 6 13 11 106 109 85 2 10 11 17 93 98 71 4 3 4 2 9 7 109 115 76 3 9 10 10 102 95 61 6 5 7 8 3 7 122 104 74

50 51 52

13 13 22

18 11 19

12 147 9 143 12 138

79 85 83

36 38 39

8 12 16

9 5 6

15 4 10 3 13 -

7 6 7

1 4 3

158 155 158

93 99 97

49 46 45

Finally, run the @Risk simulation using 1000 iterations to create the following results table. Output Name Market Share: A Market Share: B Market Share: C

Minimum

Maximum

122 64 3

Mean

198 135 68

Std Dev

167 99 34

10 12 12

ANSWER, Part (A): The Mean market share for each firm after one year, based on my simulation, is as follows: Firm A: 167, Firm B: 99, Firm C: 34 NOTES, Part (B): Using the same data as in Part (A), add two columns to the 52-week table: Cumulative Revenue starts with -$1 Million to represent the initial investment, and Incremental Revenue measures the increase (or decrease) in Market Share over the previous week and adds (or subtracts) $10,000 for each 1% change. Week New A New B New C Inc. Rev Cum. Rev 100 100 100 0 -1000000 1 105 104 91 50,000.00 (950,000) 2 108 107 85 28,571.43 (921,429) 3 120 98 82 111,111.11 (810,317)

50 51 52

229 222 216

68 75 81

3 3 3

17,777.78 (30,567.69) (27,027.03)

(140,503) (171,070) (198,097)

Also, change the @Risk function for the Weekly Loss of Market Share of A from =ROUND(RiskBinomial(R2,$C$9),0) TO

=ROUND(RiskBinomial(R2,$C$9)/2,0)

in order to cut the percentage of unsatisfactory juice cartons in half for company A. Finally, run the @Risk simulation using 1000 iterations to create the following results table. Output Name Market Share: A Market Share: B Market Share: C Cumulative Revenue

Minimum

Maximum

198 35 (278,302)

Mean

241 93 44 (76,712)

Std Dev

222 66 12 (165,608)

7 9 8 30,308

ANSWER, PART(B): The mean Market Share for company A does significantly increase with the investment of $1MM to reduce the number of unacceptable juice cartons. This simulation shows an increase of 55 families from 167 (in part A) to 222 (in part B). However, the increased Market Share does not provide sufficient Cumulative Revenue to offset the $1MM investment, making it not worthwhile. Question 2: #23, Page 1040 NOTES Begin by building a table that uses @Risk functions to calculate 10 years of market growth in this industry. Use the following formulas Begin Mkt Shr: =RiskTriang(B9,B10,B11) Pig Growth: =RiskNormal($B$5,$B$8) MKTSHR: =IF(E24=0,$D$22,$D$22*(1-E24*$B$17)) Competitors: =IF(E24<3,MIN(E24+RiskBinomial(3,0.4),3),3) Actual Profit: =(C24*D24)*($B$18-$B$19)

Estimated Market Share Year Pig Growth Pig Population 1 2.27% 1,022,716 2 4.59% 1,069,641 3 4.81% 1,121,074 4 4.08% 1,166,859 5 6.28% 1,240,105 6 3.48% 1,283,283 7 4.47% 1,340,611 8 4.88% 1,406,070 9 4.91% 1,475,078 10 4.96% 1,548,182

Begin Mkt Shr 38.51% MKTSHR Competetors Actual Profit 38.51% $ 708,940 30.81% 1.00 $ 593,174 23.11% 2.00 $ 466,273 15.40% 3.00 $ 323,543 15.40% 3.00 $ 343,853 15.40% 3.00 $ 355,825 15.40% 3.00 $ 371,721 15.40% 3.00 $ 389,871 15.40% 3.00 $ 409,005 15.40% 3.00 $ 429,276

Next, calculate the NPV of the 10-years based on a 10% annual profit discount rate, and assign it to an @Risk output using the formula NPV: = RiskOutput("NPV: ")+NPV(B20,F24:F33) Finally, run the @Risk simulation using 1000 iterations to create the following results table. Output Name Total Revenue: NPV:

Minimum

$ $

1,902,958 1,177,725

Maximum

$ $

7,904,789 5,273,019

Mean

$ $

Std Dev

4,024,795 2,553,484

$ $

1,010,703 665,236

ANSWER, PART (A): The mean NPV for Mutron, using the simulation outlined above is $2,553,484. ANSWER, PART (B): Using @Risk Results window, we can modify the value of the left and right slider in the NPV Distribution graph such that the values are 2.5% and 97.5%. This gives the confidence interval of 95% for Mutron’s actual NPV. As shown in the graph below, Mutron can be 95% certain that the actual NPV will be between $1,423,556 and $3,925,667.

Distribution for NPV: /F35 X <=1423556 2.5%

7

X <=3925667 97.5%

Mean = 2553484 6

Values in 10^ -7

5

4

@RISK Student Version For Academic Use Only

3

2

1

0 1

2.5

4

5.5

Values in Millions

Question 3: #43, Page 1050 NOTES: Begin by building a table that uses @Risk functions to calculate 10 years of market changes in this industry. Use the following formulas Potential Market: =B43*(1+RiskNormal($B$6,$B$9)) Competitors: =IF(C43<5,MIN(C43+RiskBinomial(1,0.2),4),4) Price: =D43*(1.05) Cost: =RiskDiscrete(B13:C13,B14:C14)*(1.05) Mean Sales: =IF(A44<=$B$22,ROUND((($F$23-($F$24*C44))*G43) + (($F$25-($F$26*C44))*(B44-G43)),0),0) Unit Sales: =ROUND(RiskNormal(F44,(0.075*F44)),0) Profit: =G44*(D44-E44) Cumulative Profit: =IF(F44=0,0,I43+H44)

To determine the Number of Years total, I used =RISKDISCRETE(B3:F3,B4:F4). To determine the Development Cost, I used =RiskDiscrete(B17:D17,B18:D18). Year PotMarket 1 2 3 4 5 6 7 8 9 10

1,000,000 1,056,779 1,112,049 1,162,514 1,201,933 1,255,991 1,324,235 1,399,349 1,464,673 1,562,218

Competitors 1 2 2 2 3 4 4 4 4 4

Price Cost Mean Sales Unit Sales $ 10.00 $ 6.00 160,000 144,667 $ 10.50 $ 6.30 210,720 237,334 $ 11.03 $ 6.62 $ 11.58 $ 6.95 $ 12.16 $ 7.29 $ 12.76 $ 7.66 $ 13.40 $ 8.04 $ 14.07 $ 8.44 $ 14.77 $ 8.86 $ 15.51 $ 9.31 -

Profit Cum Profit $ 578,668 $ (9,421,332) $ 996,803 $ (8,424,529) $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ -

Next, calculate the NPV of the 10-years based on a 5% Annual Discount Rate, and assign it to an @Risk output using the formula below. Please note that there was no discount rate assigned in the question, so this 5% value was somewhat arbitrary. =RiskOutput("NPV")+NPV(0.05,I43:I52) Finally, run the @Risk simulation using 1000 iterations to create the following results table. Output Name NPV

Minimum

($42,674,000)

Maximum

$74,257,536

Mean

($5,638,617)

Std Dev

$18,723,056

ANSWER, PART (A): The mean NPV for Toys For U, using the simulation outlined above is -$5,638,617. ANSWER, PART (B): Using @Risk Results window, we can modify the value of the left and right slider in the NPV Distribution graph such that the values are 2.5% and 97.5%. This gives the confidence interval of 95% for Toys For U’s actual NPV. As shown in the graph below, Toys For U can be 95% certain that the actual NPV will be between $30.972,520 and $41,626,296, with a Standard Deviation of $18,723,056.

Distribution for NPV/I54 3.5

X <=-30972520 2.5%

X <=41626296 97.5% M ean =-5638617

Values in 10^ -8

3 2.5 2

@RISK Student Version For Academic Use Only

1.5 1 0.5 0 -60

-40

-20

0

20

40

60

80

Values in Millions

This is a very large Standard Deviation, so I ran a sensitivity analysis on the data, as shown below. It reveals a strong impact by the Development Cost as well as Unit Sales in later years. If Toys For U could find a more accurate estimate of the development costs, they may find this project has a more positive NPV, allowing them to reduce risk. Regression Sensitivity for NPV/I54 Devel Cost/B30

-0.645

Unit Sales/G49

0.613

Unit Sales/G51

0.606

Variable Cost Yr0/B26

-0.327 -0.292

YRS Doll w ill sell/B22 Unit Sales/G50

-0.253

@RISK Student Version

Unit Sales/G47

For Academic Use Only

Unit Sales/G52

-0.185

Competitors/C44

0.214

-0.055

Unit Sales/G44

0.044

Competitors/C47

0.031

Competitors/C45

-0.029 0.028

Unit Sales/G43 -1

-0.8

-0.6

-0.4

-0.2

0

0.2

Std b Coefficients

0.4

0.6

0.8

1

Question 4 NOTES PART (A) Sub(i): Using the formula provided to determine Price, I used RISKNORMAL(0,1) to identify a standard normal value with mean 0 and standard deviation 1. For simplification of the formulas, I calculated the exponents separately from the Price, using the formulas below. Exponent: =($D$4-(0.5*$D$5^2))*B19+($D$5*RiskNormal(0,1)*SQRT(B19)) Price: =RiskOutput("Price at 6 Months")+$D$13*EXP(C19) Value: =RiskOutput("Put Value")+D8-D19 Months Exponent Price 0.50 0.167634 $

82

Value $ (12)

I then ran the @Risk simulation using 1000 iterations to create the following results table. Output Name Price at 6 Months Put Value

Minimum

Maximum

$ 31.47 $ $ (86.35) $

156.35 38.53

Mean

Std Dev

$ 74.37 $ 18.67 $ (4.37) $ 18.67

ANSWER PART (A) Sub(i): The mean value of the Put after 6 months is -$4.37.

NOTES PART (A) Sub(ii): I calculated the value of Portfolio 1 by subtracting the calculated current stock price minus the original stock price, then adjusting for present value. Portfolio 1 Value: =RiskOutput("P1 Value")+(D19/(1+$D$6/2))-($D$3) Portfolio 2 Value is the value of the put plus the value of the stock on the sale date (6 months). These numbers

Using the formula provided to determine Price, I used RISKNORMAL(0,1) to identify a standard normal value with mean 0 and standard deviation 1. For simplification of the formulas, I calculated the exponents separately from the Price, using the formulas below.

Exponent: =($D$4-(0.5*$D$5^2))*B19+($D$5*RiskNormal(0,1)*SQRT(B19)) Price: =RiskOutput("Price at 6 Months")+$D$13*EXP(C19) Value: =RiskOutput("Put Value")+D8-D19 Days Exponent Price 126.00 0.084184 $

75.06

Put Value P1 Value P2 Value P1 Return P2 Return $ (4.94) $ 4.23 $ (0.71) 6.13% -1.10%

I then ran the @Risk simulation using 1000 iterations to create the following results table. Output Name Price at 6 Months Put Value P1 Value P2 Value Portfolio 1 Return Portfolio 2 Return

Minimum

$ $ $ $

Maximum

70.65 (7.89) (0.07) (0.71) -0.11% -1.16%

$ $ $ $

78.09 (0.63) 7.18 (0.71) 10.41% -1.03%

Mean

$ $ $ $

Std Dev

74.37 (4.27) 3.56 (0.71) 5.16% -1.09%

$ $ $ $

1.16 1.13 1.13 1.64% 0.02%