By Jonas Tyrone Lobaton, MSc Quantitative Finance, FMVA®
Founder, Frac CFO · Updated October 11, 2026
An Excel pricing model should produce a correct quote before it tries to optimize a price. Start with a tier table, decide whether discounts apply incrementally or to every unit, then check costs, margin and demand before using Solver. This walkthrough includes working Excel formulas and a 600-unit example.
Quick answer: For 600 units at $20 for the first 100, $17 for the next 400, and $15 thereafter, incremental tiers charge $10,300. All-units volume pricing charges $9,000. Different contract, different invoice.
A working
SUMPRODUCTuses an allocated-units helper column. Optimize profit only with a demand assumption you can defend, and do not misread Solver’s LP sensitivity ranges as suggested selling-price changes.
Frac CFO • Pricing analysis & Solver modeling
Need a business-specific pricing analysis? Review the scoped Profitability and Pricing Analysis service. For an agreed Solver decision, see Optimization and Decision Modeling.
Table of Contents
- Choose the right pricing model
- Working incremental and all-units formulas
- Exact Excel cell references
- Workbook build and QA
- Solver price optimization
- Sensitivity-report limitations
- Build-time considerations
- Markup versus margin
- Pricing and cash flow
- Pricing dashboard example
- Ongoing controls
- Industry applications
- Frac CFO services
- FAQ
- Sources
Choosing an Excel pricing model or template
A useful pricing workbook defines the commercial rules first: units, rate breakpoints, whether a qualifying rate applies only to extra units or to the entire order, variable cost, discounts, taxes, and contract exceptions. Then it separates editable assumptions from tested formulas and approval outputs.
For a manageable tiered model, use Inputs, Tiers, Calc, and Dashboard sheets, plus a QA sheet for tier boundaries. Add recurring revenue and churn assumptions only if the business actually sells subscriptions; see our subscription revenue modeling tutorial.
Service versus template: Our Profitability and Pricing Analysis product is a scoped analysis service with an editable deliverable, not a generic instant-download volume-pricing workbook. Confirm its tier, segment count, and scenario limits before ordering.
Working Excel formulas: incremental versus all-units tiers
Incremental tiers charge each unit at the rate for its bracket. All-units pricing chooses one rate based on the total order and applies that rate to every unit. Do not swap these methods when quoting.
| Incremental band | Units | Rate | Charge |
|---|---|---|---|
| Units 1–100 | 100 | $20 | $2,000 |
| Units 101–500 | 400 | $17 | $6,800 |
| Units 501–600 | 100 | $15 | $1,500 |
| Incremental invoice | 600 | $10,300 | |
| All-units invoice: 600 × $15 | $9,000 |
Exact cell formulas for the sample workbook
In the companion workbook, Inputs!G6 is order quantity (600). Tiers!E6:E8 contains prior cumulative units (0, 100, 500), F6:F8 contains cutoffs (100, 500, blank for unlimited), and G6:G8 contains rates ($20, $17, $15).
Calculate units within each tier in Tiers!H6, then copy to H7:H8:
=MAX(0,MIN(Inputs!$G$6,IF(F6="",Inputs!$G$6,F6))-E6)
Calculate tier revenue in I6 using =H6*G6 and copy downward. Incremental invoice: =SUM(I6:I8). Or use the valid SUMPRODUCT alternative once allocated units exist:
=SUMPRODUCT(G6:G8,H6:H8)
For the same 600 units under all-units pricing, use the selected tier rate on the entire quantity:
=IF(Inputs!G6=0,0,IF(Inputs!G6<=100,G6,IF(Inputs!G6<=500,G7,G8))*Inputs!G6)
Keep tier cutoffs increasing, with no overlaps or gaps. Validate negative and noninteger quantities according to contract rules. Do not use a blanket IFERROR(...,0) to hide invalid rate tables.
Step-by-step: build and check the pricing workbook
- Inputs: Enter quantity, unit variable cost, fixed costs, reference price, expected base demand and an explicitly labeled hypothetical or measured elasticity.
- Tiers: Enter previous cumulative units, cutoffs and rates. Calculate allocated units and revenue per tier using the formulas above.
- Calc: Separate invoice amount from forecast profit. If price affects demand, calculate quantity using a defensible demand function and apply marginal costs.
- Dashboard: Show incremental and all-units invoices side by side, the difference, effective price per unit, margin and assumptions.
- QA Tests: Test 0, 99, 100, 101, 499, 500, 501 and 600 units. For the illustrated incremental tiers, the expected invoices are $0, $1,980, $2,000, $2,017, $8,783, $8,800, $8,815 and $10,300 respectively.
Use light-yellow editable inputs, protected calculation cells, source documentation, and visible model checks. This helps users change rates without silently rewriting formulas. Avoid promising that a workbook is reliable until its output matches the agreed contract rules and test cases.
When Solver can improve pricing decisions
Scenario comparison is enough when you have a few prices to test. Solver becomes useful when price, sales volumes or product mix must satisfy meaningful capacity, contractual or margin constraints. The model still needs an economically defensible objective: optimizing bad assumptions gives a precisely calculated bad answer.
For a single product, an illustrative price-dependent model is:
Expected profit = (Selling price − variable cost per unit) × forecast units sold at that price − avoidable fixed costs.
One possible hypothetical constant-elasticity demand equation is Q(P)=BaseUnits*(P/BasePrice)^Elasticity. For example, at base price $20, base demand 600 units, variable cost $9 and assumed elasticity −2, scanning $10–$30 in $1 increments gives an illustrative peak near $18. This is only a teaching case: actual elasticities require commercial data and validation.
- Objective: Maximize forecast contribution or profit, not price by itself.
- Changing cells: Selling prices and/or quantities or selection variables that management can control.
- Constraints: Minimum/maximum approved prices, nonnegative quantities, available capacity, floor margins, customer or regulatory limits, and product-mix rules.
- Engine: Simplex LP for genuinely linear continuous models, GRG Nonlinear for smooth nonlinear models, and Evolutionary for suitable discontinuous/non-smooth formulations. For discrete choices, first consider whether the model can be represented as a linear mixed-integer program supported by Solver.
- Validation: Compare multiple feasible starting values and realistic scenarios. Review elasticity, competitor reactions, churn, capacity, and uncertainty before approving price changes.
Microsoft documents the Excel Solver workflow. You can commission an agreed Solver model through our Optimization and Decision Modeling service; it is distinct from a standard margin analysis.
Test and sensitivity checklist before changing prices
Do not interpret Solver sensitivity ranges as automatically permissible changes in selling price. In an eligible continuous linear program solved using Simplex LP, allowable increase/decrease usually concerns objective coefficients or constraint right-hand sides under the report’s assumptions, not the effect of adjusting a retail price that changes demand.
The familiar LP sensitivity report is not available as the same report for arbitrary GRG Nonlinear, Evolutionary or integer-constrained models. Nonlinear models may expose different local diagnostics, but these should not be confused with LP allowable ranges or treated as proof of a global optimum.
Use this checklist before a price goes live:
- Check tier-boundary quantities (0, 99, 100, 101, 499, 500, 501 and 600 in this example).
- Validate negative, blank, duplicate, unsorted and overlapping tier inputs. An Excel formula returning zero is not necessarily a passing test.
- Check revenue, discounts, variable costs, contribution margin, taxes and collection assumptions separately.
- Stress-test demand elasticity, competitor behavior and volume shifts. Changing price without changing the demand model is usually not meaningful price optimization.
- Check Solver feasibility, binding constraints, objective direction and, when applicable, multiple starting points and alternate formulations.
- Compare actual results with the approved baseline and retain a versioned decision record.
For background on linear sensitivity interpretation, see Frontline Systems’ Solver sensitivity guidance.
How long does a reliable Excel pricing model take?
There is no dependable universal build time. A clean, three-tier calculator with documented rules is less work than one with customer-specific exceptions, multiple currencies, rebate true-ups, returns, annual commitments and integrations. The real workload depends on input quality, pricing policy, exception volume, tests, and approvals, not only the number of formulas.
Estimate effort from the required outputs and acceptance tests first. For any client-facing quote engine, reserve time to reconcile sample invoices, check every boundary, validate margin treatment, and document edge cases. Add a separate scope for market research or elasticity estimation; an Excel worksheet cannot invent reliable demand data.
Pricing structures, markup and margin
Common structures include cost-plus, target margin, all-units discounts, incremental usage tiers, flat-rate subscription fees, per-seat rates, and base-fee-plus-usage hybrids. A workbook should implement the contract being sold rather than forcing every offer into the same tier formula.
Markup is not the same as gross margin
Suppose unit cost is $80. A 25% markup on cost means =80*(1+25%), giving a $100 price and a 20% gross margin. A 25% target gross margin means =80/(1-25%), giving approximately $106.67. The second formula is only valid for a margin target below 100% and a consistent definition of unit cost.
| Calculation | Excel | Price |
|---|---|---|
| 25% markup on $80 | =80*(1+25%) |
$100.00 |
| 25% gross margin on $80 | =80/(1-25%) |
$106.67 |
Net contribution should also consider applicable transaction fees, customer discounts, fulfillment, commissions and other costs that change with a sale. Fixed costs and shared overhead require a separately defined planning purpose.
For subscription and value-based pricing, unit economics, churn and willingness-to-pay matter too. Do not assume a cost-plus formula proves the market will accept the resulting price.
Connect pricing decisions to revenue and cash flow
A price or quote is not cash collected, and a signed order is not necessarily recognized revenue. Link the model through a clear bridge: priced units or orders → expected fulfilled sales → recognized revenue under the applicable policy → invoices/accounts receivable → cash collections.
For a 13-week cash forecast, apply payment terms, realistic customer collections, seasonality, taxes, credits, refunds and bad-debt assumptions. A net-30 term does not guarantee receipt exactly 30 days later. In a subscription model, distinguish billings, deferred revenue and cash receipts where relevant.
Keep model versions and assumptions visible. If liquidity is the main decision, a 13-Week Cash Flow Forecast can complement—not replace—pricing analysis.
Dashboard: show the commercial impact of pricing
The executive view should answer: What will this order bill under each pricing contract? What margin remains after variable costs? How sensitive is expected profit to assumed demand? Show results, not only the input cells.
This is a text-accessible HTML preview of the actual Excel example, not an embedded workbook screenshot. The companion workbook also contains scenario inputs, price-versus-demand calculations, and eight boundary checks.
Use conditional formatting to flag failed calculations or minimum-margin exceptions, not to make a dashboard appear sophisticated. Protect formula ranges and clearly mark the assumptions someone is allowed to change.
Maintain prices, assumptions and controls
Reconcile unit costs and realized selling prices to actual transactions. Reconfirm tier cutoffs when commercial policies change, and retain prior versions of calculations and approval decisions. Review frequency should reflect price volatility, demand seasonality, supplier-cost changes and business risk—not an arbitrary once-per-quarter rule.
For every significant price revision, document the date, owner, new assumptions, affected SKUs or customers, test results and approval. A configurable margin variance threshold can flag investigation, but no universal 1–2-percentage-point trigger applies to every business.
If scenario logic becomes burdensome or monthly decision reviews require help, Custom Financial Model is a separately scoped modeling option; recurring advisory services should be considered only if ongoing work is genuinely needed.
Choose the pricing model that matches your business
Wholesale and distribution: Distinguish all-units volume rebates from incremental price bands. Include freight, minimum order requirements, customer-specific agreements and returns.
Agencies and services: Model project or retainer pricing alongside staffing hours, utilization, scope changes and paid extras. Price must recover the relevant cost-to-serve and reflect perceived value.
SaaS: Separate base subscriptions, seats, usage charges, annual prepayments, churn and expansion assumptions. Use subscription-model guidance for MRR and cohort forecasting.
Healthcare and regulated services: Understand actual service costs, reimbursement rules and applicable price restrictions before using a generic markup model. A spreadsheet does not replace regulatory advice.
There is no universal SKU count or company size at which a worksheet stops working. Evaluate input quality, integration needs, approvals, versioning and update costs.
Practical checks before a quote goes out
Three errors cause disproportionate trouble: treating incremental tiers as all-units rates, confusing 25% markup with 25% margin, and optimizing a price while holding demand unchanged. The remedy is a documented contract rule, a tested contribution calculation and a clearly labeled demand assumption.
Reject negative quantities, invalid rate bands, overlapping brackets and missing unit costs with explicit warnings. Protect your calculation layer. Retain the original quote and the approved rate table version, especially when customers have different contracts.
Get help with a business-specific pricing model
Need profitability and pricing scenarios? Our Profitability and Pricing Analysis product is a scoped service with an editable Excel deliverable. Service tiers have defined product/segment counts and pricing-scenario limits; a full automated tier calculator is not automatically included.
Need Solver optimization? Optimization and Decision Modeling covers one agreed objective and set of variables and constraints, subject to the chosen service tier and available data.
For custom models combining pricing, margin, revenue and cash flow, start with the One-time Projects page and confirm the full scope before purchasing.
Explore pricing analysis service
Frequently asked questions
How do I calculate tiered pricing in Excel?
Create a table of cumulative quantity cutoffs and rates, calculate the number of units that fall into each bracket with MAX and MIN, and total the amounts using SUMPRODUCT(rate_range,allocated_units_range). See the exact formulas above and test every boundary.
What’s the difference between incremental and all-units pricing?
Incremental pricing applies a rate only to the units within each bracket. All-units pricing uses one qualifying rate for the entire order. For the 600-unit example, the respective invoices are $10,300 and $9,000.
How do I calculate selling price from unit cost and target margin?
=UnitCost/(1-TargetGrossMargin). At $80 cost and a 25% gross-margin target, the price is approximately $106.67. Markup uses the different formula =UnitCost*(1+Markup).
How do I calculate a break-even selling price?
For a simplified one-product model at a specified positive sales volume, =(FixedCosts/ForecastUnits)+VariableCostPerUnit. If changing price changes sales volume, solve the break-even condition using a consistent demand function instead of assuming constant volume.
When should I use Excel Solver for pricing?
Use Solver if price or package mix affects forecast profit and the choices must meet real constraints. Validate demand response, variable costs and feasibility first. Choose the Solver engine based on model mathematics, not a rule that GRG always works for prices.
How can I model demand elasticity in Excel?
One illustrative formula is =BaseUnits*(TestPrice/BasePrice)^Elasticity with a negative own-price elasticity. Estimate the elasticity from relevant evidence when possible and test a range of assumptions; a hypothetical elasticity should never be sold as a measured forecast.
About the author
Jonas Tyrone Lobaton is the founder of Frac CFO. He holds a Master’s degree in Quantitative Finance and the FMVA® certification, with work spanning financial modeling, forecasting, optimization, dashboards, business finance, and spreadsheet automation.
Sources and references
- Microsoft Support – Define and solve a problem with Excel Solver
- Frontline Systems – Interpreting Solver sensitivity reports
- MIT – Pricing lecture notes
All article figures and the sample workbook are illustrative calculations, not observed business results or market research.
Related Frac CFO services
- Profitability and Pricing Analysis – scoped pricing and financial analysis with an editable workbook
- Optimization and Decision Modeling – an agreed Solver problem with defined objectives and constraints
- Custom Financial Model – separate modeling scope where integrated projections are needed

6 Responses