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.