Please use the 4 excel sheets to answer the question

Please show all work/formula in excel. Need ASAP

0 1 129,950 1,170 2 139,047 1,251 3 145,999 1,314 4 151,109 1,360 S 155,643 1,401 6 160,312 1,443 7 165,121 1,486 128,781 51,512 137,795 55,118 144,685 57,874 149,749 59,900 154,242 61,697 158,869 63,548 163,635 65,454 98,181 77,268 1,000,000 (922,732) 82,677 100,000 (17,323) 86,811 100,000 (13,189) 89,850 100,000 (10,150) 2 3 Potential Gross Income 4 Vacancy & Collection Losses 5 Other Income 6 Rental Concessions 7 Effective Gross Income 8 Operating Expenses Expense Recoveries O Net Operating Income 11 Capital Expenditures 12 Operating Cash Flows 13 Reversion Cash Flows 14 Property-Before-Tax Cash Flows 15 Debt Service 16 Equity-Before-Tax Cash Flows 17 18 19 Present Value of PBTCFS 20 NPV for All Investors 21 IRR for All Investors 22 23 4 Present Value of EBTCFS 25 NPV of Equity Investment 6 IRR of Equity Investment 27 92,545 95,321 100,000 100,000 (7,455) (4,679) 1,922,712 (7,455) 1,918,033 205,122 1,737,749 (212,577) 180,284 Assumptions: Purchase Price Square footage Year O Rent/SF Year 1 Rent Growth Year 2 Rent Growth Year 3 Rent Growth Year 4 Rent Growth Year 5+ Rent Growth Vacancy Rate Operating Expense Ratio Year 1 Capex Year 2+ Cap Ex Cap Rate Selling Expenses / Price Loan-to-Value Ratio Loan Term (years) Margin Cost of Equity Cost of Debt Weighted Average cost of Capital 3,045,016 10,184 11.87 7.5% 7.0% 5.0% 3.5% 3.0% 0.9% 40% 1,000,000 100,000 4.8% 6.0% 75% 15 2.25% 9% 5% 6% (3,045,016) (922,732) (2,283,762) 199,296 (761,254) (1,122,028) (17,323) 196,750 (214,073) (13,189) 199,728 (212,917) (10,150) 202,521 (212,671) (870,501) (15,417) (11,074) (8,040) (5,571) 1,352,138 (3,045,016) (2,603,481) -12% (180,181) (164,411) (150,662) (138,161) 107,498 (761,254) (1,029,383) (2,316,553) UNUMI Pre Forma Arion Table 0 1 129,950 1,170 2 139,047 1,251 3 145,999 1,314 4 151,109 1,360 5 155,643 1,401 6 160,312 1,443 7 165,121 1,486 128,781 51,512 137,795 55,118 144,685 57,874 149,749 59,900 154,242 61,697 158,869 63,548 163,635 65,454 Potential Gross Income Vacancy & Collection Losses Other Income Rental Concessions Effective Gross Income Operating Expenses Expense Recoveries Net Operating Income Capital Expenditures Operating Cash Flows Reversion Cash Flows Property Before-Tax Cash Flows Debt Service Equity-Before-Tax Cash Flows 98,181 77,268 1,000,000 (922,732) 82,677 100,000 (17,323) 86,811 100,000 (13,189) 89,850 100,000 (10,150) 92,545 95,321 100,000 100,000 (7,455) (4,679) 1,922,712 (7,455) 1,918,033 43,848 1,870,858 (51,303) 47,175 Assumptions: Purchase Price Square Footage Year O Rent/SF Year 1 Rent Growth Year 2 Rent Growth Year 3 Rent Growth Year 4 Rent Growth Year 5+ Rent Growth Vacancy Rate Operating Expense Ratio Year 1 Capex Year 2+ Cap Ex Cap Rate Selling Expenses / Price Loan-to-Value Ratio Loan Term (years) Mortgage Rate Cost of Equity Cost of Debt Weighted Average cost of Capital 3,045,016 10,184 11.87 7.5% 7.0% 5.0% 3.5% 3.0% 0.9% 40% 1,000,000 100,000 4.8% 6.0% 60% 7 2.40% 9% 7% 8% (3,045,016) (1,827,010) (1,218,006) (922,732) 43,848 (966,580) (17,323) 43,848 (61,171) (13,189) 43,848 (57,037) (10,150) 43,848 (53,999) (855,966) (14,907) (10,528) (7,516) (5,121) 1,222,204 Present Value of PBTCFS NPV for All Investors IRR for All Investors (3,045,016) (2,716,851) -12% (886,770) (51,486) (44,043) (38,254) (33,344) 28,129 Present Value of EBTCFS NPV of Equity Investment IRR of Equity Investment (1,218,006) (2,243,775) WNUM! 4 2 139,047 1,251 3 145,999 1,314 151,109 1,360 5 155,643 1,401 6 160,312 1,443 165,121 1,486 3,045,016 10,184 11.87 7.5% 137,795 55,118 144,685 57,874 149,749 59,900 154,242 61,697 158,869 63,548 163,635 65,454 Assumptions: Purchase Price Square Footage Year O Rent/SF Year 1 Rent Growth Year 2 Rent Growth Year 3 Rent Growth Year 4 Rent Growth Year 5+ Rent Growth Vacancy Rate Operating Expense Ratio Year 1 Capex Year 2+ Cap Ex 98,181 82,677 100,000 (17,323) 86,811 100,000 (13,189) 89,850 100,000 (10,150) 92,545 95,321 100,000 100,000 17,455) (4,679) 1,922,712 17,455) 1,918,033 (17,323) (13,189) (10,150) 2 0 1 3 Potential Gross Income 129,950 Vacancy & Collection Losses 1,170 5 Other Income 5 Rental Concessions Effective Gross Income 128,781 3 Operating Expenses 51,512 Expense Recoveries o Net Operating Income 77,268 1 Capital Expenditures 1,000,000 2 Operating Cash Flows (922,732) 3 Reversion Cash Flows 4 Property-Before-Tax Cash Flows (3,045,016) (922,732) s Debt Service 6 Equity-Before-Tax Cash Flows (3,045,016) (922,732) 7 8 9 Present Value of PBTCFs (3,045,016) (836,462) O NPV for All Investors (2,852,613) 1 IRR for All Investors -12% 2 3 4 GP EBTCFS (1,045,016) (922,732) 5 Present Value of GP EBTCFS (1,045,016) (846,543) 6 NPV for GPS (1,928,359) 7 IRR for GPS #NUMI 7.0% 5.0% 3.5% 3.0% 0.9% 40% 1,000,000 100,000 4.8% 6.0% 2,000,000 66% 9% 11% 10% 15% Cap Rate (17,323) (13,189) (10,150) (7,455) 1,918,033 (14,235) (9,825) (6,854) (4,564) 1,064,342 Selling Expenses / Price LP Investment LP-to-Value Ratio GP Cost of Equity LP Cost of Equity Weighted Average Cost of Capital LPS' Preferred IRR (17,323) (14,580) (13,189) (10,184) (10,150) (7,191) (7.455) (4.845) 3 5 129,950 1,170 2 139,047 1,251 145,999 1,314 151,109 1,360 155,643 1,401 160,312 1,443 165, 121 1,486 128,781 51,512 137,795 55,118 144,685 57,874 149,749 59,900 154,242 61,697 158,869 63,548 163,635 65,454 98,181 77,268 1,000,000 (922,732) 82,677 100,000 (17,323) 86,811 100,000 (13,189) 89,850 100,000 (10,150) 2 3 Potential Gross Income 4 Vacancy & Collection Losses 5 Other Income 6 Rental Concessions 7 Effective Gross Income 8 Operating Expenses 9 Expense Recoveries L0 Net Operating Income 11 Capital Expenditures 12 Operating Cash Flows 13 Reversion Cash Flows 14 Property-Before-Tax Cash Flows 15 Debt Service 16 Equity-Before-Tax Cash Flows 17 18 19 Present Value of PBTCFS 20 NPV for All Investors 21 IRR for All Investors 22 23 24 Present Value of EBTCFS 25 NPV of Equity Investment 26 IRR of Equity Investment 92,545 95,321 100,000 100,000 (7,455) (4,679) 1,922,712 (7,455) 1,918,033 Assumptions: Purchase Price Square Footage Year O Rent/SF Year 1 Rent Growth Year 2 Rent Growth Year 3 Rent Growth Year 4 Rent Growth Year 5+ Rent Growth Vacancy Rate Operating Expense Ratio Year 1 Capex Year 2+ Cap Ex Cap Rate Selling Expenses / Price Loan-to-Value Ratio Cost of Equity Cost of Debt Weighted Average Cost of Capital 3,045,016 10,184 11.87 7.5% 7.0% 5.0% 3.5% 3.0% 0.9% 40% 1,000,000 100,000 4.8% 6.0% 0% 9% 0% 9% (3,045,016) (922,732) (17,323) (13,189) (10,150) (3,045,016) (922,732) (17,323) (13,189) (10,150) (7,455) 1,918,033 (14,580) (10,184) (7,191) (4,845) 1,143,661 (3,045,016) (846,543) (2,784,699) -12% (846,543) (14,580) (10,184) (7,191) (4,845) 1,143,661 (3,045,016) (2,784,699) -12% Remember: Do all your calculations in Excel, and submit the final spreadsheet with all your work. Each cell must contain the correct formula in order for you to get credit. Financial calculators are not acceptable. After reviewing your pro formas for the new life sciences facility in the Inland Empire, the CFO of your company suggests that you change the rental calculations to better reflect the true value of the property after renovations. Use the midterm exam answer key to consider the following options: 1. Instead of the rent growth you previously assumed, you decide to calculate a step-up rent after year 1, with an increase of $10 in year 2 and $3 in each year thereafter. 2. Instead of step-up rent after year 2, you decide to calculate an indexed rent following the growth of the life sciences industry. You assume the industry will grow 10% in year 3 and annually every year thereafter. For each option, how does each NPV and IRR change? How does your analysis of the best option change for your company? Remember: Do all your calculations in Excel, and submit the final spreadsheet with all your work. Each cell must contain the correct formula in order for you to get credit. Financial calculators are not acceptable. Your CFO still isn't satisfied. She asks you to make one more adjustment: Don't let the tenant get away with a gross lease. Consider the following options: 1. Consider a double-net lease. How do NPV and IRR change? 2. Consider a triple-net lease. How do NPV and IRR change? Given all the options above, what is best for your company, and what is best for your partners? 0 1 129,950 1,170 2 139,047 1,251 3 145,999 1,314 4 151,109 1,360 S 155,643 1,401 6 160,312 1,443 7 165,121 1,486 128,781 51,512 137,795 55,118 144,685 57,874 149,749 59,900 154,242 61,697 158,869 63,548 163,635 65,454 98,181 77,268 1,000,000 (922,732) 82,677 100,000 (17,323) 86,811 100,000 (13,189) 89,850 100,000 (10,150) 2 3 Potential Gross Income 4 Vacancy & Collection Losses 5 Other Income 6 Rental Concessions 7 Effective Gross Income 8 Operating Expenses Expense Recoveries O Net Operating Income 11 Capital Expenditures 12 Operating Cash Flows 13 Reversion Cash Flows 14 Property-Before-Tax Cash Flows 15 Debt Service 16 Equity-Before-Tax Cash Flows 17 18 19 Present Value of PBTCFS 20 NPV for All Investors 21 IRR for All Investors 22 23 4 Present Value of EBTCFS 25 NPV of Equity Investment 6 IRR of Equity Investment 27 92,545 95,321 100,000 100,000 (7,455) (4,679) 1,922,712 (7,455) 1,918,033 205,122 1,737,749 (212,577) 180,284 Assumptions: Purchase Price Square footage Year O Rent/SF Year 1 Rent Growth Year 2 Rent Growth Year 3 Rent Growth Year 4 Rent Growth Year 5+ Rent Growth Vacancy Rate Operating Expense Ratio Year 1 Capex Year 2+ Cap Ex Cap Rate Selling Expenses / Price Loan-to-Value Ratio Loan Term (years) Margin Cost of Equity Cost of Debt Weighted Average cost of Capital 3,045,016 10,184 11.87 7.5% 7.0% 5.0% 3.5% 3.0% 0.9% 40% 1,000,000 100,000 4.8% 6.0% 75% 15 2.25% 9% 5% 6% (3,045,016) (922,732) (2,283,762) 199,296 (761,254) (1,122,028) (17,323) 196,750 (214,073) (13,189) 199,728 (212,917) (10,150) 202,521 (212,671) (870,501) (15,417) (11,074) (8,040) (5,571) 1,352,138 (3,045,016) (2,603,481) -12% (180,181) (164,411) (150,662) (138,161) 107,498 (761,254) (1,029,383) (2,316,553) UNUMI Pre Forma Arion Table 0 1 129,950 1,170 2 139,047 1,251 3 145,999 1,314 4 151,109 1,360 5 155,643 1,401 6 160,312 1,443 7 165,121 1,486 128,781 51,512 137,795 55,118 144,685 57,874 149,749 59,900 154,242 61,697 158,869 63,548 163,635 65,454 Potential Gross Income Vacancy & Collection Losses Other Income Rental Concessions Effective Gross Income Operating Expenses Expense Recoveries Net Operating Income Capital Expenditures Operating Cash Flows Reversion Cash Flows Property Before-Tax Cash Flows Debt Service Equity-Before-Tax Cash Flows 98,181 77,268 1,000,000 (922,732) 82,677 100,000 (17,323) 86,811 100,000 (13,189) 89,850 100,000 (10,150) 92,545 95,321 100,000 100,000 (7,455) (4,679) 1,922,712 (7,455) 1,918,033 43,848 1,870,858 (51,303) 47,175 Assumptions: Purchase Price Square Footage Year O Rent/SF Year 1 Rent Growth Year 2 Rent Growth Year 3 Rent Growth Year 4 Rent Growth Year 5+ Rent Growth Vacancy Rate Operating Expense Ratio Year 1 Capex Year 2+ Cap Ex Cap Rate Selling Expenses / Price Loan-to-Value Ratio Loan Term (years) Mortgage Rate Cost of Equity Cost of Debt Weighted Average cost of Capital 3,045,016 10,184 11.87 7.5% 7.0% 5.0% 3.5% 3.0% 0.9% 40% 1,000,000 100,000 4.8% 6.0% 60% 7 2.40% 9% 7% 8% (3,045,016) (1,827,010) (1,218,006) (922,732) 43,848 (966,580) (17,323) 43,848 (61,171) (13,189) 43,848 (57,037) (10,150) 43,848 (53,999) (855,966) (14,907) (10,528) (7,516) (5,121) 1,222,204 Present Value of PBTCFS NPV for All Investors IRR for All Investors (3,045,016) (2,716,851) -12% (886,770) (51,486) (44,043) (38,254) (33,344) 28,129 Present Value of EBTCFS NPV of Equity Investment IRR of Equity Investment (1,218,006) (2,243,775) WNUM! 4 2 139,047 1,251 3 145,999 1,314 151,109 1,360 5 155,643 1,401 6 160,312 1,443 165,121 1,486 3,045,016 10,184 11.87 7.5% 137,795 55,118 144,685 57,874 149,749 59,900 154,242 61,697 158,869 63,548 163,635 65,454 Assumptions: Purchase Price Square Footage Year O Rent/SF Year 1 Rent Growth Year 2 Rent Growth Year 3 Rent Growth Year 4 Rent Growth Year 5+ Rent Growth Vacancy Rate Operating Expense Ratio Year 1 Capex Year 2+ Cap Ex 98,181 82,677 100,000 (17,323) 86,811 100,000 (13,189) 89,850 100,000 (10,150) 92,545 95,321 100,000 100,000 17,455) (4,679) 1,922,712 17,455) 1,918,033 (17,323) (13,189) (10,150) 2 0 1 3 Potential Gross Income 129,950 Vacancy & Collection Losses 1,170 5 Other Income 5 Rental Concessions Effective Gross Income 128,781 3 Operating Expenses 51,512 Expense Recoveries o Net Operating Income 77,268 1 Capital Expenditures 1,000,000 2 Operating Cash Flows (922,732) 3 Reversion Cash Flows 4 Property-Before-Tax Cash Flows (3,045,016) (922,732) s Debt Service 6 Equity-Before-Tax Cash Flows (3,045,016) (922,732) 7 8 9 Present Value of PBTCFs (3,045,016) (836,462) O NPV for All Investors (2,852,613) 1 IRR for All Investors -12% 2 3 4 GP EBTCFS (1,045,016) (922,732) 5 Present Value of GP EBTCFS (1,045,016) (846,543) 6 NPV for GPS (1,928,359) 7 IRR for GPS #NUMI 7.0% 5.0% 3.5% 3.0% 0.9% 40% 1,000,000 100,000 4.8% 6.0% 2,000,000 66% 9% 11% 10% 15% Cap Rate (17,323) (13,189) (10,150) (7,455) 1,918,033 (14,235) (9,825) (6,854) (4,564) 1,064,342 Selling Expenses / Price LP Investment LP-to-Value Ratio GP Cost of Equity LP Cost of Equity Weighted Average Cost of Capital LPS' Preferred IRR (17,323) (14,580) (13,189) (10,184) (10,150) (7,191) (7.455) (4.845) 3 5 129,950 1,170 2 139,047 1,251 145,999 1,314 151,109 1,360 155,643 1,401 160,312 1,443 165, 121 1,486 128,781 51,512 137,795 55,118 144,685 57,874 149,749 59,900 154,242 61,697 158,869 63,548 163,635 65,454 98,181 77,268 1,000,000 (922,732) 82,677 100,000 (17,323) 86,811 100,000 (13,189) 89,850 100,000 (10,150) 2 3 Potential Gross Income 4 Vacancy & Collection Losses 5 Other Income 6 Rental Concessions 7 Effective Gross Income 8 Operating Expenses 9 Expense Recoveries L0 Net Operating Income 11 Capital Expenditures 12 Operating Cash Flows 13 Reversion Cash Flows 14 Property-Before-Tax Cash Flows 15 Debt Service 16 Equity-Before-Tax Cash Flows 17 18 19 Present Value of PBTCFS 20 NPV for All Investors 21 IRR for All Investors 22 23 24 Present Value of EBTCFS 25 NPV of Equity Investment 26 IRR of Equity Investment 92,545 95,321 100,000 100,000 (7,455) (4,679) 1,922,712 (7,455) 1,918,033 Assumptions: Purchase Price Square Footage Year O Rent/SF Year 1 Rent Growth Year 2 Rent Growth Year 3 Rent Growth Year 4 Rent Growth Year 5+ Rent Growth Vacancy Rate Operating Expense Ratio Year 1 Capex Year 2+ Cap Ex Cap Rate Selling Expenses / Price Loan-to-Value Ratio Cost of Equity Cost of Debt Weighted Average Cost of Capital 3,045,016 10,184 11.87 7.5% 7.0% 5.0% 3.5% 3.0% 0.9% 40% 1,000,000 100,000 4.8% 6.0% 0% 9% 0% 9% (3,045,016) (922,732) (17,323) (13,189) (10,150) (3,045,016) (922,732) (17,323) (13,189) (10,150) (7,455) 1,918,033 (14,580) (10,184) (7,191) (4,845) 1,143,661 (3,045,016) (846,543) (2,784,699) -12% (846,543) (14,580) (10,184) (7,191) (4,845) 1,143,661 (3,045,016) (2,784,699) -12% Remember: Do all your calculations in Excel, and submit the final spreadsheet with all your work. Each cell must contain the correct formula in order for you to get credit. Financial calculators are not acceptable. After reviewing your pro formas for the new life sciences facility in the Inland Empire, the CFO of your company suggests that you change the rental calculations to better reflect the true value of the property after renovations. Use the midterm exam answer key to consider the following options: 1. Instead of the rent growth you previously assumed, you decide to calculate a step-up rent after year 1, with an increase of $10 in year 2 and $3 in each year thereafter. 2. Instead of step-up rent after year 2, you decide to calculate an indexed rent following the growth of the life sciences industry. You assume the industry will grow 10% in year 3 and annually every year thereafter. For each option, how does each NPV and IRR change? How does your analysis of the best option change for your company? Remember: Do all your calculations in Excel, and submit the final spreadsheet with all your work. Each cell must contain the correct formula in order for you to get credit. Financial calculators are not acceptable. Your CFO still isn't satisfied. She asks you to make one more adjustment: Don't let the tenant get away with a gross lease. Consider the following options: 1. Consider a double-net lease. How do NPV and IRR change? 2. Consider a triple-net lease. How do NPV and IRR change? Given all the options above, what is best for your company, and what is best for your partners