Dahlia Simmons, CFO of Ulrich Enterprises, is analyzing a new project to sell solar powered batteries for cell phones. Dahlia has estimated the following probability distributions for the variables in the project: PROBABILITY $10\%30\%40\%20\%$ INDUSTRY DEMAND $80$ milion $95$ milion $108$ milion $124$ million PROBABILITY ULRICH MARKET SHARE $5\%1\%20\%2\%20\%3\%25\%4\%20\%5\%10\%6\%$ PROBABIUTY INITIAL COST $20\%$ $$60$ million $70\%$ $$65$ million $10\%$ $$72$ milion PROBABILITY VARIABLE COST PER UNIT $20\%$ $$2465\%$ $$2615\%$ $$29$ PROBABILITY $15\%25\%40\%20\%$ FIXED COSTS $$20$ million $$24$ milion $$27$ milion $$31$ milion 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 the equation: Price Industry demand $/2,000,000+/-$ $$2$ The random $+/-$$$2"$ term represents an increase or decrease in price according to the following distribution: $55\%$ PROBABILITY PRICE RANDOMNESS The length of the project, tax rate, and required return are: Project length years Required return 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 flows are negative? The IRR is less than $100$ percent. This is not a problem when you are calculating the IRR one timebecause you can see the IRR is too low, but when you are running $500$ or more iterations, it can create a problem when you try to summarize the results. Because of this issue. you should create an IF statement that tests whether the operating cash flow divided by the absolute value of the initial investment is less than $10.$ If this is the case, the cell will return an IRR of $-99\mathrm{.}99$ percent; if this is not the case, the cell will calculate the IRR.

$1-$ Calculate the missing probabililty $($where $?$ appears$)$ in the above.

Fill Table $1($shown on next sheet$)$:

Run a Monte Carlo simulation for the project with at least $500$ iterations of the calculation.

How?

Step $1$: In the Simulation Analysis excel sheet, type in $=$Rand$()$ in cell B$37$ and drag it all the way to G$536$ to create six columns of $500$ random numbers each reflecting random probability for the following six variables: Industry Demand, Dahlia Market Share, Price per Unit, Initial Investment, Variable Cost per Unit, and Fixed Costs.

Step $2$: Use the generated random probability to assign a value for each of the six variables for each iteration $($e$.$g$.,$ if the probability for industry demand is $76\%,$ then the industry demand would be $108,000,000).$ For that you need to insert an extended if statement

in cell H$37,$ I$37,$ K$37,$ L$37,$ N$37,$ P$37$ and then drag the if conditions all the way to row $536.$ Create the appropriate calculation formula in J$37,$ M$37,$ O$37,$ Q$37,$ R$37,$ S$37,$ T$37$ and U$37,$ and then drag the corresponding formula all the way to row $536.$

$2-$Using the $500$ iterations from Table $1($which you already developed on next sheet when answering question a$),$ do the necessary computations in Table $2($shown on next sheet$)$ to calculate the Operating Cash Flows $($OCF$)$ for each iteration, the IRR and NPV for each iteration.

NOTE: 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 flows are negative? The IRR is less than $-100$ percent. This is not a problem when you are calculating the IRR one time since 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, 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 $0\mathrm{.}1.$ If this is the case, the cell will return an IRR of $-99\mathrm{.}99$ percent, else the cell will calculate the IRR.

$3-$Using Table $3$ results $($automatically displayed on next sheet$),$ create a graph of the distribution of the IRRs from

$4-$Using Table $3$ results $($automatically displayed on next sheet$),$ create a graph for the cumulative probability function for the IRR distribution. Please comment.

$5.$What is the probability of positive NPV$?$

$6.$Based on the above results, do you accept or reject the project and why?

Kindly provide the excel formula and any explanation that can help.Thank you $.$