Excel Solver Example: Product-Mix Walkthrough You Can Reuse
Walk through a concrete Excel Solver product-mix example: maximize profit with resource constraints, run Simplex LP, and reuse the layout on new data.
A reusable maximize-profit model you can teach, audit, and extend.
A concrete excel solver example beats abstract dialog screenshots. The classic product-mix story—choose how much of each item to produce to maximize profit within resource caps—maps cleanly to Excel cells and teaches every moving part. This walkthrough builds that model, runs Simplex LP, and shows how to reuse the layout for new products.
For template packaging and buy-versus-build context, see the Excel Solver example template guide. For Solver button issues, enable the add-in first via how to enable Solver in Excel.
Key takeaway: Place profits and resource coefficients in tables, let SUMPRODUCT build the objective and constraint left-hand sides, run Simplex LP to maximize profit, then swap data rows to reuse the example.
Scenario narrative
A shop makes chairs, tables, and bookshelves. Each item earns a known profit per unit and consumes labor hours and board feet. Weekly labor and lumber are capped. How many of each item should you produce this week to maximize total profit?
Microsoft's optimal product mix with Solver uses the same story arc with different numbers.
Lay out the coefficient table
[TABLE: Product | Profit per unit | Labor hours per unit | Board feet per unit | Chair | 35 | 2 | 4 | Table | 40 | 4 | 6 | Bookshelf | 45 | 3 | 5]
Put decision variables—units to produce—in B12:D12 initially blank or seeded with ones. Total profit objective in B15:
=SUMPRODUCT(B8:D8,B12:D12)
Labor used:
=SUMPRODUCT(B9:D9,B12:D12)
Lumber used:
=SUMPRODUCT(B10:D10,B12:D12)
Limits might sit in F9:F10 with values 120 labor hours and 200 board feet.
Wire Solver
Open Data > Solver after loading the add-in. Set Objective B15 to Max. Changing cells B12:D12. Constraints:
F8 <= 120ifF8holds labor LHS, or direct cell references to formula cellsF9 <= 200for lumber- Optional:
B12:D12 >= 0
Select Simplex LP, Solve, keep Answer report. Excel Easy and Ablebits show parallel layouts you can cross-check.
Enter table → write SUMPRODUCT formulas → open Solver → Max profit → add resource constraints → Simplex LP → interpret results
Read the solution like an analyst
Suppose Solver sets chairs to zero, tables high, bookshelves moderate. Ask why: maybe tables dominate profit per labor hour. Check binding constraints—if lumber binds, profit rises only if lumber increases or usage per unit falls.
Change table profit from 40 to 50 and rerun. Students should predict direction before clicking Solve. That habit builds optimization intuition better than treating Solver as a black box.
Warning: If you require whole units, add int constraints; fractional optimal solutions are fine for theory but confusing for shop-floor readers.
Product highlight: Reusable examples separate data rows from formula rows so you can paste new products without rebuilding Solver references.
Extend the template
Add a fourth product column by copying formulas and expanding Solver ranges. Add a marketing budget constraint with another SUMPRODUCT row. Save the model inside the file for next month.
When maintenance cost exceeds teaching value, adopt a structured workbook. The PlanoNest Excel Solver Example1 template ships with Start Here, Example, and About sheets tuned for Simplex LP demos—one-time purchase, instant digital download; confirm scope on the product page.
Connect to deeper topics
Linear structure details live in Excel Solver linear programming. Engine comparisons live in Excel Solver optimization. Dialog mechanics live in how to use Solver in Excel.
Audit trail for stakeholders
Export the solved values, paste as values beside the live formulas, and attach a short note listing assumptions: profit per unit source week, resource availability, integrality choice, and Simplex LP engine. Optimization results are only as credible as those assumptions.
Scenario tables without rebuilding Solver
Keep coefficients in a labeled table and reference them with absolute addresses in SUMPRODUCT. When next month's profits arrive, paste new numbers into the table, rerun Solve, and keep the same dialog model. That pattern is how production planners reuse excel solver example files monthly.
Compare manual corner trials to Solver output
With two products, pick a corner—produce only tables, or only chairs—and compute profit manually. Repeat for another corner. Solver's optimum should beat or match those trials. If it does not, the changing cells or objective formula is wrong. This exercise builds trust before you email results to leadership.
Pair with template onboarding
If you distribute a structured file, ask readers to run the bundled example unchanged first, then swap one profit cell. The PlanoNest Example1 template is designed for that teaching path—one-time purchase, instant digital download; confirm sheet names on the product page. Return to the pillar guide when you must decide between DIY layout and that workbook.
Handoff note
Before handing off the sheet, replace example coefficients with clearly marked test values and ask another person to reproduce one objective total manually. That review catches mislabeled constraints, locked variable cells, and wrong changing-cell ranges faster than trusting the Solver status line alone.
Treat Solver reports as calculated outputs, not standalone evidence. Keep the workbook, engine choice, assumption date, and coefficient table together. If profits or capacities change later, create a new dated scenario instead of silently replacing numbers behind a report already shared with stakeholders.
Archive one solved workbook per month if you use Solver for planning. Future you will need to explain why March's mix differed from February's when commodity prices moved. A short changelog tab beats reconstructing decisions from email threads.
If students or colleagues fear Solver, remind them it only adjusts cells you permit. You remain in control of every formula and limit; Solver searches within the fence you built.
Before handing off the sheet, replace example coefficients with clearly marked test values and ask another person to reproduce one objective total manually. That review catches mislabeled constraints, locked variable cells, and wrong changing-cell ranges faster than trusting the Solver status line alone.
Treat Solver reports as calculated outputs, not standalone evidence. Keep the workbook, engine choice, assumption date, and coefficient table together. If profits or capacities change later, create a new dated scenario instead of silently replacing numbers behind a report already shared with stakeholders.
Archive one solved workbook per month if you use Solver for planning. Future you will need to explain why March's mix differed from February's when commodity prices moved. A short changelog tab beats reconstructing decisions from email threads.
If students or colleagues fear Solver, remind them it only adjusts cells you permit. You remain in control of every formula and limit; Solver searches within the fence you built.
Before handing off the sheet, replace example coefficients with clearly marked test values and ask another person to reproduce one objective total manually. That review catches mislabeled constraints, locked variable cells, and wrong changing-cell ranges faster than trusting the Solver status line alone.
Treat Solver reports as calculated outputs, not standalone evidence. Keep the workbook, engine choice, assumption date, and coefficient table together. If profits or capacities change later, create a new dated scenario instead of silently replacing numbers behind a report already shared with stakeholders.
Archive one solved workbook per month if you use Solver for planning. Future you will need to explain why March's mix differed from February's when commodity prices moved. A short changelog tab beats reconstructing decisions from email threads.
Frequently asked questions
What is a product-mix Solver example?
A model that chooses how much of each product to make to maximize profit while respecting resource limits like labor, machine hours, or materials.
What does maximize profit look like in Excel?
An objective cell with SUMPRODUCT of per-unit profit and production quantities, set to Max in the Solver dialog.
How many products can the example handle?
Solver supports many variables, but teaching examples usually use two to four products so reports stay readable.
What if optimal production includes fractional units?
Relax integrality for theory, or add integer constraints for realistic discrete units—note the choice in your write-up.
Where can I get a prebuilt example workbook?
You can build one with this walkthrough or download a structured template such as the PlanoNest Example1 workbook on the product page.
About the author
PlanoNest Editorial prepares practical Excel and planning template guides for the PlanoNest blog.
Disclosure: PlanoNest sells digital Excel, Google Sheets, and Notion templates. Links to PlanoNest products and collections point to our own template shop.
