Solver and Optimization
Convert business limits, targets and choices into a structured Excel model that identifies the best feasible decision for profit, cost, production, workforce, logistics and budget allocation.
After This Chapter, You Will Be Able To
Design and validate optimization models that convert business rules into defendable decisions.
Structure Models
Separate decision variables, formulas, assumptions, constraints and results clearly.
Configure Solver
Set an objective, changing cells, constraints and the correct solving method.
Optimize Decisions
Maximize profit, minimize cost and allocate limited resources efficiently.
Validate Results
Check feasibility, sensitivity, assumptions and practical business usability.
Turn a Business Decision into a Mathematical Model
Optimization does not merely calculate an answer. It searches among feasible alternatives and identifies the best result according to a defined objective.
1 What Solver and Optimization Mean
Optimization is the process of finding the best possible value of an objective while respecting limits. Excel Solver changes selected cells repeatedly, recalculates the workbook and searches for a feasible combination that improves the objective.
The Four Essential Model Components
Practical Experiment 1: Identify the Optimization Components
Analyze a small production decision before opening Solver.
List two products, their unit profit and the resources each product consumes.
Mark the quantity cells, total-profit formula and capacity limits.
Change quantities manually and confirm profit and resource use update correctly.
2 Build a Professional Optimization Workbook
A reliable Solver workbook separates assumptions from choices and results. This improves auditability, reduces accidental overwriting and makes the model easier to explain.
State the exact business decision and optimization objective.
Enter verified profit, cost, capacity and demand assumptions.
Create dedicated cells for decision variables.
Connect choices to objective and resource-use formulas.
Translate every real-world limit into a worksheet rule.
Check feasibility, business logic and implementation readiness.
Recommended Worksheet Zones
| Zone | Contains | Recommended Control |
|---|---|---|
| Assumptions | Unit profit, costs, hours, capacities, demand and policy limits | Use consistent colours, units and source notes |
| Decision Variables | Quantities, allocations, selections or schedules Solver changes | Keep together and avoid formulas in these cells |
| Calculations | Objective, resource usage, demand served and constraint balances | Use transparent formulas such as SUMPRODUCT |
| Controls | Feasibility checks, slack, exceptions and reconciliation totals | Display PASS/FAIL or remaining capacity clearly |
| Output | Recommended decisions, objective result and management summary | Present units, assumptions and caveats |
=SUMPRODUCT(UnitProfit,DecisionQty)Typical total-profit objective.
=SUMPRODUCT(ResourcePerUnit,DecisionQty)Total resource consumed by the chosen plan.
=Capacity-ResourceUsedRemaining capacity or slack.
=IF(Used<=Capacity,"PASS","FAIL")Simple worksheet constraint check.
Practical Experiment 2: Create a Solver-Ready Model Layout
Build the five worksheet zones for a two-product planning model.
Create separate assumption, variable, calculation, control and output blocks.
Use SUMPRODUCT formulas for profit and each resource requirement.
Enter sample quantities and confirm every control cell responds correctly.
Enable Solver and Configure Every Dialog Box Field
Correct configuration is as important as correct formulas. A missing constraint or wrong changing-cell range can produce an unusable recommendation.
3 Enable and Open the Solver Add-In
In desktop Excel, open File → Options → Add-ins. At the bottom, choose Excel Add-ins in the Manage box, click Go, select Solver Add-in and confirm. Solver then appears on the Data tab.
Solver Parameters Dialog
Common Constraint Operators
| Operator | Meaning | Example |
|---|---|---|
| <= | Cannot exceed a limit | Total labour hours <= Available labour hours |
| >= | Must meet a minimum | Units supplied >= Minimum demand |
| = | Must equal a required value | Total allocation = Approved budget |
| int | Decision must be a whole number | Number of workers, trucks or machines |
| bin | Decision must be zero or one | Select or reject a project |
| dif | Selected decision cells must contain different integers | Assign distinct positions or ranks where appropriate |
Practical Experiment 3: Configure a Target-Profit Model
Set Solver to reach a required contribution amount through one changeable sales-volume cell.
Create price, variable cost, fixed cost, volume and profit formulas.
Set profit as the objective, choose Value Of and select volume as the changing cell.
Apply non-negative and whole-number rules, solve and verify the target manually.
Build Product-Mix and Allocation Models with Simplex LP
Linear programming is suitable when the objective and every constraint are linear combinations of the decision variables.
4 Understand Linear Relationships
A model is linear when each variable is multiplied only by a constant and variables are added together. The model should not multiply decision variables by one another, raise them to powers or place them inside nonlinear functions.
Usually Linear
- Product mix with fixed unit profit
- Transport allocation with fixed cost per unit
- Work-hour allocation
- Budget allocation with fixed return per amount
- Blending with fixed ingredient coefficients
Usually Nonlinear
- Price changes demand
- Interest compounds with a decision variable
- Variables are multiplied together
- Square, exponential or logarithmic relationships
- Complex step or discontinuous rules
Product-Mix Model Structure
| Product | Unit Profit | Labour Hours | Material Kg | Decision Quantity |
|---|---|---|---|---|
| Product A | $500 | 2 | 3 | Solver changes |
| Product B | $350 | 1 | 4 | Solver changes |
Total Profit = SUMPRODUCT(Unit Profit, Quantity)The objective cell to maximize.
Labour Used = SUMPRODUCT(Labour per Unit, Quantity)Constraint: labour used must not exceed capacity.
Material Used = SUMPRODUCT(Material per Unit, Quantity)Constraint: material used must not exceed available stock.
Quantity >= 0 and Quantity = IntegerPrevents negative or fractional production where inappropriate.
Practical Experiment 4: Maximize Product-Mix Profit
Find the most profitable production quantities under labour and material limits.
Create decision cells for Product A and B quantities and SUMPRODUCT resource formulas.
Maximize total profit with labour, material, non-negative and integer constraints.
Record unused capacity and explain why the recommended mix is optimal.
5 Minimize Cost and Allocate Resources
Optimization is not limited to profit. Many business models minimize transport cost, overtime, waiting time, raw-material cost, staffing expense or total distance while meeting service requirements.
Equality and Balance Constraints
A transport or allocation model often requires row and column totals. Supply leaving a source cannot exceed available supply, while delivery to a destination must meet demand. Totals and units must reconcile before the result is accepted.
Practical Experiment 5: Minimize Distribution Cost
Allocate shipments from two warehouses to three markets.
Create a shipment matrix and cost-per-unit matrix with matching dimensions.
Limit source totals by supply and require destination totals to meet demand.
Minimize SUMPRODUCT of cost and shipment quantities, then reconcile all totals.
Select the Method that Matches the Model
Solver methods use different search strategies. The correct choice depends on formula behaviour, smoothness and the presence of discrete decisions.
6 Simplex LP, GRG Nonlinear and Evolutionary
Use when objective and constraints are linear. It is usually the preferred method for product mix, transportation and fixed-rate allocation models.
Use for smooth nonlinear formulas. Starting values matter, and different starting points may lead to different local solutions.
Use for non-smooth, discontinuous or difficult models, including logic-heavy formulas. It may require more time and careful settings.
| Question | Simplex LP | GRG Nonlinear | Evolutionary |
|---|---|---|---|
| Are all relationships linear? | Yes | Not required | Not required |
| Are formulas smooth and continuous? | Yes | Normally yes | Not necessary |
| Can the model contain abrupt IF-based jumps? | Avoid | Avoid where possible | More suitable |
| Does the starting value matter? | Usually less | Yes | Population-based search reduces dependence but settings still matter |
| Typical strength | Fast linear optimization | Smooth nonlinear optimization | Complex or non-smooth search |
Nonlinear Example
Suppose selling price is a decision variable and demand decreases as price increases. Revenue becomes Price × Demand(Price), so the decision variable affects more than one part of the formula and the relationship is nonlinear.
Demand = BaseDemand - Sensitivity × PriceDemand changes with the selected selling price.
Profit = (Price - UnitCost) × DemandPrice appears in multiple connected terms, creating a nonlinear objective.
Practical Experiment 6: Optimize Selling Price
Find a price that maximizes profit when demand changes with price.
Create a demand formula linked to the price decision cell.
Use GRG Nonlinear with practical minimum and maximum price constraints.
Run the model from several starting prices and compare the final solutions.
Use Integer and Binary Constraints for Real-World Choices
Many decisions cannot be fractional. A business cannot normally deploy 2.4 vehicles, approve 0.6 of a project or open part of a branch.
7 Whole-Number, Yes/No and Logical Decisions
An integer constraint forces a decision cell to contain a whole number. A binary constraint restricts a decision to 0 or 1, commonly representing No/Yes, Reject/Select or Closed/Open.
Integer
Use for employees, machines, trucks, batches, rooms or other indivisible quantities.
Binary
Use for project selection, vendor approval, branch opening or campaign activation.
Linking Constraint
Connect binary selection with quantity, cost or capacity using a logical mathematical rule.
Typical Binary Linking Formulas
Allocation <= Maximum × SelectedIf Selected is 0, allocation must be 0. If Selected is 1, allocation may rise to the maximum.
Allocation >= Minimum × SelectedIf an option is selected, it must receive at least the specified minimum allocation.
SUM(SelectedProjects) <= 4Choose no more than four projects.
ProjectA + ProjectB <= 1Projects A and B are mutually exclusive.
Practical Experiment 7: Select Projects under a Budget
Choose the best combination of proposed projects using binary variables.
Enter project cost, expected benefit and a 0/1 selection cell for each proposal.
Keep total cost within budget and restrict selection cells to binary values.
Maximize total benefit and document why selected projects outperform alternatives.
Interpret Solver Results, Reports and Warning Messages
A technically solved model still requires feasibility checks, business review and clear communication before implementation.
8 Keep, Restore and Document the Solution
After Solver finishes, review the status message. Choose Keep Solver Solution only after checking decision cells, objective value and all constraints. Restore original values when the result is infeasible, unrealistic or based on incorrect assumptions.
Result Validation Checklist
- Every decision variable is within its permitted range.
- All capacity, demand, budget and policy constraints are satisfied.
- The objective formula matches the intended business goal.
- Integer and binary decisions contain valid values.
- Units and reporting periods are consistent.
- The recommended plan can actually be implemented.
- A manual or alternative-scenario check supports the result.
| Report | Purpose | Important Interpretation |
|---|---|---|
| Answer Report | Shows original and final values plus constraint status | Useful for documenting the recommended solution and binding limits |
| Sensitivity Report | Available for suitable linear continuous models | Shows reduced costs, shadow prices and allowable changes |
| Limits Report | Shows objective values when variables move toward limits | Helps review how individual decisions may affect the objective |
Binding Constraints, Slack and Shadow Price
A binding constraint is fully used at the solution. Slack is unused capacity for a non-binding limit. In a linear sensitivity report, a shadow price estimates how much the objective may improve for one additional unit of a constrained resource, within the stated allowable range.
9 Troubleshoot Solver Models
| Problem | Likely Cause | Corrective Action |
|---|---|---|
| Objective does not change | Changing cells are not linked to the objective | Trace formulas and manually test decision cells |
| Unbounded solution | A necessary upper or lower limit is missing | Add realistic capacity, demand or decision bounds |
| No feasible solution | Constraints conflict or requirements exceed capacity | Review each rule, units and minimum requirements |
| Negative quantities | Non-negativity was not enforced | Add decision cells >= 0 or enable non-negative variables |
| Fractional people or assets | Integer restriction is missing | Add int constraints to indivisible decisions |
| Different answers from GRG | Nonlinear local solutions or starting-point dependence | Test several starting values and compare objective results |
| Very slow solution | Excessive discrete rules or a poorly scaled model | Simplify logic, tighten bounds and improve model structure |
| Business-rejected result | A real-world rule is absent | Add the missing operational, policy or quality constraint |
Practical Experiment 8: Compare Methods and Diagnose Errors
Use three prepared models: linear, smooth nonlinear and logic-heavy.
Select the expected solving method for each model and justify the choice.
Remove one critical constraint and observe the resulting warning or unrealistic answer.
Restore the rule, solve again and document the change in feasibility and objective.
Choose a Solver Method and Constraint Pattern
Select the model characteristics to receive a practical starting recommendation.
Production and Workforce Optimization Model
Create a complete decision model for a business producing multiple products with limited machines, labour, materials and staffing capacity.
State whether the objective is maximum contribution, minimum total cost or a balanced service target.
Enter product contribution, machine time, labour time, material use, demand limits and workforce costs.
Add production-quantity cells and, where required, integer staffing or binary activation decisions.
Use SUMPRODUCT for contribution, capacity usage, wage cost and demand fulfilment.
Set objective, changing cells, constraints and the correct solving method.
Reconcile constraints, explain binding resources and prepare a management recommendation.
Complete These Optimization Tasks
Save each model or calculation as evidence for your Advanced Excel portfolio.
Write the decisions, objective, formulas and constraints for a local business problem.
Create separate assumption, decision, calculation, control and output zones.
Use Solver to determine the activity level required to reach a specified profit.
Maximize profit for at least three products under two resource limits.
Minimize shipment cost from multiple sources to multiple destinations.
Meet daily staffing demand using full-time and overtime resources at minimum cost.
Use binary variables to choose projects within budget and policy conditions.
Optimize price when demand changes with the selected price.
For a suitable linear model, explain binding constraints, slack and one shadow price.
Present recommendation, objective improvement, key limits, risks and implementation conditions.
Mistakes Solver Users Should Avoid
Most unreliable Solver results come from incomplete business logic rather than from the optimization engine itself.
Wrong Practices
- Changing cells are not connected to the objective.
- A critical capacity, demand or policy constraint is missing.
- Simplex LP is selected for a nonlinear model.
- People, vehicles or projects are allowed to be fractional.
- Negative decisions are permitted unintentionally.
- Units or reporting periods are inconsistent.
- The first nonlinear result is accepted without retesting.
- An infeasible business recommendation is presented as final.
Professional Practices
- Test every formula manually before running Solver.
- Create a written list of all real-world constraints.
- Select the solving method from model behaviour.
- Use integer or binary rules only where genuinely required.
- Add practical upper and lower bounds.
- Maintain consistent units and source documentation.
- Retest nonlinear models from multiple starting points.
- Validate, reconcile and explain the solution before implementation.
Quick Quiz: Solver and Optimization
Answer all 12 questions and review the explanations after submitting.
1. What does an Excel optimization model require?
2. Which cells should be selected as changing variable cells?
3. Which method is normally preferred for a fully linear product-mix model?
4. Which formula pattern is nonlinear?
5. What does an integer constraint do?
6. What values can a binary decision cell contain?
7. What is a binding constraint?
8. Why can a model become unbounded?
9. Which method is suitable for smooth nonlinear formulas?
10. What is slack?
11. Why should nonlinear models be tested from different starting values?
12. What should happen before a Solver recommendation is implemented?
Remember These Optimization Principles
Review these points before moving to Formula Auditing and Error Management.
Define the Decision
State exactly what Solver may change and what business result should improve.
Build Transparent Formulas
Connect decision cells to objective and constraints using auditable calculations.
Capture Every Constraint
Translate capacity, demand, budget, policy and implementation limits.
Match the Method
Use Simplex LP, GRG Nonlinear or Evolutionary according to formula behaviour.
Use Discrete Rules Carefully
Apply integer and binary constraints where decisions cannot be fractional.
Validate Before Action
Check feasibility, assumptions, reports, sensitivity and practical usability.