Solver and Business Optimization

Introduction

Business decisions often require managers to allocate scarce resources among competing activities. A manufacturer may decide how many units of each product to make; a bank may allocate a limited lending budget; a service organization may schedule employees across shifts; and a project team may minimize cost while meeting performance requirements. These are optimization problems: problems in which the decision-maker seeks the best feasible outcome according to a stated objective.

Microsoft Excel Solver provides a practical spreadsheet implementation of mathematical optimization. Its value is not that it replaces managerial judgment, but that it makes a formal decision model operational. A well-designed Solver model translates managerial assumptions into decision variables, an objective function, and constraints; Solver then searches for values of the decision variables that optimize the objective while satisfying the constraints.

At MBA level, Solver should be treated as a modelling and governance tool rather than merely a button in Excel. The quality of the solution depends on the quality of the formulation, the validity of the data, the assumptions about the business environment, and the interpretation of the results.

Learning Objectives

By the end of this chapter, you should be able to:

1. Distinguish decision variables, parameters, objective functions, and constraints.
2. Formulate linear business optimization models.
3. Build and configure an Excel Solver model.
4. Apply Solver to product mix, resource allocation, budgeting, scheduling, and profit or cost optimization.
5. Interpret feasibility, optimality, shadow prices, reduced costs, and sensitivity information.
6. Evaluate Solver assumptions, limitations, ethical risks, and managerial implications.

1. The Structure of an Optimization Model

An optimization model has four essential elements.

Decision variables

Decision variables represent controllable choices. Examples include units of product A manufactured, dollars allocated to advertising, employees assigned to a shift, or loans approved. They are represented mathematically by symbols such as x₁, x₂, and x_j. Their values are determined by the model.

A decision variable must be operationally meaningful. “Production quality” is not normally a decision variable unless management can directly choose its level. A variable should also have a clear unit, such as units, hours, dollars, employees, or tonnes.

Parameters

Parameters are known or estimated inputs. Examples include unit profit, labour hours per unit, available machine capacity, demand limits, minimum service requirements, and fixed costs. Parameters are not chosen by Solver; they describe the business environment assumed in the model.

Objective function

The objective function measures what the organization wants to maximize or minimize. A general linear objective is:

Here, x_j is decision variable j, c_j is its contribution to the objective, and Z is the total objective value. In a profit model, c_j may be contribution margin per unit; in a cost model, it may be cost per activity unit. Unit consistency is essential: if c_j is dollars per unit, then c_jx_j is dollars.

Constraints

Constraints define the feasible region: the combinations of decisions that satisfy business, technical, legal, or policy requirements. A general linear constraint is:

where a_ij is the amount of resource i used by decision j, and b_i is the available amount, required amount, or target for constraint i. For example, if each unit of A uses 2 labour hours and each unit of B uses 3 hours, with 120 hours available, the labour constraint is 2x_A + 3x_B ≤ 120.

Non-negativity is normally required:

A negative production quantity or negative number of employees has no practical meaning. Integer or binary restrictions are added when fractional decisions are impossible or inappropriate.

2. Linear Programming and Solver Model Types

A linear programming (LP) model has a linear objective and linear constraints. It assumes that contribution and resource use change proportionally with activity levels, and that the effects of activities are additive. The Excel Solver engine most appropriate for such models is Simplex LP.

A model is feasible if at least one combination of decision variables satisfies every constraint. It is infeasible when requirements conflict—for example, when minimum production requirements consume more labour than is available. A model is unbounded if the objective can improve indefinitely without violating constraints, often because a limiting constraint has been omitted.

Not all business models are linear. Nonlinear models may include percentage returns, economies of scale, ratios, or products of variables. Solver’s GRG Nonlinear method is designed for many smooth nonlinear models. Evolutionary is a heuristic method useful for discontinuous, non-smooth, or highly complex models, but it does not provide the same guarantee of global optimality as a correctly formulated LP solved by Simplex LP.

Integer and binary decisions

Integer restrictions require a variable to take whole-number values, such as the number of employees, trucks, branches, or projects. Binary variables take only 0 or 1 and often represent yes/no decisions:

For example, y_k = 1 may mean that project k is selected. A mixed-integer model can combine continuous production quantities with integer or binary strategic decisions. Such models are generally more computationally demanding and may require careful time-limit and optimality-gap settings.

3. Translating a Business Problem into Excel

A robust spreadsheet separates inputs, decision cells, calculations, and outputs. A typical layout contains:

The objective cell should contain a formula linked to the decision cells, usually through SUMPRODUCT. For example, if unit profits are in B5:C5 and quantities are in B6:C6, total profit can be calculated as:

=SUMPRODUCT(B5:C5,B6:C6)

If resource usage coefficients are in B10:C10, resource usage is:

=SUMPRODUCT(B10:C10,B6:C6)

This arrangement is preferable to typing calculated values directly because it makes the model auditable and allows Solver to change the decision cells dynamically.

Configuring Solver

The standard procedure is:

1. Enable Solver through Excel Add-ins if it is not visible on the Data tab.
2. Identify the objective cell.
3. Select Max or Min and specify the objective cell.
4. Select the changing variable cells.
5. Add constraints using the correct relation: ≤, =, or ≥.
6. Add integer or binary restrictions where necessary.
7. Select the solving method appropriate to the model.
8. Choose options such as “Make Unconstrained Variables Non-Negative” when appropriate.
9. Solve, inspect the result, and retain or reject the solution.
10. Test the solution through scenario and sensitivity analysis.

Solver should never be configured before the model logic is checked. A technically successful Solver run can still produce a meaningless answer if a formula, unit, or constraint is wrong.

4. Worked Example: Product-Mix Optimization

Problem formulation

An electronics firm produces products A and B. The contribution margins are $40 and $30 per unit, respectively. Product A requires 2 labour hours and 3 kilograms of material. Product B requires 1 labour hour and 2 kilograms of material. The firm has 100 labour hours and 150 kilograms of material. Demand is limited to 40 units of A and 60 units of B.

Let:

• x_A = units of A produced;
• x_B = units of B produced;
• objective coefficients = dollars per unit;
• labour coefficients = hours per unit;
• material coefficients = kilograms per unit.

The objective is:

Subject to:

All constraint terms have consistent units. For instance, 2x_A represents labour hours, and the right-hand side is also labour hours.

Excel implementation

Place products in columns B and C. Place unit contribution, labour use, and material use in rows. Put quantities in changing cells. Use SUMPRODUCT for total contribution and each resource-use calculation. In Solver, set the total contribution cell to Max, select the two quantity cells, and add the four constraints plus non-negativity.

The optimal solution is:

• x_A = 30 units;
• x_B = 40 units;
• labour used = 2(30) + 40 = 100 hours;
• material used = 3(30) + 2(40) = 170 kilograms.

This result violates the stated material capacity of 150 kilograms, so it is not feasible. The arithmetic exposes an important modelling discipline: a proposed solution must be checked against every constraint.

Solving the model correctly, the material constraint binds before the labour constraint at the relevant optimum. The intersection of labour and material constraints is obtained from:

Multiplying the first equation by 2 gives 4x_A + 2x_B = 200. Subtracting the second equation gives x_A = 50, which exceeds the demand limit for A. Therefore the relevant candidate involving the demand limit x_A = 40 is:

Profit is 40(40) + 30(15) = 2,050 dollars. Other corner points include (0,60), with profit $1,800, and (40,0), with profit $1,600. Thus the optimal feasible product mix is 40 units of A and 15 units of B, assuming the stated linear model and no additional constraints.

The example demonstrates why Solver is useful but not sufficient: model formulation and validation determine whether the answer is meaningful.

5. Resource Allocation, Budgeting, and Cost Optimization

Resource allocation

Resource allocation models distribute limited inputs across activities. A marketing manager might allocate a budget among search, social media, and television. If x_j is spending on channel j, r_j is expected incremental revenue per dollar, and B is the budget, a simplified model is:

This model is useful only if the response is approximately linear over the decision range. If advertising response diminishes with spending, a nonlinear or piecewise-linear formulation is more appropriate.

Budgeting and project selection

Suppose projects require investment and generate estimated strategic value. A binary variable y_k equals 1 if project k is selected. With budget B:

Additional constraints may require at least one digital project, prohibit mutually exclusive projects, or enforce dependencies. For example, selecting project C only if project A is selected can be represented by y_C ≤ y_A. Estimated value should not be treated as certainty; managers should test multiple scenarios.

Cost minimization

In a procurement model, the objective may be to minimize total purchasing cost while satisfying demand and supplier requirements:

Here, c_j is unit purchase cost, D is required quantity, and U_j is supplier capacity. If suppliers have fixed activation charges, binary variables are required; otherwise, the model may incorrectly split orders among suppliers without recognizing fixed costs.

6. Scheduling and Workforce Planning

Scheduling models assign people, machines, or activities to time periods. Let x_et represent the number of employees of type e assigned to period t. If demand in period t is d_t, and each employee supplies one unit of coverage, a coverage constraint is:

A cost-minimizing schedule may be:

subject to coverage, maximum working hours, employment availability, and labour-policy constraints. If an employee works across consecutive periods, linking constraints may be needed. Binary variables can represent whether a shift is opened or whether an employee is assigned to a particular shift.

Scheduling models require special attention to fairness and labour law. A mathematically cheap schedule may create excessive split shifts, unequal undesirable assignments, or non-compliance with rest requirements. Such considerations must be represented as constraints or evaluated outside the numerical model.

7. Interpreting Solver Results

Feasibility and binding constraints

A constraint is binding when it is satisfied exactly at the optimum. Its slack is the unused amount for a ≤ resource constraint:

A nonbinding resource has positive slack. A binding resource may be a bottleneck, but binding status alone does not establish that additional capacity has high economic value.

Shadow price

For a linear maximization model, the shadow price of a resource is the marginal change in the optimal objective value from increasing the right-hand side of that resource constraint by one unit, within the allowable sensitivity range and assuming other model assumptions remain fixed. If a machine-hour shadow price is $12, one additional hour is theoretically worth up to $12 in additional objective value, within that range. It is not necessarily the market purchase price, and it should not be extrapolated indefinitely.

Reduced cost

In a maximization model with nonnegative variables, the reduced cost of a variable at zero indicates how much its objective coefficient would need to improve, under the applicable LP sensitivity assumptions, before that variable could enter an alternative optimum. Interpretation depends on Solver’s reporting convention and model orientation; managers should read the report labels carefully rather than applying memorized signs.

Sensitivity and scenario analysis

Sensitivity analysis changes one parameter or a controlled set of parameters to assess solution stability. Scenario analysis changes coherent groups of assumptions—for example, a recession scenario may reduce demand, margins, and available cash together. A single optimal answer should not be presented as a forecast when key parameters are uncertain.

8. Illustrative Case: Nova Foods Capacity Decision

This is a fictional illustrative case.

Background and problem

Nova Foods produces premium meal kits and corporate catering boxes. Management must decide the monthly mix using limited kitchen labour, packaging material, and refrigerated storage. Meal kits yield a contribution of $18 each; catering boxes yield $30 each. Demand limits are 4,000 and 2,000 units, respectively. The operations director suspects that refrigerated storage, rather than labour, is the strategic bottleneck.

Analysis and approach

The analyst defines two continuous decision variables: meal-kit units and catering-box units. The model includes contribution margin, labour, packaging, storage, demand limits, and non-negativity. A second version adds integer restrictions because catering boxes are packed in batches of 10. The base model is solved with Simplex LP; the batch model is solved as a mixed-integer model.

Management then creates three scenarios: baseline demand, a 20% demand reduction, and a 15% increase in storage capacity. The output dashboard reports product quantities, contribution, resource utilization, slack, and changes from the baseline.

Outcome

The baseline model confirms that storage is binding and labour has slack. The shadow price of storage is positive, suggesting that additional storage could increase contribution, but only within the sensitivity range. The demand-reduction scenario changes the bottleneck: storage is no longer fully used, and the value of expansion becomes negligible. The integer model produces a slightly lower contribution than the continuous model because batch requirements restrict feasible quantities.

Learning points

1. The apparent bottleneck can change as demand and capacity assumptions change.
2. A continuous solution may be infeasible operationally when batch or staffing rules apply.
3. Shadow prices support capacity discussions but do not replace investment appraisal.
4. Scenario analysis is essential when forecasts are uncertain.
5. An optimization result should be communicated with assumptions, not as an unquestionable instruction.

9. Limitations, Governance, and Ethical Use

Solver optimizes the model, not reality. Common limitations include inaccurate forecasts, omitted constraints, double-counted resources, unrealistic linearity, and false precision in coefficients. Correlated assumptions can make scenario results appear more independent than they are. A model may also produce a mathematically optimal answer that conflicts with customer service, employee welfare, environmental commitments, or reputational risk.

Governance practices should include version control, documented assumptions, named model owners, protected formula cells, input validation, independent review, and a record of Solver settings. Important models should be tested with known cases and extreme values. Managers should retain the original input data and the Solver Answer and Sensitivity Reports used to support a decision.

Ethically, organizations should examine distributional effects. A staffing model that minimizes cost may disproportionately assign undesirable shifts to a vulnerable group. A lending allocation model may reproduce historical discrimination if its data or constraints encode biased practices. Optimization should therefore be combined with legal review, fairness metrics where relevant, human oversight, and an appeals or override process.

AI-assisted spreadsheet generation can accelerate formula construction and scenario analysis, but AI output must be verified. It may invent cell references, omit constraints, or express unjustified confidence. The accountable manager remains responsible for the decision and its consequences.

10. Best-Practice Workflow

A professional Solver workflow is:

1. Define the decision and the decision-maker’s authority.
2. State the objective in measurable units.
3. Define every variable and parameter.
4. Establish data sources, time horizon, and assumptions.
5. Write the mathematical formulation before building the spreadsheet.
6. Build a transparent input-calculation-output layout.
7. Test formulas independently, including boundary cases.
8. Select Solver’s method based on model structure.
9. Check feasibility, binding constraints, and resource utilization.
10. Compare Solver output with simple benchmarks or managerial rules.
11. Conduct sensitivity and scenario analysis.
12. Document limitations and obtain decision-owner approval.

Chapter Summary

Business optimization formalizes managerial choices under scarcity. A complete model identifies decision variables, parameters, an objective function, constraints, and appropriate domain restrictions such as non-negativity, integer, or binary conditions. Excel Solver implements these models by changing decision cells to maximize or minimize an objective while satisfying constraints.

Linear product-mix, resource-allocation, budgeting, scheduling, and cost-minimization models can often be solved with Simplex LP. Nonlinear and integer models require different methods and more cautious interpretation. Feasibility, binding constraints, slack, shadow prices, reduced costs, and sensitivity analysis help managers understand not only what solution Solver found, but why it found it and how robust it may be. Effective practice requires sound formulation, accurate data, scenario analysis, governance, ethical review, and clear communication of assumptions.

Key Terms

Decision variable: A controllable quantity whose value is determined by the optimization model.

Parameter: A known or estimated input used by the model.

Objective function: A mathematical expression representing the quantity to maximize or minimize.

Constraint: A restriction defining which decisions are feasible.

Feasible solution: A decision combination satisfying every constraint.

Optimal solution: The best feasible solution according to the objective function.

Linear programming: Optimization with linear objective and constraint expressions.

Simplex LP: Solver method for suitable linear programming models.

Nonlinear model: A model containing nonlinear relationships among variables.

Integer variable: A variable restricted to whole-number values.

Binary variable: A variable restricted to 0 or 1.

Slack: Unused amount of a resource or allowance in a constraint.

Binding constraint: A constraint satisfied exactly at the optimum.

Shadow price: Marginal objective improvement from one additional unit of a constrained resource within a valid sensitivity range.

Reduced cost: Objective-coefficient improvement associated with a variable at a bound, under LP sensitivity assumptions.

Sensitivity analysis: Examination of how changes in parameters affect the solution.

Scenario analysis: Evaluation of coherent alternative sets of assumptions.

Feasibility: The condition that at least one solution satisfies all constraints.

Mixed-integer model: An optimization model containing continuous and integer or binary variables.

Model governance: Controls that support accuracy, accountability, documentation, and responsible use of a model.

Review Questions

Short Answer Questions

1. What are the four essential elements of an optimization model?
2. Why is non-negativity commonly imposed on production variables?
3. Distinguish a binding constraint from a constraint with slack.
4. When is Simplex LP an appropriate Solver method?
5. What does a shadow price represent?

Long Answer Questions

1. Explain how a business problem is translated into an Excel Solver model, from problem definition through result validation.
2. Compare linear, nonlinear, integer, and binary optimization models, giving a business example of each.
3. Explain how shadow prices, reduced costs, and sensitivity analysis support managerial decisions.
4. Discuss the limitations and governance requirements of spreadsheet-based optimization.
5. Develop and explain a product-mix formulation involving two products, two resources, and demand limits.

Analytical / Application Questions

1. A firm has three products, limited labour and material, and a minimum contractual order for one product. Design the decision variables, objective function, constraints, and Solver configuration. Explain how you would test feasibility.
2. A workforce schedule minimizes cost but assigns most night shifts to temporary employees. Analyze how the model should be revised to address fairness, labour rules, and operational practicality.
3. A Solver report shows a positive shadow price for warehouse capacity under the baseline scenario, but zero value under a low-demand scenario. Explain the economic meaning and recommend a capacity decision process.

Multiple Choice Questions with Answers

Question 1

Which element of an optimization model represents the controllable choices made by management?

A. Parameters

B. Decision variables

C. Constraints

D. Sensitivity ranges

Correct Answer: B. Decision variables

Question 2

Which Solver method is generally appropriate for a linear programming model?

A. Simplex LP

B. Evolutionary only

C. Random sampling

D. Moving Average

Correct Answer: A. Simplex LP

Question 3

A resource constraint has capacity 500 units and modeled usage of 430 units. What is its slack?

A. 70 units

B. 430 units

C. 500 units

D. 930 units

Correct Answer: A. 70 units

Question 4

What does a binary variable normally represent?

A. Any positive quantity

B. A continuous percentage

C. A yes/no decision coded as 0 or 1

D. An unrestricted financial amount

Correct Answer: C. A yes/no decision coded as 0 or 1

Question 5

If product A earns $20 contribution per unit and product B earns $15, with quantities in cells B6 and C6, which formula calculates total contribution?

A. =SUM(B6:C6)

B. =SUMPRODUCT(B5:C5,B6:C6)

C. =AVERAGE(B5:C6)

D. =COUNT(B5:C5)

Correct Answer: B. =SUMPRODUCT(B5:C5,B6:C6)

Question 6

A solution is infeasible when:

A. The objective value is positive

B. At least one constraint is violated

C. A variable equals zero

D. A constraint has slack

Correct Answer: B. At least one constraint is violated

Question 7

In a maximization model, a positive shadow price for an extra machine hour generally indicates that:

A. The machine should be discarded

B. An additional hour may improve the objective within the valid sensitivity range

C. The model has no feasible solution

D. Demand must be zero

Correct Answer: B. An additional hour may improve the objective within the valid sensitivity range

Question 8

Which restriction is appropriate when the number of trucks selected must be a whole number?

A. Continuous

B. Integer

C. Percentage

D. Text

Correct Answer: B. Integer

Question 9

Why should a Solver result be tested under alternative scenarios?

A. Solver cannot calculate an objective value

B. All business parameters are perfectly certain

C. The optimal solution may change when assumptions change

D. Constraints are unnecessary after solving

Correct Answer: C. The optimal solution may change when assumptions change

Question 10

In a cost-minimization procurement model, a demand requirement is most naturally written as:

A. Total supplied ≤ demand

B. Total supplied = 0

C. Total supplied ≥ required demand

D. Total cost ≥ demand

Correct Answer: C. Total supplied ≥ required demand