Chapter 11-P18: Doing the Scenario Analysis "Manually"
I want to explain an alternative method (and I think better, for learning at least) of solving the scenario analysis than working with built-in Excel tables.
Working with datatables is really an advanced feature in Excel. It's fine to this part "manually." By manually, what I mean is to change the input variable in question then see how it effects the NPV and then type in the NPV in the associated cell. Then change the next input variable and repeat.
For example, in the first table with the sensitivity analysis we're varying the "Units Sold." The cell this actually appears in within the model is D29, and it is originally set to 1000 units sold. In the table we're varying the units sold by -20%, -10%, 0%, +10% and +20%. This equates to 800, 900, 1000, 1100, and 1200 units sold, respectively. To fill in the NPV, you can just go to cell D29, type in 800 and the look at the NPV output cell (D79). Then manually enter the value of D79 into the sensitivty table next to the 800 units (cell C103). Then you repeat this for the 900 units, 1000 units, 1100 units and 1200 units. Then you move to the next table, find the appropriate input cell in the model and then see how varying that affects the NPV, and so on. The benefit of this method is it forces you to really see, one variable at a time, how changing the input affects the output (i.e. the NPV).© BrainMass Inc. brainmass.com March 22, 2019, 3:16 am ad1c9bdddf
This solution shows how to build an Excel model for capital budgeting. The model includes calculations for NPV, IRR, MIRR, PI, payback period, crossover rate and discounted payback.