To make a vendor quotation comparison template in Excel, list suppliers in columns B through D and put quoted unit price, quantity, freight, setup, discount, and tax in rows 6 through 11. With those values in place, the total evaluated cost in B12 can be calculated as =B6*B7+B8+B9-B10+B11; copy the formula across for the other suppliers.
What a quotation comparison template should show
A quotation comparison, sometimes called a bid tabulation, puts offers for the same purchase side by side. The purpose is not simply to identify the smallest number. It is to make differences in price, scope, delivery, payment, and other relevant conditions visible so that a buyer can compare offers on a consistent basis.
A useful workbook separates facts taken from the quotation from calculations and evaluation judgments. Keep the original quote or a link to it, record who supplied the information, and avoid silently filling gaps with assumptions. If one supplier excludes freight or quotes a different quantity, flag that difference before treating the totals as comparable.
- Commercial details: vendor name, quotation date, quote reference, currency, validity period, and contact.
- Price details: unit price, quantity, freight, setup or other fees, discounts, and applicable tax.
- Delivery and terms: lead time, delivery location, payment terms, warranty, and stated exclusions.
- Evaluation: compliance with requirements, risks or exceptions, and any approved scoring criteria.
Set up the workbook layout
For a small purchase, one worksheet is often sufficient. Put a descriptive title in A1, the criterion names down column A, and each vendor in a separate column. For example, use B for Supplier A, C for Supplier B, and D for Supplier C. Put the supplier name in row 2, quotation date in row 3, quotation reference in row 4, and currency in row 5. These identifying fields help prevent a total from becoming detached from the offer it represents.
Use rows 6 through 11 for the price inputs: unit price, required quantity, freight, setup or one-time fees, discount, and tax amount. Put the calculated total in row 12. Keep delivery days, payment terms, warranty, specification compliance, and exceptions below the price section. The quantity should represent the same requirement for every vendor; if offers cover different quantities or package sizes, first calculate a comparable scope or explain the difference.
For a larger sourcing exercise, use a second sheet for qualitative scoring and a third sheet or a linked document for detailed notes. A wide comparison matrix can become hard to read when it contains dozens of requirements. Freeze the header row using View > Freeze Panes > Freeze Top Row in Excel for Microsoft 365 on Windows. The Freeze Panes command is also available on Excel for Mac, though its ribbon arrangement can vary slightly by version.
Calculate comparable total cost
With Supplier A’s unit price in B6, quantity in B7, freight in B8, setup fees in B9, discount in B10, and tax amount in B11, enter this in B12 and copy it across to C12 and D12:
=B6*B7+B8+B9-B10+B11
For an illustrative example, suppose Supplier A quotes 48 per unit for 200 units, freight of 350, setup fees of 150, a discount of 300, and no tax in the comparison. The formula returns 9,800: 48 multiplied by 200 is 9,600; adding 350 and 150 gives 10,100; subtracting 300 gives 9,800. Use the currency stated in the quote rather than assuming every offer is in the same currency.
The formula assumes the discount is entered as a positive amount to subtract and that tax is entered as a calculated amount, not a percentage. If the quote gives a tax rate, calculate the tax according to the applicable rules and quote details before entering it; taxable bases and treatment can differ. Do not add tax twice if a supplier’s price already includes it. Where tax is not relevant or is included in the quoted price, document that treatment consistently. A cell containing a text note such as included will not be treated as a numeric tax amount, so keep explanatory notes in a separate row or comment.
With Supplier A, B, and C totals in B12:D12, the lowest displayed total can be identified with =MIN(B12:D12). For the example above, if B12 is 9,800 and the other totals are 10,000 and 10,450, the result is 9,800. This is a useful check, not an award decision: it does not account for exclusions, quality, delivery, or whether the suppliers priced the same scope.
Compare scope, terms, and delivery fairly
Before ranking prices, check whether each offer covers the same specification and commercial scope. A low quote may omit installation, training, packaging, maintenance, or a required accessory. Another supplier may include those items in its price. Record what is included and excluded, and request clarification when the difference could change the comparison.
Capture the exact delivery promise rather than reducing it to a vague label such as fast. Record the quoted lead time and, where material, whether it begins after order placement, approval of drawings, or another event. Compare payment terms in the same way: an offer due in advance is not identical to one with payment after delivery. These details may affect suitability even when the workbook does not assign them a monetary value.
Also note quotation expiry dates. A price may be valid only for a stated period, and availability or terms can change after that date. If a quote is revised, retain the revision date and reference so the workbook points to the current offer. Do not replace an old input without leaving a trace of which version was evaluated.
Add an optional weighted evaluation
For purchases where non-price factors matter, make a scoring table with criteria in A23:A26, their weights in B23:B26, and Supplier A’s scores in C23:C26. Put the other suppliers’ scores in columns D and E. Use agreed criteria such as delivery capability, technical compliance, service, or warranty, and use a consistent scale, for example 1 for poor and 5 for strong. Define what the scale means before scoring rather than deciding after seeing the results.
With weights in B23:B26 that add up to a positive total and Supplier A’s scores in C23:C26, enter this in C27:
=SUMPRODUCT($B$23:$B$26,C23:C26)/SUM($B$23:$B$26)
If the weights are 40%, 25%, 20%, and 15%, and the scores are 4, 5, 3, and 4, the formula returns 4.05 out of 5. The arithmetic is 0.40 times 4, plus 0.25 times 5, plus 0.20 times 3, plus 0.15 times 4. Copy the formula across for the other suppliers, changing the score range to their column while keeping the weight range fixed. This is a decision aid, not an objective measure of quality; weights and scores should be justified and applied consistently. Do not combine a weighted score with total cost unless the scoring method and decision rule are explicitly explained.
Make the sheet readable and auditable
Use a clear number format for costs and keep the same currency and decimal precision across suppliers. In Excel for Microsoft 365 on Windows, select the relevant cells and use the Number group on the Home tab to choose an appropriate number format. Use percentage formatting for weights, not for currency inputs. Labels such as USD or EUR should be visible in the heading or currency row, especially if offers use different currencies.
Distinguish editable inputs from formulas with consistent formatting, such as one fill color for input cells and another for calculated cells. Add a notes column or a notes sheet for qualifications and assumptions. You can use Home > Conditional Formatting to highlight a lowest price or a missing input, but color should support—not replace—clear labels and written explanations. Make sure the comparison remains understandable when printed or viewed without color.
For an auditable workbook, identify the person who entered or reviewed the figures and record the evaluation date. Keep the source quotation files with the workbook or provide stable file links. Protecting formula cells can reduce accidental edits, but it does not validate the underlying figures. Review copied formulas and totals whenever rows or columns are inserted, and have a second person check material calculations where the purchase warrants it.
Common mistakes and how to prevent them
- Comparing unlike quantities: confirm that every supplier is priced for the same units, term, or service scope.
- Mixing currencies: do not compare raw totals in different currencies. If conversion is needed, state the exchange rate source and date and preserve the original quote amounts.
- Forgetting one-time costs: include setup, delivery, tooling, or other relevant fees, and avoid counting an item twice.
- Treating blank as zero: a blank freight or tax cell may mean unknown, not free. Mark missing information and obtain clarification rather than letting a formula imply certainty.
- Choosing only by lowest total: review compliance, exclusions, delivery, reliability evidence, and terms before making a decision.
- Changing criteria mid-review: define requirements and weights before evaluating offers, and document any approved change.
A particularly common misconception is that a spreadsheet can determine the best supplier automatically. It can calculate totals and apply stated weights, but it cannot decide whether a specification exception is acceptable or whether a promised lead time is credible. Those are evaluation judgments that should be recorded, not hidden inside an unexplained score.
When to use another approach
A basic Excel workbook works well for a limited number of suppliers, a straightforward scope, and a review process where people can manage files and approvals directly. A structured procurement system may be more suitable when an organization needs controlled approval workflows, supplier records, audit trails, many concurrent bids, or integration with purchasing and finance systems. An existing organizational template may also be required for policy or reporting reasons.
For a very small purchase, a concise comparison table in a document may be enough, but it is less convenient for formulas and repeated updates. For a complex tender, a workbook can still support analysis, while the formal solicitation, evaluation criteria, and decision record should live in the organization’s established process. Choose the simplest method that preserves clear source information, consistent comparisons, and the approvals the purchase requires.
Frequently Asked Questions
Can I download a vendor quotation comparison template in Excel?
Excel and organizations may offer ready-made workbooks, but their fields and formulas vary. Check that any template you use shows scope, currency, relevant fees, and terms, and verify its formulas against a small example before relying on it. The layout and formulas above can also be built in a blank workbook.
How many vendor quotations should I compare?
There is no universal number that applies to every purchase. Follow your organization’s purchasing rules, the requirements of the transaction, and any applicable process. The spreadsheet can compare two or more suppliers, but adding vendors does not by itself make the process fair if the scope or evaluation method differs.
Should tax be included in the comparison?
Use a consistent tax basis that matches the purchase and the quotations. Record whether amounts are tax-inclusive or tax-exclusive, and do not add tax again when it is already included. Tax treatment can depend on the transaction, so confirm the correct treatment with the relevant finance or tax specialist when uncertain.
Is the lowest quotation always the best choice?
No. A low total may exclude required work, use a different specification, or come with unsuitable delivery or payment terms. Compare like-for-like costs first, then assess relevant non-price requirements using criteria established in advance.
Can I use the same template for services?
Yes, but adapt the price inputs to the service model. For example, compare the same hours, service period, deliverables, and assumptions, and include recurring charges or one-time implementation fees where relevant. Record service levels and exclusions separately so a lower rate is not mistaken for an equivalent offer.
Related
- Resize Columns in Excel: Keyboard Shortcuts and Tips
- Update Number Format in Excel: How to Change Formats
- US Number Format in Excel: Dates and Numbers
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Identify Circular References in Excel: A Step-by-Step GuideTo identify a circular reference in Excel for Microsoft 365 on Windows, open Formulas > Error Checking >…
- Flip Paste in Excel: Turn Rows Into Columns EasilyTo flip-paste a range in Excel for Microsoft 365 on Windows, select and copy the cells, click a…
- Add a Watermark in Word on One Page: Step-by-Step GuideTo add a watermark to just one page in Word, isolate that page in its own section, unlink…
- How to Set Your Out of Office in New Outlook: Step by StepIn New Outlook, open Settings (the gear icon), go to Accounts > Automatic replies, turn on automatic replies,…
- Clear Cache in Microsoft Outlook: Windows, Mac & MobileTo clear cache in the Microsoft Outlook app, use the method for your device: repair or reset new…
- Share an Excel Document on Google Docs: Step by StepTo share an Excel document through Google’s tools, upload the .xlsx file to Google Drive, select it, choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.