Scenario planning functions

Look at the business as it is and as it could be.

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.

PLANNING_DASHBOARD(period, [periodEnd], [fields], [locationId])
PLANNING_CFO_SUMMARY(period, [periodEnd], [locationId])
PLANNING_LOAD_SCENARIOS([scenarioType])
PLANNING_PROFITABILITY_PANELS(period, [periodEnd], [locationId])
REPLAY_PROFIT_GUARD(accountId, period, [periodEnd], [contextId])

Parameters and arguments

Arguments are written in brackets, separated by commas. An argument in square brackets is optional.

  • PLANNING_DASHBOARD

    period

    Required: Yes
  • periodEnd

    Required: No
  • fields

    Required: No
  • locationId

    Required: No
  • PLANNING_CFO_SUMMARY

    period

    Required: Yes
  • periodEnd

    Required: No
  • locationId

    Required: No
  • PLANNING_LOAD_SCENARIOS

    scenarioType

    Required: No
  • PLANNING_PROFITABILITY_PANELS

    period

    Required: Yes
  • periodEnd

    Required: No
  • locationId

    Required: No
  • REPLAY_PROFIT_GUARD

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • contextId

    Required: No

Examples

A headline, the key driver, the downside, a recommendation and a risk score, drawn from the base, downside and severe stress scenarios.

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_DASHBOARDrecordThe planning page's sections computed togetherPLANNING_DASHBOARD("ROLLING_90_DAYS", "cfoSummary")
PLANNING_CFO_SUMMARYrecordA headline, key driver, downside, recommendation and risk score from the scenario resultsPLANNING_CFO_SUMMARY("ROLLING_90_DAYS")
PLANNING_LOAD_SCENARIOSrecordThe 50 most recently saved scenariosPLANNING_LOAD_SCENARIOS()
PLANNING_PROFITABILITY_PANELSrecordFour panels: true profit, customer quality, inventory movement and takeover riskPLANNING_PROFITABILITY_PANELS("ROLLING_90_DAYS")
REPLAY_PROFIT_GUARDrecordProfit, margins, capital efficiency and leakage for a period, worked out again from the recorded factsREPLAY_PROFIT_GUARD("ACCOUNT_ID", "ROLLING_90_DAYS")

PLANNING_CFO_SUMMARY answers

FieldTypeMeaning
headlinetextOne sentence: value destruction risk, profitable but below the cost of capital, profitable but fragile, or profitable and value creating
keyDrivertextWhether revenue sensitivity or cost structure moves profit most
downsideRisktextWhat happens under severe stress
recommendationtextThe action that follows from the headline
confidencedecimalConfidence in the projection
riskScorewhole number0 to 100, from base profit, downside profit, the return spread and stress
baseProfit, downsideProfit, stressProfitmoneyTrue profit over the horizon in the base, downside and severe stress scenarios
baseSpreaddecimalThe base scenario's return on capital less the cost of capital

PLANNING_DASHBOARD answers

FieldTypeMeaning
continuationForecastrecordThe answer of PLANNING_CONTINUATION_FORECAST for 12 months
cfoSummaryrecordThe answer of PLANNING_CFO_SUMMARY
savedScenariosrecordThe answer of PLANNING_LOAD_SCENARIOS for all types
profitabilityPanelsrecordThe answer of PLANNING_PROFITABILITY_PANELS

PLANNING_LOAD_SCENARIOS answers

FieldTypeMeaning
scenarioslist of recordsscenarioId, scenarioName, scenarioType, discount, demandLift, priceChange, shippingShock, cogsShock (all percent numbers), investment (money), projectName and createdAt, newest first
countwhole numberHow many scenarios are listed

PLANNING_PROFITABILITY_PANELS answers

FieldTypeMeaning
trueProfitrecordtrueProfit, revenue, trueProfitMargin, contribution, grossMargin, contributionMargin, roic, wacc, economicProfit, breakEvenRevenue, totalDiscount, totalFixedCost, fixedCostMonthly, momTrend (a row for each month) , riskFlag and months
customerQualityrecordtotalCustomers, newCustomers, repeatCustomers, repeat, new, newToRepeat and riskFlag
inventoryMovementrecordtotalUnitsSold, avgInventoryUnits, inventoryChurn, inventoryValue, grossProfit, gmroi and riskFlag
takeoverRiskrecordspread, roic, wacc, valueCreationFlag, cv, marginTrend, meanMargin, stdDev, stabilityFlag, fixed, discount and costPressureFlag

REPLAY_PROFIT_GUARD answers

FieldTypeMeaning
tenantId, asOf, contextIdtextYour account, the period's date and the id you passed
revenue, cogs, contribution, trueProfitmoneyThe period's money lines
grossMargin, contributionMarginpercent numberMargins
breakEvenRevenuemoneyRevenue needed to cover costs
roic, wacc, spreadpercent numberReturn on capital, the cost of capital, and the gap between them
economicProfitmoneyProfit above the cost of capital
shippingLeakage, digitalCostLeakage, discountLeakagemoneyMoney lost to free shipping, software cost and discounts
warningslist of recordscode, message, severity: advisory notes about missing data or breached limits
classificationslist of recordsdimension, label and value: for example capital efficiency as VALUE_CREATING or VALUE_DESTROYING
traceslist of recordsstep, formula, value, inputs and notes, so an auditor can see how a result was made
settingsVersion, policyVersionwhole numbersThe versions of the settings and policies in force on that date
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
  • The scenario functions need at least 3 months of order history. With less they answer {error, message} (or the sentence form for PLANNING_CFO_SUMMARY).
  • PLANNING_CFO_SUMMARY, PLANNING_DASHBOARD and PLANNING_PROFITABILITY_PANELS accept a period, but the scenarios behind them learn from the last 12 months of orders.
  • Saving a scenario is not a function call. PLANNING_LOAD_SCENARIOS lists what has been saved.
Permissions and security

These functions read your orders, per order profit, customers, order lines, inventories, saved scenario definitions, settings and the recorded journals. 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
  • Start a review with PLANNING_CFO_SUMMARY, then open PLANNING_PROFITABILITY_PANELS for the cause.
  • Use REPLAY_PROFIT_GUARD to settle an argument about a past period: it reads the recorded facts, so it does not drift.
  • Ask PLANNING_DASHBOARD for named sections.