(Solved) : Excel Chapter 5 Grader Project Travel Expenses 10 Project Description Manager Information Q27939579 . . .
EXCEL CHAPTER 5 GRADER PROJECT – TRAVEL EXPENSES 1.0 ProjectDescription: You are the manager of an information technology (IT)team. Your employees go to training workshops and nationalconferences to keep up-to-date in the field. You created a list ofexpenses by category for each employee for the last six months. Nowyou want to subtotal the data to review total costs by employee andthen create a PivotTable to look at the data from differentperspectives. INSTRUCTIONS: 1.) On the Subtotals worksheet, sortthe data by Employee and further sort by Category, both inalphabetical order. 2.) Use the Subtotals feature to insertsubtotal rows by Employee to calculate the total expense byemployee. 3.) Collapse the Donaldson and Hart sections to show onlytheir totals. Leave the other employees’ individual rows displayed.4.) Use the Expenses worksheet to create a blank PivotTable on anew worksheet named Summary. Name the PivotTable Categories. 5.)Use the Category and Expense fields, enabling Excel to determinewhere the fields go in the PivotTable. 6.) Modify the Values fieldto determine the average expense by category. Change the customname to Average Expense. 7.) Format the Values field withAccounting number type. 8.) Type Category in cell A3 and change theGrand Totals layout option to On for Rows Only. 9.) Apply PivotStyle Dark 2 and Display banded rows. 10.) Insert a slicer for theEmployee field, change the slicer height to 2 inches and apply theSlicer Style Dark 5. Move the slider below the PivotTable. Note,depending upon the version of Office being used, the style name maybe Light Blue, Slicer Style Dark 5. 11.) Use the Expenses worksheetto create another blank PivotTable on a sheet named Totals. Add theEmployee to the Rows and add the Expense field to the Values area.Sort the PivotTable from largest to smallest expense. 12.) Changethe name for the Expenses column to Totals and format the fieldwith Accounting number format. 13.) Insert a calculated field tosubtract 2659.72 from the Expenses field. Format the field with thecustom name Above or Below Average and apply Accounting numberformat to the field. 14.) Set 12.25 width for column C, change therow height of row 3 to 30, and apply word wrap to cell C3. 15.)Create a clustered column PivotChart from the PivotTable. Move thePivotChart to a new sheet named Chart. Hide the field buttons inthe PivotChart. 16.) Add a chart title above the chart and typeExpenses by Employee. Change the chart Style 14. 17.) Apply 11 ptfont size to the value axis and display vertical axis with zerodecimal places. 18.) Create a footer on all worksheets with yourname in the left section, the sheet name code in the centersection, and the file name code in the right section. 19.) Ensurethat the worksheets are correctly named and placed in the followingorder in the workbook: Subtotals, Summary, Chart, Totals, Expenses.Save the workbook. Close the workbook and then exit Excel. Submitthe workbook as directed.
Expert Answer
Answer to Excel Chapter 5 Grader Project Travel Expenses 10 Project Description Manager Information Q27939579 . . .
OR