Question: Create a new table labeled Summary Table next to the Tax Table In the Summary Table, calculate: i. Total of Salary (SUM) ii. Average Salary

Create a new table labeled Summary Table next to the Tax

Create a new table labeled Summary Table next to the Tax Table

In the Summary Table, calculate:

i. Total of Salary (SUM)

ii. Average Salary by Job Ratings 1 -5 (AVERAGEIF) (Hint: there should be 5 cells, each containing the average salary for one of the 5 job ratings)

iii. Calculate number of people with Low, Moderate, High Salary (COUNTIF)

iv. Calculate the total salary of people with High Salary (SUMIF)

B E G H 1 J L 3. N 0 Tax Rate Job Rating 4 5 3 1 3 4 2 Tax Table Salary 5 Tax Rate 4 0% Taft 2 5 1% 5% 1 Employee Name 2 Abbott, James 3 Acosta, Robert 4 Adams, David 5 Adkins, Michael 6 Aguilar, Kevin 7 Alexander, Charles 8 Allen, Thomas 9 Allison, Timothy 10 Alvarado, Sonia 11 Alvarez, Steven 12 Anderson, Teason 13 Andrews, Diane 14 Anthony, Robert 15 Armstrong, David 16 Arnold, Cole 17 Ashley, Michael 18 Atkins, Kevin 19 Atkinson, Danielle Austin, 21 Avila, Jody 22 Ayala, Polly 23 Ayers, Douglas 24 Bailey, Victor 25 Baker, Barney Employee ID 509-784 687-501 419-669 707-261 719-452 672-566 992-251 788-644 147-767 273-611 581-999 671-250 501-322 480-233 972-938 451-963 333-947 697-499 777-726 248-275 222-913 283-205 591-467 232-856 Phone Number SSN (213) 559-1345 (650) 635-6195 (408) 749-9026 (415) 564-2886 (213) 679-7983 (213) 732-6795 (310) 754-8981 (805) 583-7411 (408) 524-0571 (415) 994-8598 (408) 362-4955 (213) 644-3013 (310) 784-9132 (415) 966-7273 (650) 850-7991 (213) 510-5363 (415) 443-1600 (310) 420-4808 (415) 285-6487 (310) 685-2047 (415) 621-4459 (213) 787-2044 (310) 724-8488 (408) 572-6290 Building Department Status 185-62-6047 North Quality Assurar Full Time 704-76-7569 Main Quality Assurar Full Time 663-55-3618 Main Product Develo, Full Time 533-14-4589 North Quality Assurar Full Time 886-81-2008 West Quality Assurar Full Time 738-47-5847 West Manufacturing Contract 453-48-4880 Main Manufacturing Full Time 435-81-8361 Main Creative Full Time 732-67-6609 Main IT Half-Time 773-12-0440 Quality Assurar Contract 618-36-8390 Watson Account Manag Contract 562-14-0062 North Account Manag Hourly 310-43-5053 Watson IT Half-Time 830-76-2743 North Quality Control Contract 499-67-4558 North Sales Full Time 449-56-4824 North Account Manag Full Time 321-53-8794 North Manufacturing Hourly 295-04-8866 North Account Manag Full Time 887-84-7088 Mair Account Manag Full Time 632-17-1392 Main Quality Assurar Hourly 366-32-1775 Main Account Manag Full Time 203-32-8951 Main Manufacturing Contract 653-59-3284 Watson Research Center Full Time 836-27-9875 Taft Creative Contract Hire Date Salary 1-Jan-00 29,260 24-Feb-07 39,000 20-Jan-10 49,260 31-Jan-11 24,840 10-May-99 39,000 27-Aug-00 74,500 31-Mar-06 79,730 10-Jan-12 82,500 21-Sep-11 35,045 13-Feb-11 89,450 3-Aug-99 71,300 21-Aug-98 16,688 3-Aug-10 16,925 31-Jan-98 45,030 26-Feb-10 22,860 26-Oct-10 63,206 6-Jul-98 8,904 21-Mar-03 23,560 10-Sep-07 62,688 3-Dec-10 33,508 2-Apr-07 23,320 11-Oct-10 52,940 4-Mar-03 42,800 7-Jan-11 80,050 3 6% 1 3 5,000 25,000 35,000 45,000 55,000 65,000 75,000 85,000 95,000 5 7% 8% 10% 11% 12% 13% 1 3 3 2 4 4 4 5 2 B E G H 1 J L 3. N 0 Tax Rate Job Rating 4 5 3 1 3 4 2 Tax Table Salary 5 Tax Rate 4 0% Taft 2 5 1% 5% 1 Employee Name 2 Abbott, James 3 Acosta, Robert 4 Adams, David 5 Adkins, Michael 6 Aguilar, Kevin 7 Alexander, Charles 8 Allen, Thomas 9 Allison, Timothy 10 Alvarado, Sonia 11 Alvarez, Steven 12 Anderson, Teason 13 Andrews, Diane 14 Anthony, Robert 15 Armstrong, David 16 Arnold, Cole 17 Ashley, Michael 18 Atkins, Kevin 19 Atkinson, Danielle Austin, 21 Avila, Jody 22 Ayala, Polly 23 Ayers, Douglas 24 Bailey, Victor 25 Baker, Barney Employee ID 509-784 687-501 419-669 707-261 719-452 672-566 992-251 788-644 147-767 273-611 581-999 671-250 501-322 480-233 972-938 451-963 333-947 697-499 777-726 248-275 222-913 283-205 591-467 232-856 Phone Number SSN (213) 559-1345 (650) 635-6195 (408) 749-9026 (415) 564-2886 (213) 679-7983 (213) 732-6795 (310) 754-8981 (805) 583-7411 (408) 524-0571 (415) 994-8598 (408) 362-4955 (213) 644-3013 (310) 784-9132 (415) 966-7273 (650) 850-7991 (213) 510-5363 (415) 443-1600 (310) 420-4808 (415) 285-6487 (310) 685-2047 (415) 621-4459 (213) 787-2044 (310) 724-8488 (408) 572-6290 Building Department Status 185-62-6047 North Quality Assurar Full Time 704-76-7569 Main Quality Assurar Full Time 663-55-3618 Main Product Develo, Full Time 533-14-4589 North Quality Assurar Full Time 886-81-2008 West Quality Assurar Full Time 738-47-5847 West Manufacturing Contract 453-48-4880 Main Manufacturing Full Time 435-81-8361 Main Creative Full Time 732-67-6609 Main IT Half-Time 773-12-0440 Quality Assurar Contract 618-36-8390 Watson Account Manag Contract 562-14-0062 North Account Manag Hourly 310-43-5053 Watson IT Half-Time 830-76-2743 North Quality Control Contract 499-67-4558 North Sales Full Time 449-56-4824 North Account Manag Full Time 321-53-8794 North Manufacturing Hourly 295-04-8866 North Account Manag Full Time 887-84-7088 Mair Account Manag Full Time 632-17-1392 Main Quality Assurar Hourly 366-32-1775 Main Account Manag Full Time 203-32-8951 Main Manufacturing Contract 653-59-3284 Watson Research Center Full Time 836-27-9875 Taft Creative Contract Hire Date Salary 1-Jan-00 29,260 24-Feb-07 39,000 20-Jan-10 49,260 31-Jan-11 24,840 10-May-99 39,000 27-Aug-00 74,500 31-Mar-06 79,730 10-Jan-12 82,500 21-Sep-11 35,045 13-Feb-11 89,450 3-Aug-99 71,300 21-Aug-98 16,688 3-Aug-10 16,925 31-Jan-98 45,030 26-Feb-10 22,860 26-Oct-10 63,206 6-Jul-98 8,904 21-Mar-03 23,560 10-Sep-07 62,688 3-Dec-10 33,508 2-Apr-07 23,320 11-Oct-10 52,940 4-Mar-03 42,800 7-Jan-11 80,050 3 6% 1 3 5,000 25,000 35,000 45,000 55,000 65,000 75,000 85,000 95,000 5 7% 8% 10% 11% 12% 13% 1 3 3 2 4 4 4 5 2

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Finance Questions!

I attached my assignment. can someone please help me Joyce writes childrens books for a variety of publishers. She has been selfemployed since 2007. As a freelance writer, Joyce incurs costs...

INTRODUCTION The information below will allow you to prepare the 2012 federal tax return for Bill and Joyce Schnappauf. The information is provided in three phases, which correspond to the three...

Using Table 6-2 as a guide, compare and contrast predictive and prescriptive analytics. How might these be used in an audit? Or a continuous audit? 6-2 Perform Substantive Tests of Account Balances...

Exercise Z-Excel File Notes about this le: The file has four tabs Presentation, Analysis, SurveyData, and lssueData. The SurveyData and IssueData tabs contain the data that you will use for Exercise...

Using the information from BE15-2, assume this is the only difference between Murphys pretax financial income and taxable income. Prepare the journal entry to record the income tax expense, deferred...

Water waves approach an underwater shelf where the velocity changes from 2.8 m/s to 2.1 m/s. If the incident wave crests make a 34o angle with the shelf, what will be the angle of refraction?

2. Do the following events represent business transactions? Explain your answer in each case. a. A computer is purchased on account. b. A customer returns merchandise and is given credit on account....

From Following Information of OGDCL share prices and KSE 100 index, Calculate Beta of OGDCL Date OGDCL (Rs) KSE 100 Index Dec 31, 2013 50 10,000 March 30, 2014 60 12,000 June 30, 2014 68 14,000 Sept...