19. An investor wants to determine what the effect will be given a change in the discount rate on a prospective project. The project will require a $30,000 initial investment and $1,000 in expenses each year for five years. The investor is confident that he will be able to sell the project for $65,000 at the end of a 5-year holding period. Use Microsoft Excel to construct a simple sensitivity analysis of the discount rate. Use the figure below as a reference to construct the worksheet. Please refer to the Introduction to Excel found in the Foreword to this workbook and on the course resources webpage for a step-by-step guide on how to use and create a what-if analysis. (NOTE: Do not confuse this with the IF Function) F G H C D E Sensitivity on the NPV Project Present Value to Variations in the Discount Rate 2 $1,000.00 3 $1,000.00 5 -$1,000.00 Reversion $65,000.00 $1,000.00 A B 1 2 3 Project Cash Flows: 4 0 1 5 $30,000.00 $1,000.00 6 7 NPV: $11,230.43 8 Discount Rate: 0.075 9 10 Discount Net Present 11 Rate (%) Value ($) 12 0.075 11,230 13 0.10 =NPV (B8,B5,C5,D5,E5,F5+G5)+A5 =B7 14 15 16 0.12 0.13 17 18 19 20 21 0.14 0.15 0.16 0.17 0.18 0.19 0.20 0.25 22 23 Which of the following four columns represents the correct result for the sensitivity analysis? A B D 11,230 11,230 11,230 11,230 6,569 1,569 9,674 6,569 3,278 -1,722 6,115 4,594 1,762 -3,238 4,476 1,762 326 -4,674 2,923 384 -1,036 -6,036 1,450 -1,036 -2,327 -7,327 54 -2,333 -3,552 -8,552 -1,272 -3,743 -4,715 -9,715 -2,530 -4,715 -5,819 -10,819 -3,724 -5,634 -6,869 -11,869 -4,859 -6,375 -11,390 -16,390 -9,752 -11,390 (1) A (2) B (3) C (4) D