Excel_4E_Concessions

Project Description:

In the following Mastering Excel project, you will help Lindsay Johnson, the Director of Parks and Recreation, create and modify a PivotTable report and PivotChart report to analyze revenue from park concessions such as food, boat rentals, and golf fees.

Start   Excel. From the files you downloaded with this project, open the file Student_Excel_4E_Concessions.xlsx.

Click cell A2. Insert the last   Recommend PivotTable—Sum of Amount by Park. Add the Month field to the Rows   area below Park, and the Item field in the Columns area, and then Close the   PivotTable Fields pane. (Mac users, be sure that Park displays above Month.)

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

Use the slicers to filter the   PivotTable 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 aligns with the   upper left corner of cell E3. Move the Item slicer so that its upper left   corner aligns with the upper left corner of cell H3.

Make a copy of this worksheet   and Rename the copied worksheet Memorial Golf Fees Insert a footer with the file name in the left   section. Change the Orientation to Landscape, set the Width to 1 page, and   center the sheet Horizontally.

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.

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.

Insert a PivotChart using the   Stacked Column chart type. (Mac users, click the Design tab, click Change   Chart Type, point to Column, and then click Stacked Column.) Move the chart   to a new worksheet named Concessions Chart.

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 in the left section. Hide all of the field buttons on the   chart, if necessary. 

Hide the Park Concessions   worksheet. Display the Concessions Revenue sheet, and then insert a footer   with the file name in the left section. Change the Orientation to Landscape,   set the Width to 1 page, and center the sheet Horizontally.

Display the properties. As the   Tags, type concession revenue and as the Subject, type your course name and   section number. 

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