By Jonas Tyrone Lobaton, MSc Quantitative Finance, FMVA®
Founder, Frac CFO · Updated October 16, 2026
Product mix optimization determines how many units of each product to make or sell within machine, labor, raw material and demand limits. It can maximize total contribution margin, but it does not automatically maximize cash flow or account for setup costs and other fixed costs unless those are modeled.
Quick answer: Calculate each SKU’s unit contribution, resource use and demand cap. Use Simplex LP when quantities are continuous and the model is linear; use integer or binary constraints for whole units, batches or setup decisions. Verify the solution against every constraint, then test capacity and demand changes.
Our worked three-product example produces $3,680 contribution with 50 units of A, 26 of B and 18 of C, exhausting 180 labor hours and 120 machine hours. Shadow prices are local marginal LP values, not automatically a business case for expansion.
Need a scoped Solver model for your business? Frac CFO’s Optimization and Decision Modeling service delivers an editable decision model within the selected tier’s variable and constraint limits; it does not promise operational implementation or financial gains.
Table of Contents
- What optimization decides
- Why product mix matters
- LP, MIP and nonlinear decision models
- Inputs, contribution margin and constraints
- Shadow prices and local sensitivity
- Solved three-product example and Solver configuration
- Exact Excel Solver formulas
- AI and data preparation
- Implementation and governance
- Pricing and demand
- Demand scenarios
- Profit and fixed costs
- Strategic constraints
- Common mistakes and QA checks
- Frac CFO models and packages
- FAQ
- References
What does product mix optimization actually decide?
Let x be the units produced for each SKU. Maximize total contribution = Σ(unit price − incremental variable cost) × units within the production and customer-demand limits. Include committed minimum order volumes, storage, material availability and labor-skill limits when they genuinely apply.
Contribution is not operating profit. Where fixed costs are unchanged by the decision, maximizing contribution also maximizes operating profit. Report operating profit = contribution − relevant fixed costs separately. Avoidable setup charges, overtime premiums and additional shifts must be modeled when choosing them alters the cost structure.
A one-resource ranking based on contribution per bottleneck-hour can sometimes give the correct plan; with multiple competing constraints, ranking is not reliable. A feasible, quantified optimization model is more defensible than choosing SKUs by unit margin alone.
Why product mix matters for profit, operations and customer demand
Products compete for labor, equipment, materials, storage and customer attention. Products with attractive gross margins can consume disproportionate bottleneck capacity. A production plan should also check changeovers, delivery obligations, yield losses, inventory carrying costs and quality constraints.
Do not delete a low-volume SKU solely because Solver assigns it zero units in one scenario. It may support a larger customer relationship, improve a product bundle, or meet a strategic obligation that has not been encoded in the model. Use sensitivity analysis and scenario testing, then ask operations and sales to validate the assumptions.
Begin by reconciling selling prices net of relevant discounts, incremental unit costs, capacities and demand forecasts. If different products use minutes and hours, convert everything to consistent units before optimizing.
Which model should you use: LP, MIP, nonlinear or scenario testing?
- Linear programming (LP): Continuous quantities, fixed unit contribution and linear resource constraints. Select Simplex LP in Excel Solver. Standard Solver sensitivity reports apply to the appropriate continuous LP solution.
- Mixed-integer programming (MIP): Whole batches, yes/no product activation, minimum runs and setup costs require integer/binary decisions. For product i, link quantity xi to a binary start decision yi with
0 ≤ x_i ≤ DemandCap_i × y_i; deduct applicable setup costs from the objective. For fixed 20-unit batches, setx_i = 20 × n_iwith integer ni. - Nonlinear pricing: If price and quantity are both changing and demand responds to price, the objective may be nonlinear. Do not apply Simplex LP to a nonlinear revenue or price-elasticity formula as if it were linear.
- Scenario, robust or stochastic analysis: Evaluate demand uncertainty explicitly. Solving low/base/high cases separately shows the best possible plan after each demand case is known, not necessarily the best advance production commitment.
The built-in Microsoft Excel Solver has a 200 decision-variable limit for the standard Solver engine; multiple products × months × facilities plus binary variables can exhaust the available decision variables. Consider a more capable solver when the actual model exceeds supported limits.
Start with a transparent continuous LP to understand bottlenecks. Add discrete decisions and uncertainty only when business rules justify the complexity; do not use continuous-LP shadow prices as if they were sensitivity outputs for an integer solution.
What financial and production data does Solver need?
For every product and reporting period, record a stable SKU ID, expected selling price net of discounts and returns, relevant variable costs per unit, estimated demand cap, and resource requirements (labor hours, machine hours, raw materials and storage as applicable). Validate resource availability and minimum order commitments with operations.
Use a consistent cost convention: unit contribution = net selling price − incremental variable cost. Include avoidable batch/setup costs explicitly if they depend on which products are selected. Report unavoidable fixed overhead separately when converting total contribution to operating profit. For example, the worked LP returns $3,680 contribution; if the period has $2,000 of unchanged fixed costs, illustrative operating profit is $1,680.
Reconcile input units (minutes versus hours, kilograms versus grams), production yields, sales periods and SKU mappings before running Solver. A mathematically optimal answer based on inconsistent inputs is still operationally invalid.
Also document customer dependencies, minimum display/assortment commitments, and any portfolio rules not captured by the simple model. These may materially change the preferred production plan.
Shadow prices: local LP values, not automatic purchasing recommendations
A continuous LP shadow price estimates the marginal increase in the objective from one more unit of a resource while the relevant optimal basis and assumptions remain valid. A zero shadow price can indicate local slack capacity but does not prove a resource is strategically worthless. A reduced cost can help evaluate whether an excluded activity needs better contribution economics to enter an LP solution.
In the solved three-product example below, the local shadow price is $14 per additional labor hour and $8 per additional machine hour. Independently re-solving with labor increased from 180 to 181 hours increases contribution from $3,680 to $3,694. Increasing machine capacity from 120 to 121 hours increases contribution to $3,688.
| Capacity scenario | Optimized contribution | Change |
|---|---|---|
| 180 labor hours, 120 machine hours | $3,680 | Baseline |
| 181 labor hours, 120 machine hours | $3,694 | +$14 |
| 180 labor hours, 121 machine hours | $3,688 | +$8 |
Expansion decision: Compare the incremental benefit with the actual incremental cost of obtaining feasible capacity, including overtime premiums, minimum shift lengths, setup charges and additional bottlenecks. A local value of $14/hour is not automatically a business case for a $100,000 machine or a 40-hour shift.
The Sensitivity Report from a suitable continuous Simplex LP can show shadow prices, reduced costs and allowable parameter changes. It does not establish that an integer/binary MIP solution has the same marginal sensitivities. Re-solve for material, discontinuous or out-of-range changes.
Worked example: solve a three-product mix in Excel Solver
Consider a fictional factory making products A, B and C. Unit contributions are $40, $30 and $50 respectively. Each product uses labor and machine hours, and production cannot exceed expected demand.
| Input | A | B | C |
|---|---|---|---|
| Contribution / unit | $40 | $30 | $50 |
| Labor hours / unit | 2 | 1 | 3 |
| Machine hours / unit | 1 | 2 | 1 |
| Maximum demand (units) | 50 | 60 | 40 |
The factory has 180 labor hours and 120 machine hours. Maximize 40*A+30*B+50*C, subject to 2*A+B+3*C <= 180, A+2*B+C <= 120, product demand caps and nonnegative quantities.
| Product | Optimal continuous units | Contribution |
|---|---|---|
| A | 50 | $2,000 |
| B | 26 | $780 |
| C | 18 | $900 |
| Total | 94 | $3,680 |
Reconciliation: labor 2*50+26+3*18 = 180/180 hours; machines 50+2*26+18 = 120/120 hours. The resource limits bind and the demand cap on Product A binds. These quantities and the objective were independently verified with an LP solver.
Exact cells and Solver configuration
In the accompanying sample workbook’s Model sheet, E6:E8 contains products; F6:F8 unit contributions; G6:G8 decision quantities; H6:H8 demand limits; I6:I8 labor hours/unit; J6:J8 machine hours/unit.
G12 =SUMPRODUCT(F6:F8,G6:G8) [maximize contribution]
G14 =SUMPRODUCT(I6:I8,G6:G8) [labor hours used]
G15 =SUMPRODUCT(J6:J8,G6:G8) [machine hours used]
Open Excel’s Solver add-in. Set Objective Model!G12 → Max; changing cells Model!G6:G8; add Model!G14 <= Model!H14, Model!G15 <= Model!H15, Model!G6:G8 <= Model!H6:H8, and nonnegativity. Choose Simplex LP and, after successful solve, keep the solution. Ask for the Sensitivity Report only for the continuous LP case.
For whole-unit, 20-unit-batch or binary setup-cost choices, use integer constraints and an appropriate MIP model instead. Confirm that all reported results satisfy the original capacity, demand and logical constraints.
Where automation and AI help and where humans must review
Automation can import SKU sales history, normalize product identifiers, detect duplicate source rows, refresh resource-use assumptions and create reproducible demand scenarios. Generative AI may assist analysts by proposing mapping candidates and drafting commentary, but its outputs require review against source records.
Do not allow an AI-generated product list or cost assumption to flow into Solver without validation. Trace every cost, demand estimate and restriction back to a source or approved manual override. Preserve model version, timestamp, data cut-off, scenario assumptions and the approver.
From optimal spreadsheet result to production decision
Ask finance to verify contribution and cost treatment, operations to validate hours, yields and changeovers, and sales to confirm the demand caps and customer obligations. A financially optimal model can still create unacceptable stockouts, delayed deliveries or contractual problems.
Before changing the schedule, record the baseline, chosen decision, intended benefit, implementation cost, operational owner and stop/rollback criteria. Pilot a subset of products when disruption risk is material. After implementation, compare actual volumes, contribution and resource consumption with the optimized plan.
Re-run when important prices, costs, capacity, demand or contractual commitments change; don’t rely on a universal monthly or quarterly cadence. If future capacity investment is involved, assess the full batch or shift economics rather than multiplying a local shadow price by an arbitrary large expansion.
Where an Excel-first model adds value
An editable Excel workbook is useful when the team needs a transparent decision model and can maintain the data and controls. It can make assumptions and mathematical constraints auditable without introducing enterprise software. However, a spreadsheet is not automatically faster, easier to govern or less risky than a specialized planning system.
Choose a one-time scoped optimization project when the problem has defined variables, constraints, outputs and acceptance tests. Recurring finance support may fit situations that require ongoing management interpretation, but does not imply all optimization, data integration or implementation work is included.
Pricing and product mix: don’t assume a discount creates demand
In a standard LP, unit selling prices, variable costs and demand caps are fixed inputs for each solve. A change in price may change contribution and expected demand, but the change in demand must be estimated from evidence or tested as a scenario, not automatically increased whenever price falls.
Compare a clearly documented baseline against price scenarios that update both unit contribution and demand estimates, then rerun Solver. If price and quantity are optimized simultaneously and demand depends on price, the objective can become nonlinear; a continuous Simplex LP may then be inappropriate.
When pricing a potential new product, continuous-LP reduced costs may help explain why it is currently excluded. A reduced-cost figure is not an unrestricted retail price recommendation and may not apply to an integer model or demand-responsive pricing decision.
See our Excel Pricing Model guide for tiering and explicit pricing assumptions, and Profitability and Pricing Analysis for a separately scoped margin/pricing engagement.
Low, base and high demand scenarios: compare feasible plans
Forecast demand is uncertain. In the workbook, using 80%, 100% and 120% of each product’s baseline demand cap produces three independently optimized LPs using the same 180 labor hours and 120 machine hours.
| Demand case | A units | B units | C units | Contribution |
|---|---|---|---|---|
| Low | 40 | 28 | 24 | $3,640 |
| Base | 50 | 26 | 18 | $3,680 |
| High | 60 | 24 | 12 | $3,720 |
Important: These are the optimums if the demand case is known when production quantities are chosen. They do not automatically represent a feasible single production commitment made before uncertain demand is resolved. An advance plan may need scenario-linked inventory, shortage and write-off costs, or a robust/stochastic optimization model.
Products that remain in all three solutions demonstrate some stability, but that is not proof they are safe investments. Evaluate forecast reliability, customer contracts and costs of missing demand.
Contribution margin, operating profit and break-even
With fixed selling prices and variable costs, total contribution = SUMPRODUCT(UnitContribution,Quantity). If unavoidable fixed costs for the period are $2,000, the worked optimal plan yields $3,680 − $2,000 = $1,680 operating profit before other costs or adjustments.
Fixed costs that are avoidable or depend on which products are run belong in the decision model, often with binary variables. A constant fixed cost may be excluded from the objective without changing the optimal continuous mix, but should remain visible in the management profit bridge.
If the current modeled feasible set cannot cover fixed costs, it does not prove no future product-mix improvement is possible: new SKUs, different prices, new resources, revised demand or altered contractual constraints could change the economics.
Related: Break-Even Analysis in Excel and Client Profitability Analysis.
Align the product mix with company strategy
Profit-maximizing production quantities reflect only the objective and constraints you define. Protect contractual minimum deliveries, qualified suppliers, minimum assortment commitments, regulatory rules and risk limits by modeling them explicitly where feasible. Leadership can then compare the economic cost of a strategic minimum against the no-minimum baseline.
If a strategic rule forces a product into the plan, compare the revised optimum to the original, and disclose the difference as the opportunity cost of the policy. Review its longer-term rationale instead of silently overriding a spreadsheet cell after Solver finishes.
Product and inventory decisions can affect cash, lead times and working capital. Our Working Capital Optimizer is a separately scoped service for trade-offs involving inventory, supplier payment timing and receivables; it is not part of this sample LP.
Common Solver mistakes and a practical QA checklist
- Wrong objective: using gross revenue or allocated fixed-cost margin instead of comparable incremental contribution.
- Missing restrictions: no demand cap, no nonnegativity rule, or inconsistent labor and machine units.
- Wrong Solver method: solving a nonlinear or binary decision with a continuous linear model.
- Overclaiming sensitivity: treating the local LP shadow price as a guaranteed return on a large capacity investment or using it for a MIP solution.
- Ignoring uncertainty: assuming a different optimal solution in every known demand scenario proves the same decision is feasible before demand becomes known.
- Incomplete checks: always verify production quantities, total contribution, both resource limits, every demand cap, saved input version and required customer commitments.
The companion seven-sheet SAMPLE workbook includes 10 formula-driven QA checks for the saved continuous LP solution, profit bridge, capacity sensitivities and independent demand scenarios. Changing source economics or constraints requires a fresh Solver run—the scenario quantities are saved examples, not a fully automatic recalculation of the optimum.
Frac CFO tools for product mix, margin and capacity decisions
Primary service: Optimization and Decision Modeling delivers one scoped, editable Excel Solver model with results and findings. Its published packages have different limits:
| Tier | Starting price | Published scope |
|---|---|---|
| Essential | $89 | Up to 10 variables / 15 constraints |
| Standard | $129 | Up to 20 variables / 30 constraints; supported sensitivity |
| Advanced | $209 | Up to 40 variables / 60 constraints; expanded scope and supported sensitivity |
The service does not include operational implementation, unrelated decisions, enterprise optimization systems or guaranteed commercial results. Confirm the agreed objective, variables, data and feasibility rules before purchase. Sensitivity output is limited to cases where the mathematical model supports it.
Supporting service: Profitability and Pricing Analysis can assess unit margins and pricing scenarios within a selected tier, but does not replace a constrained production model.
Need an editable model for your actual production limits?
Review the product’s scope limits, or explore one-time custom Excel projects if you require more complex demand, cost or capacity assumptions.
Frequently asked questions about product mix optimization
What is product mix optimization?
It is a constrained decision about how much of each product to produce or sell to achieve a clearly defined objective, commonly total contribution margin, while respecting resource availability, realistic demand and other requirements.
Can Excel Solver handle product mix optimization?
Yes. Build an objective formula using SUMPRODUCT, define the production quantities as changing cells and add resource, demand and nonnegativity constraints. Use Simplex LP for genuinely linear, continuous models. The standard Excel Solver has a decision-variable limit, so large multi-period or mixed-integer models may need a different solver.
Must production quantities be integers or whole batches?
Use integer or batch decision variables if the product cannot be produced fractionally. If manufacturing also requires one-time setup costs, introduce binary activation variables and linking constraints. Continuous LP results may not remain optimal after imposing these rules.
Why does Solver say the product mix is infeasible?
Check whether minimum customer commitments collectively exceed labor, machine or material capacity; whether two restrictions contradict each other; and whether mixed units or incorrect formula references create impossible limits. An infeasible result can indicate a real business conflict, not necessarily a software error.
Can I use an LP shadow price for a mixed-integer result?
Not as a general rule. Standard continuous-LP shadow prices and allowable ranges describe a local LP solution. Discrete setup, batch and binary decisions can make the marginal value of capacity discontinuous. Re-solve the integer model at realistic alternative capacities and compare the actual objective change.
When should I buy more bottleneck capacity?
Compare the marginal benefit from a feasible increment of capacity with all relevant incremental costs, setup charges, minimum shift lengths, demand limits and secondary bottlenecks. A positive LP shadow price is a useful screening metric, not an unconditional instruction to purchase a machine.
How frequently should I rerun product mix optimization?
Re-solve after material changes to demand, prices, variable costs, contract commitments, resource capacity or production rules. Agree a reporting cadence with operations and finance and preserve assumptions and actual results for comparison.
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 technical references
- Microsoft Support – Using Solver to determine the optimal product mix
- Microsoft Support – Define and solve a problem by using Solver
- MIT OpenCourseWare – Optimization Methods in Management Science
All manufacturing, shadow-price and demand-scenario numbers in this article are fictional illustrations. The LP optimum and scenario outputs were independently computed and checked; they do not establish client savings or guaranteed business outcomes.

3 Responses