{"id":7300,"date":"2021-10-12T19:03:54","date_gmt":"2021-10-12T19:03:54","guid":{"rendered":"https:\/\/assignmenttutors.net\/blog\/exp19_excel_ch05_capassessment_travel-computer-science-homework-help\/"},"modified":"2021-10-12T19:03:54","modified_gmt":"2021-10-12T19:03:54","slug":"exp19_excel_ch05_capassessment_travel-computer-science-homework-help","status":"publish","type":"post","link":"https:\/\/assignmenttutors.net\/blog\/exp19_excel_ch05_capassessment_travel-computer-science-homework-help\/","title":{"rendered":"Exp19_excel_ch05_capassessment_travel | Computer Science homework help"},"content":{"rendered":"
\n

\u00a0\u00a0<\/p>\n

You are the manager of an information technology (IT) team. Your employees go to training workshops and national conferences to keep up-to-date in the field. You created a list of expenses by category for each employee for the last six months. Now you want to subtotal the data to review total costs by employee and then create a PivotTable to look at the data from different perspectives.<\/p>\n

Steps to Perform:<\/strong><\/h2>\n

\u00a0\u00a0\u00a0<\/p>\n

Step<\/strong><\/p>\n

Instructions<\/strong><\/p>\n

Points Possible<\/strong><\/p>\n

\u00a0<\/p>\n

1<\/p>\n

Start Excel. Download and open the file named Exp19_Excel_Ch05_CapAssessment_Travel.xlsx<\/em>. \u00a0\u00a0Grader has automatically added your last name to the beginning of the \u00a0\u00a0filename.<\/p>\n

0<\/p>\n

\u00a0<\/p>\n

2<\/p>\n

Before using the Subtotal \u00a0\u00a0command, you must first sort the data.
\u00a0<\/p>\n

\u00a0\u00a0On the Subtotals worksheet, sort the data by Employee and further sort by \u00a0\u00a0Category, both in alphabetical order.<\/p>\n

4<\/p>\n

\u00a0<\/p>\n

3<\/p>\n

You want to subtotal the data to calculate expenses by \u00a0\u00a0employee.
\u00a0<\/p>\n

\u00a0\u00a0Use the Subtotals feature to insert subtotal rows by Employee to calculate \u00a0\u00a0the total expense by employee.<\/p>\n

6<\/p>\n

\u00a0<\/p>\n

4<\/p>\n

Now you want to hide the details \u00a0\u00a0for two employees and focus on details for the remaining employees.
\u00a0<\/p>\n

\u00a0\u00a0Collapse the Davidson and Miller sections to show only their totals. Leave \u00a0\u00a0the other employees\u2019 individual rows displayed.<\/p>\n

\u00a0<\/p>\n

5<\/p>\n

\u00a0<\/p>\n

5<\/p>\n

For more detailed analysis, you are ready to create a \u00a0\u00a0PivotTable.
\u00a0<\/p>\n

\u00a0\u00a0Use the Expenses worksheet to create a blank PivotTable on a new worksheet \u00a0\u00a0named Summary<\/strong>. \u00a0\u00a0Name the PivotTable Categories<\/strong>.\u00a0<\/p>\n

8<\/p>\n

\u00a0<\/p>\n

6<\/p>\n

You want to include the Category \u00a0\u00a0and Expense fields in the PivotTable.
\u00a0<\/p>\n

\u00a0\u00a0Use the Category and Expense fields, enabling Excel to determine where the \u00a0\u00a0fields go in the PivotTable.\u00a0<\/p>\n

5<\/p>\n

\u00a0<\/p>\n

7<\/p>\n

You want to display average expenses instead of totals.
\u00a0<\/p>\n

\u00a0\u00a0Modify the Values field to determine the average expense by category. Change \u00a0\u00a0the custom name to Average Expense<\/strong>.<\/p>\n

4<\/p>\n

\u00a0<\/p>\n

8<\/p>\n

Format the Values field with \u00a0\u00a0Accounting number type.<\/p>\n

4<\/p>\n

\u00a0<\/p>\n

9<\/p>\n

You want to display a meaningful label and select a \u00a0\u00a0different layout.
\u00a0<\/p>\n

\u00a0\u00a0Type Category<\/strong> \u00a0\u00a0in cell A3 and change the Grand Totals layout option to On for Rows Only.<\/p>\n

\u00a0<\/p>\n

5<\/p>\n

\u00a0<\/p>\n

10<\/p>\n

Apply Light Blue, Pivot Style \u00a0\u00a0Dark 2 and display banded rows.<\/p>\n

5<\/p>\n

\u00a0<\/p>\n

11<\/p>\n

To make it easy to filter the PivotTable results, you will \u00a0\u00a0insert and format a slicer.
\u00a0<\/p>\n

\u00a0\u00a0Insert a slicer for the Employee field, change the slicer height to 2<\/strong> \u00a0\u00a0inches and apply the Light Blue, Slicer Style Dark 5. Move the slicer so that \u00a0\u00a0the upper-left corner is in cell A10.<\/p>\n

6<\/p>\n

\u00a0<\/p>\n

12<\/p>\n

You want to insert another \u00a0\u00a0PivotTable to analyze the data on the Expenses worksheet.
\u00a0<\/p>\n

\u00a0\u00a0Use the Expenses worksheet to create another blank PivotTable on a sheet \u00a0\u00a0named Totals<\/strong>. \u00a0\u00a0Name the PivotTable Employees<\/strong>. Add the Employee to the Rows and \u00a0\u00a0add the Expense field to the Values area. Sort the PivotTable from largest to \u00a0\u00a0smallest expense.<\/p>\n

\u00a0<\/p>\n

10<\/p>\n

\u00a0<\/p>\n

13<\/p>\n

Change the name for the Expenses column to Totals<\/strong> \u00a0\u00a0and format the field with Accounting number format.<\/p>\n

\u00a0<\/p>\n

6<\/p>\n

\u00a0<\/p>\n

14<\/p>\n

Insert a calculated field to \u00a0\u00a0subtract 2659.72<\/strong> \u00a0\u00a0from the Totals field. Change the custom name to Above or Below Average<\/strong> and \u00a0\u00a0apply Accounting number format to the field.<\/p>\n

\u00a0<\/p>\n

10<\/p>\n

\u00a0<\/p>\n

15<\/p>\n

Set 10.86 <\/strong>(approximate) as the width for column \u00a0\u00a0B and 13.71<\/strong> \u00a0\u00a0for column C, change the row height of row 3 to 30<\/strong>, and apply word wrap to \u00a0\u00a0cell C3.<\/p>\n

4<\/p>\n

\u00a0<\/p>\n

16<\/p>\n

Now, you want to create a \u00a0\u00a0PivotChart to depict the data visually.
\u00a0<\/p>\n

\u00a0\u00a0Create a clustered column PivotChart from the PivotTable. Move the PivotChart \u00a0\u00a0to a new sheet named Chart<\/strong>. Hide all field buttons in the \u00a0\u00a0PivotChart, if necessary.<\/p>\n

8<\/p>\n

\u00a0<\/p>\n

17<\/p>\n

Add a chart title above the chart and type Expenses by \u00a0\u00a0Employee<\/strong>. Change the chart style to Style 14.<\/p>\n

2<\/p>\n

\u00a0<\/p>\n

18<\/p>\n

Apply 11 pt font size to the \u00a0\u00a0value axis and display the axis as Accounting with zero decimal places.<\/p>\n

\u00a0<\/p>\n

4<\/p>\n

\u00a0<\/p>\n

19<\/p>\n

Create a footer on all worksheets with your name in the \u00a0\u00a0left section, the sheet name code in the center section, and the file name \u00a0\u00a0code in the right section.<\/p>\n

\u00a0<\/p>\n

4<\/p>\n

\u00a0<\/p>\n

20<\/p>\n

Ensure that the worksheets are \u00a0\u00a0correctly named and placed in the following order in the workbook: Subtotals, \u00a0\u00a0Summary, Chart, Totals, Expenses.<\/p>\n

0<\/p>\n

\u00a0<\/p>\n

21<\/p>\n

Save and close Exp19_Excel_Ch05_CapAssessment_Travel.xlsx<\/em>. \u00a0\u00a0Exit Excel. Submit the file as directed.<\/p>\n

0<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

\u00a0\u00a0 You are the manager of an information technology (IT) team. Your employees go to training workshops and national conferences to keep up-to-date in the field. You created a list of expenses by category for each employee for the last six months. Now you want to subtotal the data to review total costs by employee […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31],"tags":[],"class_list":["post-7300","post","type-post","status-publish","format-standard","hentry","category-homework-help"],"_links":{"self":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/posts\/7300","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/comments?post=7300"}],"version-history":[{"count":0,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/posts\/7300\/revisions"}],"wp:attachment":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/media?parent=7300"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/categories?post=7300"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/tags?post=7300"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}