Compare the alternatives

Split two quotes into fixed and variable costs, find the volume at which the cheaper option changes, and report how far each assumption would have to move before the recommendation reverses.

Cedar Ridge has two quotes for next year's equipment.

Option A is an annual contract: 1,200 up front, then 3.60 an item. Option B is ordering each term: no fee, 4.25 an item, and 85 for delivery on each of six orders.

Somebody will read those two paragraphs and say Option A is cheaper, because 3.60 is less than 4.25. Somebody else will say Option B, because 1,200 is a lot of money to hand over in September. Both are guessing, and the meeting will be decided by whoever is more confident.

The arithmetic is not hard. At the 900 items the centre plans to buy, Option A costs 4,440 and Option B costs 4,335. Option B wins by 105.

That is where most comparisons stop, and it is where this one becomes interesting. 105 on an order of 4,335 is 2.4%. The two options change places at 1,062 items — and the centre's own busy-year estimate is 1,200. So the honest finding is not "Option B is cheaper". It is "Option B is cheaper at the volume we plan for, and a year 18% busier than planned reverses that".

The second sentence is longer and it is the one that survives contact with a busy year. Producing it takes three cells more than producing the first.

Every option has a fixed part and a variable part, including the ones that look like they don't.

Option A's fixed cost is obvious: it is called a fee. Option B's is 510, and nothing on the quote calls it anything. Six deliveries at 85 are owed whether the centre orders one item or a thousand, which makes them fixed by behaviour rather than by name.

Finding the fixed cost that nobody labelled is most of the work in a comparison like this. Minimum charges, call-out fees, licence seats bought in blocks, a per-order handling charge — all fixed, all easy to bury in a per-unit conversation.

             Option A                 Option B
Fixed        =B5              1,200   =B8*B9              510
Variable     =B4*B6           3,240   =B4*B7           3,825
Total        =B12+B13         4,440   =C12+C13         4,335
Per item     =B14/B4           4.933  =C14/B4            4.817

The per-item row is worth a moment. Option A's headline price is 3.60 and it actually costs 4.93 an item; Option B's headline is 4.25 and it costs 4.82. Neither headline price is what anybody pays, and comparing the two headlines — the thing a reader does in three seconds — gets the answer backwards.

Break-even is one division.

Two straight lines cross once. The option with the higher fixed cost is behind at low volumes and catches up as volume rises, and it catches up at exactly the rate its unit price is lower:

=(fixed A − fixed B) / (unit price B − unit price A)
=(1200 − 510) / (4.25 − 3.60) = 690 / 0.65 = 1,061.54

690 of extra fixed cost, recovered at 0.65 an item, takes 1,061.54 items. Items are whole and the number has to be rounded up: at 1,061 items Option A is still behind. Rounding to nearest would name a volume at which the recommendation is wrong.

Then the question that matters: does the plausible range straddle it?

A break-even of 1,062 means nothing on its own. Set against a plan of 900 it means the recommendation has 162 items of headroom, or 18%. Set against a busy-year estimate of 1,200 it means the recommendation is already wrong in a year the centre thinks is possible.

              Items   Option A   Option B   Cheaper
Quiet year      700      3,720      3,485   Option B
Planned year    900      4,440      4,335   Option B
Busy year     1,200      5,520      5,610   Option A

Three arithmetic results, not three predictions. Nothing in this table says a busy year is likely; it says what each option costs if one arrives. That distinction is worth being strict about, because a scenario table read as a forecast is a forecast nobody chose to make.

Sensitivity: how far would an input have to move?

Volume is not the only assumption. The delivery charge is quoted at 85 today, and the two options level out when it reaches 102.50 — a rise of 17.50 per delivery.

=(total A − items × unit price B) / orders per year
=(4440 − 3825) / 6 = 102.50

That number is more useful than it looks. A reader who knows the supplier is about to reprice deliveries now knows exactly how much repricing would matter, and a reader who does not can see that the recommendation does not hinge on a rounding error.

The habit: for every input the answer depends on, report the value at which the answer changes, not just the value it currently has.

The two quotes, written out as they arrived:

Option A   Annual contract
           Fee                    1,200 per year
           Unit price              3.60 per item
           Delivery              included

Option B   Order each term
           Fee                     none
           Unit price              4.25 per item
           Delivery                  85 per order, six orders a year

Read as prose, Option B has no fixed cost. Read as arithmetic, it has 510 of one.

At the planned 900 items:

                    Option A   Option B
Fixed                  1,200        510
Variable               3,240      3,825
Total                  4,440      4,335
Cost per item          4.933      4.817

Option B is cheaper by 105.

Now the same two options at 1,200 items:

Total                  5,520      5,610

Option A is cheaper by 90. Neither quote changed. The ranking reversed because the volume crossed 1,062, and every conclusion drawn at 900 items carries that condition whether or not anybody writes it down.

The break-even cell and the two either side of it:

B19  =(B12-C12)/(B7-B6)
B20  =ROUNDUP(B19,0)
B21  =IF(B4>B19,"above break-even",IF(B4<B19,"below break-even","exactly at break-even"))

The contract fee in B5 is 1,200. Suppose the supplier raises it to 1,500 and nothing else changes.

Predict all three cells, and predict what happens to the busy-year row of the scenario table.

B19   =(1500-510)/(4.25-3.60) = 990/0.65 =           1,523.1
B20                                                   1,524
B21   900 against 1,523                     below break-even

              Items   Option A   Option B   Cheaper
Quiet year      700      4,020      3,485   Option B
Planned year    900      4,740      4,335   Option B
Busy year     1,200      5,820      5,610   Option B

A comparison as it usually arrives:

Option A: 1,200 + 900 × 3.60 = 4,440
Option B:   510 + 900 × 4.25 = 4,335
Recommend Option B.

Correct arithmetic, and three things are missing.

First, the size of the gap relative to the total. 105 out of 4,335 is 2.4% - =B17/C14, one division against the total of the option being recommended. It changes how the sentence reads: a 2.4% saving is a reason to prefer Option B and not a reason to stop thinking. The brief in the next lesson carries that percentage as a cell of its own; here it is worth doing in your head before going any further.

Second, the point where the answer changes. One division gives 1,062. Without it, the recommendation is stated as though it were a property of the two quotes, when it is a property of the two quotes at one volume.

Third, whether the plausible range crosses that point. The centre's own busy-year figure is 1,200. It does. So the recommendation needs a condition attached, and the condition is a number the centre can monitor: if the year looks like passing 1,062 items, the contract is the cheaper buy.

None of the three requires a new technique. The whole addition is:

B19  =(B12-C12)/(B7-B6)                       break-even volume
B20  =ROUNDUP(B19,0)                          rounded up, because items are whole
B28  =IF(COUNTIF(E24:E26,E24)=3,"no","yes")   does the answer change

Three cells and one division turn a recommendation into a recommendation somebody can act on for a year without re-running it, because it tells them what to watch.

Build the comparison. Split both options into fixed and variable first — Option B's fixed cost is the one to get right — then the totals, then break-even, then the scenarios.

The last two cells are the sensitivity: the delivery charge at which the two options level out, and how far that is from the charge quoted.

Take a recurring cost you actually pay where two structures are available — a phone plan against pay-as-you-go, a season ticket against single fares, an annual subscription against monthly, a service contract against call-outs.

1. Write both as fixed plus variable. Find the fixed cost in the option that appears not to have one. Booking fees, minimum charges and per-order delivery all count.
2. Compute break-even in the natural unit — journeys, months, calls, items.
3. Write down your actual usage last year. Not your estimate of it; look it up if you can.
4. Put the two numbers side by side. How far is your usage from break-even, as a percentage?
5. Then the question worth the exercise: in the last three years, has your usage ever crossed that line? If it has, the cheaper option is the one you have to keep choosing, not the one you choose once.

There is no threshold that makes this answer for you, and anybody offering one is guessing. What the number does is tell you which conversation to have: headroom you could cross in a normal year means the decision has a review date attached, and headroom several times your usage means it does not. Cedar Ridge's is 18%, which is the first kind.

Sign in to track your progress on this exercise.

A comparison is finished when both options are split into fixed and variable including the fixed cost nobody labelled, when the crossing point is a computed number rather than an impression, when the scenarios are stated as arithmetic rather than forecast, and when the sensitivity says how far each assumption would have to move before the answer reverses.

Option B by 105, or 2.4%, at the planned volume. Break-even at 1,062 items, 18% above plan. Delivery would have to rise 17.50 to level them.

Those are four numbers and a conclusion. Next, they have to fit on one screen alongside what the assembled data could not establish, in a form somebody who was not in any of these meetings can read in thirty seconds and check in ten minutes.