A Skills Approach: Excel 2016
Chapter 8: Exploring Advanced Data Analysis
Skill Review 8.1
In this project, you will analyze sales data using consolidation, subtotals, outlines, conditional formatting,
advanced filters, PivotTables, and PivotCharts.
Skills needed to complete this project:
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Step 1
Download
start file
Using Consolidate to Create Subtotals
Sorting Data on Multiple Criteria
Adding Subtotals
Creating an Outline
Creating New Conditional Formatting Rules
Filtering and Sorting Using Cell Attributes
Managing Conditional Formatting Rules
Using Advanced Filter
Refreshing Data in a PivotTable
Adding a Calculated Field to a PivotTable
Changing the Look of a PivotTable
Filtering PivotTable Data
Filtering Data in a PivotChart
Changing the Look of a PivotChart
This image appears when a project instruction has changed to accommodate an update to
Microsoft Office 365. If the instruction does not match your version of Office, try using the alternate instruction
instead.
1. Open the start file EX2016-SkillReview-8-1. The file will be renamed automatically to include your name.
Change the project file name if directed to do so by your instructor, and save it.
2. If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the top
of the workbook so you can modify the workbook.
3. Use consolidate to create a summary of the sales data by region. The summary will be located on the
Analysis worksheet.
a.
Go to the Analysis worksheet, and select cell A2.
b. On the Data tab, in the Data Tools group, click the Consolidate button.
c. Verify that Sum is selected in the Function box.
d. If there are any references in the All references box, click each and then click the [Delete] button to
remove them.
e. Click in the Reference box, and then click the Sales Data worksheet tab. Click and drag to select cells
C3:E67.
f.
Click both the Top row and Left column check boxes.
g. Click OK.
h. On the Analysis worksheet, delete cells B2:B10, allowing the other cells to shift left.
1 | Page
Skill Review 8.1
Last Updated 4/4/18
A Skills Approach: Excel 2016
Chapter 8: Exploring Advanced Data Analysis
4. Sort the sales data alphabetically by region and then by last name.
a.
Go to the Sales Data worksheet, and click any cell in the data set.
b. On the Data tab, in the Sort & Filter group, click the Sort button.
c. In the Sort dialog, expand the Sort by list, and select Region.
d. Click the Add Level button.
e. Expand the Then by list, and select Last Name.
f.
Click OK.
5. Add subtotals to the data to calculate the total commission earned for each sales associate.
a.
On the Data tab, in the Outline group, click the Subtotal button.
b. Expand the At each change in list, and select Last Name.
c. Verify that Sum is selected in the Use function box.
d. Verify that there is a check mark next to Commission Earned in the Add subtotal to box.
e. Click OK.
6. Copy the subtotal data to the Analysis worksheet.
a.
On the Sales Data worksheet, click the outline level 2 button to collapse the list so only the total
commission earned for each sales associate is visible. This will make it easier to copy the data.
b. Select cells A3:G77 and copy them to the Clipboard. Do not include the Grand Total row (row 78).
c. Go to the Analysis worksheet and paste the copied data beginning in cell A13.
7. Notice that when you paste the subtotaled data, Excel removes the subtotal grouping, but keeps the
subtotal rows and formulas. You can use the Outline command to re‐create groups based on the subtotal
formulas.
a.
Click any cell in the data range.
b. On the Data tab, in the Outline group, click the Group button arrow, and select Auto Outline.
c. Click the outline level 1 button to collapse the list so only the total commission earned for each sales
associate is visible.
8. On the Commissions worksheet, create a new conditional formatting rule to apply an icon set to the values in
the Rating column.
a.
Go to the Commissions worksheet and select cells H3:H66.
b. On the Home tab, in the Styles group, click the Conditional Formatting button. Click New Rule ...
c. In the Select a Rule Type box, verify that Format all cells based on their values is selected.
d. In the Edit the Rule Description box, expand the Format Style list, and select Icon Sets.
e. Expand the Icon Style list, and select Three Stars.
f.
Click the Show Icon Only checkbox to hide the values in the selected cells.
g. In the Display each icon according to these rules section, expand the first Type list and select Number.
2 | Page
Skill Review 8.1
Last Updated 4/4/18
A Skills Approach: Excel 2016
Chapter 8: Exploring Advanced Data Analysis
h. In the first Value box type 9000 to display a completely filled gold star when the cell value is greater
than or equal to 9,000.
i.
Expand the second Type list and select Number.
j.
In the second Value box, type 4500 to display a partially filled gold star when the cell value is less
than 9,000 and greater than or equal to 4,500.
k. Click OK.
9. Sort the sales data so cells with the completely filled gold star icon appear first.
a.
Scroll down and find a cell with a completely filled gold star.
b. Right-click a cell with a completely filled gold star, point to Sort, and select Put Selected Cell Icon
On Top.
10. On the Sales Data Filter worksheet, delete the conditional formatting rule that applies a font format to cells
where the value is above average.
a.
Go to the Sales Data Filter worksheet.
b. On the Home tab, in the Styles group, click the Conditional Formatting button. Click Manage
Rules... to open the Conditional Formatting Rules Manager.
c. If necessary, expand the Show formatting rules for list and select This Worksheet.
d. Select the Above Average rule and click the Delete Rule button.
e. Click OK.
11. Use the Advanced Filter feature to find sales greater than $110,000 for George Anderson or Xin Zhu.
a.
Insert four new rows above row 3, so the column label row is now row 7 and there are five blank rows
between the title and the column labels. If necessary, clear all formatting from the new rows.
b. Copy the range A7:G7 (the column labels) and paste in A3:G3.
c. Enter the filter criteria in A4:G5 as follows:
i. In A4, type: George
ii. In B4, type: Anderson
iii. In E4, type: >110000
iv. In A5, type: Xin
v. In B5, type: Zhu
vi. In E5, type: >110000
d. Click any cell in the data set.
e. On the Data tab, in the Sort & Filter group, click the Advanced button.
f.
Verify that the Filter the list, in place radio button is selected.
g. The List range box should display $A$7:$G$71. If it does not, click in the List range box, and then
click and drag to select cells A7:G71.
h. Click in the Criteria range box. Click and drag to select the range A3:G5 as the criteria range.
i.
Click OK.
3 | Page
Skill Review 8.1
Last Updated 4/4/18
A Skills Approach: Excel 2016
Chapter 8: Exploring Advanced Data Analysis
12. The data in the Sales Data worksheet has changed since the PivotTable was created. Refresh the PivotTable
to reflect the changes.
a.
Go to the PivotTable worksheet and click anywhere in the PivotTable.
b. On the PivotTable Tools Analyze tab, in the Data group, click the Refresh button.
c. In the PivotTable Fields pane, click Last Name to add the Last Name field to the Rows box.
d. In the Rows box, click Last Name and move it above Client.
13. Add a calculated field to determine the average commission rate for each row in the PivotTable.
a.
On the PivotTable Tools Analyze tab, in the Calculations group, click the Fields, Items, & Sets button.
b. Click Calculated Field...
c. In the Name box, type: Average Commission Calculated
d. In the Fields box, click Commission Earned, and then click the Insert Field button.
e. Type: /
f.
In the Fields box, double‐click Annual Sales.
g. The final formula in the Formula box should look like this:
='Commission Earned'/'Annual Sales'
h. Click OK to add the calculated field to the PivotTable.
i.
Click any cell in the new calculated column.
j.
On the PivotTable Tools Analyze tab, in the Active Field group, click the Field Settings button to open
the Value Field Settings dialog.
k. In the Custom Name box, type: Average Commission
l.
Click the Number Format button at the bottom‐left corner of the dialog. In the Category list, click
Percentage. If necessary, type 2 in the Decimal places box.
m. Click OK.
n. Click OK again to close the Value Field Settings dialog.
14. Apply a Quick Style to the PivotTable.
a.
Click anywhere in the PivotTable.
b. On the PivotTable Tools Design tab, in the PivotTable Styles group, click the More button to expand the
gallery.
c. Click Sky Blue, Pivot Style Medium 6.
Click Pivot Style Medium 6.
15. Use slicers to filter the PivotTable by region to show only the NW1 region.
a.
On the PivotTable Tools Analyze tab, in the Filter group, click the Insert Slicer button.
b. In the Insert Slicers dialog, click the Region check box. Click OK.
c. In the Region slicer, click the NW1 button
4 | Page
Skill Review 8.1
Last Updated 4/4/18
A Skills Approach: Excel 2016
Chapter 8: Exploring Advanced Data Analysis
16. Filter the PivotChart data to show only the NW1 and NW2 regions.
a.
Go to the PivotChart worksheet and select the chart.
b. On the PivotChart Tools Analyze tab, in the Filter group, click the Insert Slicer button.
c. In the Insert Slicers dialog, click the Region check box. Click OK.
d. In the Region slicer, click the NW1 button. Press the [Ctrl] key and click the NW2 button.
17. Apply a Quick Style to the PivotChart.
a.
Select the PivotChart.
b. On the PivotChart Tools Design tab, in the Chart Styles group, click the More button to expand
the gallery.
c. Click Style 11.
Step 2
Upload &
Save
Step 3
Grade my
Project
18. Save and close the workbook.
19. Upload and save your project file.
20. Submit project for grading.
5 | Page
Skill Review 8.1
Last Updated 4/4/18
Sales by Region
Sales by Sales Associate
National Pharmaceutical Sales
First Name
Jan
George
Marques
Tatisha
Matthew
Matthew
George
Matthew
Tatisha
Matthew
Jan
Marques
Marques
Marques
George
Jan
Raminian
Tatisha
Matthew
Tatisha
George
Tatisha
Matthew
Jan
Tatisha
Xin
Raminian
Marques
Marques
Raminian
Tatisha
Xin
Jan
Raminian
Raminian
Marques
George
Jan
Julieta
Jan
Julieta
Julieta
Last Name
Johansen
Anderson
Quarles
Brown
Schmidt
Schmidt
Anderson
Schmidt
Brown
Schmidt
Johansen
Quarles
Quarles
Quarles
Anderson
Johansen
Ahjab
Brown
Schmidt
Brown
Anderson
Brown
Schmidt
Johansen
Brown
Zhu
Ahjab
Quarles
Quarles
Ahjab
Brown
Zhu
Johansen
Ahjab
Ahjab
Quarles
Anderson
Johansen
Lopez
Johansen
Lopez
Lopez
Region
SW2
SW1
NE2
SE1
NW2
NW2
SW1
NW2
SE1
NW2
SW2
NE1
NE2
NE2
SW1
SW2
NE1
SE1
NW2
SE1
SW1
SE1
NW2
SW2
SE1
SE2
NE1
NE1
NE2
NE2
SE1
SE2
SW2
NE1
NE1
NE1
SW1
SW2
NW1
SW2
NW1
NW1
Client
Ambulatory Care of TX, Inc.
AZ Ambulatory Care
Baltimore Bariatrics, Inc.
Bama Chiropractic Clinics, Inc.
Bellevue Bariatics, Inc.
Boise Oncology
CA Chiropractic Associates, Inc.
Casper Cardiology, Inc.
Bama Chiropractic Clinics, Inc.
Cheyenne Cardiologists, Inc.
CO Pharmacies, Inc.
Connecticut Pulmonary Associates, Inc.
DC Dialysis
Delaware Dental Associates
Emergency Medicine Association
EMTS of CO, Inc.
Garden State Pharmacies
Gerontology Associates of MS
ID Internal Medicine Associates
Internal Medicine of MS
Joint Healthcare Associates
LA Cardiologists, Inc.
Laramie EMTs, Inc.
Lone Star Medicine
Louisiana Dermatologists, Inc.
Low County Medical Associates, Inc.
MA OBGyn Associates
Maine Medical Associates, Inc.
Maryland Pharmacies, Inc.
MD Dental Associates
MS Women's Clinics, Inc.
NC Medical Associates
Nephrology Associates, Inc.
New England Pharmacies, Inc.
New England Physicians, Inc.
Maine Medical Associates, Inc.
NM Pharmacies, Inc.
NV Emergency Medical Associates, Inc.
OBGyn Associates of Montana, Inc.
OK Clinics
Oregon Opthamology, Inc.
Orthodontial, Inc.
Xin
Raminian
Xin
Xin
Jan
Tatisha
Xin
Marques
Xin
Julieta
Julieta
George
George
Jan
George
Xin
Julieta
Julieta
Tatisha
Matthew
Matthew
Xin
Zhu
Ahjab
Zhu
Zhu
Johansen
Brown
Zhu
Quarles
Zhu
Lopez
Lopez
Anderson
Anderson
Johansen
Anderson
Zhu
Lopez
Lopez
Brown
Schmidt
Schmidt
Zhu
SE2
NE2
SE2
SE2
SW2
SE1
SE2
NE2
SE2
NW1
NW1
SW1
SW1
SW2
SW1
SE2
NW1
NW1
SE1
NW2
NW2
SE2
Orthopaedic Medical Associates
PA Medical Practices, Inc.
Palmetto Physicans
Palmetto Podiatrists, Inc.
Pharmacies of Nevada, Inc.
Physicians of MS, Inc.
Piedmont Health Associates, Inc.
Rhode Island Gastroenterology, Inc.
SC Medical Associates
Seattle Psychiatric Associates, Inc.
Seattle Psychologists, Inc.
Southwest Dermatology
Southwest Medical Associates
TEX-MEX Pharmacies, Inc.
Tri-state Pharmacies
Upstate Pharmacies, Inc.
Utah Urology Associates, Inc.
Washington Physicians, Inc.
Women's Medicine of FL, Inc.
WY OBGyn, Inc.
Wyoming Health Care, Inc.
NC Medical Associates
al Sales
Annual Sales
156,208
91,222
200,708
157,721
152,101
108,878
107,373
207,555
153,211
114,411
106,266
103,301
162,202
215,609
115,609
152,686
122,222
127,555
185,272
169,272
142,734
162,777
201,211
133,991
118,118
82,625
128,979
165,907
209,708
133,288
133,288
151,827
87,556
91,995
97,489
165,907
115,975
114,111
88,888
105,767
96,606
83,363
Commission
Rate
4%
2%
6%
4%
4%
2%
2%
6%
4%
2%
2%
2%
4%
6%
2%
4%
2%
2%
4%
4%
4%
4%
6%
2%
2%
2%
2%
4%
6%
2%
2%
4%
2%
2%
2%
4%
2%
2%
2%
2%
2%
2%
Commission
Earned
6,248.32
1,824.44
12,042.48
6,308.84
6,084.04
2,177.56
2,147.46
12,453.30
6,128.44
2,288.22
2,125.32
2,066.02
6,488.08
12,936.54
2,312.18
6,107.44
2,444.44
2,551.10
7,410.88
6,770.88
5,709.36
6,511.08
12,072.66
2,679.82
2,362.36
1,652.50
2,579.58
6,636.28
12,582.48
2,665.76
2,665.76
6,073.08
1,751.12
1,839.90
1,949.78
6,636.28
2,319.50
2,282.22
1,777.76
2,115.34
1,932.12
1,667.26
87,773
88,878
165,700
82,935
115,234
141,111
98,333
91,708
115,272
97,443
97,002
108,277
82,651
153,848
87,442
89,695
82,319
89,107
109,333
176,168
111,111
151,827
2%
2%
4%
2%
2%
4%
2%
2%
2%
2%
2%
2%
2%
4%
2%
2%
2%
2%
2%
4%
2%
4%
1,755.46
1,777.56
6,628.00
1,658.70
2,304.68
5,644.44
1,966.66
1,834.16
2,305.44
1,948.86
1,940.04
2,165.54
1,653.02
6,153.92
1,748.84
1,793.90
1,646.38
1,782.14
2,186.66
7,046.72
2,222.22
6,073.08
National Pharmaceutical Sales
First Name
Jan
George
Marques
Tatisha
Matthew
Matthew
George
Matthew
Tatisha
Matthew
Jan
Marques
Marques
Marques
George
Jan
Raminian
Tatisha
Matthew
Tatisha
George
Tatisha
Matthew
Jan
Tatisha
Xin
Raminian
Marques
Marques
Raminian
Tatisha
Xin
Jan
Raminian
Raminian
Marques
George
Jan
Julieta
Jan
Julieta
Julieta
Last Name
Johansen
Anderson
Quarles
Brown
Schmidt
Schmidt
Anderson
Schmidt
Brown
Schmidt
Johansen
Quarles
Quarles
Quarles
Anderson
Johansen
Ahjab
Brown
Schmidt
Brown
Anderson
Brown
Schmidt
Johansen
Brown
Zhu
Ahjab
Quarles
Quarles
Ahjab
Brown
Zhu
Johansen
Ahjab
Ahjab
Quarles
Anderson
Johansen
Lopez
Johansen
Lopez
Lopez
Region
SW2
SW1
NE2
SE1
NW2
NW2
SW1
NW2
SE1
NW2
SW2
NE1
NE2
NE2
SW1
SW2
NE1
SE1
NW2
SE1
SW1
SE1
NW2
SW2
SE1
SE2
NE1
NE1
NE2
NE2
SE1
SE2
SW2
NE1
NE1
NE1
SW1
SW2
NW1
SW2
NW1
NW1
Client
Ambulatory Care of TX, Inc.
AZ Ambulatory Care
Baltimore Bariatrics, Inc.
Bama Chiropractic Clinics, Inc.
Bellevue Bariatics, Inc.
Boise Oncology
CA Chiropractic Associates, Inc.
Casper Cardiology, Inc.
Bama Chiropractic Clinics, Inc.
Cheyenne Cardiologists, Inc.
CO Pharmacies, Inc.
Connecticut Pulmonary Associates, Inc.
DC Dialysis
Delaware Dental Associates
Emergency Medicine Association
EMTS of CO, Inc.
Garden State Pharmacies
Gerontology Associates of MS
ID Internal Medicine Associates
Internal Medicine of MS
Joint Healthcare Associates
LA Cardiologists, Inc.
Laramie EMTs, Inc.
Lone Star Medicine
Louisiana Dermatologists, Inc.
Low County Medical Associates, Inc.
MA OBGyn Associates
Maine Medical Associates, Inc.
Maryland Pharmacies, Inc.
MD Dental Associates
MS Women's Clinics, Inc.
NC Medical Associates
Nephrology Associates, Inc.
New England Pharmacies, Inc.
New England Physicians, Inc.
Maine Medical Associates, Inc.
NM Pharmacies, Inc.
NV Emergency Medical Associates, Inc.
OBGyn Associates of Montana, Inc.
OK Clinics
Oregon Opthamology, Inc.
Orthodontial, Inc.
Xin
Raminian
Xin
Xin
Jan
Tatisha
Xin
Marques
Xin
Julieta
Julieta
George
George
Jan
George
Xin
Julieta
Julieta
Tatisha
Matthew
Matthew
Xin
Zhu
Ahjab
Zhu
Zhu
Johansen
Brown
Zhu
Quarles
Zhu
Lopez
Lopez
Anderson
Anderson
Johansen
Anderson
Zhu
Lopez
Lopez
Brown
Schmidt
Schmidt
Zhu
SE2
NE2
SE2
SE2
SW2
SE1
SE2
NE2
SE2
NW1
NW1
SW1
SW1
SW2
SW1
SE2
NW1
NW1
SE1
NW2
NW2
SE2
Orthopaedic Medical Associates
PA Medical Practices, Inc.
Palmetto Physicans
Palmetto Podiatrists, Inc.
Pharmacies of Nevada, Inc.
Physicians of MS, Inc.
Piedmont Health Associates, Inc.
Rhode Island Gastroenterology, Inc.
SC Medical Associates
Seattle Psychiatric Associates, Inc.
Seattle Psychologists, Inc.
Southwest Dermatology
Southwest Medical Associates
TEX-MEX Pharmacies, Inc.
Tri-state Pharmacies
Upstate Pharmacies, Inc.
Utah Urology Associates, Inc.
Washington Physicians, Inc.
Women's Medicine of FL, Inc.
WY OBGyn, Inc.
Wyoming Health Care, Inc.
NC Medical Associates
al Sales
Annual Sales
156,208
91,222
200,708
157,721
152,101
108,878
107,373
207,555
157,721
114,411
106,266
103,301
162,202
215,609
121,252
152,686
122,222
127,555
185,272
169,272
142,734
162,777
201,211
133,991
118,118
82,625
128,979
165,907
209,708
133,288
133,288
151,827
86,288
91,995
97,489
165,907
115,975
114,111
88,888
105,767
96,606
83,363
Commission
Rate
4%
2%
6%
4%
4%
2%
2%
6%
4%
2%
2%
2%
4%
6%
2%
4%
2%
2%
4%
4%
4%
4%
6%
2%
2%
2%
2%
4%
6%
2%
2%
4%
2%
2%
2%
4%
2%
2%
2%
2%
2%
2%
Commission
Earned
6,248.32
1,824.44
12,042.48
6,308.84
6,084.04
2,177.56
2,147.46
12,453.30
6,308.84
2,288.22
2,125.32
2,066.02
6,488.08
12,936.54
2,425.04
6,107.44
2,444.44
2,551.10
7,410.88
6,770.88
5,709.36
6,511.08
12,072.66
2,679.82
2,362.36
1,652.50
2,579.58
6,636.28
12,582.48
2,665.76
2,665.76
6,073.08
1,725.76
1,839.90
1,949.78
6,636.28
2,319.50
2,282.22
1,777.76
2,115.34
1,932.12
1,667.26
87,773
88,878
165,700
82,935
112,788
141,111
98,333
91,708
115,272
95,292
97,002
108,277
82,651
153,848
87,442
89,695
82,319
89,107
109,333
171,171
111,111
151,827
2%
2%
4%
2%
2%
4%
2%
2%
2%
2%
2%
2%
2%
4%
2%
2%
2%
2%
2%
4%
2%
4%
1,755.46
1,777.56
6,628.00
1,658.70
2,255.76
5,644.44
1,966.66
1,834.16
2,305.44
1,905.84
1,940.04
2,165.54
1,653.02
6,153.92
1,748.84
1,793.90
1,646.38
1,782.14
2,186.66
6,846.84
2,222.22
6,073.08
National Pharmaceutical Sales
First Name
Raminian
Raminian
Raminian
Raminian
Raminian
Raminian
George
George
George
George
George
George
George
George
Tatisha
Tatisha
Tatisha
Tatisha
Tatisha
Tatisha
Tatisha
Tatisha
Tatisha
Jan
Jan
Jan
Jan
Jan
Jan
Jan
Jan
Jan
Julieta
Julieta
Julieta
Julieta
Julieta
Julieta
Julieta
Marques
Marques
Marques
Marques
Last Name
Ahjab
Ahjab
Ahjab
Ahjab
Ahjab
Ahjab
Anderson
Anderson
Anderson
Anderson
Anderson
Anderson
Anderson
Anderson
Brown
Brown
Brown
Brown
Brown
Brown
Brown
Brown
Brown
Johansen
Johansen
Johansen
Johansen
Johansen
Johansen
Johansen
Johansen
Johansen
Lopez
Lopez
Lopez
Lopez
Lopez
Lopez
Lopez
Quarles
Quarles
Quarles
Quarles
Region
NE1
NE1
NE2
NE1
NE1
NE2
SW1
SW1
SW1
SW1
SW1
SW1
SW1
SW1
SE1
SE1
SE1
SE1
SE1
SE1
SE1
SE1
SE1
SW2
SW2
SW2
SW2
SW2
SW2
SW2
SW2
SW2
NW1
NW1
NW1
NW1
NW1
NW1
NW1
NE2
NE1
NE2
NE2
Client
Garden State Pharmacies
MA OBGyn Associates
MD Dental Associates
New England Pharmacies, Inc.
New England Physicians, Inc.
PA Medical Practices, Inc.
AZ Ambulatory Care
CA Chiropractic Associates, Inc.
Emergency Medicine Association
Joint Healthcare Associates
NM Pharmacies, Inc.
Southwest Dermatology
Southwest Medical Associates
Tri-state Pharmacies
Bama Chiropractic Clinics, Inc.
Bama Chiropractic Clinics, Inc.
Gerontology Associates of MS
Internal Medicine of MS
LA Cardiologists, Inc.
Louisiana Dermatologists, Inc.
MS Women's Clinics, Inc.
Physicians of MS, Inc.
Women's Medicine of FL, Inc.
Ambulatory Care of TX, Inc.
CO Pharmacies, Inc.
EMTS of CO, Inc.
Lone Star Medicine
Nephrology Associates, Inc.
NV Emergency Medical Associates, Inc.
OK Clinics
Pharmacies of Nevada, Inc.
TEX-MEX Pharmacies, Inc.
OBGyn Associates of Montana, Inc.
Oregon Opthamology, Inc.
Orthodontial, Inc.
Seattle Psychiatric Associates, Inc.
Seattle Psychologists, Inc.
Utah Urology Associates, Inc.
Washington Physicians, Inc.
Baltimore Bariatrics, Inc.
Connecticut Pulmonary Associates, Inc.
DC Dialysis
Delaware Dental Associates
Marques
Marques
Marques
Marques
Matthew
Matthew
Matthew
Matthew
Matthew
Matthew
Matthew
Matthew
Xin
Xin
Xin
Xin
Xin
Xin
Xin
Xin
Xin
Quarles
Quarles
Quarles
Quarles
Schmidt
Schmidt
Schmidt
Schmidt
Schmidt
Schmidt
Schmidt
Schmidt
Zhu
Zhu
Zhu
Zhu
Zhu
Zhu
Zhu
Zhu
Zhu
NE1
NE2
NE1
NE2
NW2
NW2
NW2
NW2
NW2
NW2
NW2
NW2
SE2
SE2
SE2
SE2
SE2
SE2
SE2
SE2
SE2
Maine Medical Associates, Inc.
Maryland Pharmacies, Inc.
Maine Medical Associates, Inc.
Rhode Island Gastroenterology, Inc.
Bellevue Bariatics, Inc.
Boise Oncology
Casper Cardiology, Inc.
Cheyenne Cardiologists, Inc.
ID Internal Medicine Associates
Laramie EMTs, Inc.
WY OBGyn, Inc.
Wyoming Health Care, Inc.
Low County Medical Associates, Inc.
NC Medical Associates
Orthopaedic Medical Associates
Palmetto Physicans
Palmetto Podiatrists, Inc.
Piedmont Health Associates, Inc.
SC Medical Associates
Upstate Pharmacies, Inc.
NC Medical Associates
utical Sales
Annual Sales
122,222
128,979
133,288
91,995
97,489
88,878
91,222
107,373
115,609
142,734
115,975
108,277
82,651
87,442
157,721
153,211
127,555
169,272
162,777
118,118
133,288
141,111
109,333
176,208
106,266
152,686
133,991
87,556
114,111
105,767
115,234
153,848
88,888
96,606
83,363
97,443
97,002
82,319
89,107
300,708
103,301
162,202
215,609
Commission
Rate
2%
2%
2%
2%
2%
2%
2%
2%
2%
4%
2%
2%
2%
2%
4%
4%
2%
4%
4%
2%
2%
4%
2%
4%
2%
4%
2%
2%
2%
2%
2%
4%
2%
2%
2%
2%
2%
2%
2%
6%
2%
4%
6%
Commission
Earned
2,444.44
2,579.58
2,665.76
1,839.90
1,949.78
1,777.56
1,824.44
2,147.46
2,312.18
5,709.36
2,319.50
2,165.54
1,653.02
1,748.84
6,308.84
6,128.44
2,551.10
6,770.88
6,511.08
2,362.36
2,665.76
5,644.44
2,186.66
7,048.32
2,125.32
6,107.44
2,679.82
1,751.12
2,282.22
2,115.34
2,304.68
6,153.92
1,777.76
1,932.12
1,667.26
1,948.86
1,940.04
1,646.38
1,782.14
18,042.48
2,066.02
6,488.08
12,936.54
Rating
2,444.44
2,579.58
2,665.76
1,839.90
1,949.78
1,777.56
1,824.44
2,147.46
2,312.18
5,709.36
2,319.50
2,165.54
1,653.02
1,748.84
6,308.84
6,128.44
2,551.10
6,770.88
6,511.08
2,362.36
2,665.76
5,644.44
2,186.66
7,048.32
2,125.32
6,107.44
2,679.82
1,751.12
2,282.22
2,115.34
2,304.68
6,153.92
1,777.76
1,932.12
1,667.26
1,948.86
1,940.04
1,646.38
1,782.14
18,042.48
2,066.02
6,488.08
12,936.54
165,907
209,708
165,907
91,708
152,101
108,878
207,555
114,411
185,272
201,211
176,168
111,111
82,625
151,827
87,773
165,700
82,935
98,333
115,272
89,695
151,827
4%
6%
4%
2%
4%
2%
6%
2%
4%
6%
4%
2%
2%
4%
2%
4%
2%
2%
2%
2%
4%
6,636.28
12,582.48
6,636.28
1,834.16
6,084.04
2,177.56
12,453.30
2,288.22
7,410.88
12,072.66
7,046.72
2,222.22
1,652.50
6,073.08
1,755.46
6,628.00
1,658.70
1,966.66
2,305.44
1,793.90
6,073.08
6,636.28
12,582.48
6,636.28
1,834.16
6,084.04
2,177.56
12,453.30
2,288.22
7,410.88
12,072.66
7,046.72
2,222.22
1,652.50
6,073.08
1,755.46
6,628.00
1,658.70
1,966.66
2,305.44
1,793.90
6,073.08
Row Labels
Ambulatory Care of TX, Inc.
AZ Ambulatory Care
Baltimore Bariatrics, Inc.
Bama Chiropractic Clinics, Inc.
Bellevue Bariatics, Inc.
Boise Oncology
CA Chiropractic Associates, Inc.
Casper Cardiology, Inc.
Cheyenne Cardiologists, Inc.
CO Pharmacies, Inc.
Connecticut Pulmonary Associates, Inc.
DC Dialysis
Delaware Dental Associates
Emergency Medicine Association
EMTS of CO, Inc.
Garden State Pharmacies
Gerontology Associates of MS
ID Internal Medicine Associates
Internal Medicine of MS
Joint Healthcare Associates
LA Cardiologists, Inc.
Laramie EMTs, Inc.
Lone Star Medicine
Louisiana Dermatologists, Inc.
Low County Medical Associates, Inc.
MA OBGyn Associates
Maine Medical Associates, Inc.
Maryland Pharmacies, Inc.
MD Dental Associates
MS Women's Clinics, Inc.
NC Medical Associates
Nephrology Associates, Inc.
New England Pharmacies, Inc.
New England Physicians, Inc.
NM Pharmacies, Inc.
NV Emergency Medical Associates, Inc.
OBGyn Associates of Montana, Inc.
OK Clinics
Oregon Opthamology, Inc.
Orthodontial, Inc.
Orthopaedic Medical Associates
PA Medical Practices, Inc.
Palmetto Physicans
Palmetto Podiatrists, Inc.
Sum of Annual Sales
$
156,208
$
91,222
$
200,708
$
310,932
$
152,101
$
108,878
$
107,373
$
207,555
$
114,411
$
106,266
$
103,301
$
162,202
$
215,609
$
115,609
$
152,686
$
122,222
$
127,555
$
185,272
$
169,272
$
142,734
$
162,777
$
201,211
$
133,991
$
118,118
$
82,625
$
128,979
$
331,814
$
209,708
$
133,288
$
133,288
$
303,654
$
87,556
$
91,995
$
97,489
$
115,975
$
114,111
$
88,888
$
105,767
$
96,606
$
83,363
$
87,773
$
88,878
$
165,700
$
82,935
Sum of Commission Earned
$
6,248
$
1,824
$
12,042
$
12,437
$
6,084
$
2,178
$
2,147
$
12,453
$
2,288
$
2,125
$
2,066
$
6,488
$
12,937
$
2,312
$
6,107
$
2,444
$
2,551
$
7,411
$
6,771
$
5,709
$
6,511
$
12,073
$
2,680
$
2,362
$
1,653
$
2,580
$
13,273
$
12,582
$
2,666
$
2,666
$
12,146
$
1,751
$
1,840
$
1,950
$
2,320
$
2,282
$
1,778
$
2,115
$
1,932
$
1,667
$
1,755
$
1,778
$
6,628
$
1,659
Pharmacies of Nevada, Inc.
Physicians of MS, Inc.
Piedmont Health Associates, Inc.
Rhode Island Gastroenterology, Inc.
SC Medical Associates
Seattle Psychiatric Associates, Inc.
Seattle Psychologists, Inc.
Southwest Dermatology
Southwest Medical Associates
TEX-MEX Pharmacies, Inc.
Tri-state Pharmacies
Upstate Pharmacies, Inc.
Utah Urology Associates, Inc.
Washington Physicians, Inc.
Women's Medicine of FL, Inc.
WY OBGyn, Inc.
Wyoming Health Care, Inc.
Grand Total
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
115,234
141,111
98,333
91,708
115,272
97,443
97,002
108,277
82,651
153,848
87,442
89,695
82,319
89,107
109,333
176,168
111,111
8,144,659
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
2,305
5,644
1,967
1,834
2,305
1,949
1,940
2,166
1,653
6,154
1,749
1,794
1,646
1,782
2,187
7,047
2,222
261,614
Row Labels
Ahjab
Anderson
Brown
Johansen
Lopez
Quarles
Schmidt
Zhu
Grand Total
Sum of Annual Sales
$
662,851
$
851,283
$
1,272,386
$
1,125,667
$
634,728
$
1,315,050
$
1,256,707
$
1,025,987
$
8,144,659
Sum of Annual Sales
Total
Last Name
Ahjab
Anderson
Brown
Johansen
Lopez
Quarles
Schmidt
Purchase answer to see full
attachment