Excel chapter 5 real estate

t] Alternate

t] Alternate

A Skills Approach: Excel 2016 Chapter 5: Adding Charts and Analyzing Data

Skill Review 5.2

Step 1 Download start file

In this project you will analyze real estate data.

Skills needed to complete this project: • Converting Data into Tables • Adding Total Rows to Tables • Sorting Data • Filtering Data • Inserting a Line Chart • Resizing and Moving Charts • Showing and Hiding Chart Elements • Exploring Charts • Applying Quick Styles and Colors to Charts • Creating PivotTables Using Recommended PivotTables • Inserting Sparklines • Creating a PivotChart from a PivotTable • Analyzing Data with Data Tables • Analyzing Data with Goal Seek

This image appears when a project instruction has changed to accommodate an update to Microsoft Office 365. If the instruction does not match your version of Office, try using the alternate instruction instead

1. Open the start file EX2016-SkillReview-5-2. The file will be renamed automatically to include your name.

Change the project file name if directed to do so by your instructor, and save it.

2. 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.

3. Format the data on the Sales Data worksheet as a table using the Green, Table Style Light 14, or Table

Style Light 14 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 Green, Table Style Light 14 style.

Click the Table Style Light 14 style

d. Verify that the My table has headers check box is checked and that the correct data range is selected.

e. Click OK.

4. 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 Tools Design tab, in the Table Style Options group, click the Total Row check box.

1 | Page Skill Review 5.2 Last Updated 6/18/18

A Skills Approach: Excel 2016 Chapter 5: Adding Charts and Analyzing Data

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.

IMPORTANT: You must complete steps 5 and 6 in the order they are written to receive all points for

completing step 7.

5. 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.

6. Filter the data to show only houses sold by owner with four or five 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 4 and 5.

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

the Ctrl key 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 Chart button.

c. Select the first line chart type shown.

d. Click OK.

2 | Page Skill Review 5.2 Last Updated 6/18/18

A Skills Approach: Excel 2016 Chapter 5: Adding Charts and Analyzing Data

8. Move the chart to its own sheet named Purchase Prices.

a. If necessary, select the chart. On the Chart Tools Design tab, in the Locations group, click the Move

Chart button.

b. In the Move Chart dialog, click the New sheet radio button.

c. In the box type: Purchase Prices

d. Click OK.

9. Update the chart title and display the data labels as callouts.

a. Change the chart title to: For Sale By Owner

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. Apply the Style 2 Quick Style to the chart.

a. Click the Chart Styles button that appears near the upper right corner of the chart.

b. Click Style 2.

11. 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 Tools 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.

12. 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.

3 | Page Skill Review 5.2 Last Updated 6/18/18

A Skills Approach: Excel 2016 Chapter 5: Adding Charts and Analyzing Data

d. Add the range G5:G9 to the Location Range box either by typing the cells range or by clicking and

dragging to select it in the worksheet.

e. Click OK.

13. Create a PivotChart from the PivotTable.

a. Select any cell in the PivotTable.

b. On the PivotTable Tools Analyze tab, in the Tools group, click the PivotChart button.

c. Select the first bar chart type from the Insert Chart dialog. Click OK.

d. If necessary, move the PivotChart to another part of the worksheet so it does not cover the PivotTable

data.

14. 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 two‐variable 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.

15. 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 $198,988.

Step 2 Upload & Save

Step 3 Grade my Project

16. Save and close the workbook.

17. Upload and save your project file.

18. Submit project for grading.

4 | Page Skill Review 5.2 Last Updated 6/18/18

Skill Review 5.2
Skills needed to complete this project: