(Solved) : Ex16xlch05graderml1hw Mountain View Realty 11 Project Description Instructions Purpose Gra Q26730573 . . .

EX16_XL_CH05_GRADER_ML1_HW – Mountain View Realty1.1

Project Description:

<Project Description>

Instructions:

For the purpose of grading the project you are required toperform the following tasks:

Step Instructions Points Possible
1 Open exploring_e05_grader_h2_RealEstate.xlsx and saveit asexploring_e05_grader_h2_RealEstate_LastFirst. 0.000
2 In cell G2 in the Sales Subtotals sheet, insert a formula tocalculate the selling price percentage of the asking price, formatit with Percent Style with one decimal place, and then copy theformula down the column. 5.000
3 In cell J2, enter a formula to calculate the number of daysbetween the listing date and sale date. Copy the formula down thecolumn. 5.000
4 Sort the list by City in alphabetical order, then by SellingAgent in alphabetical order, and finally by Listing Date inchronological order (oldest to newest). 9.000
5 Use the Subtotal feature to calculate the average selling price,percentage of asking price, and days on market by city. Ensure thatSummary below data is selected, and then click OK. Group the datausing the Auto Outline feature. Collapse the outline to hide thelisting and sale dates. Click the outline symbol to display thegrand average and city average rows only. 15.000
6 Format the average days on market as numbers with zero decimalplaces. Set a print area for the range C1:J88. 3.000
7 Go to cell C101, read the questions, and type the correctanswers in the respective highlighted cells in the range G102:G106.Apply Accounting Number Format with zero decimal places to cellG102. 5.000
8 Click the Sales Data worksheet and create a blank PivotTable ona new worksheet. Name the new worksheetPivotTable. Name the PivotTable AverageCity Prices. 10.000
9 Place the City field in rows, the Selling Agent field incolumns, and the Asking Price and Selling Price fields as values.Display averages rather than sums with Accounting Number Formatwith zero decimal places. Pivot the data by placing the City fieldbelow the Values field in the Columns area and moving the SellingAgent field to the Rows area. 9.000
10 Add a filter to cell B3 to display only Alpine andCedar Hills. 2.000
11 Go back to the Sales Data worksheet. You realize that a sellingprice is incorrect. Change the selling price for Number 40 from$140,000 to $1,400,000. Refresh thePivotTable. 3.000
12 Change the widths of columns A, B, C, D, and E to11. Change the widths of columns F and G to14. Wrap text and center horizontally data incells B4, D4, F4, and G4. Apply the Bottom Border to the rangeB4:E4. Change the label in cell A5 to Agent.Change the height of row 4 to 40. 10.000
13 Display the Sales Data worksheet. Create a recommendedPivotTable using the Sum of Selling Price by City thumbnail. Changethe name of the new PivotTable worksheet to SellingPrice. 5.000
14 Change the value to display averages not sums. Apply theAccounting Number Format with 0 decimal places to the values. ApplyLight Blue, Pivot Style Medium 2 to the PivotTable.

Note, depending upon the version of Office being used, the stylename may be Pivot Style Medium 2.

6.000
15 Create a clustered column PivotChart from the PivotTable on theSelling Price worksheet. Move the chart to a chart sheet namedSales Chart. 5.000
16 Change the chart title to Average Selling Price byCity and apply Dark Blue font color. Remove the legend.Apply Blue, Accent 1 fill color to the data series. 6.000
17 Create a footer with your name on the left side, the sheet namecode in the center, and the file name code on the right side of allworksheets. Adjust the scaling if needed. 2.000
18 Save the workbook. Ensure that the worksheets are namedcorrectly and in the following order: Sales Subtotals, PivotTable,Sales Chart, Selling Price, Sales Data. Close the workbook and exitExcel. Submit the file as directed. 0.000
Total Points 100.000

 

Expert Answer


. . .