Complete both Exercise $1$ and $2$ in one excel worksheet and submit it for Module $1.$

Exercise $1$

Open a blank worksheet in Excel. Enter the First name, Last Name, Gender, Department, and Yearly Salary $($with $2$ decimals$)$ for $10$ fictitious people in Columns A$-$E$,$ Beginning in Row $2.$ Be sure to enter column headings $($Row $1)$ and align them with the data. Adjust column widths as needed.

Sort the data by Last Name.

Insert a column before the name $($new column A$),$ and enter the Employee Number. Type only the first two numbers, then select both and copy down for autofill.

In Column G$,$ type the heading "Tax Withheld", and enter a formula that computes it$.$ Assume $20\%$ of the salary is withheld for taxes.

The new tax code give a Tax Break of $$2,000$ for all those who have more than $$20,000$ withheld. Use an IF statement to compute this in Column H$.($If Tax Witheld is greater than $$20000,$ then Tax Break is $$2000$; otherwise, Tax Break is $$0.)$

Assume now that the Tax Break is more complex. If you had a least $$10,000$ withheld, you get $$500$ back. If you had at least $$15,000$ withheld, you get $$1,000$ back. If you had $$20,000$ or more withheld, you get $$2,000$ back as before. Use a VLOOKUP statement to compute the New Tax Break in Column I.

$($Hint: if TAX WITHELD $$$$10000,$ then NEW TAX

BREAK $=$$$0$;

if $$10000$ TAX WITHELD $$$$15000,$ then NEW

TAX BREAK $=$$$500$;

if $$15000$ TAX WITHELD $$$$20000,$ then NEW

TAX BREAK $=$$$1000$;

if TAX WITHELD $$$$20000,$ then NEW TAX

BREAK $=$$$2000$

Only put the lower bound of each range as the cutoff point on the VLOOKUP table. Please

seeExcelExampleSolutionSteps.doc, Page $4,$ on Canvas.$)$

Draw a bar graph showing the salaries for each person, along with their last name. Label axes appropriately.

Exercise $2$

Continue with the spreadsheet from the above Exercise $1,$ and do the following

Insert a new row right after all $10$ row of data; use Excel formula to find the AVERAGE salary of all persons

Insert a new row right after average salary of all persons; use Excel formula to find the MEDIAN salary of all persons.

Sort the entire dataset by Gender $($do not include the averages in the sorting$)$

Insert a new row right after median salary of all persons; use Excel formula to find the AVERAGE salary of all females

Insert a new row right after average salary of all females; use Excel formula to find the MEDIAN salary of all females

Insert a new row right after median salary of all females; use Excel formula to find the AVERAGE salary of all males

Insert a new row right after average salary of all males; use Excel formula to find the MEDIAN salary of all males

Turn in this assignment by uploading your excel file to Canvas. Include your name in the file upload.