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.

Park

Concessions

Park

July

Food

July

Food

July

Food

July

Food

July

Food

July Five Points Park

July Hidden Hill Park Batting Cages –
July Kennedy Park Batting Cages –
July Knight’s Bridge Park Batting Cages

July Memorial Park Batting Cages

July Sunset Park Batting Cages

July Five Points Park

July Hidden Hill Park Bike Rentals

July Kennedy Park Bike Rentals

July Knight’s Bridge Park Bike Rentals

July Memorial Park Bike Rentals

July Sunset Park Bike Rentals

July Five Points Park

July Hidden Hill Park Boat Rentals –
July Kennedy Park Boat Rentals –
July Knight’s Bridge Park Boat Rentals –
July Memorial Park Boat Rentals –
July Sunset Park Boat Rentals –
July Five Points Park

–

July Hidden Hill Park Field Rentals –
July Kennedy Park Field Rentals –
July Knight’s Bridge Park Field Rentals

July Memorial Park Field Rentals

July Sunset Park Field Rentals

July Five Points Park

July Hidden Hill Park Golf Fees

July Kennedy Park Golf Fees –
July Knight’s Bridge Park Golf Fees –
July Memorial Park Golf Fees

July Sunset Park Golf Fees –

Five Points Park Food

August Hidden Hill Park Food

August Kennedy Park Food

August Knight’s Bridge Park Food

August Memorial Park Food

August Sunset Park Food

August Five Points Park Batting Cages –
August Hidden Hill Park Batting Cages –
August Kennedy Park Batting Cages –
August Knight’s Bridge Park Batting Cages

August Memorial Park Batting Cages

August Sunset Park Batting Cages

August Five Points Park Bike Rentals 2,670
August Hidden Hill Park Bike Rentals

August Kennedy Park Bike Rentals

August Knight’s Bridge Park Bike Rentals

August Memorial Park Bike Rentals

August Sunset Park Bike Rentals 630
August Five Points Park Boat Rentals

August Hidden Hill Park Boat Rentals –
August Kennedy Park Boat Rentals –
August Knight’s Bridge Park Boat Rentals –
August Memorial Park Boat Rentals –
August Sunset Park Boat Rentals –
August Five Points Park Field Rentals –
August Hidden Hill Park Field Rentals –
August Kennedy Park Field Rentals –
August Knight’s Bridge Park Field Rentals

August Memorial Park Field Rentals

August Sunset Park Field Rentals

August Five Points Park Golf Fees

August Hidden Hill Park Golf Fees

August Kennedy Park Golf Fees –
August Knight’s Bridge Park Golf Fees –
August Memorial Park Golf Fees

August Sunset Park Golf Fees –

Five Points Park Food

September Hidden Hill Park Food

September Kennedy Park Food

September Knight’s Bridge Park Food

September Memorial Park Food

September Sunset Park Food

September Five Points Park Batting Cages –
September Hidden Hill Park Batting Cages –
September Kennedy Park Batting Cages –
September Knight’s Bridge Park Batting Cages

September Memorial Park Batting Cages

September Sunset Park Batting Cages

September Five Points Park Bike Rentals

September Hidden Hill Park Bike Rentals

September Kennedy Park Bike Rentals

September Knight’s Bridge Park Bike Rentals

September Memorial Park Bike Rentals

September Sunset Park Bike Rentals

September Five Points Park Boat Rentals

September Hidden Hill Park Boat Rentals –
September Kennedy Park Boat Rentals –
September Knight’s Bridge Park Boat Rentals –
September Memorial Park Boat Rentals –
September Sunset Park Boat Rentals –
September Five Points Park Field Rentals –
September Hidden Hill Park Field Rentals –
September Kennedy Park Field Rentals –
September Knight’s Bridge Park Field Rentals

September Memorial Park Field Rentals

September Sunset Park Field Rentals

September Five Points Park Golf Fees

September Hidden Hill Park Golf Fees

September Kennedy Park Golf Fees –
September Knight’s Bridge Park Golf Fees –
September Memorial Park Golf Fees

September Sunset Park Golf Fees –

Revenue from Park Concessions
Month	Item	Amount
																																			July																		Five Points Park																		Food	$ 34,722
																	Hidden Hill Park	22,376
																	Kennedy Park	8,428
																	Knight’s Bridge Park	18,821
																	Memorial Park	25,856
																	Sunset Park	6,104
																	Batting Cages																																										–
3,722
3,640
2,717
																	Bike Rentals		2,670
1,860
780
930
1,560
	630
																	Boat Rentals	7,841
																	Field Rentals
11,640
13,500
6,600
																	Golf Fees	38,376
18,816
24,550
																																			August	33,049
24,310
9,920
21,581
28,985
7,970
3,642
3,864
2,430
1,620
690
870
1,680
7,530
11,400
13,860
6,294
47,984
22,204
24,785
																																			September	42,670
22,304
8,970
18,155
24,252
6,065
3,917
3,750
2,984
2,809
1,625
540
674
1,488
656
7,856
10,870
13,338
6,689
47,569
19,681
25,398

Grader – Instructions Excel 2019 Project

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.

Steps to Perform:

Step

Instructions

Points Possible

1

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

0

2

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.)

20

3

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.

10

4

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.

10

5

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.

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.

14

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. (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.

8

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

11

10

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.

5

11

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

2

12

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

0

Total Points

100

Created On: 08/22/2019 1 GO19_XL_CH04_GRADER_4E_HW – Concessions 1.0