Replace Excel Solver with Python

Excel Solver is where a lot of practical optimisation lives: which products to make with limited machine time, how to allocate a budget across channels, which warehouse should serve which customer. The model is usually sound; the problems are around it. The setup lives in a dialog that nobody can review, runs cannot be scheduled, the 200-variable limit of the built-in engine arrives sooner than expected, and the sensitivity report is a separate sheet that is overwritten on the next run. SciPy ships the HiGHS solver, one of the fastest open-source linear and integer solvers, behind two functions — linprog and milp — so the same model becomes a short script that reads its data from the workbook and writes the plan back. This page is part of Excel Formula Equivalents in pandas.

Prerequisites

pip install scipy pandas openpyxl

production_plan.xlsx holds a classic product-mix problem, the kind usually set up in Solver. The Products sheet lists four products with their profit per unit, the assembly hours, finishing hours and metres of wood each one uses, and the most the market will take. The Resources sheet says how much of each resource is available this month. The question: how many of each product should the workshop make to earn the most?

Step 1 — Read the model's data from the workbook

In Solver the data and the model are tangled together in cells. In Python they separate cleanly: the numbers stay in the workbook, where the planners maintain them, and the script turns them into arrays:

import numpy as np
import pandas as pd

products = pd.read_excel("production_plan.xlsx", sheet_name="Products")
resources = pd.read_excel("production_plan.xlsx", sheet_name="Resources")

profit = products["Profit"].to_numpy()
usage = products[resources["Resource"]].T.to_numpy()    # one row per resource
available = resources["Available"].to_numpy()
caps = products["Max demand"].to_numpy()
print(usage)
print(available, caps)

usage is the constraint matrix: row i, column j is how much of resource i one unit of product j consumes. Building it by selecting the columns named on the Resources sheet means adding a resource is a matter of adding a row there and a column on Products — the script does not change.

Step 2 — Solve the linear programme

linprog minimises, so maximising profit means minimising negative profit. Resource limits go in as A_ub @ x <= b_ub, and the demand caps as bounds on each variable:

from scipy.optimize import linprog

res = linprog(-profit, A_ub=usage, b_ub=available,
              bounds=list(zip(np.zeros(len(caps)), caps)), method="highs")
print(res.status, res.message)
plan = products[["Product"]].assign(Quantity=res.x.round(2))
print(plan.to_string(index=False))
print(f"profit {-res.fun:,.2f}")

The best plan makes no chairs, the full 120 tables, about 274 shelves and 131 desks, for a profit of about 35,114. Always check res.status before using the answer: 0 is optimal, 2 means the constraints contradict each other (infeasible), and 3 means the objective can grow without limit — usually a missing constraint. Solver reports the same outcomes in its result dialog; in a scheduled job they need to stop the run.

Step 3 — Whole units with milp

Nobody makes 274.29 shelves. Solver has an "int" constraint for this; SciPy has milp, which takes the same data in matrix form plus an integrality flag per variable:

from scipy.optimize import milp, LinearConstraint, Bounds

res_int = milp(-profit,
               constraints=LinearConstraint(usage, -np.inf, available),
               integrality=np.ones(len(profit)),
               bounds=Bounds(0, caps))
print(res_int.status, res_int.message)
plan["Whole units"] = res_int.x.round().astype(int)
print(plan.to_string(index=False))
print(f"profit {-res_int.fun:,.0f}; resources used {usage @ res_int.x}")

Rounding the continuous answer down would have been feasible but not necessarily best. The integer solution happens to be close to it — one chair appears, filling a gap the rounding leaves — at a profit of 35,110. On larger problems the integer optimum can look quite different from the rounded continuous one, which is why the integer solver exists.

Step 4 — Read the sensitivity report

Solver's sensitivity report answers "what is an extra hour of finishing worth?" linprog returns the same information for the continuous model: the slack of each constraint and its marginal value, the shadow price:

report = resources.assign(
    Used=(usage @ res.x).round(1),
    Slack=res.ineqlin.residual.round(1),
    ShadowPrice=(-res.ineqlin.marginals).round(2),
)
print(report.to_string(index=False))
print("value of one more unit of demand:", dict(zip(products["Product"], ((-res.upper.marginals).round(2) + 0.0).tolist())))

Finishing time is the real bottleneck: every hour is used, and one more would add about 38.57 of profit. Wood is also fully used, but a metre is worth only about 3.57. Assembly has over 50 hours to spare, so its shadow price is zero — buying more assembly capacity would earn nothing. Among the demand caps, only the table limit binds: one more table of demand would be worth about 2.14. That is the list a production manager needs: pay up to 38 an hour for overtime in finishing, do not bother with assembly.

Step 5 — Add a business rule

Real plans have rules that the pure optimum ignores. Suppose the showroom needs at least 50 chairs. In Solver that is another row in the constraint list; here it is a lower bound:

lower = np.zeros(len(caps))
lower[products["Product"].tolist().index("Chair")] = 50
res_rule = milp(-profit, constraints=LinearConstraint(usage, -np.inf, available),
                integrality=np.ones(len(profit)), bounds=Bounds(lower, caps))
plan["With 50 chairs"] = res_rule.x.round().astype(int)
print(plan.to_string(index=False))
print(f"profit {-res_rule.fun:,.0f} — the rule costs {-res_int.fun + res_rule.fun:,.0f}")

The rule costs only 35 of profit: the optimiser makes up for the chairs by switching some desks to shelves. Quantifying the cost of a rule like this is one of the most useful things an optimisation model does, and with the model in a script, each variant is a few lines instead of a new Solver setup.

Step 6 — Write the plan back to the workbook

Return the results to the file the planners already use, next to their data:

with pd.ExcelWriter("production_plan.xlsx", engine="openpyxl", mode="a", if_sheet_exists="replace") as xw:
    plan.assign(Profit=lambda d: d["Whole units"] * profit).to_excel(xw, sheet_name="Plan", index=False)
    report.to_excel(xw, sheet_name="Constraints", index=False)
with pd.ExcelFile("production_plan.xlsx") as xl:
    print(xl.sheet_names)

mode="a" with if_sheet_exists="replace" keeps the input sheets and replaces last run's results, so the workbook always shows the current plan. Append a DataFrame to an Existing Excel File with pandas explains the options.

Beyond linear models

linprog and milp cover linear objectives and constraints, which is most of what Solver's Simplex LP and integer settings are used for. Solver's GRG Nonlinear engine corresponds to scipy.optimize.minimize with method="SLSQP" or "trust-constr", which handle smooth nonlinear objectives with constraints — portfolio variance, pricing with a demand curve. For larger models with many named constraints, modelling libraries such as PuLP, Pyomo or OR-Tools let you write constraints by name instead of as matrices; check the current documentation of whichever you choose, since PuLP's 4.0 release, for one, changed how variables are created.

Performance notes

Excel's built-in Solver engine is limited to 200 decision variables, which a realistic planning model — products by week by site — passes quickly. HiGHS has no such limit: linear models with tens of thousands of variables solve in seconds, and integer models of a few thousand variables are usually fine, though integer problems can get hard in ways that have little to do with their size. Pass options={"time_limit": 60} to milp so a difficult instance returns its best solution so far instead of running all night, and check res.status to see whether it was proven optimal.

Common pitfalls

• Forgetting that linprog minimises. Negate the objective to maximise, and negate the result back.
• Using the answer without checking status. An infeasible model still returns an object; only status == 0 means optimal.
• Rounding a continuous solution. Rounding up can break constraints and rounding down can leave profit on the table; use milp for whole units.
• Shadow prices from the integer model. Marginals are well defined for the continuous problem; read sensitivity from linprog, not milp.
• Units mixed across rows. Hours, metres and kilograms in one matrix are fine, but each row's available amount must use that row's unit.

Conclusion

Keep the numbers in the workbook and move the model into a script: read the profit, usage matrix, availability and caps with pandas, solve with linprog for the continuous plan and its shadow prices, switch to milp for whole units and business rules, check the status every time, and write the plan and the constraint report back to the workbook. The result is Solver's answer, reviewable, repeatable and without its size limits.

Frequently asked questions

What is the Python equivalent of Excel Solver? For linear and integer models, scipy.optimize.linprog and scipy.optimize.milp, which use the HiGHS solver. For nonlinear models, scipy.optimize.minimize. Modelling libraries such as PuLP, Pyomo and OR-Tools add a friendlier way to write large models.

How do I maximise instead of minimise with linprog? Pass the negative of the objective coefficients and negate the returned objective value. The optimal decision variables are the same.

How do I get integer solutions like Solver's int constraint? Use scipy.optimize.milp and set integrality to 1 for every variable that must be a whole number. linprog alone always returns continuous values.

Can Python produce Solver's sensitivity report? For linear models, yes: linprog's result has ineqlin.residual (slack) and ineqlin.marginals (shadow prices) for each constraint, and upper.marginals for variable bounds. Negate the marginals when you maximised by negating the objective.

Related

• Up one level: Excel Formula Equivalents in pandas — Excel functions and tools translated to Python.
• Goal Seek in Python Instead of Excel — hitting a single target instead of optimising.
• Allocate Shared Costs Across Departments — another allocation problem, solved with rules.
• SUMPRODUCT and Weighted Average in pandas — the matrix product behind resource usage.