Price Volume Profit Scenario Simulator

Purpose and model boundary

This calculator projects the profit of up to five products that share one production resource, such as oven hours, roaster capacity or liters of freezer space, over a plan of 1 to 12 months. You enter each product's prices, unit cost, monthly volumes and how its demand responds to price, and the capacity steps you pay for each month. The spreadsheet serves demand in the priority order you set until capacity runs out, charges each month the fixed cost of the capacity step it falls in, and reports the profit.

One Calculate fills every result section of that one model. It runs the plan as entered. It finds the best price: it tries evenly spaced prices for one product, keeping every other price fixed, and reports the best of them. When you enter a target horizon profit, it finds the target price of that product at which the horizon profit equals the target; with no target, that section stays empty. It runs the risk check: 100 seeded demand scenarios on the plan prices. An 11 by 11 what-if table of a price change for every product against the strength of the demand response is calculated with them.

Every figure on this page is computed by the spreadsheet; the page adds nothing to it. Demand is your assumption, not an estimate of how customers will respond: the calculator does not measure elasticity, forecast sales or guarantee a profit.

Inputs and units

• Months in the horizon, from 1 to 12, counted from month 1.
• Capacity unit, an optional label such as hours, liters or lb; blank means units. Nothing is converted.
• Products, up to 5 rows, filled from the top without a blank row between used rows, each with:
  • a product ID: it starts with a capital letter, uses upper case and no spaces, is at most 16 characters long and is used once; punctuation such as A-1 is accepted;
  • the reference price at which its volumes apply;
  • the plan price;
  • the unit variable cost;
  • the demand model (Fixed, Linear or Constant elasticity);
  • the price elasticity, from -10 to 0, required unless the model is Fixed;
  • the capacity used per unit, from 0 to 1,000 (0 means the product does not use the shared capacity);
  • its capacity priority, where the products in use take the priorities 1, 2, 3 and so on, each once.
• Reference monthly volumes: one row per month of the horizon and one column per product row, in units a month at the reference price. Every cell for a product in use inside the horizon is required; 0 is a valid volume and differs from blank. Cells after the horizon, or under an unused product row, must stay blank.
• Capacity steps, up to 5 rows: the monthly capacity each step covers up to, rising from row to row, and the fixed cost a month pays at that step. The last step's limit is the capacity of every month.
• Price search: the product to vary, chosen from the product IDs in the products table, and the lowest and highest price to try for it, from 0 to 1,000,000. The lowest must not be above the highest, and must be above 0 when that product has constant elasticity demand. Every other product keeps its plan price.
• Optional, under "Optional: target and risk":
  • the target horizon profit (blank means no target search);
  • the low, most likely and high monthly demand multipliers of the simulation, each from 0 to 5 (0.8, 1 and 1.2 unless you change them);
  • the random seed;
  • the number of candidate prices, from 2 to 101 (101 unless you change it);
  • a minimum unit margin (blank means none).
• Random seed: the starting number of the spreadsheet's random-number generator, a whole number from 1 to 2,147,483,646. Blank means 42, the seed of the worked examples. Each press of Calculate first sets it to the next seed of the last result, as on the Monte Carlo Retirement Success Calculator, except in two cases. The first press after a worked example or Reset uses that example's seed. A seed you type is used as typed. Through the API or MCP the seed you pass is used as given, so passing a result's seed again reproduces it.
• What-if table: a price change applied to every plan price, from -90% to 200%; a demand response scale that multiplies every elasticity, from 0 to 5; and the spacing of the table's rows and columns.

Money is in dollars; percentages are shares of a price.

Governing relationships

Demand. For a product with volume Q0 in a month at its reference price R0, the demand at price P is

• Fixed: D = Q0, whatever the price;
• Linear: D = max(0, Q0 × (1 + e × (P / R0 − 1)));
• Constant elasticity: D = Q0 × (P / R0)^e,

where e is the elasticity times the demand response scale. The plan price used everywhere is the entered plan price times (1 + the what-if price change). One product's price never changes another product's demand.

Capacity. Each month, products are served in priority order. A product gets its whole demand while capacity lasts; the product at which capacity runs out gets what is left, divided by its capacity use per unit, and the products after it get nothing. Unserved demand is lost, not carried to a later month. A product with capacity use 0 is always served in full. Production is never held back to stay in a cheaper step.

Capacity steps. Step 1 covers a month's capacity use from 0 up to and including its limit; each later step covers use above the previous limit up to its own. A use within 0.000001 capacity units of a limit counts as inside it. Each month pays exactly one step's fixed cost, including a month with no production.

Profit. For each product and month, revenue is price times units sold and variable cost is unit cost times units sold; each is posted to the cent, rounding half away from zero. Contribution is revenue minus variable cost; horizon profit is contribution minus the fixed costs of the months' steps. Totals are sums of the posted cents. Contribution margin is contribution divided by revenue and is not shown when revenue is 0.

Best price. The admissible range of the product to vary runs from the larger of the lowest price to try and its unit cost / (1 − minimum unit margin) up to the highest price to try. The range is split into the number of candidate prices you set, ends included, and the plan price is tried too when it lies in the range. Each candidate is evaluated over the whole horizon, including the capacity allocation and the steps, and the one with the highest profit to the cent wins; a tie goes to the price nearest the plan price, then to the lower price. The result is the best of those candidates, found by trying every one; a better price can lie between two candidates. When the best is at an end of the range a warning says so, because profit may keep improving beyond it; the status stays OK. With fixed demand profit only rises with price, so the highest price allowed always wins. When the margin floor is above the highest price, no price is admissible and the status is INFEASIBLE.

Target price. Only when you enter a target horizon profit. The search scans the candidate prices for places where profit minus the target changes sign, and narrows each of the first three such crossings by 40 halvings of its interval, far finer than a cent. A crossing whose profit ends within one cent of the target is reported as a target price. A crossing where profit jumps over the target, because a capacity step switches on or off, is reported as a jump with the profit on each side, never as a target price. When profit only rises or only falls across the scan, a located price is the only one in the range; otherwise two crossings closer together than one candidate step can be missed, and the page says so. When no price is located the status is NO SOLUTION FOUND, with the highest and lowest profit the scan saw.

Risk simulation. Each of 100 trials draws one demand multiplier per month from a triangular distribution with your low, most likely and high values; every product in a month shares that month's multiplier, and months are drawn independently. For a uniform draw u between 0 and 1, the multiplier is low + sqrt(u × (high − low) × (mode − low)) when u is below (mode − low) / (high − low), and high − sqrt((1 − u) × (high − low) × (high − mode)) otherwise. The draws come from the multiplicative congruential generator of Park and Miller (multiplier 48,271, modulus 2^31 − 1), seeded from your seed, the same generator the Monte Carlo Retirement Success Calculator uses, so the same seed and inputs always give the same 100 trials. Every trial keeps the plan prices; nothing is re-optimized inside a trial. Setting low, most likely and high equal removes the variation, and every trial then equals the plan with volumes times that multiplier.

What-if table. Rows are 11 price changes centred on yours, one row step apart; columns are 11 demand response scales centred on yours, one column step apart. Each cell is the horizon profit recalculated with every plan price changed by the row value and every elasticity multiplied by the column value, through the spreadsheet's own two-variable data table. The middle cell is your plan. When a step would take the lowest row's prices to zero or below, or the lowest column's scale below zero, the table is withheld with that reason and every other result still stands.

Calculation sequence

The spreadsheet first checks every product, volume and capacity step row and every visible setting, and stops with NOT VALID at the first problem it finds, with every figure at 0. It then sets the effective plan prices and elasticities, computes each month's demand, serves it in priority order, picks each month's capacity step and posts revenue, variable cost and fixed cost to the cent. The candidate prices of the product to vary are then evaluated and ranked for the best price and the price-profit chart; when a target is entered they are also scanned and the crossings narrowed; and the 100 trials run. The what-if table recalculates the plan at its 121 combinations. A block of consistency checks, including that posted revenue less posted costs equals posted profit, that no month uses more than its capacity, and that the table's middle cell equals the horizon profit, runs before the status is returned.

Outputs and interpretation

The headline gives the horizon profit, the best candidate price and the simulated mean horizon profit. The result sections follow.

• Plan: revenue, variable cost, contribution, fixed cost and contribution margin; units demanded, sold and lost; average and peak capacity utilization; the months that use more than the first step; and the results by product and by month.
• Best price: the profit at the best price and at the plan price, the uplift, whether the best is at an end of the range, the number of candidates evaluated and their spacing.
• Target price: up to three target prices to six decimals with their residuals, the number of crossings, whether profit moves one way with price, any jumps and the highest and lowest scanned profit. With no target entered this section is empty and says so.
• Risk: the median, 10th and 90th percentile, lowest and highest trial profit; the share of trials with a loss and the share reaching your target, each as a count out of 100 with its standard error; the share of trials losing demand and the mean units lost; the means of trials 1 to 50 and 51 to 100; and the seed used. With only 100 trials the percentiles are illustrative, and 0 of 100 trials with a loss does not mean the risk is zero.

For nearby questions, the Job Cost Markup Estimator prices one job from its costs, and the Landed Cost Calculator and Freight Cost Calculator build up the unit cost of goods you buy in.

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: a product row (a blank row above a used row, a missing or malformed ID, a repeated ID, a price, cost, elasticity or capacity use outside its limits, an unknown demand model, a reference price of zero for a responsive product, a plan price of zero for constant elasticity, or a missing elasticity for a responsive product); capacity priorities that are not 1 to the number of products, each once; no product; a volume that is blank inside the horizon for a product in use, or filled after the horizon or under an unused product row; a capacity step row (a gap, a missing value, or a limit that does not rise); no capacity step; and then a setting outside its limits: a product to vary that is not one of the product IDs in the table, a lowest price to try above the highest, a lowest price to try of zero when the product to vary has constant elasticity demand, a target that is not in whole cents, a seed outside its range, or demand multipliers not in the order low, most likely, high. NOT VALID is a refusal, not a numeric answer: every figure is withheld.
• INFEASIBLE means the minimum unit margin needs a price above the highest price to try, so no candidate price is admissible. The plan's own figures and the simulation stay valid and shown; the best-price and target outputs are withheld.
• NO SOLUTION FOUND happens only when a target is entered. It means the target search located no price whose profit equals the target, with the highest and lowest profit it scanned and any jump it found. This is not a proof that no such price exists between the candidates. The plan, the best price and the simulation stay valid and shown.
• CHECK accompanies a valid result that needs a look because a workbook consistency check failed.
• OK otherwise.

Warnings say when demand exceeds capacity in some months (with the units lost), when a product with capacity use 0 sits outside the capacity check, when the best price is at an end of the range, when a plan price is below half or above twice its reference price so the demand assumption is extrapolated, when the effective plan price of the product to vary lies outside the price range to try, when no product responds to price so the what-if columns are identical, when linear demand reaches zero at a plan price, when the demand multipliers are all equal, when profit crosses the target more than three times, and when the what-if table is withheld. A warning never changes the status.

Assumptions and limitations

• Capacities are 5 products, 12 months and 5 capacity steps; the price search tries at most 101 evenly spaced prices plus the plan price.
• Each product has one reference price, plan price and unit cost for the whole horizon; seasonality enters through the monthly volumes. Capacity is the same every month.
• Demand follows the model you choose, with the elasticity you enter. One product's price does not change another product's demand: substitution between products is not modelled.
• Quantities are expected values and may be fractional. Unserved demand is lost, never carried to a later month or to another product.
• Capacity is shared in a fixed priority order; the model does not search for the allocation with the highest contribution, and production is never held back to stay in a cheaper step.
• The best-price and target searches vary one product at a time and keep every other price fixed. The best candidate is the best of the prices tried; a better price can lie between two candidates.
• The simulation varies demand only, with one multiplier per month shared by every product and months drawn independently. Prices, costs and capacity are fixed in every trial, and 100 trials give rough percentiles: a share of 0 out of 100 does not mean the risk is zero.
• Money is in one currency and is not discounted; taxes, financing, inventory and payment timing are not modelled.
• Results exactly on, or within rounding noise of, a half-cent edge may differ by one cent, including a tie between prices, so the best price named can change.

Restrictions and non-computing states

The calculator enforces its numeric and choice limits before it accepts a result. A blank row between used product or capacity step rows, a repeated product ID, priorities that are not one each from the first, and volumes outside the horizon or under an unused product row are refused by the workbook. With no target entered the target outputs are left blank. When a table step would take the lowest row's prices to zero or below, or the lowest column's response scale below zero, only the what-if table is withheld, with that reason.

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 sales and cost data, before using it for a real pricing decision.

Sources and further reading

Your prices, costs, volumes, demand responses, capacity steps and fixed costs come from the inputs; the calculator holds no market, industry 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 Economics 3e, 5.1 Price Elasticity of Demand and Price Elasticity of Supply defines price elasticity of demand as the percentage change in quantity demanded divided by the percentage change in price, the quantity the calculator's elasticity column holds (retrieved 2026-11-02).
• 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 amount left to cover fixed costs and profit (retrieved 2026-11-02).
• 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 demand multipliers (DOI checked 2026-11-02).

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