Please help answer part a to i:
Attached below are the excel spreadsheets for the data:
Use a lookup function to look up the Development ID from the Sold worksheet, and to determine the actual tax, multiply the tax rate by the sold price, and divide the the Currency Number Format, and display two decimal places: Fl all formula and 1 pon the Excel file02ch03RealEstate. Save your file as 002 cho3 RealEstate_LastFirst, using your last and first name. b. On the Sold worksheet, create a column named Price/SqFt in column N, and then Create a formula to calculate the price per square foot based on of the square foot Create a column named Down PMT. and then create a formula that will calculate a d. Create a column named Days on MKT, and then create a formula to calculate the e. Create a column named Monthly Payment. Create a formula to calculate the monthly mortgage payment for each property. With a mixed reference, use the interest rate found in cell V2. With a mixed reference, use the term in cell W2. Deduct the down payment from the sold price to calculate the amount financed. 1. Create a column named Monthly Taxes. Each development has a different tax rate. functions through the appropriate rows. age and the price sold. down payment of 20% of the sold price. number of days each property was on the market. Edit the function to display positive numbers. determine that development's Tax Rate from the Development worksheet. Then. tax by 12. This will determine the amount to save each month to pay the taxes at the end of the year (Month Tax). To reference a lookup table on a different worksheet, click on the worksheet tab that contains the lookup table, select the range, type a comma (). and then click the original worksheet tab. g. Create a column named Monthly Insurance. The cost to insure homes is different in different neighborhoods. Use a lookup function to look up the Development ID and determine that development's Insurance Rates from the Development worksheet. Then multiply the insurance rate by the sold price, and divide by 12 to determine the monthly payment. h. Create a column named Total Payment. Add the monthly tax and insurance to the monthly payment to determine how much the homeowner needs to live in the newly purchased home. 1. Ensure that your columns are sufficiently wide to display their content fully. Wrap the text in the header rows on each worksheet as necessary. Format the workbook professionally. H. On the Documentation worksheet in cell A8, type today's date. In cell B8, type your name in the Firstname Lastname format. Complete the remainder of the Documentation worksheet according to your instructor's directions. k. Add the file name to the left footer on all sheets. I. Save the workbook, exit Excel, and then submit your file as directed by your instructor N C P Q R S T V G. W Term in Years Rate 4.89% 30 4 - 0202 A B D E F G 1 Agent ID Development ID Listing ID Address City State Postal Code 2 1 3-2373-6 XAU-9473 1181 Eastwind Drive Santa Clara ICA 95051 3 53-2373-6 TCO-2213 242 Chester Court Santa Clara ICA 95051 the 4 79-1902-2 HTO-1151 1191 Topaz Street Montara ICA 94037 .. PLUS 5 109-4841-3 ACO-8981 1135 Italy Circle Courtland CA 95615 Han 6 64-0393-9 CWA-2039 152 Baron Drive Corona --- WR2032 CA 92882 Tu 7 1514-0393-9 ACO-2534 388 Gibstay Circle Corona CA 92882 stay - - 8 74-0393-9 EBR-9341 874 Robert Circle Corona CA 92882 9 The - 143-2373-6 Tu NNE-2643 P07 320 Freebird Circle Santa Clara CA 95051 P. 10 8|4-0393-9 SLO-8692 571 Scenic Way Corona CA 92882 - 11 44-8619-4 Runway AKI-8296 1197 Paradise Lane King City CA 93930 A - 12 12 9-7232-3 TEA-5262 1091 Paso Place South Lake Tahoe CA 96151 - 13 11/3-2373-6 APA-3638 206 Chapel Circle Santa Clara CA 95051 Sun - 14 139-1902-2 YHA-2586 916 Verdant Circle Montara CA 94037 fu B 15 10 4-8619-4 TMA-4565 240 Gregory Road e King City CA sy 193930 16 1414-8619-4 APA-7283 749 13th Avenue King City CA 193930 F 17 f 74-0946-0 AMA-4787 2704 267 Juanita Spur Corona CA 192882 mu Her T 18 10 4-8619-4 XGA-3142 2192 41 Barbara Drive King City CA 193930 19 T4 1 9-1902-2 LHU-1527 1096 Marino Drive Montara CA 94037 20 1314-8619-4 He ZTE-2664 419 Illian Lane King City CA 193930 an sy 21 74-8619-4 NFO-3821 231 Ella Circle f CA King City 193930 22 1114-0393-9 P. AMA-5636 985 Delak's Way Corona CA 92882 F 23 Podstaway 54-0393-9 ZME-9214 1109 Ronny Drive Corona - CA - nove 92882 919-1902-2 tu 24 ACA-2477 2004 1116 Hearthstone Drive Montara 94037 me ca 25 1 4-8619-4 HLO-6086 wy 804 Mc Lure Circle King City 193930 26 63-2373-6 LPA-2897 1165 Atkinson Drive Santa Clara CA 95051 27 12/9-1902-2 AMO-2576 726 Bowlby Street Montara 194037 28 1/3-2373-6 2372.6 486 Burlwood Drive CERCA CA Santa Clara 95051 ato 2009 29 13/9-1902-2 HAK-4499 205 20 Montara 396 Gardner Street 20 194037 30 tocan 64-8619-4 CYO-1637 22 1193 Trail Drive King City 93930 secto 31 12 4-8619-4 tou see DCH-1651 47 Ike Walton Road CA CA King City 93930 44-8619-4 A 32 ADU-1262 CA 870 Sagwon Avenue King City CA 93930 33 194 14-0393-9 YLO-5588 124 Fire House Lane Corona CA 92882 24 34 139-1902-2 ATR-3135 368 Hill Road Montara CA a 94037 35 33-2373-6 KSC-7661 507 Nordic Street Santa Clara CA 36 37 38 39 10 Sold + . J Listing Date Sold Date Square Feet 2018-09-23 2018-12-23 2017-09-25 2018-08-26 2017-05-23 2017-06-29 w 2018-07-28 2018-11-14 2016-08-19 2017-03-23 2018-03-02 2018-08-17 2017-11-13 2018-05-24 ma 2018-10-15 2018-11-07 2018-05-11 2018-11-11 142 - 2017-07-15 2018-04-12 40 40 2018-10-17 2018-11-25 --- 2017-05-01 2018-04-01 2018-07-06 2019-01-30 2018-01-03 2018-12-03 .. 2018-01-20 2018-12-24 --- --- 2017-04-09 2018-07-01 -- 2018-01-24 2019-01-14 12 2017-11-18 2018-07-21 2018-04-25 2019-01-07 2018-01-21 2018-03-09 - 2018-11-16 2019-01-26 --- 2017-10-09 2018-03-06 2018-05-13 2018-08-11 2018-11-24 2019-04-01 2018-08-03 2019-11-02 2017-01-13 2018-02-16 2018-01-10 2019-01-18 2010 20 2018-07-17] 2018-08-08 2010 2009 2010 2018-02-16 2018-10-19 2010 041040992 2018-04-12 2018-11-22 2018-01-19 2018-08-23 2018-12-24 2019-05-07 2017-06-10 2018-01-23 2018-07-23 2019-03-05 K Listing Agent 1042 1072 1083 1086 1127 1139 1200 100 1223 1245 1296 1303 1314 1329 1242 1342 - 1371 - 1382 1385 e 1386 1413 ** 1741 --- 1794 1893 1 1979 1989 120 2089 w 2345 2489 ara 2523 2520 2539 2570 2579 2723 2975 2987 2999 M List Price Sold Price 1 145,880.00 123,998.00 5 150,080.00 127,568.00 7 173,280.00 155,952.001 122222. 10 206,340.00 193,959.60 ed 6 225,400.00 202,860.00 15 227,800.00 205,020.00 7 240,000.00 216,000.00 ed mies 14 171,220.00 145,537.00 8 249,000.00 224,100.00 12.c 14 194,400.00 180,792.00 * . 12 260,600.00 252,782.00 es See 11 183,960.00 156,366.00 were 13 212,640.00 191,376.00 - - 10 201,300.00 187,209.00 ed - 14 205,650.00 191,254.50 w on 7 241,850.00 222,502.00 10 207,750.00 193,207.50 11 --- 1 221,760.00 199,584.00 www.cu 122.20. 13 211,950.00 197,113.50 7 261,150.00 242,869.50 ed See 11 358,800.00 322,920.00 5 378,600.00 340,740.00 9 316,640.00 284,976.00 10 1 298,350.00 277,465.50 - 6 292,460.00 248,591.00 12 375,200.00 337,680.00 220 AC 1 348,460.00 206 1010 296,191.00 13 403,680.00 363,312.00 c 2100 5 6 61 380,850.00 354,190.50 12 386,850.00 359,770.50 250 220 50 4 408,450.00 379,858.50 1 595,000.00 535,500.00 13 477,920.00 430,128.00 3 419,860.00 356,881.00 1027 95051 Development Documentation D A B 1 Development ID Development Name Pool 2 2-1021-5 Orange Estates TRUE 3 2-3901-9 Sedgebrook Greens TRUE 4 3-2373-6 King's Forest FALSE 5 4-0393-9 Sedgebrook Plantation TRUE 6 4-0946-0 River Run FALSE 7 4-8619-4 St. James' Manor TRUE 85-2752-3 Pacific Pleasures TRUE 98-0936-1 Fieldstone Estates TRUE 10 9-1902-2 Fairway Forest FALSE 11 9-4841-3 Twin Lakes FALSE 12 9-7232-3 Orchard Manor FALSE 13 14 15 Playground TRUE TRUE FALSE FALSE TRUE TRUE TRUE TRUE FALSE TRUE TRUE E Bike Trail TRUE TRUE FALSE TRUE FALSE FALSE TRUE TRUE FALSE TRUE TRUE F Dog Park TRUE TRUE TRUE FALSE TRUE TRUE FALSE TRUE TRUE FALSE FALSE G H HOA Dues Tax Rates/$1K Insurance Rates/$1K 2,000.00 0.0235 0.011 1,200.00 0.015 0.021 100.00 0.01 0.025 400.00 0.0195 0.0195 400.00 0.02 0.0175 600.00 0.02 0.015 1,000.00 0.021 0.015 2,400.00 0.025 0.01 200.00 0.011 0.0235 600.00 0.015 0.02 800.00 0.0175 0.02 16 17 18 19 20 21 22 23 24 Sold Development Documentation D E B 1 Schalow Real Estate 3 4 5 Create Date By Whom 6 2018-12-18 Dawn Schalow 7 Mod. Date By Whom 8 9 Description Workbook Name Real Estate Last Version Backup Name Mod. Description Purpose 10 11 12 13 14 15 16 17 18 19 Create Date Sheet Name 20 2018-12-18 Sold 21 2018-12-18 Development 22 23 24 25 26 27 28 29 30 Sold Development Creator Dawn Schalow Dawn Schalow Documentation
