Bella Capelli Academy Robinson Module 11 Qualey Consulting Sales Report please find attached assignment details……………………………………..

Bella Capelli Academy Robinson Module 11 Qualey Consulting Sales Report please find attached assignment details…………………………………………………… New Perspectives Excel 2019 | Module 11: End of Module Project 1
Qualey Consulting
CREATE ADVANCED PIVOTTABLES AND USE DATABASE FUNCTIONS
Author: Isaiah ward
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the
file from the SAM website.
w copy of the
Q U ALEY CONSU LTING
SALES REPORT
Sales in $000s
Service
Brand positioning
Brand positioning
Brand positioning
Brand positioning
Brand positioning
Brand positioning
Brand research
Brand research
Brand research
Brand research
Brand research
Digital marketing
Digital marketing
Digital marketing
Digital marketing
Digital marketing
Digital marketing
Market assessment
Market assessment
Market assessment
Market assessment
Market assessment
Market assessment
Marketing plan
Marketing plan
Marketing plan
Marketing plan
Marketing plan
Marketing plan
Target marketing
Target marketing
Target marketing
Target marketing
Target marketing
Target marketing
Target marketing
Target marketing
Client ID
ASHBY
CLAUS
FLATH
KERRA
NEETN
RENOR
ASHBY
CLAUS
KERRA
NEETN
RENOR
ASHBY
CLAUS
HARRA
LUCIO
MIRSS
RENOR
CLAUS
FLATH
KERRA
LUCIO
MIRSS
WELLS
ASHBY
CLAUS
HARRA
LUCIO
MIRSS
RENOR
CLAUS
FLATH
HARRA
KERRA
LUCIO
MIRSS
NEETN
RENOR
Category
Utilities
Technology
Healthcare
Energy
Energy
Banking
Utilities
Technology
Energy
Energy
Banking
Utilities
Technology
Healthcare
Utilities
Technology
Banking
Technology
Healthcare
Energy
Utilities
Technology
Banking
Utilities
Technology
Healthcare
Utilities
Technology
Banking
Technology
Healthcare
Healthcare
Energy
Utilities
Technology
Energy
Banking
State
GA
NJ
GA
NY
FL
CT
GA
NJ
NY
FL
CT
GA
NJ
NJ
NY
FL
CT
NJ
GA
NY
NY
FL
NY
GA
NJ
NJ
NY
FL
CT
NJ
GA
NJ
NY
NY
FL
FL
CT
Start Date
2/5/2021
10/12/2021
2/12/2021
2/6/2021
3/8/2021
9/5/2021
4/1/2021
1/21/2021
2/6/2021
3/8/2021
4/20/2021
8/15/2021
1/21/2021
1/17/2021
3/2/2021
2/25/2021
2/24/2021
1/21/2021
2/12/2021
5/17/2021
8/25/2021
2/25/2021
3/10/2021
5/5/2021
9/8/2021
4/30/2021
3/7/2021
2/25/2021
2/24/2021
9/8/2021
8/24/2021
1/17/2021
7/20/2021
3/7/2021
9/1/2021
9/2/2021
2/24/2021
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
Qtr 1
3.985
5.675
4.875
1.275
3.450
2.070
5.465
5.065
3.255
3.045
2.550
1.975
3.270
5.055
1.275
2.245
8.565
6.500
7.675
4.860
2.475
3.455
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
Qtr 2
2.690
5.850
4.065
2.095
1.275
4.675
3.850
2.235
3.160
2.190
2.130
3.025
5.055
1.750
1.275
2.750
7.750
3.475
4.950
2.400
3.765
4.400
1.370
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
Qtr 3
1.470
4.590
2.135
3.560
5.065
3.450
1.450
6.775
3.055
3.345
5.055
2.775
2.750
3.240
1.275
7.950
1.260
2.540
7.050
4.875
3.575
2.040
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
Qtr 4
1.590
8.755
5.395
2.260
2.165
2.450
4.015
4.095
2.775
3.730
1.035
3.545
3.750
5.055
4.275
4.075
2.575
2.245
7.190
8.950
4.575
3.270
2.750
7.275
2.060
3.795
3.275
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
Total Sales
9.735
8.755
11.525
8.940
3.370
9.985
3.535
10.290
6.655
9.315
6.250
7.655
12.905
13.595
7.720
9.325
10.705
13.390
20.220
6.025
6.850
5.125
9.990
18.180
10.225
16.000
8.565
11.450
10.075
4.530
5.290
8.625
14.325
6.875
6.935
7.370
10.140
Category
Banking
Energy
Healthcare
Technology
Utilities
Total Sales
>10000
Projects:
Client ID
KERRA
Projects:
Service
Marketing plan
Total Sales:
State
NJ
Average Sales:
Projects
Total Sales Avg Sales
QUALEY CONSULTING
SALES BY CATEGORY
Sum of Total Sales
Category
Banking
Energy
Healthcare
Technology
Utilities
Grand Total
Start Date
2/24/2021
3/10/2021
4/20/2021
9/5/2021
2/6/2021
3/8/2021
5/17/2021
7/20/2021
9/2/2021
1/17/2021
2/12/2021
4/30/2021
8/24/2021
1/21/2021
2/25/2021
9/1/2021
9/8/2021
10/12/2021
2/5/2021
3/2/2021
3/7/2021
4/1/2021
5/5/2021
8/15/2021
8/25/2021
Service
Brand
positioning
Brand
research
Digital
Market
Marketing
marketing assessment
plan
$10.705
$10.075
$9.990
$6.250
$9.985
$8.940
$3.370
$6.655
$9.315
$6.025
$13.595
$11.525
$20.220
$16.000
$10.290
$12.905
$9.325
$13.390
$5.125
$11.450
$10.225
$8.755
$9.735
$7.720
$8.565
$3.535
$18.180
$7.655
$52.310
$36.045
$61.905
$6.850
$61.600
$74.495
Target
marketing Grand Total
$10.140
$30.920
$9.990
$6.250
$9.985
$15.595
$12.685
$6.025
$14.325
$14.325
$7.370
$7.370
$8.625
$22.220
$31.745
$16.000
$5.290
$5.290
$36.585
$25.900
$6.935
$6.935
$4.530
$14.755
$8.755
$9.735
$7.720
$6.875
$15.440
$3.535
$18.180
$7.655
$6.850
$64.090
$350.445
QUALEY CONSULTING
SALES BY CLIENT
Sum of Total Sales
Row Labels
ASHBY
CLAUS
FLATH
HARRA
KERRA
LUCIO
MIRSS
NEETN
RENOR
WELLS
Grand Total
Column Labels
CT
$47.155
$47.155
FL
GA
NJ
NY
Grand Total
$39.105
$39.105
$60.095
$60.095
$37.035
$37.035
$38.220
$38.220
$35.945
$35.945
$30.010
$30.010
$32.835
$32.835
$20.055
$20.055
$47.155
$9.990
$9.990
$52.890 $76.140 $98.315 $75.945
$350.445
QUALEY CONSULTING
SALES BY SERVICE
Row Labels
Brand positioning
Brand research
Digital marketing
Market assessment
Marketing plan
Target marketing
Grand Total
Q1
$15.810
$10.985
$15.890
$11.845
$22.740
$10.790
$88.060
Q4
$15.740
$10.890
$15.180
$21.975
$20.715
$22.425
$106.925
Annual
$52.310
$36.045
$61.905
$61.600
$74.495
$64.090
$350.445
New Perspectives Excel 2019 | Module 11: End of Module Project 1
Qualey Consulting
CREATE ADVANCED PIVOTTABLES AND USE DATABASE FUNCTIONS
GETTING STARTED
•
Open the file NP_EX19_EOM11-1_FirstLastName_1.xlsx, available for download
from the SAM website.
•
Save the file as NP_EX19_EOM11-1_FirstLastName_2.xlsx by changing the “1” to a
“2”.
o
•
If you do not see the .xlsx file extension in the Save As dialog box, do not type it.
The program will add the file extension for you automatically.
With the file NP_EX19_EOM11-1_FirstLastName_2.xlsx still open, ensure that your
first and last name is displayed in cell B6 of the Documentation sheet.
o
If cell B6 does not display your name, delete the file and download a new copy
from the SAM website.
PROJECT STEPS
1.
Nadia Ivanov is a partner at Qualey Consulting, a consulting firm with headquarters in
Hoboken, New Jersey. She uses an Excel workbook to track sales related to consulting
projects and asks for your help in summarizing project data. To do so, you will use
database functions and advanced PivotTable features.
Go to the Sales Report worksheet, which contains a table named Sales listing details
about consulting projects completed in 2021. In the range M3:P8, Nadia wants to
summarize project information. Start by calculating the number projects for clients in
each business category. In cell N4, enter a formula using the COUNTIF function that
counts the number of projects for clients in the Banking category, checking that the
Category column in the Sales table (Sales[Category]) is equal to the value in cell M4.
Fill the range N5:N8 with the formula in cell N4.
2.
In column O, Nadia wants to calculate the total sales for each category. In cell O4, enter
a formula using the SUMIF function that totals the sales for Banking projects, checking
that the Category column in the Sales table is equal to the value in cell M4, and that the
formula totals the sales for all four quarters (Sales[Total Sales]). Fill the range O5:O8
with the formula in cell O4.
3.
In column P, Nadia wants to calculate the average sales for each category. In cell P4,
enter a formula using the AVERAGEIF function that averages the sales for Banking
projects, checking that the Category column in the Sales table is equal to the value in
cell M4, and that the formula averages the sales for all four quarters (Sales[Total
Sales]). Fill the range P5:P8 with the formula in cell P4.
4.
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.
5.
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.
New Perspectives Excel 2019 | Module 11: End of Module Project 1
6.
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.
7.
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.
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.]
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.
New Perspectives Excel 2019 | Module 11: End of Module Project 1
Final Figure 1: Sales Report Worksheet
© Zavadskyi Ihor/Shutterstock.com
New Perspectives Excel 2019 | Module 11: End of Module Project 1
Final Figure 2: Sales by Category Worksheet
New Perspectives Excel 2019 | Module 11: End of Module Project 1
Final Figure 3: Sales by Client Worksheet
New Perspectives Excel 2019 | Module 11: End of Module Project 1
Final Figure 4: Sales by Service Worksheet

Purchase answer to see full
attachment