Dahlia Simmons

Dahlia Simmons, CFO of Dahlia 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\%45\%?$

Industry demand: $80,000,00095,000,000108,000,000124,000,000$

Probability: $10\%20\%20\%25\%20\%?$

Dahlia market share: $1\%2\%3\%4\%5\%6\%$

Probability: $20\%70\%?$

Initial cost: $$60,000,000$ $$65,000,000$ $$72,000,000$

Probability: $20\%65\%?$

VC per unit $$24$ $$26$ $$29$

Probability: $15\%25\%40\%?$

Fixed costs: $$20,000,000$ $$24,000,000$ $$27,000,000$ $$31,000,000$

The unit price depends on the industry demand since a greater demand will result in a higher price. Dahlia determines that the price per unit will be given by the following equation:

Price $=$ Industry demand $/2,000,000+$ $$1(+/-$$$2)$

The random $"+/-$$$2"$ term represents a increase or decrease in price according to the following distribution:

Probability: $40\%?$

Price randomness: $($$$2)$ $$2$

The length of the project, tax rate, and required return are:

Project length $($years$)$: $6$

Depreciation Straight Line Method with Zero scrap value

Tax rate: $34\%$

Required return: $14\%$

QUESTIONS:

$1"(25$ Points$)$

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"(20$ Points$)$

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(5$ Points$)$ Using Table $3$ results $($automatically displayed on next sheet$),$ create a graph of the distribution of the IRRs from the Monte Carlo simulation for different ranges of IRR. Please comment.

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

$5(2\mathrm{.}5$ Points$)$ What is the probability of positive NPV$?$

$6(2\mathrm{.}5$ Points$)$ Based on the above results, do you accept or reject the project and why?