Purchase Requisition (PR) Management System in Excel

In corporate procurement, company budgets are rarely lost through major capital equipment contracts. Instead, financial waste creeps in through small, unmonitored day-to-day operational purchases. When department managers order IT peripherals, replacement printer toner or facilities cleaning supplies, requests often happen through informal chat threads, casual emails or unnumbered paper memos.

Without a centralized procurement system, separate branch offices frequently order the exact same supplies twice. These duplicate purchases bypass administrative authorization limits, inflate carrying costs and create hours of extra reconciliation during annual fiscal audits.

To eliminate these purchasing bottlenecks, we designed the Purchase Requisition (PR) Management System in Microsoft Excel. This workbook locks vendor selections using strict data validation, detects duplicate orders automatically using multi-condition COUNTIFS formulas and produces print-ready, sequential PR forms ready for management sign-off.

The Problem: Uncontrolled Departmental Purchasing

When organizations expand beyond twenty staff members, decentralized buying introduces three primary operational risks:

• Loss of Volume Pricing: Different departments purchase stationery from local retail stores at full consumer prices instead of combining orders through negotiated corporate supplier accounts.
• Duplicate Monthly Orders: An operations lead and a branch admin order replacement inventory during the same week because neither knows the other submitted a purchase request.
• Audit Trail Breakdowns: Accounts teams receive supplier bills without an approved paper trail, causing delays in vendor invoice settlement.

Procurement Authorization Hierarchy & Approval Matrix

To maintain financial control, organizations enforce approval thresholds based on total purchase value. Standardizing these financial bands prevents unauthorized spending while keeping routine small purchases moving quickly.

Table 1: Structured purchasing authority tiers defining financial approval levels across corporate cost centers.

Locking Approved Suppliers with Data Validation

Unregulated purchasing harms negotiated supplier terms. If staff buy office hardware from arbitrary retail stores, the business loses out on annual credit lines and GST input tax credits.

This Excel system enforces supplier compliance through a central Validation sheet. The procurement manager defines pre-approved vendor names, tax identification numbers and assigned expense categories on a protected master tab.

The main entry screen uses Data Validation dropdown menus connected to this catalog. If an employee tries to type an unapproved vendor name, Excel rejects the entry immediately. This simple safeguard keeps company purchases aligned with authorized corporate suppliers.

The Validation Master Sheet. Governs vendor and category dropdown menus to prevent unauthorized departmental purchases.

Automated Duplicate Order Detection with COUNTIFS

Catching duplicate orders before money is spent is essential for budget discipline. The master registry uses background Microsoft Support COUNTIFS formulas to scan active records for matching orders automatically.

Table 2: Multi-criteria audit fields monitored to identify duplicate procurement requisitions.

The workbook tests for duplicate submissions using this formula:

= COUNTIFS(Company_Range, "MediaCorp", Category_Range, "IT Assets", Month_Range, "June")

If Excel finds that an order for IT Assets already exists for MediaCorp within the active billing month, Conditional Formatting highlights the duplicate row in soft red. This visual warning stops duplicate purchase orders before managers sign documents.

The Central Registry. Multi-condition formulas scan past entries to catch repeated requests within the same month.

Automated PR Form Generation using VLOOKUP

Typing purchase order slips by hand produces typos and wastes valuable administrative time. This system separates data entry from document generation completely.

Once an order is entered in the registry, staff navigate to the Requisition Form tab and pick the PR serial number from a dropdown cell.

An array of VLOOKUP and INDEX/MATCH formulas pulls the transaction details (including date, department, approving manager, specifications and quantity) and formats the slip automatically.

The sheet also builds an official PR Number using string concatenation:

= "PR/" & Company_Code & "/" & TEXT(Date_Cell, "YY-MM") & "/" & TEXT(Row_ID, "000")

This generates clean identifiers like PR/Admin/PUNE/25-26/014. The final output is an A4 requisition form with formal signature lines for the Preparer, Reviewer and Approving Manager.

The Automated Requisition Form. Selecting a PR serial number populates the printable voucher automatically.

Managing Multiple Cost Centers & Subsidiaries

Many organizations run several branch locations or partner entities from a central administrative office. Maintaining isolated spreadsheets for each branch leads to conflicting records.

This system consolidates all purchasing data into a unified database. By assigning a Company Code to every row, procurement teams can review spending across branches independently. During quarterly budget meetings, managers filter the database by location to evaluate departmental spending habits in seconds.

Connecting Procurement with Inventory & Facility Operations

Standardized purchasing controls support clean stockroom and site operations across your business. For tracking spare parts storage and inventory reorder points, integrate this requisition workflow with our Stock Allocation & Inventory Tracker. If your team oversees office stationery, pantry stock and outsourced contractors, explore our companion models for Corporate Office Consumption Reporting and Vendors Manpower Attendance & Salary Tracking to keep departmental spending completely audit-ready.

Frequently Asked Questions

Can we modify the PR number format to match our company naming convention?
Yes. You can adjust the text concatenation formula in the PR Number cell to add custom cost center codes, branch initials or financial year formats.

What happens if a purchase order includes items from multiple vendors?
Procurement best practices require separate PR numbers for different vendors. This ensures invoice matching and accounts payable tracking remain accurate for each supplier account.

Does this workbook run on Microsoft Excel for Mac?
Yes. Because the model relies on standard formulas like COUNTIFS, VLOOKUP and built-in Data Validation without Windows-only macros, it works smoothly on both Windows and macOS.

If your organization manages multi-tier purchase approval matrices or customized department cost codes in Excel, leave a comment below and I will help you adjust the formula rules.