The first step is to rank the range of possible npvs

This preview shows page 61 - 64 out of 605 pages.

The first step is to rank the range of possible NPV's for each of the input variables that is being changed. We shown in the rose-colored area below. In our example, the range for sales price/unit is the largest and the ra capital (r) is the smallest. In the yellow figure below, we created an XY scatter chart, with three X-values for each of the input variables deviations shown in the "Data for Sensitivity Graph" shown above). The Y-values for each series are the corr rank repeated for all 3 X-values) for the input, shown below in the rose and aqua areas (the aqua areas are j that each X-value will have a Y-value). To summarize, each variable is plotted so that its "width" on the X-axi has on NPV, and its "height" on the chart (the Y-axis) determined by the input's rank in terms of the NPV's se It is helpful to also plot a vertical line showing the base-case NPV. To do this, we have all 3 X-values equal to t corresponding Y-values go from the lowest rank to the highest rank (shown in the area below with the nause shade.) In the final presentation of the tornado diagram below, we set the vertical axis to cross the horizontal axis a (i.e., we put the X-axis at the top of the chart instead of at the bottom). For the vertical axis (the Y-axis), we c and for labels, so the chart doesn't show this axis. Finally, we formatted the "right" data points for each serie These changes are purely cosmetic in nature. The advantage of this method is that the chart below will update automatically if you change the model (and For a slightly less complicated approach that requires manual intervention, see the example to the right. For diagram below Tornado Diagram for Solar Water Heater Project: Range of Outcomes for Input Deviations from Base-Case (Dollars in Thousands) −$200,000 −$150,000 −$100,000 −$50,000 $0 $50,000 1 NPV A B C D E F G 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254
A B C D E F G 255 256 257 258 259 260 261 262 263 264 265 266 267
NPV Breakeven Analysis Table 11-1 Input Input Value that Produces Zero NPV Sales price per unit, Year 1 $1.4567 Variable cost per unit (VC), Year 1 $1.1128 Annual change in units sold, after Year 1 7.40% Units sold, Year 1 8,968 Nonvariable cost (Non-VC), Year 1 $2,649 Project r 13.79% Data Tables: Multiple Outputs for a Single Input NPV Breakeven Analysis (Dollars in Thousands) Example: NPV and IRR for Changes in Sales Price % Deviation SALES PRICE from Sales NPV IRR Base Case Price ### -29.9% -30% $1.05 ### -143.1% -15% $1.28 ### Not found 0% $1.50 ### -29.9% 15% $1.73 ### -27.5% 30% $1.95 ### -25.2% In breakeven analysis, we find the value of the input variable that produces a zero NPV. It is easiest to do thi Seek. For example, the screen shot below shows the Goal Seek inputs we used to set the cell for NPV to a valu changing the cell for the sales price. We repeated this for the other inputs. Data tables can easily be extended to show multiple outputs for a single input. Simply add an additional colu reference to the desired additional output. Highlight the specified values for the input and highlight all the c output as we show shaded in gray below (be sure to also highlight the cell references above the outputs). Th Data, Tables, and set "Column input" to the cell reference of the desired input.

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture