Question: ?Illustrated Excel 365/2021 | Module 8: End of Module Project 1 Slate Blue TechnologyANALYZE DATA WITH PIVOTTABLES GETTING STARTED? Save the file IL_EX365_2021_EOM8-1_FirstLastName_1.xlsx as IL_EX365_2021_EOM8-1_FirstLastName_2.xlsxo
?Illustrated Excel 365/2021 | Module 8: End of Module Project 1 Slate Blue TechnologyANALYZE DATA WITH PIVOTTABLES GETTING STARTED? Save the file IL_EX365_2021_EOM8-1_FirstLastName_1.xlsx as IL_EX365_2021_EOM8-1_FirstLastName_2.xlsxo Edit the file name by changing "1" to "2".o If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.? With the file IL_EX365_2021_EOM8-1_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.o If cell B6 does not display your name, delete the file and download a new copy. PROJECT STEPS1. 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 website to submit your completed project.Final Figure 1: Revenue by Department WorksheetMicrosoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright 2020 Cengage Learning. All Rights Reserved. Final Figure 2: Client PivotChart Worksheet Final Figure 3: Revenue by Client Worksheet Final Figure 4: California Worksheet
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts