Real Estate Workbook - You are interning at Schalow Real Estate firm. Your supervisor has asked you to cleanse the data in a spreadsheet so that she can analyze it and look for trends. She asks that you create new columns as directed. Format all currency spreadsheet values using the Currency Number Format, and display two decimal places. Fill all formula and functions through the appropriate rows.a.
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-age and the price sold.
Create a column named Down PMT, and then create a formula that will calculate a down payment of 20% of the sold price.
Create a column named Days on MKT, and then create a formula to calculate the number of days each property was on the market.
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. Edit the function to display positive numbers.
Create a column named Monthly Taxes. Each development has a different tax rate. Use a lookup function to look up the Development ID from the Sold worksheet, and determine that developments Tax Rate from the Development worksheet. Then, to determine the actual tax, multiply the tax rate by the sold price, and divide the 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.
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 developments Insurance Rates from the Development worksheet. Then multiply the insurance rate by the sold price, and divide by 12 to determine the monthly payment.
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.
Rate Term in Year 4.8996 30 City IState Postal Code Listing Date Sold Date Square Feet Listing Agent List Price Sold Price Price/SqFt Down PMT Days on MKT Monthly Payment Monthly Taxes Monthly Insurance Total Payment Santa Clara CA 95051 9/23/2021 12/23/2021 1042 1 145,880.00 123,998.00 $119.00 90 Santa Clara CA 95051 9/25/2020 8/26/2021 1072 5 150,080.00 127,568.00 $119.00 331 Montara CA 94037 5/23/2020 6/29/2020 1083 7 173,280.00 155,952.00 $144.00 36 Courtland CA 95615 7/28/2021 11/14/2021 1086 10 206,340.00 193,959.60 $178.60 106 Corona CA 92882 8/19/2019 3/23/2020 1127 6 225,400.00 202,860.00 $180.00 214 Corona CA 92882 3/2/2021 8/17/2021 1139 15 227,800.00 205,020.00 $180.00 165 Corona CA 92882 11/13/2020 5/24/2021 1200 7 240,000.00 216,000.00 $180.00 191 Santa Clara CA 95051 10/15/2021 11/7/2021 1223 14 171,220.00 145,537.00 $119.00 22 Corona CA 92882 5/11/2021 11/11/2021 1245 8 249,000.00 224,100.00 $180.00 180 King City CA 93930 7/15/2020 4/12/2021 1296 4 194,400.00 180,792.00 $139.50 267 South Lake Tahoe CA 96151 10/17/2021 11/25/2021 1303 12 260,600.00 252,782.00 $194.00 38 Santa Clara CA 95051 5/1/2020 4/1/2021 1314 11 183,960.00 156,366.00 $119.00 330 Montara CA 94037 7/6/2021 1/30/2022 1329 13 212,640.00 191,376.00 $144.00 204 King City CA 93930 1/3/2021 12/3/2021 1342 10 201,300.00 187,209.00 $139.50 330 King City CA 93930 1/20/2021 12/24/2021 1371 14 205,650.00 191,254.50 $139.50 334 Corona CA 92882 4/9/2020 7/1/2021 1382 7 241,850.00 222,502.00 442 King City CA 93930 1/24/2021 1/14/2022 1385 10 207,750.00 193,207.50 $139.50 350 Montara CA 94037 11/18/2020 7/21/2021 1386 1 221,760.00 199,584.00 $144.00 243 King City CA 93930 4/25/2021 1/7/2022 1413 13 211,950.00 197,113.50 $139.50 252 King City CA 93930 1/21/2021 3/9/2021 1741 7 261,150.00 242,869.50 $139.50 48 Corona CA 92882 11/16/2021 1/26/2022 1794 11 358,800.00 322,920.00 $180.00 70 Corona CA 92882 10/9/2020 3/6/2021 1893 5 378,600.00 340,740.00 $180.00 147 $161.00 Rate Term in Year 4.8996 30 City IState Postal Code Listing Date Sold Date Square Feet Listing Agent List Price Sold Price Price/SqFt Down PMT Days on MKT Monthly Payment Monthly Taxes Monthly Insurance Total Payment Santa Clara CA 95051 9/23/2021 12/23/2021 1042 1 145,880.00 123,998.00 $119.00 90 Santa Clara CA 95051 9/25/2020 8/26/2021 1072 5 150,080.00 127,568.00 $119.00 331 Montara CA 94037 5/23/2020 6/29/2020 1083 7 173,280.00 155,952.00 $144.00 36 Courtland CA 95615 7/28/2021 11/14/2021 1086 10 206,340.00 193,959.60 $178.60 106 Corona CA 92882 8/19/2019 3/23/2020 1127 6 225,400.00 202,860.00 $180.00 214 Corona CA 92882 3/2/2021 8/17/2021 1139 15 227,800.00 205,020.00 $180.00 165 Corona CA 92882 11/13/2020 5/24/2021 1200 7 240,000.00 216,000.00 $180.00 191 Santa Clara CA 95051 10/15/2021 11/7/2021 1223 14 171,220.00 145,537.00 $119.00 22 Corona CA 92882 5/11/2021 11/11/2021 1245 8 249,000.00 224,100.00 $180.00 180 King City CA 93930 7/15/2020 4/12/2021 1296 4 194,400.00 180,792.00 $139.50 267 South Lake Tahoe CA 96151 10/17/2021 11/25/2021 1303 12 260,600.00 252,782.00 $194.00 38 Santa Clara CA 95051 5/1/2020 4/1/2021 1314 11 183,960.00 156,366.00 $119.00 330 Montara CA 94037 7/6/2021 1/30/2022 1329 13 212,640.00 191,376.00 $144.00 204 King City CA 93930 1/3/2021 12/3/2021 1342 10 201,300.00 187,209.00 $139.50 330 King City CA 93930 1/20/2021 12/24/2021 1371 14 205,650.00 191,254.50 $139.50 334 Corona CA 92882 4/9/2020 7/1/2021 1382 7 241,850.00 222,502.00 442 King City CA 93930 1/24/2021 1/14/2022 1385 10 207,750.00 193,207.50 $139.50 350 Montara CA 94037 11/18/2020 7/21/2021 1386 1 221,760.00 199,584.00 $144.00 243 King City CA 93930 4/25/2021 1/7/2022 1413 13 211,950.00 197,113.50 $139.50 252 King City CA 93930 1/21/2021 3/9/2021 1741 7 261,150.00 242,869.50 $139.50 48 Corona CA 92882 11/16/2021 1/26/2022 1794 11 358,800.00 322,920.00 $180.00 70 Corona CA 92882 10/9/2020 3/6/2021 1893 5 378,600.00 340,740.00 $180.00 147 $161.00
