4 115040 1199 2644 1444 353 214 477333 6 219998 300788 351579 410946 480338

4 115040 1199 2644 1444 353 214 477333 6 219998

This preview shows page 6 - 9 out of 12 pages.

4115040$11.9926.44%14.44%$3.532.14%477,3336-$219,998$300,788$351,579$410,946$480,338$561,447$656,2525119718$11.7326.25%7.57%$3.173.66%458,3546-$189,544$299,741$334,230$372,688$415,571$463,388$516,7076109221$11.1128.87%8.99%$3.043.38%485,7048-$231,192$286,772$323,122$364,080$410,229$462,227$520,817771856$11.2026.14%11.71%$2.893.27%505,8878-$349,862$180,006$207,674$239,593$276,419$318,906$367,922898716$11.6030.25%8.01%$2.872.17%486,0203-$225,044$288,017$317,861$350,797$387,145$427,260$471,531980465$14.7428.37%7.74%$3.033.48%428,4836-$161,139$298,063$332,312$370,497$413,069$460,533$513,45110101383$10.6925.52%5.69%$3.363.65%445,7236-$256,289$207,528$227,350$249,065$272,855$298,916$327,46744
4/Budgeting the ProjectSpreadsheet to Simulate Project’s Completion 500 Times (continued)A histogram (risk profile) summarizing the 500 replications is shown below. 3.How does the average of the simulation compare to the expected value you calculated? What are the managerial implications of this difference? The average of the simulation was $1,423,668 which is relatively close to the expected value. However, the range of values observed in the simulation experiment varied from a loss of almost $65,000 to a profit of over $4.5 million. Thus, the simulation experiment demonstrates the uncertainty of the actual outcome of this project.Using Crystal Ball:The use of Crystal Ball can greatly speed up the development and analysis of the simulation model for this case. To illustrate this, the spreadsheet shown on the following page was created. With Crystal Ball, values for the random parameters are generated by defining cells as Assumption Cellsrather than using Excel’s Random Histogram020406080100$165,251$625,251$1,085,251$1,545,251$2,005,251$2,465,251$2,925,251$3,385,251$3,845,251$4,305,251MoreBinFrequency17181920212223242526272829QRSTCash FlowCash FlowCash FlowYearYearYear10.00%8910NPV$522,373$590,082$666,567$851,709$879,837$1,053,604$1,261,691$298,689$595,390$653,833$718,013$1,244,393$767,066$896,592$1,047,990$1,208,590$576,161$642,457$716,380$1,100,677$586,834$661,218$745,032$1,574,923$424,472$489,715$564,985$888,853$520,389$574,310$633,819$272,259$572,449$638,226$711,562$1,119,011$358,745$393,010$430,548$617,60045
4/Budgeting the ProjectUsing Crystal Ball to Simulate Project CostsNumber Generation tool. In this particular case, cells B5 to I5 were defined as assumption cells. For example, cell B5 corresponds to the market size in the first year of the product’s introduction. From the case, it is estimated that the market size follows a normal distribution with a mean of 100,000 units and standard deviation of 10,000 units. To define cell B5 as an assumption cell, first the cursor was placed in this cell. Next, the Define Assumption button was selected. Then the Normal distribution was selected from the Distribution Gallerythat appeared. In the next window, 100,000 was entered in the Mean field and 10,000 was entered in the Std Devfield and the OKbutton selected. Cells C5 to H5 were specified in a similar fashion.Cell I5 was a little different in that the useful life of the equipment does not follow a continuous distribution. In this case, the Custom distribution was selected from the Distribution Gallery.Next 3 was entered in the Valuefield and .1 entered in the Prob.field and the Enter button clicked. In a similar fashion, 6 was then entered in the Valuefield, .8 in the Prob.field and the Enter button clicked. This was repeated for the probabilities of 8 year and 10 year useful lives, respectively. After all this information was entered, OK was clicked.

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture