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 |
About this data: Teaching sample for a fictional shop “Maple Bakery Co”. Figures and names (e.g. Aarti Kulkarni) are made up for this lesson on solver brief.
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
In Solver, many students forget the constraints – then Solver gives ridiculous answers like "negative crates" or "10,000 crates". Add every real-world limit (space, supply, minimum stock, integer) as a constraint. And for a linear model, choose Simplex LP – it gives the best answer quickly and reliably.
Ravindra Bagale's Tip – मराठी
Solver मध्ये बरेच students constraints विसरतात – मग Solver "negative crates" किंवा "10,000 crates" सारखं हास्यास्पद उत्तर देतो. प्रत्येक खऱ्या जगातली मर्यादा (space, supply, minimum stock, integer) constraint म्हणून घाला. आणि linear model साठी Simplex LP निवडा – ते लवकर आणि खात्रीने best उत्तर देतं.
Ravindra Bagale's Tip – हिंदी
Solver में बहुत से students constraints भूल जाते हैं – फिर Solver "negative crates" या "10,000 crates" जैसे हास्यास्पद जवाब देता है. असली दुनिया की हर सीमा (space, supply, minimum stock, integer) को constraint के रूप में डालो. और linear model के लिए Simplex LP चुनो – वह जल्दी और भरोसे से best जवाब देता है.
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!
Loading practice tasks...
- Kurta profit (Easy): Profit from kurtas (row 2).
- Total profit (objective) (Easy): Total profit of all 3 products.
- Total units (Easy): Total of E2:E4.
- Machine hours used (Medium): Machine hours used by the plan.
- Labour hours used (Medium): Labour hours used by the plan.
- Machine hours left (Medium): Machine limit C5 minus machine hours used.
- Plan fits machines? (Medium): Are machine hours used at most C5? (TRUE or FALSE)
- Plan fits both limits? (Hard): Do both machine and labour hours fit their limits? (TRUE or FALSE)
- Profit per machine hour (Hard): Highest profit per machine hour among the 3 products, rounded to 2 decimals.
- Best product per labour hour (Hard): Product with the highest profit per labour hour.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
