top of page

Can an Optimizer Help Your Forecast Choose the Deals?

Writer: Gandhinath Swaminathan
Gandhinath Swaminathan
1 hour ago
10 min read

And How to Tune the Optimizer With Your Team's Expertise

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 your demand forecast can tell you which deals to run.

Cartoon: Riley, standing, says "We still haven't checked a single lift." Nina sits at her laptop, and the year's calendar on the wall shows 10% and 15% deals.

Your Forecast Has the Volume, and You Still Choose the Deals

Your forecast can tell you how many boxes a quarter's plan will sell. It can't tell you which deals to put in it. Do you run 30% off with an end-of-aisle display and a spot in the retailer's mailer? 10% off on the shelf tag alone? Or no deal at all? In the last post, the forecast's own models split on what a deal adds.

This post follows the team at Larkfield Foods, which makes Sunny Oats cereal, and the cereal buyer at Kestrel Market, the retailer. Seven 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

Maya Okafor

Sunny Oats brand manager at Larkfield

This post

At the yearly joint business planning meeting, Grace Lin, Kestrel's cereal buyer, asked Larkfield Foods for the year's promotion calendar for Sunny Oats Toasted O's 12 oz. Kestrel wanted the box on deal at least once a quarter, and both sides agreed to plan for last year's volume. Tom Becker in finance capped the year's trade spend at last year's level. Maya Okafor, the Sunny Oats brand manager, held the number of deals to the most the brand had run in a year. Riley Shaw, who heads revenue growth, brought last year's calendar, copied as it stood. Most of its deals ran 25–30% off.

Nina Alvarez, the revenue analyst, suggested building the calendar the way trade promotion optimization tools do. It would start from the demand forecast of Hannah Weiss, who runs demand planning.

Trade Promotion Optimization Tools Predict, Price and Search

Planning software vendors and some retail data firms sell trade promotion optimization (TPO) tools. Most do three jobs:

  1. Predict what each deal sells. A forecast gives normal sales. A lift model adds what each part of a deal does on top: the price cut, the display and the ad. Vendors usually fit it on past deals in your scan data. Many tools also model how lift fades when deals run close together.

  2. Price each deal. Your trade terms and costs turn each deal into dollars. They give the funding per box, the display and ad fees, and what's left after the cost of the boxes.

  3. Search the calendar. An optimizer searches the possible calendars for the one that does best on your goal, usually profit. It stays inside the limits you set: the trade budget, the retailer's required events, and its ad and display space.

The planner sets the limits, checks what goes in and runs what-if cases, such as dropping the mailer for a quarter. The tool knows only the data, terms and limits the team gives it.

Each Deal Can Combine a Price Cut, a Display and the Mailer

Larkfield builds a deal at Kestrel from up to three parts:

  • A price cut. The shelf price drops 10, 15, 25 or 30% while the deal runs. Larkfield pays Kestrel the whole cut on every box sold: at 10% off the $3.81 shelf price, $0.38 a box.

  • A display. Kestrel's store teams stack the boxes on an end-of-aisle stand, called an end cap. Larkfield pays Kestrel a fee for each store that builds one, $107 a deal on average last year.

  • The mailer. Kestrel's ad, printed and online, reaches every household in a zone. Kestrel makes it and chooses which items go in. Larkfield pays an ad fee to be in it, $90 a deal last year.

Four price cuts, each with or without the mailer and a display, make 16 deals. The team numbered them:

Price cut

Price cut only

With the mailer

With a display

With a display and the mailer

10% off

Deal 1

Deal 2

Deal 3

Deal 4

15% off

Deal 5

Deal 6

Deal 7

Deal 8

25% off

Deal 9

Deal 10

Deal 11

Deal 12

30% off

Deal 13

Deal 14

Deal 15

Deal 16

The deal with the best post-event report last year, 30% off with a display and the mailer, is Deal 16.

An Optimizer Needs Inputs From Your Whole Team

Hannah's models had split on what deals add, so Nina took the lifts from last year's post-event reports, which compare each deal's sales with the sales before it.

A diagram. Four inputs on the left, each with who brought it: normal sales, from Hannah's forecast; what each deal adds, from Nina's line through the reports; sales right after a deal, from Priya's read of Kestrel's shoppers; funding and fees, from Tom's trade terms. Arrows run from each into the optimizer, which searches the calendars. Below it, the limits feed in too: Tom's cap, Grace's volume and Kestrel's rules. An arrow runs from the optimizer to the year's calendar that earns the most on the team's numbers.
  • Hannah's forecast gives normal sales across the year.

  • Nina's line, drawn through the lifts in the reports, says a deeper cut adds more and a display adds more than the mailer. The reports scatter widely around it.

  • Priya Raman, the key account manager who plans Larkfield's deals with Kestrel, expects sales right after a deal to run at about 95% of normal. She took Kestrel's limits from this box's past deals.

  • Tom brought the funding and fees above.

  • Maya brought the limit on deals in a year.

Set Up Your Calendar as an Optimization Problem

An optimization problem has three parts. The decision variables are what you choose. The objective is what you want the most of. The constraints are the limits every answer must keep.

Each deal at Kestrel runs for one week, in a promotion slot that Kestrel sets well ahead so it can print the mailer and build displays. Kestrel had already set the year's first slots, one of them with Deal 16. The optimizer chooses the rest, and every limit counts the slots already set.

In the optimizer

Larkfield's calendar

Why it's there

Decision variables

For each open slot: full price, or one of Deals 1 to 16

The choice Riley makes for each slot

Objective

The most contribution over the year: sales to Kestrel after trade, minus the cost of the boxes

What Larkfield keeps, for this box alone; the optimizer doesn't count sales this box takes from Larkfield's other boxes

Constraint: trade

Planned spend of at most $37,483

Tom held trade at last year's spend

Constraint: volume

Planned boxes of at least 70,886

Last year's volume, agreed with Grace; her category plan and Larkfield's supply plan both start from it

Constraint: number of deals

At most 15 deals in the year

The most the brand has run in a year, Maya's limit

Constraint: required events

At least one deal a quarter

Kestrel wants the box on deal every quarter

Constraint: spacing

Deals no closer together than Kestrel has run them on this box

Kestrel's own past calendar

Constraint: space

No more mailers or displays in a year than Kestrel has given this box, and the mailer only at depths Kestrel has featured

Every brand competes for Kestrel's ad and display space

For each open slot, the optimizer has a yes-or-no choice for full price and for each deal, with exactly one yes. Choices that can only be yes or no make this an integer program. A solver searches the possible calendars and proves which one earns the most inside the constraints. Nina wrote the program in Python with Google's OR-Tools, which is free.

Before solving, Nina checked what each deal does in a typical slot on these numbers:

Deal

Lift the line predicts

Break-even lift

Spend

Deal 1: 10% off

53%

49%

$600

Deal 5: 15% off

87%

89%

$1,110

Deal 9: 25% off

156%

305%

$2,530

Deal 13: 30% off

191%

850%

$3,450

Deal 4: 10% off, display and mailer

105%

70%

$1,010

Deal 8: 15% off, display and mailer

140%

115%

$1,620

Deal 12: 25% off, display and mailer

209%

362%

$3,250

Deal 16: 30% off, display and mailer

244%

984%

$4,270

Eight of the 16 deals, each in a typical slot (Simulated market).

The break-even lift is what a deal needs to earn as much as full price would in its slot. Larkfield funds the cut on every box, so the deep deals fall far short. Deals 1 and 5 come within a few points of theirs, too close for the reports to tell apart. On the line, Deals 4 and 8 clear theirs.

The Optimizer Filled the Calendar With Shallow Deals

Two rows of squares, one square for each slot of the year at Kestrel Market, split into four quarters, with the first slots shaded as already set. The shading shows the discount, darker for deeper; an empty square runs at full price. The top row, last year's plan copied, has 15 deals, 11 of them 25 to 30% off. The bottom row, the optimizer's calendar, has 15 deals: the 30% deal already set, 10 at 10% off and 4 at 15% off. A note gives the optimizer's plan: $17,800 of the $37,483 cap, and 70,886 boxes against 70,886 agreed.
Simulated market.

The solver used all 14 deals that Maya's limit left open: 10 at 10% off and 4 at 15% off. Of the 10% deals, nine were Deal 4, 10% off with a display and the mailer, and one was Deal 2, with the mailer alone. With Deal 16 already set, that takes every mailer and display Kestrel allows in a year. To reach the boxes agreed, the solver added four Deal 5s, plain 15% off. Deal 5 sells more boxes than 10% off on the shelf tag, and it earns about what full price would in its slot.

The calendar plans exactly the 70,886 boxes agreed, with none to spare, and leaves about $19,700 of Tom's cap unspent. On these lifts, the deep deals earn less than full price would in their slots, and Maya's limit and Kestrel's space rule out any more shallow deals. A bigger cap wouldn't change this calendar.

On the team's numbers

Last year's plan, copied

The optimizer's calendar

With Priya's spacing

Deals in the year

15

15

12

25–30% off

11

1, Deal 16, already set

1, Deal 16, already set

10 or 15% off

4

14

11

Planned trade spend

$42,800

$17,800

$19,200

Planned boxes

82,200

70,886

71,100

Simulated market.

On these numbers, last year's plan, copied, would spend more than it did last year and run over Tom's cap, mostly because Hannah's normal sales run high.

What Your Team Knows That the Optimizer Doesn't

Before Grace saw the calendar, the team checked it:

  1. Nina: no report covers this calendar's main deal. No report on file covers Deal 4, the deal this calendar uses most. The line puts Deal 4 above every 10% deal on file.

  2. Hannah: normal sales run high. Her forecast puts normal sales at about 1,040 boxes a week; last year's full-price sales ran about 900. "I can't tell yet whether the year or my baseline is off," she said. So the calendar reaches the boxes agreed only if her normal sales hold. Under this calendar, her models also forecast next quarter at about 15,400 to 18,300 boxes, against about 18,400 on the optimizer's numbers. The whole gap is in what the deals add.

  3. Priya: Grace gives her ad and end caps to 10% off only now and then. Three of the nine 10% deals on file had the mailer and one had a display. This calendar puts all ten 10% deals in the ad and nine on an end cap, and Priya would have to agree on that with Grace.

"Back to back, the second deal rarely sells like the first," Priya said.

Nina added Priya's rule, a stretch at full price between deals, and solved again. With fewer deals, the solver needed more boxes from each, so it went to 15% off. Seven of the 12 deals were Deal 8, with a display and the mailer. Deal 8 had run only once, and it had sold below what the line said. This calendar planned more trade than the first and, on the team's numbers, earned a little less.

"Both calendars leave about half my cap unspent," Tom said. "I'll hold the rest back as a reserve."

"Grace needs one calendar," Riley said. "We still haven't checked a single lift."

What the Optimizer Couldn't Check

You promise Kestrel a year of deals before the first one runs, and each deal locks before you see what the last one did. The optimizer chose the deals your forecast couldn't, inside your team's limits, from lifts no one at the table could check. Its calendar makes the boxes agreed only if every lift holds.

Next, the team tests both calendars against its own doubts about each number.

What limits does your team give its promotion optimizer? I'd like to hear.

Words Behind Your Promotion Calendar

Term

What it means

Promotion calendar

When each product runs a deal at a retailer, and which deal

Depth (depth of discount)

How far a deal cuts the price, as % off the regular price: 10%, 15%, 25% or 30% off here

Joint business plan

The yearly plan a manufacturer and a retailer agree on, including the promotion calendar and the volume

Required event

A deal the retailer asks for in a set period, here at least one a quarter

Funding

What the manufacturer pays the retailer for the price cut, here the whole cut on every box sold

Display

Extra space for the deal, such as an end-of-aisle stand (end cap); the brand pays the retailer a display fee

Mailer (feature)

The retailer's ad, printed and online; the retailer picks the items and the brand pays an ad fee to be in it

Trade cap

The most trade spend finance approves for the year

Planned spend

A deal's funding at the boxes you expect it to sell, plus display and ad fees

Planned boxes

The boxes a calendar should sell on the team's numbers, after the dip right after each deal

Trade promotion optimization (TPO)

Software that predicts what each deal sells, prices it with your trade terms and searches for the calendar that does best inside your limits

Optimization problem

Decision variables, an objective and constraints; a solver finds the choices that do best on the objective

Decision variable

A choice the optimizer makes, here yes or no to each deal in each open slot

Objective

The number the optimizer makes as large as it can, here the year's contribution

Constraint

A limit every answer must keep, such as the trade cap

Integer program

An optimization problem whose choices are whole numbers, here yes or no

Solver

The engine that searches the choices and proves the best one, such as SCIP, run here through Google's OR-Tools

Lift

How far sales rise above normal while a deal runs, as a share of normal

Break-even lift

The lift a deal needs to earn as much as full price would in its slot

Contribution

Sales to the retailer after trade, minus the cost of the boxes


Comments

Rated 0 out of 5 stars.
No ratings yet

Add a rating
bottom of page