Purchase Order Template Malaysia Excel

1. System Overview & Purpose

• Purpose: To manage, track, and execute domestic and international purchase orders (POs) originating from Malaysia, ensuring full compliance with the Royal Malaysian Customs Department (SST regulations), local withholding tax provisions, and multi-currency exchange rates (MYR, USD, SGD, EUR).
• Scope: End-to-end procurement lifecycle from requisition logging, vendor matching, tax calculation (Sales and Service Tax / SST), delivery tracking, to financial reconciliation and accounts payable (AP) matching.
• Data Update Cadence: Real-time entry for new PO generation; daily reconciliation of delivery receipts; weekly updates for vendor master data and exchange rates.

2. Column Definitions & Validation Rules Table

3. Complete Master Data Table

4. Formulas & Calculation Logic

Implement these formulas in your calculated columns to maintain automated financial tracking. Assuming row 2 is the active data row:

• Subtotal (Foreign Currency): =Quantity*Unit_Price
• Subtotal (MYR Base): =ROUND(Quantity * Unit_Price * Exchange_Rate_MYR, 2)
• SST Amount (MYR Base): =IF(SST_Applicable="YES", ROUND(Subtotal_MYR * SST_Rate, 2), 0)
• Total PO Value (MYR Inclusive of SST): =Subtotal_MYR + SST_Amount_MYR
• Vendor Lookup (Dynamic Population): =XLOOKUP(Vendor_ID, Vendor_Master[Vendor_ID], Vendor_Master[Vendor_Name], "Invalid Vendor")
• PO Aging Tracker (Days Open): =IF(OR(PO_Status="Closed", PO_Status="Cancelled"), "-", DATEDIF(Requisition_Date, TODAY(), "d"))
• SST Compliance Audit Check: =IF(AND(SST_Applicable="YES", ISBLANK(Vendor_BRN)), "NON-COMPLIANT: MISSING BRN", "COMPLIANT")

5. Summary KPI Dashboard

Construct your executive dashboard using these high-level summary formulas referencing the Master Data table (assuming data spans rows 2 to 100):

• Total Active PO Spend (MYR): =SUMIFS(Total_PO_Value_MYR_Column, PO_Status_Column, "<>Cancelled", PO_Status_Column, "<>Closed")
• Pending Approval Exposure: =SUMIF(PO_Status_Column, "Pending Approval", Total_PO_Value_MYR_Column)
• Total SST Committed (MYR): =SUMIFS(SST_Amount_MYR_Column, PO_Status_Column, "<>Cancelled")
• Open PO Count: =COUNTIF(PO_Status_Column, "Issued") + COUNTIF(PO_Status_Column, "Pending Approval")
• Health Status Flags & Conditional Formatting Rules:
  • Overdue Delivery Flag: =IF(AND(Expected_Delivery_Date < TODAY(), NOT(OR(PO_Status="Closed", PO_Status="Cancelled"))), "CRITICAL: OVERDUE", "ON TRACK")
    • Conditional Formatting Rule: Apply red fill if cell equals "CRITICAL: OVERDUE".
  • High-Value Threshold Flag (DOA Check): =IF(Total_PO_Value_MYR > 50000, "BOARD/C-LEVEL REQ", "STANDARD")
    • Conditional Formatting Rule: Apply yellow fill if cell equals "BOARD/C-LEVEL REQ".

6. Standard Operating Workflow

1. Requisition & Setup:
   • Navigate to the Data Entry sheet. Select the Vendor_ID from the dropdown list. Verify that the Vendor Business Registration Number (BRN) populates automatically to comply with Malaysian e-invoicing and SST validation mandates.
2. Line Item & Currency Input:
   • Enter the Item_SKU, Quantity, and Unit_Price.
   • Select the transaction currency. If transacting in USD, SGD, EUR, or CNY, input the official Bank Negara Malaysia (BNM) daily mid-rate into Exchange_Rate_MYR.
3. Tax Configuration (SST):
   • Set SST_Applicable to YES or NO. Select the applicable Malaysian tax rate (6.00% for applicable services/digital goods, 8.00% for standard taxable goods/services, or 0.00% for exempt/export items).
4. Authorization & Approval Workflow:
   • Submit the row. If the calculated Total_PO_Value_MYR exceeds RM50,000, the status defaults to Pending Approval, routing the alert to the designated Approver_ID based on the company's Delegation of Authority (DoA) matrix.
5. Issuance & Tracking:
   • Once approved, change the PO_Status to Issued. Print or export the sheet via the designated Print Area template to generate the formal PDF Purchase Order for transmission to the vendor.
6. Reconciliation & Closure:
   • Upon receipt of goods and the vendor's e-invoice, match the physical Goods Received Note (GRN) quantities against the PO. Update the status to Closed upon successful Accounts Payable (AP) matching.