- 4 12 Back Instructions Np Ex19 Eom11 1 Nadia Ivanov Is A Partner At Qualey Consulting A Consulting Firm With Headq 1 (58.16 KiB) Viewed 202 times
- 4 12 Back Instructions Np Ex19 Eom11 1 Nadia Ivanov Is A Partner At Qualey Consulting A Consulting Firm With Headq 2 (56.88 KiB) Viewed 202 times
- 4 12 Back Instructions Np Ex19 Eom11 1 Nadia Ivanov Is A Partner At Qualey Consulting A Consulting Firm With Headq 3 (58.91 KiB) Viewed 202 times
- 4 12 Back Instructions Np Ex19 Eom11 1 Nadia Ivanov Is A Partner At Qualey Consulting A Consulting Firm With Headq 4 (39.54 KiB) Viewed 202 times
4:12 Back Instructions_NP_EX19_EOM11-... Nadia needs to identify the number of projects that have total sales more than $10,000 and those for client KERRA. In cell N12, create a formula using the DCOUNT function to count the number of projects with total sales of more than $10,000, using the data in the entire Sales table (Sales[#All]) and counting the values in the Total Sales column ("Total Sales") that are equal to the values in the range M10:M11 In cell N16, create a formula using the DCOUNTA function to count the number of projects for client KERRA, using the data in the entire Sales table (Sales[#All]) and counting the values in the Client ID column ("Client ID") that are equal to the values in the range M14:M15. Nadia also needs to calculate the total sales for marketing plans and the average sales for clients in New Jersey. In cell N20, create a formula using the DSUM function to calculate the total sales for marketing plans using the data in the entire Sales table (Sales[#All]) and totaling the values in the Total Sales column ("Total Sales") for services equal to the values in the range M18:M19. In cell N24, create a formula using the DAVERAGE function to calculate the average sales for clients in New Jersey. using the data in the entire Sales table (Sales[#All]) and averaging the values in the Total Sales column ("Total Sales") for clients in states equal to the values in the range M22:M23 Go to the Sales by Category worksheet. Nadia has created a Pivot Table 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 Pivot Table look less cluttered. Go to the Sales by Client worksheet. Nadia created a PivotTable named Client Pivot that lists sales client and state ut she wants
4:12 Back Instructions_NP_EX19_EOM11-... Go to the Sales by Client worksheet. Nadia created a Pivot Table named ClientPivot that lists sales by client and state, but she wants to simplify the Pivot Table by displaying the sales by client and region. Manually group the CT, N), 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 Pivot Table 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 Qtri field amount from the Qtr4 field amount. In cell E2, use +/- Q4 to Q1 as the column label. 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 % 24 to Q1 as the column label. Change the number format of the % 04 to 01 amounts to Percentage with 2 decimal points. 12. Add the Total Sales field to the Values area of the Pivot Table, 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. 1. 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. er by
4:12 Back Instructions_NP_EX19_EOM11-... 11 Insert another calculareg nieg named 4 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 % 04 to 01 as the column label. Change the number format of the % 04 to Q1 amounts to Percentage with 2 decimal points 12 Add the Total Sales field to the Values area of the Pivot Table, 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. 12 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, Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project. . Final Figure 1: Sales Report Worksheet Zavadskyl thor/Shutterstock.com - Final Figure 2: Sales by Category Worksheet • Final Figure 3: Sales by Client Worksheet Final Figure 4: Sales by Service Worksheet