In the following project, you will assist James Davis, the Recreation Director

 Excel_4G_Recreation

 

Project Description:

In the following project, you will assist James Davis, the Recreation Director, in creating and modifying a PivotTable report and a PivotChart report.

 

Navigate   to your Excel Chapter 4 folder, and then double-click the Excel file you   downloaded from MyLab IT that displays your name—Student_Excel_4G_Recreation_as.xlsx.

 

Click cell A2, and then insert a   Recommended PivotTable by choosing the Blank PivotTable option in the lower   left corner of the dialog box. Add the Month field to the Filters area, add   the Center field to the Rows area and the Program field to the Columns area.   Place the Amount field in the Values area and then Close the PivotTable   Fields list.
Note, Mac users, move fields as necessary so that they are placed in the   correct areas of the layout section.

 

Format the values in the PivotTable using the Number category to   display zero decimal places and the 1000 Separator.

 

Insert slicers for the Program   and Center fields, and then filter by the Bard Hall Senior Center and   Ceramics and Water Color revenue. Move the Program slicer so that it’s upper   left corner aligns with the upper left corner of E3. Move the Center slicer so   that its upper left corner aligns with H3. Make a copy of this worksheet, and   then name the copied worksheet Bard Hall.

 

Display Sheet1 and clear the   filters from the slicers and remove the slicers from the worksheet. Rename   the sheet 3Q Revenue

 

Insert a PivotChart using the   Stacked Column chart type. Move the chart to a new worksheet named 3Q Revenue   Chart

 

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

 

Add a new worksheet to the   workbook. On the Data tab, click Get Data and then from your downloaded   files, import the Microsoft Access Database Excel_4G_Rec_Centers.accdb. Load both of the tables in the   database.
Note, Mac users, on the Data tab, click From Text. From your downloaded   files, click Excel_Rec_Center_Supplies_csv.csv,   and then click Get Data. Use Comma delimiters and General column data format.   Put the data in the Existing sheet in A1.

 

Insert a PivotTable in the   Existing Worksheet and verify that Use this workbook’s Data Model is   selected.

 

To create the PivotTable, place   the Supplier field from the Suppliers table in the Columns area. From the   Supply Order Summary table, place the Item field in the Rows area, and the   Quantity field in the Values area. Apply the Number format to the values in   the PivotTable with zero decimals and the 1000 separator.

 

Insert a row above the   PivotTable, if necessary, and in cell A1, type Recreation Center Supplies and then apply the Title cell   style. Rename the sheet Recreation Supplies

 

Click in the PivotTable, and   then insert a 3-D Pie PivotChart. Move the chart to a new sheet with the name   Supplies   Chart
 

Display the Field List and then   modify the chart so that only the Supplier field displays in the Axis   (Categories) area. Remove any fields that display in the Legend (Series)   area.

 

Apply Style 3, remove the legend   from the chart, and display only the Category Name and Percentage data labels   positioned in the Center.

 

Change the chart title to Supply   Purchases and   then change the font size to 24. Hide all of the field buttons on the chart,   and then insert a footer with the file name in the left section.

 

Hide the Program Revenue sheet.   Select all the sheets (excluding the chart sheets) and insert a footer in the   left section that includes the file name, and then center the worksheet   Horizontally on the page. Change the Orientation to Landscape and scale the   Width to fit to 1 page.

 

Display the properties. As the   Tags/Keywords, type rec center and as the Subject, type your course name and   section #. Under Related People, be sure that your name displays as Author.   Save your workbook.

 

Ensure the worksheets are in the   following order:
Bard Hall, 3Q Revenue Chart, Supplies   Chart, Recreation Supplies, 3Q Revenue
Save and close the file, and then submit for grading.