11.5 Solver (Brief)
Solver finds the best value of a formula (maximum, minimum or a target) by changing several input cells, subject to constraints (मर्यादा / अटी). It is a free add-in.
Steps in Excel – enable Solver
- File › Options › Add-ins › Manage: Excel Add-ins › Go… › tick Solver Add-in › OK.
- Solver appears at Data › Analyze › Solver.
Worked example – cold-room crate mix (fictional). The Kothrud store's cold room has 60 crate slots. Each milk crate (Gokul/Amul) earns ₹180 margin per day, each fruit crate (Nashik grapes, Nagpur oranges) ₹260. The supplier can send at most 25 fruit crates, and the store must keep at least 20 milk crates.
| Cell | Item | Value |
|---|---|---|
| B2 | Milk crates (changing) | 20 |
| B3 | Fruit crates (changing) | 20 |
| B5 | Slots used | =B2+B3 |
| B6 | Daily margin (objective) | =180*B2+260*B3 |
Steps in Excel – run Solver
- Data › Analyze › Solver.
- Set Objective:
$B$6› Max. - By Changing Variable Cells:
$B$2:$B$3. - Add constraints:
$B$5 <= 60;$B$3 <= 25;$B$2 >= 20;$B$2:$B$3 = int(integer). - Tick Make Unconstrained Variables Non-Negative › Select a Solving Method: Simplex LP (the model is linear) › Solve › Keep Solver Solution (optionally tick Answer report).
Solution: 35 milk crates and 25 fruit crates → daily margin 180 × 35 + 260 × 25 = ₹12,800. Solver can also minimise cost (e.g. rider shift planning) – a question of this type ("optimise a product mix with Solver") is reported in interviews, see Module 18.
Ravindra Bagale's Tip
Solver madhe khup students constraints visartat – mag Solver "negative crates" kiwa "10,000 crates" sarkhe hasyaspad uttar deto. Pratyek kharya jagatli maryada (space, supply, minimum stock, integer) constraint mhanun ghala. Ani linear model sathi Simplex LP nivda – te jaldi aani khatrine best uttar deta.
Practice task
Add a third product (bakery crates of ladi pav, ₹150 margin, max 15) to the Solver model and re-solve. Save the Answer report.
Thodkyaat sangaycha tar (quick recap)
Model nehmi inputs + formulas ne banva; ek input ne target gathaycha = Goal Seek; named cases = Scenario Manager (summary static aste); ek/don inputs chi sensitivity = Data Table (row/column input cell nit lava); anek inputs + constraints ne best uttar = Solver. Aata pudhe jaauya – Macros aani VBA!