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

    period

    Required: Yes
  • periodEnd

    Required: No
  • iterations

    Required: Yes
  • seed

    Required: Yes
  • locationId

    Required: No
  • PLANNING_COST_SHOCK

    period

    Required: Yes
  • periodEnd

    Required: No
  • shipping

    Required: Yes
  • fulfilment

    Required: Yes
  • software

    Required: Yes
  • payment

    Required: Yes
  • cogs

    Required: Yes
  • wages

    Required: Yes
  • rent

    Required: Yes
  • PLANNING_MACRO_STRESS

    period

    Required: Yes
  • periodEnd

    Required: No
  • interestRateRise

    Required: Yes
  • inflation

    Required: Yes
  • currencyShock

    Required: Yes
  • demandContraction

    Required: Yes
  • wageInflation

    Required: Yes
  • PLANNING_CONTINUATION_FORECAST

    period

    Required: Yes
  • periodEnd

    Required: No
  • horizonMonths

    Required: No
  • locationId

    Required: No

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.

FunctionReturnsBehaviourForm
PLANNING_MONTE_CARLOrecordThe spread of yearly profit across many simulated futures, and the chance of a lossPLANNING_MONTE_CARLO("ROLLING_365_DAYS", 10000, 42)
PLANNING_COST_SHOCKrecordProfit after each cost rises by the amount you name, with a waterfall and the loss making ordersPLANNING_COST_SHOCK("ROLLING_30_DAYS", 1000, 500, 800, 200, 600, 400, 300)
PLANNING_MACRO_STRESSrecordReturn on capital, economic profit and value at risk under a macro downturnPLANNING_MACRO_STRESS("ROLLING_90_DAYS", 200, 400, 500, 1000, 300)
PLANNING_CONTINUATION_FORECASTrecordFour scenarios, base, downside, severe stress and best case, projected month by monthPLANNING_CONTINUATION_FORECAST("ROLLING_365_DAYS", 12)

PLANNING_MONTE_CARLO answers

FieldTypeMeaning
iterations, seedwhole numbersThe draws used and the seed, after the range limit
p5, p25, p50, p75, p95moneyProfit at those percentiles of the simulated years
expectedProfitmoneyThe average simulated profit
probabilityOfLossdecimalThe share of simulated years that lose money, 0 to 1
probabilityOfMarginBreachdecimalThe share that fall below your margin floor
probabilityOfRoicBelowWaccdecimalThe share where return on capital is under the cost of capital
var95moneyThe loss not exceeded in 95 per cent of years
waccdecimalThe cost of capital used
historyMonthswhole numberMonths of history the draws come from
distributionParamsrecordrevenueMean, revenueStdDev, variableCostMin, variableCostMode and variableCostMax: the ranges the draws were taken from

PLANNING_COST_SHOCK answers

FieldTypeMeaning
baselinerecordrevenue, contribution and trueProfit for the last 30 days
shockedrecordThe same, plus ebitda, after the cost rises
deltarecordcontribution and trueProfit change
waterfallStepslist of recordsname, value and isTotal, from baseline profit through each cost to shocked profit
breakEvenOrderswhole numberOrders needed to cover costs after the shock
lossMakingOrderPctpercent numberThe share of orders that lose money after the shock
shocksAppliedrecordThe seven rises, echoed as percent numbers
riskFlagslist of recordscode, severity, reason and financialImpact
tracerecordallocationMethod, baselineOrderCount, baselinePeriodDays, wagesShareOfFixedCosts and rentShareOfFixedCosts

PLANNING_MACRO_STRESS answers

FieldTypeMeaning
baselinerecordrevenue, nopat, roic, wacc and economicProfit over the last 90 days
stressedrecordThe same after the shock, plus var95
deltarecordThe change in each of those figures
stressParametersrecordinterestRateDelta, inflation, fxShock, demandContraction and wageInflation as fractions
takeoverVulnerabilityScorewhole numberHow exposed the stressed business is to a takeover
tracerecordThe rates and weights used: baseRiskFreeRate, stressedRiskFreeRate, beta, marketReturn, costOfEquity, costOfDebt, taxRate and equityWeight

PLANNING_CONTINUATION_FORECAST answers

FieldTypeMeaning
scenarioslist of recordsOne 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
confidencedecimalHow much to trust the projection, from how well the history fits
historyMonths, forecastMonthswhole numbersMonths of history used and months projected
settingsVersionwhole numberThe version of your cost settings used
rSquareddecimalThe 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
ErrorWhat happenedHow to recover
HTQL-5001The function was given the wrong number of arguments. The message names the function and the countsGive it the arguments listed under Parameters
HTQL-1004 Unsupported functionThe name is not a function this engine runsCheck the name and its spelling against this entry
Limitations
  • PLANNING_MONTE_CARLO and PLANNING_CONTINUATION_FORECAST need 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 period argument 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 seed when you compare two settings, so the difference comes from the setting, not the draw.
  • Use 10,000 iterations or more for a stable p5 and var95.
  • Pair PLANNING_COST_SHOCK with PLANNING_MACRO_STRESS: one tests costs, the other the market.