Promotion & Discount Profitability Calculator

Purpose and model boundary

This calculator asks whether a promotion earns more than selling with no promotion. You enter up to 5 products over 1 to 6 periods, the units each product would sell with no promotion, and up to 3 promotions to compare on that same baseline and the same stock. A promotion has a campaign cost, a window of periods and at most one offer per product: a percent off, a fixed amount off, or a multibuy. Each offer has a gross lift, and you can say how products affect each other, how many extra units are bought early, how much stock you have and how much comes back as returns.

One Calculate fills every result section of that one model: the plan of the promotion you study with its profit bridge, the comparison of every promotion, the lift search, 100 simulated lifts and an 11 by 11 what-if table of incremental profit by offer depth and lift scale.

Lift is your assumption here. It does not respond to the depth of the discount, so the calculator never looks for a best discount: the deepest cut would never win and the shallowest always would. The what-if table shows that trade-off, and you judge how far along it you would land. Every figure on this page is computed by the spreadsheet; the page adds nothing to it. Every value is your own assumption.

Inputs and units

• Periods in the plan, from 1 to 6, such as weeks or months.
• Promotion to study, chosen from the Promotion IDs you typed. The plan, the bridge, the lift search, the charts and the what-if table use this promotion. The comparison shows every promotion.
• Products, up to 5 rows, filled from the top without a blank row between used rows: a product ID, which is a capital letter and then up to 15 capital letters or digits, each ID used once; a list price and a unit cost, each from 0 to 100,000; and, if you need them, opening stock from 0 to 1,000,000,000 (blank means no stock limit), the share of units returned from 0% to 100% (blank means none), the handling cost and the value recovered per returned unit, each from 0 to 100,000 (blank means 0). The value recovered is at most the unit cost.
• Baseline units, one row for each period and one column for each product row: the units that product would sell in that period with no promotion, from 0 to 100,000. Fill exactly the periods in the plan for the used products. A blank there is not 0, and every other cell must stay blank. Quantities are expected values, so fractions are allowed.
• Promotions, up to 3 rows: a promotion ID in the same form, a campaign cost from 0 to 1,000,000,000, and the first and last period of its window, which must lie inside the plan.
• Offer lines, up to 15 rows: a promotion, a product, the offer, then only the terms that offer needs, the gross lift from 0% to 500% and, if you need it, the share of extra units bought early from 0% to 100% (blank means 0). A product has at most one offer in a promotion, because offers do not stack. This grid has a Paste rows button.
  • Percent: the percent off, from 0% to 100%.
  • Fixed: the amount off each unit, from 0 to 100,000.
  • Multibuy: the price of one group, the units in a group from 2 to 1,000, and what happens to units left over: Full price, or Pro rata, where every unit pays the group price divided by the group size. The group price is at most the list price of the group.
• Product relationships, up to 10 rows, which may be empty: a promotion, the promoted product, the affected product, Cannibalization or Halo, and the units per extra unit, from 0 to 1. The promoted product must have an offer in that promotion. In one promotion, the cannibalization from one product adds up to at most 1, and relationships run one way only.
• What-if table: the offer depth, which multiplies every discount, and the lift scale, which multiplies every gross lift, each from 0 to 2 (1 is what you entered); and the spacing of the table's rows, above 0 up to 0.2, and columns, above 0 up to 0.4.
• Optional, under "Optional: target, budget, search range and risk range":
  • the target incremental profit, in whole cents, from −1,000,000,000 to 1,000,000,000 (blank means break-even, a target of 0);
  • the campaign budget, from 0 to 1,000,000,000 (blank means no limit);
  • the highest lift to search, above 0% up to 500%. It is required and comes filled in with 500%;
  • the lift variation, low, most likely and high, each from 0 to 2 and in that order. These three are required and come filled in with 0.5, 1 and 1.5. Set all three to 1 to switch this variation off;
  • the random seed, from 1 to 2,147,483,646 (blank means 42).

Money is in dollars. Percentages are shares of a price or of units.

Governing relationships

Offers. A percent or fixed offer takes the same amount off every unit in the window, and the price never goes below 0. A multibuy with leftovers at full price takes the saving off each complete group, and units outside a complete group pay list price. Groups are counted per period on expected units, so 7 units with a group of 3 make 2 groups. A multibuy pro rata takes the same share off every unit. At an offer depth of 1 a multibuy group pays the price you entered; a different depth scales the saving against the list price of the group.

Demand in the window. Demand is the baseline units, plus the lift, plus halo units, less cannibalized units, and it stops at 0. Each extra unit a promoted product sells passes its relationships on: a cannibalization coefficient of 0.4 means each extra unit replaces 0.4 of a sale of the affected product, and a halo coefficient of 0.5 means each extra unit brings 0.5 of a sale. Products are worked in the order the relationships run, so chains count each unit once. A product that sells below its baseline passes nothing on.

Sales bought early. The share you enter of a product's extra units in the window comes off the demand of the following periods, up to that demand, until the share is used up. Those units still count as extra units for the relationships. Whatever falls after the last period is not charged, and a warning says so, because profit may then be overstated.

Stock. Opening stock is one pool for the whole plan, for the product's baseline and for each promotion. Sales fill demand period by period until the stock left runs out, and the rest is lost.

Returns. Returned units are refunded at the price paid, cost the handling amount, recover the value you enter, and do not go back into stock.

Money. For each product and period, gross sales, discount, refund, product cost, handling and recovery are each rounded once to the cent, after rounding to 8 decimals so float noise cannot move a half cent. Contribution is gross sales less discount, refund, product cost and handling, plus recovery. Incremental profit is the promotion's contribution, less the baseline contribution, less the campaign cost.

Profit bridge. The effect lines value each change in units at the list margin, price less unit cost, and add up to the promotion's result together with the discount cost, the change in refunds and return costs, and the campaign cost. A Rounding line holds whatever makes the rounded lines reconcile, at most 1.00.

Best promotion. A promotion whose campaign cost is above the budget cannot be chosen. Among the rest, the highest incremental profit is best; a tie goes to the lower campaign cost, then the earlier row. A truly lower campaign cost wins the tie even when the costs differ by under a cent. A best promotion can still lose money, and a warning then says that running none earns more.

Lift search. The search sets one lift on every offer line of the promotion you study, tries 51 lifts from 0% to the highest lift to search, and looks for where profit crosses the target. It locates the first 3 crossings with 40 halving steps, keeps a lift only when profit there is within 0.01 of the target, and counts every crossing. It does not use your lift scale. When profit moves one way with lift and the promotion has no group discount, the required lift is the only one on the range. When "Profit moves one way with lift" reads FALSE, profit does not rise smoothly with lift here: other lifts that reach the target can sit between the scanned points, and the lowest can be up to one scan step lower. A multibuy always reads FALSE, because each completed group drops profit by a jump far narrower than one scan step. A jump across the target is reported as a jump, never as a required lift.

Simulation. Each of the 100 trials draws one lift multiplier from your low, most likely and high (a triangular distribution). That one number scales every lift of every promotion, so promotions are compared on the same draws. Offers and windows stay as entered. A trial computes incremental profit exactly at that multiplier and is not rounded line by line, so a trial with no variation can differ from the plan by a few cents. The same seed and inputs give the same 100 trials, and each Calculate moves on to the next seed.

What-if table. Rows are 11 offer depths centred on yours, one row step apart; columns are 11 lift scales centred on yours, one column step apart. Each cell is the incremental profit of the promotion you study, recalculated by an Excel Data Table. The middle cell is the plan itself. It covers the plan only, not the search or the simulation.

Calculation sequence

The spreadsheet first checks the periods, then every product, baseline cell, promotion, offer line and relationship, then the other settings, and stops with NOT VALID at the first problem it finds, with every figure at 0. It then works out the baseline, runs each promotion period by period through its products, posts the money to the cent and builds the bridge. It then compares the promotions, runs the lift search and the simulation, and fills the what-if table.

Outputs and interpretation

The headline gives the incremental profit of the promotion you study, the best promotion and the required lift. The result sections follow.

• Plan of the promotion to study: the baseline contribution, gross sales, discount cost, refunds, net revenue, product cost, return costs, the promotion's contribution and margin, the campaign cost and the profit after the campaign; the units sold and where they came from, and the units lost and returned; with the profit bridge for every promotion, the results by product, including the price paid, and by period.
• Comparison of the promotions: the best promotion's incremental profit, how much more it earns than the one you study, how many promotions fit the budget, and every promotion side by side. A promotion over the budget still shows, with "Can be chosen" reading No.
• Lift search: the target used, any further required lifts and how far each stays from the target, how many lifts were found and how many crossings the scan saw, whether profit moves one way with lift, any jumps, the highest and lowest profit on the scan, and whether stock runs out at the top; with the 51 lifts as a table.
• Risk: 100 simulated lifts: the mean, median, 10th and 90th percentile, lowest and highest incremental profit; the share of trials with a profit and the share reaching your target, each with its count and standard error; the means of the first and last 50 trials, whose gap shows how far 100 trials can wander; the seed; and the same figures for every promotion, with how often each one was best. Each share is a count out of 100, so 81% means 81 of 100 trials. 0 of 100 does not mean zero risk.

Percentiles from 100 trials are illustrative only.

For nearby questions, the Price Volume Profit Scenario Simulator projects profit over months when demand responds to price, and the Quote Pricing & Deal Margin Optimizer prices one deal.

Validation and status logic

The workbook returns one status, chosen in this order: NOT VALID, then INFEASIBLE, then NO SOLUTION FOUND, then CHECK, then OK.

• NOT VALID names the first problem: the periods; a product (a blank row above a used row, a missing, malformed or repeated ID, a value outside its limits, or a value recovered above the unit cost); no product; a baseline cell (blank inside the plan for a used product, filled anywhere else, or outside its limits); a promotion (a missing, malformed or repeated ID, a campaign cost outside its limits, or a window that is out of order or beyond the plan); no promotion; an offer line (a promotion or product not in its table, a repeated pair, a term the offer needs left blank, a value outside its limits, or a group price above the list price of the group); a promotion with no offer lines; a relationship (a name not in its table, a promoted product with no offer in that promotion, the same product both ways, a coefficient outside its limits, or a repeated row); cannibalization from one product adding up to more than 1; relationships that form a loop; or a setting outside its limits. Every figure is then 0.
• INFEASIBLE means every promotion costs more than the campaign budget. The plan, the lift search and the simulation still stand, and no promotion is named best.
• NO SOLUTION FOUND means the search found no lift that reaches the target on the range. The status says how far the scan ran and whether stock runs out. The plan, the comparison, the simulation and the table still stand, and the required lift stays blank.
• CHECK accompanies a valid result that needs a look because a workbook consistency check failed.
• OK otherwise.

Warnings say when stock runs out, with or without the promotion; when another promotion earns more; when every promotion that can be chosen loses money; when the promotion you study is over the budget; when an offer takes a price to zero; when a multibuy product sells fewer units than one full group in a period; when units bought early fall after the last period; when the search finds more crossings than it locates, or a jump across the target; when the what-if table is withheld; when lift variation is switched off; when promotions tie; and when cannibalization would push demand below zero.

Assumptions and limitations

• Capacities are 5 products, 6 periods, 3 promotions, 15 offer lines and 10 relationships.
• Above about $10 trillion of revenue plus cost, the promotion and its baseline together, the calculator refuses, because whole cents stop being exact there. When only some what-if scenarios pass that limit, only the table is withheld. Within a fraction of a cent of the limit, a plan can be refused just below it.
• Results exactly on, or within rounding noise of, a half-cent edge may differ by one cent, including a tie between promotions, so the best promotion named can change.
• Amounts within two cents below the $10 trillion limit may show as $10 trillion.
• Above $10 billion, a simulated mean can be off by less than one cent.
• A true margin far under a cent can read as 0, and the status can follow it. Three products at a price and unit cost of 77,165.93, 100,000 baseline units each, a 100% lift and search limit, and a campaign cost of 0.0004, about $139 billion of revenue plus cost, read OK with a required lift, where the exact answer is a loss of 0.0004 at every lift and no solution found. The money reads 0.00 both ways.
• With a margin far under a cent, a required lift can stop early. A price of 77,165.93, a unit cost of 77,165.929999999, 20,153 baseline units, a search limit of 500% and a campaign cost of 0.00005147538 read a required lift of 10.00%, where the exact answer is 255.42%. It needs a cost entered to 6 or more decimals; costs in whole cents did not move the figure by more than 0.00004 of a point.
• Lift does not respond to offer depth, and a discount does not change how customers respond to anything else.
• One offer per product in a promotion, and promotions are alternatives: they are compared separately and never run together.
• Multibuy groups are counted per period on expected units, not by customer basket.
• Each product has one opening stock for the plan, with no receipts during it.
• Returned units are not resold.
• The search sets the same lift on every offer line of the promotion.
• Each trial uses one lift multiplier for every line, so a run does not model one offer doing well while another does badly.

Restrictions and non-computing states

The calculator enforces its numeric and choice limits before it accepts a result. A blank row between used rows, a repeated ID, an offer that repeats a product in a promotion, cannibalization above 1 and a loop of relationships are refused by the workbook. With no target the search looks for break-even, and the share of trials reaching a target stays blank. When a table step would take the lowest row or column below 0, only the what-if table is withheld, with the reason; a depth or lift scale of 0 always withholds it.

Errors and warnings

A request rejected by the page's input limits is not a computed result. A transport or service failure, a timeout or a cancelled run cannot establish a fresh workbook answer, and the last result stays on the page marked as out of date. Review the workbook's References and Disclaimer, and test a result against your own data before using it to plan a promotion.

Sources and further reading

Your prices, costs, baseline units, offers, lifts, relationships, stock and returns come from the inputs; the calculator holds no market, retail or customer data, and every value is your own assumption. The sources below explain the ideas the model uses. They do not endorse this calculator.

• OpenStax, Principles of Accounting, Volume 2: Managerial Accounting, 3.1 Explain Contribution Margin defines contribution margin as the amount by which a selling price exceeds variable cost per unit; the calculator's contribution is net revenue less product cost and return costs (retrieved 2026-11-04).
• OpenStax, Principles of Accounting, Volume 2: Managerial Accounting, 3.2 Calculate a Break-Even Point in Units and Dollars explains the break-even point as the level at which costs are recovered; the calculator's break-even lift is the lift at which the promotion earns the same as selling with no promotion, once the discount and the campaign are paid for (retrieved 2026-11-03).
• Microsoft Support, Calculate multiple results by using a data table describes the two-variable data table the spreadsheet uses for the 11 by 11 what-if table (retrieved 2026-11-02).
• Park, S. K. and Miller, K. W. (1988), Random number generators: good ones are hard to find, Communications of the ACM 31(10) describes the multiplicative congruential generator with modulus 2^31 − 1 that draws the simulation's lift multipliers (DOI checked 2026-11-02).

This is an educational model of the plan you enter, not financial, accounting, pricing or legal advice. It does not estimate how customers respond to a discount, it claims no best discount, it is not certified financial reporting, and it does not guarantee a profit; test a result against your own data before acting on it.