Company
No. EmpName DepCode Account
1 Acosta, Rogy IT-0012 Microsoft
2 Adricula, Rommel IS-0014 Apple
3 Alda, Andrey CS-0015 Microsoft
4 Arboleda, Vince Ryan CS-0105 Apple
5 Bibangco, EJ IT-0012 Microsoft
6 Caquicla, Hazel IS-0014 IBM
7 Dalut, Marvin CS-0015 Red Hat Inc.
8 Descartin, Robert IT-0012 Adobe
9 Dionson, Mary Gift CS-0105 Microsoft
10 Epacta, Micheas IS-0014 IBM
11 Gustilo, Klexa IS-0014 IBM
12 Jamandre, Markh CS-0015 Red Hat Inc.
13 Mendoza, Anna IS-0014 IBM
14 Nobleza, Janine CS-0105 Apple
15 Sabay, Reymund CS-0105 IBM
16 Sarabia, Dianne IS-0014 Microsoft
17 Tomesa, Jeron IT-0012 Adobe
18 Torres, Ariane CS-0105 Apple
19 Ubas, Erul John IS-0014 IBM
20 Virtucio, Jimmy CS-0015 Red Hat Inc.
Conditions for Sales Remarks
<=1000 to <2500 low
>=2500 to <5000 average
>=5000 to <=8000 high
>8000 very high
Answer the followin
[Link] Basic Pay?
2. Who got the lowest sales amount?
3. Average sales amount of employees assigned in Microsoft Account?
4. How many is assigned in Red Hat Inc.?
5. Total amount of Deduction for the month of March
5. How many has Sales Remarks of Average?
6. Average commission of employees with DepCode CS-0015?
7. How many have "Low Pay" Remarks
8. Who got the highest Monthly Net Pay?
9. What is the average Sales Amount
10. What is the Monthly Net Pay remarks of Sarabia, Dianne?
11. Total Monthly Gross Pay of employees with DepCode IT-0012
12. How many employees are assigned to Microsoft?
13. Sum of the deduction of employees who are assigned to Apple
14. Total number of employees who are assigned in Adobe
15. Total Monthly Net Pay
16. What is the average commission amount of employees with Apple account?
17. How many employees have CS-0015 depcode?
18. How many employees are assigned to Adobe and Red Hat Inc.?
19. What is the commission rate of Adricula, Rommel?
20. What is the average sales commission rate?
21. What is the sum of sales amount of empployees assigned in IBM?
22. What is the total deduction amount of employees for the month of September?
23. What is the highest commission amount?
24. Who got the highest PHIC deduction?
25. What is the least commission rate value?
So
Basic Pay Sales Amount
$ 50,000.00 $ 6,500.00
$ 25,690.00 $ 2,500.00
$ 21,548.00 $ 2,568.00
$ 25,000.00 $ 8,461.00
$ 15,000.00 $ 9,500.00
$ 20,000.00 $ 3,510.00
$ 100,000.00 $ 5,500.00
$ 11,355.00 $ 1,580.00
$ 21,550.00 $ 1,896.00
$ 12,565.00 $ 15,000.00
$ 12,875.00 $ 6,000.00
$ 14,865.00 $ 1,000.00
$ 10,500.00 $ 8,500.00
$ 16,250.00 $ 20,000.00
$ 6,845.00 $ 1,542.00
$ 15,000.00 $ 2,605.00
$ 12,350.00 $ 4,350.00
$ 10,540.00 $ 4,565.00
$ 8,752.00 $ 23,000.00
$ 51,000.00 $ 10,500.00
Do not Edit the following columns:
EmpName
DepCode
CompanyAccount
BasicPay
Sales Amount
Answer the following requirements:
$ 100,000.00
Jamandre, Markh
osoft Account? $ 4,613.80
$ 67,853.21
0015? $ 820.45
Dalut, Marvin
$ 6,953.85
anne? average pay
e IT-0012 $ 91,698.00
ed to Apple $ 11,683.79
obe 2
$ 416,812.55
ees with Apple account? $ 1,493.91
ed Hat Inc.? 5
10%
14%
igned in IBM? $ 57,552.00
or the month of September? $ 67,853.21
$ 4,600.00
Dalut, Marvin
Software Initiative
Payroll Report
August 2019
Commission
Sales Remarks Rate
high 15%
average 10%
average 10%
very high 15%
very high 15%
average 10%
high 15%
low 10%
low 10%
very high 20%
high 15%
low 10%
very high 15%
very high 20%
low 10%
average 10%
average 10%
average 10%
very high 20%
very high 20%
Conditions for Net Pay Remarks
<=11000
>11000 to <=15000
>15000 to <=20000
>20000
requirements:
100,000.00 ç Type your formula here!
Jamandre, Markh ç Type your formula here!
4,613.80 ç Type your formula here!
3 ç Type your formula here!
67,853.21 ç Type your formula here!
6 ç Type your formula here!
820.45 ç Type your formula here!
5 ç Type your formula here!
Dalut, Marvin ç Type your formula here!
6,953.85 ç Type your formula here!
average pay ç Type your formula here!
91,698.00 ç Type your formula here!
5 ç Type your formula here!
11,683.79 ç Type your formula here!
2 ç Type your formula here!
416,812.55 ç Type your formula here!
1,493.91 ç Type your formula here!
4 ç Type your formula here!
5 ç Type your formula here!
10% ç Type your formula here!
14% ç Type your formula here!
57,552.00 ç Type your formula here!
67,853.21 ç Type your formula here!
4,600.00 ç Type your formula here!
Dalut, Marvin ç Type your formula here!
ç Type your formula here!
are Initiatives Inc.
Payroll Report
August 2019
Deduction
Commission
Amount Monthly Gross Pay SSS
8.00%
$ 975.00 $ 50,975.00 $ 4,078.00
$ 250.00 $ 25,940.00 $ 2,075.20
$ 256.80 $ 21,804.80 $ 1,744.38
$ 1,269.15 $ 26,269.15 $ 2,101.53
$ 1,425.00 $ 16,425.00 $ 1,314.00
$ 351.00 $ 20,351.00 $ 1,628.08
$ 825.00 $ 100,825.00 $ 8,066.00
$ 158.00 $ 11,513.00 $ 921.04
$ 189.60 $ 21,739.60 $ 1,739.17
$ 3,000.00 $ 15,565.00 $ 1,245.20
$ 900.00 $ 13,775.00 $ 1,102.00
$ 100.00 $ 14,965.00 $ 1,197.20
$ 1,275.00 $ 11,775.00 $ 942.00
$ 4,000.00 $ 20,250.00 $ 1,620.00
$ 154.20 $ 6,999.20 $ 559.94
$ 260.50 $ 15,260.50 $ 1,220.84
$ 435.00 $ 12,785.00 $ 1,022.80
$ 456.50 $ 10,996.50 $ 879.72
$ 4,600.00 $ 13,352.00 $ 1,068.16
$ 2,100.00 $ 53,100.00 $ 4,248.00
tions for Net Pay Remarks Commission Rate
Low Pay Sales Amount
Average Pay <=5000
High Pay >5001 to <=10000
Very High Pay >10000
Deduction
PHIC Pag-Ibig Total Deduction
4.00% 2.00%
$ 2,039.00 $ 1,019.50 $ 7,136.50
$ 1,037.60 $ 518.80 $ 3,631.60
$ 872.19 $ 436.10 $ 3,052.67
$ 1,050.77 $ 525.38 $ 3,677.68
$ 657.00 $ 328.50 $ 2,299.50
$ 814.04 $ 407.02 $ 2,849.14
$ 4,033.00 $ 2,016.50 $ 14,115.50
$ 460.52 $ 230.26 $ 1,611.82
$ 869.58 $ 434.79 $ 3,043.54
$ 622.60 $ 311.30 $ 2,179.10
$ 551.00 $ 275.50 $ 1,928.50
$ 598.60 $ 299.30 $ 2,095.10
$ 471.00 $ 235.50 $ 1,648.50
$ 810.00 $ 405.00 $ 2,835.00
$ 279.97 $ 139.98 $ 979.89
$ 610.42 $ 305.21 $ 2,136.47
$ 511.40 $ 255.70 $ 1,789.90
$ 439.86 $ 219.93 $ 1,539.51
$ 534.08 $ 267.04 $ 1,869.28
$ 2,124.00 $ 1,062.00 $ 7,434.00
ommission Rate
Commision Rate Commission Amount is e
10% Monthly Gross Pay is e
15% SSS Deduction is equal to the prod
20% PHIC Deduction is equal to the
Pag-Ibig Deduction is equal to the prod
Total Deduction is
Monthly Net Pay is equal to
Condition Rate value assigned to the us
Commission Amount is equa
Monthly Net Pay Net Pay Remarks
$ 43,838.50 very high pay
$ 22,308.40 very high pay
$ 18,752.13 high pay
$ 22,591.47 very high pay
$ 14,125.50 average pay
$ 17,501.86 high pay
$ 86,709.50 very high pay
$ 9,901.18 low pay
$ 18,696.06 high pay
$ 13,385.90 average pay
$ 11,846.50 average pay
$ 12,869.90 average pay
$ 10,126.50 low pay
$ 17,415.00 high pay
$ 6,019.31 low pay
$ 13,124.03 average pay
$ 10,995.10 low pay
$ 9,456.99 low pay
$ 11,482.72 average pay
$ 45,666.00 very high pay
Formula:
Commission Amount is equal to Sales Amount * Commision Rate
Monthly Gross Pay is equal to Basic Pay plus the Commission
SSS Deduction is equal to the product of Monthly Gross Pay and SSS Deduction Rate
PHIC Deduction is equal to the Product of Monthly Gross Pay and PHIC Rate
Pag-Ibig Deduction is equal to the product of Monthly Gross Pay and Pag-Ibig Deduction Rate
Total Deduction is the sum of SSS, PHIC and Pag-Ibig.
Monthly Net Pay is equal to Total Gross Pay minus the Total Deduction
Condition Rate value assigned to the user is depended on the conditions for Commission Rate
Commission Amount is equal to Sales multiplied by the Comission Rate.
e
ction Rate
C Rate
Deduction Rate
tion
mmission Rate
ate.
Conditions for Sales Remarks
<=1000 to <2500 low
>=2500 to <5000 average
>=5000 to <=8000 high
>8000 very high
high
Conditions for Net Pay Remarks
<=11000 Low Pay
>11000 to <=15000 Average Pay
>15000 to <=20000 High Pay
>20000 Very High Pay
Low Pay
Commission Rate
Sales Amount Commision Rate
<=5000 10%
>5001 to <=10000 15%
>10000 20%
20%
25690
3
Alda, Andrey
Acosta, Rogy IT-0012 Microsoft
Adricula, Rommel IS-0014 Apple
Alda, Andrey CS-0015 Microsoft
Arboleda, Vince Ryan CS-0105 Apple
Bibangco, EJ IT-0012 Microsoft
5000
if <2500
if <5000
if <=8000
otherwise/else > 8000
high
6000
if <=11000
if <=15000
if <=20000
else >20000
Low Pay
15000
if <=5000
if <=10000
else >10000
20%
25690
3
da, Andrey
$ 50,000.00
$ 25,690.00
$ 21,548.00
$ 25,000.00
$ 15,000.00