Supplier terms comparison
One row per supplier and SKU: price, rate, delivery, MOQ, lead time, prepayment, credit, defects and the cost of money. The output is the unit cost with financing and defects.
What the template is for
Choosing a supplier by total cost, not by list price.
- What one row is
- one row = supplier × SKU
Columns
| Column | Code | Type | Required | Validation rule |
|---|---|---|---|---|
| Supplier code | supplier | Text | yes | a value from the supplier reference |
| Style SKU | article | Text | yes | a value from the product reference |
| Delivery terms (Incoterms) | incoterm | List value | yes | EXW, FOB, CIF, DAP or DDP |
| Price per unit (in currency) | price | Number | yes | a number not below 0 |
| Currency | cur | List value | yes | an ISO 4217 currency code |
| Rate to the rouble | fx | Number | yes | a number not below 0 |
| Delivery to the warehouse per unit, RUB | deliver | Number | yes | a number not below 0 |
| MOQ, units | moq | Integer | yes | an integer not below 1 |
| Production and delivery lead time, days | lead | Integer | yes | an integer not below 0 |
| Prepayment, % | prepay | Number | yes | from 0 to 100 |
| Credit days after delivery | credit | Integer | yes | an integer not below 0 |
| Expected defects, % | defect | Number | yes | from 0 to 100 |
| Cost of money, % a year | rate | Number | yes | from 0 to 100 |
| Price in roubles with delivery | rub | Number | yes | calculated: price × rate + delivery |
| Financing cost per unit | fin | Number | yes | calculated: price in roubles × (prepayment × lead time − (100 − prepayment) × credit days) ÷ (100 × 365) × rate ÷ 100 |
| Defect loss per unit | loss | Number | yes | calculated: price in roubles × defects ÷ 100 |
| Total unit cost | total | Number | yes | calculated: price in roubles + financing cost + defect loss |
Example rows
| Supplier code | Style SKU | Delivery terms (Incoterms) | Price per unit (in currency) | Currency | Rate to the rouble | Delivery to the warehouse per unit, RUB | MOQ, units | Production and delivery lead time, days | Prepayment, % | Credit days after delivery | Expected defects, % | Cost of money, % a year | Price in roubles with delivery | Financing cost per unit | Defect loss per unit | Total unit cost |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| S01 | A100 | FOB | 28.5 | CNY | 12.6 | 36 | 300 | 75 | 30 | 30 | 2 | 20 | 395.1 | 0.32 | 7.9 | 403.33 |
| S02 | A100 | EXW | 31 | CNY | 12.6 | 74 | 150 | 60 | 50 | 0 | 1.2 | 20 | 464.6 | 7.64 | 5.58 | 477.81 |
| S03 | A100 | DDP | 5.1 | USD | 91.5 | 0 | 100 | 35 | 100 | 0 | 0.8 | 20 | 466.65 | 8.95 | 3.73 | 479.33 |
The example data is illustrative: an exercise, replace it with your own.
Note
Use your own cost of money (the loan rate or the return on idle cash); the example uses 20% a year for illustration. Delivery terms set who pays freight and insurance: compare suppliers on one base. The example data is illustrative: an exercise, not a norm or an industry benchmark.
How to open the file
The file is UTF-8 with a BOM, comma separated, with a decimal point. Set the text format for code columns (SKU, article, GTIN) so that long numbers and leading zeros are not distorted. The validation rules are suggested defaults, not norms: your systems and contracts may require something else.
Calculations that use these fields
- Supplier terms: total cost of buying per unit of demand
- Comparing quotes: CMT, FOB, full package
- Cost of payment terms, deductions and factoring
- Landed cost by style: spreading the shipment
- Far and near factory: break-even premium
- Collection calendar: critical path and slack
- Air or sea: does speeding up pay
- Customer credit limit and wholesale receivables aging
Documents that need this data
- Commercial terms: payment terms, retro bonuses, MDF and listing fees, SLA penalties, chargebacks
- Sourcing from factories: CMT, FOB, OEM/ODM, full cost and lead times
- Price list and costing (landed cost)
- Collection calendar (critical path) and lead time
- Group structure, dividends and CFC: how to arrange the ownership of a brand and a retailer and what to check
- Job profiles: sourcing manager, PLM manager and marketplace manager — what they count and which documents they make
- Treasury: credit lines, overdraft, factoring, letters of credit, covenants