[Solved] In this project, you are an accountant fo

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 09, 2024

In this project, you are an accountant for National Pharmaceutical Sales. You want to make a spreadsheet that contains each sales associate's statistics. Because the

In this project, you are an accountant for National Pharmaceutical Sales. You want to make a spreadsheet that contains each sales associate's statistics. Because the company has over 100 sales associates, only a partial spreadsheet will be provided. Please follow the steps below to complete the partial spreadsheet.

Skills needed to complete this project:

Extracting Text with RIGHT, LEFT, and MID (Skill 6.1)
Using the IFS Function (Skill 6.13)
Rounding with Functions (Skill 6.2)
Finding the Middle Value with MEDIAN (Skill 6.7)
Calculating the Standard Deviation (Skill 6.8)
Using SUMIF and SUMIFS (Skill 6.4)
Using AVERAGEIF and AVERAGEIFS (Skill 6.5)
Using Database Functions (Skill 6.20)
Managing Errors with the IFERROR Function (Skill 6.21)
Analyzing Complex Formulas Using Evaluate Formula (Skill 6.22)

Open the start file EX2019-ChallengeYourself-6-3. The file will be renamed automatically to include your name.
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.
On the Sales Data worksheet, enter a formula in cell J4 to find the sales associate's region by extracting the first three characters of the sales associate's ID in cell C4. Use cell references where appropriate. Fill the formula down through cell J64.
On the Sales Data worksheet, calculate the commission rate and commission paid for each sales associate.
1. Beginning in cell F4, enter a formula using the IFS function to calculate the commission rate based on the following table. Hint: Do not include comma separators in numerical values in formulas. Excel interprets commas as function argument separators.
  Table displays calculations for commission rates based on annual sales.
  
  IF ANNUAL SALES ARE: THEN THE COMMISSION RATE IS:
  
  >=225,000 6%
  
  >=175,000 4%
  
  >=100,000 2%
  
  0%
2. Fill the formula down through cell F64. If necessary, format the range as a percentage, and center the values in the cells.
3. In cell G4, calculate the commission earned by multiplying the annual sales by the commission rate (E4*F4).
4. Add a rounding function to the formula in cell G4 to round the result to two decimal places.
5. Fill the formula down through cell G64. If necessary, format the range using the default Comma Style number format.
In the Summary sheet, calculate average and median sales and commissions. Use the named ranges shown in cells A23:B26.
1. In cell B5, enter a formula to calculate the average annual sales for all sales associates. Use the named range AnnualSales.
2. In cell C5, enter a formula to calculate the median annual sales for all sales associates. Use the named range AnnualSales.
3. In cell D5, enter a formula to calculate the standard deviation for sales. This dataset is a sample of the larger population. Use the named range AnnualSales.
4. Add a rounding function to the formula in cell D5 to round the result to two decimal places.
5. In cell B6, enter a formula to calculate the average commission. Use the named range CommissionEarned.
6. In cell C6, enter a formula to calculate the median commission. Use the named range CommissionEarned.
7. In cell D6, enter a formula to calculate the standard deviation for commissions. This dataset is a sample of the larger population. Use the named range CommissionEarned.
8. Add a rounding function to the formula in cell D6 to round the result to two decimal places.
9. If necessary, format cells B5:D6 with the default Accounting Number Format.
Calculate the total and average sales by region.
1. In cell B10, use a SUMIF formula to calculate the sum of AnnualSales where the value in the Regions named range is equal to the region listed in cell B9.
2. Copy the formula to cells C10:I10.
3. In cell B11, use an AVERAGEIF formula to calculate the average of AnnualSales where the value in the Regions named range is equal to the region listed in cell B9.
4. Add a rounding function to the formula in cell B11 to round the result to two decimal places.
5. Copy the formula to cells C11:I11.
6. If necessary, format cells B10:I11 with the default Accounting Number Format.
The second part of the Summary sheet contains a sales associate lookup form, but the appropriate formulas have not been entered yet.
1. In cell B18, enter a formula to calculate the average commission earned for the sales associate last name entered in cell A16. (Hint: Use a database function. Use the named range Earnings as the Database argument and the column label Commission Earned as the Field argument. The criteria range should be A15:B16.)
2. In cell B19, enter a formula to calculate the total commission earned for that same sales associate. (Hint: Use a different database function. The function arguments are the same as those you used in step 7a.)
3. If necessary, apply the Accounting Number Format to cells B18:B19.
4. In cell B20, enter a formula to calculate the number of clients the sales associate had with annual sales reaching the target set in cell B16. (Hint: This time, use the column label Client as the Field argument. The criteria range should be A15:B16.) Remember, the values in the Client field are text values. Use the appropriate function.
5. If necessary, apply the General number format to cell B20.
6. Test your formulas by entering Lopez as the sales associate's last name (cell A16) and >200000 as the sales target (cell B16).
7. Add an IFERROR function to the formula in cell B18 to display the error text Associate not found.
8. If you receive errors, use Evaluate Formula to troubleshoot your formulas.
Save and close the workbook.
Upload and save your project file.
Submit project for grading.

A B D E F G H J National Pharmaceutical Sales 1 2 3 Sales Summary Standard Deviation Average Median 4 4 5 Annual Sales 6 Commission Earned SW2 NW1 NE2 NW2 Sales Summary by Region 9 NE1 SE2 swi SE1 10 Total 11 Average 12 13 Enter the sales associate's last name and annual sales target in the cells below 14 if no choices are entered the table below displays company-wide data 15 Sales Associate's Last Name Annual Sales 16 17 18 Average Commission Earned 19 Total Commission Earned Number of Clients with Annual Sales 20 Reaching Target 21 22 23 AnnualSales ='Sales Data'!$E$4:$E$64 24 CommissionEarned ='Sales Data'l$G$4:$G$64 25 Earnings ='Sales Data'l$A$3:$1$64 26 Regions ='Sales Data'!$C$4:$C$64 27 7 28 29 30 B D E F K 1 National Pharmaceutical Sales 2 Sales Associate's Sales Associate's Sales Associate's Last Name First Name ID Commission Commission Rate Earned 3 Client Annual Sales Salary Total Earnings Region 55,000 78,133 230,427 262,620 249,654 226,250 232,475 85,573 51,000 60,000 4 Ahjab 5 Ahjab 6 Ahjab 7 Ahjab 8 Ahjab 9 Ahjab 10 Anderson 11 Anderson 12 Anderson 13 Anderson 14 Anderson 15 Anderson 16 Anderson 17 Anderson 18 Anderson 19 Anderson 20 Anderson 21 Anderson 22 Anderson 23 Anderson 24 Anderson 25 Anderson 26 Brown 27 Brown 28 Brown 29 Brown 30 Brown 31 Brown 32 Brown 33 Brown 34 Johansen 35 Johansen 36 Johansen 37 Johansen 38 Johansen 39 Johansen 40 Johansen 41 Johansen 42 Johansen 43 Lopez Raminian Raminian Raminian Raminian Raminian Raminian Cynthia Cynthia Cynthia Cynthia Cynthia Cynthia Cynthia Cynthia George George George George George George George George Tatisha Tatisha Tatisha Tatisha Tatisha Tatisha Tatisha Tatisha Jan Jan Jan Jan Jan Jan Jan 50,000 NE1-73647 NE1-217199 NE1-247543 NE1-235322 NE1-213262 NE1-219129 SE2-80660 SE2-231417 SE2-189894 SE2-87721 SE2-153666 SE2-117771 SE2-81906 SE2-115585 SW1-137004 SW1-149663 SW1-82895 SW1-183010 SW1-150820 SW1-126767 SW1-248636 SW1-186345 SE1-230271 SE1-119903 SE1-253055 SE1-132658 SE1-77149 SE1-157263 SE1-230063 SE1-151999 SW2-214933 SW2-94637 SW2-92179 SW2-194117 SW2-140911 SW2-202653 SW2-175034 SW2-226564 SW2-68539 NW1-176917 NW1-207538 NW1-73903 NW1-173347 NW1-198233 NW1-218623 NW1-245472 NE2-69555 NE2-175858 NE2-150501 NE2-123129 NE2-192148 NE2-149537 NE2-177202 NW2-210844 NW2-213043 NW2-186063 NW2-149341 NW2-234369 NW2-146676 NW2-255982 NW2-80590 Garden State Pharmacies MA OBGyn Associates MD Dental Associates New England Pharmacies, Inc. New England Physicians, Inc. PA Medical Practices, 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. 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. 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 Maine Medical Associates, Inc. Maryland Pharmacies, 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. 62,000 245,511 201,459 93,064 163,025 134,945 86,895 132,625 145,349 158,778 87,944 194,157 160,006 134,489 263,779 197,695 244,296 127,207 268,467 140,738 81,848 166,841 244,074 161,256 228,023 99,402 97,794 205,940 149,494 214,995 185,695 240,362 72,714 187,692 220,178 78,405 183,905 210,306 231,938 260,423 73,792 186,569 159,668 130,628 203,851 158,645 187,995 223,686 226,019 197,395 158,437 248,644 155,610 271,572 85,499 55,000 NE1 NE1 NE1 NE1 NE1 NE1 51.000 SE2 SE2 SE2 SE2 SE2 SE2 SE2 SE2 60,000 SW1 SW1 swi SW1 SW1 SW1 swi swi 50,000 SE1 SE1 SE1 SE1 SE1 SE1 SEI SE1 62,000 SW2 SW2 SWZ SW2 SW2 SW2 SW2 SW2 SW2 55,000 NWI NWI NW1 NW1 NWI NWI NW1 58,000 NE2 NEZ NE2 NE2 NE2 NEZ NE2 55,000 NW2 NW2 NW2 NW2 NW2 NW2 NWZ NW2 Jan 55,000 44 Lopez 45 Lopez 46 Lopez 47 Lopez 48 Lopez 49 Lopez 58,000 Jan Julieta Julieta Julieta Julieta Julieta Julieta Julieta Marques Marques Marques Marques Marques Marques Marques Matthew Matthew Matthew Matthew Matthew Matthew Matthew Matthew 50 Quarles 51 Quarles 52 Quarles 53 Quarles 54 Quarles 55 Quarles 56 Quarles 57 Schmidt 58 Schmidt 59 Schmidt 60 Schmidt 61 Schmidt 62 Schmidt 63 Schmidt 64 Schmidt 65 66 67 68 55,000 A B D E F G H J National Pharmaceutical Sales 1 2 3 Sales Summary Standard Deviation Average Median 4 4 5 Annual Sales 6 Commission Earned SW2 NW1 NE2 NW2 Sales Summary by Region 9 NE1 SE2 swi SE1 10 Total 11 Average 12 13 Enter the sales associate's last name and annual sales target in the cells below 14 if no choices are entered the table below displays company-wide data 15 Sales Associate's Last Name Annual Sales 16 17 18 Average Commission Earned 19 Total Commission Earned Number of Clients with Annual Sales 20 Reaching Target 21 22 23 AnnualSales ='Sales Data'!$E$4:$E$64 24 CommissionEarned ='Sales Data'l$G$4:$G$64 25 Earnings ='Sales Data'l$A$3:$1$64 26 Regions ='Sales Data'!$C$4:$C$64 27 7 28 29 30 B D E F K 1 National Pharmaceutical Sales 2 Sales Associate's Sales Associate's Sales Associate's Last Name First Name ID Commission Commission Rate Earned 3 Client Annual Sales Salary Total Earnings Region 55,000 78,133 230,427 262,620 249,654 226,250 232,475 85,573 51,000 60,000 4 Ahjab 5 Ahjab 6 Ahjab 7 Ahjab 8 Ahjab 9 Ahjab 10 Anderson 11 Anderson 12 Anderson 13 Anderson 14 Anderson 15 Anderson 16 Anderson 17 Anderson 18 Anderson 19 Anderson 20 Anderson 21 Anderson 22 Anderson 23 Anderson 24 Anderson 25 Anderson 26 Brown 27 Brown 28 Brown 29 Brown 30 Brown 31 Brown 32 Brown 33 Brown 34 Johansen 35 Johansen 36 Johansen 37 Johansen 38 Johansen 39 Johansen 40 Johansen 41 Johansen 42 Johansen 43 Lopez Raminian Raminian Raminian Raminian Raminian Raminian Cynthia Cynthia Cynthia Cynthia Cynthia Cynthia Cynthia Cynthia George George George George George George George George Tatisha Tatisha Tatisha Tatisha Tatisha Tatisha Tatisha Tatisha Jan Jan Jan Jan Jan Jan Jan 50,000 NE1-73647 NE1-217199 NE1-247543 NE1-235322 NE1-213262 NE1-219129 SE2-80660 SE2-231417 SE2-189894 SE2-87721 SE2-153666 SE2-117771 SE2-81906 SE2-115585 SW1-137004 SW1-149663 SW1-82895 SW1-183010 SW1-150820 SW1-126767 SW1-248636 SW1-186345 SE1-230271 SE1-119903 SE1-253055 SE1-132658 SE1-77149 SE1-157263 SE1-230063 SE1-151999 SW2-214933 SW2-94637 SW2-92179 SW2-194117 SW2-140911 SW2-202653 SW2-175034 SW2-226564 SW2-68539 NW1-176917 NW1-207538 NW1-73903 NW1-173347 NW1-198233 NW1-218623 NW1-245472 NE2-69555 NE2-175858 NE2-150501 NE2-123129 NE2-192148 NE2-149537 NE2-177202 NW2-210844 NW2-213043 NW2-186063 NW2-149341 NW2-234369 NW2-146676 NW2-255982 NW2-80590 Garden State Pharmacies MA OBGyn Associates MD Dental Associates New England Pharmacies, Inc. New England Physicians, Inc. PA Medical Practices, 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. 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. 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 Maine Medical Associates, Inc. Maryland Pharmacies, 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. 62,000 245,511 201,459 93,064 163,025 134,945 86,895 132,625 145,349 158,778 87,944 194,157 160,006 134,489 263,779 197,695 244,296 127,207 268,467 140,738 81,848 166,841 244,074 161,256 228,023 99,402 97,794 205,940 149,494 214,995 185,695 240,362 72,714 187,692 220,178 78,405 183,905 210,306 231,938 260,423 73,792 186,569 159,668 130,628 203,851 158,645 187,995 223,686 226,019 197,395 158,437 248,644 155,610 271,572 85,499 55,000 NE1 NE1 NE1 NE1 NE1 NE1 51.000 SE2 SE2 SE2 SE2 SE2 SE2 SE2 SE2 60,000 SW1 SW1 swi SW1 SW1 SW1 swi swi 50,000 SE1 SE1 SE1 SE1 SE1 SE1 SEI SE1 62,000 SW2 SW2 SWZ SW2 SW2 SW2 SW2 SW2 SW2 55,000 NWI NWI NW1 NW1 NWI NWI NW1 58,000 NE2 NEZ NE2 NE2 NE2 NEZ NE2 55,000 NW2 NW2 NW2 NW2 NW2 NW2 NWZ NW2 Jan 55,000 44 Lopez 45 Lopez 46 Lopez 47 Lopez 48 Lopez 49 Lopez 58,000 Jan Julieta Julieta Julieta Julieta Julieta Julieta Julieta Marques Marques Marques Marques Marques Marques Marques Matthew Matthew Matthew Matthew Matthew Matthew Matthew Matthew 50 Quarles 51 Quarles 52 Quarles 53 Quarles 54 Quarles 55 Quarles 56 Quarles 57 Schmidt 58 Schmidt 59 Schmidt 60 Schmidt 61 Schmidt 62 Schmidt 63 Schmidt 64 Schmidt 65 66 67 68 55,000