Excel Master It! Problem Excelcoverageonlinevariablesintheproject:DahliaSimmons,CFOofUlrichEnterprises,isanalyzinganewprojecttosellsolar-poweredbatteriesforcellphones.Dahliahasestimatedthefollowingprobabilitydistributionsforthe The unit price depends on the industry demand because a greater demand will result in a higher price. Dahlia determines that the price per unit will be given by this equation: Price=Industrydemand/2,000,000+/$2 The random "+ $2 " term represents an increase or decrease in price according to the following distribution: The length of the project, tax rate, and required return are: a. Create a Monte Carlo simulation for the project with at least 500 runs. Calculate the IRR for each run. Note that the IRR function in Excel will return an error if the IRR of the project is too low. For example, what is the IRR if both the initial cash flow and the operating cash flow are negative? The IRR is less than 100 percent. This is not a problem when you are calculating the IRR one time because you can see the IRR is too low, but when you are running 500 or more iterations, it can create a problem trying to summarize the results. Because of this issue, you should create an IF statement that tests if the operating cash flow divided by the absolute value of the initial investment is less than .l. If this is the case, the cell will return an IRR of 99.99 percent, or else the cell will calculate the IRR. b. Create a graph of the distribution of the IRRs from the Monte Carlo simulation for different ranges of IRR. c. Create a graph for the cumulative probability function for the IRR distribution. Excel Master It! Problem Excelcoverageonlinevariablesintheproject:DahliaSimmons,CFOofUlrichEnterprises,isanalyzinganewprojecttosellsolar-poweredbatteriesforcellphones.Dahliahasestimatedthefollowingprobabilitydistributionsforthe The unit price depends on the industry demand because a greater demand will result in a higher price. Dahlia determines that the price per unit will be given by this equation: Price=Industrydemand/2,000,000+/$2 The random "+ $2 " term represents an increase or decrease in price according to the following distribution: The length of the project, tax rate, and required return are: a. Create a Monte Carlo simulation for the project with at least 500 runs. Calculate the IRR for each run. Note that the IRR function in Excel will return an error if the IRR of the project is too low. For example, what is the IRR if both the initial cash flow and the operating cash flow are negative? The IRR is less than 100 percent. This is not a problem when you are calculating the IRR one time because you can see the IRR is too low, but when you are running 500 or more iterations, it can create a problem trying to summarize the results. Because of this issue, you should create an IF statement that tests if the operating cash flow divided by the absolute value of the initial investment is less than .l. If this is the case, the cell will return an IRR of 99.99 percent, or else the cell will calculate the IRR. b. Create a graph of the distribution of the IRRs from the Monte Carlo simulation for different ranges of IRR. c. Create a graph for the cumulative probability function for the IRR distribution
