Shelly Cashman Excel 365 | Module 8: SAM Project B Davis Sports Center
Analyze Data with Charts and PivotTables
· Excel Projects Help
GETTING STARTED
1. Save the file SC_EX365_8B_FirstLastName_1.xlsx as SC_EX365_8B_FirstLastName_2.xlsx
a. Edit the file name by changing “1” to “2”.
b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.
2. With the file SC_EX365_8B_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.
a. If cell B6 does not display your name, delete the file and download a new copy.
PROJECT STEPS
1. Hana Hwang is a financial analyst for Davis Sports Center, a franchise of recreational facilities for youth and adults with headquarters in Chicago, Illinois. She is using an Excel workbook to analyze the company's recent financial performance, and asks for your help in creating advanced types of charts and PivotTables to provide an overview of recreational programs, members, and sports centers. Go to the Program Types worksheet, which contains a table named Programs in the range B3:G17 that contains data about membership programs. Hana asks you to create a chart comparing the total charges to the three program types to identify the median charge by program type. Insert a chart as follows:
a. Create a Box and Whisker chart based on the Program Type data (range C3:C17) and the Total Charge data (range G3:G17).
b. Resize and position the chart so that it covers the range B19:G33.
c. Use Total Charge by Program Type as the chart title.
d. Format the data series using the default gradient fill to make the data easier to interpret.
2. The Program Types worksheet also contains a table named Rentals in the range I3:K10 that compares the facility rental data for each day of the week. Hana asks you to create a chart that shows the relationship between the day and rentals.
a. Insert a Scatter chart that shows the relationship between the day of the week (range I3:I10) and the number of participants (range K3:K10).
b. Resize and position the Scatter chart so that it covers the range I12:L33.
c. Use Participants and Days of the Week as the chart title.
3. Hana wants to further analyze the relationship between rental participants and days of the week. Add a Logarithmic trendline to the scatter chart.
4. Go to the Members worksheet, which lists member details in a table named Members. Hana wants to display the number of members for each type of facility. Insert a recommended PivotTable based on the Members table as follows:
a. Insert the Count of Member by Facility recommended PivotTable. [Mac Hint: Use the Facility field in the Rows area, the Member field in the Values area, and show grand totals for columns only. Update the Value Field settings for the Member field to Count.]
b. Use Members by Facility as the name of the new worksheet.
c. Apply the Light Blue, Pivot Style Medium 9 style to the PivotTable.
d. Add the Charge field to the Values area of the Field List, and then change its summary function to Average so that Hana can determine the average charge for each facility type.
e. Set the Number format of the Average of Charge value field to Currency with 2 decimal places and the $ symbol, if necessary.
f. Use Total Members as the column heading in cell B3, and use Average Charge as the column heading in cell C3.
5. Insert a PivotChart based on the new PivotTable as follows to help Hana visualize the data:
a. Insert a Combo PivotChart based on the new PivotTable.
b. Display the Total Members as a Line chart and the Average Charge as a Clustered Column chart.
c. Include a secondary axis for the Total Members data.
d. Hide the Field List so that you have room to format the chart.
e. Change the minimum bounds for the value axis on the right to 2.
f. Change the PivotChart colors to Monochromatic Palette 8.
g. Resize and position the chart so that it covers the range A9:E24.
h. Display the Field List again, and hide the field buttons, if necessary.
6. Hana also wants you to insert a PivotTable that includes other member data so that she can analyze membership information. Return to the Members worksheet, and then create a PivotTable based on the Members table as follows:
a. Place the PivotTable on a new worksheet, and then use Members Pivot as the name of the worksheet.
b. Display the Facility field as column headings.
c. Display the Location field and then the Member field as row headings.
d. Display the Years field as the values.
e. Display the Program Type field as a filter, and then filter the PivotTable to display member information for Adult memberships only.
f. Hide the field headers to reduce clutter in the PivotTable.
7. Go to the Programs worksheet. The ProgramTypes table in the range B3:E14 shows the registration revenue from three seasons of recreational sports, with each season divided into sports and then into programs. Hana wants you to display these hierarchies of information in a chart. Insert a Sunburst chart to display the hierarchies for Hana as follows:
a. Insert a Sunburst chart based on the program type data in the range B3:E14.
b. Resize and position the chart so that it covers the range B16:E32.
c. Use Sports by Season as the chart title.
8. Go to the Members by Program worksheet, which contains a PivotTable showing the number of members, programs, and charges for each program type, facility type, and location. Hana does not need to display the charges, but does want to compare the subtotals of members and programs for each facility location within the program types. Modify the PivotTable for Hana as follows:
a. Remove the Sum of Charge field from the Values area.
b. Change the report layout to Tabular Form.
9. Go to the Rentals Pivot worksheet, which contains a PivotTable listing the rental amounts and number of participants for each day of the week. Hana wants you to include the amount per participant. Reorder the fields and add a calculated field to the PivotTable as follows:
a. Change the order of the fields in the Values area to display the Sum of Rentals field before the Sum of Participants field.
b. Create a calculated field using Per Participant as its name.
c. The formula should divide the Rentals field value by the Participants field value to calculate the rental charge per participant.
d. Use Charge Per Participant in cell D1 as the column heading for the calculated field.
e. Change the Number format of the Charge Per Participant value field to Currency with 2 decimal places and the $ symbol.
10. Go to the Facility Pivot worksheet, which contains a PivotTable and PivotChart that should show total charges according to facility type and location. Move the Location field below the Facility field in the Rows area so that the PivotTable is easier to interpret.
11. Make the PivotChart easier to understand and use as follows:
a. Change the PivotChart to a Clustered Column chart.
b. Apply Layout 11 to the PivotChart to use a more streamlined layout, and then apply the Monochromatic Palette 8 colors to use the same colors as in the other PivotChart.
c. Increase the height of the chart until the lower-right corner is in cell G40.
12. Add a slicer to the PivotTable and PivotChart as follows to make it easy for Hana to filter the data:
a. Add a slicer based on the Years field.
b. Without resizing the slicer, position it so that its upper-left corner is in cell G2.
c. Use the slicer to filter the PivotTable and PivotChart to display facility data for people who have been members for 5, 6, or 7 years only.
Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project.
Final Figure 1: Program Types Worksheet
Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 2: Members by Facility Worksheet
Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 3: Members Pivot Worksheet
Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 4: Members Worksheet
Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 5: Programs Worksheet
Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 6: Members by Program Worksheet
Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 7: Rentals Pivot Worksheet
Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 8: Facility Pivot Worksheet
Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.