📋 Purchase Rate Comparison
& Audit Annexure Generator
Upload a PO Register, map columns once, and instantly identify items procured from multiple suppliers in the same month at above-minimum rates — with excess cost quantified and audit annexures ready to export.
What This Tool Does
In procurement audits a common risk is paying different rates to different suppliers for the same item in the same period. This tool automates that comparison — parsing your PO Register, grouping by Item Code × Month, identifying the lowest available rate, and flagging every row where a higher rate was paid when a cheaper source existed that month.
The output is a structured Audit Annexure pairing each exception with its lowest-rate counterpart, plus a complete Working Sheet with all calculated columns preserved in original row order — ready to attach to an internal audit report.
Input File Format
Upload a PO Register as an .xlsx or .xls
file. The file can have multiple sheets — you choose which sheet to read.
Column headers can be in any row (configurable); rows above the header row are ignored.
Mandatory Columns
All eight columns below must be present (names do not need to match exactly — the tool auto-guesses and you confirm).
| Column Label in Tool | What It Should Contain | Auto-matched Aliases | Required |
|---|---|---|---|
| PoNo | Purchase Order number / reference | PO No, PO Number, Purchase Order | Yes |
| PoDate | Date the PO was raised | PO Date, po_date | Yes |
| Supplier Code | Unique supplier identifier | SupplierCode, Vendor Code | Yes |
| SupplierName | Supplier / vendor name | Supplier Name, Vendor Name | Yes |
| Item Code | Unique item / material code | ItemCode, Material Code | Yes |
| ItemDescription | Human-readable item name | Item Description, Description | Yes |
| Price | Unit rate (numeric) | Rate | Yes |
| Sum of Po.Qty | Quantity ordered (numeric) | Quantity, Qty, PO Qty | Yes |
Optional Columns
These columns pass through to the Working Sheet and can be used as filters. If your file does not have them, select "— Not Available —" in the dropdown — the tool works fine without them.
| Column Label | Typical Use | Required |
|---|---|---|
| Key1 | Any classification — plant, location, department, etc. | Optional |
| ItemType | Category or type of item | Optional |
| Key2 | Any secondary classification | Optional |
PoDate
cannot be parsed, Price is non-numeric or zero, or
Item Code is blank will be skipped and counted separately
in the Dashboard.
Step-by-Step Usage Guide
Upload Your PO Register
Drag and drop your Excel file onto the upload zone, or click to browse.
Supported formats: .xlsx, .xls.
Once uploaded, a sheet explorer appears — click the correct sheet tab
to preview its data.
- If your header row is not Row 1, change the Header Row setting (default: 1).
- The preview table shows the first few rows so you can confirm the right sheet is selected.
Map Mandatory & Optional Columns
The tool auto-guesses column mappings based on common naming conventions.
Verify each dropdown before running — particularly
Price, Item Code, and PoDate.
- All 8 mandatory columns must be mapped to a column from your file.
- For optional columns (Key1, ItemType, Key2) — select "— Not Available —" if your file does not have them.
- Optional filter dropdowns (Key1, ItemType) only appear in the Results panel when those columns are mapped.
Run Analysis
Click Run Analysis. The tool processes all rows and immediately shows the Exception Dashboard, Working Sheet, Audit Annexure, and Top 10 Exceptions tabs. Use the filters to drill into specific months, suppliers, or item codes.
Export
Choose from three export buttons depending on what you need for your audit file. Exports are always unfiltered (full dataset) regardless of active filters — preserving a complete audit trail.
Dashboard Metrics
The dashboard appears at the top of the Results panel and gives an at-a-glance summary of the analysis.
| Metric | Description |
|---|---|
| Total Records | Valid rows processed (excludes skipped rows) |
| Exception Records | Rows where the item was bought from multiple suppliers in the same month and this row's rate was above the month's lowest available rate |
| Total Potential Excess Cost | Sum of (Price − Lowest Rate) × Qty across all exception records — i.e., the total that could have been saved had the lowest rate been used |
| Rows Skipped | Rows excluded due to invalid date, non-numeric price, or blank Item Code |
Below the four stat cards, the tool highlights the single exception with the largest Excess Cost — showing the PO number, Item Code, Item Description, both supplier names, their respective prices, and the excess cost amount. If no exceptions are found, a green "No Exceptions Found" message appears instead.
Working Sheet
The Working Sheet preserves every valid row from the original PO Register in its original row order — no grouping or sorting is applied. Seven calculated columns are appended to the right of the original data.
| Column | Origin | Description |
|---|---|---|
| PoNo | Source | As-is from your file |
| PoDate | Source | Displayed as DD-Mon-YYYY |
| Month | Calculated | Derived from PoDate — e.g. Mar-26. Used for grouping. |
| Supplier Code | Source | As-is |
| SupplierName | Source | As-is |
| Key1 | Source / blank | Only populated if column was mapped |
| ItemType | Source / blank | Only populated if column was mapped |
| Key2 | Source / blank | Only populated if column was mapped |
| Item Code | Source | As-is |
| ItemDescription | Source | As-is |
| Price | Source | Unit rate |
| Sum of Po.Qty | Source | Quantity ordered |
| Lowest Rate | Calculated | Minimum Price among all suppliers for the same Item Code in the same Month |
| Lowest Supplier | Calculated | SupplierName corresponding to the Lowest Rate |
| Rate Diff | Calculated | Price − Lowest Rate (zero for non-exceptions) |
| Excess Cost | Calculated | Rate Diff × Sum of Po.Qty |
| Flag | Calculated |
Exception when: (a) the item appears with 2+ different suppliers in the same month, AND (b) this row's Price > Lowest Rate.Blank otherwise. |
Flag column is blank, Rate Diff is 0, and
Excess Cost is 0 for those rows.
Audit Annexure
The Audit Annexure is structured for direct inclusion in an internal audit report. Each exception group consists of two types of rows:
| Column | Description |
|---|---|
| Group No | Sequential group number — each unique Month + Item Code pair gets one Group No |
| Record Type | Lowest Rate or Exception |
| PoNo | Purchase Order number |
| Supplier | Supplier name |
| Item Code | Item / material code |
| Item Description | Item name |
| Price | Unit rate paid in this PO |
| Qty | Quantity ordered |
| Lowest Rate | The baseline minimum rate for this item in this month |
| Lowest Supplier | Supplier providing the lowest rate |
| Rate Diff | Price − Lowest Rate (0 for Lowest Rate rows) |
| Excess Cost | Rate Diff × Qty (0 for Lowest Rate rows) |
Top 10 Exceptions
The Top 10 Exceptions tab ranks exception records by
Excess Cost (descending) and shows the ten highest-impact
procurement anomalies. This view is unaffected by filters
— it always shows the top 10 across the full dataset so the highest-risk
items are never hidden.
Filters
The filter bar appears above the data tabs in the Results panel. All filters apply simultaneously (AND logic) to the Working Sheet and Audit Annexure views.
| Filter | Values | Notes |
|---|---|---|
| Month | All, or any month present in data (e.g. Mar-26) |
Always shown |
| Supplier | All, or any supplier name | Always shown |
| Item Code | All, or any Item Code | Always shown |
| Key1 | All, or any Key1 value | Only shown if Key1 was mapped |
| ItemType | All, or any ItemType value | Only shown if ItemType was mapped |
| Exception Only | Checkbox — when checked, Working Sheet shows only rows where Flag = Exception | Does not affect Top 10 tab |
Export Options
Three export buttons are available. All exports use the full, unfiltered dataset to maintain a complete audit trail — active filters do not restrict exports.
Export Working
Single-sheet workbook with all valid rows and all 17 calculated columns.
Export Annexure
Single-sheet workbook with the structured exception pairs grouped by Group No.
Export Complete Audit File
Full audit package — three sheets in one workbook, ready to attach to the audit report.
Summary Sheet (Complete Audit File only)
| Particulars | Description |
|---|---|
| Total Records Processed | Count of valid rows included in the analysis |
| Total Exceptions | Count of rows flagged as Exception |
| Total Potential Excess Cost (₹) | Sum of Excess Cost across all exceptions |
| Highest Exception Amount (₹) | Largest single Excess Cost value |
Analysis Logic (Step by Step)
Each row is checked: PoDate must be a recognisable date, Price must be a non-zero number, Item Code must not be blank. Invalid rows are skipped and counted in "Rows Skipped".
The Month column is computed from PoDate as
Mon-YY (e.g. Mar-26). This is the
primary grouping unit — two POs with different PoDate values
in the same calendar month are treated as the same period.
All rows with the same Item Code in the same Month are placed in one group. Items that appear with only one supplier in a month are never flagged — there is no comparison available.
Within each group, the row with the minimum Price is the
Lowest Rate record. Its supplier becomes the
Lowest Supplier for the entire group.
If two rows share the same minimum price (a tie), neither is flagged —
the tied rows are both treated as the Lowest Rate baseline.
A row is flagged Exception when:
① the group has more than one record (multi-supplier month), AND
② this row's Price > Lowest Rate.
Rate Diff = Price − Lowest Rate
Excess Cost = Rate Diff × Sum of Po.Qty
For each exception group, one Lowest Rate context row is written first, followed by all its paired Exception rows. Groups are numbered sequentially starting from 1. The Working Sheet retains original row order.
Auditor Disclaimer
Important — For Auditor Review
This tool identifies rate-based exceptions only. A higher rate paid to one supplier does not automatically constitute a procurement irregularity. Before drawing audit conclusions, the responsible auditor must consider all non-rate factors, including:
- Technical specifications and product differences between suppliers
- Brand, quality grade, or material composition variations
- Urgent procurement requirements (emergency orders)
- Freight, delivery, and logistics cost implications
- Approved vendor lists and vendor qualification constraints
- Management approvals and sanctioned deviations
- Contractual obligations, rate contracts, or annual agreements
The output of this tool is working paper material to assist audit analysis — not a final audit finding. All exceptions should be discussed with the relevant procurement and management teams before formal reporting.
FAQ
Rate Diff and Excess Cost are zero for both.