Build your first trade promotion calendar and budget

Your first trade promotion calendar should answer three questions: what did we agree to do, what could it cost, and when will we know the final amount? Start with one retailer or distributor and the next 90 days. Expand after you can carry one event from proposal through settlement without losing its supporting documents.

This guide proposes a spreadsheet workflow for US consumer brands, including household and personal care. Use three linked tabs: Calendar, Terms, and Spend. Give every event a permanent promotion ID. Put that ID on each term line, forecast update, claim, and settlement reference so a renamed campaign remains traceable.

Trade allowance workflows can include billbacks, off-invoice allowances, and lump-sum payments for agreed performance. Microsoft’s documentation illustrates separate steps for budgeting, agreements, claims, and settlement. Those distinctions are useful when designing a manual tracker; its software procedures are not instructions for your retailer account. [1]

Adopt the following working definitions before anyone enters a dollar amount. They are commercial planning conventions for this worksheet, not GAAP recognition rules. Saving terms records an agreement; it does not post a journal entry, establish an accounting accrual, or prove that a customer earned an allowance.

Use a separate field for claims received but unresolved. Their expected valid cost belongs in the forecast without automatically becoming verified actual spend. Finance should determine the appropriate treatment of earned amounts, liabilities, receivables, and reporting periods in the accounting system. Reconcile the tracker’s definition to that system before comparing totals.

Create one Calendar row per event with promotion ID, account, ship-to scope, products, objective, owner, status, and a link to the current terms. Use Draft, Approved internally, Accepted by customer, Live, Reconciling, and Closed as your starting statuses. Internal approval should not stand in for evidence of customer acceptance.

Keep consumer event dates separate from the dates that make a transaction eligible. For example, Microsoft documents rebate calculations using ordered, delivered, or invoiced quantities and values, plus validity periods. Your agreement determines the applicable basis; a shelf event date alone cannot resolve it. [3]

Add actual customer submission deadlines from the agreement or current program instructions. Track expected deduction or payment timing separately, with an uncertainty note. A promotion’s February commercial cost can affect a later cash collection; do not force those dates into one month column.

Create separate Terms rows for the variable allowance and each fixed fee. A per-unit rate multiplies by eligible units. A lump sum is a fixed amount subject to its stated conditions; do not multiply a display fee by shipped cases. Record whether settlement is off-invoice, by later deduction, or by separate payment.

A shipping case groups individual products, and GS1 US distinguishes cases containing one product GTIN from mixed cases containing different GTINs. Keep the each and case identifiers with the verified contents rather than assuming that a case always holds the same number of consumer units. [2]

For the example, six bottles equal one case. Convert cases to bottles before applying the bottle rate. A personal-care gift set needs its own agreed counting rule. If a rate changes mid-event, save a new accepted version and its effective dates. Split eligible volume between versions; never overwrite the rate used to evaluate an earlier claim.

Consider a fictional surface-cleaner promotion, HOME-2702. The accepted terms reimburse $0.50 per eligible invoiced bottle and $750 for a completed display. Six bottles make a case. The illustration assumes no returns, other fees, tiers, taxes, or contractual cap. The original plan expects 600 eligible cases; the latest forecast expects 720.

The $2,550 plan does not limit the accepted rate. If volume reaches the higher scenario, the worksheet estimates $3,450. Flag the exposure before approving another event. Also check inventory and the commercial rationale: a larger trade budget does not by itself demonstrate incremental sales or a profitable promotion.

Roll event forecasts into the same annual budget scope. With a $12,000 annual allocation and $7,500 forecast across all other events, this event’s $2,910 forecast leaves $1,590 of planning headroom. Do not add settled spend again: each event forecast already includes it. Headroom is neither cash available nor permission to ignore accepted obligations.

Suppose valid billbacks for 2,400 bottles have settled and the completed display fee has been paid. Actual spend is $1,200 + $750 = $1,950. If total expected eligible volume remains 4,320 bottles, expected remaining spend is 1,920 × $0.50 = $960. The total forecast remains $2,910, not $4,860.

Keep an evidence link and unique settlement reference for each actual line. Where a deduction settles an allowance already represented by a credit memo, link the records as one cost. Keep potential duplicates visible as exceptions until resolved. Likewise, an off-invoice allowance should not become a second cost merely because it appears in a later report.

If final verified volume is 4,200 bottles and the display remains valid, final cost is $2,850: $300 above the original plan and $60 below the latest forecast. Record those two comparisons separately. Close only after reviewing remaining exposure, not simply because no new deduction arrived this month.