Solver is the most under-used feature in Excel. The pattern is: define the objective, define the variables, define the constraints, click Solve. The senior uses Solver for resource allocation, scheduling, and transportation problems. The right tool for problems under 100 variables; Python for anything above.
The three Solver methods
Solver offers three solving methods. Simplex LP for linear problems - the objective and constraints are linear equations. GRG Nonlinear for nonlinear problems - the objective or constraints include nonlinear functions. Evolutionary for global optimisation - slow but handles non-smooth problems. The senior uses Simplex LP for 90% of engagements and avoids GRG Nonlinear because the algorithm is unreliable on common finance and operations problems.
The classic problems
Solver shines on four classic problems. (1) The transportation problem - minimise shipping cost from warehouses to customers subject to supply and demand. (2) The assignment problem - assign N workers to M tasks to minimise cost or maximise value. (3) The production mix problem - choose the production quantities to maximise profit subject to resource constraints. (4) The workforce scheduling problem - assign shifts to employees to minimise cost subject to coverage and availability constraints.
How the senior ships Solver
The senior ships Solver problems in a one-tab workbook with three sections: Inputs (the variable cells, the constraints, the cost/benefit matrix), Calculations (the objective formula), and Results (the optimal solution from Solver). The senior uses named ranges for every cell, documents every constraint in a Notes column, and validates the solution against a hand-calc on a small instance. The pattern ships in one to two weeks for a typical problem and replaces a manual planning process that consumes a senior manager a day every month.
When to move to Python
The standard Solver tops out at 200 variables and 200 constraints. For problems above that, the senior moves to Python (scipy.optimize for LP/IP, PuLP for modelling). The transition is straightforward - the same problem formulation, the same objective and constraints, just a different solver. The senior uses Excel Solver for problems under 100 variables, Python for anything above. The pattern is the same; the tool scales.
