5
6	The top-left section of the spreadsheet is designed for summary statistics for one condition. You will calculate average selling prices and the number of houses sold in each city (the condition). In cell B2, insert the AVERAGEIF function to calculate the average Sold Price for houses in the city of Alpine. Use mixed references for the range; use a relative reference to cell A2. Copy the function and use the Paste Formulas option to paste the function in the range B3:B5 so that the bottom border in cell B5 is preserved.	5
7	You want to count the number of houses in one city. In cell C2, insert the COUNTIF function to count the number of houses in the city of Alpine. Use mixed references for the range; and use a relative reference to cell A2. Copy the function and use the Paste Formulas option to paste the function in the range C3:C5 so that the border in cell C5 is preserved.	5
8	You want to calculate the total commissions for each agent (the condition). In cell B7, insert the SUMIF function to total the commissions by agent. Use mixed references for the ranges; and use a relative reference to cell A7. Copy the function and use the Paste Formulas option to paste the function in the range B8:B9 so that the borders are preserved.	5
9	The top-middle section of the spreadsheet is designed for summary statistics for multiple conditions. You will calculate the number of houses sold for each agent when he or she served as a Dual Agent (DA). Use mixed references for ranges and the agent code condition in cell J3. Use relative cell references to the agent condition in cell E2. When you copy the formulas, use the paste Formulas options to preserve border formatting. In cell F2, insert the COUNTIFS function in cell F2 to count the number of houses sold by the first agent (cell E2) who was a Dual Agent (DA) (J3) for that house. Copy the function to the range F3:F4 and preserve the bottom border for cell F4.	5
10	You are ready to calculate the total value of those houses for each agent when he or she served as a Dual Agent (DA). Use mixed references for ranges and the agent code condition in cell J3. Use relative cell references to the agent condition in cell E2. When you copy the formulas, use the paste Formulas options to preserve border formatting. In cell G2, insert the SUMIFS function to sum the selling prices of the houses sold by the first agent (cell E2) who was a Dual Agent (DA) (J3) for that house. Copy the function to the range G3:G4 and preserve the bottom border for cell G4.	5
11	Now, you will calculate the highest-price house highest-price house sold for each agent when he or she served as a Dual Agent (DA). Use mixed references for ranges and the agent code condition in cell J3. Use relative cell references to the agent condition in cell E2. When you copy the formulas, use the paste Formulas options to preserve border formatting. In cell H2, insert the MAXIFS function in cell H2 to display the highest-price house sold by the first agent (cell E2) who was a Dual Agent (DA) (J3) for that house. Copy the function to the range H3:H4 and preserve the borders in the range H3:H4.	5
12	The Map worksheet contains a list of cities, postal codes, and average house sales. You will insert a map chart to depict the averages visually using the default gradient fill colors. Display the Map worksheet, select the range B1:C5 and insert a map chart.	5
13	Cut the map chart and paste it in cell A7. Set a 2.31" height and 3.62" width.	5
14	You want to enter a meaningful title for the map. Change the map title to Average Selling Price by Zip Code.	2
15	Display the Format Data Series task pane, select the option to display only regions with data, and show all labels. Close the task pane.	3
16	You are ready to start completing the loan amortization table. Display the Loan worksheet. In cell B8, type a reference formula to cell B1. The balance before the first payment is identical to the loan amount. Do not type the value; use the cell reference instead. In cell B9, subtract the principal from the beginning balance on the previous row. Copy the formula to the range B10:B19.	5
17	Now, you will calculate the interest for the first payment. In cell C8, calculate the interest for the first payment using the IPMT function. Copy the function to the range C9:C19.	5
18	Next, you will calculate the principal paid. In cell D8, calculate the principal paid for the first payment using the PPMT function. Copy the function to the range D9:D19.	5
19	Rows 21-23 contain a summary section for cumulative totals after the first year. In cell B22, insert the CUMIPMT function that calculates the cumulative interest after the first year. Use references to cells A8 and A19 for the period arguments.	5
20	The next summary statistic will calculate the principal paid after the first year. In cell B23, insert the CUMPRINC function that calculates the cumulative principal paid after the first year. Use references to cells A8 and A19 for the period arguments.	5
21	Rows 25-28 contain a section for what-if analysis. In cell B27, use the RATE financial function to calculate the periodic rate using $1,400 as the monthly payment (cell B26), the NPER, and loan amount in the original input section.	5
22	In cell B28, calculate the APR by multiplying the monthly rate (cell B27) by 12.	2
23	Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side of each worksheet.	3
24	Save and close Exp19_Excel_Ch07_Cap_RealEstate.xlsx. Exit Excel. Submit the file as directed.	0

Total Points

100

Home Insert Draw Page Layout Formulas View Data = Review = = Share Ev 48 Comments 4 10 AA Custom Insert v x Delete Format v Conditional Formatting Format as Table Cell Styles Arial BIUR x fx A Ideas $ % -28 X Sort & Filter Pastea B2 Find & Select I J K L M N O P 0 1 Number Sold Total # Sold Total Sold Prices Highest Sold Price Average Sold Location Price Alpine American Fork Cedar Hils Lehi Total Commission by Agent Carey Hernandez Dual Agent Carey Hemandez Pjuan Agent Codes and Commission Rates Buyer's Agent 2.75% Dual Agency 5.50% Seller's agent 3.25% Input for Bonus Percent of List Days on Market Bonus Amount 100% Pijuan $ 1.000 Number City Selling Agent Agent Type Agent Type Listing Date Sale Date DA Cedar Hils Lehi American Fork Alpine DA $ DA 68 BABA $ American Fork Alpine Alpine American Fork Lahi American Fork Alpine DA 30 Pjuan Pjuan Pjuan Carey Pjuan Hemandez Pjuan Carey Pjuan Pjuan Pjuan Pjuan Hemandez Carey Carey Hemandez Homandez Carey Carey Hemandez Carey Carey Hemandez Hemandez Carey Carey Carey Pjuan Cedar Hils Cedar Hils Cedar Hils American Fork American Fork Cedar Hils Alpine Lehi List Price Sold Price 725.000 S 705,000 350,799 S 350,ODD 3a5,900 S 385,900 500,000 S 495,800 345,999 S 345,000 325.000 $ 320,000 750.250 S 725,900 645.000 S 643.ODD 425,000 S 415.0DD 325,000 S 330,000 425,815 S 418,000 700,000 S 705,000 365.000 S 363.000 885,500 S 800,ODD 610.000 S 60D,ODD 560,700 S 585,000 450,000 S 450,000 375,000 S 372,500 650.000 S 625.750 545.000 S 560,000 315,750 S 320,ODD 300,000 S 300,000 589,000 S 595,000 345.670 S 345.000 555,000 S 550,000 475,000 S 4B5,000 400,000 S 40D,ODD 380,500 S 365,750 Days on Agent Market Bonus Commission 76 $ 0.06 $ - 54 $ 0.06 5 - 29 $ 0.06 $ 0.03 $ 59 $ 0.03 #VALUE! 48 $ 0.06 $ 76,000 203 $ 0.06 $ 54,000 66 $ 0.06 $ 29.000 73 $ 0.03 $ 60,000 34 5 0.03 $ 59,000 140 $ 0.03 $ 48,000 50 $ 0.06 $ 203,000 48 $ 0.03 $ 66.000 36 $ 0.03 $ 73.000 56 $ 0.03 $ 34,000 29 $ 0.06 $ 140,000 151 $ 0.03 $ 50,000 9 $ 0.03 $ 48.000 25 $ 0.03 $ 36.000 25 $ 0.06 $ 56.000 21 $ 0.03 $ 29,000 92 S 0.06 $ 151,000 20 $ 0.06 $ 9,000 102 $ 0.06 $ 25,000 6 $ 0.03 $ 25,000 86 $ 0.03 $ 21,000 15 $ 0.06 $ 92,000 63 $ 0.03 $ 20.000 4/1/21 6/16/21 4/1/21 5/2521 4/1/21 4/30/21 4/3/21 8/10/21 4/3121 6/1/21 4/12/21 5/30/21 4/12121 11/1/21 4/18/21 6/23/21 4/18/21 6/30/21 4/28/21 6/1/21 4/28/21 9/15/21 4/30/21 6/19/21 5/1/21 6/18/21 5/1/21 5/621 5/1/21 6/26/21 5/1/21 5/30/21 5/2/21 9/30/21 5/6/21 5/15/21 5/15/21 6/9/21 5/22/21 6/16/21 5/28121 6/18/21 6/2/21 9/2/21 6/18/21 7/8/21 6/20/21 9/30/21 6/24/21 6/30/21 6124121 9/18/21 6/30/217 /15/21 6/30/219 /1/21 43 SA $ Lehi 69 Alpine American Fork Cedar Hils Alpine American Fork American Fork BA DA BA 78 80 SA SA $ Details Map Loan + FaceTime Home Insert Draw Page Layout Formulas View Data = Review = = Share Ev 48 Comments 4 10 AA Custom Insert v x Delete Format v Conditional Formatting Format as Table Cell Styles Arial BIUR x fx A Ideas $ % -28 X Sort & Filter Pastea B2 Find & Select I J K L M N O P 0 1 Number Sold Total # Sold Total Sold Prices Highest Sold Price Average Sold Location Price Alpine American Fork Cedar Hils Lehi Total Commission by Agent Carey Hernandez Dual Agent Carey Hemandez Pjuan Agent Codes and Commission Rates Buyer's Agent 2.75% Dual Agency 5.50% Seller's agent 3.25% Input for Bonus Percent of List Days on Market Bonus Amount 100% Pijuan $ 1.000 Number City Selling Agent Agent Type Agent Type Listing Date Sale Date DA Cedar Hils Lehi American Fork Alpine DA $ DA 68 BABA $ American Fork Alpine Alpine American Fork Lahi American Fork Alpine DA 30 Pjuan Pjuan Pjuan Carey Pjuan Hemandez Pjuan Carey Pjuan Pjuan Pjuan Pjuan Hemandez Carey Carey Hemandez Homandez Carey Carey Hemandez Carey Carey Hemandez Hemandez Carey Carey Carey Pjuan Cedar Hils Cedar Hils Cedar Hils American Fork American Fork Cedar Hils Alpine Lehi List Price Sold Price 725.000 S 705,000 350,799 S 350,ODD 3a5,900 S 385,900 500,000 S 495,800 345,999 S 345,000 325.000 $ 320,000 750.250 S 725,900 645.000 S 643.ODD 425,000 S 415.0DD 325,000 S 330,000 425,815 S 418,000 700,000 S 705,000 365.000 S 363.000 885,500 S 800,ODD 610.000 S 60D,ODD 560,700 S 585,000 450,000 S 450,000 375,000 S 372,500 650.000 S 625.750 545.000 S 560,000 315,750 S 320,ODD 300,000 S 300,000 589,000 S 595,000 345.670 S 345.000 555,000 S 550,000 475,000 S 4B5,000 400,000 S 40D,ODD 380,500 S 365,750 Days on Agent Market Bonus Commission 76 $ 0.06 $ - 54 $ 0.06 5 - 29 $ 0.06 $ 0.03 $ 59 $ 0.03 #VALUE! 48 $ 0.06 $ 76,000 203 $ 0.06 $ 54,000 66 $ 0.06 $ 29.000 73 $ 0.03 $ 60,000 34 5 0.03 $ 59,000 140 $ 0.03 $ 48,000 50 $ 0.06 $ 203,000 48 $ 0.03 $ 66.000 36 $ 0.03 $ 73.000 56 $ 0.03 $ 34,000 29 $ 0.06 $ 140,000 151 $ 0.03 $ 50,000 9 $ 0.03 $ 48.000 25 $ 0.03 $ 36.000 25 $ 0.06 $ 56.000 21 $ 0.03 $ 29,000 92 S 0.06 $ 151,000 20 $ 0.06 $ 9,000 102 $ 0.06 $ 25,000 6 $ 0.03 $ 25,000 86 $ 0.03 $ 21,000 15 $ 0.06 $ 92,000 63 $ 0.03 $ 20.000 4/1/21 6/16/21 4/1/21 5/2521 4/1/21 4/30/21 4/3/21 8/10/21 4/3121 6/1/21 4/12/21 5/30/21 4/12121 11/1/21 4/18/21 6/23/21 4/18/21 6/30/21 4/28/21 6/1/21 4/28/21 9/15/21 4/30/21 6/19/21 5/1/21 6/18/21 5/1/21 5/621 5/1/21 6/26/21 5/1/21 5/30/21 5/2/21 9/30/21 5/6/21 5/15/21 5/15/21 6/9/21 5/22/21 6/16/21 5/28121 6/18/21 6/2/21 9/2/21 6/18/21 7/8/21 6/20/21 9/30/21 6/24/21 6/30/21 6124121 9/18/21 6/30/217 /15/21 6/30/219 /1/21 43 SA $ Lehi 69 Alpine American Fork Cedar Hils Alpine American Fork American Fork BA DA BA 78 80 SA SA $ Details Map Loan + FaceTime