The TY Organization need to determine some information from the HR dept. They are performing a salary audit of some departments within the organization. You are the Payroll Clerk and you are tasked with figuring out the Bonus and Gross Pay of the employees in the Payroll.xlsx spreadsheet. In addition, there are other items you are tasked with completing. Utilizing Excel, please complete the questions below. Use Excel functions to complete the following:

1. Determine the Bonus for employees (Some employees do not receive a bonus). The salary range is $0-$500,000. Bonus is given according to salary, anyone above 50,000-500,000 receive a bonus. Therefore bonus is distributed as follows: 

a. Lower than 100,000=5%, 100,000 and above 10%, 150,000 and above 15%, any salary higher than 200,000 receive a standard 20% bonus. 

b. Please create a Bonus Table to calculate the bonuses. Also name the table “Bonus” 

2. Calculate the Gross Pay. Formula is Bonus+Salary. In cases, where there isn’t a bonus, return salary. 

3. Sort the Employee list by Last Name. 

4. Total amount of Salary for the year.

5. Total amount of salary in IT dept. 

6. Total amount of salary in Advertising dept. 

7. Chart the amount of salary in IT vs Advertising Dept. One chart but 2 series. (IT, Advertising). Use any chart that will display the data accurately and clearly. 

8. How many employees in the TY Organization? 

9. Format the Gross Pay and Salary to currency ($), 0 decimal places.