Question

Unilate’s required rate of return is 10%.

Assume that you are confident about the estimates of all the variables that affect the project’s cash flows except unit sales. If product acceptance is poor, sales would be only 75,000 units a year, whereas a strong consumer response would produce sales of 125,000 units. In either case, cash costs would still amount to 60% of revenues. You believe that there is a 25% chance of poor acceptance, a 25% chance of excellent acceptance, and a 50% chance of average acceptance (the base case).

(1) What is the worst-case NPV? The best-case NPV?

(2) Use the worst-case, most likely (or base) case, and best-case NPVs and probabilities of occurrence to find the project’s expected NPV, standard deviation (σ_NPV), and coefficient of variation (CV_NPV).

(3) Discuss how one would perform a sensitivity analysis on the unit sales, salvage value, and required rate of return for the project. Assume that each of these variables deviates from its base-case, or expected, value by plus and minus 10, 20, and 30%.

(4) How would you calculate the NPV, IRR, and the payback for each case?

Year			0	1	2	3	4
Investment in:
Equipment cost			-240
Newt Working capital			20
Unit sales (Thousand)				100	100	100	100
Price / Unit Dollars				$2.10	$2.205	$2.315	$2.431
Total revenue				$210.0	$220.5	$231.5	$243.1
Depreciation				-79.2	-108	-6	-16.8
Cash operating costs (60%)				-126	-132.3	-138.9	145.9
Earning before taxes (EBT)				4.8	-19.8	56.5	80.4
Taxes (40%)				-1.9	7.9	-22.6	-32.2
Net income				2.9	-11.9	34	48.2
Plus Depreciation				79.2	108	36	16.8
Net operating CF				82.1	96.1	70	65
Salvage value							25
Tax on salvage value (40%)							-10
Recovery of NWC							20
Net cash flow			260	82.1	96.1	70	100
Cumulative CD for paycheck			-260	-177.9	-81.8	-11.8	88.2
NVP	$15
IRR	12.60%

Answer 1

1) We used a spreadsheet model to develop the scenarios (in thousands of dollars), which are summarized below:

                          Case           Probability      NPV (000s)

                          Worst                0.25              ($27.8)

                           Base                 0.50                 15.0

                            Best                 0.25                 57.8

2)

The expected NPV is $14,968 (rounded to the nearest thousand below).

E(NPV) = 0.25(-$27.8) + 0.50($15.0) + 0.25($57.8) = $15.

The standard deviation of NPV is $30.3:

s_NPV = [0.25(-$27.8 – $15)² + 0.50($15 – $15)²+ 0.25($57.8 – $15)²]^½

        = [916]^½ = $30.3,

and the project's coefficient of variation is 2.0:

CV_NPV = Stdev NPV/E(NPV) = 30.3/15 = 2

3) The base case value for unit sales was 100; therefore, if you were to assume that this value deviated by plus and minus 10%, 20%, and 30%, the unit sales values to be used in the sensitivity analysis would be 70, 80, 90, 110, 120, and 130 units. You would then go back to the table at the beginning of the problem, insert the appropriate sales unit number, say 70 units, and rework the table for the change in sales units arriving at different net cash flow values for the project. Once you had the net cash flow values, you would calculate the NPV, IRR, MIRR, and payback as you did previously. (Note that sensitivity analysis involves making a change to only one variable to see how it impacts other variables.) Then, you would go back and repeat the same steps for 80 units—this would be done for each of the unit sales values. Then, you would repeat the same procedure for the sensitivity analysis on salvage value and on cost of capital. (Note that for the cost of capital analysis, the net cash flows would remain the same, but the cost of capital used in the NPV and MIRR calculations would be different.)

Excel^® is ideally suited for sensitivity analysis. In fact we created a spreadsheet to obtain this project’s net cash flows and its NPV, IRR, MIRR, and payback. Once a model has been created, it is very easy to change the values of variables and obtain the new results. The results of the sensitivity analysis on the project's NPV (for the 5% inflation case, using Table IC 12-2) assuming the plus and minus 10%, 20%, and 30% deviations are shown below.

We generated these data with a spreadsheet model.

1.    The sensitivity lines intersect at 0% change and the base case NPV, at approximately $15,000. Since all other variables are set at their base case, or expected, values, the zero change situation is the base case.

2.    The plots for unit sales and salvage value are upward sloping, indicating that higher variable values lead to higher NPVs. Conversely, the plot for WACC is downward sloping, because a higher WACC leads to a lower NPV.

3.    The plot of unit sales is much steeper than that for salvage value. This indicates that NPV is more sensitive to changes in unit sales than to changes in salvage value.

4.    Steeper sensitivity lines indicate greater risk. Thus, in comparing two projects, the one with the steeper lines is considered to be riskier.

The sensitivity data are given here in tabular form (in thousands of dollars):

Change from              Resulting NPV after the Indicated Change in:

    Base Level                      Unit Sales     Salvage Value      WACC      

        -30%                          ($36.4)               $11.9             $34.1

        -20                                (19.3)                 12.9               27.5

        -10                                  (2.1)                 13.9               21.1

           0                                 15.0                  15.0               15.0

       +10                                 32.1                  16.0                 9.0

       +20                                 49.2                  17.0                 3.3

       +30                                 66.3                  18.0               (2.2)

4) The NPV, IRR and Payback can be calculated using the values generated and the excel function for NPV, IRR and payback.