# The Assignment Is Designed To Gain Additional Proficiency In The Use Of Ms Excel Tables And Pivot Tables.

Posted Under: Operations Management

Order Now

#### Related Study Services

DESCRIPTION
Posted
Modified
Viewed 13
Excel Tables The Orders worksheet shows orders from a SuperStore. Some research needs to be done to locate a group of information. A. Convert the Orders dataset into an Excel Table (remember you only need to select one cell to create excel table) B. 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. C. Format the Sales and Profit to be currency (\$) D. Enter customer name with the most distinct product id from this filtered set into Page 1 of 2 cell A30 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). F Apply the Blue, Table Style Medium 9 to the table. Pivot Tables and Pivot Charts Pivot Table 1 : A. 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 B. Filter the information so that only Office Supplies is displayed. C. Which sub-category of Office Supplies has the most sales? Answer in cell J1 of this sheet. D. Format the Sum of Sales and Profit column to be currency (\$) E. Rename the PivotTable's worksheet: Pivot Table 1. Pivot Table 2 & Pivot Chart: A. 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 (\$) B. In cell J1 of this sheet put in the state that has the highest average coffee sales \$ C. Collapse the Product rows and create a 3D Clustered Column Pivot Chart. D. Apply Chart Style 5 and Quick Layout 3. E. Enter as Title of the Chart: Average Sales Totals. F. Rename the PivotTable's worksheet: Pivot Table 2. G. Move the Pivot Chart to its own worksheet. Rename this new worksheet as Pivot Chart.
Attachments