Computer Science Homework Help

Directions:

• Follow the requirements listed on the next page.
• Create the Excel Table and the Pivot Tables as indicated.
• Enter your name on cell J1 of the worksheets.
• Submit your assignment11.xlsx file to Canvas using the Canvas-> Assignments link.Requirements: Excel TablesThe Orders worksheet shows orders from a SuperStore. Some research needs to be done to locate a group of information.
  1. Convert the Orders dataset into an Excel Table (remember you only need to select one cell to create excel table)
  2. Then, locate the records of the Orders dataset that comply with all the following characteristics:
     1. City is New York or Philadelphia.
     2. Segment is Corporate.
  3. Format the Sales and Profit to be currency ($)
  4. Enter customer name with the most distinct product id from this filtered set into Page 1 of 2

cell A30

1. E Add a Total row and use the properties of the Total Row to calculate:
   1. the sum of sales (Column R).
   2. the sum of profit (column U).
2. F Apply the Blue, Table Style Medium 9 to the table.

Pivot Tables and Pivot Charts

Pivot Table 1 :

1. Use the Orders worksheet and create a Pivot Table on new worksheet with a filter on Category, row of Sub-Category, and sum of Quantity, Sum of Sales, Sum of Profit
2. Filter the information so that only “Office Supplies” is displayed.
3. Which sub-category of Office Supplies has the most sales? Answer in cell J1 of thissheet.
4. Format the Sum of Sales and Profit column to be currency ($)
5. Rename the PivotTable’s worksheet: Pivot Table 1.

Pivot Table 2 & Pivot Chart:

1. Use the West Coast Coffee worksheet and create another Pivot Table that displays the average of Sales $ organized by: Product and Month as row labels (in that order) and State as column labels. Format all sales numbers as currency ($)
2. In cell J1 of this sheet put in the state that has the highest average coffee sales $
3. Collapse the Product rows and create a 3D Clustered Column Pivot Chart.
4. Apply Chart Style 5 and Quick Layout 3.
5. Enter as Title of the Chart: Average Sales Totals.
6. Rename the PivotTable’s worksheet: Pivot Table 2.
7. Move the Pivot Chart to its own worksheet. Rename this new worksheet as Pivot Chart.

References:

1. Chapters 13 and 14 of the Fluency7 textbook
2. Support.office.com Table and Pivot Table Tutorials
3. Lectures and Recitations