Duldulao Lexie Abbegail Payroll

EXCEL Activity
INSTRUCTIONS: Perform the requirements below on the given data. The given data is marked with Start and End of given
DULDULAO, LEXIE ABBEGAIL F.

AUGUST 31, 2022
Start of given data

AHJ BUILDERS, INC.
Don Bosco Rd., Trancoville, Baguio, Benguet
09564015047 / ahj@yahoo.com
Payroll Period: January 5 to 10, 2021
ID NO. EMPLOYEE NAME
M123 Ilagan, Harvert O.

F554 Olano, Siobhan D.
F166 Damayon, Sam B.
F127 Fernandez, Grace D.
M457 Taan, EJ E.
F433 Esteban, Janine P.
F554 Pascua, Janelle D.
M411 Kinao, Eric P.
F765 Halaman, Joy H.
F428 Dionisio, Aibee L.
M311 Zamora, Chris C.
M341 Villamor, Israel P.
M445 Bagtang, Daryll B.
M721 Apilado, Matthew M.
F834 Danao, Angela S.
F334 Duclayan, Myca L.
M132 Lunes, Christopher U.
F645 Pacudan, Iriz D.
M733 Pacudan, Steven D.
F435 Bulayang, Glenice P.
M822 Dinamman, Enrico H.
F435 Saure, Erica P.
M732 Duldulao, Denver F.
M222 Peralta, JM D.
F522 Cadelinia, Jailine P.
F224 Garcia, Xia D.
F456 Ascano, Ellaine P.
F111 Prodigalidad, Maureen H.
F435 Quidpoan, Jorem C.
End of given data
NUMBER OF FEMALE 17.00

NUMBER OF MALE 12.00
TOTAL BASIC PAY AND OVERTIME PAY BASIC PAY
OVERTIME PAY
TOTAL BASIC AND OVERTIME PAY
HIGHEST BASIC PAY AND OVERTIME PAY HIGHEST BASIC PAY
HIGHEST OVERTIME PAY
LOWEST BASIC PAY AND OVERTIME PAY LOWEST BASIC PAY
LOWEST OVERTIME PAY
REQUIREMENTS:
1. Assign an appropriate name to each employee. Format should be : Last name, First name Middle Initial. Take note that if the ID NO sta
2. Insert a column after the EMPLOYEE NAME then store the gender of each employee in this column by extracting it from the given ID N
That is, if the ID number starts with M, the gender to be assigned to the employee is MALE otherwise FEMALE.
Label the new column appropriately.
3. Compute the information identified below in the given sequence using formulas, cell references and commonly used Excel functions. S
Use the columns after the HOURS WORKED PER WEEK column.
Label the new columns appropriately.
a. Basic Pay = if Hours worked per week is less than or equal to 40, it is computed as Salary rate per hour multiplied by Hours worked ot
it is computed as Salary rate per hour multiplied by 40.
b. Overtime Pay = if Hours worked per week is more than 40, it is computed as (hours worked per week - 40) multiplied by Salary ratepe
otherwise it is zero
c. Gross Pay = Basic Pay plus Overtime Pay
d. Tax Deduction = if Gross pay is less than 5000, then it is zero else
if Gross pay is from 5001 to 9000, then it is 5% of the Gross pay otherwise
if Gross pay is above 9000, it is 7.5% of Gross pay
e. Net Pay = Gross Pay - Tax Deduction
Note: Display all computations to 2 decimal places and use the comma style.
4. Sort the entire worksheet information according to decreasing NET Pay. Take note that there is no heading since there is a space betwe
5. Determine the number of female and male employees. Use a commonly used function to do this.
Store determined information after the last data on the Gender column. Label computed values appropriately.
6. Determine the sum total of the Basic pay and the Overtime pay. Display it in separate cells.
7. Determine the highest Basic pay, lowest Basic pay, the highest Overtime pay and lowest Overtime pay. Display in separate cells.
8. Rename the worksheet as Payroll.
9. Save the completed workbook using the filename: Family Name, Given Name-Payroll
data is marked with Start and End of given data.
GENDER SALARY RATE HOURS WORKED BASIC PAY OVERTIME PAY

PER HOUR PER WEEK
Male 200.00 48.00 8,000.00 2,400.00

Female 210.00 44.00 8,400.00 1,260.00
Female 160.00 50.00 6,400.00 2,400.00
Female 185.00 45.00 7,400.00 1,388.00
Male 175.00 46.00 7,000.00 1,575.00
Female 195.00 42.00 7,800.00 585.00
Female 210.00 38.00 7,980.00 -
Male 190.00 39.00 7,410.00 -
Female 185.00 40.00 7,400.00 -
Female 190.00 38.00 7,220.00 -
Male 180.00 40.00 7,200.00 -
Male 200.00 36.00 7,200.00 -
Male 140.00 47.00 5,600.00 1,470.00
Male 190.00 35.00 6,650.00 -
Female 165.00 40.00 6,600.00 -
Female 180.00 36.00 6,480.00 -
Male 150.00 40.00 6,000.00 -
Female 170.00 34.00 5,780.00 -
Male 190.00 30.00 5,700.00 -
Female 160.00 34.00 5,440.00 -
Male 187.00 28.00 5,236.00 -
Female 160.00 32.00 5,120.00 -
Male 150.00 32.00 4,800.00 -
Male 140.00 36.00 5,040.00 -
Female 185.00 25.00 4,625.00 -
Female 140.00 32.00 4,480.00 -
Female 100.00 40.00 4,000.00 -
Female 187.00 20.00 3,740.00 -
Female 165.00 22.00 3,630.00 -
178,331.00
11,078.00
189,409.00
8,400.00
2,400.00
3,630.00
-
ame Middle Initial. Take note that if the ID NO starts with M, the name should be male. If ID NO starts with F, the name should be female.
in this column by extracting it from the given ID NO. using Excel functions.
MALE otherwise FEMALE.
references and commonly used Excel functions. Store each computed information in a new column.
alary rate per hour multiplied by Hours worked otherwise
worked per week - 40) multiplied by Salary rateper hour then multiplied by 1.5
at there is no heading since there is a space between the heading and the data.
uted values appropriately.
est Overtime pay. Display in separate cells.

GROSS PAY TAX DEDUCTION NET PAY
10,400.00 780.00 9,620.00

9,660.00 724.50 8,935.50
8,800.00 440.00 8,360.00
8,788.00 439.40 8,348.60
8,575.00 428.75 8,146.25
8,385.00 419.25 7,965.75
7,980.00 399.00 7,581.00
7,410.00 370.50 7,039.50
7,400.00 370.00 7,030.00
7,220.00 361.00 6,859.00
7,200.00 360.00 6,840.00
7,200.00 360.00 6,840.00
7,070.00 353.50 6,716.50
6,650.00 332.50 6,317.50
6,600.00 330.00 6,270.00
6,480.00 324.00 6,156.00
6,000.00 300.00 5,700.00
5,780.00 289.00 5,491.00
5,700.00 285.00 5,415.00
5,440.00 272.00 5,168.00
5,236.00 261.80 4,974.20
5,120.00 256.00 4,864.00
4,800.00 - 4,800.00
5,040.00 252.00 4,788.00
4,625.00 - 4,625.00
4,480.00 - 4,480.00
4,000.00 - 4,000.00
3,740.00 - 3,740.00
3,630.00 - 3,630.00
me should be female.

Duldulao Lexie Abbegail Payroll

Uploaded by

Copyright:

Available Formats

Duldulao Lexie Abbegail Payroll

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Duldulao Lexie Abbegail Payroll

Uploaded by

Copyright:

Available Formats

EXCEL Activity

DULDULAO, LEXIE ABBEGAIL F.

Start of given data

Payroll Period: January 5 to 10, 2021

ID NO. EMPLOYEE NAME

M123 Ilagan, Harvert O.

End of given data

NUMBER OF FEMALE 17.00

8. Rename the worksheet as Payroll.

GENDER SALARY RATE HOURS WORKED BASIC PAY OVERTIME PAY

Male 200.00 48.00 8,000.00 2,400.00

alary rate per hour multiplied by Hours worked otherwise

uted values appropriately.

est Overtime pay. Display in separate cells.

10,400.00 780.00 9,620.00

You might also like