Liberty BUSI 201 Assignment 13 Excel 2016 Skill Review 5.2 Answers Complete Solutions
Complete many different versions to get an A on your grade!
Download it for more and ace on your assignments!
Skill Review 5.2
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.
STATUS
a. On the Table Tools 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.
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.
8. Move the chart to its own sheet named Purchase Prices.
9. Update the chart title and display the data labels as callouts.
10. Apply the Style 2 Quick Style to the chart.
11. Create a PivotTable to summarize the average purchase price of different house types for each agent.
12. Add column Sparklines to the right of the PivotTable.
13. Create a PivotChart from the PivotTable.
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.
15. Use Goal Seek to determine the most you can afford to borrow, on a $950 per month budget:
17. Upload and save your project file.
18. Submit project for grading.
