Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

8. Go to the Sales by Category worksheet. Nadia has created a PivotTable named CategoryPivot to list the sales by business category, date, and service

8. Go to the Sales by Category worksheet. Nadia has created a PivotTable named CategoryPivot to list the sales by business category, date, and service type. The data would be easier to interpret if the dates appeared as quarter numbers. Group the Start Date field values by Quarters only. Change the report layout to Compact Form to make the PivotTable look less cluttered.

9. Go to the Sales by Client worksheet. Nadia created a PivotTable named ClientPivot that lists sales by client and state, but she wants to simplify the PivotTable by displaying the sales by client and region. Manually group the CT, NJ, and NY column labels (cells B3, E3, and F3). Use North as the name of the group. Manually group the GA and FL column labels in row 4 and use South as the name of the group. Remove the State field from the Columns area. Rename the State2 field to use Region as the custom name. Sort the South values in ascending order to list first the North sales amounts in column B followed by the South sales amounts in column C. Hide the field headers to further streamline the layout.

10. Go to the Sales by Service worksheet, which contains a PivotTable named ServicePivot that compares the Quarter 1, Quarter 4, and Annual sales for each service provided in 2021. Nadia wants to know the difference and the percentage of difference between the Quarter 4 and Quarter 1 sales and display the average total sales for each service. Insert a calculated field named Difference that subtracts the Qtr1 field amount from the Qtr4 field amount. In cell E2, use +/- Q4 to Q1 as the column label.

11. Insert another calculated field named % Difference that subtracts the Qtr 1 field amounts from the Qtr 4 field amounts, and then divides the result by the Qtr 1 field amount. In cell F2, use % Q4 to Q1 as the column label. Change the number format of the % Q4 to Q1 amounts to Percentage with 2 decimal points.

12. Add the Total Sales field to the Values area of the PivotTable, making it the last field in the Values area. Use the Average calculation to summarize the Total Sales field data. Move the Annual field so that it is listed as the last field in the Values area.

13. Nadia also wants to display details about the two services with the highest total sales. Apply a Value Filter to the Service field that displays the top 2 items by the Annual sales amount. [Mac hint - Sort the filter by Descending.]

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Effective Auditing The Simple Systems Series Book 5

Authors: Jennie Clark CQP

1st Edition

B09YHJR18Y, 979-8802614082

More Books

Students also viewed these Accounting questions

Question

Research RAID 6 and its second parity computation.

Answered: 1 week ago