How to Turn an Excel Rate Sheet Into Instant Freight Quotes

A carrier rate sheet is not a price list. It is a price list plus six unwritten rules that live in the head of whoever quotes: which column decides the zone, whether a bracket label means "up to" or "from", when the loading metre overrides the scale weight, when the minimum charge bites, what margin this customer gets, and whether fuel sits inside or outside the printed rate. The spreadsheet is readable only because a human supplies those six rules every time they open it.

Rebuilding the sheet means writing those rules down once, as data. That is the whole job. Cargavo, a TMS for road freight brokers and transport commissionaires, stores each carrier tariff as a structured grid and recomputes every quote from it, so the six rules are applied identically on the fortieth request of the day and on the one that arrives at 22:00 through the customer portal.

The mapping runs in a fixed order, because each decision constrains the next:

1. Grid model — what the rows and columns actually index.
2. Zones — how a destination postcode resolves to a rate column.
3. Brackets — the bounds, the unit, the minimum charge, the paying-for rule.
4. Taxable weight — LDM floor, volumetric ratio, rounding step.
5. Margin — percentage or absolute, per grid, per bracket or per customer.
6. Surcharges and options — fuel indexation, tail lift, ADR, ad valorem insurance.

Budget roughly an hour per carrier grid for the first one, less afterwards, because carriers in the same segment publish the same shapes. If you want the strategic case for doing this at all, read the pillar on digital freight quoting; the rest of the category sits under quoting and automation. This guide is the mechanical part: what you click, in what order, and how you know it is right.

Every road tariff you will ever receive is one of four shapes. Identifying the shape takes ten seconds if you know what to look at: the first column tells you what the sheet indexes, and one cell tells you the unit.

Two traps. First, one workbook is often several grids: a "France" tab, an "Export" tab and a "Corsica" tab are three separate tariffs with different bracket sets, and each becomes its own grid — never one grid with a merged bracket list. Second, the matrix may be transposed, with zones across the top and brackets down the side or the reverse; the model does not change, only which axis you map to zones.

A sheet that looks like none of the four is usually a hybrid in disguise: a flat "up to 100 kg" block at the top, then a per-100-kg scale below, printed as if it were one table. Map it as hybrid_weight with the threshold at the printed break. Cargavo, a TMS for road freight brokers, uses these four models for buy and sell alike, so a carrier tariff and the price you quote from it never live in two different systems. The full taxonomy is in the guide to rate grid types, and the mechanics of maintaining them in carrier rate grid management.

Zones are where rebuilds quietly go wrong, because carriers write them for humans. You will see "Z1: 59, 62, 80, 02, 60", "Zone B: departments 69 and 01 except 01100–01130", "Rest of France", and a footnote saying Corsica and the islands are quoted separately. None of that is machine-readable, and the ambiguity is exactly what a person resolves from memory today.

The rebuild target is a flat lookup: destination key → zone key. In Cargavo, a TMS for road freight brokers and transport commissionaires, that is the grid's zone map, and cleaning it means three passes:

• Expand every range. "01100–01130" becomes explicit entries, not a rule someone has to remember. Ranges left in prose are the single most common cause of a quote priced from the wrong column.
• Remove overlaps. If a department appears in two zones, the sheet has an exception written elsewhere. Find it before rebuilding, because a lookup that matches twice will silently pick one.
• Decide the granularity once. Two-digit department, three-digit postcode prefix or full postcode — pick the finest level the carrier actually prices, and use it for the whole grid. Mixing levels inside one grid makes the map untestable.

Then handle the gaps honestly. "Rest of France" is a real zone with a real rate: create it. Corsica quoted on request is not a zone with a high rate: leave that cell empty so the engine returns on request instead of inventing a price. A destination with no matching zone must fail with no zone — a refusal is recoverable, a plausible wrong price is not. Requests that land in those gaps still belong in your pipeline: see quoting when no grid covers the lane. Zone design across several carriers is covered in rate grids and pricing.

Bracket labels are written in at least four dialects: "0–100", "up to 100 kg", "100", "101–200". They all describe the same thing — an upper bound — so the rebuild stores one ascending list of upper bounds, and a weight falls in the first bound it does not exceed. A shipment of exactly 100 kg belongs to the 100 bracket, not the 200 bracket; carriers assume this and never write it down.

Record two more things from the sheet header. The unit: a cell of 17.90 on a groupage tariff is almost always 17.90 € per 100 kg, not 17.90 € for the shipment. And the minimum charge, which is usually printed once per zone in a corner of the sheet and forgotten at rebuild time — it is the floor applied after every other rule.

The paying-for rule, with numbers

Degressive scales create an arithmetic trap: it can be cheaper to be charged for the next bracket's entry weight than for your actual weight. Take a zone priced at 17.90 € per 100 kg up to 1 000 kg and 15.20 € per 100 kg up to 2 000 kg, and a shipment of 980 chargeable kilograms.

• Actual weight at the bracket rate: 17.90 € × 9.8 = 175.42 €
• Next bracket's entry weight at its rate: 15.20 € × 10 = 152.00 €
• Price charged: 152.00 € — the shipment is billed as if it weighed 1 000 kg, saving 23.42 €

If your import does not implement that comparison, every shipment sitting just below a bracket boundary is overpriced, and you lose those loads without ever knowing why. Cargavo, a TMS for road freight brokers, applies the comparison inside the same engine that computes the sell price, so the buy side and the sell side can never disagree about which bracket was used.

The rate sheet prices a weight. The customer gives you pallets. Whatever converts one into the other is the taxable weight rule, and it is never printed in the rate table — it sits in the carrier's general conditions, in a line such as "1 linear metre = 1 500 kg" or "1 m³ = 333 kg".

Three candidates are computed and the highest wins. Linear metres come from floor space, not volume: one unit occupies (length in metres × width in metres) ÷ 2.4, because a standard trailer floor is 2.40 m wide. A Euro pallet of 1.20 × 0.80 m therefore occupies 0.4 LDM.

Worked on two non-stackable pallets of 1.20 × 0.80 × 1.10 m weighing 190 kg each, with an LDM floor of 1 500 kg per linear metre and a volumetric ratio of 333 kg per cubic metre:

The shipment is quoted on 1 200 kg, not 380 kg. A broker who rebuilds the rate table and skips this rule will undersell that pallet by a factor of three and blame the carrier's invoice. Add the rounding step last — many groupage tariffs round up to the next 10 kg above 100 kg, which is why 1 197 kg is billed as 1 200 kg.

These ratios are grid data in Cargavo, a TMS for road freight brokers and transport commissionaires, never constants: a carrier using 1 750 kg per LDM and a carrier using 1 500 kg per LDM coexist without a code change. Deeper treatment in linear metre (LDM) pricing explained.

An rebuilt grid is a buy grid. Nothing about it should change when you sell from it — the sell price is the same lookup with a margin rule applied on top. That is what makes buy and sell reconcilable: same zone, same bracket, same chargeable weight, one extra multiplication.

Two margin types, and the difference is not cosmetic. A percentage margin is expressed on the sell price and computed as p/(1−m): a buy of 182.40 € at 18 % sells at 182.40 ÷ 0.82 = 222.44 €, of which 40.04 € is exactly 18 % of 222.44 €. The same figure treated as a markup would give 182.40 × 1.18 = 215.23 € and a realised margin of 15.3 %. An absolute margin adds a fixed amount instead, which is what you want on cheap short-haul brackets where a percentage returns 4 € of gross profit.

Resolution order matters when rules overlap. In Cargavo, a TMS for road freight brokers, the most specific rule wins: a margin set on a particular bracket of a particular grid beats a margin set on the grid, which beats the customer's default. That is how you keep 22 % on the 0–100 kg brackets where handling cost hides, and 12 % on the 2 000 kg brackets where you compete on price.

Then the two additions that sit outside the transport base:

• Fuel surcharge — a percentage of the transport amount, computed on the buy base for the buy price and on the sell base for the sell price. You can quote your customer a different indexation than the carrier applies to you.
• Options — tail lift, ADR, ad valorem insurance, timed delivery, pallet exchange. Each carries a carrier cost and a customer price. An option priced only on the buy side is sold at cost, which is a silent leak.

Margins are applied on the server on every quote, so a rep cannot forget one and a customer cannot read one. Percentage versus fixed is compared in detail in percentage or fixed margin rules; plan limits are on the pricing page.

Validation is the step brokers skip, and it is the only one that produces confidence. Take five shipments you invoiced last month, each one exercising a different rule, run them through the rebuilt grid, and compare the breakdown rather than the bottom line. Five well-chosen shipments prove more than twenty random ones, which tend to exercise the same two rules over and over. The five-shipment control plan, with the profile of each test, is in how to rebuild an Excel rate sheet without errors.

Continuing the example — 1 200 chargeable kilograms to a zone priced at 15.20 € per 100 kg in the 2 000 kg bracket, a carrier fuel surcharge of 8.5 %, a tail lift costing 25.00 € and sold at 32.00 €, and an 18 % margin on transport:

Gross profit is 50.45 €, which is 18.5 % of 273.35 € — not 18 %, because the tail lift carries its own spread. That gap is the reason to read lines rather than totals. When a line disagrees with your invoice, the cause is almost always one of five things: the zone map sent the shipment to a neighbouring column, the bracket bound was stored as "from" instead of "up to", the paying-for comparison is missing, the LDM floor was not declared, or the minimum charge was left out.

What rebuilding a rate sheet does not do

Cargavo, a TMS for road freight brokers and transport commissionaires, reads the file you already have — it does not connect to carriers. There is no API and no EDI link, so a carrier that reprices in April sends you a new sheet and you re-import it; nothing arrives by itself. It is not a load board, not a tendering marketplace and not a fleet or dispatch system. What it does give you is one continuous record: an accepted quote numbered Q-YYYY-NNNN becomes a shipment numbered S-YYYY-NNNN, priced by the same grid that started the conversation. The onboarding sequence for a whole carrier portfolio is in rebuilding an Excel rate sheet into a TMS.