ABC employs agents to promote and sell its diverse product range and services. To reward and provide incentive to these agents, they are paid a commission of 5% of the amount of sales over the target. For example, the target is $34,000. If an agent sells $40,000 they will receive 5% of $6,000 as a commission. And, for greater incentive, they will receive 10% if they double the target. For example, if an agent sells $72,000 they will receive 10% of $38,000 as a commission. If it's less than the target then the function should not display anything, should make an empty cell. Note that a nested if function is required to calculate the commission. For Status, use a nested if function to display a message "Exceeded" if it is over the target. In case it's equal to the targeted value then the function should display a message "Reached", otherwise the function should not display anything, should make an empty cell.
In this worksheet, the target and commission amounts are displayed in their own cells. These amounts are then referenced by the formulas. Since the formulas will be copied to other cells, you need to be mindful of absolute versus relative cell addressing.
Task 1 ABC International Enterprises Employee Details
Bad Practice
Age Service
47 15
26 2
21 1
34 12
59 39
32 10
31 11
26 5
29 9
Good Practice
Age Commenced
56 30.05.98
35 30.05.11
30 29.05.12
43 30.05.01
67 30.05.74
41 30.05.03
40 30.05.02
36 29.05.08
38 29.05.04
First Name Last Name
Michelle Chalahan
Kira Convery
Paddy Deegan
Marty Doyle
Connor Healy
Alana Keane
Siobhan Kelliher
Anthony O'Brien
Melissa Quinn
Day Month Year Date of Birth Service
30 12 1966 30.12.66 25
30 12 1987 30.12.87 12
29 12 1992 29.12.92 11
30 12 1979 30.12.79 22
30 12 1955 30.12.55 49
30 12 1981 30.12.81 20
30 12 1982 30.12.82 21
30 12 1986 30.12.86 15
29 12 1984 29.12.84 19
Task 2 ABC International Enterprises Weekly Payroll
First Name Last Name Pay Scale Hourly Rate Hours Worked Gross Pay Tax Rate Tax Net Pay
Michelle Calahan 2 12.5 9.0
Kira Convery 3 16.0
Paddy Deegan 4 35.5
Marty Doyle 3 5.0
Connor Healy 2 40.5
Alana Keane
Siobhan Kelliher
Anthony O'Brien
Melissa Quinn
Tax Table
Salary Rate Tax
0 0%
500 10%
1,000 12%
1,200 16%
1,400 18%
1,600 20%
1,800 22%
2,000 24%
2,200 26%
2,400 28%
2,600 30%
Hourly Rates Totals
1 2 3
23.50 30.00 35.00 38.50 42.50
Task 3 ABC International Enterprises Agency Commissions
Agent Monthly Sales Commission Status
Janet Costas $45,000.00
Mark Daniels $25,000.00
Maureen Grayson $46,002.00
Jerry Hancock $34,000.00
Brian Houson $18,350.00
Helen Kai $12,500.00
Norris Maunga $75,800.00
Alex Nuyen $43,778.00
Kate Rualowy $23,400.00
Target: $34,000.00
Commission: 5%
Result:
