Optimization and Prescriptive Decision Models

Introduction

Business analytics is often used first to describe what has happened and then to predict what is likely to happen. Prescriptive analytics addresses the next managerial question: What should the organization do? Optimization provides a disciplined answer by selecting the best feasible decision according to a stated objective and a defined set of restrictions.

Excel Solver is a practical optimization tool for business analysts. It can determine a product mix, allocate scarce resources, schedule employees, route shipments, select investments, or minimize operating cost. However, Solver does not replace managerial reasoning. A technically correct Solver result is useful only when the model represents the real decision, the data are credible, the assumptions are understood, and the result is implemented responsibly.

This chapter develops the structure of optimization models, explains how to implement them in Excel, and shows how to interpret solutions, sensitivity information, infeasibility, and nonlinear behavior.

Learning Objectives

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

1. Formulate decision variables, objective functions, and constraints.
2. Distinguish linear, integer, binary, and nonlinear optimization models.
3. Build and solve optimization models using Excel Solver.
4. Apply optimization to product mix, allocation, scheduling, logistics, staffing, and finance.
5. Interpret shadow prices, reduced costs, allowable changes, and binding constraints.
6. Diagnose infeasible, unbounded, or operationally unrealistic solutions.
7. Evaluate ethical, governance, and implementation issues in prescriptive analytics.

1. The Structure of an Optimization Model

An optimization model consists of four essential elements:

• Decision variables: controllable quantities that represent the decision.
• Objective function: the quantity to maximize or minimize.
• Constraints: restrictions that feasible decisions must satisfy.
• Parameters: known inputs such as prices, costs, capacities, processing times, or demand.

A general optimization model can be written as:

Here, x is the vector of decision variables, f(x) is the objective, g_i(x) represents constraint functions, b_i is a right-hand-side limit, and X specifies domain conditions such as nonnegativity, integrality, or binary values. The model is feasible if at least one decision satisfies every constraint.

1.1 Decision variables

A decision variable should answer: What quantity can the decision-maker control? For example, if a factory produces products A and B, let x_A be units of A produced and x_B be units of B produced. If a company decides whether to open a warehouse, a binary variable may be used:

y = 1 if the warehouse is opened, and y = 0 otherwise.

Variables should be measurable, nonduplicative, and connected to an actual decision. A variable representing “quality” without a measurable operational definition is not a satisfactory modeling choice.

1.2 Objective function

The objective expresses the decision criterion. If contribution margins are $40 and $30 per unit for products A and B, respectively, total contribution is:

The coefficients and variables must have compatible units. If contribution is measured in dollars per unit and production is measured in units, the objective is measured in dollars. A cost-minimization model may instead be written as:

where c_j is the cost per unit of activity j, and x_j is the corresponding activity level.

1.3 Constraints

Constraints describe scarce resources, policies, contractual requirements, or physical limits. If each unit of A uses 2 labor hours, each unit of B uses 1 labor hour, and 100 labor hours are available, the labor constraint is:

If production capacity is 80 units in total:

Nonnegativity is also a constraint:

A constraint is binding when it is satisfied exactly at the optimum. A nonbinding constraint has unused capacity, called slack. For a resource constraint, slack is:

Slack is nonnegative for a constraint of the form “less than or equal to.”

2. Linear, Integer, and Nonlinear Models

Linear programming

A linear program has a linear objective and linear constraints. In matrix notation, a maximization model is:

The coefficients are constant, and variables are not multiplied by one another or raised to powers. Linear programming is appropriate when marginal returns and resource use are constant over the relevant range.

Integer and binary programming

Some decisions must be whole numbers. A staffing model cannot assign 4.6 employees, and a delivery truck cannot be divided into 0.35 trucks. Integer restrictions are written as:

A binary variable is restricted to zero or one. Binary variables are useful for yes/no decisions, project selection, facility opening, machine activation, and logical conditions. Integer restrictions often make a model substantially more difficult to solve because the feasible region is discrete rather than continuous.

Nonlinear programming

A model is nonlinear when the objective or a constraint contains powers, products of variables, ratios involving variables, or nonlinear functions. For example, a simplified revenue model with price decreasing as quantity increases might be:

where q is quantity, a is the initial revenue coefficient, and b > 0 represents the effect of declining price. Nonlinear models require careful attention to local versus global optima. Solver’s GRG Nonlinear method is designed for smooth nonlinear models but may find a local optimum rather than the global optimum.

3. A Worked Product-Mix Model

A factory produces A and B. The data are:

The model is:

At the intersection of the two resource constraints:

Multiplying the second equation by 2 gives 2x_A + 4x_B = 160. Subtracting the first equation gives 3x_B = 60, so x_B = 20. Substitution gives x_A = 40. The contribution is:

The solution uses all 100 labor hours and all 80 machine hours. In this particular model, the corner points are (0, 0), (50, 0), (40, 20), and (0, 40). Their objective values are $0, $2,000, $2,200, and $1,200, respectively. Thus, (40, 20) is the optimal continuous solution.

If production must be in whole units, the continuous solution happens to be integral. In other models, rounding a continuous answer is unsafe because it may violate a constraint or produce a nonoptimal integer solution. Integer Solver restrictions should be used instead.

4. Building the Model in Excel

A robust worksheet separates inputs, decision variables, calculations, and outputs.

Recommended worksheet design

1. Inputs: prices, costs, capacities, demand, times, and policy parameters.
2. Decision-variable cells: initially populated with zeros or an estimated plan.
3. Objective cell: a formula such as =SUMPRODUCT(contribution_range, decision_range).
4. Resource-use cells: formulas such as =SUMPRODUCT(resource_coefficients, decision_range).
5. Constraint table: resource, available amount, calculated use, and slack.
6. Output area: objective value, status, and key management indicators.

For the product-mix example, the objective formula can be:

=SUMPRODUCT(B5:C5,B10:C10)

where cells B5:C5 contain unit contributions and B10:C10 contain production quantities. Labor use can be calculated with another SUMPRODUCT formula. This structure reduces transcription errors and makes the model auditable.

Solver configuration

In Excel, activate Solver through File → Options → Add-ins → Excel Add-ins → Solver Add-in. Then use Data → Solver.

Typical configuration is:

• Set Objective: the objective formula cell.
• To: Max or Min.
• By Changing Variable Cells: the decision-variable range.
• Subject to the Constraints: resource, demand, balance, and domain restrictions.
• Make Unconstrained Variables Non-Negative: selected when appropriate.
• Solving Method: Simplex LP for linear models; GRG Nonlinear for smooth nonlinear models; Evolutionary for difficult non-smooth or discontinuous models.

For a linear model, select Simplex LP and enable options appropriate to linearity. For integer variables, add an integer constraint; for yes/no variables, select binary. Solver may also permit precision and tolerance settings. These should reflect business significance rather than be chosen arbitrarily.

5. Interpreting Solver Results

A Solver answer is not merely a list of numbers. It should be interpreted in relation to the model.

Optimality and feasibility

A solution is feasible if it satisfies all constraints. It is optimal if no other feasible solution improves the objective under the model assumptions. Solver may report:

• An optimal solution was found.
• Solver could not find a feasible solution.
• The objective is unbounded.
• Solver stopped because a limit was reached.
• A nonlinear model converged to a solution.

“Solver stopped” does not automatically mean “optimal.” The analyst must inspect the message, constraint violations, convergence settings, and model structure.

Sensitivity analysis for linear programs

For a linear model, the Solver Sensitivity Report may include shadow prices, reduced costs, and allowable changes. These concepts are most reliable for continuous linear programs and should not be interpreted mechanically for integer or highly nonlinear models.

A shadow price is the marginal improvement in the objective caused by increasing the right-hand side of a constraint by one unit, within the allowable range and while the current basis remains valid. If the labor shadow price is $12 per hour, one additional labor hour is worth approximately $12 in objective value, subject to the report’s allowable range.

A nonbinding resource normally has a shadow price of zero because additional units are not immediately valuable. A binding constraint may have a positive shadow price in a maximization model, although degeneracy and other model features require care.

A reduced cost indicates, for a variable currently at zero, how much its objective coefficient would need to improve before it becomes attractive under the current basis. Reduced costs are basis-specific and are not the same as a product’s accounting cost.

The allowable increase and allowable decrease for an objective coefficient or constraint right-hand side define a range over which the reported sensitivity interpretation remains valid. They do not guarantee that the same solution remains economically sensible when multiple parameters change simultaneously.

Example of sensitivity interpretation

Suppose the product-mix solution has a labor shadow price of $5 per hour and an allowable increase of 20 hours. Increasing labor from 100 to 110 hours is estimated to increase contribution by approximately:

This estimate is valid only within the stated sensitivity range and assuming other parameters remain unchanged. It does not mean labor is always worth $5 per hour, nor does it establish a wage rate that the organization should pay without considering availability, contracts, or implementation costs.

6. Applications of Optimization

Allocation and capital budgeting

A capital-budgeting model selects projects subject to a budget and possibly strategic requirements. Let y_j be 1 if project j is selected and 0 otherwise. With expected value v_j and investment k_j, the model is:

Additional constraints can require at least one digital project, prohibit simultaneous selection of incompatible projects, or link project selection to staffing capacity.

Scheduling and staffing

Let x_{e,t be the number of employees of type e assigned to time period t. Coverage requirements can be written as:

The objective may minimize wages, overtime, or a weighted combination of understaffing and overtime. Scheduling models commonly require integer variables and may include employee availability, maximum hours, rest periods, and fairness constraints.

Transportation and logistics

Let x_ij be the quantity shipped from origin i to destination j. If shipping cost is c_ij, the cost-minimization objective is:

Supply and demand constraints are:

The exact inequality depends on whether all supply must be shipped and whether demand must be met exactly or may be exceeded. Units should be consistent—for example, pallets, cases, or kilograms.

Financial decisions

Optimization can support portfolio selection, cash allocation, loan structuring, and working-capital decisions. Financial models require particular caution because returns are uncertain, correlations may be estimated poorly, and constraints may represent regulatory or fiduciary requirements. A mathematically optimal portfolio under estimated returns can be fragile when forecasts change.

7. Illustrative Case: Regional Distribution Planning

This is a fictional illustrative case.

Background

Northstar Foods operates two distribution centers, East and West, and serves three retail regions. Management wants to minimize weekly shipping cost while meeting regional demand.

Problem

The analyst must decide weekly shipments from each distribution center to each region. East can supply 600 cases and West can supply 500 cases. Regional demand is 300, 400, and 250 cases. Shipping costs per case are:

Total supply is 1,100 cases, while demand is 950. Therefore, the model does not need to use all supply unless a business rule requires it.

Analysis and approach

Define six shipment variables, such as x_E1 for East-to-Region-1 shipments. Minimize total shipping cost:

Subject to:

Because every shipment has a positive cost, an optimal solution will not ship more than demand unless an additional requirement exists. A low-cost plan sends West to Region 3, West preferentially to Region 2, and East to Region 1, while respecting capacity. One feasible low-cost allocation is:

This meets all demand, uses 450 East cases and 500 West cases, and costs:

However, the analyst should still run Solver rather than rely on intuition, because alternative allocations may have the same or lower cost. The worksheet should calculate supply used, demand served, route cost, and slack. Management should then test fuel-price changes, supply disruptions, minimum shipment quantities, and service-level requirements.

Outcome and learning points

The case illustrates that optimization identifies a coordinated system-wide plan rather than choosing the cheapest route independently for every destination. It also demonstrates the importance of checking units, excess capacity, demand satisfaction, and alternative scenarios. A model that omits delivery-time requirements may produce a cheap plan that violates customer-service policy.

8. Feasibility, Unboundedness, and Model Diagnosis

Infeasibility

A model is infeasible when no decision satisfies all constraints. Common causes include:

• Demand requirements exceeding total available supply.
• Contradictory minimum and maximum requirements.
• Incorrect cell references.
• Inconsistent units, such as mixing hours and minutes.
• Accidental constraints applied to the wrong variable.
• Integer restrictions that eliminate all continuous solutions.

To diagnose infeasibility, temporarily remove or relax constraints, inspect each constraint’s left-hand side and right-hand side, and use a feasibility-oriented model. Do not simply delete a troublesome constraint; determine whether the requirement, data, or formulation is wrong.

Unboundedness

An objective is unbounded when it can improve indefinitely. In business models this usually indicates a missing capacity, demand, balance, or nonnegativity constraint. For example, maximizing profit from a positive-margin product without limiting production creates an unrealistic unlimited solution.

Degeneracy and multiple optima

A model may have more than one optimal solution. Multiple plans can produce the same objective value, allowing management to choose one with better risk, simplicity, fairness, or resilience. A single Solver output should not be assumed to be the only acceptable plan.

9. Nonlinear and Integer Solver Practice

For nonlinear models, verify that formulas are smooth where GRG Nonlinear is used, provide sensible starting values, and test multiple starting points. If different starting points produce different solutions, the model may contain multiple local optima. Evolutionary Solver can explore difficult solution spaces but may require substantial computation and careful parameter settings.

For integer models, use integer or binary restrictions rather than rounding. Examine the optimality gap when Solver reports one. A small gap means the best known solution is close to the best bound found, but it does not necessarily prove that the solution is globally optimal unless Solver reports completion according to its criteria.

Complex models can often be improved by:

• Scaling variables and coefficients so values are not extremely different in magnitude.
• Tightening obvious variable bounds.
• Removing redundant formulas and constraints.
• Using logical constraints explicitly.
• Separating data from formulas.
• Testing a small version of the model before expanding it.

10. Governance, Ethics, and Managerial Judgment

Prescriptive models can distribute work, determine staffing, approve loans, or prioritize customers. These uses create governance responsibilities.

First, the objective must reflect legitimate organizational purposes. Maximizing contribution while ignoring worker safety or service equity can produce harmful decisions. Second, constraints should include legal, contractual, and ethical requirements, not merely financial limits. Third, data and formulas should be documented so another analyst can reproduce the result. Fourth, decisions affecting people should permit review, explanation, and appropriate human oversight.

Optimization can reproduce historical bias if biased data are embedded in costs, demand estimates, credit scores, or productivity assumptions. A model should therefore be tested across relevant groups and scenarios. Privacy, access control, version management, and audit trails are also important when the workbook contains sensitive data.

AI-assisted spreadsheet generation can accelerate formula writing and scenario analysis, but generated formulas must be independently verified. AI does not know whether a constraint is legally valid, whether an input is current, or whether a result is operationally fair. Human accountability remains essential.

11. Best Practices for Professional Excel Models

1. Define the business decision before opening Solver.
2. State units beside inputs and constraints.
3. Use descriptive labels and named ranges where practical.
4. Keep assumptions separate from calculations.
5. Use SUMPRODUCT for transparent linear calculations.
6. Include explicit nonnegativity, integer, and binary restrictions.
7. Check feasibility before interpreting optimality.
8. Compare the optimized plan with the current plan.
9. Perform sensitivity and scenario analysis.
10. Record Solver settings and version the workbook.
11. Test extreme but plausible inputs.
12. Present operational implications, not only objective values.
13. Review the result with subject-matter experts.
14. Monitor actual outcomes and update parameters.

Chapter Summary

Optimization is the foundation of prescriptive business analytics. A complete model identifies decision variables, an objective function, constraints, and parameter values. Linear programming is appropriate when relationships are linear and continuous; integer and binary programming represent indivisible or yes/no decisions; nonlinear programming represents changing marginal effects or interactions.

Excel Solver implements these models through an objective cell, changing variable cells, constraints, and an appropriate solving method. Product mix, allocation, scheduling, transportation, staffing, capital budgeting, and financial planning are important applications. Solver results must be evaluated for feasibility, optimality, binding constraints, slack, sensitivity, and operational realism. Shadow prices and reduced costs provide useful marginal insights for continuous linear models, but their interpretation has defined validity ranges.

Infeasibility usually signals conflicting requirements, data errors, or formulation mistakes, while unboundedness often indicates a missing limit. Integer and nonlinear models require additional care because rounding, local optima, and computational gaps can affect conclusions. Finally, optimization is a decision-support method rather than an autonomous decision-maker. Ethical objectives, transparent documentation, human review, and scenario testing are necessary for responsible use.

Key Terms

Decision variable: A controllable quantity selected by the model.

Objective function: A mathematical expression to maximize or minimize.

Constraint: A restriction that feasible decisions must satisfy.

Parameter: A known input or coefficient in an optimization model.

Feasible solution: A solution satisfying every model constraint.

Optimal solution: A feasible solution with the best objective value under the model.

Linear programming: Optimization with linear objective and constraint expressions.

Integer programming: Optimization requiring one or more variables to be whole numbers.

Binary variable: A variable restricted to 0 or 1.

Nonlinear programming: Optimization containing nonlinear objective or constraint relationships.

Slack: Unused capacity in a less-than-or-equal constraint.

Binding constraint: A constraint satisfied exactly at the solution.

Shadow price: Marginal objective improvement from relaxing a constraint within a valid range.

Reduced cost: Objective-coefficient improvement required for a currently inactive variable to become attractive under a current basis.

Sensitivity analysis: Examination of how parameter changes affect a solution or objective.

Simplex LP: Solver method for linear programming models.

GRG Nonlinear: Solver method for smooth nonlinear models.

Evolutionary Solver: A heuristic method for difficult, discontinuous, or non-smooth models.

Optimality gap: Difference between a known feasible solution and a bound on the best possible solution.

Prescriptive analytics: Analytics that recommends actions to achieve an objective under constraints.

Review Questions

Short Answer Questions

1. What are the four essential elements of an optimization model?
2. What is the difference between a binding constraint and a constraint with slack?
3. Why should integer variables not be handled by simply rounding continuous Solver results?
4. When is Simplex LP the appropriate Solver method?
5. What does a shadow price represent in a continuous linear model?

Long Answer Questions

1. Explain how a product-mix problem can be formulated and implemented in Excel Solver.
2. Compare linear, integer, binary, and nonlinear optimization models, including an example application of each.
3. Explain how sensitivity reports support managerial decisions and describe their limitations.
4. Discuss the principal causes of infeasibility and a systematic approach to diagnosing it.
5. Evaluate the ethical and governance responsibilities associated with optimization models affecting employees or customers.

Analytical / Application Questions

1. A firm earns $50 per unit of Product A and $35 per unit of Product B. A has resource use of 3 labor hours and 1 machine hour; B uses 2 labor hours and 2 machine hours. Weekly availability is 120 labor hours and 80 machine hours. Formulate the linear product-mix model and identify the continuous optimum.
2. A transportation model reports a shipment plan that meets cost targets but leaves one region underserved. Identify at least three formulation or data checks that should be performed before accepting the result.
3. A staffing model produces a mathematically optimal schedule that assigns employees to consecutive shifts without adequate rest. Explain how the model should be revised and how the revised result should be evaluated beyond wage cost.

Multiple Choice Questions with Answers

Question 1

Which element of an optimization model represents the quantities directly controlled by the decision-maker?

A. Parameters

B. Decision variables

C. Shadow prices

D. Slack values

Correct Answer: B. Decision variables

Question 2

For the product-mix model with objective Z = 40x_A + 30x_B, which contribution results from producing 40 units of A and 20 units of B?

A. $1,600

B. $2,000

C. $2,200

D. $2,400

Correct Answer: C. $2,200

Question 3

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

A. Simplex LP

B. GRG Nonlinear

C. Evolutionary

D. Random Search

Correct Answer: A. Simplex LP

Question 4

What does a binding constraint indicate?

A. The constraint has unused capacity

B. The constraint is satisfied exactly at the solution

C. The constraint is irrelevant to the model

D. The constraint must be deleted

Correct Answer: B. The constraint is satisfied exactly at the solution

Question 5

A binary variable is restricted to which values?

A. Any nonnegative real number

B. Any whole number greater than zero

C. −1 and 1 only

D. 0 and 1

Correct Answer: D. 0 and 1

Question 6

If a labor constraint has a shadow price of $5 per hour and labor increases by 10 hours within the allowable range, what is the approximate objective improvement?

A. $5

B. $10

C. $50

D. $500

Correct Answer: C. $50

Question 7

Which situation most directly indicates an infeasible model?

A. Several solutions have the same objective value

B. No decision satisfies all constraints

C. The objective value is positive

D. A constraint has slack

Correct Answer: B. No decision satisfies all constraints

Question 8

Why is rounding a continuous optimization solution potentially unsafe?

A. Rounding always increases the objective

B. Rounding can violate constraints and need not be optimal

C. Rounding converts a maximization problem into minimization

D. Rounding removes all parameters

Correct Answer: B. Rounding can violate constraints and need not be optimal

Question 9

Which model is most naturally represented using binary variables?

A. Selecting whether to open a warehouse

B. Measuring kilograms shipped

C. Calculating total revenue

D. Recording hourly demand

Correct Answer: A. Selecting whether to open a warehouse

Question 10

What is a principal limitation of interpreting a linear-programming sensitivity report?

A. It is valid regardless of parameter changes

B. It applies only within specified ranges and model assumptions

C. It eliminates the need for feasibility checks

D. It applies automatically to all nonlinear models

Correct Answer: B. It applies only within specified ranges and model assumptions