Procurement·Nov 22, 2026·1 min read

Bid Comparison Spreadsheet: How to Build One That Holds Up

A bid comparison spreadsheet should explain what each supplier offered, how you made offers comparable, and why the recommended bidder deserves the award.

Procurement

You've entered the quotes, lined up the suppliers and highlighted the lowest total. Then someone notices that one quote excludes installation, another uses different payment terms, and the preferred supplier hasn't answered a mandatory requirement. Your comparison looks finished, but you can't recommend an award yet.

A bid comparison spreadsheet should let you explain what each supplier offered, how you made the offers comparable and why the recommended bidder deserves the award. Treat it as a controlled decision record, not just a calculator, and you'll catch problems before they become contract arguments.

Table of Contents

What a Bid Comparison Spreadsheet Must Show

Start with five linked tabs that separate supplier submissions from your adjustments and judgements. Keep the main comparison readable, but give every result a traceable path back to its inputs.

Workbook tabs and what each must hold

Tab

Purpose

Required fields

Requirements

Lists every spec line as a testable item

Requirement ID, acceptance test, mandatory flag, weight

Responses

Stores supplier answers as received

Supplier, quote date, original value, currency, tax basis, term, quantity

Costs

Converts quotes to comparable totals

Normalized price, delivery, warranty, recurring fees, total cost

Scoring

Applies raw scores and weights

Raw score, anchor used, weight, weighted points, total

Audit Log

Records every change and judgement

Evaluator, timestamp, source page, rationale, change reason

Use the requirement ID to connect the tabs. A reviewer should be able to follow a requirement into the supplier's answer, inspect any adjustment and see the resulting score without searching through email.

Separate evidence from interpretation

In Responses, preserve what the supplier submitted. In Costs and Scoring, show what your team did with that information. Don't replace a quoted value with a corrected value, even when the correction seems obvious.

That separation matters because small workbooks aren't automatically reliable. The review of spreadsheet-error studies reports that 51% of spreadsheets in rigorous laboratory studies contained at least one error, although most models contained only 25–50 cells.

A neat layout won't prevent a formula from referencing the wrong range. Keep formulas visible for review, protect approved calculation cells and use consistent column names rather than relying on colour alone.

The same discipline applies beyond equipment purchases. ABS Insurance Brokers Pty Ltd's guide on how to compare insurance brokers is a useful companion when you're designing a comparison for a service purchase.

You don't have to replace your existing procurement systems to improve this work. At Procright, we don't replace SAP, Ariba or a supplier portal; customers can keep them while we handle the quote-reading and comparison work those systems don't do.

Rebuild test: Could someone who wasn't in the evaluation meeting reconstruct the ranking from these tabs alone? If not, add the missing field before scoring starts.

Normalizing Quotes into Comparable Totals

A lower quoted price can become the more expensive offer once you account for commercial terms. In a worked example in Julian R. Sterling's total-cost comparison guide, a bid quoted at 50.50 became 52.87 after adjustments for payment terms, warranty, take-or-pay exposure and freight, reversing its apparent advantage.

The useful lesson isn't the adjustment amount. It's that each adjustment needs its own row, calculation and supporting assumption.

Cost layers to normalize before comparing totals

Cost layer

What to capture

How it enters the total

Freight and delivery

Delivery terms, freight quote, lead time

Added to landed cost per unit

Duties and taxes

Duty rate, tax basis, recoverable tax

Added or excluded according to the tax basis

Installation and setup

One-time fees, tooling, commissioning

Added once to first-year cost

Warranty

Length in months, claim rules, exclusions

Expected cost of failures over the term

Payment terms

Days to payment, financing rate

Price adjusted to the cost of money over the term

Recurring fees

Licences, maintenance, minimum volumes

Multiplied by the contract term

For recurring fees, match the billing frequency to the evaluation period. For setup charges, retain the full contract view as well as the first-year view so a one-time fee doesn't get multiplied accidentally.

Keep different totals visibly different

Use distinct column labels rather than calling everything “price”:

  • Quoted price is the supplier's submitted amount, before buyer adjustments.

  • Landed cost adds the relevant delivery, duty and handling costs.

  • Expected cost adds supported operating and risk costs over the evaluation period.

  • Non-monetary score captures important differences you can't credibly price.

Show an equivalence price beside the quoted price, with a reconciliation between them. Finance should be able to identify which adjustments represent cash expenditure and which represent estimated exposure.

For payment terms, use Finance's approved financing rate and the actual difference from your reference terms. For warranty, estimate uncovered failures only where you have a defensible failure assumption, repair cost and coverage interpretation. Don't invent a failure rate just to complete the worksheet.

Our guide to breaking down supplier prices can help you structure the underlying commercial lines before adding evaluation adjustments.

Leave uncertainty visible

Model only cost layers that differ materially. If every bidder has the same delivery basis, an elaborate freight model adds maintenance work without helping the decision.

Where an adjustment is uncertain, retain low and high estimates. Lead-time risk may deserve a scenario rather than a precise monetary penalty, especially when nobody can support the assumed downtime cost.

If a supplier leaves a field blank, enter not stated, not zero. Zero means you know there's no charge; not stated means you still need an answer.

How to Build the Workbook Step by Step

Build the workbook in Excel or Google Sheets before entering evaluation judgements. Use structured tables so added rows inherit formulas, and keep editable inputs visually distinct from calculated outputs.

This process overview is useful when agreeing who owns each part of the workbook.

A five-step infographic showing the process for creating a bid comparison workbook for professional evaluations.

Build the inputs before the summary

  1. Capture the approved requirements. Copy every specification line into Requirements, assign an ID and write a measurable acceptance test with a mandatory or rated flag. Don't combine unrelated obligations into a single row, because a supplier could satisfy part of it while appearing fully compliant.

  2. Set the evaluation assumptions. Store the scoring scale, anchors and approved weights in a protected assumptions block before opening bids. Check the weight total and percentage formatting, because entering a whole number where Excel expects a decimal can distort the calculation.

  3. Load the original responses. Paste each supplier answer exactly as received, with the document name, revision and source page alongside it. Don't shorten a qualified answer to yes just because the supplier's opening sentence sounds positive.

  4. Build the comparable-cost calculation. Reference the original inputs and convert them to the approved currency, tax basis, quantity and evaluation term. Keep conversion assumptions in named cells rather than burying them inside formulas, and check that a unit rate hasn't been treated as a complete contract price.

Connect eligibility, scoring and ranking

  1. Create the scoring calculation. Give each criterion a raw-score cell, weight reference, weighted-points formula and evidence reference, then sum the weighted points by bidder. Copy formulas through structured columns rather than dragging them selectively, which can leave a supplier outside the total.

  2. Apply the mandatory gate. Calculate an eligibility status separately from the rated total, and route failed or unresolved mandatory responses for the approved decision. Don't let an attractive numerical score conceal an ineligible bid.

  3. Create the ranked summary. Rank eligible bidders by their weighted totals and display eliminated bidders separately with their reasons. For a hypothetical eligible-bidder range in column H, use =RANK.EQ(H2,$H$2:$H$9,0), then sort the summary by rank; the formula assigns ranks but doesn't physically reorder rows.

Don't create independent calculation logic on every supplier sheet. If you need bidder-specific views, generate them from the same underlying tables so a formula correction applies consistently.

Use the accompanying video as a discussion aid when reviewing the workbook-building process with colleagues.

Before loading final scores, change a test input and confirm that its weighted points and total change correctly. Restore the input, add a temporary supplier row and check that the summary includes it, then remove the test entry.

Scoring Mandatory Gates and Weighted Criteria

Mandatory compliance and rated merit answer different questions. The gate asks whether a bid can proceed. Rated scoring asks how well an eligible bid meets the evaluation criteria.

Record each mandatory response as yes, no or partial, supported by evidence. A partial response isn't automatically acceptable: the evaluator needs to identify what's missing and follow the event's clarification or escalation rules.

The World Bank's rated-criteria guidance supports multiplying criterion scores by agreed weights and totaling the results; set weights totaling 100% and document the evidence behind the scores.

Make the arithmetic reproducible

The following hypothetical worked example uses a common raw-score scale, with each point figure calculated as raw score multiplied by weight.

Weighted scores for two example bidders

Criterion

Weight

Bidder A points

Bidder B points

Technical fit

40%

32

24

Delivery time

20%

12

16

Warranty

15%

15

9

Total cost

25%

25

20

Total

100%

84

69

In this example, technical raw scores of 80 and 60 produce 32 and 24 points. Delivery scores of 60 and 80 produce 12 and 16 points, so Bidder B's delivery advantage is visible rather than buried in the total.

Warranty scores of 100 and 60 produce 15 and 9 points. Cost scores of 100 and 80 produce 25 and 20 points; Bidder A wins the example overall, but not on every criterion.

For relative price scoring, a hypothetical compliant-total range could use =100*MIN($C$2:$C$4)/C2, followed by multiplication by the price weight. Keep eliminated bids outside that range, and don't let missing totals evaluate as zero.

Define what earns a score

Write anchors that describe evidence, not impressions. For delivery, distinguish a contractual commitment supported by an implementation schedule from an indicative lead time with unresolved dependencies.

TekRecruiter's weighted scoring framework for vendors is a useful companion for structuring the criteria discussion. Our weighted scorecard guide also helps translate that structure into an evaluation record.

  • Validate inputs. Restrict raw-score cells to the approved values and reject entries outside the scale.

  • Protect calculations. Lock formula cells and maintain a separate assumptions sheet.

  • Moderate disagreements. Have evaluators score independently, flag outliers against a predetermined panel tolerance and record the reason for any revision.

Show the multiplication and evidence beside the total. A reviewer shouldn't have to trust the evaluator's memory to reproduce the award.

Stress-Testing the Ranking Before You Trust It

A weighted total isn't an objective fact just because Excel calculated it. Evaluators still choose the criteria, scales, weights and price formula.

The United Nations guidance on evaluation methods identifies linked factors, subjective ratings and weights as weaknesses of weighted scoring, including the risk of double counting. The right response is to test the decision model, not abandon structured evaluation.

A review should challenge the assumptions behind the ranking, not just check the arithmetic.

A person using a red pen to correct rankings on a bid comparison table spreadsheet.

Run alternative scenarios

  • Change plausible weights. In a hypothetical scenario, transfer weight from price to technical fit while preserving the overall weight total. Use changes that reflect a credible business priority, not changes designed to make a preferred supplier win.

  • Compare price functions. Test the relative-price formula against an approved absolute-value or budget-referenced method. Record whether the lowest bidder's advantage comes from a meaningful cost difference or from the shape of the formula.

  • Remove each criterion in turn. Recalculate the remaining weights proportionally and inspect the winner. If a weakly evidenced criterion determines the award, resolve that evidence gap before approval.

  • Check overlapping criteria. Compare the evidence used for technical quality, performance and reliability. If evaluators reward the same characteristic repeatedly, merge the criteria or revise their boundaries.

Use a scenario-results table with columns for the changed assumption, resulting winner and explanation. Add conditional formatting to highlight a rank reversal, but keep the underlying values visible rather than showing only a warning colour.

Interpret the size of the gap

Coarse score bands can put materially different bids into the same category. Conversely, a tiny total-score difference can look decisive even when evaluators can't reliably distinguish the offers.

Use total cost of ownership when lifecycle differences matter, and carry uncertain operating assumptions through the scenarios. Don't revise the published evaluation method after seeing bids just because a sensitivity test produces an uncomfortable result; use the test to understand and explain the recommendation within the applicable process.

If the winner changes under reasonable assumptions, you have a close call to explain, not an unqualified result to announce.

Keeping an Audit Trail When Bids Change

Once scoring starts, criteria and weights are locked. Any authorized change needs a logged date, reason and approver, followed by a controlled recalculation rather than a silent edit.

Scope changes deserve the same discipline as formula changes. The iBeam guide to bid tabulation highlights issues such as outdated specifications, missing addenda, exclusions and pricing anomalies that can undermine an apparently fair comparison.

Record what changed and what it affected

Keep these fields close to the relevant response, not in an unconnected meeting note:

  • Receipt timestamp. Record when the bid or clarification arrived and which submission it supersedes.

  • Source reference. Identify the document, revision and page supporting the value.

  • Clarification status. Use open, answered or closed, with a named owner and resolution.

  • Evidence type. Separate the supplier's claim from independently verified evidence.

  • Elimination reason. State the failed requirement and decision in plain language, including who authorized it.

When quotes arrive through email, PDF and portals, preserve each original file. Map the contents into common fields, then normalize units, currencies and delivery bases in the calculation layer without overwriting the original response.

That distinction is central to why traceability matters in procurement. A correct current value isn't enough if nobody can explain why it differs from the submitted quote.

Control the revision, not just the filename

Give each released workbook an identifiable version and keep a change log inside it. A filename such as “final revised” doesn't tell an approver whether the supplier changed a warranty exclusion or an evaluator corrected a formula.

Release discipline: Keep the original bid files in a read-only folder and rerun the complete ranking after an addendum, not just the visibly affected subtotal.

Before releasing a revision, check which bidders received the changed requirement and whether their responses address the same baseline. If clarification is still open, make that status visible to the approver rather than presenting a provisional comparison as settled.

Confirm the Ranking Before You Award

Recommend the award only when an independent reviewer can reconstruct the decision without relying on the evaluation team's memory. A polished summary is useful, but it isn't a substitute for a complete award file.

Use the final review as a go or no-go check, not a chance to tidy unsupported conclusions.

A professional man and woman collaborating over a spreadsheet document at a bright, modern office desk.

Check the decision record

  • Mandatory gates are resolved. Every mandatory line has a status, evidence and a documented disposition for exceptions.

  • Weights are approved. The protected weighting structure matches the authorized evaluation method and reconciles to its required total.

  • Costs reconcile. Each normalized amount can be traced through its adjustments to the original supplier response.

  • Sensitivity is explained. The recommendation either survives reasonable scenarios or identifies the assumption that makes the award a close call.

  • Changes are accounted for. Every revision since scoring began has an owner, reason and approval where required.

If a check fails, fix the source row or calculation that caused the problem. Don't type a replacement value over the final total to make the summary look right.

Freeze the approved version

Record the final approver and approval date. Keep the signed-off workbook, original bids, annexes and resolved clarifications together in a read-only award folder, with the recommendation identifying the exact workbook version.

The first event doesn't require a new document set: we can work from the specification, quotes and annexes you already have. The practical starting point is a real purchase with enough detail to test whether the evidence supports the comparison.

A defensible bid comparison spreadsheet doesn't remove procurement judgement. It makes that judgement inspectable, so Finance, Operations and compliance reviewers can understand both the recommendation and its limits.

Procright's free trial offers a full procurement, where we write the specification, scan the market and provide a side-by-side comparison, or a gap analysis that flags missing items, single-supplier lines and skipped items in your specification. Visit Procright to test the approach using your existing spec, quotes and annexes, and build a comparison you can explain before you award.

Try it on a real buy

Bring one category. Watch where the flags land.

Book 20 minutes
Book 20 minutes →