Sales promotion forecasting: baseline, lift and timing

Sales promotion forecasting adds an expected event lift to the demand you would have planned without the promotion. Keep baseline units, lift assumptions and discounted prices separate, then compare actual sales with each one. Observational comparisons estimate lift; they do not establish that the event caused every additional sale.

Start with a baseline you can explain

Use a supplied no-promotion baseline for each product, store, channel and event period. Document who approved it and which comparable trading periods informed it. Adjusting the baseline after seeing the result makes the event look more predictable than it was.

Keep units consistent. Cases cannot be added to individual packs without a documented conversion. Distinguish consumer purchases from shipments to a distributor: an advance shipment may raise the manufacturer's event sales while moving stock into a customer's warehouse.

Collect event dates, units sold, net selling prices, returns, availability and promotion terms. Keep store openings, closures and distribution changes visible. Missing sales during a stockout are not observed demand; the separate lost-sales forecasting review covers that uncertainty.

Calculate historical lift on matched rows

A US packaged-food brand reviews an earlier promotion for the same snack pack across three comparable groups. Each row covers the same event dates within its group. The baseline was supplied before the event; actual units are net of recorded returns.

Observed additional units = Actual event units - Baseline event units

Observed lift rate = Observed additional units / Baseline event units

Put the table in columns A-E, with data in rows 2-4:

In D2 and E2, enter these formulas and fill down:

=C2-B2
=IF(B2=0,"",D2/B2)

The total lift is =SUM(D2:D4)/SUM(B2:B4): 920 / 3,000 = 30.7%, rounded to one decimal place. Do not average the group percentages, because their baseline volumes differ.

If the detailed actuals sheet has group in column A, event ID in B and units in C, this aggregates one group's event units:

=SUMIFS(Actuals!$C$2:$C$10000,Actuals!$A$2:$A$10000,A2,Actuals!$B$2:$B$10000,$G$1)

Put the chosen event ID in G1. Microsoft documents SUMIFS as summing amounts that meet multiple criteria. Keep every selected row inside the agreed event period and preserve the detail behind the total.

Build the next event forecast

For a later event, the team approves the same group lift rates as planning assumptions. That requires comparable price, distribution, display and availability conditions; it is not a promise that the earlier result will repeat. The new baseline mix differs, so applying one company-wide lift rate would obscure the group assumptions.

Use a separate planning sheet with columns A-E as shown:

Enter =B2*C2 in D2 and =B2+D2 in E2, then fill down. The identity is 3,100 baseline units plus 970 planned additional units equals 4,070 event units. All unit figures here are exact; retain unrounded calculations when real assumptions produce fractional units, then document the final handling of pack quantities.

Compare that plan with available stock and event dates before approval. A sales forecast describes expected demand under stated conditions; it is not evidence that every planned store can fulfill it.

Check selling price and gross profit

Assume the pack's regular net price is $5.00, the event net price is $4.50 and unit product cost is $3.00. Applying those inputs to the new forecast gives:

Revenue equals units times net price; gross profit equals revenue minus product cost. The event adds units and revenue but reduces gross profit by $95 before separate event spending. Reconcile discounts and allowances into the net price once, and use a consistent gross profit definition.

Look beyond the event window

For the historical event, the following period had baseline units of 1,000 and actual units of 850:

The broader window is 770 units above baseline, smaller than the event's 920. That is consistent with some demand arriving earlier, but it does not prove the explanation. NIST distinguishes association from causality: another factor can produce an observed relationship. Record availability and competing activity before drawing conclusions.

Include the event and following-period comparisons in the sales forecasting report, alongside the baseline version and assumption owner.

Where it goes wrong

• Baseline contamination. Using earlier promotional weeks as ordinary demand inflates the starting point.
• Overlapping events. Counting one sale in multiple promotion IDs overstates event volume.
• Availability limits. A stockout suppresses observed sales and makes historical lift a poor guide to unconstrained demand.
• Discount double counting. Subtracting an allowance already included in net price understates revenue a second time.

Covirage is not affiliated with Microsoft.

Review your promotion files in Covirage

Bring a sample of your own event calendar, baseline, promotion plan and actual sales file. Covirage maps the columns, its tools compute and check the comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account; account creation takes about a minute, then you set up your business and bring your files.

See the supporting approach for CPG brands.