Shelly Cashman Excel 365

Shelly Cashman Excel 365 | Module 8: End of Module Project 1 FitPro Home Gyms

ANALYZE DATA WITH CHARTS AND PIVOTTABLES

September 14, 2026 · Excel Projects Help

GETTING STARTED

1. Save the file SC_EX365_EOM8-1_FirstLastName_1.xlsx as SC_EX365_EOM8-1_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_EOM8-1_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. Dana Simon is a sales manager for FitPro Home Gyms, a company in Austin, Texas, that manufactures workout equipment for the home. She is using an Excel workbook to analyze the company's financial performance, and asks for your help in creating advanced types of charts and PivotTables to provide an overview of product sales. Go to the Sales History worksheet, which summarizes the number of products sold from in the last six years. Dana wants you to illustrate the trend in the data and forecast product sales for the next two years. Create a Scatter chart based on the range B2:H3. Add a Logarithmic trendline to the chart that forecasts two periods forward to project sales for the next two years. Resize and position the chart so that it covers the range B5:H18.

2. Go to the Product Sales worksheet, which contains a table named Sales with details about products sold in the previous and current years. Dana wants you to display the number of products sold in the previous year by location. Insert the Sum of Prev Year Units by Location recommended PivotTable. [Mac Hint: Insert a PivotTable based on the Sales table on a new worksheet. Add the Location field as rows, and the Previous Year Units field as the values.] Use Previous Year Sales as the name of the new worksheet. Apply the Rose, Pivot Style Medium 9 style to the PivotTable. Add the Rep ID field to the bottom of the Rows area. Add the Prev Year Sales field as the first field in the Values area. Change the number format of the Prev Year Sales value field to Currency with 0 decimal places and the $ symbol. Use Previous Year Sales as the column heading in cell B3, and use Previous Year Units as the column heading in cell C3.

3. Dana wants you to include the sales amount per unit in the PivotTable. Add a calculated field to the PivotTable named Sales per Unit that divides the Prev Year Sales field value by the Prev Year Units field value. Use Sales per Product as the column label in cell D3. Format the Sales per Product field values using the Currency number format with 2 decimal places and the $ symbol.

4. Dana also wants you to display the previous year sales data as a chart. Insert a Combo PivotChart based on the new PivotTable. Display the Previous Year Sales and the Previous Year Units data as a Clustered Column chart and the Sales per Product data as a Line chart. Include a secondary axis for the Sales per Product data. [Mac Hint: Select the Sales per Product series and use the Format Data Series option to plot the series on the Secondary Axis.]

5. Format the PivotChart to make it easier to interpret and to coordinate it with the PivotTable. Resize and position the PivotChart so that it covers the range A16:D30. Display the legend at the bottom of the chart, and then hide the Field List and all the field buttons to remove clutter from the worksheet. Change the maximum bounds for the value axis on the left to 5,000,000. Change the PivotChart colors to Monochromatic Palette 1.

6. Dana asks you to provide a quick way to filter the PivotTable and PivotChart by product type. Add a slicer based on the Product Type field, and then apply the Rose, Slicer Style Dark 1. Display the slicer buttons in two columns, and then move and resize the slicer so that the upper-left corner is in cell F3 and the lower-right corner is in cell J7. Use the slicer to filter the PivotTable and PivotChart to display Exercise bike and Treadmill data.

7. Dana also wants you to insert a PivotTable that includes other sales information so that she can analyze the current product sales. Return to the Product Sales worksheet, and then create a PivotTable based on the Sales table. Place the PivotTable on a new worksheet, using Current Year Sales as the worksheet name. Make sure the 'Add this data to the Data Model' check box is unchecked. [Mac Hint: There is no check box, so Mac users can ignore this.] Display the product types and then the rep IDs as the row headings, the locations as the column headings, the current sales as the values, and the years of experience as the filter.

8. Dana wants you to improve the appearance of the PivotTable and focus on products sold by sales reps with 10 or more years of experience. Change the layout of the PivotTable to the Outline form to separate the product types from the rep IDs. Hide the field headers to streamline the design of the PivotTable. Display the subtotals at the bottom of each group, and then filter the PivotTable to show the products sold by reps with 10 or more years of experience.

9. Dana wants to know more details about the products sold by the top-selling sales rep. Drill down into the Rowing machines sold in the Georgetown location by rep FP-3283 (cell C10). Use Top Rep as the name of the new worksheet.

10. Return to the Product Sales worksheet. Dana wants to compare the average sales generated by sales reps based on their years of experience. Insert a PivotTable and PivotChart on a new worksheet, using Average Sales as the worksheet name. Make sure the 'Add this data to the Data Model' check box is unchecked. [Mac Hint: There is no check box, so Mac users can ignore this.] With the blank PivotChart selected, add the Years Experience field to the Axis (Categories) area, Prev Year Sales as the first Values field, and Current Sales as the second Values field. Change the value field settings of both value fields to display averages, using the Currency number format with 2 decimal places and the $ symbol. Change the PivotChart layout to Layout 3, and use Sales by Years of Experience as the chart title. Move and resize the PivotChart so that its upper-left corner is in cell A9 and its lower-right corner is in cell D24.

11. Return to the Product Sales worksheet. Dana asks you to create a chart comparing the number of current units sold to the three categories of experience for each sales rep. She wants to find the median products sold this year by experience and any sales totals that are outliers in each category. Create a Box and Whisker chart based on the Years Experience data (range D2:D40) and the Current Units data (range G2:G40). Move the chart to a new worksheet and use Products by Experience as the name of the worksheet. Use Products Sold by Rep Experience as the chart title. Format the data series to show the inner points so that the data values appear as dots on the chart, and then apply the default gradient fill to make the dots easier to see.

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: Sales History Worksheet

Windows, Access, Excel, 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: Previous Year Sales Worksheet

Final Figure 3: Top Rep Worksheet

Final Figure 4: Current Year Sales Worksheet

Final Figure 5: Average Sales Worksheet

Final Figure 6: Products by Experience Worksheet

Final Figure 7: Product Sales Worksheet

Need help with this project?