How to Compare Supplier Quotes in Excel

Use one row per quoted line, not one column per supplier

The common layout of items down the side and suppliers across the top breaks as soon as suppliers describe items differently or add lines the others don’t have. A long table is more robust: one row per line per supplier, with a “Match group” column that you fill in to say which lines correspond. Pivot tables can then build the side-by-side view for you.

Suggested columns

• A Supplier, B Description (copied exactly), C Match group (your label, such as “20mm aggregate”)
• D Qty, E Unit, F Rate, G Amount as quoted
• H GST: ex, inc or free, taken from the quote, not guessed
• I Status: priced, included, excluded, TBA, not mentioned
• J Factor to base unit, K Qty in base unit, L Amount ex GST, M Rate per base unit
• N Source: page and line, or sheet and row

Formulas

K2  =D2*J2                                    qty in base unit (kg→t: J = 0.001)
L2  =IF(I2<>"priced","",IF(H2="inc",ROUND(G2/1.1,2),G2))   amount ex GST
M2  =IF(L2="","",ROUND(L2/K2,4))             rate per base unit
Unknown lines per supplier  =COUNTIFS(A:A,"Supplier B",I:I,"TBA")+COUNTIFS(A:A,"Supplier B",I:I,"not mentioned")

Worked example

Supplier A quotes 10 t of 20mm aggregate at $120.00/t ex GST plus $300.00 freight. Supplier B quotes 10,000 kg at $0.125/kg inc GST, freight TBA.

B qty:    10,000 kg × 0.001 = 10 t
B amount: $1,250.00 inc GST ÷ 1.1 = $1,136.36 ex GST
B rate:   $1,136.36 ÷ 10 t = $113.64/t ex GST   (A: $120.00/t)
A total:  $1,200.00 + $300.00 = $1,500.00 ex GST (complete)
B total:  $1,136.36 + freight unknown → INCOMPLETE
Break-even freight for B = $1,500.00 − $1,136.36 = $363.64

A simple SUM would show Supplier B at $1,136.36 and declare it $363.64 cheaper. That is the classic spreadsheet failure: the blank freight cell counted as zero.

Checks spreadsheets commonly skip

• Re-check the supplier’s arithmetic. Add a column =ROUND(D2*F2,2)-G2 and investigate anything that isn’t zero.
• Reconcile to the quote total. Your sum of a supplier’s lines should equal their stated subtotal; if it doesn’t, you have missed or double-keyed a line.
• Check GST. Stated GST should be 10% of the taxable subtotal; mixed quotes with GST-free lines need separate handling.
• Lock assumptions. Put densities, days per week or pack sizes in named cells and reference them, so every assumption is visible.
• Keep the source reference. Without it, nobody can audit the sheet later.

When to stop re-keying

The spreadsheet above works, but every row is typed by hand from a PDF. QuantDoc reads PDF (with a text layer), Excel and CSV quotes, fills in the equivalent of every column above with source references, runs the arithmetic and GST checks, and exports the result to Excel so you can keep working in a spreadsheet. The example below is its output for the same two quotes.