top of page

Can You Test Your Calendar Before You Promise It?

Writer: Gandhinath Swaminathan
Gandhinath Swaminathan
4 hours ago
11 min read

Put Your Team's Beliefs and Expertise to Work on Your Calendar

This series follows one team through a year of pricing and promotion decisions. It starts with trade promotion at a set list price. Future posts take on other revenue growth levers like list price, the store brand, competitors and new packs. I made up Larkfield Foods, Kestrel Market and the people in this story, and the numbers come from a simulated market. The last post asked whether an optimizer can help your forecast choose the deals.

Cartoon: Tom asks "Are you sure?" Nina, at her laptop, answers "Let's test them." A board on the wall shows four calendars, each as a spread of possible results.

Your Calendar Rests on Three Estimates

You promise the retailer a year of deals before any of them runs. That promise rests on three numbers your team estimates: the baseline, the lift and the post-promotion dip. The baseline is what a product sells without a deal. The lift is what each deal adds. The dip is how far sales fall below normal after a deal, while shoppers use up what they stocked up on. If those numbers are off, which calendar still comes out ahead?

This post follows the team at Larkfield Foods, which makes Sunny Oats cereal, and the cereal buyer at Kestrel Market, the retailer. Six people appear in it:

Name

Role

First appears in

Riley Shaw

Head of revenue growth at Larkfield; signs off on the promotion calendar

Nina Alvarez

Revenue analyst at Larkfield; writes the post-event reports and builds the models

Priya Raman

Key account manager at Larkfield; plans its deals with Kestrel

Tom Becker

Finance partner at Larkfield; approves the trade spend

Hannah Weiss

Runs demand planning at Larkfield; owns the demand forecast

Grace Lin

Cereal buyer at Kestrel Market

Larkfield Foods' team had two new calendars for Sunny Oats Toasted O's 12 oz at Kestrel Market. Both came from an optimizer, a program that searches the possible calendars for the one that earns the most inside the team's limits. The optimizer's calendar reaches the boxes agreed with Kestrel for the year only if every lift holds. Nina Alvarez, the revenue analyst, set both beside two others:

  • Last year's plan, copied

  • Kestrel's minimum: one deal a quarter

  • The optimizer's calendar

  • The spaced calendar: the optimizer solved again with the rule of Priya Raman, the key account manager who plans Larkfield's deals with Kestrel: a stretch at full price between deals

Tom Becker in finance asked Nina, "Are you sure the new calendars beat last year's plan?" He also set what any calendar needed for his approval. In at most 10% of the years the team thinks possible, it could fall short of the boxes agreed. In at most 5% of them, it could run over his trade cap.

"Each of us gave the optimizer one number," Nina said. "Let's each say how far off ours could be, then test the calendars across all of it."

Ask Each Owner How Far Off Their Number Could Be

Each number is a belief, made of a best guess and how far off it could be. Three come with a range. Riley Shaw, who heads revenue growth, adds a fourth with no number yet.

Number

Whose belief

Where it comes from

Best guess

How far off it can be

Lift

Nina Alvarez, revenue analyst

The post-event reports

53% above normal sales for 10% off, more for a deeper cut, a display or the mailer

The 10%-off deals on file ran from 39% to 89%

Baseline

Hannah Weiss, who runs demand planning

Her demand forecast

About 1,040 boxes a week, by season

Her forecast missed full-price sales by about 16% on average

Post-promotion dip

Priya Raman, who plans the deals with Kestrel

Years of watching Kestrel's shoppers, and what Grace Lin, Kestrel's cereal buyer, tells her

Sales right after a deal run at about 95% of normal

Between 92% and 98%, 19 times in 20

Deals run close together

Riley Shaw, who heads revenue growth

Every category Riley has worked in, and why Priya spaces deals

They lift less

No number yet for this box

The test draws from the first three. Riley's belief has no number yet, so the test can't draw from it and leaves it out.

The optimizer chose a calendar from one estimate of each number: Hannah's forecast, Nina's line through the reports (her fit of what each kind of deal adds) and Priya's 95%. The stress test checks how each calendar holds up when each estimate moves as far as the team's records and judgment say it can. The test can't catch a bias that every record shares, and the choice stays with the team.

Run Each Calendar Through Thousands of Possible Years

A what-if is the usual tool for a question like this, and it moves one number at a time. Tom's limits ask something a what-if can't count: how many of the possible years fall short with every number uncertain at once. A Monte Carlo simulation helps answer that by running each calendar through thousands of possible years. In each year, every belief takes a value from its range, and the simulation records what each calendar earns. Then you count how often each calendar comes out ahead and how far its results spread.

Nina set up her calendar stress test this way:

In the simulation

Larkfield's numbers

Why

What stays the same

The four calendars, slot by slot, and Larkfield's prices, costs and fees

It tests the plans and doesn't change them

Lift, in each possible year

Nina refits her line on a random pick of the reports (a bootstrap), so some reports count twice and some not at all. Each deal then gets the swing of one report: what that report's deal sold over what the line said

A different set of reports would give a different line, and single deals land far from any line

Baseline, in each possible year

Hannah's forecast times one of her past misses, taken in runs about a month long

Her misses come in stretches, and a month-long run (Nina's choice of length) keeps a high or low stretch together. Nina centers the misses on the forecast, so the test keeps Hannah's level as forecast and varies only her short-run misses. Her past misses say her level runs low (sales ran about 7% over her forecasts). Last year's sales say it runs high (this year's 1,040 boxes against last year's 900)

Post-promotion dip, in each possible year

A value from Priya's range, most often near 95%

How sure Priya is

What it records

Each calendar's contribution (what Larkfield keeps after trade spend and the cost of the boxes), trade spend and boxes

What Tom and Grace hold the plan to

How many possible years

13,830, the same ones for every calendar

Tom wants any share near his 10% limit read to within half a point, 19 times in 20. With p the share (10%) and N the number of years, the counting error on a share is 1.96 × √(p(1−p)/N), so that takes 13,830 years

More years shrink only that counting error, and they can't fix a wrong belief. Nina ran two tests in the same program, on the same 13,830 possible years:

  1. Test 1, all beliefs vary. In each possible year, Nina's lift, Hannah's baseline and Priya's dip each take a value from their ranges.

  2. Test 2, one belief varies. Only one person's number moves, and the other two stay at their best guess. It runs once for each of the three beliefs with a range.

She wrote it in Python, with NumPy for the random draws and pandas for the summary.

Monte Carlo Is One of Four Ways to Test a Plan

Teams commonly test a plan four ways:

Way to test

What it does

What it tells you

Where it stops

Best and worst case

Sets every belief high, then low

How far the plan can move

Not how likely either end is

One belief at a time

Varies one belief and holds the rest

Which belief moves the result

Misses beliefs moving together

Monte Carlo

Varies every belief at once, thousands of times

How often each plan comes out ahead

Only as good as the ranges you give it, and Nina draws each belief separately

Planning with the ranges built in

The optimizer weighs the ranges while it chooses

A calendar chosen for the whole range

Harder to build and explain

Many planning teams stop at the first two, in a spreadsheet or their promotion tool. Nina's program does the second and the third.

The Calendar That Earns the Most Can Still Miss Your Limits

In Test 1, each calendar earns one result in each of the 13,830 possible years. The chart shows how those results spread. To sum up each spread, the table uses two measures:

  • A typical year is the middle result (the median). Half the possible years earn more, and half earn less.

  • The middle 80% of years is the range left when you drop the best 10% and the worst 10% of years. Planners often call its ends P10 and P90. It's the spread of results the team's own beliefs allow, without the luckiest and unluckiest years. A wider window, such as the middle 90%, would show a wider range. How often each calendar comes out ahead doesn't depend on the window.

Test 1 of the calendar stress test, as four rows, one per calendar. Across is the year's contribution in dollars; each bar's height is the share of possible years that landed in that $500 band. Each row also shows a line for the middle 80% of years and a dot for a typical year. Last year's plan copied: typical year $62,500, middle 80% between $59,500 and $65,700. Kestrel's minimum: typical year $68,800, between $65,800 and $72,100. The optimizer's calendar: typical year $73,000, between $69,000 and $77,400. The spaced calendar: typical year $71,900, between $68,200 and $75,900.
Simulated market.

Test 1: all beliefs vary

Last year's plan, copied

Kestrel's minimum

The optimizer's calendar

The spaced calendar

Contribution in a typical year

$62,500

$68,800

$73,000

$71,900

Range of the middle 80% of years

$59,500 to $65,700

$65,800 to $72,100

$69,000 to $77,400

$68,200 to $75,900

Comes out ahead of the other three

0% of years

Under 1% of years

88% of years

12% of years

Trade spend over Tom's cap (Tom's limit: 5%)

99% of years

0%

0%

0%

Boxes under what Grace and Larkfield agreed (Tom's limit: 10%)

Under 0.1% of years

Over 99.9%

50%

47%

Contribution = sales to Kestrel − trade spend − cost of the boxes, for this box at Kestrel over the year. Shares are of the 13,830 possible years. Each assumes Hannah's baseline of about 1,040 boxes (Simulated market).

What the test shows:

  • Both new calendars beat last year's plan in every possible year. The optimizer's earns the most in 88% of them.

  • It also falls short of the boxes agreed in 50% of them. It plans exactly the 70,886 boxes agreed, so it lands under in about half the possible years.

  • Its results spread the widest. Its shallow deals earn more on each extra box than last year's deep ones, so a lift that lands high or low moves its year further. With only the lift varying (Test 2, below), its middle 80% is $5,000 wide, against $2,600 for last year's plan.

  • Last year's plan is the only calendar that almost always sells the boxes agreed with Grace, but it goes over Tom's trade cap in 99% of years.

No calendar stays inside both of Tom's limits.

These numbers likely run high. The boxes agreed and Tom's cap match what last year's plan sold and spent. Yet the test has that plan selling and spending more in almost every year.

Find the Belief That Decides the Choice

Test 2 shows whose number decides between the calendars: Nina's lift, Hannah's baseline or Priya's dip.

Test 2: the belief that varies

The optimizer's calendar beats the spaced one

Width of the optimizer's middle 80%

Width of last year's plan's middle 80%

Lift (Nina)

89% of years

$5,000

$2,600

Baseline (Hannah)

Every year

$6,500

$5,600

Post-promotion dip (Priya)

Every year

$800

$800

All three at once (Test 1)

88% of years

$8,400

$6,200

Simulated market.

The baseline moves each calendar's year the most, but it moves all four up or down together, so it never changed the winner. Priya's range is the narrowest, and it moves the results least. In Larkfield's test, only Nina's lift changed which calendar came out ahead.

The optimizer's calendar falls short of the boxes agreed in about 50% of years, whichever belief varies, because it plans no boxes to spare. The spaced calendar plans about 220 boxes more. With only Priya's dip varying, that leaves it short in 12% of years, just over Tom's 10%. With only the lift or the baseline varying, it falls short in 46% and 47% of years.

Stress-Test Your Own Deals

Download the calendar stress test (Excel) below. It runs the deals in one calendar through 1,000 possible years and varies each deal's lift. It counts boxes, not dollars. A thousand years keeps Excel quick, but with so few, the shares move by a point or two each time you recalculate.

  1. On the Reports sheet, enter each report's lift and the lift your line gives that kind of deal.

  2. On the Calendar sheet, enter up to 12 deals. Give each one's weeks (one to four), its boxes in a typical full-price week and the lift your line gives it.

  3. On the Calendar sheet, enter the boxes your plan counts on the deals to add.

  4. Read the Summary sheet. It shows the boxes your deals add in a typical year, the range of the middle 80% of years, and how often they reach the number your plan counts on.

Each row of the Years sheet is one possible year. For each deal, RANDBETWEEN picks one of your reports and INDEX looks up its swing. The deal sells what your line says times that swing, and the sheet counts the boxes it adds over normal sales. MEDIAN and PERCENTILE read the range, and F9 (Cmd+= on a Mac) draws new years. The workbook keeps your line and your baseline fixed, so your real range is wider. It also skips the post-promotion dip, so it counts a little high.

Check the Beliefs Against Sales You Already Have

The test can only be as right as the beliefs in it. One check is open to any team: run last year's plan on this year's beliefs and set it beside what it actually sold. Nina ran last year's plan through Test 1, with this year's forecast as its baseline.

Last year's plan

Boxes

As it actually ran

70,886

In the test, a typical year

About 82,300

Possible years that sell as few as it actually did

Under 0.1% of years

Simulated market.

This checks the beliefs, not the calendars. Last year's real sales land below almost every possible year. Either sales did rise that much this year, or a belief runs high, or the test leaves something out. If sales didn't rise, every calendar's typical year in Test 1 runs high too. Four beliefs could explain the gap:

  • Hannah's baseline. The test's baseline is about 1,040 boxes, while last year's full-price sales ran about 900. The check can't say whether this year's normal sales actually rose. Test 2 kept her level as forecast. Her short-run misses moved all four calendars together and never changed the winner, but Test 2 can't say what a lower baseline would do.

  • Nina's lift. This check can't reach the optimizer's most-used deal, 10% off with a display and the mailer. Last year's plan never ran it, no report on file covers it, and the line gives it 105%. No 10%-off deal on file beat 89%.

  • Priya's dip. The scan data reports sales by week, and the test counts the dip only in the first full-price week once a deal ends. The dip can last longer.

  • Riley's belief. The test leaves it out, and last year's plan runs deals close together.

What the Stress Test Couldn't Weigh

You fund a calendar before any of its deals has run, and each deal shapes the ones after it. The test couldn't weigh Riley's belief, because no one had put a number on it. Of the three beliefs Larkfield's team did put a number on, only the lift changed which calendar came out ahead. No calendar stayed inside both of Tom's limits.

Next, the team asks how far off the lift can be before the choice changes.

Which number in your calendar would you most want to stress-test? I'd like to hear.

Words Behind Your Calendar Stress Test

Term

What it means

Baseline

What a product sells at full price, with no deal

Lift

What a deal adds on top of the baseline, as a share of it

Post-promotion dip

How far sales fall below the baseline after a deal, while shoppers use up what they stocked up on

Belief

What the team expects a number to be: a best guess and how far it can be off

Calendar stress test

Running each calendar through thousands of possible years, with each belief drawn from its range, and counting how often each calendar comes out ahead

Monte Carlo simulation

Trying a plan many times with numbers drawn at random from their ranges, and counting how often each result happens

Possible year

One try: a value for each belief, and what each calendar earns with them

Bootstrap

Redrawing your own records at random, with repeats, to see how much a number fitted to them could move

Swing (of a deal)

What a deal sold over what the line said it would

Typical year

For each calendar, the contribution that half its possible years beat and half fall short of

Middle 80% of years

The range left after dropping the best 10% and the worst 10% of possible years

Contribution

What the manufacturer keeps: sales to the retailer, minus trade spend and the cost of the boxes

Trade cap

The most trade spend finance approves for the year

Boxes agreed

The boxes the manufacturer and the retailer agree the year should at least match; here, last year's


Comments

Rated 0 out of 5 stars.
No ratings yet

Add a rating
bottom of page