The Touring School of Business of the Impala University has $48$ faculty members. The School has four departments $$ Marketing, Finance, Accounting and Information Systems. The raw data contains following details about each faculty member $$ Last Name, First Name, Department, Rank, Year Hired, Gender and Salary.

$1.$Calculate Income tax: For the first $$28,000,$ no taxes are withheld. For anything above $$28,000,$ the amount above $$28,000$ is taxed at $7\mathrm{.}25\%.$

a$.$ Create a new column for $$Income Tax$.$

b$.$ Compute income tax and place them in this column. HINT: You need an IF function here.

c$.$ Conditionally format the column such that if income tax is less than $$500,$ the cell is bold $($font$)$ and yellow colored $($text color only, NOT the fill color$)$; if income tax is between $$500$ and $$1500,$ make the cell bold $($font$)$ and fill it with light blue color.

$2.$Sort the Faculty list by Department and then by Last name.

$3.$In a separate table $($below the given raw data table$),$ calculate the Sum, Average, Maximum, Minimum & percentage $(\%)$ Salary for Each Department.

a$.$ Write the Department, Sum, Average, Maximum, Minimum & percentage $(\%)$ Salary as Column heading.

b$.$ Write Department names under $$Department$$ column.

c$.$ Compute Sum, Average, Maximum and Minimum for each department.

d$.$ Compute Sum of the salaries for all departments at the end of the Sum column $($in other words, total salaries for all the departments$).$

e$.$ Compute percentage $(\%)$ Salary of each department as Sum of Salary for the department divided by Total of Salaries for all departments.

$4.$Make a line chart to compare maximum, minimum and average salaries of each department $($NOTE: One line chart with THREE lines; $1$ each for max, min, and average; department names on x$-$axis and salaries on y$-$axis$).$

a$.$ Give chart heading as $$Comparison of Departmental Salaries$.$

b$.$ Make sure to name X$-$axis label and series name.

c$.$ Make chart as a separate sheet $($call this sheet $$chart$).$

$5.$Compare the average salary by Rank $($in a separate sheet$)$

a$.$ Copy the raw data in an empty sheet $($HINT: copy raw data from first sheet and paste into second sheet$).$

b$.$ Sort data by rank.

c$.$ Compute average salary by rank using subtotals.

d$.$ Name the sheet as $$Salary by Rank$.$

$6.$Copy the raw data in a new sheet. On this new sheet, using the filter option in Excel, filter the list of male faculty hired on or after $2010.$ Name the sheet as $$Recent Faculty$.$

$7.$Compare average salary between male and female for each department $($in a separate sheet$)$

a$.$ Copy the raw data in an empty sheet $($HINT: copy raw data from first sheet and paste into second sheet$).$

b$.$ Compute average salary for each department & within each department calculate average of salary for male and female faculties $($Use Subtotals$).$

c$.$ Name the sheet as $$Salary Comparison$.$