Excel Model for Capital Budgeting
Not what you're looking for?
Chapter 11-P18: Doing the Scenario Analysis "Manually"
Hi All:
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).
Purchase this Solution
Solution Summary
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.
Purchase this Solution
Free BrainMass Quizzes
IPOs
This Quiz is compiled of questions that pertain to IPOs (Initial Public Offerings)
MS Word 2010-Tricky Features
These questions are based on features of the previous word versions that were easy to figure out, but now seem more hidden to me.
Balance Sheet
The Fundamental Classified Balance Sheet. What to know to make it easy.
Understanding Management
This quiz will help you understand the dimensions of employee diversity as well as how to manage a culturally diverse workforce.
Introduction to Finance
This quiz test introductory finance topics.