Chapter 12 Homework

Pg. 405-406: #2

The solutions are posted below. I simply need the excel document tailored separately to present as my own.

Probability 10% 20% 40% 20% 10% Expected NPV Variance Standard Deviation Coefficient of Variation Prob(NPV 455,772.01 60% 20% To create the histogram, we need to set up the The first, in C37:D38 is the left bar. The others blank row. This allows us to add the data as on C37:D44)). Create a Scatter chart from that dat 15 or so, and then add data labels (x value) ab that, click the series to select it, and then click the bars. Right click and choose Add Data Labe label and format it. Do the same for the other Scenario Analysis NPV Histogram 5 60% 50% 40% Be sure to set the Cap type to Flat (with Squar not line up correctly with the probabilties). Probabilities 30% 2 20% 8 Format the rest of the chart to fit your needs. 10% 0% -600,000 -400,000 -200,000 0 200,000 NPV Chart Data Data for XY to get bars (set to 15 wide) $ $ (198,083.40) (198,083.40) 0 20% 400,000 600,000 $ $ 91,272.55 91,272.55 0 60% $ $ 455,772.01 455,772.01 0 20% Cumulative Distribution Function 100% Plot at right --> togram, we need to set up the data with three series. D38 is the left bar. The others are separated by a allows us to add the data as one series (select te a Scatter chart from that data. Set the line width to en add data labels (x value) above the points. To do ries to select it, and then click the top point of one of lick and choose Add Data Label. Then click the data t it. Do the same for the other two. e Cap type to Flat (with Square or Round the tops will ectly with the probabilties). of the chart to fit your needs. 80% 60% Probability 40% 20% 0% -4.0 -3.0 -2.0 -1.0 0.0 1.0 2.0 3.0 4.0 Standard Deviations Away from Mean The chart above can be created with two data series. The first is the cumulative distribution function from -5 to 5 (you can use other ranges - and I have reset the visible range on the x-axis). The second series is similar, but its range goes from -5 to the number of standard deviations away from the mean (the z-score). The second series is chart just like the first, except that it only covers the appropriate range, and has error bars (this is what creates the "shading"). To add error bars, select the series and then go to the Design tab and choose Add Chart Element -> Error Bars -> More Error Bar Options. Delete the horizontal error bars. Set the Direction to Minus (we only want the bottom ones), and choose Custom for the Error Amount. For the negative error values choose the probabilities corresponding to the number of standard deviations under "Shaded Area" on the worksheet. Now, select the error bars and set their width and color. You want the width to be pretty wide so that they appear to be a solidly filled area (I used 5.25 points in this case). Be sure to set the series itself to have no line so that it doesn't show up. I also have a third series in the chart that draws a vertical line at the number of standard deviations. It isn't needed, so it is turned off. Also, note that the error bars show a small amount above the CDF. This can't be completely fixed, but I have made the CDF line a bit thick to hide some of it. thick to hide some of it. tion 1.0 2.0 3.0 4.0 m Mean t is the cumulative and I have reset the visible goes from (the z-score). y covers the appropriate To add error bars, select the ement -> Error Bars -> More rection to Minus (we only ount. For the negative error of standard deviations under and set their width and color. be a solidly filled area (I used e no line so that it doesn't at the number of standard he error bars show a small ave made the CDF line a bit Cumulative Distribution Function Data X Y -5.0 0.0000 -4.5 0.0000 -4.0 0.0000 -3.5 0.0002 -3.0 0.0013 -2.5 0.0062 -2.0 0.0228 -1.5 0.0668 -1.0 0.1587 -0.5 0.3085 0.0 0.5000 0.5 0.6915 1.0 0.8413 1.5 0.9332 2.0 0.9772 2.5 0.9938 3.0 0.9987 3.5 0.9998 4.0 1.0000 4.5 1.0000 5.0 1.0000 #NAME? 0 #NAME? #NAME? Shaded Area #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? V Histogram Chart Title Simulation Report by ExcelSim Trial Results Trial Net Present Value 1 ($69,885.48) 2 $17,504.43 3 $101,745.36 498 $58,417.18 499 $31,484.35 500 $31,393.53 Min ($112,714.17) Max $383,077.74 Mean $87,407.76 Median $81,766.56 Mode #VALUE! Avg Dev 65,955.84 Std Dev 80,766.19 Coef. Var. 0.92 Skewness 0.26 Kurtosis (0.20) Prob(NPV