Compare plans on total cost

Convert two quotes written in different units into totals for the same quantity, compute the quantity at which they cost the same, and state the condition under which that break-even figure is meaningful.

Two quotes almost never arrive in the same shape. One is per unit, one is per case. One has a delivery charge, the other a setup fee. One has a discount that starts somewhere. Neither is trying to be confusing; each is written the way that supplier's business works.

The consequence is that the cheaper quote and the cheaper supplier are different questions. A lower price per unit loses to a setup fee at small quantities and wins at large ones, and the quantity where that flips is a real number that can be worked out — but only after both quotes have been converted into totals for the same amount of the same thing.

The mistakes here are not subtle once seen and are almost invisible in a spreadsheet: comparing a case price against a unit price, forgetting a fixed charge, or applying a discount at a quantity that does not qualify for it. Each produces a confident recommendation.

Total cost, not price. The comparable quantity is what leaves your account for a given amount of goods. That is the unit price times the quantity, plus every charge that does not scale with quantity, minus every discount that applies at that quantity. A quote is a set of rules for producing that number; the sheet's job is to apply them.

Convert the units first, and convert in the right direction. The price per cone is the case price divided by twelve, but the quantity must be rounded up to whole cases. Cases cannot be split. Five hundred and seventy-four cones needs forty-eight cases, which supply 576 cones, two extra. Rounding down to 47 cases supplies only 564 cones, ten short. ROUNDUP is the function, and the fact that it always rounds away from zero is exactly why.

That indivisibility has a consequence people miss: a supplier who sells in cases has a stepped cost, not a straight line. Between 577 and 588 cones the price does not change at all, and at 589 it jumps by a whole case.

Fixed charges are what make the answer depend on quantity. If both suppliers charged only per unit, the cheaper one would be cheaper at every quantity and there would be nothing to compute. A setup fee, a delivery charge, a minimum order — each is a cost you pay once, spread over however much you buy, so it matters more the less you order.

A whole-order volume discount creates a price drop and a new slope. At four hundred cones or more, Supplier A applies its five-percent discount to every cone in the order. Reaching that threshold lowers the total: 399 cones cost 1,321.80, while 400 cost 1,261.00. After that drop, each additional cone costs 3.04. One price-per-cone figure cannot describe both sides of the threshold.

The break-even quantity is where the two totals meet. With straight-line costs it is arithmetic:

= (difference in fixed charges) / (difference in price per unit)

Two conditions have to hold for that number to mean anything, and both are easy to forget.

The prices you used must be the prices that apply there. If you computed the break-even using a discounted rate, and the break-even lands below the discount threshold, you have used a price the supplier would not charge. Put that check in the sheet as a cell, because it is not visible from the answer.

Neither cost is stepped. If one supplier sells in cases, the two totals are not actually equal at the break-even quantity — they are close, and which is cheaper right around it depends on where the steps fall. The linear break-even is an estimate of where the answer changes, not a crossing point.

And one thing worth saying out loud whenever a break-even is reported: check whether the quantity is one anybody would ever order. A break-even of nine thousand cones for an organisation that buys three hundred a year is a correct number and a useless one, and reporting it without that observation invites somebody to plan around it.

Two quotes for training cones:

Supplier A, per cone           Supplier B, per case of 12
  3.20 each                      32.40 per case      = 2.70 per cone
  45.00 delivery per order      240.00 setup fee, paid once
  5% off at least 400 cones        cases cannot be split

Two purchasing units in the quote

Supplier A: per cone

Purchasing unit

One cone

Quoted rate

3.20 per cone before the volume discount

Use the number of cones with a per-cone price.

Supplier B: per case

Purchasing unit

One whole case of 12 cones, stated in the quote

Quoted rate

32.40 per whole case

Convert the required cones into whole cases before using a per-case price.

Before any formula: A is dearer per cone and cheaper to get started with; B is cheaper per cone and costs 240 before a single cone arrives. Which is cheaper therefore depends entirely on how many you buy, and nothing about the two quotes side by side tells you where the line is.

At 300 cones:

A   300 × 3.20 + 45                     = 1,005.00
B   ROUNDUP(300/12) = 25 cases
    25 × 32.40 + 240                    = 1,050.00

A is cheaper by 45.00.

At 600 cones, A qualifies for its discount:

A   600 × 3.04 + 45                     = 1,869.00
B   50 × 32.40 + 240                    = 1,860.00

B is cheaper by 9.00. The answer has flipped, and it flipped somewhere between 300 and 600 without anything about either quote changing.

Using those two quotes, predict all four.

=ROUNDUP(574/12,0)
=574*3.20*(1-0.05)+45
=(240-45)/(3.20*(1-0.05)-32.40/12)
=ROUNDUP(573.53/12,0)*32.40+240

A first comparison:

A total   =B17*$B$5
B total   =B17*$B$12

Three faults, in descending order of size.

The units. $B$12 is the price per case and B17 is a number of cones, so the second formula prices three hundred cases. It is out by a factor of twelve and there is nothing on screen to suggest it.

B cases   =ROUNDUP(B17/$B$11,0)
B total   =B19*$B$12

The fixed charges. Delivery and setup are the whole reason the answer depends on quantity, and leaving them out removes the question.

A total   =B17*$B$5+$B$6
B total   =B19*$B$12+$B$13

The discount. It applies at or above a threshold, not everywhere:

A total   =IF(B17>=$B$7,B17*$B$5*(1-$B$8),B17*$B$5)+$B$6

Applying it at every quantity understates A at small orders and would have recommended A at 300 by a larger margin than is real. Applying it nowhere overstates A at large orders and hands the decision to B too early.

The corrected pair are now comparable, and the comparison is a formula rather than a judgement:

=IF(B18<B20,"Supplier A",IF(B20<B18,"Supplier B","the same"))

The three-way form matters. A two-way IF reports one supplier as cheaper when the totals are equal, which at a break-even is exactly the case you are looking at.

A recommendation says Supplier B saves 18% and should be used for everything. The order that prompted it was for 250 cones.

1. Recompute both totals at the actual quantity. Not at a round number, and not at the quantity in the quote's example. At 250, B needs 21 cases and pays a 240 setup fee; A pays 800 plus 45 delivery. B is dearer.
2. Find where the 18% came from. Almost always, a per-unit price compared against a per-unit price, with the fixed charges left out. 2.70 against 3.20 is a saving of 15.6% on the unit price and says nothing about the total.
3. Check the units on both sides. If one quote is per case, a comparison of the two quoted numbers is out by the case size before anything else is wrong.
4. Ask at what quantity the claim would be true. Work out the break-even. If it is 574 and the organisation buys 250 a year, the recommendation is right about a business the organisation is not in.
5. Check whether the discount was applied where it applies. A discount that starts at 400 does nothing for an order of 250, and a model that applies it anyway makes A look better than it is — which, in this case, would have hidden the error by pointing at the right answer for the wrong reason.

The general shape: a percentage saving with no quantity attached is not a claim about anything. Ask "at what quantity?" before asking whether the number is right.

Two quotes, three quantities, and a break-even. Then the part that matters: work out whether the break-even you calculated is a figure this model can support.

Find two real quotes for the same thing — two broadband plans, two printers with their cartridges, two suppliers at work, two ways of getting somewhere.

1. Write down the total cost of each for a quantity or a period you would actually buy. Include everything that is not the headline price.
2. Do the same for a quantity four times larger and a quantity a quarter the size.
3. If the cheaper option changes, work out the break-even. If it does not, say why not — usually one option dominates because it has no fixed charge.
4. Say whether the break-even is a quantity you would ever reach.

Question 4 is what separates a comparison from a decision. A break-even you will never reach means the answer is settled, which is a more useful thing to know than the break-even itself.

Sign in to track your progress on this exercise.

This is a good place for an assistant, because there is a figure you can check it against.

Ask it to derive the break-even formula. "Two suppliers: one charges F1 fixed plus P1 per unit, the other F2 plus P2. At what quantity are they equal?" The answer — the difference in fixed charges over the difference in unit prices — is standard algebra, and you can verify it in the sheet immediately: put that quantity into both total-cost formulas and see whether they agree.

Ask it to check your arithmetic against your own working. Give it the two quotes and your total for one quantity, and ask whether it gets the same. A disagreement is worth investigating whichever of you is wrong.

Do not ask it which supplier to use. It does not know your quantity, your delivery deadline, whether either supplier has let you down before, or whether the setup fee is refundable. Those are most of the decision.

Move on when you can do these.

• Say what has to be true before two quotes can be compared at all.
• Explain why 574 cones is 48 cases, and what rounding the other way would cost you.
• Name the two charges in this lesson that make the cheaper supplier depend on quantity, and say which direction each pushes.
• Write the break-even formula and name the two conditions under which it is meaningful.
• Explain why the two totals are not equal at the break-even quantity in this exercise.
• Say what question to ask about any reported percentage saving.

Sign in to track your progress on this exercise.

A comparison is finished when both quotes are totals for the same quantity of the same thing, every fixed charge and every conditional discount is in, the answer has been checked at more than one quantity, and the break-even is reported with the condition that makes it meaningful and the observation of whether anyone would ever order that much.

Next: Scenarios and sensitivity. Every figure so far has assumed the inputs are known. The last lesson of this module is about what to say when they are not.