Only steps 21-28 need to figure out formules for each cell in those steps using the excel spreadsheet

Complete steps 21-28 to fill put the cells that are highlighted in grey on the third image, using the excel spreadsheet

21 Set cell E55 equal to the Year 1 Insurance expense in the 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 Year 2 to Year 10 expenses using their respective annual percentage increases displayed in column B 3 22 3 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 using the annual percentage fee displayed in column B. You negotiate a maintenance contract with the elevator manufacturer for a fixed cost of $900 per year for 10 years. Set cell E62 equal to the appropriate cell in the Operating Expense Assumptions table. Enter a formula in cells F62:N62 to display the annual Elevator Maintenance cost for each year, 23 2 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. 2 Updated: 10/30/2020 Suructions Step 25 Excel Project Instructions Points Possibl Set cell E64 equal to the appropriate cell in the Operating Expense Assumptions table. Assume you'll pay a brokerage fee to either rent out or renew all of the residential units every 2 years. The brokerage fee for each unit will be 1-month of that year's rental rate. Enter a formula every two years starting in Year 3 to calculate the Brokerage Fee for Residential units. Set cell E65 equal to the appropriate cell in the 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 rent over the entire 5 year lease period. 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 Operating Expense Assumptions table. Enter a formula in cells F66:N66 to display the annual Capital Reserves using the annual percentage increase 2 displayed in column B. 26 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. N 2 28 2022. Vear Bantal Assumption Extra Ore Rent / Belts Un type 11 Ser Unit Toby UT More $1.100 2.300 O 2032 AM trenie ENE 2013 Bate Per FAST 133 Sc $400 MM A Mart 314 5.20 1.2 500 Ft SC Pach 10 bores 1 LAM S. 38.00 500 RES 9.00 TO 1 60 2.100 100 NE GELTE ons he Hube Swy IND TE Mem NEW 2 os 1200 gereed 52 5200 Metery 5 Income 2022 NOC 102 2012 2011 20 2017 BOTE 22.44 1 2 he 2015 2010 SER SEX WE WE IN W CHE ONE M NEW 18 WOT 2017 MO 21,1 TE 2 7711 WEW w 20 2X 2004 2 3 MOUR 274 10 MIN 30 OSTE ON COM SOSE MIN 22 VE 200 HA 23.01 'SE TOT HIM ME 2. TOT 938 2013 FEDE 14 234 21,71 SO LEER W 2018 235 MIN 2013 IN MAIN trix 23 7744 ZA YOR S EL W DER 2014 1. 21 2014 M WS ON OVE mo See WE WE OH 2018 UN EN MIN TER TELE 023 MN ta WE CHE BEN DOS GI 200 WW ws 20 M M400 M 43 741, 57111 11 OVH MAN 73114$ 73,5 Torta $ 110,00 15,630 SMP SET 5 1 OR DOO 1000 Percentageant Potential Grow 10000 13000 TILA, TOO 1600 HLAS S 7920 12000 B2330 SES 12.08 $70,000 S4M 33,331 $ SITE 11.932 HSS 14 19,2 S S PUNO 708,44 $ LOVE TEE 1 B Expenses 2014 2033 2026 1.450 20 25 2240 2016 2N 2914 HAR SCE OSW WH OOSTE LAR | CHEME CE w OCEX QUE VER WEGE WS cores USD 3 Bed Unit U Un 63 UN 4 Total Base Rent HAN OCET ONE 40 M400 40 721.2485 . MUN 195 BA 19 6,13 150 . 0.100 020 0.30 4220 240 0.140 30 745.60 $ 107,847 5820,108 5.313.018 31.03 2.000 400 2.400 1.400 200 400 743.59 $ 755,111 5 7616535 OREO OVEC M400 1.00 400 732,1135 CO w M60 $710.400$ Percentage Rent Potential Gross Rent 10400 $ 720,000 $ 105.00 731.345 11000 741,3135 $ 10900 1096 11600 754,49 5764,091 575,255 11900 12000 12000 12000 107,530 $ 119.847 $ 112,100 $ 145,014 la Vary ective Gross Rent $ WEES 5 69.915 5 11.4495 709,145 33.9525 34,4755286 720,567 5711,598 5 158.4675 36,11 M6,893 W454 S 100M 771,192 $ 22.954 SSA 50,912 $1645 Expenses 20222 2023 2024 2005 2026 2037 2008 2009 2030 2011 Tawe Acting Clean rum FM are feet gelee.Com Reserves Total Expen Net Operating income 52510 Total Mortgage Payment Residual Cash Flow * Additional Notes Large Commercial Tenant Sok Projections Suso 530000 000WTS 5500 595000 600000 610000 625000 520000 21 Set cell E55 equal to the Year 1 Insurance expense in the 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 Year 2 to Year 10 expenses using their respective annual percentage increases displayed in column B. 3 22 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 using the annual percentage fee displayed in column B. 3 23 You negotiate a maintenance contract with the elevator manufacturer for a fixed cost of $900 per year for 10 years. Set cell E62 equal to the appropriate cell in the Operating Expense Assumptions table. Enter a formula in cells F62:N62 to display the annual Elevator Maintenance cost for each year. 2 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. 2 2 Updated: 10/30/2020 SU Uccions Step Excel Prole Points Possibl 25 Instructions Set cell E64 equal to the appropriate cell in the Operating Expense Assumptions table. Assume you'll pay a brokerage fee to either rent out or renew all of the residential units every 2 years. The brokerage fee for each unit will be 1-month of that year's rental rate. Enter a formula every two years starting in Year 3 to calculate the Brokerage Fee for Residential units. Set cell E65 equal to the appropriate cell in the 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 rent over the entire 5 year lease period. 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 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. 26 a 27 2 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. 2 28 Draw Page Layout Formulas Data Review View Tell me 2 Wrap Text alibri Insert 14 AA = General O Delete AA lille Merge & Center 6 % - $ *18-28 Format Conditional Format Cell Formatting as Table Styles Styles Font Allman Number Calls fx Expenses F M N OWO UNGA Total Base Rent 18.400 5710,400 18.400 $721,243 38.400 $732,311 38.400 $743,599 38,400 $755,111 19,240 $783,653 12,240 $795,630 12.40 $807,147 $820,300 12.140 $833,010 Percentage Rent Potential Grous Rent 10400 $720,800 10600 $731,848 11000 $743,313 10900 $754,499 10960 $766,071 11600 $795,253 11900 S807,500 12000 $819.847 12000 5832,208 12000 $845,011 Las Vegas Effective Grossent $32.435 $688,364 $32,933 $698,915 $33.449 5709,854 $33,952 5720,547 534,473 5731.598 $35,786 5759,467 $36,339 $771.192 $26,893 $782,950 $37,454 $734,854 $38,020 $806,99 $ Expenses 2022 2023 2024 2025 2026 2027 2021 2029 2019 2011 TA or 50,000 Repair & Mama 512) $17,710 LOCSIS 58.467 $11,277 54111 14,141 59.312 $17.94 SAT 14.76 56433 14 $6.000 $14.000 55.00 14,000 $1.200 SI5.000 $35.520 5900 res GIOVS thun ETS 5.142 $15.715 1.294 $4,371 SHAV? $1702 337.17 500 SALAMAT $144 SAN 14120 $1.29 SLOG S. SCO L.12 Cov 9.224 $14 SA SAT S010 $18.00 $12.67 9900 SOAS. 15 51041 SI $931 $5.21 5411 5303 HIAS $10.11 SI241 $7.27 $5.00 $18, 1410140 SCO S. 541.00 $16.550 SMS 65 100 $15.00 $17.30 387.755 SEA 51.781,52 HISENS 0006 00 006 06 SOWE $2.5 tollen Management olevator Man Senty Groepere renfee Commercial Captive Total Expenses SATA2,46 54500 132651 545,00 $216.1 545,200 545.200 $42.000 $14.7.12 $49.000 $16.1250 $14.377 SISSA OSTS 991153 $15.19 $16.60527 517.107.55 $17.620.77 S181491 Net Operating Income 149-667 Total MorgoPayment Residual Cash Flow $172,464 VALUTI $172,464 15172464) $172,464 172,464) $172,464 $172.464 15172,484) 5372.464) $172,464 15172464) $172.464 172.454) 5172,464 15172464) $172,464 5172,464 5172,464) 172464 Additional Notes ge Commercial Tenant Safes Projections 520000 0000ES S50000 S45000 548000 580000 S95000 610000 625000 640000 Taxcas Average: 18417.22659 Count: 135 Sum 2154815.511 H M ental Assumptions 2022 Operating Expense Assumptions of Units room room Rent/Month $1,600 $2.200 $2.000 $3.200 54.400 $14.200 Total RSF by Unit Type 3,320 9,950 BURSO RSF per Unit 830 995 1,475 1.200 2.100 10 6 3 1 24 ercial 3.500 2.100 27.820 Total Expense Tanes insurance Repair & Maintenance Utilities Legal Accounting Antral Ceaning Management Fees Elevator Maint Security Brokerage fee. Residential Brokerage Fee-Commercial Capital Reserves 2022 Expense Rate Per $30,453 $1.3767 Residential SF / year 58.000 5400 Residential Unt/year $14400 5120 1/2 Residential Units/month $5,760 50,40 Per Common SF /month 54,000 54.000 Year $3,200 $3,200 Year $15.600 $150 Day of Cleaning $35.520 SN Dollar of Base Rent/year $900 5900 Year $59:04 $12 Hour 545200 Monthly Rent Every 2 Year 542.000 5% Total Lease Base Rent 12010 $0.50 RSF Number of months/year Residential Rent increase Commercial Rent Increase Common Area 54. Footage Vacancy & Collection Loss 12 2x 1ON 1200 4.5N Annual Every 5 Year SF Annual Income Unit Type 2022 2023 2024 2925 2026 2022 2928 2022 203 2 3 Unit 1A Unt 10 Unit C Unt 10 Unit 2A Unit 28 Unit 2c Unt20 Unit A Unt38 Unit 3C Unit 3D UNA Unit 40 Unit 4C Unit 4D USA Unit 50 USC Unit 50 Unit 1 Unit 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 www 19.00 20,400 2.400 500 19.200 26,400 26.000 33.000 19.200 2.400 26.400 1600 19.200 26.400 26.400 1600 26.000 26.000 1000 1600 52.800 19,584 26,928 26,928 34.272 19,504 26,928 26,928 34,272 19.54 26,928 26,928 34.272 19.584 26,928 26,921 34.222 26,921 26.920 14,272 34.222 52.000 BRADO 19,976 27A67 27462 34,957 19,976 27,467 27462 34,957 19,376 27457 27457 34,957 19,976 27ASY 27A67 34,957 27462 27462 34.97 34.957 52.800 BRADO 20375 28.016 28,016 35,657 20,375 28.016 28.016 35,657 20,375 28.016 28,016 35.657 20,375 28.016 28.016 35.657 28.016 28.016 35.657 35.657 52.800 3400 20,783 28,576 28.576 36.370 20,78 28.576 28.576 36.370 20.78) 28.576 28.576 16.30 20,783 28.576 28.576 16.370 28.576 28.576 16.370 16.370 52,800 TRADO 21,199 29,148 29,148 37,097 21.190 29,148 29,140 37,097 21.190 29,140 29,148 37,097 21,190 29,140 29.140 37,097 29.140 29.140 37,092 37,097 58,080 1140 21.422 29,731 29,731 37839 21.622 29731 29,731 37,339 21.422 29,731 29,731 37139 21.422 29,731 29.731 37839 29,731 29,231 37219 37839 58080 4) 22.055 30,325 30,325 38.96 22.095 30,325 30,325 38.596 22.055 30325 30.325 30.50 22.0SS 30.125 30.325 3.56 30.125 10.325 38.1 38.56 58.00 A110 2749 SCE OS 2 3 Bedroom Dedim 11 Unit Unit 61 CUVE NW OCEX QUE OOSTI CUM ce TAR VER WE VE WS DO ore M400 2016 214 17.09 MAS 31.03 2.300 100 OM M400 M.400 200 1.400 743.59 $ 755,111 $ 71,6535 21. MEN 195 60115 10 0.100 20 0.340 4.2.200 40240 140 2.0 745.60 $ 107,847 $ 820,108 5811018 orto OPCOR WED OPEDY NEO U 40 MO 400 721.2485 Un 63 Un 64 Total Basement 00 M $710.400$ 400 72,1135 to Percentage Rent Potential Grous Rent $ES 10400 $ 720,000 10600 731,145 11000 743,313 5 $ 10000 1096 11600 754,499 $ 76,0915 795,25) 12000 12000 12000 109.530 $ 119,847 $ 112,100 $ 145,018 www hective Gross Rent $32.45 $ 6,145 12.933 5 69.915 5 11.495 709,145 33525 34,4735 586 720.567 731,598 578.4675 36,379 693 17454 S 18.036 771,199 $ 780,954 $74,854 $0.992 Expenses 2023 2024 2005 2026 2097 2020 2009 2030 2011 2022 GAME Tas RM Acting Manaf FM SY margele Reserves Total Espen Net Operating income SP510 Total Mortgage Payment Residual Cash Flow Aditional Notes Lampe Commercial Tenant Sol Projection COOTS 000055 Seso 595000 25000 000 610000 520000