Illustrated Excel 2019 | Module 8: End of Module Project 1 Slate Blue Technology ANALYZE DATA WITH PIVOTTABLES GETTING STARTED Open the file IL_EX19_EOM8-1_FirstLastName_1

Subject:MS ExcelPrice: Bought3

Share With

Illustrated Excel 2019 | Module 8: End of Module Project 1

Slate Blue Technology

ANALYZE DATA WITH PIVOTTABLES

• GETTING STARTED

• Open the file IL_EX19_EOM8-1_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as IL_EX19_EOM8-1_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file IL_EX19_EOM8-1_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

• PROJECT STEPS

1. Chuntao Huang is a senior consultant at Slate Blue Technology, a consulting firm in Santa Clara, California specializing in technology services. She is using an Excel workbook to track the sales of the company's consulting services to California clients. She asks for your help in creating PivotTables and PivotCharts to provide an overview of the revenue by month, city, and department.
   Go to the California worksheet, which contains a table of data named Revenue. Chuntao wants to display the revenue amounts by department. Insert the Sum of Revenue by Department recommended PivotTable on a new worksheet, using Revenue by Department as the name of the worksheet.
2. Chuntao asks you to modify the new PivotTable to make it easier to interpret. Format the revenue amounts using the Accounting number format with 2 decimal places and the $ symbol. In cell A3, use the text Departments to identify the row headings.
3. She also wants to display the revenue for each department by month. Add the Month field to the Rows area of the PivotTable after the Department field.
4. Consultants earn a 2 percent bonus on the revenue generated by each department. Chuntao wants to include the bonus totals with the PivotTable. Add a calculated field named Bonus to the PivotTable. The Bonus field should multiply the Revenue value by 0.02 to calculate the bonus amount.
5. Return to the California worksheet. Chuntao also wants to display the revenue totals by client and month. Insert a PivotTable on a new worksheet based on the Revenue table. Use Revenue by Client as the name of the worksheet. Use ClientRevenue as the name of the PivotTable.
6. Add the Client ID field to the Rows area of the PivotTable. Add the Month field to the Columns area of the PivotTable. Add the Revenue field to the Values area, and then change the Value Field Settings to display the revenue totals in the Accounting number format with 0 decimal places and the $ symbol.
7. Chuntao wants to more clearly identify the contents of the ClientRevenue PivotTable and make it more attractive. In cell A4, use the text Clients to identify the row headings. In cell B3, use the text Months to identify the column headings. Apply the Sky Blue, Pivot Style Medium 12 to coordinate with the Revenue table.
8. She also decides a chart would help to analyze the data. In the Revenue by Client worksheet, insert a Clustered Column PivotChart based on the ClientRevenue PivotTable, and then move the PivotChart to a new sheet. Use Client PivotChart as the name of the new worksheet.
9. Change the chart type to a Stacked Column chart to reduce the number of columns and make the PivotChart easier to interpret. Change the colors to Monochromatic Palette 4 to use a more soothing range of colors.
10. Go to the Revenue by Client worksheet. Chuntao requests an easy way to filter the data by department and office. Add a slicer to the ClientRevenue PivotTable based on the Department field. Add a second slicer based on the Office field.
11. Move and resize the Department slicer so that the upper-left corner is in cell G3 and the lower-right corner is in cell I13. Move and resize the Office slicer so that the upper-left corner is in cell G14 and the lower-right corner is in cell I21. Apply Sky Blue, Slicer Style Light 4 to both slicers.
12. Return to the California worksheet. In the range G3:H4, Chuntao wants to highlight the total revenue earned in June and the total revenue that the Cloud Department earned. Both totals are included in the workbook's PivotTables. In cell H3, start to enter a formula, and then select cell B13 in the ClientRevenue PivotTable to insert a complete formula that uses the GETPIVOTDATA function to retrieve data from the PivotTable. In cell H4, use the same method to insert a formula that uses the GETPIVOTDATA function to get data from cell B4 on the Revenue by Department worksheet.

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 SAM website to submit your completed project.