* ONLY NEED THE SENSITIVITY ANALYSIS SHEET* Assignment instructions: Show how a sensitivity analysis would;d be done in excel with following data:

AutoSave OFF H FC ... Book2 Q Home Insert Draw Page Layout Formulas Data Review View Automate Tell me Comments Share fx A v Q V Define Name v Trace Precedents Calculate Now Use in Formula Trace Dependents Insert AutoSum Recently Financial Logical Text Date & Lookup & Math & More Name Show Error Watch Calculation Calculate Sheet Function Used Time Reference Trig Functions Manager i Create from Selection Remove Arrows v Formulas Checking Window Options Function Library Defined Names Formula Auditing Calculation x Update Available We've made some fixes and improvements. To complete the process, the app needs to restart. Restart Now A1 fx A B C D E F G H K L M N O P Total acres: 16.5 Selling price per acre: $200,000 3 Total Sale Value $3,300,000 Basis $2,500,000 5 Profit $800,000 6 Capital Gains Tax Rate 20% Tax on profit $160,000 8 Net Cash Flow $640,000 9 Discount Rate 15% 10 NPV ($1,689,981) 11 12 13 14 15 29 30 Scenario 1 Sell Now Scenario 2 Hold and Sell Scenario 3 Develop and Sell Summary Sensitivity Analysis + Ready Accessibility: Good to go 125%AutoSave OFF H FC ... Book2 Q Home Insert Draw Page Layout Formulas Data Review View Automate Tell me Comments Share fx A v Q V Define Name v Trace Precedents A Calculate Now Use in Formula Trace Dependents Ge Insert AutoSum Recently Financial Logical Text Date & Lookup & Math & More Name Show Error Watch Calculation Function Used Time Reference Trig Functions Manager i Create from Selection Remove Arrows v Calculate Sheet Formulas Checking Window Options Function Library Defined Names Formula Auditing Calculation x Update Available We've made some fixes and improvements. To complete the process, the app needs to restart. Restart Now B14 fx A B C D E F G H I J K L Intial Investment $2,500,000 2 Retail Space (sq ft) 175,000 Vacancy Rate 75% W Net Rent per sq ft $4.00 Annual Rental Income $175,000.00 Holding Period (years) Total Rental Income $525,000.00 Initial Investment 25000000 00 Future Selling price per acre $300,000 Year 1 Income $175,000.00 9 Future Sale Value $4,950,000.0 Year 2 Income $175,000.00 10 Profit $5,300,000.00 Year 3 Income $175,000.00 11 NPV $1,154,269.75 $5,125,000.00 12 IRR -32% 13 14 15 16 17 18 19 20 21 22 23 24 25 Scenario 1 Sell Now Scenario 2 Hold and Sell Scenario 3 Develop and Sell Summary Sensitivity Analysis + Ready Accessibility: Good to go 150%AutoSave OFF H FC ... Book2 Q Home Insert Draw Page Layout Formulas Data Review View Automate Tell me Comments Share fx A v Q V Define Name v Trace Precedents A Calculate Now Use in Formula Trace Dependents Ge Insert AutoSum Recently Financial Logical Text Date & Lookup & Math & More Name Show Error Watch Calculation Function Used Time Reference Trig Functions Manager i Create from Selection Remove Arrows v F Calculate Sheet Formulas Checking Window Options Function Library Defined Names Formula Auditing Calculation x Update Available We've made some fixes and improvements. To complete the process, the app needs to restart. Restart Now C21 fx B C D E F G H K Auumptions Contruction Cost W NH $30,000,000 Year 1 2 3 4 Apartment Count 250 Construction Costs ($30,000,000) Initial Vacancy Rate 15% 5 Loan Interest $1,470,000 $1,470,000 $1,470,000 $1,470,000 Monthly rent per apartment $1,200 6 Annual Rent Income $261,375.00 $267,909.38 274,607.11 $281,472.29 Rent Growth Rate 2.50% Monthly Rent Income $21,781.25 $22,325.78 $22,883.93 $23,456.02 Construction Du 12 EBITDA $104,550.00 $107,163.75 109,842.84 $112,588.91 Stabilized EBITDA Percentage 10% 9 Selling Costs $3,136.50 $3,214.91 $3,295.29 $3,377.67 Selling Costs Per 3% 10 Net Sale Proceeds $101,413.50 $103,948.84 $106,547.56 $109,211.25 Cap Rate at Year 5 6% 1 Cumulative Cash Flow ($30,000,000) $29,895,450.00) ($29,788,286.25) $29,678,443.41) $29,565,854.49) Desired Return Rate 15% 12 Annual Cash Flow ($30,000,000 ($1,208,625.00) ($1,202,090.63) ($1,195,392.89) ($1,188,527.71) Loan amount (70% of Contruction Costs) $21,000,000 NPV ($29,065,622.85) Loan Intrest Rate 7% 14 IRR 4.6% Loan Term (years) 5%/ 15 Loan Intrest Rate $1,470,000 16 Scenario 1 Sell Now Scenario 2 Hold and Sell Scenario 3 Develop and Sell Summary Sensitivity Analysis + Ready Accessibility: Good to go 100%