The blended average that drops an option

The mistake

An offer with several options has no single order value. The sheet makes one by weighting each option's order value by the share of orders that take it. That blended figure then feeds everything downstream: the break-even ad cost, the revenue forecast, the comparison with last month's offer.

The weighting formula covers a range of rows. When someone adds an option below the range, or inserts one at its edge, the formula keeps its old range. The new option's orders are still in the shares, which still add up to 100%, so nothing looks wrong. Its dollars are in no total.

A worked example

A bottle of supplements at $37.95, sold with quantity breaks: 5% off each for two, 10% for three, 15% for four, and $9.29 of shipping charged on every order. Out of 1,000 orders, 450, 300, 150, 100 take one, two, three and four bottles. The four-bottle option was added last.

Weighting all four options gives a blended order value of $76.28. The sheet's formula stops at the third row and shows $62.45, which is $13.83 too low: the fourth option's $138.33 order times its 10.0% share.

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

The sheet reports 18.1% less revenue per order than the offer earns, and every figure built on the blended order value inherits that. The offer keeps $36.84 an order after its costs. A team that works out what it can pay for an order from the sheet's figure sets the cap too low and turns off ads that were paying.

The option that goes missing is the one added last, and here that is the largest pack, so the sheet drops the order that would have raised the average the most. The error has no fixed direction, though. If the range loses the cheapest option instead, the blended figure comes out too high and the offer looks better than it is.

What prevents it

Offer Suite has no range to maintain. An offer's options live in one offer document, and every figure that blends them reads all of them from it. The margin engine's projection, which blends options by take share, refuses a mix whose shares don't add up to 1 instead of computing a number from part of the offer.

After launch the blend isn't typed at all. Realized profit counts each option's orders from the store, so the shares are observed and every order belongs to exactly one option.

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 split of orders across the four options is made up. Every other input is sourced below, or marked there as an assumption.