Exercise 1

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.
2. Sort the data by Last Name.
3. 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.
4. In Column G, type the heading Tax Withheld, and enter a formula that computes it. Assume 20% of the salary is withheld for taxes.
5. 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.)
6. 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.)

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