Purchase Order Template Excel Philippines

1. System Overview & Purpose

• Purpose: This enterprise-grade Purchase Order (PO) framework is engineered for Philippine business compliance, integrating local statutory requirements including Value-Added Tax (VAT) computations, Withholding Tax on Goods/Services (EWT), and multi-branch SEC/DTI-registered entity formatting.
• Scope: Manages the end-to-end procurement lifecycle from requisition matching to goods receipt confirmation and accounts payable (AP) readiness.
• Data Update Cadence: Transactional data is updated in real-time upon PO issuance. Master data (suppliers, tax rates, chart of accounts) is audited and updated monthly. FX rates (if applicable) are locked at the time of PO generation based on Bangko Sentral ng Pilipinas (BSP) daily closing rates.

2. Column Definitions & Validation Rules Table

3. Complete Master Data Table

4. Formulas & Calculation Logic

• Gross Line Amount (Subtotal): =[@Quantity]*[@Unit_Price]
• Value-Added Tax (12% Philippine standard): =IF([@VAT_Type]="Vatable (12%)", ([@Quantity]*[@Unit_Price]) / 1.12 * 0.12, 0) (Note: Philippine Peso pricing is customarily quoted VAT-inclusive; extraction divides by 1.12)
• Net of VAT (Base Amount for EWT): =IF([@VAT_Type]="Vatable (12%)", ([@Quantity]*[@Unit_Price]) / 1.12, [@Quantity]*[@Unit_Price])
• Expanded Withholding Tax (EWT): =[@Net_of_VAT]*[@EWT_Rate]
• Total Payable Amount: =([@Quantity]*[@Unit_Price]) - [@EWT]
• Vendor Dynamic Lookup (Data Entry Automation): =XLOOKUP([@Vendor_ID], Vendors[Vendor_ID], Vendors[Vendor_Name], "Not Found")
• PO Total Aggregation (Header Level): =SUMIFS(Data[Total_Payable], Data[PO_Number], "PO-2023-0101")
• Active PO Status Flag Check: =IF(ISBLANK([@PO_Number]), "", IF(OR([@Status]="Closed", [@Status]="Cancelled"), "Archived", "Active"))

5. Summary KPI Dashboard

6. Standard Operating Workflow

1. Requisition & Master Data Check:
   • Verify that the requesting department head has approved the Purchase Requisition (PR).
   • Ensure the supplier exists in the Vendors tab with a verified, active Philippine TIN and valid SEC/DTI registration status.
2. PO Data Entry & Validation:
   • Input the generated PO_Number using the format PO-YYYY-0000.
   • Select the Vendor_ID from the drop-down list to auto-populate vendor details via XLOOKUP.
   • Enter line items using approved Item_Code identifiers. Input exact quantities and negotiated unit prices.
3. Tax Determination (BIR Compliance):
   • Confirm the correct VAT_Type. If the supplier is VAT-registered, select Vatable (12%) to ensure correct extraction of the 12% statutory output/input tax.
   • Assign the correct EWT_Rate based on the nature of income payments (e.g., 1% for goods, 2% for services under RR No. 2-98).
4. Review and Authorization:
   • Inspect the Summary KPI Dashboard to verify that the total PO value does not breach department budget limits.
   • Export the finalized sheet to PDF using the designated print template layout, adhering to Philippine corporate documentation standards.
5. Issuance, Delivery, and AP Matching:
   • Transmit the official PO PDF to the vendor via secure corporate email.
   • Upon physical delivery at the designated site (e.g., Makati Warehouse or BGC HQ), reconcile the Delivery Receipt (DR) and Sales Invoice (SI) against the PO quantities.
   • Update the Status column from Issued to Partially Received, Closed, or cancel where discrepancies occur prior to endorsing documents to Accounts Payable for check voucher preparation or online banking transfer.