Cost Management 6th Edition by Edward Blocher,David Stout ,Paul Juras,Gary Cokins

Exercise 12

NPV Analysis; Sensitivity Analysis; Data Tables in Excel This assignment is designed to introduce you to the preparation of both a one-variable and a two-variable "data table" in Excel 2010. Such tables are useful for conducting and reporting the results of a series of "what-if" analyses. Assume the following cash flows for a hypothetical five-year investment project that in time 0 would require a net investment outlay of $350,000, annual (after-tax) cash inflows of $100,000, and a 10% discount rate (weighted-average cost of capital).
Required
1. Consult the specified online help file (Microsoft website) regarding the preparation of "data tables." (See footnote 17.)
2. Prepare a one-variable data table where you depict NPV of the proposed investment at each of the following discount rates: 8% to 12%, in increments of 0.5%.
3. Prepare a two-variable data table where in addition to the 10 discount rates assumed in requirement 2 (above), you want to consider three possible levels of after-tax cash inflows per year: $90,000, $100,000, and, $110,000. (Thus, your table will include 10 3 = 30 cells.)
Reference: