Microsoft Excel

Challenging assignment Description:

In this assignment, you will create and modify a PivotTable report
and a PivotChart report in order to analyze revenue from the park
concessions.

Instructions:

For the purpose of grading the Challenging assignment you are required to perform the following tasks:

Step

Instructions

Points Possible

1

Start Excel. Open, save, the downloaded Excel workbook named Challenging assignment5.xlsx.

0.000

2

Click cell A2. Insert the Recommend PivotTable, Sum of Amount by Park. Add the Month field to the ROWS area below Park. Place the Item field in the COLUMNS area. Close the PivotTable Fields pane.

12.000

3

Insert a slicer for the Park field and for the Item field. Apply
Slicer Style Light 3 to the Park slicer and Slicer Style Light 4 to the
Item slicer.

10.000

4

Using the Park and Item slicers, filter the data to show the total revenue for golf fees in Memorial Park.

8.000

5

Change the height of each slicer to 1.5″. Reposition the Park slicer
so that the top left corner is located inside the borders of cell E3.
Reposition the Item slicer so that the top left corner is located inside
the borders of cell E12.

6.000

6

Modify the value field settings in the PivotTable so that values are
formatted as numbers with zero decimal places and a 1000 separator.

8.000

7

Make a copy of the Sheet1 worksheet. Rename the copied worksheet Memorial – 3Q Golf Fees. Rename the Sheet1 worksheet Concessions Revenue.

4.000

8

On the Concessions Revenue worksheet, clear all filters and remove the slicers. In cell A1, type Third Quarter Park Concession Revenue.

7.000

9

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

8.000

10

From the Concessions Revenue worksheet, insert a PivotChart using the
stacked bar chart type. Move the PivotChart to a new worksheet using
the name 3Q Concessions Chart.

10.000

11

Change the layout of the PivotChart to Layout 3 and change the chart style to Style 4.

6.000

12

Change the chart title to 3rd Quarter Concession Revenue and ensure that data labels are added to the center of the data points.

8.000

13

Format the Plot Area with the Gradient fill Light Gradient Accent 6
(sixth column, first color). Format the Chart Area with a solid fill
using Blue, Accent 1, Lighter 60% (fifth column, third color). Hide all
of the field buttons on the chart.

8.000

14

Hide the Park Concessions worksheet.

5.000

15

Save the workbook. Close the workbook and then exit Excel. Submit the workbook for grading.