Compare supplier total cost: delivery, minimums and volume

Compare supplier total cost by pricing the quantity you must actually buy, then adding delivery. After this guide, you can calculate two order payments and identify the volume where the lower-cost offer changes.

Updated: 29 October 2026. Worked example with hypothetical figures.

What must match before you calculate?

Start with a purchasing requirement, not a supplier ranking. Specify one item, its size and material, the delivery address and the required date. A cheaper substitute is a separate decision. This worksheet assumes the goods are genuinely equivalent. Use the wholesale supplier guide if you still need to establish a shortlist.

The result is the amount payable for one order under stated assumptions. It is not a complete economic assessment of a supplier relationship. Both quotations must use the same confirmed tax basis and include every mandatory charge. If freight is missing, leave the decision unresolved rather than entering zero. If another charge applies, add it before calling the result a complete order amount.

Minimum order quantity means the smallest purchase the supplier accepts. Shopify explains that minimums can involve units, order value and more complex constraints. Here, we use a simple unit minimum only. Case packs, product-specific restrictions and changing price tiers need a different worksheet. Confirm those conditions rather than quietly treating every quantity as available.

Enter the four inputs and preserve their sources

Record the quantity you need, unit price, delivery charge per order and minimum quantity. Keep the quotation version beside the numbers. A spreadsheet cannot tell whether a shipping price from an old email still applies to a revised product offer. When the supplier changes a condition, create a new calculation version.

All amounts use the same illustrative currency. This is a worked example with hypothetical figures, not a supplier quotation, customer case or reported saving. We assume one shipment, identical specifications, the same fully included tax basis, no additional charges and unchanged prices across the quantities tested. None of those assumptions should be imported into a real purchase without verification.

If your difficulty is extracting reliable inputs, first use the source-first quotation comparison. That workflow identifies missing facts. This calculation begins after those facts are known and answers a narrower question: which offer requires less payment at a particular volume?

Calculate the quantity you must actually buy

A minimum changes the order quantity before it changes the price. Take the greater of your requirement and the supplier minimum. Multiply that purchase quantity by the unit price, then add the single delivery charge. This method assumes you are allowed to buy any whole number above the minimum. It does not handle mandatory carton multiples.

Purchase units = MAX(required units, minimum units)
Order amount = purchase units × unit price + delivery

For a requirement of 100 units, A costs 100 × 2 + 10 = 210. B costs 100 × 1.80 + 40 = 220. The apparently cheaper unit price produces a higher order payment. At this requirement, A is lower by 10 under the stated assumptions.

Now reduce the requirement to 80. A allows 80 units and costs 170. B still requires 100 units, so its payment remains 220. You would also receive 20 units beyond the immediate requirement. Calculating 80 × 1.80 + 40 would ignore the offer's minimum. Do not value those extra units as an automatic benefit; record whether and when you can actually use them.

Find the volume where the result changes

Once both minimums are satisfied, A follows 2q + 10 and B follows 1.80q + 40, where q is the purchase quantity. Set them equal: 2q + 10 = 1.80q + 40. Rearranging gives 0.20q = 30, so the crossover is 150 units. This is ordinary algebra applied to the example, not a supplier policy or a forecast.

The neighboring rows matter. At 149, A is still lower. At 150, both amounts are 310. At 151, B becomes lower. Checking the boundary prevents a rounded summary from turning a tie into a recommendation. At 200 units, B costs 10 less, but this does not justify doubling a genuine requirement of 100.

If the calculated crossover falls below either minimum, the simple linear comparison is not valid there. Use the actual minimum-adjusted quantities instead. Repeat the calculation for every price tier or delivery threshold rather than extending one formula across changing conditions.

Build a small spreadsheet you can check

For this exercise, open an existing desktop installation of Excel and choose Blank workbook. Enter Offer, Unit price, Delivery, Minimum, Purchase units and Payment in A1:F1. Set A2 to A, B2 to 2, C2 to 10 and D2 to 50. Set A3 to B, B3 to 1.80, C3 to 40 and D3 to 100. Use your regional decimal separator so B3 is numeric, not text. Enter 100 in H1. For each line below, select the named cell and type only the formula beginning with the equals sign, then press Enter. For example, enter =MAX($H$1,D2) in E2, without the E2 label.

E2 =MAX($H$1,D2)
F2 =E2*B2+C2
E3 =MAX($H$1,D3)
F3 =E3*B3+C3

Microsoft documents MAX as returning the largest supplied value. Your regional settings may require a semicolon instead of a comma between arguments. The function does not verify commercial facts, and an empty charge field must not be treated as confirmation of free delivery.

Test three inputs before using the worksheet. At 100 required units, the amounts should be 210 and 220. At 80, B's purchase quantity should stay at 100. At 150, both amounts should be 310. A mismatch means the sheet needs correction. Keep those expected results as a small check beside the formulas so another person can reproduce the exercise.

For a larger purchasing review, annual inventory consumption analysis helps organize items by usage value. That is a separate prioritization task, not a replacement for checking this order's actual quantities and charges.

Record a purchasing decision, not just a winner

A lower calculated payment is one decision input. Check delivery reliability, payment timing and specification acceptance separately. Two offers with identical totals may require very different cash commitments. Put the proposed payment dates into your cash flow plan before approving an order.

Use a short decision record: “Required quantity: 100. A: 210. B: 220. A requires the lower payment if the identical-specification and complete-charge assumptions are confirmed. Outstanding checks: delivery date and payment terms.” This wording preserves the limits of the calculation without hiding the useful answer.

If B offers a new delivery price, save the revised offer and rerun the sheet. Do not replace the old number without a version note. If you want to change the commercial terms, use the supplier negotiation guide. A desired discount is not an accepted discount, so it belongs in a scenario until confirmed.

Also resist purchasing excess stock simply to reach a mathematically attractive quantity. Storage space, spoilage, uncertain demand and financing are outside this small model. A threshold describes the example's cost curves. It does not tell your business how much demand exists.

Finish a repeatable 15-minute exercise

Use one item and treat four minutes for inputs, four for calculation, four for checks and three for recording as a planning target, not a measured or guaranteed duration. After the final 150-unit check, put the conditional decision in A6, assumptions and unresolved conditions in A7, and input source names and dates in A8. Choose File → Save As and save supplier-comparison.xlsx in Documents. If that name exists, choose another rather than overwrite it. Close the workbook, reopen that saved file from Documents and check H1 is 150, F2 and F3 are 310, the formula bar still shows formulas, and A6:A8 retain your notes. Until you perform this readback, do not label the saved result verified.

This is a suggested practice structure, not a guaranteed completion time. If essential facts are missing, the useful finished output is a blocked comparison with precise questions. You have still improved the decision by showing what prevents an honest total.

1. Save the requirement and both offer versions.
2. Keep formulas visible rather than typing totals manually.
3. Check one quantity below a minimum and three near the crossover.
4. Separate calculated payment from the final approval decision.

When a colleague repeats the exercise, ask them to start from the saved inputs rather than your conclusion. They should reach the same purchase quantities and amounts. If they cannot, clarify the assumptions before using the worksheet for a real order.