Decision Modeling, Scenario Analysis, and Optimization

Introduction

Supply-chain decisions are rarely made under conditions of certainty. A sourcing manager may compare suppliers with different prices, lead times, capacities, and disruption risks. An inventory manager must balance service requirements against holding and shortage costs. A transportation manager may select modes and routes while considering fuel prices, emissions, and delivery promises. A capacity planner must decide whether to use overtime, subcontracting, or capital expansion. These decisions are interdependent: a low purchase price may be unattractive if it creates excessive inventory, unreliable delivery, or high cost-to-serve.

Spreadsheets are valuable because they make assumptions explicit, connect operational drivers to financial outcomes, and allow decision makers to test alternatives rapidly. They are not, however, substitutes for judgment. A spreadsheet model is a simplified representation of a real system. Its quality depends on the quality of its structure, data, assumptions, validation, and governance.

This chapter develops a decision-modeling approach using what-if analysis, sensitivity testing, goal seeking, simulation concepts, and spreadsheet optimization. The emphasis is on graduate-level managerial analysis: understanding model structure, recognizing assumptions, interpreting results, and communicating decisions responsibly.

Learning Objectives

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

1. Construct transparent spreadsheet models for sourcing, inventory, transportation, capacity, and cost-to-serve decisions.
2. Distinguish inputs, decision variables, calculated outputs, constraints, and performance measures.
3. Apply scenario analysis, one-way and two-way sensitivity analysis, and goal seeking appropriately.
4. Explain the difference between deterministic optimization and stochastic simulation.
5. Formulate and interpret linear and mixed-integer spreadsheet optimization models.
6. Evaluate model quality, robustness, ethics, and governance before implementing a recommendation.

1. Decision Models as Structured Representations

A decision model converts a managerial question into a logically connected structure. A useful model normally contains five elements:

A basic total-cost model can be expressed as:

where TC is total cost, PC purchase cost, FC freight and transportation cost, HC holding cost, SC shortage or service-failure cost, TC_r tariffs and regulatory costs, and EC externality or environmental cost when it is monetized. All terms must use the same currency and time basis.

The equation is a framework rather than a universal accounting identity. Some organizations include transportation in purchase cost, and environmental cost may be reported separately. The modeler must define boundaries clearly to avoid double counting.

1.1 Model architecture

A robust spreadsheet separates:

• Input area: assumptions, source, date, units, and confidence level.
• Decision area: cells changed manually or by an optimization tool.
• Calculation area: formulas and intermediate logic.
• Output area: financial, operational, and risk indicators.
• Documentation area: definitions, assumptions, version history, and validation checks.

Hard-coding numbers inside formulas is poor practice. A formula such as =B7*1250 hides the origin of 1,250. A better design places demand in a labeled input cell and references it. This improves auditability and makes scenario changes safer.

1.2 From decision question to model

The modeling process should begin with a precise question. “Which supplier is best?” is incomplete. A stronger question is: “Which allocation of annual demand across qualified suppliers minimizes expected landed cost while achieving at least 98% on-time service, respecting capacity and dual-sourcing requirements?”

The question defines the objective, scope, time horizon, constraints, and relevant data. It also identifies what the model should not attempt to answer. For example, a cost model cannot by itself assess labor conditions unless such information is represented through data or a separate evaluation framework.

2. Deterministic Spreadsheet Models

A deterministic model treats inputs as fixed values. It is useful when variation is small, when the decision is preliminary, or when the purpose is to understand structural relationships.

2.1 Sourcing allocation model

Suppose demand for a component is 100,000 units. Supplier i has unit price p_i, freight per unit f_i, fixed award cost F_i, and allocated quantity x_i. A simple sourcing cost is:

Here, z_i is a binary variable: 1 if supplier i is used and 0 otherwise. The binary variable is necessary when a fixed cost applies only if the supplier is selected. The model assumes costs are additive, demand is known, supplier prices do not change within the modeled range, and quality and service requirements are represented separately or treated as constraints.

Typical constraints include:

where D is demand, and:

where U_i is supplier capacity. If a supplier must receive a minimum quantity when selected, the constraint can be written:

where L_i is the minimum award quantity. These constraints are linear, but the binary variables make the complete problem a mixed-integer model.

2.2 Inventory cost model

For a periodic planning model, ending inventory can be calculated as:

where I_t is ending inventory in period t, Q_t is replenishment received, and D_t is demand. The relationship assumes inventory is measured in consistent units and that receipts and demand occur according to the chosen timing convention.

A simple period holding cost is:

where h is holding cost per unit per period. If average inventory is more appropriate because receipts and demand occur throughout a period, use:

This average-inventory formula assumes a roughly linear inventory pattern during the period. It should not be used blindly when demand or receipts are highly lumpy.

For a continuous-review item with stable demand, the economic order quantity is:

where D is annual demand in units per year, S is ordering cost per order, and H is annual holding cost per unit. The classical formula assumes constant demand, instantaneous replenishment, no quantity discounts, no shortages, and a constant unit holding cost. It is therefore a benchmark, not a complete policy for uncertain supply chains.

When demand during lead time is uncertain, reorder point can be represented as:

where μ_L is mean demand during lead time, σ_L is the standard deviation of lead-time demand, and z is the standard-normal service factor. This form assumes a demand distribution for which the selected z meaningfully represents the desired cycle-service probability. If lead time is also variable, σ_L must reflect both demand and lead-time variation rather than demand variation alone.

2.3 Transportation and cost-to-serve

Transportation models should distinguish fixed and variable costs. For lane r, a simplified cost can be written:

where A_r is a fixed shipment or route cost, y_r indicates whether the lane is used, v_r is variable freight cost per unit, t_r is tariff or surcharge per unit, and q_r is quantity moved. Capacity, vehicle minimums, delivery windows, and mode eligibility must be modeled explicitly.

Cost-to-serve allocates supply-chain costs to customers, products, channels, or orders. A general customer-level measure is:

where CTS_j is contribution after service costs for customer j. Activity-based allocation may use orders, order lines, deliveries, pallets, returns, or special handling events. A single revenue percentage is often misleading because customers with identical sales can consume very different operational resources.

3. What-If Analysis and Scenario Planning

What-if analysis changes one or more inputs and observes the resulting output. It answers questions such as:

• What happens if fuel cost rises by 25%?
• What if demand is 15% below plan?
• What if a supplier loses 20% of capacity?
• What if the firm raises its target service level?

A scenario is a coherent set of assumptions. A “high-demand” scenario may simultaneously include increased volume, tighter transportation capacity, higher overtime, and greater shortage risk. Changing only demand while leaving every related assumption unchanged may understate the effect of the scenario.

3.1 Scenario design

A scenario table should identify the assumption, baseline, alternative value, rationale, and source. For example:

Scenario analysis is not forecasting. It explores conditional outcomes: “If these assumptions hold, what follows?” It is particularly useful when the future is uncertain but decision makers can identify plausible states.

3.2 Sensitivity analysis

Sensitivity analysis varies an input systematically while holding other inputs constant. It reveals which assumptions have the greatest influence on the result. A one-way sensitivity table may vary unit price from $9.00 to $11.00 and show total annual cost. A two-way data table may vary both demand and fuel price.

The local sensitivity of an output Y to an input X can be approximated by:

The units are output units per input unit. A dimensionless elasticity is often easier to compare across variables:

This approximation is most informative for relatively small changes and locally smooth relationships. It can be misleading near thresholds, capacity limits, or fixed-cost jumps.

A tornado chart ranks variables by their effect on an output. It is useful for communication, but it does not establish causality or probability. A variable may have a large modeled effect because its range is wide, not because it is intrinsically more important.

3.3 Break-even analysis

A break-even point identifies the value at which two alternatives have equal cost. If option A has fixed cost F_A and variable cost v_A per unit, and option B has corresponding values F_B and v_B, the break-even volume is:

This formula applies when v_A ≠ v_B and the alternatives have comparable scope and relevant costs. The result is meaningful only if the break-even volume lies within feasible demand and capacity ranges.

4. Goal Seeking and Managerial Thresholds

Goal seeking determines the input required to reach a specified output. Examples include:

• the maximum purchase price consistent with a target margin;
• the service level required to avoid a penalty;
• the volume required to justify a dedicated vehicle;
• the inventory reduction needed to release working capital.

A spreadsheet goal-seek tool usually changes one input cell until one formula reaches a target. It is appropriate for a single changing input and a clear, continuous relationship. It is less suitable when several decision variables interact, when constraints are essential, or when the model contains discontinuities.

For example, if contribution margin is:

where P is selling price per unit, D demand, c variable cost per unit, and F fixed cost, the maximum variable cost for a target margin M_T is:

This algebraic solution is preferable to a trial-and-error tool when the relationship is simple and the assumptions are valid.

5. Simulation Concepts for Uncertainty

Optimization selects the best decision under a defined model. Simulation evaluates the consequences of a decision across many possible realizations of uncertain inputs. The two methods answer different questions:

• Optimization: What decision is best under the stated parameters and constraints?
• Simulation: What range of outcomes might occur if this decision is implemented?

A spreadsheet simulation samples uncertain inputs such as daily demand, lead time, supplier yield, or transport delay. For iteration k, the model produces an outcome Y_k. After N iterations, the estimated mean is:

The simulated probability of an event, such as stockout, is:

These are estimates, not exact probabilities. Larger sample sizes generally reduce sampling error, but they do not correct a wrongly specified distribution or correlation structure.

5.1 Distribution and dependence

The choice of distribution must be supported by historical data, process knowledge, or transparent judgment. Demand may be modeled with a normal, Poisson, negative-binomial, empirical, or intermittent-demand distribution depending on the setting. Lead times may be discrete or skewed. Supplier disruptions may be represented by a Bernoulli event or a duration model.

Correlation matters. Demand surges may coincide with higher transport rates; a regional disruption may affect multiple suppliers simultaneously. Modeling all variables as independent can understate joint risk. Conversely, imposing correlation without evidence can exaggerate risk.

5.2 Simulation output and convergence

Useful outputs include mean cost, standard deviation, selected percentiles, probability of stockout, probability of late delivery, and conditional loss. A decision should not be judged only by expected cost. Two strategies may have similar averages but very different downside exposure.

A practical spreadsheet simulation should use a reproducible random-number seed where possible, record the number of iterations, inspect histograms or percentiles, and perform convergence checks. If the estimated 95th-percentile cost changes materially when iterations increase from 10,000 to 50,000, the original run was insufficient for that decision.

6. Spreadsheet Optimization

Spreadsheet optimization uses an objective cell, variable cells, and constraints. Linear programming is appropriate when the objective and constraints are linear functions of decision variables. A general minimization model is:

subject to:

and:

Here, c_i is cost per unit of decision variable x_i, a_ri is resource consumption, and b_r is resource availability. Units must be consistent: for example, cost coefficients in dollars per pallet and capacity limits in pallets.

A sourcing model may include demand satisfaction, supplier capacity, quality minimums, transportation capacity, and a risk-diversification rule. A constraint such as “at least two suppliers must be used” requires binary variables and a mixed-integer solver. A nonlinear solver is needed when relationships include products of decision variables, nonlinear economies, or functions such as squared utilization.

6.1 Solver interpretation

An optimal solution is optimal only relative to the model. It may be infeasible if a constraint is entered incorrectly, or unrealistic if important requirements are omitted. Important outputs include:

• objective value;
• values of decision variables;
• binding constraints;
• slack or unused capacity;
• reduced costs and shadow prices where available;
• sensitivity ranges and alternative optima.

A binding constraint is satisfied exactly and limits improvement. Its shadow price estimates the marginal change in objective value from relaxing the constraint by one unit, within the valid sensitivity range and under the solver’s assumptions. It is not necessarily a market price or a strategic value beyond that range.

7. Worked Example: Dual-Sourcing Decision

Consider annual demand of 10,000 units. Two qualified suppliers are available:

The company requires at least 3,000 units from each supplier for resilience and wants total expected purchase-plus-freight cost minimized. The fixed award costs apply because both suppliers are used.

Effective variable costs are $50 for A and $50 for B. The variable cost is therefore $500,000 for any feasible allocation totaling 10,000 units. Fixed costs total $30,000. The optimized cost is consequently:

Any allocation satisfying:

and:

has the same modeled cost. This is an example of multiple optimal solutions. The manager should therefore use secondary criteria, such as service reliability, quality, capacity flexibility, or expected disruption loss, to select among them.

Suppose the company adds an expected late-delivery penalty of $4 per unit for the portion of demand associated with late deliveries. Using on-time rates as probabilities, expected penalty rates are $0.08 per unit for A and $0.24 per unit for B. Expected effective costs become $50.08 for A and $50.24 for B. The model now favors A, subject to capacity and the minimum award to B. A feasible allocation of 7,000 units to A and 3,000 to B has expected cost:

The arithmetic is:

• A: 7,000 × $50.08 = $350,560;
• B: 3,000 × $50.24 = $150,720;
• fixed costs = $30,000;
• total = $531,280.

The result depends on treating on-time performance as a stable probability and valuing each late unit at $4. In practice, late deliveries may be correlated, penalties may be nonlinear, and service effects may extend beyond the immediate unit. Simulation can test these limitations.

8. Illustrative Case: Northstar Home Appliances

Background

Northstar Home Appliances imports a critical electronic control module. Annual demand is 240,000 units, but monthly demand is seasonal. The firm currently buys from one low-cost supplier in East Asia. The supplier has competitive prices but a variable 42-day lead time. Northstar experiences occasional stockouts, emergency air shipments, and high inventory before peak season.

Problem

The procurement team proposes a second supplier with a higher unit price but a 14-day lead time. Finance wants the lowest annual cost; operations wants a 98% cycle-service level; sustainability staff want to limit air freight. The decision cannot be made using unit price alone.

Analysis approach

The team builds a monthly spreadsheet with:

1. forecast demand and forecast-error assumptions;
2. supplier capacity and lead-time distributions;
3. ocean and air freight alternatives;
4. inventory balance by month;
5. holding, shortage, purchase, and transportation costs;
6. service-level and air-freight indicators;
7. scenario and simulation outputs.

Three sourcing policies are tested: single sourcing, a 70/30 split, and a 50/50 split. For each policy, 20,000 simulated demand-and-lead-time paths are generated. The model records annual cost, stockout probability, 95th-percentile cost, and emergency air-freight volume.

Findings and solution

The single-source policy has the lowest average purchase cost but fails the 98% service target in a substantial proportion of simulations. The 70/30 policy improves service but remains vulnerable when the primary supplier experiences a long disruption. The 50/50 policy has the highest normal freight cost but materially lowers downside exposure and emergency air shipments.

Management adopts the 50/50 policy for the peak season and a 70/30 policy in low-demand months, subject to quarterly review. The spreadsheet is connected to actual lead-time and service data, and the model includes a trigger for re-estimation when performance diverges from assumptions.

Learning points

The case illustrates five principles:

• the cheapest unit price is not necessarily the lowest total cost;
• scenario analysis exposes structural weaknesses;
• simulation is useful when demand and lead time interact;
• service and sustainability can be modeled as constraints or evaluation criteria;
• a decision model must remain connected to measurement after implementation.

9. Model Validation, Governance, and Ethics

A model should be tested at several levels. Formula validation checks calculations against hand calculations and extreme cases. Logic validation asks whether relationships reflect the process. Data validation checks definitions, units, dates, missing values, and source reliability. Face validation asks experienced users whether outputs are operationally plausible. Outcome validation compares predictions with subsequent actual results.

Useful spreadsheet controls include:

• input cells with consistent formatting;
• protected formula cells;
• data validation rules;
• checks for negative inventory, capacity violations, and unmatched totals;
• reconciliation to financial and operational systems;
• version control and change logs;
• documented ownership and approval thresholds.

Ethical issues arise when models obscure trade-offs or privilege measurable costs over human consequences. A sourcing model that minimizes cost while ignoring forced labor, safety, emissions, or community impact is not neutral; it encodes a narrow value system. Decision makers should identify affected stakeholders, disclose material assumptions, and include regulatory and responsible-sourcing requirements as constraints or explicit evaluation criteria.

AI-assisted spreadsheet generation can accelerate formula writing, scenario creation, and anomaly detection. However, AI may invent formulas, misinterpret units, reproduce biased historical patterns, or expose confidential data. Human review, controlled data access, reproducible prompts or scripts, and independent testing remain necessary. AI-generated recommendations should be treated as model outputs requiring validation, not as authoritative decisions.

10. Contemporary Developments and Best Practices

Modern planning platforms increasingly combine spreadsheets with cloud databases, optimization engines, simulation libraries, and interactive dashboards. The strategic issue is not whether spreadsheets disappear, but whether they are used within a controlled analytical architecture. A spreadsheet may be appropriate for exploration, while an approved planning system manages production execution and master data.

Best practices include:

1. Define the decision, objective, horizon, and scope before building formulas.
2. Use consistent units, currencies, calendars, and data definitions.
3. Separate assumptions, decisions, calculations, and outputs.
4. Include fixed, variable, risk, quality, sustainability, and service costs where relevant.
5. Test baseline, extreme, and infeasible cases.
6. Use scenarios for coherent futures and sensitivities for influence testing.
7. Use goal seeking for one changing input and optimization for constrained choices.
8. Use simulation when uncertainty materially affects the decision.
9. Report ranges, downside risk, and assumptions rather than a single false-precision number.
10. Reassess the model after implementation using actual performance.

Chapter Summary

Decision modeling translates supply-chain questions into parameters, decision variables, calculations, constraints, and objectives. Deterministic spreadsheet models clarify cost and inventory relationships, while scenario and sensitivity analysis reveal how outcomes respond to changing assumptions. Goal seeking identifies thresholds for a target result. Simulation estimates distributions of outcomes under uncertain demand, lead time, disruption, and cost conditions. Optimization identifies the best feasible decision under a specified objective and constraint structure; linear, mixed-integer, and nonlinear formulations are appropriate for different relationships.

A professional model is more than a collection of formulas. It requires explicit boundaries, unit consistency, validated data, transparent assumptions, robust spreadsheet architecture, and governance. Strategic decisions should consider service, resilience, sustainability, ethics, and downside risk alongside expected cost. The most defensible recommendation is usually not the one with the lowest modeled average, but the one that remains acceptable across plausible conditions and can be monitored after implementation.

Key Terms

Decision variable: A value controlled by the decision maker.

Parameter: An input value treated as known or estimated in a model.

Objective function: The measure a model maximizes or minimizes.

Constraint: A condition limiting feasible decisions.

Scenario analysis: Evaluation of outcomes under coherent sets of assumptions.

Sensitivity analysis: Systematic variation of one or more inputs to assess influence.

Goal seeking: Finding an input that produces a specified output target.

Simulation: Repeated evaluation of a model using sampled uncertain inputs.

Deterministic model: A model whose inputs are treated as fixed.

Stochastic model: A model that represents uncertain inputs or outcomes.

Linear programming: Optimization with linear objective and constraint relationships.

Mixed-integer programming: Optimization including continuous and discrete variables.

Binding constraint: A constraint satisfied exactly at a solution and limiting improvement.

Shadow price: Estimated marginal value of relaxing a constraint within a valid range.

Break-even point: The activity level at which alternatives have equal modeled cost or value.

Cost-to-serve: Revenue or margin evaluated after the resources required to serve a customer or channel.

Cycle-service level: Probability of meeting demand without a stockout during a replenishment cycle.

Model validation: Testing whether data, formulas, logic, and outputs are credible.

Sensitivity range: The interval over which a model’s reported marginal relationships remain applicable.

Review Questions

5 Short Answer Questions

1. What are the five principal elements of a supply-chain decision model?
2. How does scenario analysis differ from sensitivity analysis?
3. State two assumptions underlying the classical economic order quantity formula.
4. Why might a sourcing model require binary variables?
5. What is the purpose of a binding-constraint or shadow-price analysis?

5 Long Answer Questions

1. Explain how you would design a spreadsheet model for a dual-sourcing decision, including inputs, variables, constraints, outputs, and validation checks.
2. Compare deterministic optimization and stochastic simulation in terms of purpose, inputs, outputs, and managerial use.
3. Discuss how cost-to-serve analysis can change customer, channel, or product decisions compared with revenue-based analysis.
4. Explain the limitations of using on-time delivery percentages as direct probabilities in a sourcing model.
5. Develop a governance framework for a spreadsheet used to approve a major transportation or inventory decision.

3 Analytical / Application Questions

1. A supplier offers a lower unit price but has lower capacity and poorer service. Design a scenario-and-simulation analysis to determine whether dual sourcing is justified. Specify at least five uncertain inputs and four outputs.
2. A distribution center has a fixed annual operating cost, a variable handling cost, and a competing outsourced option. Formulate the break-even analysis and identify the conditions under which the result would be unreliable.
3. Construct a linear or mixed-integer optimization formulation for allocating demand among three suppliers subject to demand satisfaction, capacity, minimum awards, a dual-sourcing rule, and a service-level requirement. Explain how you would test the recommendation for robustness.

Multiple Choice Questions with Answers

Question 1

Which element of a decision model represents a value controlled by the manager?

A. Parameter

B. Decision variable

C. Constraint

D. Historical observation

Correct Answer: B. Decision variable

Question 2

A company wants to determine the purchase price that permits a target contribution margin. Which spreadsheet technique is most directly appropriate for one changing input?

A. Goal seeking

B. Random simulation

C. Data cleansing

D. Network optimization

Correct Answer: A. Goal seeking

Question 3

Which condition is required for the classical economic order quantity formula to be directly applicable?

A. Highly variable demand and lead time

B. Constant demand and no shortages

C. Mandatory quantity discounts

D. Multiple binary supplier decisions

Correct Answer: B. Constant demand and no shortages

Question 4

In the sourcing model, what does a binary variable usually indicate?

A. The exact number of units shipped

B. Whether a supplier or route is selected

C. The standard deviation of demand

D. The currency conversion rate

Correct Answer: B. Whether a supplier or route is selected

Question 5

What is the principal purpose of sensitivity analysis?

A. To prove that a forecast is correct

B. To vary inputs and assess their influence on an output

C. To replace all operational constraints

D. To guarantee an optimal decision under uncertainty

Correct Answer: B. To vary inputs and assess their influence on an output

Question 6

A simulation runs 20,000 iterations, and 1,000 iterations produce a stockout. What is the estimated stockout probability?

A. 0.5%

B. 2%

C. 5%

D. 20%

Correct Answer: C. 5%

Question 7

Which model type is required when a sourcing problem includes continuous quantities and yes-or-no supplier-selection decisions?

A. Simple descriptive model

B. Mixed-integer programming model

C. Moving-average model only

D. Unconstrained regression model

Correct Answer: B. Mixed-integer programming model

Question 8

If supplier capacity is binding in an optimization solution, what does that indicate?

A. The capacity is unused

B. The capacity limits improvement under the current model

C. The supplier must be eliminated

D. The objective function is invalid

Correct Answer: B. The capacity limits improvement under the current model

Question 9

Why can independent sampling of all uncertain supply-chain variables be misleading?

A. It always produces too many iterations

B. It may ignore correlations and understate joint risk

C. It prevents the use of averages

D. It makes all constraints nonlinear

Correct Answer: B. It may ignore correlations and understate joint risk

Question 10

In the illustrative dual-sourcing example, why did multiple allocations initially have the same modeled cost?

A. Both suppliers had identical capacities

B. Their effective variable costs were equal and fixed costs were unchanged

C. Demand was zero

D. The model excluded all supplier costs

Correct Answer: B. Their effective variable costs were equal and fixed costs were unchanged