Hi, I need help with this spreadsheet, 29 and 30 do not have to be completed. I appreciate it.Thank You!!!
Step Instructions Points Possible Start Excel. Download and open the file named Excel Prest In the NOI sheet calculate the Total Rentable Square Feet (RSF by Unit Type in range F3-F7 by multiplying the number of units for each type by the square footage of each unit type. Then calculate the Total rentable square footage of the building in cell FB. In cell 14, enter a formula that calculates the annual insurance expense for Year 1. As the building owner, you obtain insurance at a rate of $400 per residential unit. Commercial units pay their own insurance. So you only pay insurance on the residential units. In cel I5, enter a formula to calculate the Repairs & Maintenance expense estimate for Year 1. You assume that each month, there will be a repair expense of $120 for half of the residential units. Commercial units pay for their own repairs and maintenance. In cel 16, enter a formula to calculate the annual Utilities expense estimate for Year 1. All tenants will pay their own utilities. But you, the owner, will pay $0.40 per square foot per month for the 1200 square feet of common area space (E13) of the building (lobby, hallways, elevator, gym, etc.). In cell 19, enter a formula to calculate the annual Janitorial Cleaning expense estimate for Year 1. A cleaning company can clean the 1200 SF common area twice per week at a rate of $150 per day. Enter the annual deaning expense, assuming 52 weeks in a year. In cell 110, enter a formula to calculate the annual property Management fee. Management companies charge 5% of base rent. (Use monthly rent estimates in range C3:C7 and the number of units per type in range D3:D7 to determine annual base rent.) In cell 112, enter a formula to calculate the annual doorman security expense estimate for Year 1. You employ 2 doormen each day, one for an 8am-4pm shift and the other between 4pm-12 midnight. Each one spends an 8 hour shift each day, 6 days per week. Assume 52 weeks per year and $10/hour wage for each doorman In cell 113, enter a formula to calculate the Brokerage Fee to find tenants for the residential units. Realtors charge the equivalent of one month of rent for each unit leased. In cell 114, enter a formula to calculate the Brokerage Fee to find tenants for the commercial units. Each commercial lease is negotiated for a 5-year term of fixed base rent. So the brokerage fee for each unit is an upfront charge of 5% of the total base rent for the entire 5-year lease period. 15 In cel 115, enter a formula to calculate the Capital Reserves estimate for Year 1. Capital Reserves are savings put aside in case there is a large unforeseen repair or expense incurred. You estimate a capital reserve of $0.50 cents per RSF of the building. In cell E19, enter a Vookup function to calculate the annual base rent for the first unit based on Unit Type. Use the 2020 Rental Assumptions table in your formula and make sure to calculate awa/ rent. Copy your formula down the column to cell E42. You assume that Residential Rent will increase by 2% each year (see cell E11). For each residential unit, calculate annual rent estimates for Years 2021 to 2029 by applying the Residential Rent Increase to the previous year's rent. Base rent for the commercial leases are fixed for 5 years, after which you will either renew with the current tenant or re-lease to a new tenant for an increased fixed rent for 5 years. You assume a Year 6 rent increase (see cell E12) over the current negotiated rent for each unit. For each commercial unit, enter the annual rent for Years 2021 to 2029, assuming that in the 6th Year, there will be a new increased rent for the next 5 years. 15 2 Calculate Total Base Rent for each year in row 43. Apply Bold font to this row and format the values as Currency format with no decimals. You negotiate a percentage rent component of the lease with the Large Commercia/tenant in Unit G1. The Percentage Rent clause says that the tenant will pay you 2% of their gross sales, not to exceed $12,000 in percentage rent. The Large Commercial tenant has given you their sales projections for the next 10 years frow 79). Enter an IF function in cells E45:N45 that calculates the percentage rent based on the tenant's sales projection for that Year. Calculate Potential Gross Rent for each year in row 46 by adding Percentage Rent to Total Base Rent. Apply Bold font to this row and format the values as Currency format with no decimals. 2 You can expect a certain amount of vacancy and uncollected rent each year, which is accounted for in the Vacancy and Collection Loss estimate (cell E14), Calculate Vacancy and Collection Loss estimate by multiplying the loss factor to the Potential Gross Rent for each year in row 48. 19 Subtract Vacancy & Collection Loss from Potential Gross Rent to determine Effective Gross Rent for each year in row 49. Apply Bold font to this row and format the values as Currency format with no decimals. In cell E54. enter an HLookup formula based on the Year to determine the Per RSF tax rate using the table in the Taxcast sheet. (Do not enter the hardcoded tax rate.) You apply the Per RSF rate found by the HLookup to the residential RSF. The commercial units will pay their own share of taxes. Copy your formula to Years 2021 to 2029 Hint: See Tax Expense formula in cells 13 and 13. Set cell E55 equal to the 2020 Insurance expense in the 2020 Operating Expense Assumptions table at the top of the sheet. Copy the formula down to cell E60. For each of the expenses in rows 55 to 60, calculate 2021 to 2029 expenses using their respective annual percentage increases displayed in column B Management Fees are calculated as a percentage of Total Base Rent. In row 61, enter a formula to calculate the Management Fee for each year. You negotiate a maintenance contract with the elevator manufacturer for a forced cost of $900 per year for 10 years. Set cell E62 equal to the appropriate cell in the 2020 Operating Expense Assumptions table. Enter a formula in cells F62:N62 to display the annual Elevator Maintenance cost for each year. Updated: 10/30/2017 Office 2016 - Instructions el Project Step Instructions Points Possible 24 Set cell E63 equal to the appropriate cell in the 2020 Operating Expense Assumptions table. Enter a formula in cells F63:N63 to display the annual Security expense using the annual percentage increase displayed in column B. 25 Set cell E64 equal to the appropriate cell in the 2020 Operating Expense Assumptions table. Assume you'll pay a brokerage fee to re-rent or renew all of the residential units every 2 years. The brokerage fee for each unit will be 1-month's rent at the then-prevailing rent. Enter a formula every two years starting in 2022 to calculate the Brokerage Fee for Residential units. Set cell E65 equal to the appropriate cell in the 2020 Operating Expense Assumptions table. Assume you'll pay a brokerage fee to re-rent or renew each commercial unit every 5 years. The brokerage fee for each unit will be a percentage of the total lease base rent of the then-prevailing base rent over the entire lease Enter a formula to calculate the Brokerage Fee for Commercial units. Hint: Commercial unit brokerage fees will only be paid out in Year 1 and Year 6. Set cell E66 equal to the appropriate cell in the 2020 Operating Expense Assumptions table. Enter a formula in cells F66:N66 to display the annual Capital Reserves using the annual percentage increase displayed in column B. 27 Calculate Total Expenses for each year in row 67. Then calculate Net Operating Income for each year in row 69. Net Operating Income is calculated as Effective Gross Rent less Total Expenses. Apply Bold font and format the values as Currency format with no decimals for both rows. 29 Select the 2020 Expense Assumptions in array H3:115 and make a pie chart. Move it to its own sheet named Expense Chart 30 Apply Chart Style 6 to the pie chart. Change the Chart Title to Expense Comparison. Position the Legend on the righthand side. A E F H J K L M N 2020 Rental Assumptions 2020 Operating Expense Assumptions Extra Credit Total RSF by Unit Type #of Units Expense 2020 Taxes $0 $6,100,000 Unit Type 1 Bedroom 2 Bedroom 3 Bedroom 10 45% Rent Month $1,600 $2,200 $2,800 $3,200 $4,400 RSF per Unit 830 995 1,475 1,200 2 ,100 1 Mortgage Finance Purchase Price Down Payment Rate Down Payment Loan Amount Annual Interest Rate Loan Term (years) Monthly Debt Payment Annual Debt Payment 3.125% Large Commercial Total $3,000 $2.400 Insurance Repairs & Maintenance Utilities Legal Accounting Janitorial Cleaning Management Fees Elevator Maint. Security Brokerage Fee - Residential Brokerage Fee - Commercial Capital Reserves 24 Expense Rate Per $1.3767 Residential RSF/year $400 Residential Unit/year $120 Residential Unit / month $0.40 Per Common SF / month $3,000 Year $2,400 Year $150 Day of Cleaning 5% Dollar of Base Rent/year $900 Year $10 Hour Monthly Rent Every 2 Years 5% Total Lease Base Rent $0.50 RSF $900 Number of months / year Residential Rent Increase Commercial Rent Increase Common Area Sq. Footage Vacancy & Collection Loss 12 2% 10% 1200 4.5% Annually Every 5 Years Annually Income Unit # Unit Type 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 Unit 1A Unit 1B Unit 1C Unit 1D Unit 2A Unit 2B Unit 2C Unit 2D Unit 3A Unit 3B Unit 3C Unit 3D Unit 4A Unit 4B Unit 4C Unit 4D Unit SA Unit 5B Unit 5C Unit 5D Unit G1 1 Bedroom 2 Bedroom 2 Bedroom 3 Bedroom 1 Bedroom 2 Bedroom 2 Bedroom 3 Bedroom 1 Bedroom 2 Bedroom 2 Bedroom 3 Bedroom 1 Bedroom 2 Bedroom 2 Bedroom 3 Bedroom 2 Bedroom 2 Bedroom 3 Bedroom 3 Bedroom Large Commercial B Formula Bar F KL M N C Income Unit # Unit Type 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 Unit 1A 1 Bedroom Unit 1B 2 Bedroom Unit 1C 2 Bedroom Unit 1D 3 Bedroom Unit 2A 1 Bedroom Unit 2B 2 Bedroom Unit 2C 2 Bedroom Unit 2D 3 Bedroom Unit 3A 1 Bedroom Unit 3B 2 Bedroom Unit 3C 2 Bedroom Unit 3D 3 Bedroom Unit 4A 1 Bedroom Unit 4B 2 Bedroom Unit 4C 2 Bedroom Unit 4D 3 Bedroom Unit 5A 2 Bedroom Unit 5B 2 Bedroom Unit 5C 3 Bedroom Unit 5D 3 Bedroom Unit G1 Large Commerdal Unit G2 Small Commercial Unit G3 Small Commercial Unit 64 Small Commercial Total Base Rent 2% Percentage Rent Potential Gross Rent Less: Vacancy & Collection Loss Effective Gross Rent Less: Vacancy & Collection Loss Effective Gross Rent Expenses 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 Use Hookup Taxes 3% Insurance 3% Repairs & Maintenance 3% Utilities 3% Legal 3% Accounting 3% Janitorial Cleaning 5% Management Fees 0% Elevator Maint. 3% Security 1 month Brokerage Fee - Residential 5% Brokerage Fee - Commercial 3% Capital Reserves Total Expenses Net Operating Income Total Debt Service Cash Flow After Debt Service * Additional Notes Large Commercial Tenant Sales Projections 520000 530000 550000 545000 548000 580000 595000 610000 625000 640000 Step Instructions Points Possible Start Excel. Download and open the file named Excel Prest In the NOI sheet calculate the Total Rentable Square Feet (RSF by Unit Type in range F3-F7 by multiplying the number of units for each type by the square footage of each unit type. Then calculate the Total rentable square footage of the building in cell FB. In cell 14, enter a formula that calculates the annual insurance expense for Year 1. As the building owner, you obtain insurance at a rate of $400 per residential unit. Commercial units pay their own insurance. So you only pay insurance on the residential units. In cel I5, enter a formula to calculate the Repairs & Maintenance expense estimate for Year 1. You assume that each month, there will be a repair expense of $120 for half of the residential units. Commercial units pay for their own repairs and maintenance. In cel 16, enter a formula to calculate the annual Utilities expense estimate for Year 1. All tenants will pay their own utilities. But you, the owner, will pay $0.40 per square foot per month for the 1200 square feet of common area space (E13) of the building (lobby, hallways, elevator, gym, etc.). In cell 19, enter a formula to calculate the annual Janitorial Cleaning expense estimate for Year 1. A cleaning company can clean the 1200 SF common area twice per week at a rate of $150 per day. Enter the annual deaning expense, assuming 52 weeks in a year. In cell 110, enter a formula to calculate the annual property Management fee. Management companies charge 5% of base rent. (Use monthly rent estimates in range C3:C7 and the number of units per type in range D3:D7 to determine annual base rent.) In cell 112, enter a formula to calculate the annual doorman security expense estimate for Year 1. You employ 2 doormen each day, one for an 8am-4pm shift and the other between 4pm-12 midnight. Each one spends an 8 hour shift each day, 6 days per week. Assume 52 weeks per year and $10/hour wage for each doorman In cell 113, enter a formula to calculate the Brokerage Fee to find tenants for the residential units. Realtors charge the equivalent of one month of rent for each unit leased. In cell 114, enter a formula to calculate the Brokerage Fee to find tenants for the commercial units. Each commercial lease is negotiated for a 5-year term of fixed base rent. So the brokerage fee for each unit is an upfront charge of 5% of the total base rent for the entire 5-year lease period. 15 In cel 115, enter a formula to calculate the Capital Reserves estimate for Year 1. Capital Reserves are savings put aside in case there is a large unforeseen repair or expense incurred. You estimate a capital reserve of $0.50 cents per RSF of the building. In cell E19, enter a Vookup function to calculate the annual base rent for the first unit based on Unit Type. Use the 2020 Rental Assumptions table in your formula and make sure to calculate awa/ rent. Copy your formula down the column to cell E42. You assume that Residential Rent will increase by 2% each year (see cell E11). For each residential unit, calculate annual rent estimates for Years 2021 to 2029 by applying the Residential Rent Increase to the previous year's rent. Base rent for the commercial leases are fixed for 5 years, after which you will either renew with the current tenant or re-lease to a new tenant for an increased fixed rent for 5 years. You assume a Year 6 rent increase (see cell E12) over the current negotiated rent for each unit. For each commercial unit, enter the annual rent for Years 2021 to 2029, assuming that in the 6th Year, there will be a new increased rent for the next 5 years. 15 2 Calculate Total Base Rent for each year in row 43. Apply Bold font to this row and format the values as Currency format with no decimals. You negotiate a percentage rent component of the lease with the Large Commercia/tenant in Unit G1. The Percentage Rent clause says that the tenant will pay you 2% of their gross sales, not to exceed $12,000 in percentage rent. The Large Commercial tenant has given you their sales projections for the next 10 years frow 79). Enter an IF function in cells E45:N45 that calculates the percentage rent based on the tenant's sales projection for that Year. Calculate Potential Gross Rent for each year in row 46 by adding Percentage Rent to Total Base Rent. Apply Bold font to this row and format the values as Currency format with no decimals. 2 You can expect a certain amount of vacancy and uncollected rent each year, which is accounted for in the Vacancy and Collection Loss estimate (cell E14), Calculate Vacancy and Collection Loss estimate by multiplying the loss factor to the Potential Gross Rent for each year in row 48. 19 Subtract Vacancy & Collection Loss from Potential Gross Rent to determine Effective Gross Rent for each year in row 49. Apply Bold font to this row and format the values as Currency format with no decimals. In cell E54. enter an HLookup formula based on the Year to determine the Per RSF tax rate using the table in the Taxcast sheet. (Do not enter the hardcoded tax rate.) You apply the Per RSF rate found by the HLookup to the residential RSF. The commercial units will pay their own share of taxes. Copy your formula to Years 2021 to 2029 Hint: See Tax Expense formula in cells 13 and 13. Set cell E55 equal to the 2020 Insurance expense in the 2020 Operating Expense Assumptions table at the top of the sheet. Copy the formula down to cell E60. For each of the expenses in rows 55 to 60, calculate 2021 to 2029 expenses using their respective annual percentage increases displayed in column B Management Fees are calculated as a percentage of Total Base Rent. In row 61, enter a formula to calculate the Management Fee for each year. You negotiate a maintenance contract with the elevator manufacturer for a forced cost of $900 per year for 10 years. Set cell E62 equal to the appropriate cell in the 2020 Operating Expense Assumptions table. Enter a formula in cells F62:N62 to display the annual Elevator Maintenance cost for each year. Updated: 10/30/2017 Office 2016 - Instructions el Project Step Instructions Points Possible 24 Set cell E63 equal to the appropriate cell in the 2020 Operating Expense Assumptions table. Enter a formula in cells F63:N63 to display the annual Security expense using the annual percentage increase displayed in column B. 25 Set cell E64 equal to the appropriate cell in the 2020 Operating Expense Assumptions table. Assume you'll pay a brokerage fee to re-rent or renew all of the residential units every 2 years. The brokerage fee for each unit will be 1-month's rent at the then-prevailing rent. Enter a formula every two years starting in 2022 to calculate the Brokerage Fee for Residential units. Set cell E65 equal to the appropriate cell in the 2020 Operating Expense Assumptions table. Assume you'll pay a brokerage fee to re-rent or renew each commercial unit every 5 years. The brokerage fee for each unit will be a percentage of the total lease base rent of the then-prevailing base rent over the entire lease Enter a formula to calculate the Brokerage Fee for Commercial units. Hint: Commercial unit brokerage fees will only be paid out in Year 1 and Year 6. Set cell E66 equal to the appropriate cell in the 2020 Operating Expense Assumptions table. Enter a formula in cells F66:N66 to display the annual Capital Reserves using the annual percentage increase displayed in column B. 27 Calculate Total Expenses for each year in row 67. Then calculate Net Operating Income for each year in row 69. Net Operating Income is calculated as Effective Gross Rent less Total Expenses. Apply Bold font and format the values as Currency format with no decimals for both rows. 29 Select the 2020 Expense Assumptions in array H3:115 and make a pie chart. Move it to its own sheet named Expense Chart 30 Apply Chart Style 6 to the pie chart. Change the Chart Title to Expense Comparison. Position the Legend on the righthand side. A E F H J K L M N 2020 Rental Assumptions 2020 Operating Expense Assumptions Extra Credit Total RSF by Unit Type #of Units Expense 2020 Taxes $0 $6,100,000 Unit Type 1 Bedroom 2 Bedroom 3 Bedroom 10 45% Rent Month $1,600 $2,200 $2,800 $3,200 $4,400 RSF per Unit 830 995 1,475 1,200 2 ,100 1 Mortgage Finance Purchase Price Down Payment Rate Down Payment Loan Amount Annual Interest Rate Loan Term (years) Monthly Debt Payment Annual Debt Payment 3.125% Large Commercial Total $3,000 $2.400 Insurance Repairs & Maintenance Utilities Legal Accounting Janitorial Cleaning Management Fees Elevator Maint. Security Brokerage Fee - Residential Brokerage Fee - Commercial Capital Reserves 24 Expense Rate Per $1.3767 Residential RSF/year $400 Residential Unit/year $120 Residential Unit / month $0.40 Per Common SF / month $3,000 Year $2,400 Year $150 Day of Cleaning 5% Dollar of Base Rent/year $900 Year $10 Hour Monthly Rent Every 2 Years 5% Total Lease Base Rent $0.50 RSF $900 Number of months / year Residential Rent Increase Commercial Rent Increase Common Area Sq. Footage Vacancy & Collection Loss 12 2% 10% 1200 4.5% Annually Every 5 Years Annually Income Unit # Unit Type 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 Unit 1A Unit 1B Unit 1C Unit 1D Unit 2A Unit 2B Unit 2C Unit 2D Unit 3A Unit 3B Unit 3C Unit 3D Unit 4A Unit 4B Unit 4C Unit 4D Unit SA Unit 5B Unit 5C Unit 5D Unit G1 1 Bedroom 2 Bedroom 2 Bedroom 3 Bedroom 1 Bedroom 2 Bedroom 2 Bedroom 3 Bedroom 1 Bedroom 2 Bedroom 2 Bedroom 3 Bedroom 1 Bedroom 2 Bedroom 2 Bedroom 3 Bedroom 2 Bedroom 2 Bedroom 3 Bedroom 3 Bedroom Large Commercial B Formula Bar F KL M N C Income Unit # Unit Type 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 Unit 1A 1 Bedroom Unit 1B 2 Bedroom Unit 1C 2 Bedroom Unit 1D 3 Bedroom Unit 2A 1 Bedroom Unit 2B 2 Bedroom Unit 2C 2 Bedroom Unit 2D 3 Bedroom Unit 3A 1 Bedroom Unit 3B 2 Bedroom Unit 3C 2 Bedroom Unit 3D 3 Bedroom Unit 4A 1 Bedroom Unit 4B 2 Bedroom Unit 4C 2 Bedroom Unit 4D 3 Bedroom Unit 5A 2 Bedroom Unit 5B 2 Bedroom Unit 5C 3 Bedroom Unit 5D 3 Bedroom Unit G1 Large Commerdal Unit G2 Small Commercial Unit G3 Small Commercial Unit 64 Small Commercial Total Base Rent 2% Percentage Rent Potential Gross Rent Less: Vacancy & Collection Loss Effective Gross Rent Less: Vacancy & Collection Loss Effective Gross Rent Expenses 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 Use Hookup Taxes 3% Insurance 3% Repairs & Maintenance 3% Utilities 3% Legal 3% Accounting 3% Janitorial Cleaning 5% Management Fees 0% Elevator Maint. 3% Security 1 month Brokerage Fee - Residential 5% Brokerage Fee - Commercial 3% Capital Reserves Total Expenses Net Operating Income Total Debt Service Cash Flow After Debt Service * Additional Notes Large Commercial Tenant Sales Projections 520000 530000 550000 545000 548000 580000 595000 610000 625000 640000
