Quote Pricing & Deal Margin Optimizer

Purpose and model boundary

This calculator prices one quote, or deal, of up to 20 lines. Each line is goods, labor, a one-time fee or a monthly service, with a quantity, a list price, a planned discount and the lowest and highest discount you allow on it. You enter the cost of each line, the hourly cost of your labor roles, the sales commission plan and when the customer pays and when you pay your costs. The spreadsheet works out the net revenue, the direct cost, the commission, the contribution and margin of the deal, and dates its cash by month.

One Calculate fills every result section of that one model. It prices the plan as entered and dates its cash. When you enter a lowest margin or a lowest net revenue, it finds the largest discount one common rate can give before either limit breaks; with neither, that section stays empty. When you enter a target margin, it finds the price change on every list price that reaches it; with no target, that section stays empty. An 11 by 11 what-if table of the labor hours against a change in every discount is calculated with them.

Every figure on this page is computed by the spreadsheet; the page adds nothing to it. Prices, costs, discounts, the commission plan and the payment schedules are your own inputs: the calculator does not say whether a customer will accept a price, it is not certified financial reporting, and it does not guarantee a profit.

Inputs and units

• Deal terms:
  • what the commission is paid on: net revenue, or gross profit (net revenue minus direct cost, before commission and other deal costs);
  • the other deal cost, such as bid preparation or travel, in whole cents from 0 to 1,000,000,000 (blank means 0), paid at signing;
  • the yearly discount rate for the present value of the cash, from 0% to 100% (blank means 0%).
• Quote lines, up to 20 rows, filled from the top without a blank row between used rows. Rows can be typed or pasted from a spreadsheet. Each line has:
  • a line ID: a capital letter, then up to 15 capital letters or digits, each ID used once;
  • a line type: Goods, Labor, Fee (one time, treated as goods) or Recurring (a monthly service);
  • the quantity, from 0 to 1,000,000: billed hours or days for a labor line, units a month for a recurring line;
  • the list price per unit, from 0 to 1,000,000 (per unit a month for a recurring line);
  • the planned discount and the lowest and highest discount allowed, each from 0% to 100%, with lowest at or below planned at or below highest. A blank planned or lowest discount means 0%; a blank highest discount means the planned discount, so there is no extra room;
  • the direct cost per unit, required on every line except a labor line, and blank on a labor line;
  • for a labor line only, its labor role and the hours per unit, from 0 to 1,000;
  • for a recurring line only, the months billed, from 1 to 12, and the first month billed, from 0 to 12 (blank means 1).
• Labor roles, up to 5 rows: a role ID in the same form as a line ID, used once, and its cost per hour from 0 to 100,000. Every role a labor line names must be in this table; it may be empty when no line is labor.
• Commission tiers, up to 5 rows: where each tier starts, the first at 0 and each next one higher, and the rate on that tier, from 0% to 100%. An empty table means no commission.
• Customer payments and cost payments, up to 5 rows each: the month, from 0 (signing) to 24, rising from row to row, and the share paid in it. Each schedule must add up to 100%. A quote with a goods, labor or fee line needs both schedules; a quote of monthly service lines only may leave them empty.
• What-if table: a labor hours multiplier, from 0 to 5 (1 unless you change it), applied to every labor line before anything is calculated; a discount change, from -100 to +100 percentage points (0 unless you change it), added to every planned discount and then held inside each line's lowest and highest discount; and the spacing of the table's rows and columns.
• Optional, under "Optional: discount limits and target":
  • the lowest margin allowed, from 0% to 95% (blank means no floor);
  • the lowest net revenue allowed, in whole cents (blank means no minimum);
  • the target margin, from -100% to 95% (blank means no price search);
  • the lowest and highest price multiplier to try, from 0.1 to 10 (0.5 and 2 unless you change them), the lowest below the highest.

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

Governing relationships

Line revenue. List revenue is quantity times list price, times the months billed for a recurring line, which counts its months once. Net revenue is list revenue times (1 minus the line's discount), and the discount given is the difference. Each line is posted to the cent, rounding half away from zero, and every total is a sum of posted lines.

Direct cost. A labor line costs billed units times hours per unit times the hours multiplier times its role's cost per hour. Labor cost comes only from the role table, which is why a unit cost on a labor line is refused: labor is never counted twice. Every other line costs quantity times its direct cost per unit, times the months billed for a recurring line. Gross profit is net revenue minus direct cost.

Commission. The commission is paid on marginal tiers of its basis: each rate applies only to the part of the basis inside its tier, so a basis one cent above a tier start pays one cent times the higher rate more, with no jump. A basis of zero or less pays no commission. On a gross profit basis, a discount lowers the commission too, so the sales rep shares its cost.

Contribution. Contribution is net revenue minus direct cost, commission and the other deal cost. Margin is contribution divided by net revenue, and is not shown when net revenue is 0. Markup is contribution divided by direct cost, commission and other deal cost together.

Cash. Months run from 0, the signing, to 24. The net revenue of the one-time lines (goods, labor and fees) is received by the customer payment schedule and their direct cost is paid by the cost payment schedule. A recurring line is received and costed in equal parts in each of its own months. The commission is paid in step with what the customer pays, and the other deal cost at signing. Each stream is posted by cumulative rounding, so its months add up exactly to its total, and the cumulative cash at month 24 equals the contribution. The present value discounts each month's net cash at the monthly rate (1 + yearly rate)^(1/12) − 1. The peak cash need is the lowest point of the cumulative cash below zero, with the first month it is reached. Cash timing never changes the contribution.

Largest discount. Only when you enter a lowest margin or a lowest net revenue. Every line is set to one common discount, held inside that line's lowest and highest discount, and the rate is raised in steps of 0.01% from 0% up to the highest discount any line allows. Direct cost does not depend on the discounts, so a larger common rate only lowers net revenue, and the smallest net revenue that still keeps both limits is the largest discount in money that any split between the lines could give. The limits are tested on the posted cents the quote shows, so rounding never breaks them. A scan of 101 rates finds the last one that keeps both limits, and halving the gap after it finds the exact 0.01% step. The page reports the rate, the net revenue, discounts, commission, contribution and margin there, and which limit stops it; when the lines' own highest discounts stop it first, a warning says so. When the margin falls steadily as the discount grows, the rate found is the largest; when it does not (a steep upper commission tier on a net revenue basis can do that), a warning says that a feasible rate between two scanned rates can be missed. When even the lowest discount on every line breaks a limit, the status is INFEASIBLE. A contribution exactly at the margin floor passes and a deficit of any size fails, for a floor of up to 6 decimals, so an exact boundary or a deficit under a cent can move the common discount by one 0.01% step on deals from about $10 million up.

Price for the target margin. Only when you enter a target margin. Every list price is multiplied by one number, with the discounts and hours as planned, and the search looks for the multiplier at which the margin equals the target. It scans 101 multipliers from the lowest to the highest you set, and narrows each of the first three places where the margin crosses the target by 40 halvings, far finer than a cent. A crossing whose contribution ends within one cent of the target is reported as a multiplier on every list price: 1.10 means list prices 10% higher. The page shows it to two decimals; the API and MCP return it unrounded. Commission tier starts are inside the calculation exactly. When the margin moves only one way with price across the scan, the multiplier found is the only one in the range; otherwise two crossings closer together than one scan step can be missed, and the page says so. A target met exactly at either end of the range is found, and the status is OPTIMAL. A margin that does not move at all with price reads as moving one way, with all 101 scan points counted as crossings, and the warning gives that count. When no multiplier is located the status is NO SOLUTION FOUND, with the lowest and highest margin the scan saw.

What-if table. Rows are 11 labor hours multipliers centred on yours, one row step apart; columns are 11 discount changes centred on yours, one column step apart. Each cell is the contribution recalculated with every labor line's hours times the row value and every planned discount changed by the column value and held inside its line's bounds, through the spreadsheet's own two-variable data table. The middle cell is your plan. When the hours step would take the lowest row below 0, the table is withheld with that reason and every other result still stands. The same holds when a what-if scenario is above the size limit: "Some what-if scenarios exceed the supported numerical range." One named case under Assumptions and limitations still shows the table.

Calculation sequence

The spreadsheet first checks every quote line, labor role, commission tier and payment row and every setting, and stops with NOT VALID at the first problem it finds, with every figure at 0. It then posts each line's list revenue, net revenue and direct cost to the cent, the commission on its tiers, and the contribution. It dates the cash month by month and discounts it. When a lowest margin or a lowest net revenue is entered it scans the common discount rates and narrows the last one that keeps both; when a target margin is entered it scans the price multipliers and narrows the crossings. The what-if table recalculates the plan at its 121 combinations. A block of consistency checks, including that posted net revenue less posted costs equals posted contribution, that each cash stream adds up to its total, and that the table's middle cell equals the contribution, runs before the status is returned.

Outputs and interpretation

The headline gives the contribution, the margin and the present value of the deal's cash. The result sections follow.

• Plan: list revenue, the discounts given and their share of list revenue, net revenue, direct cost and its labor part, gross profit, the commission basis, the commission and its effective rate, the other deal cost, markup, labor hours and the number of lines sold below their direct cost; with the results by line (including the optimized discount when the search ran), by labor role and by commission tier.
• Cash by month: the peak cash need and its month, and the receipts, cost payments, commission, other deal cost, net cash, cumulative cash, discount factor and present value of each month.
• Largest discount: the common discount, the net revenue, discounts, extra discount beyond the plan, commission, contribution and margin there, the limit that stops it, whether the margin falls steadily, the highest discount any line allows and the number of 0.01% rates available. Under INFEASIBLE it shows only the margin and net revenue at the lowest discounts. With neither limit entered this section is empty and says so.
• Price for the target margin: up to three multipliers with their residuals, the number found and the number of crossings, whether the margin moves one way with price, the list revenue, net revenue, contribution and margin at the first multiplier, and the lowest and highest margin scanned. With no target entered this section is empty and says so.

For nearby questions, the Price Volume Profit Scenario Simulator projects profit over months when demand responds to price, the Job Cost Markup Estimator prices one job from its costs, and the Sales Commission Accelerator Simulator models a full commission plan over a year.

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 quote line (a blank row above a used row, a missing or malformed ID, a repeated ID, an unknown type, a quantity, price or discount outside its limits, a unit cost on a labor line or a missing one on another line, a missing role or hours on a labor line or one on another line, months on a line that is not recurring or missing on one that is, or a planned discount outside its lowest and highest); no quote line; a labor role row (a gap, a missing or malformed ID, a repeated ID, a cost outside its limits); a labor line naming a role not in the role table; a commission tier row (a gap, a missing value, a first tier not starting at 0, or tiers not rising); a customer or cost payment row (a gap, a missing value, a month outside 0 to 24 or not rising) or a schedule that does not add up to 100% or is missing for a one-time line; and then a setting outside its limits, including a lowest price multiplier not below the highest; and last a quote above the size limit: "NOT VALID: Calculation exceeds the supported numerical range; reduce the prices, quantities or months." NOT VALID is a refusal, not a numeric answer: every figure is withheld.
• INFEASIBLE means a lowest margin or lowest net revenue is entered and even the lowest discount on every line breaks it. The plan, the cash and the target search stay valid and shown; the largest-discount outputs are withheld, except the margin and net revenue at the lowest discounts.
• NO SOLUTION FOUND happens only when a target margin is entered. It means no multiplier in the range was located, with the lowest and highest margin the scan saw. This is not a proof that no such price exists between the scanned multipliers. The plan, the cash and the largest discount stay valid and shown.
• CHECK accompanies a valid result that needs a look because a workbook consistency check failed.
• OK otherwise.

Warnings say when a line sells below its direct cost, when the commission basis is zero or negative so no commission is paid, when the lines' own highest discounts stop the largest-discount search before any limit, when the margin does not fall steadily as the discount grows, when the target margin is below the lowest margin allowed, when the target search crosses the target more than three times, when the deal makes a profit but its present value is negative, when no line has room between its lowest and highest discount or no line is labor (so the what-if columns or rows are identical), when net revenue is zero so margins are not shown, and when the what-if table is withheld. A warning never changes the status.

Assumptions and limitations

• Capacities are 20 quote lines, 5 labor roles, 5 commission tiers and 5 payments in each schedule; cash is dated by month from 0 to 24, and a recurring line runs at most 12 months, starting no later than month 12.
• The largest-discount search moves every line by one common rate inside each line's own bounds, in 0.01% steps. It reaches the largest discount in money exactly, but other splits between the lines can give the same total and are not shown.
• The price search multiplies every list price by one number with the discounts as planned. A lowest margin is not applied to it; a warning says when the target is below that floor.
• Labor cost is hours times the role's cost per hour; hours and prices do not change with each other except through the what-if multiplier.
• The commission is paid in step with the customer's payments; one cost payment schedule covers every one-time line; there is no day-by-day dating, financing, tax or currency conversion.
• The calculator does not estimate whether the customer accepts a price or a discount: every figure follows from what you enter.
• Very large quotes are refused, not estimated. Above about $10 trillion of revenue plus cost, where whole cents stop being exact, the calculator refuses. It tests that on the plan, on the largest-discount search when it runs and on the price search when it runs.
• Amounts within two cents below the $10 trillion limit may show as $10 trillion.
• Results exactly on, or within rounding noise of, a half-cent edge may differ by one cent, including a discount-limit pass or the month a cent of cash falls in, so the largest common discount or the month of the peak cash need shown can change.
• A plan a fraction of a cent above the $10 trillion limit may compute instead of refusing.
• In one named case, a what-if row a fraction of a cent above the $10 trillion limit shows in the table, with no warning, where the table should be withheld. The limit otherwise stands.
• The lowest margin allowed can have at most four decimals of a percent, such as 12.3456%. The slider's normal moves are steps of 0.1%. A request with more decimals is refused with NOT VALID. Above about $750 billion a margin that does not move with price can still read as not moving one way; that is a flag only.

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 line or role ID, a labor line with a unit cost, a payment schedule that does not add up to 100% and tiers or months that do not rise are refused by the workbook. With no lowest margin and no lowest net revenue the largest-discount outputs are left blank, and with no target margin the price search outputs are left blank. When the hours step would take the lowest row of the what-if table below 0, or a what-if scenario is above the size limit, only the table is withheld, with that reason, apart from the one named case under Assumptions and limitations.

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 check a result against your own costs and terms, before using it to price a real quote.

Sources and further reading

Your prices, discounts, costs, labor rates, commission tiers and payment schedules 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 Accounting, Volume 2: Managerial Accounting, 3.1 Explain Contribution Margin defines contribution margin as the amount by which a selling price exceeds variable cost, the amount left to cover fixed costs and profit; the calculator's contribution is the deal's net revenue less its direct cost, commission and other deal cost (retrieved 2026-11-03).
• OpenStax, Principles of Accounting, Volume 2: Managerial Accounting, 11.3 Explain the Time Value of Money explains why an amount received later is worth less today and how a present value is calculated, the idea behind the present value of the deal's cash by month (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).

This is an educational model of the quote you enter, not financial, accounting, pricing or legal advice. It does not estimate whether a customer will accept a price, it is not certified financial reporting, and it does not guarantee a profit; check a result against your own costs and terms before using it.