| 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 sold. Set these variables values at 10% and 20% above and below their base case values. Include a graph in your analysis. |
| | | | | | | | | |
| Evaluating Risk: Sensitivity Analysis | | | | | | |
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 prices, holding other thing constant. The one-variable Data Table is setup in this worksheet. There are many videos in YouTube to show how one- variable inputs work. You do not have to setup the Data Table. You have to fill up the number for Base Case only. Formula is setup for the orange highlighted cells. 1st YEAR UNIT SALES WACC % Deviation from % Deviation from Units NPV NPV Base Case Sold Base Case WACC -20% -20% 0.00% -10% -10% 0.00% $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 0% 0% 10% 10% 0.00% 20% 20% 0.00% % Deviation VARIABLE COSTS % Deviation SALES PRICE from Variable NPV from Sales NPV Base Case Costs Base Case Price -20% 0.00 -20% 0.00 -10% 0.00 -10% 0.00 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 0% 0% 10% 0.00 10% 0.00 20% 0.00 20% 0.00 % Deviation NON-VARIABLE COSTS from Fixed NPV Base Case Costs $4,788,667 -20% $4,788,667 -10% $4,788,667 0% $4,788,667 10% $4,788,667 20% $4,788,667 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! Sensitivity Analysis $11,000 $9,000 --Sales price - VC NPV Units Non-var. cost $7,000 $5,000 $3,000 $1,000 -$1,000 $3,000 $5,000 $7,000 -20% WACC -10% 0% 10% 20% Deviation Sales Price from Base Case -20% WACC NPV at Different Deviations from Base Variable Non-variable Cost Units Sold Cost $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 -10% $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 0% $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 10% 20% Range $ S os $ c. Provide an interpreation of the Sensivity Chart created above. d. Calculate the Present Value of CCA Tax Shield for the five years using the equation (11-3) from the book Total PV of CCA Tax Shield for assuming asset is held in perpetuity adjusted with one-half year rule PV of the Lost Tax Shield PV of CCA Tax Shield for the project e. Would you recommend that the project be accepted? Explain. f. What would be your recommendation for source of capital to finance this project; debt, preferred shares or common shares? Explain. 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 prices, holding other thing constant. The one-variable Data Table is setup in this worksheet. There are many videos in YouTube to show how one- variable inputs work. You do not have to setup the Data Table. You have to fill up the number for Base Case only. Formula is setup for the orange highlighted cells. 1st YEAR UNIT SALES WACC % Deviation from % Deviation from Units NPV NPV Base Case Sold Base Case WACC -20% -20% 0.00% -10% -10% 0.00% $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 0% 0% 10% 10% 0.00% 20% 20% 0.00% % Deviation VARIABLE COSTS % Deviation SALES PRICE from Variable NPV from Sales NPV Base Case Costs Base Case Price -20% 0.00 -20% 0.00 -10% 0.00 -10% 0.00 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 0% 0% 10% 0.00 10% 0.00 20% 0.00 20% 0.00 % Deviation NON-VARIABLE COSTS from Fixed NPV Base Case Costs $4,788,667 -20% $4,788,667 -10% $4,788,667 0% $4,788,667 10% $4,788,667 20% $4,788,667 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! Sensitivity Analysis $11,000 $9,000 --Sales price - VC NPV Units Non-var. cost $7,000 $5,000 $3,000 $1,000 -$1,000 $3,000 $5,000 $7,000 -20% WACC -10% 0% 10% 20% Deviation Sales Price from Base Case -20% WACC NPV at Different Deviations from Base Variable Non-variable Cost Units Sold Cost $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 -10% $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 0% $4,788,667 $4,788,667 $4,788,667 $4,788,667 $4,788,667 10% 20% Range $ S os $ c. Provide an interpreation of the Sensivity Chart created above. d. Calculate the Present Value of CCA Tax Shield for the five years using the equation (11-3) from the book Total PV of CCA Tax Shield for assuming asset is held in perpetuity adjusted with one-half year rule PV of the Lost Tax Shield PV of CCA Tax Shield for the project e. Would you recommend that the project be accepted? Explain. f. What would be your recommendation for source of capital to finance this project; debt, preferred shares or common shares? Explain
