Your start file has two sheets. You will start your work on the second sheet:

Add the name of your company somewhere in Row $1($with professional formatting$)$

Add today's date utilizing the appropriate function in Row $2.$ Use a label: Last Updated

Add a Defined Name for the range P$8$:Q$16$

Name it TaxPercent

Make the main data range $($A$4$:N$104)$ into an Excel Table

Name the table StaffPay

Use structured references for all formulas and functions

Sort the table first by Department, then by Last Name, and then by First Name

In Column D$,$ add a formula without using a function that combines the First and Last Names of the employees

In Column I, utilize the VLOOKUP function to find the tax percentage based on # of Dependents and TaxPercent range $($be sure and use an approximate match so those with more than $7$ dependents still have a tax rate$)$

In Column J$,$ add a fomula without using a function to calculate the amount of taxes owed.

In Column K$,$ add a formula without using a function to calculate Net Pay

In Column L$,$ add conditional formatting that you believe best represents the range of scores

In Column M$,$ add a nested IF function, or the IFS function to determine the Bonus give to employees based on their Service Score and these ranges:

Service Score $=5$; Bonus $=$ $$1,250$

Service Score $=4$; Bonus $=$ $$750$

Otherwise; Bonus $=$ $$0$

In Column N$,$ add a formula to calculate Net Pay with the Bonus added

Add an Excel Total Row to the table and do the following:

Add a Count to Column D to show the total number of employees

Add an Average to Column L to show the average Service Score

Add a Sum to Column N to show the total net pay with bonus

Name the worksheet

Create a PivotTable from the StaffPay table:

Place it in the first worksheet

Name the PivotTable ServiceScorePivot

Add Location and Department to Rows $($in that order$)$

Add Service Score to the Values

Change the Service Score field to an Average calculation

Update this field to have a Custom Name Service Average and change to number format with $2$ decimals

Insert a PivotChart that you believe best depicts the data $($with appropriate title, style, etc.$)$

Insert a Slicer based on Location and Filter by Cleveland only $($place the slicer where appropriate so that it does not cover the data$)$

In Cell A$30$ include an email link to your email with the following:

Text to display: Email to Human Resources

Payroll Data Workbook Concerns

ScreenTip: Email HR with any questions you may have

Name the worksheet Bonus PivotTable

Final Instructions $/$ requirements:

Formatted professionally $($style$,$ borders, spelling, percentages, dollars, etc.$)$

Structured references should be used throughout $($i$.$e$.$ all formulas, functions should use them$)$