Microsoft Excel · Advanced modeling and platform-dependent features · Intermediate
Optimize a constrained model with Solver in Microsoft Excel
Solve a small linear allocation problem, verify all constraints, and recompute the optimum after a capacity change.
Official documentation reviewed; synthetic fixtures checked independently; not executed in Excel · Editorial standards
Quick answer: Define a formula objective, identify the variable cells, add every constraint, and choose Solver’s linear method for a linear model. Check the returned solution against the formulas and constraints rather than accepting a success message without inspection.
Scope and model
Use English Excel for Microsoft 365 desktop on Windows and the Microsoft Solver add-in. If the command is absent, choose File > Options > Add-ins, select Excel Add-ins in Manage, choose Go, check Solver Add-in, and confirm. If your managed installation does not permit the add-in, stop and use your approved support route. No third-party download is needed for this exercise.
This synthetic allocation model maximizes contribution units for two products. It is arithmetic practice, not business or investment advice. Start a new worksheet and enter:
| Cell | Entry |
|---|---|
| A1:C1 | Item, A, B |
| A2 | Quantity |
| B2:C2 | 0, 0 |
| A3 | Contribution per unit |
| B3:C3 | 3, 2 |
| A5 | Total contribution |
| B5 | =B2*B3+C2*C3 |
| A6 | Capacity used |
| B6 | =B2+C2 |
| A7 | Capacity limit |
| B7 | 8 |
| A8 | Maximum A |
| B8 | 4 |
B2 and C2 are adjustable quantities. B5 is the objective formula. The restrictions are total quantity no more than 8, A no more than 4, and both quantities nonnegative. This model permits continuous quantities; the optimum happens to be whole numbers without adding integer constraints.
Configure and solve
- Choose Data > Solver.
- Set Objective to
$B$5and select Max. - Set By Changing Variable Cells to
$B$2:$C$2. - Add
$B$6 <= $B$7for total capacity. - Add
$B$2 <= $B$8for the A limit. - Add
$B$2:$C$2 >= 0so negative production cannot be used to distort the model. - Choose the linear solving method, Simplex LP (described as LP Simplex in Microsoft’s guidance), and select Solve.
- In Solver Results, inspect the status and choose Keep Solver Solution only when the result satisfies the intended model.
The expected optimum is A=4 and B=4, producing 20 contribution units while using all 8 capacity units. Check B5=3×4+2×4=20 and B6=4+4=8.
An independent argument verifies optimality. Since A+B≤8, contribution 3A+2B equals 2(A+B)+A, which cannot exceed 16+4=20. The proposed solution reaches that upper bound and satisfies every constraint. This is stronger than merely finding a feasible pair.
Change a binding limit
Change B7 from 8 to 6 and solve again using the same model. The expected optimum becomes A=4, B=2, and contribution 16. The same bound is now 2×6+4=16. Keep or restore values deliberately in the results dialog rather than overwriting the variable cells without noting what changed.
Save the workbook as XLSX, close, and reopen. Check the formulas, capacity 6, solution 4/2, and Solver selections. Saving the workbook retains the worksheet’s last Solver settings.
If Solver says the model is unbounded, look for a missing capacity constraint. If it reports infeasibility, inspect conflicting lower and upper bounds. If changing B2 or C2 does not alter B5, repair the objective formula first. For basic formula reconciliation, see SUM.