60.1K

Verified Solution

Link Copied!

a) In cell F2 apply a conditional function that will reward employees who have sales exceeding $100,000 with bonuses by paying 10% of the total sales on the amount over the threshold of $100,000 and for employees not achieving bonuses, display the text No Bonus. Copy the results for all employees.

b) In H2, employees will receive an increase in their annual salary based on the Cost of Living (COL) with Increases scaled by their current annual salary. Using a lookup function determine the increased annual salary for employees referring to the table located in A26:B36. Note: this is a two-part calculation. First, lookup the COL rate, an exact match is not required. Confirm that the function has reported the correct percentage from the table noting that this a range where results round down. Edit the function to add a formula that will calculate the new COL Increase $ amount(Hint: Multiply the Annual Salary by the lookup function result). Format appropriately and copy the results for all employees.

c) In I2 use a function to calculate the Total Income earned by each employee including the current Annual Salary, the COL increase and any bonuses. Copy the results for all employees.

F2 A B D F G H Cost of Living Increase Basic Provincial Taxation Sales Bonus Total Income 2020 Regional Office Brandon Brandon Calgary Regina Calgary Thunder Bay Heuer E Total Annual Sales $ 100,000 $ 120,000 $ 75,000 $ 89,000 $ 130,000 $ 140,000 $ 105,600 $ 98,000 $ 147.000 $ 120.000 $ 97,000 $ $ 87.000 $ 99,990 Calgary Annual Salary S 57,600 S 62,400 S 62,400 S 73,200 S 57,600 S 62,400 S 73.200 S 57,600 S 62.400 S 62,400 S 73,200 S S 57,600 S 73,200 Thunder Bay Victoria Iqualuit Calgary Calgary Regina Nunavit Alberta Manitoba Ontario Province Taxation Rate Saskatchewan 10.50% British Columbia 5.06% 4.00% 10.00% 10.80% 5.05% 1 First Name Last Name Province 2 Fred Davolio Manitoba 3 Shelly Dilfer Manitoba 4 Tracy Dodsworth Alberta 5 Calvin Saskatchewan 6 Jeff Kamenetz Alberta 7 Nadine Manjoo Ontario 8 Debbie Millar Alberta 9 9 Don Sadri Ontario 10 Kerry Suyama British Columbia 11 Allison Tsai Nunavit 12 Mary Walker Alberta 13 James Waller Alberta 14 Ebony Byas Saskatchewan 15 16 17 Province Regional Office 18 Saskatchewan Regina 19 Nunavit Iqualuit 20 Alberta Calgary 21 British Columbia Victoria 22 Manitoba Brandon 23 Ontario Thunder Bay 24 Cost of Living 25 Salary Range Salary Increases 26 $50,000 2.90% 27 $55,000 2.85% 28 S60,000 2.80% 29 S65,000 2.75% 30 $70,000 2.70% 31 $75,000 2.65% 32 S80,000 2.60% 33 585,000 2.55% 34 $90,000 2.50% 35 $95,000 2.45% 36 S100,000 2.40% 37 38 39 F2 A B D F G H Cost of Living Increase Basic Provincial Taxation Sales Bonus Total Income 2020 Regional Office Brandon Brandon Calgary Regina Calgary Thunder Bay Heuer E Total Annual Sales $ 100,000 $ 120,000 $ 75,000 $ 89,000 $ 130,000 $ 140,000 $ 105,600 $ 98,000 $ 147.000 $ 120.000 $ 97,000 $ $ 87.000 $ 99,990 Calgary Annual Salary S 57,600 S 62,400 S 62,400 S 73,200 S 57,600 S 62,400 S 73.200 S 57,600 S 62.400 S 62,400 S 73,200 S S 57,600 S 73,200 Thunder Bay Victoria Iqualuit Calgary Calgary Regina Nunavit Alberta Manitoba Ontario Province Taxation Rate Saskatchewan 10.50% British Columbia 5.06% 4.00% 10.00% 10.80% 5.05% 1 First Name Last Name Province 2 Fred Davolio Manitoba 3 Shelly Dilfer Manitoba 4 Tracy Dodsworth Alberta 5 Calvin Saskatchewan 6 Jeff Kamenetz Alberta 7 Nadine Manjoo Ontario 8 Debbie Millar Alberta 9 9 Don Sadri Ontario 10 Kerry Suyama British Columbia 11 Allison Tsai Nunavit 12 Mary Walker Alberta 13 James Waller Alberta 14 Ebony Byas Saskatchewan 15 16 17 Province Regional Office 18 Saskatchewan Regina 19 Nunavit Iqualuit 20 Alberta Calgary 21 British Columbia Victoria 22 Manitoba Brandon 23 Ontario Thunder Bay 24 Cost of Living 25 Salary Range Salary Increases 26 $50,000 2.90% 27 $55,000 2.85% 28 S60,000 2.80% 29 S65,000 2.75% 30 $70,000 2.70% 31 $75,000 2.65% 32 S80,000 2.60% 33 585,000 2.55% 34 $90,000 2.50% 35 $95,000 2.45% 36 S100,000 2.40% 37 38 39

Answer & Explanation Solved by verified expert

See Answer