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