Spreadsheet mistakes that misprice offers
Nine mistakes that turn up in the spreadsheets brands price their offers in. Each is worked on one order with costs anyone can check: what the sheet shows, what the order earns, what the error does to the decision, and what prevents it. For where a spreadsheet is the right tool, see Offer Suite vs a spreadsheet.
The nine mistakesEach with the sheet's figure beside the order's
- 1. The blended average that drops an optionA blended order value whose formula stops one row short leaves an option's dollars out of every total, while the shares still add up to 100%.
$76.28blended order value; the sheet shows $62.45
- 2. Averaging margin percentages instead of dollarsA blended margin that averages each option's percentage by its share of orders is wrong. Margin has to be weighted by revenue: sum the dollars, divide once.
48.3%blended margin; the sheet shows 47.0%
- 3. Refunds counted twice in an offer's marginA refund reserve taken off revenue and then summed again inside total cost understates margin by the whole reserve. The worst hit are high-return categories.
$39.16margin per order; the sheet shows $16.00
- 4. Break-even renewals that ignore churnDividing a subscription's first-order loss by one renewal's margin assumes every subscriber renews. With churn the count is higher, or the loss never returns.
9 renewalsto pay back the first order; the sheet shows 5 renewals
- 5. A hard-coded total in a margin sheetOne typed number in a chain of formulas stays behind when the tab is copied or a price changes, and the sheet sets one offer's costs against another's revenue.
$56.57margin per order; the sheet shows −$2.86
- 6. Dead references in copy-pasted offer tabsCopying a tab for the next offer leaves formulas pointing at rows that are gone. The errors that show are the mild case; the ones guarded into zeros get used.
$25.42average first-order margin; the sheet shows $6.94
- 7. Margin computed on a price that includes VATWhere shelf prices include VAT or GST, margin worked out on the shelf price counts the tax as money kept. The tax comes almost entirely out of the real margin.
18.6%margin; the sheet shows 30.8%
- 8. Row labels that don't match their formulasA row's label is typed text and its formulas are whatever was copied. A tier that says three units and costs two shows margin the offer never had.
50.6%margin on the three-pack; the sheet shows 61.5%
- 9. A sign error in a free trial's first orderA free trial's first order earns zero. A sheet that books it as minus the price charges the trial for the product twice, and a trial that pays back is rejected.
−$24.79first-order margin; the sheet shows −$64.79
ExplainersHow a cost behaves, worked on one order
- Why your 3-pack costs almost the same to shipA shipping label is priced in whole-pound steps and a warehouse charges little for an extra item, so a three-pack ships for barely more than one unit.
$10.50label on a three-pack; the sheet shows $27.87