USA: +1(669)289-8683 
Sign in
Date of Purchase Agent3/27/2022 By Owner
9/15/2021 By Owner
7/14/2021 By Owner
1/13/2022 By Owner
7/14/2021 Century 21
3/26/2022 Century 21
9/15/2021 Century 21
3/26/2022 Century 21
3/26/2022 Century 21
3/26/2022 Century 21
9/13/2021 Century 21
4/18/2021 Century 21
9/13/2021 Williman’s
3/26/2022 Williman’s
9/15/2021 Williman’s
9/15/2021 Williman’s
4/18/2021 Remax
9/15/2021 Remax
9/15/2021 Remax
3/27/2022 Remax
3/26/2022 Remax
3/26/2022 Remax
9/15/2021 Remax
3/3/2022 Remax
7/14/2021 By Owner
9/13/2021 By Owner
3/3/2022 By Owner
7/14/2021 By Owner
4/18/2021 By Owner
9/13/2021 By Owner
9/15/2021 Century 21
3/27/2022 Century 21
3/26/2022 Century 21
7/14/2021 Century 21
1/13/2022 Williman’s
1/13/2022 Williman’s
9/13/2021 Williman’s
1/13/2022 Homes and Garden
3/3/2022 Homes and Garden
9/15/2021 Homes and Garden
4/18/2021 Homes and Garden
3/26/2022 Homes and Garden
4/18/2021 Homes and Garden
1/13/2022 Homes and Garden
9/15/2021 Remax
9/15/2021 Remax
9/13/2021 Remax
4/18/2021 Remax
3/3/2022 Remax
7/14/2021 Remax
3/26/2022 Remax
Buyers
House Type Bedrooms Bathrooms Fireplace
Bond
Contemporary
4
1 No
Scott
Contemporary
2
2 Yes
Johnson
Contemporary
4
2 Yes
Nosmiles
Contemporary
3
1 No
Grande
Contemporary
5
1 No
Hairless
Contemporary
1
2 Yes
Muchley
Contemporary
5
2 Yes
Holland
Contemporary
1
2 Yes
Sanchaz
Contemporary
2
1 Yes
Watkins
Contemporary
3
1 Yes
Thatcher
Contemporary
1
1 Yes
Cater
Contemporary
5
1 Yes
Roger
Contemporary
5
2 No
Slocum
Contemporary
3
2 Yes
Melon
Contemporary
3
2 No
Ace
Contemporary
3
2 No
Smith
Contemporary
1
2 Yes
Jackson
Contemporary
4
1 Yes
Newman
Contemporary
5
1 No
Nicks
Contemporary
3
2 No
Wilson
Contemporary
3
2 Yes
Nettle
Contemporary
1
2 No
Frost
Contemporary
1
1 Yes
O’Neil
Contemporary
4
1 Yes
Hawkins
Condominium
5
1 Yes
Chipman
Condominium
4
2 No
Zimmerman Condominium
5
1 Yes
Downing
Condominium
3
2 Yes
Bryant
Condominium
3
1 No
Jacobs
Condominium
3
1 No
Grande
Condominium
4
2 No
Hairless
Condominium
3
1 Yes
Muchley
Condominium
2
2 No
Holland
Condominium
5
1 Yes
Sanchaz
Condominium
2
1 Yes
Watkins
Condominium
5
1 Yes
Thatcher
Condominium
1
1 Yes
Cater
Condominium
2
1 Yes
Roger
Condominium
1
2 Yes
Slocum
Condominium
2
2 Yes
Melon
Condominium
4
1 No
Ace
Condominium
5
1 Yes
Perry
Condominium
4
1 Yes
Smith
Condominium
1
2 No
Smith
Condominium
1
2 No
Jackson
Condominium
1
2 No
Newman
Condominium
2
2 No
Nicks
Condominium
4
1 No
Wilson
Condominium
1
2 Yes
Nettle
Condominium
1
1 No
Frost
Condominium
2
2 Yes
3/26/2022 Remax
7/14/2021 Remax
7/14/2021 Remax
3/27/2022 By Owner
3/27/2022 By Owner
3/3/2022 By Owner
3/26/2022 By Owner
3/26/2022 By Owner
9/13/2021 By Owner
4/18/2021 By Owner
4/18/2021 By Owner
3/27/2022 By Owner
3/27/2022 By Owner
3/3/2022 By Owner
3/3/2022 By Owner
3/26/2022 By Owner
1/13/2022 By Owner
9/13/2021 Century 21
7/14/2021 Century 21
9/13/2021 Century 21
3/3/2022 Century 21
3/27/2022 Century 21
9/15/2021 Century 21
7/14/2021 Williman’s
3/3/2022 Williman’s
7/14/2021 Williman’s
3/26/2022 Williman’s
9/13/2021 Williman’s
1/13/2022 Williman’s
3/27/2022 Williman’s
1/13/2022 Williman’s
1/13/2022 Williman’s
3/3/2022 Williman’s
3/3/2022 Homes and Garden
3/26/2022 Homes and Garden
4/18/2021 Homes and Garden
4/18/2021 Homes and Garden
3/3/2022 Homes and Garden
3/26/2022 Homes and Garden
7/14/2021 Homes and Garden
9/15/2021 Homes and Garden
9/13/2021 Homes and Garden
3/27/2022 Homes and Garden
3/27/2022 Remax
1/13/2022 Remax
3/26/2022 Remax
7/14/2021 Remax
4/18/2021 Remax
7/14/2021 By Owner
3/27/2022 By Owner
4/18/2021 By Owner
3/27/2022 By Owner
O’Neil
Condominium
Hawkins
Condominium
Na
Condominium
Williams
Ranch
Pell
Ranch
Jones
Ranch
Jones
Ranch
Lee
Ranch
Nagato
Ranch
Nagasaki
Ranch
Lonsdale
Ranch
Negreedy
Ranch
Chama
Ranch
Smith
Ranch
Castle
Ranch
Willows
Ranch
Bond
Ranch
Scott
Ranch
Johnson
Ranch
Cowoart
Ranch
Greenston Ranch
King
Ranch
Tinker
Ranch
Zink
Ranch
Weston
Ranch
Tinkleman Ranch
Kenney
Ranch
Childs
Ranch
Nelson
Ranch
Matherson Ranch
Timmerman Ranch
Pippen
Ranch
Bryant
Ranch
Smythe
Ranch
Tithers
Ranch
Harris
Ranch
Bergquist
Ranch
Smithers
Ranch
Pod
Ranch
Sikes
Ranch
Grant
Ranch
Smith
Ranch
Smathers
Ranch
Bryan
Ranch
Able
Ranch
Kinnon
Ranch
Collarserdo Ranch
Bell
Ranch
Snipes
Colonial
Johnson
Colonial
Jackson
Colonial
Nyne
Colonial
2
5
3
4
1
1
4
1
5
5
2
1
4
1
4
5
2
3
5
3
4
5
4
5
1
5
1
5
2
3
3
5
3
2
2
5
3
2
5
5
5
3
2
5
2
1
3
5
3
5
1
2
1 Yes
2 Yes
1 Yes
2 No
2 No
1 No
1 Yes
2 Yes
1 No
2 Yes
2 Yes
1 No
2 No
2 Yes
1 Yes
1 No
2 No
2 Yes
1 No
1 Yes
1 Yes
1 Yes
2 No
1 No
1 Yes
2 Yes
2 No
1 No
2 No
1 No
1 Yes
2 No
2 Yes
2 Yes
1 Yes
2 Yes
1 Yes
1 No
1 Yes
2 Yes
2 No
1 Yes
1 No
2 No
1 No
1 Yes
1 Yes
2 Yes
2 Yes
1 No
2 No
2 Yes
3/26/2022 Century 21
4/18/2021 Century 21
3/26/2022 Century 21
3/3/2022 Century 21
4/18/2021 Century 21
9/13/2021 Century 21
3/26/2022 Century 21
3/26/2022 Century 21
4/18/2021 Century 21
3/3/2022 Century 21
4/18/2021 Williman’s
3/3/2022 Williman’s
7/14/2021 Williman’s
7/14/2021 Williman’s
9/15/2021 Williman’s
7/14/2021 Williman’s
3/3/2022 Williman’s
9/15/2021 Williman’s
4/18/2021 Williman’s
9/13/2021 Williman’s
4/18/2021 Williman’s
4/18/2021 Homes and Garden
4/18/2021 Homes and Garden
7/14/2021 Homes and Garden
9/15/2021 Homes and Garden
3/26/2022 Homes and Garden
9/15/2021 Homes and Garden
3/27/2022 Homes and Garden
3/26/2022 Homes and Garden
3/3/2022 Homes and Garden
3/27/2022 Remax
3/3/2022 Remax
9/13/2021 Remax
4/18/2021 Remax
3/3/2022 Remax
Charleson Colonial
Taylor
Colonial
Smith
Colonial
Bennett
Colonial
Withers
Colonial
Perkins
Colonial
Victor
Colonial
Smythe
Colonial
Millhouse
Colonial
Taylor
Colonial
Vu
Colonial
Xavier
Colonial
Reynolds
Colonial
Nanowski
Colonial
West
Colonial
Henney
Colonial
Young
Colonial
Bytes
Colonial
Victoria
Colonial
Youngston Colonial
Thompson Colonial
Nelson
Colonial
Whitman
Colonial
Smith
Colonial
Berquist
Colonial
Hesche
Colonial
Zinker
Colonial
Charles
Colonial
Young
Colonial
Harris
Colonial
Zimmerman Colonial
Rodman
Colonial
Reynolds
Colonial
Nelson
Colonial
White
Colonial
3
3
4
1
5
1
4
2
2
4
1
4
2
2
2
2
2
3
2
1
4
4
3
3
3
2
4
3
2
2
4
3
5
4
2
1 No
1 Yes
1 Yes
2 Yes
2 Yes
1 No
1 Yes
2 Yes
1 No
1 No
2 Yes
1 Yes
2 No
1 Yes
1 No
1 No
2 No
1 Yes
1 Yes
2 No
2 No
1 Yes
2 Yes
1 Yes
2 Yes
1 No
1 Yes
1 Yes
1 No
1 Yes
1 No
1 No
1 Yes
2 Yes
2 Yes
Near Schools Purchase Price Rate
Bank
Mortgage Years
Yes
$445,000
4.10% Bank Of America
15
No
$320,000
3.50% Citywide
30
No
$600,000
3.25% Citizens
40
Yes
$225,000
3.70% People’s
30
Yes
$425,000
3.50% Bank Of America
30
No
$390,000
3.90% People’s
30
No
$225,000
3.40% Citywide
15
No
$575,000
3.40% Nations
30
No
$355,000
4.50% Bank Of America
15
No
$440,000
3.30% Citywide
40
No
$440,000
3.50% People’s
40
No
$575,000
3.90% Bank Of America
15
Yes
$440,000
3.90% People’s
30
No
$445,000
4.30% Bank Of America
15
Yes
$425,000
3.90% Citywide
15
Yes
$355,000
3.90% Nations
30
No
$575,000
4.40% People’s
30
No
$245,000
3.50% Bank Of America
40
Yes
$445,000
4.00% Citizens
15
Yes
$320,000
4.10% Citizens
30
No
$440,000
3.70% Bank Of America
40
Yes
$225,000
3.70% First Federal
30
No
$355,000
4.40% Bank Of America
30
No
$245,000
3.40% First Federal
15
No
$550,000
4.50% People’s
15
Yes
$440,000
3.70% Citywide
30
No
$385,000
3.50% Bank Of America
30
No
$405,000
3.50% Citywide
15
Yes
$355,000
4.00% Citywide
15
Yes
$245,000
3.60% First Federal
30
Yes
$355,000
4.30% First Federal
30
No
$390,000
4.30% First Federal
30
Yes
$550,000
3.70% Nations
30
No
$550,000
3.50% Citizens
30
No
$390,000
3.20% Nations
30
No
$550,000
3.40% Bank Of America
30
No
$575,000
3.80% Nations
30
No
$425,000
4.40% Bank Of America
30
No
$355,000
4.10% First Federal
30
No
$550,000
4.30% People’s
30
Yes
$385,000
3.50% Nations
15
No
$550,000
4.00% Nations
30
No
$425,000
4.20% Bank Of America
30
Yes
$445,000
4.00% Bank Of America
30
Yes
$425,000
4.20% Nations
30
Yes
$225,000
3.40% First Federal
30
Yes
$390,000
4.10% People’s
30
Yes
$385,000
3.80% People’s
40
No
$425,000
3.70% People’s
15
Yes
$550,000
4.50% First Federal
40
No
$550,000
3.30% People’s
30
No
No
No
Yes
Yes
Yes
No
No
Yes
No
No
Yes
Yes
No
No
Yes
Yes
No
Yes
No
No
No
Yes
Yes
No
No
Yes
Yes
Yes
Yes
No
Yes
No
No
No
No
No
Yes
No
No
Yes
No
Yes
Yes
Yes
No
No
No
No
Yes
Yes
No
$390,000
$575,000
$425,000
$425,000
$390,000
$550,000
$440,000
$390,000
$575,000
$355,000
$445,000
$575,000
$440,000
$385,000
$550,000
$440,000
$385,000
$550,000
$355,000
$445,000
$425,000
$445,000
$320,000
$320,000
$385,000
$425,000
$320,000
$225,000
$390,000
$225,000
$550,000
$440,000
$385,000
$445,000
$425,000
$245,000
$575,000
$390,000
$385,000
$445,000
$320,000
$425,000
$575,000
$355,000
$225,000
$385,000
$320,000
$355,000
$225,000
$425,000
$425,000
$390,000
3.50% People’s
3.50% Nations
4.10% People’s
4.20% Citizens
3.30% Nations
3.60% Bank Of America
3.60% Nations
3.80% Citywide
3.90% Citizens
3.20% Nations
3.20% Nations
3.50% Nations
3.80% Nations
3.20% Bank Of America
3.50% Citywide
3.90% Bank Of America
3.90% People’s
3.20% Bank Of America
4.50% Citywide
3.80% Citizens
3.30% Bank Of America
4.00% Citywide
3.90% Bank Of America
4.20% People’s
3.60% Citywide
3.20% Citywide
3.50% Citywide
4.50% Nations
3.50% Bank Of America
3.90% Bank Of America
4.10% Citywide
4.20% Nations
3.90% First Federal
3.90% First Federal
3.60% Citywide
4.00% Nations
4.10% Citizens
3.40% Citizens
4.10% People’s
3.40% First Federal
3.90% People’s
4.00% Citizens
3.60% Bank Of America
3.20% Bank Of America
4.50% First Federal
3.50% Nations
4.50% Bank Of America
4.50% Bank Of America
4.30% Bank Of America
4.30% Citywide
4.10% First Federal
4.00% Citywide
15
30
30
15
15
30
30
30
30
30
30
40
15
30
30
40
40
30
30
30
30
15
30
15
30
30
40
30
15
30
30
30
15
30
15
15
30
15
40
15
30
40
30
30
30
15
30
40
40
30
40
15
Yes
No
No
No
No
Yes
No
No
Yes
Yes
No
No
Yes
No
Yes
Yes
Yes
No
No
Yes
Yes
No
No
No
No
Yes
No
No
Yes
No
Yes
Yes
No
No
No
$440,000
$445,000
$575,000
$575,000
$395,000
$320,000
$225,000
$385,000
$245,000
$550,000
$320,000
$445,000
$550,000
$440,000
$245,000
$575,000
$395,000
$245,000
$445,000
$390,000
$445,000
$575,000
$355,000
$245,000
$440,000
$390,000
$440,000
$320,000
$355,000
$395,000
$390,000
$445,000
$390,000
$575,000
$575,000
4.20% Nations
3.70% Bank Of America
4.50% Bank Of America
3.20% People’s
3.30% People’s
3.20% People’s
3.50% Citywide
4.40% People’s
3.60% Citywide
4.20% Bank Of America
3.70% People’s
3.80% Citizens
4.20% First Federal
4.20% Citizens
3.40% First Federal
4.10% First Federal
3.40% People’s
3.30% Citywide
3.40% Nations
3.70% People’s
3.30% Bank Of America
3.40% People’s
3.60% Nations
4.20% Citizens
3.80% People’s
4.20% Bank Of America
4.30% Nations
4.50% Bank Of America
3.90% People’s
4.50% Nations
3.30% First Federal
4.50% Citywide
4.20% People’s
4.00% Citizens
3.40% First Federal
30
30
30
15
40
40
30
30
30
30
30
40
30
15
30
30
15
40
30
15
30
30
30
30
30
30
30
30
30
30
30
30
30
30
15
Loan Payment per
3.70%
$200,000 Borrowed
20 years
Rates
Years
$1,180.58
3.70%
3.75%
3.80%
3.85%
3.90%
3.95%
4.00%
4.05%
4.10%
4.15%
4.20%
4.25%
4.30%
4.35%
4.40%
4.45%
4.50%
4.55%
4.60%
4.65%
15
20
30
If you can afford this lo
Loan Payment
Rate
Years
You can borrow this mu
Amount to Borrow
f you can afford this loan:
$920.57
3.70%
30
ou can borrow this much:
$
200,000
If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the top of
the workbook so you can modify the workbook.
1.
2.
3.
4.
5.
6.
Format the data on the Sales Data worksheet as a table using the White, Table Style Medium
1 table style:
a. Select any cell in the data.
b. On the Home tab, in the Styles group, click the Format as Table button to display
the Table Styles gallery.
c. Click the White, Table Style Medium 1 Quick Style. It is the first option in
the Medium section of the gallery.
d. Verify that the My table has headers check box is checked and that the correct
data range is selected.
e. Click OK.
Remove banding from the table rows.
a. On the Table Design tab, in the Table Style Options group, click the Banded
Rows check box to remove the checkmark.
Name the table.
a. On the Table Design tab, in the Properties group, type SalesData in the Table
Name box.
b. Press Enter.
Add a Total row to the table to display the number of buyers; the average number of
bedrooms and bathrooms for each sale; and the average purchase price, interest rate, and
mortgage length.
a. On the Table Design tab, in the Table Style Options group, click the Total
Row check box.
b. In the Total row at the bottom of the table, click in the Buyers column, click the
arrow, and select the Count function.
c. In the Total row at the bottom of the table, click in the Bedrooms column, click the
arrow, and select the Average function.
d. In the Total row at the bottom of the table, click in the Bathrooms column, click
the arrow, and select the Average function.
e. In the Total row at the bottom of the table, click in the Purchase Price column,
click the arrow, and select the Average function.
f. In the Total row at the bottom of the table, click in the Rate column, click the
arrow, and select the Average function.
g. In the Total row at the bottom of the table, click in the Mortgage Years column,
click the arrow, and select the Average function.
Sort the data so the newest purchases appear at the top.
a. Click anywhere in the Date of Purchase column.
b. On the Data tab, in the Sort & Filter group, click the Z-A button.
Filter the data to show only houses sold by owner with three or four bedrooms.
a. Click the arrow at the top of the Agent column.
b. Click the (Select All) check box to remove all of the checkmarks.
c. Click the check box in front of By Owner.
d. Click OK.
e. Click the arrow at the top of the Bedrooms column.
f. Click the (Select All) check box to remove all of the checkmarks.
g. Click the check boxes in front of 3 and 4.
h. Click OK.
7. Create a line chart showing the purchase prices for houses by date.
a. Select the Date of Purchase data cells. Be careful not to include the column
heading. Press and hold Ctrl and click and drag to select the Purchase Price data
cells, again being careful not to include the column heading.
b. On the Insert tab, in the Charts group, click the Insert Line or Area Chart button.
c. Select the first line chart type shown.
d. The Line Chart is inserted in the worksheet.
8. Move the chart to its own sheet named By Owner Sales.
a. If necessary, select the chart. On the Chart Design tab, in the Location group, click
the Move Chart button.
b. In the Move Chart dialog, click the New sheet radio button.
c. In the box type: By Owner Sales
d. Click OK.
9. Update the chart title and display the data labels as callouts.
a. Change the chart title to: By Owner Sales
b. Click the Chart Elements button near the upper right corner of the chart.
c. Click the Data Labels check box to add a checkmark.
d. Point to Data Labels, click the arrow that appears at the right side, and click Data
Callout.
10. Add error bars to the chart.
a. Click the Chart Elements button near the upper right corner of the chart.
b. Click the Error Bars check box to add a checkmark.
11. Apply the Style 11 Quick Style to the chart.
a. Click the Chart Styles button that appears near the upper right corner of the chart.
b. Click Style 11.
12. Create a PivotTable to summarize the average purchase price of different house types for each
agent.
a. Return to the Sales Data worksheet and click anywhere in the table.
b. Click the Insert tab. In the Tables group, click
the Recommended PivotTables button.
c. Verify that the first recommended PivotTable is Sum of Purchase Price by Agent.
d. Click OK.
e. Select any cell in the Sum of Purchase Price column.
f. On the PivotTable Analyze tab, in the Active Field group, click the Field
Settings button.
g. In the Summarize value field by box, select Average.
h. Click OK.
i. Add the House Type field to the PivotTable by clicking the check box in the Fields
List pane. Excel automatically places the House Type field in the Rows box and
displays the house type data as a subgroup of rows below each agent.
j. To summarize the house type data for each agent, use the House Type field as
columns in the PivotTable. Click and drag the House Type field from the Rows box
to the Columns box in the PivotTable Fields pane.
13. Add column Sparklines to the right of the PivotTable.
a. Select cells B5:E9 to use as the data for the Sparklines. You do not want to include
the grand total column or row.
b. On the Insert tab, in the Sparklines group, click the Column button.
c.
In the Create Sparklines dialog, verify that the cell range B5:E9 is listed in the Data
Range box.
d. Add the range G5:G9 to the Location Range box either by typing the cell range or
by clicking and dragging to select it in the worksheet.
e. Click OK.
14. Create a PivotChart from the PivotTable.
a. Select any cell in the PivotTable.
b. On the PivotTable Analyze tab, in the Tools group, click the PivotChart button.
c. Select the first bar chart type from the Insert Chart Click OK.
d. If necessary, move the PivotChart to another part of the worksheet so it does not
cover the PivotTable data.
15. Use the data in the Loan Worksheet sheet to run a what-if scenario for a client to show loan
payments for a variety of interest rates and loan lengths. This what-if scenario requires a twovariable data table.
a. Go to the Loan Worksheet sheet and familiarize yourself with the formula in
cell B5. Pay close attention to the cell references.
b. Select cells B5:E25 to use the payment formula in B5 and the various years and
rates as the data table.
c. On the Data tab, in the Forecast group, click the What-If Analysis button, and
click Data Table.
d. In the Row input cell box, enter the cell reference for the length of the loan—
the nper argument from the formula in cell B5: C2.
e. In the Column input cell box, enter the cell reference for the loan interest rate—
the interest argument from the formula in cell B5: A2.
f. Click OK. (Data table values are inserted)
16. Use Goal Seek to determine the most you can afford to borrow, on a $950 per month budget:
a. On the Loan Worksheet sheet, select the outcome formula cell, H4.
b. On the Data tab, in the Forecast group, click the What-If Analysis button and
click Goal Seek…
c. Verify that the outcome cell H4 is referenced in the Set cell box.
d. Enter the outcome value of 950 in the To value box.
e. Enter the input cell H9 in the By changing cell box.
f. Click OK.
g. Click OK again to accept the Goal Seek solution.
h. Notice the loan payment changed to $950 and the amount to borrow was
computed to be $206,395.
17. Save and close the workbook.
18.
