Payroll Template Google Sheets Philippines

Standard Operating Procedure: Deployment and Maintenance of Philippine-Compliant Payroll Templates in Google Sheets

---

1. Executive Summary & Purpose

This Standard Operating Procedure (SOP) defines the operational parameters, structural schema, and compliance validation protocols for deploying and maintaining automated payroll templates within Google Sheets tailored for the Republic of the Philippines.

The purpose of this document is to eliminate manual calculation errors, ensure strict adherence to statutory deductions mandated by the Republic Act No. 10963 (TRAIN Law), Social Security System (RA 11199), Philippine Health Insurance Corporation (Universal Health Care Act), and Home Development Mutual Fund (Republic Act No. 9679), and maintain institutional-grade financial auditing trails across all subsidiary entities under Template Registry.

---

2. Scope & Prerequisites

2.1 Scope

This procedure applies to all Finance, Payroll Operations, and Human Resources personnel utilizing Google Workspace environments for compensation disbursement calculations within Philippine jurisdictions.

2.2 Prerequisites & Technical Environment

• Software: Google Workspace Business Standard or Enterprise Tier (latest stable desktop browser version).
• Access Level: Google Sheets "Editor" permissions for the master template; "View Only" access for general employee distribution views.
• Required Baseline Data:
  • Active Regional Tripartite Wages and Productivity Board (RTWPB) wage orders.
  • Current SSS, PhilHealth, and Pag-IBIG contribution schedule tables (JSON or static range lookup format).
  • Bureau of Internal Revenue (BIR) Withholding Tax tables (Expanded Withholding Tax / Compensation Income Tax under RR No. 11-2018 and subsequent amendments).

---

3. Roles & Responsibilities (RACI Matrix)

---

4. Step-by-Step Procedure

Phase 1: Environment Initialization & Schema Protection

• 1.1 Navigate to the Template Registry repository and deploy the master "PH-Payroll-Engine-v2.1" to the target organizational shared drive.
• 1.2 Rename the spreadsheet using the strict naming convention: YYYY-MM-[Entity]_Payroll_Master.
• 1.3 Lock structural metadata ranges (A1:Z10 representing headers and lookup matrices) via Data > Protect sheets and ranges > Set permissions to restrict edits exclusively to the Chief Architect and designated Payroll Leads.
• 1.4 Validate that calculation iteration is enabled: Navigate to File > Settings > Calculation and set Iterative calculation to Off to prevent circular dependency errors in net pay formulas.

Phase 2: Statutory Matrix Calibration

• 2.1 Update the CONFIG_STATUTORY tab with the active calendar year’s contribution ceilings:
  • Verify SSS Maximum Monthly Salary Credit (MSC) and corresponding employee/employer contribution split rates.
  • Verify PhilHealth premium rate tiers and statutory floor/ceiling limits.
  • Verify Pag-IBIG Fund mandatory contribution caps (PHP 100 employee / PHP 100 employer standard, adjusted for higher Maximum Fund Salary where applicable).
• 2.2 Input current BIR Annual and Semi-Monthly Withholding Tax schedules into the range TAX_TABLES!A1:D50.
• 2.3 Run the automated test suite script via Extensions > Apps Script to execute unit tests verifying statutory output against known test vectors.

Phase 3: Employee Master Data Ingestion

• 3.1 Populate the EMPLOYEE_MASTER worksheet with validated personnel records:
  • Column A: Employee ID (Format: TR-XXXX)
  • Column B: Full Legal Name (Last Name, First Name, Middle Name)
  • Column C: Tax Status / Exemption Status (Strictly following current BIR guidelines post-TRAIN law implementation where personal exemptions are zeroed out, tracking only status indicators).
  • Column D: Basic Monthly Salary (BMS) in PHP.
  • Column E: De Minimis Benefits and Fixed Allowances.
• 3.2 Audit data types across imported ranges to ensure numeric fields (Salary, Allowances) do not contain string characters or whitespace formatting.

Phase 4: Attendance, Overtime, and Variable Pay Integration

• 4.1 Import bi-monthly or monthly timekeeping exports from the attendance system into the TIME_LOGS staging tab using Ctrl+Shift+V (Paste values only).
• 4.2 Validate automated calculation of standard metrics via formula injections:
  • Daily Rate Formula: $\text{Daily Rate} = \frac{\text{Monthly Basic Salary} \times 12}{261 \text{ or working days per annum}}$
  • Hourly Rate Formula: $\text{Hourly Rate} = \frac{\text{Daily Rate}}{8}$
• 4.3 Input approved Overtime (OT), Night Differential (ND), Regular Holiday (RH), and Special Non-Working Holiday (SWH) multiplier hours into designated tracking columns.

Phase 5: Computation and Reconciliation

• 5.1 Navigate to the PAYROLL_RUN processing sheet and verify that dynamic lookups (XLOOKUP or INDEX/MATCH) successfully pull base rates and statutory deductions per employee ID.
• 5.2 Audit Gross Pay computations: $$\text{Gross Pay} = \text{Basic Pay} + \text{Overtime} + \text{Allowances} - \text{Absences/Undertime}$$
• 5.3 Audit Statutory Deductions:
  • Confirm SSS Employee Share is computed accurately based on the MSC bracket.
  • Confirm PhilHealth Employee Share equals exactly $50%$ of the total computed premium based on the active percentage rate.
  • Confirm Pag-IBIG Employee Share matches statutory limits.
• 5.4 Verify Withholding Tax computation utilizing the annualized or period-appropriate tax formula referencing taxable income (Gross Compensation less Employee Statutory Contributions).
• 5.5 Review Net Pay output balance to ensure absolute numerical parity: $$\text{Net Pay} = \text{Gross Pay} - (\text{SSS} + \text{PhilHealth} + \text{Pag-IBIG} + \text{Withholding Tax} + \text{Company Loans})$$

Phase 6: Final Sign-Off and Exporting

• 6.1 Generate the Bank Advice file (CSV format) matching local banking institutional requirements (e.g., BPI Corporate, BDO Cash Management Services) from the BANK_EXPORT summary tab.
• 6.2 Generate government statutory remittance reports (SSS R-3/ML2 format equivalents, PhilHealth RF-1, Pag-IBIG MCRF) for compliance filing.
• 6.3 Secure final digital sign-off from the Finance Director via the integrated Google Sheets approval workflow.
• 6.4 Lock the finalized payroll processing sheet by transitioning range edit protections to "Only You" to preserve audit integrity.

---

5. Quality Assurance & Pro-Tips

5.1 Best Practices

• Immutable Formulas: Never hardcode mathematical values into processing cells. All computations must reference designated configuration matrices (CONFIG_STATUTORY).
• Version Control: Archive a point-in-time copy of the spreadsheet (File > Version history > Name current version) immediately prior to final disbursement execution.
• Conditional Formatting Rules: Implement visual error traps using conditional formatting (e.g., highlight cells where Net Pay < 0 in bright red, or where Taxable Income fails reconciliation checks).

5.2 Common Pitfalls

• Copy-Paste Corruption: Pasting raw data directly using Ctrl+V often overwrites cell formulas and styling parameters. Always enforce Ctrl+Shift+V (Paste values only) when handling inbound data sets.
• Fractional Centavo Drift: Failing to wrap calculation outputs inside standard rounding functions (ROUND(value, 2)) results in cumulative centavo discrepancies during bank bulk transfers.

5.3 Metric Thresholds

• Calculation Variance Threshold: $\pm 0.00%$ discrepancy allowed against official statutory calculation schedules.
• Processing SLA: Complete operational run execution must not exceed 45 minutes per 100 active employee records.

---

6. Frequently Asked Questions (FAQ)

Q1: How are mid-year changes to national statutory contribution tables handled within the template?
A: Do not modify historical sheets. Update the centralized CONFIG_STATUTORY tab values. The template utilizes dynamic date-indexed lookup arrays that automatically apply new rates to active payroll periods matching the effective date of the regulatory adjustment.

Q2: What is the protocol when an employee has zero earnings due to an extended Leave Without Pay (LWOP)?
A: The system automatically flags zero-gross rows. Statutory contributions (SSS, PhilHealth, Pag-IBIG) for employees on total LWOP must be reviewed against internal company policy and statutory guidelines (e.g., whether employer-employee joint remittance is suspended or paid entirely by the employer), and adjusted manually in the override column with an accompanying audit note.