1. Introduction
Unit price is the natural starting point for any comparison. However, the final cost of a purchased item can differ substantially from the quoted price — particularly when sourcing internationally. The Excel template described in this guide is designed to make that full-cost comparison fast, transparent, and repeatable.
2. Hidden Costs in International Procurement
The gap between a supplier's quoted price and the actual landed cost of goods can be significant. The main cost drivers to consider are:
2.1 Freight Costs
Shipping mode has a major impact on unit economics. Air freight or express couriers (e.g. FedEx, DHL) can cost €10–15 per kg, whereas sea freight typically runs €0.30–0.80 per kg. The template lets you enter a per-kg rate and applies it automatically based on each item's weight.
2.2 Import Duties and Tariffs
Customs duties are calculated as a percentage of the declared customs value and vary by product category (HS code) and country of origin. In the current geopolitical environment, tariff rates are subject to rapid change and should be verified before each sourcing decision. The template includes a dedicated duty-rate cell that feeds into all overseas supplier calculations.
2.3 Delivery Lead Time
Lead time has two components: production lead time (time to manufacture) and transit time (time to ship). Overseas suppliers may offer lower unit prices but require 8–14 weeks of total lead time, which ties up working capital and limits responsiveness to demand changes.
2.4 Customs Inspection Costs
Random or targeted customs inspections can add unpredictable delays and handling fees. These are difficult to estimate precisely but should be factored into contingency budgets for overseas sourcing.
2.5 Exchange Rate Risk
When a supplier invoices in a foreign currency (e.g. USD or CNY), the effective cost in your functional currency will fluctuate with exchange rates. The template includes an exchange-rate cell for USD-denominated suppliers. Since rates move continuously, it is good practice to lock in a budgeted rate at the start of a project and review it periodically.
2.6 Incoterms
Incoterms (International Commercial Terms, published by the ICC) define the precise point at which risk and cost responsibility transfers from seller to buyer. Common terms include EXW (Ex Works — buyer bears all costs from the factory gate), DDP (Delivered Duty Paid — seller bears all costs and duties), and FCA, FOB, CIF, DAP. Selecting the correct Incoterm is essential before interpreting a supplier's price: two suppliers quoting the same unit price under EXW and DDP respectively are not comparable without further calculation.
3. Template Overview
The Quote Comparison File is a single-sheet Excel workbook (with a supporting DATA tab for drop-down values). It is structured around a project: you define a list of parts or assemblies, enter quotes from up to eight suppliers, and the file automatically calculates the landed cost for each supplier and identifies the most economical option for each line item. The file covers three supplier categories: • In-house production — your own manufacturing cost, used as the baseline • Local / same-area suppliers — no freight cost applied • Overseas suppliers — freight, duties, and exchange rate applied automatically Three global parameters (cells BA1:BA3) drive all overseas cost calculations:
Column(s) | Label | Description |
BA1 | Freight rate (€ or $ per kg) | Shipping cost per kilogram. Adjust for the chosen transport mode: e.g. €0.50/kg for sea freight, €15/kg for air freight. |
BA2 | Import duty (%) | Duty rate applied to the customs value. Verify the applicable rate for the relevant HS code and country of origin. |
BA3 | Exchange rate | Conversion factor from the supplier's currency (e.g. USD) to your functional currency (e.g. EUR). Update before each analysis. |
A note on reading direction: the file reads prices from left to right. If two suppliers have an identical landed cost, the one entered further to the left is treated as the preferred choice.
4. How to Use the Template
Step 1 — Define the Project
Fill in the descriptive columns for every part or assembly in your project:
Column(s) | Label | Description |
B | Item | Part name or part number |
C | Qty | Required quantity for the project |
D | Description | Free-text description of the item |
E | Weight (per pc) | Unit weight in kg (or lb — see note below). Required for freight cost calculation |
Weight is essential: without it, the template cannot calculate shipping costs for overseas suppliers.
Step 2 — Enter the In-House Production Cost
Column(s) | Label | Description |
F | In-house label | Enter the name of the machine, process, or your company name. This label appears in the analysis output. |
G | Unit cost | Estimated unit production cost (internal). |
H | Total cost | Calculated automatically: G × C (quantity). |
I | Unit price (with margin) | Displays G multiplied by the markup set in cell I3. A negative markup is allowed here (useful for internal cost-plus-minus scenarios). |
J | Total price (with margin) | Calculated automatically: I × C. |
Step 3 — Enter Local Supplier Quotes
Columns K–X cover suppliers in the same geographic area. No freight cost is applied. Each supplier block follows the same pattern:
Column(s) | Label | Description |
K (or P, U) | Supplier name | Enter the supplier's name; it propagates automatically to the analysis section. |
L (or Q, V) | Unit purchase price (€) | The quoted unit price. |
M (or R, W) | Total purchase price (€) | Calculated: unit price × quantity. |
N (or S, X) | Unit price with markup | Unit price × markup rate (set per supplier). |
O (or T, ...) | Total price with markup | Total with markup × quantity. |
Three local supplier blocks are available: columns K–O, P–T, and U–X. To add a supplier, enter its name in the header row of the relevant block.
Step 4 — Enter Overseas Supplier Quotes
Columns Y–BE cover suppliers in a different geographic area. Freight, duty, and (where applicable) exchange rate are added automatically. The block is split into two sub-groups: • Columns Y–AJ: up to two overseas suppliers quoting in the same currency as your company (e.g. EUR). Exchange rate conversion is not applied. • Columns AK–BE: up to two overseas suppliers quoting in a foreign currency (e.g. USD). The exchange rate in BA3 is applied automatically.
Column(s) | Label | Description |
Y (or AK) | Supplier name | Enter supplier name — propagates to analysis. |
Z (or AL) | Quoted unit price | The supplier's quoted unit price in their currency. |
AA (or AM) | Landed cost per pc | Quoted price + freight (weight × BA1) + duty (× BA2) + exchange rate conversion if applicable. Calculated automatically. |
AB (or AN) | Total landed cost | Landed cost per pc × quantity. |
AC (or AO) | Total weight | Weight per pc × quantity. Used to estimate total freight cost. |
AD (or AP) | Unit price with markup | Landed cost × markup rate. |
AE (or AQ) | Total price with markup | Unit price with markup × quantity. |
Important: the file currently applies a single duty rate and a single freight rate to all overseas items. If different items attract different duty rates, you must override the relevant cells manually.
Step 5 — Read the Analysis
Columns BF–CM aggregate results across all suppliers and produce the final analysis:
Column(s) | Label | Description |
BF | Best supplier (purchase) | Name of the supplier with the lowest landed cost for that item. |
BG | Best unit price | Lowest landed unit cost (including freight and duty where applicable). |
BH | Best total price | Best unit price × quantity. |
BI | Transport weight | Weight of the item if the best supplier is an overseas one. Totalled at the bottom of the column. |
BK–BM | 2nd best supplier | Name, price, and % difference of the second-cheapest option. Useful for negotiation leverage. |
BO–BQ | Selected suppliers | Paste or type the supplier names you actually intend to use. The file cross-references these to display purchase prices and markup for the chosen selection. |
Step 6 — Read the Sales Analysis
Column(s) | Label | Description |
BT–BU | Best selling price | The lowest selling price achievable across all suppliers (using each supplier's assigned markup). Note: a supplier with a lower purchase price but a higher markup may not appear here. |
BV–BW | Markup on best purchase | The markup applied to the supplier with the lowest purchase price. |
BY | Locked selling price | Enter the actual price quoted to the customer here. This cell is intentionally decoupled from the formula chain so that cost increases during production do not automatically change the customer-facing price — allowing you to monitor margin erosion in real time. |
CA–CC | Margin analysis | Markup % and margin % for each line item and for the project total. |
Step 7 — Commercial Items Section (Rows 115–160)
This section follows the same logic as the main table but is designed for ancillary or bought-out items (e.g. fasteners, bought components). It supports comparison between two suppliers at a time.
5. Adding and Modifying Data
The template is designed to be extended: • To add more items: insert new rows between the existing data rows (not below row 110) to preserve the formula references at the bottom of each column. • To change freight mode: update cell BA1. Switch from sea freight (e.g. €0.50/kg) to air freight (e.g. €15/kg) and all overseas totals recalculate instantly. • To change duty rate: update cell BA2. If individual items carry different duty rates, override the relevant formula cells directly. • To change exchange rate: update cell BA3 with the current rate. • To add or change markup: each supplier column has its own markup rate in row 1. Set a different rate per supplier to model different pricing strategies simultaneously.
6. Practical Tips
• Always verify the Incoterm before entering a quoted price. An EXW price requires adding all freight and duty costs; a DDP price already includes them. • For USD-denominated suppliers, use a conservative (slightly unfavourable) exchange rate as a buffer against currency movement. • Use the "2nd best supplier" columns (BK–BM) as a negotiation tool: show your preferred supplier how much cheaper the alternative is. • Lock the customer selling price in column BY as soon as the quote is issued to the customer. This preserves margin visibility even if costs change during execution. • The template compares prices left to right. If you want the file to prefer a specific supplier when prices are tied, place that supplier's columns further to the left.