Work in excel please.
3-2 JENSEN'S INEQUALITY
Consider a simple situation where uncertain cash flows arise in two periods. Create a simulation model where revenues in Year 1 are mod-eled as Uniform (10,20) and costs in Year 1 are modeled as Uniform (2,12). Define net cash flow as the difference between revenues and costs. Further assume that the growth rate in revenues from Year 1 to Year 2 is normally distributed with a mean of 2% and a standard deviation of 1% and the growth rate in costs from Year 1 to Year 2 is normally distributed with a mean of 0% and a standard deviation of 1%. Assume that the discount rate is 10%.
a. Use the expected values of each variable to compute the present value of the net cash flows. (For instance, the expected value of revenues in Year 1 is 15, and the expected growth rate in revenues is 2 %.)
b. Now simulate the distribution of NPV for 10 trials. Repeat this procedure allowing for 100,1000,10,000, and 100,000 trials. Is the expected NPV from the distribution converging to a value?
c. Now compare the results in part a and part b. Are these significantly different? (Compute a percentage difference to get an estimate of this difference.) This difference is attributable to Jensen's inequality, which states that the expected value of a function (in this case, expected NPV from part b) will differ from the value of the function calculated at the expected values of the uncertain variables (the answer in part a) if the function is nonlinear in the underlying uncertain variables.
3-4 PROJECT RISK ANALYSIS-SENSITIVITY ANALYSIS Refer back to the HMG example found in Problem 2-5 (below) and answer the following questions:
3. What are the key sources of risk that you see in this project?
b. Use the "Goal Seek" function within Excel to find the breakeven values (i.e., values that force the project NPV to equal zero) for each of the following variables: sales volume, unit price, unit variable cost (both the starting unit variable cost and the rate of decline reflect the effects of learning), and fixed operating costs.
c. Which of the variables analyzed in part b do you think is the greatest source of concern? What, if anything, could you do to reduce the risk of the project?
d. Should you always seek to reduce project risk?
Exhibit P2-5.1 HMG Project Analysis
Plant life 5
Salvage value 400,000
Variable cost % 45%
Fixed operating cost 1,000,000
Tax rate 38%
Working capital 10% Change in revenues
Required rate of return 15%
0 1 2 3 4 5
Sales volume 1,000,000 1,500,000 3,000,000 3,500,000 2,000,000
Unit price 2.00 2.00 2.50 2.50 2.50
Revenues 2,000,000 3,000,000 7,500,000 8,750,000 5,000,000
Variable operating costs (900,000) (1,350,000) (3,375,000) (3,937,500) (2,250,000)
Fixed operating costs (1,000,000) (1,000,000) (1,000,000) (1,000,000) (1,000,000)
Depreciation expense (800,000) (800,000) (800,000) (800,000) (800,000)
N .. (700,000) (150,000) 2,325,000 3,012,500 950,000
Net operating income
Less: Taxes 266,000 57,000 (883,500) (1,144,750) (361,000)
NOPAT (434,000) (93,000) 1,441,500 1,867,750 589,000
800,000 800,000 800,000 800,000 800,000
Less: Working capital (200,000) (100,000) (450,000) (125,000) 375,000 500,000
Free cash flow (4,200,000) 266,000 257,000 2,116,500 3,042,750 2,137,000
Net present value 419,435
Internal rate of return 18.01 %
Please see the attached excel sheet for detailed answers and explanations. I consider the final savage ...
This solution discusses various concepts related to Jensen's inequality and HMG project risk analysis.