urpose The purpose of this assignment is to (1) improve your use of Excel, (2) demonstrate the flexibility of your Excel workbook, and (3) Analyze lease information. Scenario We are analyzing a potential investment multi-use property next to a university. The first floor is a convenience store and caf, the second floor is residential. We finally received the additional information we need to perform our due diligence and, just as you thought; the parking lot behind the building is included in the sale of the property. Note: Information given is for year 0 (2023), analysis is for Jan 1, 2024 (year 1). Tasks 1. Use the workbook from the previous assignment. Hint: Save a copy before making changes. 2. Make the following modifications and additions: a. Revenues: - The parking lot has a monthly income of $5,000 that should grow with general inflation (3%). - Retail and Restaurant market rents increase at 2.5% per year. - Chicago Dogs signed a 10-year Net-Net-Net contract that started Jan 1, 2023 at \$26/SF plus 10% of sales over $50,000. Their sales are on track to meet the projected first year sales of $40,000 with expected growth of 15% annually. They are responsible for their share of the CAM maintenance. - Shop Quick signed a 10-year Net-Net-Net contract that started Jan 1, 2020. The first 5 years were at $26/SF and will align with the market rent at year 6 . The current retail market rent is $28/SF, and the expected growth rate for retail space is 2.5% per year. They are responsible for their share of the CAM maintenance. - The residential leases are renewed on January 1 . The rents are expected to increase at the general inflation rate. b. Expenses - Common area maintenance for the first floor is $0.75 per square foot, increasing at the general inflation rate. - All operations expenses, except Property Manager, are recoverable from the 1st floor tenants. - Expected sales expenses in year 5 are 8%. 3. Analyze the information: a. Calculate the Expected sales price of the property at the end of the holding period. b. Calculate the Before Tax Cash Flow. c. Calculate the Before Tax IRR. urpose The purpose of this assignment is to (1) improve your use of Excel, (2) demonstrate the flexibility of your Excel workbook, and (3) Analyze lease information. Scenario We are analyzing a potential investment multi-use property next to a university. The first floor is a convenience store and caf, the second floor is residential. We finally received the additional information we need to perform our due diligence and, just as you thought; the parking lot behind the building is included in the sale of the property. Note: Information given is for year 0 (2023), analysis is for Jan 1, 2024 (year 1). Tasks 1. Use the workbook from the previous assignment. Hint: Save a copy before making changes. 2. Make the following modifications and additions: a. Revenues: - The parking lot has a monthly income of $5,000 that should grow with general inflation (3%). - Retail and Restaurant market rents increase at 2.5% per year. - Chicago Dogs signed a 10-year Net-Net-Net contract that started Jan 1, 2023 at \$26/SF plus 10% of sales over $50,000. Their sales are on track to meet the projected first year sales of $40,000 with expected growth of 15% annually. They are responsible for their share of the CAM maintenance. - Shop Quick signed a 10-year Net-Net-Net contract that started Jan 1, 2020. The first 5 years were at $26/SF and will align with the market rent at year 6 . The current retail market rent is $28/SF, and the expected growth rate for retail space is 2.5% per year. They are responsible for their share of the CAM maintenance. - The residential leases are renewed on January 1 . The rents are expected to increase at the general inflation rate. b. Expenses - Common area maintenance for the first floor is $0.75 per square foot, increasing at the general inflation rate. - All operations expenses, except Property Manager, are recoverable from the 1st floor tenants. - Expected sales expenses in year 5 are 8%. 3. Analyze the information: a. Calculate the Expected sales price of the property at the end of the holding period. b. Calculate the Before Tax Cash Flow. c. Calculate the Before Tax IRR