Dead references in copy-pasted offer tabs

The mistake

A workbook grows by copying. The subscription tab is duplicated for a one-time offer, the rows that don't apply are deleted, and every formula that pointed at them now reads #REF!. Cells that divided by a renewal margin read #DIV/0!. Lookups into a cost table that was renamed read #N/A.

The errors that show are the mild case, because someone sees them. The costly ones are hidden: an error wrapped in a guard that turns it into 0, or a cell on a tab nobody opens that a summary still adds up.

A worked example

Three coffee offers, one tab each. On the first order the subscription keeps $0.20, a single bag bought once keeps $20.61, and three bags bought once keep $55.45.

The three-bag tab was copied from the subscription tab, and its margin cell lost a reference. The summary tab guards against errors, so it reads that cell as 0. Its average first-order margin across the three offers is $6.94, where the offers average $25.42.

The single-bag tab kept the "renewals to break even" cell. With $35.00 spent to acquire the customer, the first order leaves $14.39 unrecovered, and there is no renewal margin to divide it by. Unguarded, the cell reads #DIV/0!. Guarded, it reads 0, which a reader takes to mean the offer pays back at once.

The order's figures are computed by the margin engine in Offer Suite's own code, in whole cents. The sheet's are the same order with the mistake applied.

What it does to the decision

A zero is a number, and it gets used. The summary's average drags down a set of offers that are fine, and the offer missing from it is the best of the three. In the other direction, a break-even of 0 renewals says that an offer which never recovers its acquisition cost has nothing to recover.

Once a workbook has a few of these, people stop trusting any tab they didn't build themselves, and each new offer starts from one more copy.

What prevents it

Offer Suite has nothing to copy between offers. Each offer is its own validated document, and its figures are computed from it by one engine, so one offer can't point at a row of another.

Where something is missing, the answer says so instead of falling back to zero. A forecast lists the required costs no line has an amount for yet, because the margin is overstated until they are entered. Payback on an option with no renewals, or whose renewals earn nothing, comes back as never.

A spreadsheet is still the right tool for some of this. Where it is, and where it stops: Offer Suite vs a spreadsheet.

Where the numbers come from

The example is not a real brand's offer. The acquisition cost of $35.00 is made up. Every other input is sourced below, or marked there as an assumption.