KEMBAR78
Payroll Setup and Calculation Guide | PDF | Teaching Mathematics | Business
Scribd
Upload
175 views2 pages

Payroll Setup and Calculation Guide

This document contains instructions for setting up multiple sheets in an Excel workbook to calculate employee payroll. Sheet 1 (SETUP) contains fields to set up payroll parameters like month, year, and employee details. Sheet 2 (ATT) will pull employee details from Sheet 1 and include fields to track hours worked. Sheet 3 (TBL) will contain contribution tables for social security and philhealth. Sheet 4 (PAYROLL) will use formulas to calculate elements of each employee's paycheck like basic pay, overtime, deductions, and net pay based on data from the other sheets. Formulas are provided as examples.

Uploaded by

Aries Vee
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
175 views2 pages

Payroll Setup and Calculation Guide

This document contains instructions for setting up multiple sheets in an Excel workbook to calculate employee payroll. Sheet 1 (SETUP) contains fields to set up payroll parameters like month, year, and employee details. Sheet 2 (ATT) will pull employee details from Sheet 1 and include fields to track hours worked. Sheet 3 (TBL) will contain contribution tables for social security and philhealth. Sheet 4 (PAYROLL) will use formulas to calculate elements of each employee's paycheck like basic pay, overtime, deductions, and net pay based on data from the other sheets. Formulas are provided as examples.

Uploaded by

Aries Vee
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

Sheet Name: SETUP

Merge cells: A1 & B1, A2 & B2, A3 & B3, A4 & B4, A6-G6
In C1, use data validation List with values January to December
In C2, use data validation between 2018 to 2050
In C4, use data validation between 16 to 24
Format as table (A7 to G9) use row 7 as header
Data validation for EMPID not to accept duplicate values
Data validation for Birthdate, should not accept date beyond today
Data validation for Sex, M or F only
Data Validation for Rate per Day 300 to 5000

Sheet Name: ATT


Merge Cells A1-C1, A2-C3
In A2, concatenate “For the Month of” with value of C1 & C2 from SETUP Sheet
Format as Table (A3 – C5)
In A4, formula to get EmpId in SETUP sheet (=SETUP!A8)
In B4, formula to concatenate LastName, FirstName, and Middle Initial. (see sample
output in B4 as reference)
In C4, data validation under No. of Hrs. Worked, 16 to 24
Sheet Name: TBL This sheet will contain the table for Philhealth Contribution and SSS Contribution Tables

Sheet Name: PAYROLL

Formulas Used (for your reference)


For EMPID: =IF(SETUP!A8="", "", SETUP!A8) (same formula for lastname for hrs worked except the cell reference)
For BASIC: =IF(SETUP!G8="", "", SETUP!G8 * SETUP!$C$3)
For OT Pay: =IF(ATT!C4="","",IF(ATT!C4<SETUP!$C$3,0,SETUP!G8*(F7-SETUP!$C$3)*1.5))
For COLA: =IF(F7="", "", F7 * 120)
For Incentives: =IF(ATT!C4="","",IF(ATT!C4>=SETUP!$C$3,1000,0))
For GROSS PAY: =IF(G7="", "", SUM(G7:J7))
For Ded fr. Abs: =IF(G7="", "", IF(ATT!C4>SETUP!$C$3,0, SETUP!G8*(SETUP!$C$3-F7)))
For SSS Cont.: =IF(BP="", "", ROUND(VLOOKUP(BP, TBL!$G$4:$J$34, 4), 2))
For PhilHealth: =IF(BP="", "", VLOOKUP(BP, TBL!$A$4:$C$31, 3))
For Pagibig: =IF(BP="", "", IF(BP>1500, BP* 2%, BP*1%))
In column P: =IF(GP="", "", GP-(SSS+PH+PC)) this is mandatory deduction before computing wtax
For WTax: =IF(Man="", "", IF(Man>=666667, 200833.33 + ((Man-666667) * 35%), IF(Man>=166667, 400833.33 +
((Man-166667) * 32%), IF(Man>=66667, 10833.33 + ((Man-66667) * 30%), IF(Man>=33333, 2500 +
((Man-33333) * 25%), IF(Man>=20833.33, 0 + ((Man-20833) * 20%), 0))))))
For Tot.Deduc: =IF(SSS="", "", (DA+ SSS+PH+PC+WTAX))
For Net Pay: =IF(GP="", "", GP-TD)
Compute also the average basic pay, average gross pay, average total deduction, and average net pay
NOTE: Column P is hidden
The area for data is formatted as table
Columns are given names
There should be Total from Basic Pay to Net Pay

You might also like