Exercise 11. Open a blank worksheet in Excel. Enter the First name, Last Name, Gender, Department, andYearly Salary (with 2 decimals) for 10 fictitious people in Columns A-E, Beginning in Row 2. Besure to enter column headings (Row 1) and align them with the data. Adjust column widths asneeded.2. Sort the data by Last Name.3. Insert a column before the name (new column A), and enter the Employee Number. Type onlythe 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. Assume20% 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,000withheld. 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 ormore withheld, you get $2,000 back as before. Use a VLOOKUP statement to compute the NewTax 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 = $2000Only put the lower bound of each range as the cutoff point on the VLOOKUP table. PleaseseeExcelExampleSolutionSteps.doc, Page 4, on Canvas.)7. Draw a bar graph showing the salaries for each person, along with their last name. Label axesappropriately.
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.
AI-generated content may present inaccurate or offensive content that does not represent bartleby’s views.
Unlock instant AI solutions
Tap the button
to generate a solution
