87 b. Now conduct a sensitivity analysis to determine the sensitivity of NPV to changes in the sales price, variable costs per unit, and number of units 88 sold. Set these variables' values at 10% and 25% above and below their base case values. Include a graph in your analysis. 89 90 Evaluating Risk: Sensitivity Analysis 91 92 I. Sensitivity of NPV to Changes in Inputs. Here we use an Excel "Data Table" to find NPV for different unit sales, variable costs, WACC, and sales 93 prices, holding other thing constant. 94 95 % Deviation 1st YEAR UNIT SALES 96 from Units NPV % Deviation from WACC NPV 97 Base Case Sold $0 98 -25% 0 $0 Base Case -25% WACC 0 0.00% $0 99 -10% 0 $0 -10% 0.00% $0 100 0% $0 0% $0 101 10% 0 $0 10% 0.00% $0 102 25% $0 25% 0.00% $0 103 104 % Deviation VARIABLE COSTS % Deviation SALES PRICE 105 from Variable NPV from Sales NPV 106 Base Case Costs $0 Base Case Price $0 107 -25% $0.00 $0 -25% $0.00 $0 108 -10% $0.00 $0 -10% $0.00 $0 109 0% $0 0% $0 110 10% $0.00 $0 10% $0.00 $0 111 25% $0.00 $0 25% $0.00 $0 112 113 % Deviation NON-VARIABLE COSTS 114 from Fixed NPV 115 Base Case Costs $0 116 -25% $0 $0 117 -10% $0 $0 118 0% $0 119 10% $0 $0 120 25% $0 $0 121 Note about data tables. The data in the column input should NOT be input using a cell reference to the column input cell. For example the base case number of units sold in cell B100 should be the number 1000; you should NOT have the formula =D29 in that cell. This is because you'll use D29 as the column input cell in the data table and if Excel tries to iteratively replace cell D29 with the formula =D29 rather than a series of numbers, Excel will calculate the wrong answer. Unfortunately, Excel won't tell you that there is a problem, so you'll just get the wrong values for the data table! 122 123 Sensitivity Analysis 124 125 126 127 $11,000 128 $9,000 129 130 $7,000 131 $5,000 132 133 NPV $3,000 --Sales price ---VC Units 134 $1,000 135 -$1,000 136 -$3,000 137 138 -$5,000 139 -$7,000 140 -20% -10% 0% 10% 20% 141 142 143 144 Deviation 145 from Sales Price 146 Base Case NPV at Different Deviations from Base Variable Cost Non-variable Units Sold Cost WACC 147 -25% $0 $0 $0 $0 $0 148 -10% $0 $0 $0 $0 $0 149 0% $0 $0 $0 $0 $0 150 10% $0 $0 $0 $0 $0 151 25% $0 $0 $0 $0 $0 152 153 Range $ $ 0 $ $ 154 Non-var. cost WACC