Monte Carlo and stress test functions
Find out how bad it could get and how likely that is.
Syntax
Write the function's name and its arguments. The period is written one of three ways: a date window name such as ROLLING_90_DAYS (the date window functions), a start date and an end date such as "2026-01-01", "2026-03-31", or an accounting period id.
Percentages in these arguments are whole numbers in hundredths of a percentage point: 100 is 1 per cent.
PLANNING_MONTE_CARLO(period, [periodEnd], iterations, seed, [locationId])
PLANNING_COST_SHOCK(
period,
[periodEnd],
shipping,
fulfilment,
software,
payment,
cogs,
wages,
rent
)
PLANNING_MACRO_STRESS(
period,
[periodEnd],
interestRateRise,
inflation,
currencyShock,
demandContraction,
wageInflation
)
PLANNING_CONTINUATION_FORECAST(period, [periodEnd], [horizonMonths], [locationId])Parameters and arguments
Arguments are written in brackets, separated by commas. An argument in square brackets is optional.
PLANNING_MONTE_CARLO
Required: Yesperiod
Required: NoperiodEnd
Required: Yesiterations
Required: Yesseed
Required: NolocationIdPLANNING_COST_SHOCK
Required: Yesperiod
Required: NoperiodEnd
Required: Yesshipping
Required: Yesfulfilment
Required: Yessoftware
Required: Yespayment
Required: Yescogs
Required: Yeswages
Required: YesrentPLANNING_MACRO_STRESS
Required: Yesperiod
Required: NoperiodEnd
Required: YesinterestRateRise
Required: Yesinflation
Required: YescurrencyShock
Required: YesdemandContraction
Required: YeswageInflationPLANNING_CONTINUATION_FORECAST
Required: Yesperiod
Required: NoperiodEnd
Required: NohorizonMonths
Required: NolocationId
Examples
10,000 simulated years drawn from the last 12 months of results: the spread of profit and the chance of a loss.
Return type and behaviour
Each function returns one value. The table shows its type, what it does and one way to write it; the examples use the functions in realistic statements.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
PLANNING_MONTE_CARLO | record | The spread of yearly profit across many simulated futures, and the chance of a loss | PLANNING_MONTE_CARLO("ROLLING_365_DAYS", 10000, 42) |
PLANNING_COST_SHOCK | record | Profit after each cost rises by the amount you name, with a waterfall and the loss making orders | PLANNING_COST_SHOCK("ROLLING_30_DAYS", 1000, 500, 800, 200, 600, 400, 300) |
PLANNING_MACRO_STRESS | record | Return on capital, economic profit and value at risk under a macro downturn | PLANNING_MACRO_STRESS("ROLLING_90_DAYS", 200, 400, 500, 1000, 300) |
PLANNING_CONTINUATION_FORECAST | record | Four scenarios, base, downside, severe stress and best case, projected month by month | PLANNING_CONTINUATION_FORECAST("ROLLING_365_DAYS", 12) |
PLANNING_MONTE_CARLO answers
| Field | Type | Meaning |
|---|---|---|
iterations, seed | whole numbers | The draws used and the seed, after the range limit |
p5, p25, p50, p75, p95 | money | Profit at those percentiles of the simulated years |
expectedProfit | money | The average simulated profit |
probabilityOfLoss | decimal | The share of simulated years that lose money, 0 to 1 |
probabilityOfMarginBreach | decimal | The share that fall below your margin floor |
probabilityOfRoicBelowWacc | decimal | The share where return on capital is under the cost of capital |
var95 | money | The loss not exceeded in 95 per cent of years |
wacc | decimal | The cost of capital used |
historyMonths | whole number | Months of history the draws come from |
distributionParams | record | revenueMean, revenueStdDev, variableCostMin, variableCostMode and variableCostMax: the ranges the draws were taken from |
PLANNING_COST_SHOCK answers
| Field | Type | Meaning |
|---|---|---|
baseline | record | revenue, contribution and trueProfit for the last 30 days |
shocked | record | The same, plus ebitda, after the cost rises |
delta | record | contribution and trueProfit change |
waterfallSteps | list of records | name, value and isTotal, from baseline profit through each cost to shocked profit |
breakEvenOrders | whole number | Orders needed to cover costs after the shock |
lossMakingOrderPct | percent number | The share of orders that lose money after the shock |
shocksApplied | record | The seven rises, echoed as percent numbers |
riskFlags | list of records | code, severity, reason and financialImpact |
trace | record | allocationMethod, baselineOrderCount, baselinePeriodDays, wagesShareOfFixedCosts and rentShareOfFixedCosts |
PLANNING_MACRO_STRESS answers
| Field | Type | Meaning |
|---|---|---|
baseline | record | revenue, nopat, roic, wacc and economicProfit over the last 90 days |
stressed | record | The same after the shock, plus var95 |
delta | record | The change in each of those figures |
stressParameters | record | interestRateDelta, inflation, fxShock, demandContraction and wageInflation as fractions |
takeoverVulnerabilityScore | whole number | How exposed the stressed business is to a takeover |
trace | record | The rates and weights used: baseRiskFreeRate, stressedRiskFreeRate, beta, marketReturn, costOfEquity, costOfDebt, taxRate and equityWeight |
PLANNING_CONTINUATION_FORECAST answers
| Field | Type | Meaning |
|---|---|---|
scenarios | list of records | One record for each of BASE, DOWNSIDE, SEVERE_STRESS and BEST_CASE: name, monthlySeries (month, revenue, cogs, shippingCost, contribution, trueProfit), totalRevenue, totalContribution, totalTrueProfit, breakEven, survivalMonths, roic, wacc, spread, economicProfit, nopat, margin, riskScore, estimatedLeakage and redFlags |
confidence | decimal | How much to trust the projection, from how well the history fits |
historyMonths, forecastMonths | whole numbers | Months of history used and months projected |
settingsVersion | whole number | The version of your cost settings used |
rSquared | decimal | The fit of the trend to the history |
redFlags entries are {flag, detail} with flag one of MARGIN_BREACH, ROIC_BELOW_WACC, NEGATIVE_ECONOMIC_PROFIT or NEGATIVE_TRUE_PROFIT.
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-5001 | The function was given the wrong number of arguments. The message names the function and the counts | Give it the arguments listed under Parameters |
HTQL-1004 Unsupported function | The name is not a function this engine runs | Check the name and its spelling against this entry |
Limitations
PLANNING_MONTE_CARLOandPLANNING_CONTINUATION_FORECASTneed at least 3 months of order history and answer{error, message}without it.- Each function reads a fixed look back of its own: 12 months for the Monte Carlo and the continuation forecast, 90 days for the macro stress and 30 days for the cost shock. The
periodargument is required but does not change it. - Percent arguments are whole numbers: write 6.5 per cent as
650. A decimal is cut to a whole number. - The scenario factors in the continuation forecast and the macro model's market assumptions are tuned by the platform and are not arguments.
Permissions and security
These functions read your orders, per order profit, settings and macro indicators. The caller needs read access to the records named, and nothing is written.
They are governed by the same read access as the tables they read, so no separate grant is needed beyond it.
Best practices
- Fix the
seedwhen you compare two settings, so the difference comes from the setting, not the draw. - Use 10,000 iterations or more for a stable
p5andvar95. - Pair
PLANNING_COST_SHOCKwithPLANNING_MACRO_STRESS: one tests costs, the other the market.