28395541-Payroll-v1 - Philippine Payroll Implemented a...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Philippine Payroll Implemented a two-way look up table using index and match functions in calculating withholding tax, SSS, Philhealth Procedures: 1 Enter Payroll Period Month: Period: 2 Update Master Data Tables Employees Withholding Tax Table Philhealth Table SSS Table Pagibig Others 3 Clear Payroll Sheet Clear 4 Process Payroll 1 Select employee number from the dropdow list in column A ( Note: Do not delete the first and second column of the payroll tab) 2. Enter any adjustments in Columns H to M 5 Reports Payroll Summary Payslip SSS Contribution Withholding Tax Philhealth Contribution Pagibig Contribution Deduction Flag $ include $ include $ include $ include Mar 2nd Half roll ow list in column A column of the payroll tab) Employee ID Last Name First Name Janet Margaret Nancy Robert Steven Steven Middle Name Seveede Petterson Anthony Greffin Jeck Jeck Period Mar-2nd Half Mar-2nd Half Mar-2nd Half Mar-2nd Half Mar-2nd Half Mar-2nd Half PayType Semi-Monthly Semi-Monthly Semi-Monthly Semi-Monthly Semi-Monthly Semi-Monthly Basic Pay 7,000.00 8,000.00 20,000.00 125,000.00 48,000.00 48,000.00 3 Leverling 4 Peacock 1 Davolio 7 King 5 Buchanan 5 Buchanan GrossPay 7,000.00 8,000.00 20,000.00 125,000.00 48,000.00 48,000.00 GrossPay Taxable 7000 8000 20000 125000 48000 48000 Employee Employee Employee Withholding SSS PHIC Pagibig Tax Contribution Contribution Contribution 466.70 175.00 100.00 755.83 500.00 200.00 100.00 802.57 500.00 375.00 100.00 4,415.83 500.00 375.00 100.00 37,376.33 500.00 375.00 100.00 12,842.89 500.00 375.00 100.00 12,842.89 NetPay 5,502.47 6,397.43 14,609.17 86,648.67 34,182.11 34,182.11 Employee ID Period Last Name Fuller 2 Feb-2nd Half First Name Andrew Middle Name James Data Basic Pay: Absent/Late/Tardy: OT Pay: Other Taxable Income: Non Taxable Income: GrossPay: Employee SSS Contribution: Employee PHIC Contribution: Employee Pagibig Contribution: Withholding Tax: NetPay: Total 15000 0 15000 500 375 100 2753.23 11271.77 Employee ID Period (All) Feb-2nd Half Sum of Withholding Tax Last Name First Name Davolio Nancy Fuller Andrew Leverling Janet Grand Total TIN Middle Name 2263579 Anthony 2347685 James 2345679 Seveede Total 4,415.83 2,753.23 755.83 7,924.89 Employee ID Period (All) Feb-2nd Half Data SSS ID Sum of Employer SSS Contribution 1233456 770.00 1233457 770.00 1233458 719.30 2,259.30 Last Name Davolio Fuller Leverling Grand Total First Name Nancy Andrew Janet Middle Name Anthony James Seveede Sum of Employee SSS Contribution 500.00 500.00 466.70 1,466.70 Period Feb-2nd Half Employee ID Last Name 1 Davolio 2 Fuller 3 Leverling First Name Nancy Andrew Janet Middle Name Anthony James Seveede PHIC ID 4589823 4589824 4589825 Grand Total Data Sum of Employer PHIC Contribution Sum of Employee PHIC Contribution 375.00 375.00 375.00 375.00 175.00 175.00 925.00 925.00 Period Feb-2nd Half Employee ID Last Name 1 Davolio 2 Fuller 3 Leverling First Name Nancy Andrew Janet Middle Name Anthony James Seveede Pagibig ID 98765 98766 98767 Grand Total Data Sum of Employer Pagibig Contribution Sum of Employee Pagibig Contribution 100.00 100.00 100.00 100.00 100.00 100.00 300.00 300.00 Employee ID Last Name 1 Davolio 2 Fuller 3 Leverling 4 Peacock 5 Buchanan 6 Suyama 7 King 8 Callahan 9 Dodsworth 10 Benosa First Name Nancy Andrew Janet Margaret Steven Michael Robert Laura Anne Claresfe MiddleName Anthony James Seveede Petterson Jeck Jason Greffin Line Fellow Deleon Title Title Of Courtesy Birth Date Sales Representative Ms. 08-Dec-1968 Vice President, Sales Dr. 19-Feb-1952 Sales Representative Ms. 30-Aug-1963 Sales Representative Mrs. 19-Sep-1958 Sales Manager Mr. 04-Mar-1955 Sales Representative Mr. 02-Jul-1963 Sales Representative Mr. 29-May-1960 Inside Sales Coordinator Ms. 09-Jan-1958 Sales Representative Ms. 02-Jul-1969 President Ms. Hire Date 507 - 20th Ave. E. Address 01-May-1992 Apt. 2A 14-Aug-1992 908 W. Capital Way 01-Apr-1992 722 Moss Bay Blvd. 03-May-1993 4110 Old Redmond Rd. 17-Oct-1993 Coventry House 14 Garrett Hill 17-Oct-1993 Edgeham Hollow Miner Rd. 02-Jan-1994 Winchester Way 05-Mar-1994 4726 - 11th Ave. N.E. 15-Nov-1994 7 Houndstooth Rd. City Seattle Tacoma Kirkland Redmond London London London Seattle London Region WA WA WA WA WA Postal Code 98122 98401 98033 98052 SW1 8JR EC2 7JR RG1 9SP 98105 WG2 7LT Country USA USA USA USA UK UK UK USA UK Home Phone (206) 555-9857 (206) 555-9482 (206) 555-3412 (206) 555-8122 (71) 555-4848 (71) 555-7773 (71) 555-5598 (206) 555-1189 (71) 555-4444 Extension 5467 3457 3355 5176 3453 428 465 2344 452 Photo EmpID1.bmp EmpID2.bmp EmpID3.bmp EmpID4.bmp EmpID5.bmp EmpID6.bmp EmpID7.bmp EmpID8.bmp EmpID9.bmp Andrew received his BTS commercial and a Ph.D. Education includes a BA in psychology from of in international marketing from the University Colorado State University.chemistry from and reads Dallas. He is fluent in French and Italian Boston Janet has a BS degree Notes also completed in She "The Art of the has also completedis a member of Gollege). holdsCold the English literature from C erman. He joined Call." Nancy as aAndrews She a BA in company St.certificate a sales Margaret Steven Buchanan graduated to sales manager and Trogram in foodwas promotedfrom representative, retailing management. American p oastmasters International. BSC degree. Upon Concordia College and an University, a graduatewith aMA from the Janet was Scotland, of Michael is sales wiredthenanam associate and was promoted to h as as of companyArts.Sussex University (MA, Ioining the Culinary Universitywas temporarily Los j nstitute eales representative.as a She of Corps andat he conomics) served in the sales representative, the Robert King andLondon office before returning to Peace California spent 6 months in an orientation program at traveled assigned(MBA, marketing). He has also taken the s xtensively before completing his degree in English the Angeles to the eer permanent post in Seattle. hoursesoffice and then returnedand "Time S the University of Michigan and then joining the c teattle "Multi-Cultural Selling" to his permanent a pompany. After w Sales Professional." He is London, Management forcompleting a course entitled c ost inreceived a the in psychology from the Laura BA fluent in in of Washington. She has also completed "Selling Japanes he was transferred to the University Europe," London of business French. She reads and writes a course in French. a BA degree in English from St. Lawrence Anne has College. She is fluent in French and German. Reports To Fuller, Andrew Fuller, Andrew Fuller, Andrew Fuller, Andrew Fuller, Andrew Buchanan, Steven Buchanan, Steven Fuller, Andrew Buchanan, Steven Buchanan, Steven TIN TaxCode 2263579 S 2347685 HF_1 2345679 ME 1235765 HF_3 4567892 ME_3 4567893 ME_2 4567894 ME_4 4567895 HF_3 4567896 HF_4 4567897 HF Pay Type Semi-Monthly Semi-Monthly Semi-Monthly Semi-Monthly Semi-Monthly Semi-Monthly Semi-Monthly Semi-Monthly Semi-Monthly Semi-Monthly SSS Pagibig PHIC ID Basic Pay 1233456 98765 4589823 20000 1233457 98766 4589824 15000 1233458 98767 4589825 7000 1233459 98768 4589826 8000 1233460 98769 4589827 48000 1233461 98770 4589828 25000 1233462 98771 4589829 125000 1233463 98772 4589830 100000 1233464 98773 4589831 75000 1233465 98774 4589832 3542 MonthlyPay 40000 30000 14000 16000 96000 50000 250000 200000 150000 7084 ID NumberFixed Amount 1 $0.00 2 $0.00 3 $20.83 4 $104.17 5 $354.17 6 $937.50 7 $2,083.33 8 $5,208.33 Rate Z S HF ME HF_1 HF_2 0.00% $0.00 $1.00 $1.00 $1.00 $1.00 $1.00 5.00% $0.00 $833.00 $1,042.00 $1,333.00 $1,375.00 $1,708.00 10.00% $417.00 $1,250.00 $1,458.00 $1,750.00 $1,792.00 $2,125.00 15.00% $1,250.00 $2,083.00 $2,292.00 $2,583.00 $2,625.00 $2,958.00 20.00% $2,917.00 $3,750.00 $3,958.00 $4,250.00 $4,292.00 $4,625.00 25.00% $5,833.00 $6,667.00 $6,875.00 $7,167.00 $7,208.00 $7,542.00 30.00% $10,417.00 $11,250.00 $11,458.00 $11,750.00 $11,792.00 $12,125.00 32.00% $20,833.00 $21,667.00 $21,875.00 $22,167.00 $22,208.00 $22,542.00 HF_3 HF_4 ME_1 ME_2 ME_3 ME_4 $1.00 $1.00 $1.00 $1.00 $1.00 $1.00 $2,042.00 $2,375.00 $1,667.00 $2,000.00 $2,333.00 $2,667.00 $2,458.00 $2,792.00 $2,083.00 $2,417.00 $2,750.00 $3,083.00 $3,292.00 $3,625.00 $2,917.00 $3,250.00 $3,583.00 $3,917.00 $4,958.00 $5,292.00 $4,583.00 $4,917.00 $5,250.00 $5,583.00 $7,875.00 $8,208.00 $7,500.00 $7,833.00 $8,167.00 $8,500.00 $12,458.00 $12,792.00 $12,083.00 $12,417.00 $12,750.00 $13,083.00 $22,875.00 $23,208.00 $22,500.00 $22,833.00 $23,167.00 $23,500.00 Monthly Salary Bracket Monthly Salary Range 1 4000 and Below 2 5000 to 5999 3 6000 to 6999 4 7000 to 7999 5 8000 to 8999 6 9000 to 9999 7 10000 to 10999 8 11000 to 11999 9 12000 to 12999 10 13000 to 13999 11 14000 to 14999 12 15000 to 15999 13 16000 to 16999 14 17000 to 17999 15 18000 to 18999 16 19000 to 19999 17 20000 to 20999 18 21000 to 21999 19 22000 to 22999 20 23000 to 23999 21 24000 to 24999 22 25000 to 25999 23 26000 to 26999 24 27000 to 27999 25 28000 to 28999 26 29000 to 29999 27 30000 and up Salary Base Total Monthly Contribution Employee Share $4,000.00 $100.00 $50.00 $5,000.00 $125.00 $62.50 $6,000.00 $150.00 $75.00 $7,000.00 $175.00 $87.50 $8,000.00 $200.00 $100.00 $9,000.00 $225.00 $112.50 $10,000.00 $250.00 $125.00 $11,000.00 $275.00 $137.50 $12,000.00 $300.00 $150.00 $13,000.00 $325.00 $162.50 $14,000.00 $350.00 $175.00 $15,000.00 $375.00 $187.50 $16,000.00 $400.00 $200.00 $17,000.00 $425.00 $212.50 $18,000.00 $450.00 $225.00 $19,000.00 $475.00 $237.50 $20,000.00 $500.00 $250.00 $21,000.00 $525.00 $262.50 $22,000.00 $550.00 $275.00 $23,000.00 $575.00 $287.50 $24,000.00 $600.00 $300.00 $25,000.00 $625.00 $312.50 $26,000.00 $650.00 $325.00 $27,000.00 $675.00 $337.50 $28,000.00 $700.00 $350.00 $29,000.00 $725.00 $362.50 $30,000.00 $750.00 $375.00 Employer Share $50.00 $62.50 $75.00 $87.50 $100.00 $112.50 $125.00 $137.50 $150.00 $162.50 $175.00 $187.50 $200.00 $212.50 $225.00 $237.50 $250.00 $262.50 $275.00 $287.50 $300.00 $312.50 $325.00 $337.50 $350.00 $362.50 $375.00 RangeFrom $1,000.00 $1,250.00 $1,750.00 $2,250.00 $2,750.00 $3,250.00 $3,750.00 $4,250.00 $4,750.00 $5,250.00 $5,750.00 $6,250.00 $6,750.00 $7,250.00 $7,750.00 $8,250.00 $8,750.00 $9,250.00 $9,750.00 $10,250.00 $10,750.00 $11,250.00 $11,750.00 $12,250.00 $12,750.00 $13,250.00 $13,750.00 $14,250.00 $14,750.00 RangeTo Monthly Salary Credit $1,249.99 1,000.00 $1,749.99 1,500.00 $2,249.99 2,000.00 $2,749.99 2,500.00 $3,249.99 3,000.00 $3,749.99 3,500.00 $4,249.00 4,000.00 $4,749.99 4,500.00 $5,249.99 5,000.00 $5,749.99 5,500.00 $6,249.99 6,000.00 $6,749.99 6,500.00 $7,249.99 7,000.00 $7,749.99 7,500.00 $8,249.99 8,000.00 $8,749.99 8,500.00 $9,249.99 9,000.00 $9,749.99 9,500.00 $10,249.99 10,000.00 $10,749.99 10,500.00 $11,249.99 11,000.00 $11,749.99 11,500.00 $12,249.99 12,000.00 $12,749.99 12,500.00 $13,249.00 13,000.00 $13,749.99 13,500.00 $14,249.99 14,000.00 $14,749.99 14,500.00 $15,249.99 15,000.00 Employer 50.70 76.00 101.30 126.70 152.00 177.30 202.70 228.00 253.30 278.70 304.00 329.30 354.70 380.00 405.30 430.70 456.00 481.30 506.70 532.00 557.30 582.70 608.00 633.30 658.70 684.00 709.30 734.70 760.00 Employee 33.30 50.00 66.70 83.30 100.00 116.70 133.30 150.00 166.70 183.30 200.00 216.70 233.30 250.00 266.70 283.30 300.00 316.70 333.30 350.00 366.70 383.30 400.00 416.70 433.30 450.00 466.70 483.30 500.00 Total 84.00 126.00 168.00 210.00 252.00 294.00 336.00 378.00 420.00 462.00 504.00 546.00 588.00 630.00 672.00 714.00 756.00 798.00 840.00 882.00 924.00 966.00 1,008.00 1,050.00 1,092.00 1,134.00 11,760.00 1,218.00 1,260.00 ECC $10.00 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00 $10.00 TotalEmployer $33.30 $50.00 $111.30 $136.70 $162.00 $187.30 $212.70 $238.00 $263.30 $288.70 $314.00 $339.30 $364.70 $390.00 $415.30 $440.70 $466.00 $491.30 $516.70 $542.00 $567.30 $592.70 $618.00 $643.30 $668.70 $694.00 $719.30 $744.70 $770.00 TotalEmployee $60.70 $86.00 $66.70 $83.30 $100.00 $116.70 $133.30 $150.00 $166.70 $183.30 $200.00 $216.70 $233.30 $250.00 $266.70 $283.30 $300.00 $316.70 $333.30 $350.00 $366.70 $383.30 $400.00 $416.70 $433.30 $450.00 $466.70 $483.30 $500.00 TotalCombined $94.00 $136.00 $178.00 $220.00 $262.00 $304.00 $346.00 $388.00 $430.00 $472.00 $514.00 $556.00 $598.00 $640.00 $682.00 $724.00 $766.00 $808.00 $850.00 $892.00 $934.00 $976.00 $1,018.00 $1,060.00 $1,102.00 $1,144.00 $1,186.00 $1,228.00 $1,270.00 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 ID PayType 1 Daily 2 Weekly 3 Semi-Monthly 4 Monthly MonthFactor 26 4 2 1 Pagibig Contributions Employee 100 Employer Total 100 200 Payroll Periods Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec cycle First Half 2nd Half deduction Flags member Withholding tax SSS Philhealth Pagibig pseudcode flag 1 1 1 1 Perform two-way table lookups All spreadsheets support lookup functions, tools that return a value from a table by looking up another value in the table. An in The lookup functions in Excel are only appropriate for one-way lookups, however. If you need to perform a two-way lookup, yo The formula in cell H4 looks up the entries in cells H2 and H3 and then returns the corresponding value from the table. The for =INDEX(A1:E14, MATCH(H2,A1:A14,0), MATCH(H3,A1:E1,0)). The formula uses the INDEX function, with three arguments. The first is the entire table range (A1:A14). The second uses the You may prefer to take advantage of Excel's natural-language formulas. For example, enter the following formula to return Spr =June Sprockets If natural-language formulas aren't working, select Tools, Options, click the Calculation tab, and place a check mark next to "A sample Bisugo jan feb mar apr may jun jul aug sep oct nov dec 100 200 300 600 340 232 23 23 23 454 45 4545 Pusit lumot 34 434 343 345 34 67 454 567 454 565 4 7 Pusit kalawang 224 624 533 535 224 257 644 757 644 755 194 197 month feb product Pusit lumot sales amount 434 another value in the table. An income tax table is a good example. You can write a formula that uses the VLOOKUP function to determine to perform a two-way lookup, you'll need more than the standard functions. The figure below shows a simple example. ing value from the table. The formula in H4 is: (A1:A14). The second uses the MATCH function to return the offset of the desired month in column A. The third argument uses the MATC e following formula to return Sprocket sales for June: d place a check mark next to "Accept labels in formulas." Be aware that using natural language formulas is not 100% reliable! KUP function to determine the tax rate for a given argument uses the MATCH function to return the off 100% reliable! ...
View Full Document

This note was uploaded on 08/12/2010 for the course ACC 452 taught by Professor Mr.cula during the Spring '10 term at Abraham Baldwin Agricultural College.

Ask a homework question - tutors are online