Stochastic Optimization in Excel | Vose Software

Guide · Optimization

Stochastic Optimization in Excel

How to choose a plan when the inputs are uncertain, with a worked example and its efficient frontier

Last updated November 20269 min read

What is stochastic optimization?

Stochastic optimization finds the decisions that give the best result when some of the inputs are uncertain. Instead of optimizing a single forecast, it simulates each candidate plan across many possible futures and judges the plan by a statistic of the simulated result, such as the average, a bad-year percentile or the chance of a loss, inside limits you set.

In a spreadsheet that means three kinds of cells. Decision cells hold what you control: how many fields to plant with each crop, which projects to fund, how much reserve to hold. Uncertain inputs are probability distributions rather than single numbers: prices, yields, durations, demand. The output is the result you care about, such as profit or cost. A Monte Carlo simulation turns one plan into a distribution of that output, and the optimization searches for the plan whose distribution is best by the measure you chose.

The term has a second meaning. In machine learning, “stochastic optimization” also covers search methods that use randomness themselves, such as stochastic gradient descent, even when the problem has no uncertain inputs. This page is about the first meaning: decisions under uncertainty, which is also called optimization under uncertainty or simulation optimization.

How is it different from optimizing with Excel Solver?

Excel’s Solver optimizes a model with fixed inputs, so it can only optimize the result of one version of the future, usually the average case. A stochastic optimization simulates every plan it tries, so it can also aim for the best result in a bad year, or keep the chance of a loss or a budget overrun below a limit.

The difference matters because a plan that is best on average inputs can carry risks that nobody chose. Average inputs also do not always give the average result: when costs are capped, delays merge or options are exercised, the result of the average case is not the average of the results. What each approach can be asked to do:

What you ask forSolver, fixed inputsStochastic optimization
Highest profit in an average yearYes, on the inputs you typedYes: maximize the mean
Highest profit in a bad yearNo: a fixed model has no bad yearYes: maximize the P10
Lowest cost with at most a 10% chance of a budget overrunNoYes: a probability limit
Smallest average loss in the worst 5% of outcomesNoYes: minimize the CVaR
A result brought as close as possible to a targetYesYes

A P10 is the value with a 10% chance of a worse result, so it describes a bad year. CVaR, conditional value at risk, is the average of the worst outcomes beyond a chosen percentile.

What does a stochastic optimization model need?

Three things on top of a working simulation model: the decisions you may change and their bounds; an objective, which is a statistic of a simulated output to maximize or minimize; and the limits that must hold, written as statistics too. Everything else, the formulas linking decisions and uncertain inputs to the output, is the model you already have.

PartWhat it isExamples
DecisionsCells you control, each with a type and boundsFields per crop, as a whole number; fund a project, yes or no; a reserve per cost line in euros
ObjectiveA statistic of one simulated output, to maximize, minimize or bring to a targetMaximize the mean revenue; maximize the P10 profit; minimize the mean daily cost
ConstraintsLimits on statistics of outputs, hard or with a penaltyStandard deviation of revenue at most $7k; chance of a loss at most 10%; 95th percentile of brine toxicity at most 34

A limit that has to hold with a stated probability, such as “under budget on 95% of outcomes”, is called a chance constraint. It is the most direct way to write a risk appetite into the model, and it is exactly what an optimizer working on fixed inputs cannot express.

A worked example: how should a farm plant ten fields?

A farm plants ten whole fields with five crops, from steady wheat to valuable but volatile vineyard, and the season’s weather moves every crop together. Planted for the highest average season, the plan is 4 sunflower and 6 vineyard, expecting $75.8k with a swing of $15k. Limiting the swing to $7k gives a different plan that expects $46.5k.

The figures come from the Farm Parcels example that comes with ModelRisk Optimizer. The decisions are the number of fields given to each crop, as whole numbers that add up to ten. The objective is the expected revenue of the season. The constraint is the swing, measured as one standard deviation of season revenue, and the optimization is run once for each limit from $3k to $15k. Each row is the exact optimum of the example’s model for that limit:

Swing up toExpected revenue1 season in 10 belowWheatMaizeSoybeanSunflowerVineyard
$3k$26.2k$22.7k63100
$5k$37.0k$30.6k32410
$7k$46.5k$37.5k21421
$9k$55.6k$44.1k01522
$11k$62.7k$48.7k01243
$13k$69.6k$53.1k00154
$15k$75.8k$56.6k00046

Solver, working on one average season, would stop at the last row: it finds the plan with the highest average and cannot report how much that plan’s revenue moves from season to season. The simulation shows the swing of every plan, so the optimization can keep it inside whatever limit the farm sets, and the table shows what each limit costs in expected revenue.

The third column holds a lesson of its own. Even one season in ten, the riskier plans earn more here, because vineyard and sunflower are worth so much more on average. A farm that only cares about its bad years would still plant the last row, and an objective of “maximize the P10” would say so. The swing limit is the right formulation when steadiness has value in itself, for example when revenue must cover fixed payments every season. Deciding what “best” means is part of the model, and a different statistic can give a different answer.

What is an efficient frontier?

An efficient frontier is the curve of the best result you can get at each level of a risk limit. Every point on it is a full optimization, so no plan does better on both counts. Plans below the curve give up return for nothing, and the shape of the curve shows how much each extra unit of risk buys.

The farm’s efficient frontier

Best expected season revenue at each limit on the swing. Each point is one optimization of the Farm Parcels example.

$20k $40k $60k $80k $3k $5k $7k $9k $11k $13k $15k Swing you accept (one standard deviation of season revenue) $46.5k at a $7k swing 2 wheat, 1 maize, 4 soybean, 2 sunflower, 1 vineyard $75.8k: highest average 4 sunflower, 6 vineyard

Each extra $2k of swing buys less: $10.8k, then $9.5k, $9.1k, $7.1k, $6.9k and $6.2k of expected revenue.

Reading the farm’s curve from left to right, the first $2k of extra swing adds $10.8k of expected revenue and the last adds $6.2k. Where to stop is a judgement about risk appetite rather than arithmetic, but the frontier turns it into a choice between named plans with known prices, instead of a single answer from an average case.

The same shape turns up wherever a return has to be traded against a risk: expected value against the chance of missing a target in a project portfolio, or the size of a reserve against the confidence of covering the overrun, which is the question behind how much contingency to hold.

How do you run a stochastic optimization in Excel?

You need a simulation inside the optimization loop. In plain Excel the workaround is to fix a set of random scenarios and let Solver optimize the average across them. An add-in such as ModelRisk does it directly: you mark the decision cells, choose the statistic to optimize and the limits, and it simulates every plan it tries.

Plain Excel, with fixed scenarios. Generate a few hundred scenarios of the uncertain inputs, paste them as values so that they stop changing, and calculate the output for every scenario in its own row from the same decision cells. Solver can then maximize the average of that column, or count the scenarios that break a limit. This is called sample average approximation. It works for small models, but the model has to be laid out once per scenario, limits on counts and percentiles make the problem hard for Solver’s smooth methods, and the answer depends on the one sample you drew.

With ModelRisk. The Optimizer opens from the ModelRisk ribbon and works on the model as it is, with its distributions in place. You add the decision cells with a type and bounds (continuous, whole number, stepped, from a list, or yes or no), pick the objective from 18 statistics of a simulated output, including the mean, a percentile, a probability and CVaR, and add hard or soft limits. The set-up is saved inside the workbook.

The Desalination example that comes with the Optimizer shows a chance constraint at work. It chooses a whole-number dose from 0 to 8 for each of six reverse-osmosis trains to minimize the mean daily cost, while the 95th percentile of brine toxicity must stay at or below 34. The run tried 400 plans, simulated each 1,000 times, and found the best at trial 239. Efficient Frontier Analysis then re-runs the optimization across a range of one limit, which is how the farm’s curve above is traced.

To try it on the examples or on your own model, start the free 15-day ModelRisk trial. The example workbooks open with their set-up saved, so you can press Start and watch one run first.

Where is stochastic optimization used?

Wherever a plan has to be fixed before the uncertainty plays out and some outcomes are worse than others: which projects to fund, how much reserve to hold, how much stock to buy, and how to allocate capital across lines of business. The decision is made once, and the simulation shows how it fares across the futures that might follow.

ProblemDecisionsTypical objective and limit
Project portfolioFund each project, yes or no, within the capital budgetHighest expected value, with a cap on the chance of missing the return target
Contingency and reservesReserve per cost lineSmallest total reserve that still meets the confidence target
Inventory and capacityOrder quantities or capacity, in whole unitsLowest expected cost, with a limit on the chance of running out
Insurance and financeCapital or limits per line of businessHighest expected return, with a cap on the CVaR of the loss

Frequently asked questions

Is stochastic optimization the same as stochastic gradient descent?

No. Stochastic gradient descent is a search method that uses random samples of the data to estimate each step, mostly to train machine-learning models. Both go by the name stochastic optimization, but this page is about the other meaning: choosing decisions when the inputs of the model are uncertain.

What is a chance constraint?

A limit that must hold with at least a stated probability rather than always, such as a chance of a loss of no more than 10% or a cost that stays under budget on 95% of outcomes. It is the usual way to write a risk appetite into an optimization model.

How many iterations does each plan need?

Enough for the statistic you optimize to settle, because two plans can only be ranked if the difference between them is larger than the simulation noise. A mean settles quickly; a P95 or a CVaR in the tail needs more iterations. The desalination example on this page simulates each plan 1,000 times. More on how many iterations a simulation needs.

Is the answer the same every time you run it?

Not unless you fix the random seed. Each run samples different futures, so plans whose results are close can swap places between runs. Setting a seed makes a run repeatable, and checking the best few plans with a longer simulation shows whether the difference between them is real.

ModelRisk logo

ModelRisk

Optimization under uncertainty, in the workbook you already use

The Optimizer is included in every ModelRisk licence. Mark the decisions, choose what “best” means and set the limits; it simulates every plan it tries. Try it free for 15 days.