Probabilistic Analysis Handout - University of Idaho



Probabilistic Analysis HandoutImportant excel functions=randbetween(bottom, top) might require analysis tool pack add-in=rand()=norminv(uniform probability, mean, standard deviation) =if(logical test, value if true, value if false)=vlookup(lookup value, table, column number)=PV(), =PMT(), and =FV()=RATE(nper, pv, pmt, fv)=countif(range, “criteria”)=count(range)=AVERAGE(range)=STEDV.S(range)Hit F9 to execute the rand() and randbetween() functions.Table 1: Using excel to get a random “draw” from a distributionCommon distributionsExcel functions to get random valueExamplesExcel codeUniform=randbetween(min, max)uniform between 2,000 and 3,000=randbetween(2000, 3000)Uniform specific values=randbetween(min, max)*factor$400 at 25%$500 at 25%$600 at 25%$700 at 25%=randbetween(4, 7)*100Normal=norminv(rand(), mean, std)normal dist. with mean = $500 and std = $200=norminv(rand(), 500, 200)Discrete (2possibilities)=if(rand()<=lower bound, lower outcome, higher outcome)15 yrs 30%16 yrs 70%=if(rand()<=.3, 15, 16)Discrete (more than 2 possibilities)=vlookup(rand(),lookup table, column)$120 at 10%$160 at 30%$180 at 60%lower bound sorted low to highDesired column is 2=VLOOKUP(RAND(), A2:B4, 2)For Alternatives A, B, and C, generate 200 simulation runs for present worth. Calculate the (1) mean, (2) standard deviation, (3) minimum, (4) maximum, and (5) and probability of loss. n = 8 years. MARR = 10%.Alternative A[This demonstrates how to model a uniform distribution and 2 possibility discrete distribution.] Initial investment is uniformly distributed between $300,000 and $150,000Annual revenues are $120,000.Annual expenses are $75,000.Salvage value has a 75% chance of being $40,000 and 25% chance of being $80,000.Alternative B[This demonstrates how to model a normal distribution.] Initial investment = $150,000Salvage value = $60,000.Annual profits is normally distributed with a mean of $37,000 and standard deviation of $10,000 (For this class, text book, homework, and exam, we will model annual probabilities as one random value for the entire study period. A more sophisticated approach would be to model the probability with a random value for each year.)Alternative C [This demonstrates how to model a uniform distribution with specific values and a multiple possibility discrete distribution that requires a VLOOKUP() in excel.] Initial investment = $280,000Annual profits have an equal chance of being $40000, $50000, $60000, $70000, or $80000.Salvage value could be: $25,000 (60% chance), $50,000 (20% chance), or $75,000 (20% chance).Comparing Alternatives Create a decision table to compare the alternatives. Include (1) mean, (2) standard deviation, (3) minimum, (4) maximum, and (5) and probability of loss. Which has the most reward? Which has the most risk?Using Running Average to examine probabilistic stabilityRepeat the simulation for Alternative A and plot the running average of PW to see how many simulations are needed for stabilization. Calculating IRR [This demonstrates how to use RATE() in excel.] Repeat the simulation for Alternative A, but this time calculate IRR. If your MARR is 12%, what is the probability this project will not beat the MARR? Monte Carlo Simulation Add in [This demonstrates how to use MCSim addin in excel.] Repeat the simulation for Alternative B by using the MCSim Add-in to analyze 10,000 trials for PW. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download