Prescriptive analytics: optimisation with Excel Solver

In short

Prescriptive analytics answers "what should we do?" — it recommends the best decision, where descriptive analytics reports what happened and predictive analytics estimates what will happen. Its workhorse is mathematical optimisation: maximise or minimise an objective (profit, cost, time, risk) by choosing values for decision variables subject to constraints, and Excel's Solver add-in is the tool most Indian MBA programmes teach it on. Syllabi under UGC CBCS and AICTE model MBA/PGDM curricula examine it as Operations Research / Business Analytics — formulation, graphical and simplex solution, duality and sensitivity, transportation, assignment and integer programming — and ICAI/ICMAI cost-management papers include LP formulation. In placements, consulting and analytics recruiters (Deloitte, EY, Accenture, Mu Sigma, ZS, Flipkart and Amazon supply chain, Delhivery, refinery and FMCG planning teams) ask candidates to formulate a model on paper and then build it in a spreadsheet.

Explained

Where optimisation sits in analytics

• Descriptive — dashboards and summaries of the past; diagnostic — why it happened; predictive — regression/ML forecasts; prescriptive — optimisation, simulation and decision analysis that choose an action.
• A prescriptive model usually *consumes* a predictive output: a demand forecast becomes the right-hand side of a capacity constraint.
• Three prescriptive families: optimisation (best decision under constraints), simulation (behaviour of a system under uncertainty, e.g. Monte Carlo), heuristics/rules (good-enough decisions when the model is too large).
• Optimisation needs a *controllable* decision, a *measurable* single objective, and *binding* constraints. With no constraint, or nothing to decide, there is no optimisation problem.

Formulating the model (the part that carries exam marks)

1. Decision variables — what the manager actually controls: units of each product, tonnes shipped on a lane, rupees in each asset, 1/0 for open-or-close. Define units explicitly (e.g. x₁ = chairs per week).
2. Objective function — one linear (or nonlinear) expression in those variables, to be maximised or minimised; it must be a single cell in Excel.
3. Constraints — resource limits (≤), minimum requirements (≥), balance equations (=), and non-negativity (x ≥ 0).
4. Sign and units discipline — every constraint row must be in one consistent unit (machine-hours, kg, ₹).
5. Assumptions of a linear programme: *proportionality* (output and resource use scale linearly), *additivity* (no interaction terms), *divisibility* (fractional values allowed — relaxed by integer programming), *certainty* (coefficients known), *finiteness* and *non-negativity*.
6. Standard form: Max Z = cᵀx subject to Ax ≤ b, x ≥ 0. Any minimisation becomes a maximisation of −Z; a ≥ constraint is multiplied by −1; an unrestricted variable is split as x = x⁺ − x⁻.
7. Feasible region of an LP is a convex polyhedron; the optimum, if it exists, lies at a corner point (extreme point) — the basis of the graphical method (two variables) and of the simplex method.
8. Possible outcomes: unique optimum, multiple optima (objective line parallel to a binding constraint), unbounded (open feasible region in the improving direction — usually a missing constraint), infeasible (contradictory constraints), degenerate (more constraints binding at a corner than variables).

Building and running Solver, step by step

• Load it once: File → Options → Add-ins → Manage: Excel Add-ins → Go → tick Solver Add-in. It then sits on the Data tab as *Solver* (Mac: same path under Tools).
• Lay the sheet out in blocks — one row of changing cells, a coefficient matrix, a column of SUMPRODUCT left-hand sides, a sign column, a right-hand-side column, and the objective cell.
• Use =SUMPRODUCT($B$4:$D$4, B7:D7) for each constraint and for the objective; never type constants inside formulas — keep every coefficient in a labelled input cell.
• Set Objective: the single objective cell → To: Max / Min / Value Of.
• By Changing Variable Cells: the contiguous (or multi-area) range of decision cells.
• Subject to the Constraints: Add → cell reference, relation (≤, =, ≥, int, bin, dif for all-different), constraint value or cell. Reference whole ranges to add many constraints at once.
• Tick Make Unconstrained Variables Non-Negative for business models (or add x ≥ 0 explicitly).
• Choose the Solving Method, press Solve, then *Keep Solver Solution* and request the Answer, Sensitivity and Limits reports.
• Options worth knowing: Constraint Precision (default 1e-6), Integer Optimality (%) — default 1% means Solver may stop within 1% of the true integer optimum, so set it to 0 for exam-exact answers; Max Time / Iterations; Use Automatic Scaling when magnitudes differ wildly; Multistart and Population Size for GRG; Mutation Rate and Random Seed for Evolutionary.
• Capacity of the shipped Solver: 200 decision variables and 100 constraints besides bounds and integer constraints. Larger models need Analytic Solver (commercial) or the free OpenSolver add-in (CBC/COIN-OR), which also reads the same sheet layout. Python's PuLP/SciPy is the usual next step.
• Goal Seek vs Solver: Goal Seek changes *one* cell to hit *one* target value — no objective, no constraints. Solver optimises many cells under constraints. Data Table/Scenario Manager only enumerate; they do not optimise.

The three solving engines

• Integer / binary variables are solved by branch and bound on top of the LP: relax the integrality, branch on a fractional variable, bound branches by the relaxed objective. The LP relaxation's objective is always at least as good as the integer optimum (an upper bound for a maximisation).
• Binary modelling tricks: y = 1 if a facility opens; linking constraint x ≤ M·y (big-M) forces x = 0 when y = 0; "at most k of n" is Σy ≤ k; "if A then B" is y_A ≤ y_B; either-or uses a big-M pair. Keep M as small as truthfully possible — a huge M weakens the bound and slows the search.
• Rounding an LP answer is not the same as solving the integer problem: the rounded point may be infeasible or clearly suboptimal.

Reading the reports: duality and sensitivity

• Answer report — original and final values of the objective and variables; for each constraint, *Status* (Binding / Not Binding) and Slack (unused resource). Binding constraints are the bottlenecks.
• Sensitivity report (not produced when integer or binary constraints are present; GRG Nonlinear also produces one, but with reduced gradients and Lagrange multipliers in place of reduced costs and shadow prices):
  • Shadow price (dual value) of a constraint = change in the optimal objective per one-unit increase in that constraint's right-hand side, valid only within the allowable increase/decrease range. A non-binding constraint has shadow price 0.
  • Reduced cost of a variable = the amount by which its objective coefficient must improve before it becomes worth using; a variable in the solution at a positive level has reduced cost 0.
  • Allowable increase/decrease on an objective coefficient = the range over which the current solution stays optimal (the objective value changes, the variable values do not).
• Duality: every primal LP has a dual; the dual of a max-profit resource problem is a min-cost problem whose variables are the resource prices. Weak duality: any feasible dual value ≥ any feasible primal value (for a max primal). Strong duality: at the optimum they are equal. Complementary slackness: a constraint with slack has a zero shadow price, and a variable with a positive value has a zero reduced cost.
• Shadow prices tell you the maximum rent worth paying for an extra machine-hour, the value of overtime, or the true cost of a quality specification — the usual interview follow-up.
• A degenerate solution can have multiple valid sets of shadow prices; do not over-interpret them, and never extrapolate a shadow price beyond its allowable range.

Standard model templates

• Product mix: max Σ (contribution per unit × units) s.t. resource rows ≤ capacity, demand caps, minimum order quantities.
• Transportation: variables x_ij (units plant i → market j); min Σ c_ij x_ij s.t. Σ_j x_ij ≤ supply_i and Σ_i x_ij ≥ demand_j. Balanced if Σsupply = Σdemand; otherwise add a dummy row/column with zero cost. Hand methods: North-West Corner, Least Cost, Vogel's approximation for the initial solution, then MODI/u-v for optimality; Solver does it in one pass.
• Assignment: a transportation problem with all supplies and demands = 1 and binary x_ij — the Hungarian method in theory papers, bin constraints in Solver.
• Blending / diet: min cost per batch s.t. nutrient or octane specifications ≥/≤ limits and component fractions summing to 1 — refinery and cattle-feed LPs.
• Workforce scheduling: integer variables = number starting on each shift/day; each day's coverage constraint sums the shifts that cover it.
• Capital budgeting / knapsack: binary project selection under a budget.
• Portfolio: min variance = w'Σw (GRG Nonlinear) s.t. Σw = 1 and expected return ≥ target — Markowitz; the efficient frontier is traced by re-solving at several targets.
• Multi-period production/inventory: balance equation I_t = I_{t−1} + P_t − D_t, with capacity and storage limits — the one formulation students most often forget to write.

Limitations and good practice

• The answer is only as good as the coefficients; LP treats every number as certain, so run what-if sensitivity rather than quoting one optimum.
• Linearity is often a simplification: quantity discounts, setup costs and economies of scale are genuinely nonlinear or integer.
• GRG returns a local optimum; the same model can give different answers from different start values — always report the engine and starting point.
• Solver gives no answer to "how confident are we?" — combine with simulation for risk.
• Spreadsheet risk is real: hard-coded constants, broken ranges after inserting rows, and Solver settings that are not saved with the workbook if not stored as a Load/Save Model block.
• Solver messages: *"Solver found a solution. All constraints and optimality conditions are satisfied"* (clean optimum); *"Solver converged to the current solution"* (nonlinear, local); *"Solver cannot improve the current solution"*; *"Solver could not find a feasible solution"* (conflicting constraints or a wrong relation sign); *"The Objective Cell values do not converge"* (unbounded — a missing constraint); *"The linearity conditions required by this LP Solver are not satisfied"* (an IF/ABS/product of variables is present — switch engine or re-model).

Key points

• Prescriptive analytics - recommends the action; optimisation and simulation are its main tools.
• Decision variables, objective, constraints - the three parts of every formulation; marks are given for defining units.
• LP assumptions - proportionality, additivity, divisibility, certainty, non-negativity.
• Corner-point theorem - an LP optimum always occurs at an extreme point of the convex feasible region.
• Simplex LP - global optimum for linear models and the engine whose Sensitivity report gives true shadow prices (GRG's gives Lagrange multipliers; Evolutionary produces only the Answer report).
• GRG Nonlinear - smooth nonlinear models, local optimum, use Multistart.
• Evolutionary - non-smooth functions (IF, ABS, VLOOKUP); no optimality guarantee.
• Binding vs non-binding - a binding constraint has zero slack and is the bottleneck.
• Shadow price - value of one extra unit of a scarce resource, valid only inside the allowable RHS range.
• Reduced cost - improvement needed in an unused variable's coefficient before it enters the solution.
• Complementary slackness - slack ⇒ zero shadow price; positive variable ⇒ zero reduced cost.
• Integer/binary variables - solved by branch and bound; Integer Optimality default 1% must be set to 0 for exact answers.
• Big-M linking - x ≤ M·y switches a quantity off when the binary y = 0.
• LP relaxation bound - its objective is never worse than the integer optimum; rounding is not solving.
• Transportation balance - add a dummy source/destination with zero cost when supply ≠ demand.
• Assignment - transportation with unit supplies/demands and binary variables (Hungarian method by hand).
• Solver limits - 200 variables, 100 constraints in the shipped add-in; OpenSolver/PuLP for larger models.
• Goal Seek ≠ Solver - one cell, one target, no constraints.

Formulas and examples

Example 1 — Product mix, shadow price and sensitivity (Simplex LP). A Pune furniture unit makes chairs (x) and tables (y). Contribution ₹300/chair and ₹500/table. Carpentry: 2 h/chair, 4 h/table, 200 h available. Finishing: 3 h/chair, 2 h/table, 180 h available.

• Model: Max Z = 300x + 500y s.t. 2x + 4y ≤ 200; 3x + 2y ≤ 180; x, y ≥ 0.
• Corner points: (0,0) → 0; (60,0) → ₹18,000; (0,50) → ₹25,000; intersection of both constraints.
• Solve 2x + 4y = 200 (i.e. x + 2y = 100) with 3x + 2y = 180: subtracting gives 2x = 80 → x = 40, then y = (100 − 40)/2 = 30.
• Z* = 300(40) + 500(30) = 12,000 + 15,000 = ₹27,000; both constraints are binding (slack 0).
• Dual: 2u + 3v = 300 and 4u + 2v = 500 → multiply the first by 2: 4u + 6v = 600; subtract: 4v = 100 → v = ₹25/finishing hour, and u = (300 − 75)/2 = ₹112.50/carpentry hour. Check 200(112.5) + 180(25) = 22,500 + 4,500 = ₹27,000 ✓.
• Verify the shadow price: with 210 carpentry hours, x + 2y = 105 and 3x + 2y = 180 → 2x = 75, x = 37.5, y = 33.75, Z = 11,250 + 16,875 = ₹28,125 = 27,000 + 10 × 112.50 ✓. So overtime carpentry is worth paying up to ₹112.50/hour, finishing only ₹25/hour.

Example 2 — Transportation (minimise freight). Supplies: P1 = 100, P2 = 150 units. Demands: M1 = 80, M2 = 90, M3 = 80 (total 250 = 250, balanced). Cost ₹/unit:

• Variables x_ij ≥ 0, six of them; Min Z = Σ c_ij x_ij; row sums = supply, column sums = demand (all =). Simplex LP.
• Cost advantage of P1 over P2: M1 −1 (P1 cheaper), M2 +3, M3 +1 → load P1 where it is cheapest: P1→M1 = 80; the remaining 20 of P1 goes to M3 (penalty 1) rather than M2 (penalty 3).
• Solution: P1→M1 = 80, P1→M3 = 20, P2→M2 = 90, P2→M3 = 60.
• Z* = 80(4) + 20(9) + 90(3) + 60(8) = 320 + 180 + 270 + 480 = ₹1,250.
• Compare the alternative P1→M1 = 80, P1→M2 = 20, P2→M2 = 70, P2→M3 = 80: 320 + 120 + 210 + 640 = ₹1,290 — worse by ₹40, confirming optimality direction.

Example 3 — Capital budgeting with binary variables (branch and bound). Budget ₹100 lakh. Projects (cost, NPV in ₹ lakh): A (30, 18), B (40, 22), C (50, 26), D (20, 11). Max Σ NPV·yⱼ s.t. Σ cost·yⱼ ≤ 100, yⱼ ∈ {0,1}.

• Feasible subsets: A+B+D = 90 → 51; A+C+D = 100 → 55; B+C = 90 → 48; A+B = 70 → 40; B+C+D = 110 ✗; A+B+C = 120 ✗.
• Integer optimum: A, C, D — cost ₹100 lakh, NPV ₹55 lakh.
• LP relaxation (variables allowed fractional) ranks by NPV/cost: A 0.600, B 0.550, D 0.550, C 0.520 → take A (30), B (40), D (20) = 90 for NPV 51, then 10/50 of C adds 0.2 × 26 = 5.2 → bound = 56.2. The relaxed 56.2 exceeds the true 55, which is why rounding the LP answer (A, B, D) would have lost ₹4 lakh of NPV.

Example 4 — Nonlinear objective with GRG (EOQ). A Chennai distributor: annual demand D = 24,000 units, ordering cost Co = ₹1,200/order, holding cost Ch = ₹30/unit/year. Minimise TC(Q) = (24,000/Q)(1,200) + (Q/2)(30), Q > 0, by changing Q with GRG Nonlinear.

• Q* = √(2 × 24,000 × 1,200 / 30) = √1,920,000 ≈ 1,385.6 units.
• Ordering cost = (24,000/1,385.6) × 1,200 = 17.32 × 1,200 ≈ ₹20,785; holding = (1,385.6/2) × 30 = 692.8 × 30 ≈ ₹20,784 — equal at the optimum, as theory requires.
• TC* = √(2 × 24,000 × 1,200 × 30) = √1,728,000,000 ≈ ₹41,569/year. With an added "order in multiples of 500" rule the problem becomes integer: Q = 1,500 gives (16 × 1,200) + (750 × 30) = 19,200 + 22,500 = ₹41,700 — only ₹131 worse, a useful sensitivity point.

Where it is used

• Refining and petrochemicals — Indian Oil, BPCL, HPCL and Reliance run crude-selection and product-blending LPs (the classic industrial use of linear programming).
• FMCG and pharma supply chain — HUL, ITC, Dabur, Sun Pharma: plant-to-depot transportation, production planning, distributor allocation; Solver for plant-level models, commercial solvers for the network.
• E-commerce and logistics — Flipkart, Amazon India, Delhivery, Blue Dart: fleet and route assignment, warehouse slotting, delivery-slot capacity, workforce rostering.
• Banking and finance — portfolio and asset-liability optimisation, branch cash-in-transit planning, loan-portfolio mix under capital constraints; equity research analysts use Solver for scenario and valuation back-solving.
• Consulting and analytics roles — Business Analyst, Supply Chain Analyst, Revenue Management Analyst, Pricing Analyst, Operations Research Scientist at Deloitte, EY, Accenture, ZS, Mu Sigma, airlines (IndiGo, Air India) and hotel revenue teams.
• Agriculture, power and public planning — cattle-feed and fertiliser blending, crop-mix planning, unit-commitment and merit-order dispatch at state load despatch centres, bus-crew scheduling at state transport undertakings.

Interview questions

• Q: How does prescriptive analytics differ from predictive analytics? A: Predictive estimates what will happen (a forecast); prescriptive chooses what to do about it by optimising an objective under constraints, often using the forecast as an input.
• Q: What are the components of a linear programming model? A: Decision variables with stated units, a single linear objective to maximise or minimise, linear constraints with consistent units, and non-negativity restrictions.
• Q: Which Solver engine would you pick and why? A: Simplex LP if the objective and all constraints are linear — it guarantees the global optimum and gives shadow prices; GRG Nonlinear for smooth nonlinear formulas; Evolutionary when IF, ABS, MAX or VLOOKUP make the model non-smooth.
• Q: What is a shadow price and how would you use it in a business meeting? A: The change in the optimal objective per unit increase in a binding constraint's right-hand side — it is the most you should pay for an extra machine-hour or kilogram, valid only within the allowable range.
• Q: Why does Solver not produce a Sensitivity report for my model? A: Because it contains integer or binary constraints (or you solved with Evolutionary, which produces only the Answer report); duals are not defined for an integer programme, so solve the LP relaxation if you need them.
• Q: Solver says the objective cell values do not converge. What went wrong? A: The model is unbounded — a constraint is missing or a sign is wrong, so the objective can improve indefinitely.
• Q: Solver reports no feasible solution. How do you debug it? A: Check relation signs and units, confirm total supply ≥ total demand, relax suspected constraints one at a time or add slack/penalty variables to find the conflicting pair.
• Q: Can you just round the LP answer to get integers? A: No — the rounded point can be infeasible or well away from the integer optimum; add int/bin constraints and let branch and bound solve it.
• Q: What does a reduced cost of ₹40 on an unused product mean? A: Its contribution must rise by ₹40 per unit (or its resource usage fall equivalently) before producing it becomes worthwhile.
• Q: How do you model "open at most 3 of 8 warehouses, and ship only from open ones"? A: Binary y_j for each site with Σy_j ≤ 3, plus linking constraints x_ij ≤ M·y_j so flow is forced to zero from a closed site.
• Q: GRG gave two different answers on two runs. Why? A: It converges to a local optimum that depends on the starting values; use Multistart with bounds on every variable, or reformulate the model as linear or convex.
• Q: When would you stop using Excel Solver? A: Beyond its 200-variable / 100-constraint limit, or when runs get slow or need version control — move to OpenSolver, Python PuLP/Pyomo with CBC/HiGHS, or Gurobi/CPLEX.
• Q: How do you handle uncertainty in the coefficients? A: Run sensitivity and scenario analysis, use the allowable ranges from the report, and for serious uncertainty use stochastic programming or simulation-optimisation rather than one deterministic answer.

Common mistakes

• Not defining decision variables with units, then writing constraints that mix hours with kilograms or weeks with months.
• Forgetting non-negativity (unticking "Make Unconstrained Variables Non-Negative") and accepting negative production or shipments.
• Hard-coding coefficients inside SUMPRODUCT or the objective formula, so the model cannot be audited or re-run with new data.
• Reporting an integer answer as exact when Integer Optimality is left at the default 1%.
• Reading a shadow price far outside its allowable increase, or quoting shadow prices from a degenerate solution as if unique.
• Using GRG or Evolutionary on a model that is actually linear — slower, no duals, and no guarantee of the optimum.
• Treating "Solver converged to the current solution" as proof of the global optimum in a nonlinear model.
• Omitting the inventory balance equation in multi-period problems, or the dummy row/column in an unbalanced transportation problem.

Related topics

Business analytics: framing problems and data-driven decisions · Linear and logistic regression · Time series forecasting · Business intelligence: Power BI and Tableau · Python for data analysis: NumPy and pandas · Operations and supply chain management