GO16_XL_CH07_GRADER_7E_HW – Concessions Revenue 1.2

Step

Instructions

Points Possible

1

Start Excel. From your student files, open go16_xl_ch07_grader_7e_hw.xlsx and Save the file in your Excel Chapter 7 folder as Lastname_Firstname_7E_Concessions_Revenue

0

2

Click cell A2. Insert the last Recommended PivotTable—Sum of Amount by Park. Add the Month field to the Rows area. Place the Item field in the Columns area. Close the PivotTable Fields pane. Note, Mac users create a pivot chart with the Park and Month fields (in that order) in the Rows area, the Item field in the Column area, and the Amount field in the Values area. CLose the PivotTable Fields pane.

12

3

Insert a slicer for the Park field and a Slicer for the Item field. Move the slicers so that they are to the right of the PivotTable. Apply Slicer Style Light 4 to the Park slicer and Slicer Style Light 6 to the Item slicer.

12

4

Filter first by Memorial Park, and then by Golf Fees. Change the Number Format to the Number category and display zero decimal places and the 1000 Separator. Move the Park slicer so that its upper left corner is in cell E3. Move the Item slicer so that its upper left corner is in cell H3.

10

5

Make a copy of this worksheet and Rename the copied worksheet Memorial Golf Fees. Note, Mac users, after copying and renaming the sheet, on the Memorial Golf Fees sheet, on the ribbon, click Filter Connections. Remove all check marks.

10

6

Rename Sheet1 Concessions Revenue and then clear the filters and remove the slicers. In cell A1, type Third Quarter Park Concession Revenue and apply the Title cell style.

12

7

Display the Field List, and then remove the Month field from the Rows area to display the Grand totals for each park and for each item. Close the PivotTable Fields pane.

10

8

Insert a PivotChart using the Stacked Column chart type. Move the chart to a new worksheet named Concessions Chart Note, Mac users, select the range A4:G9 and create a traditional Stacked Collumn chart. Move the chart to a new sheet with the name Concessions Chart

7

9

Apply the Layout 3 chart layout and Chart Style 5. As the Chart Title, type 3rd Quarter Concession Revenue and then insert a custom footer with the file name field in the left section. Hide all of the field buttons on the chart.

10

10

Hide the Park Concessions worksheet. Select all worksheets, and then insert a footer with the file name field in the left section. Change the Orientation to Landscape, set the Width to 1 page, and center the sheets Horizontally.

15

11

Click the File tab and then click Show All Properties. As the Tags, type concession revenue and as the Subject, type your course name and section #. Under Related People, be sure that your name displays as Author. Save your workbook. Note, Mac users click the File menu, and then click Properties. Change the keywords to concession revenue.

2

12

Save and close the file, and then submit for grading.

0

Total Points

100