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
Required: Yesperiod
Required: NoperiodEnd
Required: Nofields
Required: NolocationIdPLANNING_CFO_SUMMARY
Required: Yesperiod
Required: NoperiodEnd
Required: NolocationIdPLANNING_LOAD_SCENARIOS
Required: NoscenarioTypePLANNING_PROFITABILITY_PANELS
Required: Yesperiod
Required: NoperiodEnd
Required: NolocationIdREPLAY_PROFIT_GUARD
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEnd
Required: NocontextId
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.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
PLANNING_DASHBOARD | record | The planning page's sections computed together | PLANNING_DASHBOARD("ROLLING_90_DAYS", "cfoSummary") |
PLANNING_CFO_SUMMARY | record | A headline, key driver, downside, recommendation and risk score from the scenario results | PLANNING_CFO_SUMMARY("ROLLING_90_DAYS") |
PLANNING_LOAD_SCENARIOS | record | The 50 most recently saved scenarios | PLANNING_LOAD_SCENARIOS() |
PLANNING_PROFITABILITY_PANELS | record | Four panels: true profit, customer quality, inventory movement and takeover risk | PLANNING_PROFITABILITY_PANELS("ROLLING_90_DAYS") |
REPLAY_PROFIT_GUARD | record | Profit, margins, capital efficiency and leakage for a period, worked out again from the recorded facts | REPLAY_PROFIT_GUARD("ACCOUNT_ID", "ROLLING_90_DAYS") |
PLANNING_CFO_SUMMARY answers
| Field | Type | Meaning |
|---|---|---|
headline | text | One sentence: value destruction risk, profitable but below the cost of capital, profitable but fragile, or profitable and value creating |
keyDriver | text | Whether revenue sensitivity or cost structure moves profit most |
downsideRisk | text | What happens under severe stress |
recommendation | text | The action that follows from the headline |
confidence | decimal | Confidence in the projection |
riskScore | whole number | 0 to 100, from base profit, downside profit, the return spread and stress |
baseProfit, downsideProfit, stressProfit | money | True profit over the horizon in the base, downside and severe stress scenarios |
baseSpread | decimal | The base scenario's return on capital less the cost of capital |
PLANNING_DASHBOARD answers
| Field | Type | Meaning |
|---|---|---|
continuationForecast | record | The answer of PLANNING_CONTINUATION_FORECAST for 12 months |
cfoSummary | record | The answer of PLANNING_CFO_SUMMARY |
savedScenarios | record | The answer of PLANNING_LOAD_SCENARIOS for all types |
profitabilityPanels | record | The answer of PLANNING_PROFITABILITY_PANELS |
PLANNING_LOAD_SCENARIOS answers
| Field | Type | Meaning |
|---|---|---|
scenarios | list of records | scenarioId, scenarioName, scenarioType, discount, demandLift, priceChange, shippingShock, cogsShock (all percent numbers), investment (money), projectName and createdAt, newest first |
count | whole number | How many scenarios are listed |
PLANNING_PROFITABILITY_PANELS answers
| Field | Type | Meaning |
|---|---|---|
trueProfit | record | trueProfit, revenue, trueProfitMargin, contribution, grossMargin, contributionMargin, roic, wacc, economicProfit, breakEvenRevenue, totalDiscount, totalFixedCost, fixedCostMonthly, momTrend (a row for each month) , riskFlag and months |
customerQuality | record | totalCustomers, newCustomers, repeatCustomers, repeat, new, newToRepeat and riskFlag |
inventoryMovement | record | totalUnitsSold, avgInventoryUnits, inventoryChurn, inventoryValue, grossProfit, gmroi and riskFlag |
takeoverRisk | record | spread, roic, wacc, valueCreationFlag, cv, marginTrend, meanMargin, stdDev, stabilityFlag, fixed, discount and costPressureFlag |
REPLAY_PROFIT_GUARD answers
| Field | Type | Meaning |
|---|---|---|
tenantId, asOf, contextId | text | Your account, the period's date and the id you passed |
revenue, cogs, contribution, trueProfit | money | The period's money lines |
grossMargin, contributionMargin | percent number | Margins |
breakEvenRevenue | money | Revenue needed to cover costs |
roic, wacc, spread | percent number | Return on capital, the cost of capital, and the gap between them |
economicProfit | money | Profit above the cost of capital |
shippingLeakage, digitalCostLeakage, discountLeakage | money | Money lost to free shipping, software cost and discounts |
warnings | list of records | code, message, severity: advisory notes about missing data or breached limits |
classifications | list of records | dimension, label and value: for example capital efficiency as VALUE_CREATING or VALUE_DESTROYING |
traces | list of records | step, formula, value, inputs and notes, so an auditor can see how a result was made |
settingsVersion, policyVersion | whole numbers | The versions of the settings and policies in force on that date |
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
- The scenario functions need at least 3 months of order history. With less they answer
{error, message}(or the sentence form forPLANNING_CFO_SUMMARY). PLANNING_CFO_SUMMARY,PLANNING_DASHBOARDandPLANNING_PROFITABILITY_PANELSaccept 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_SCENARIOSlists 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 openPLANNING_PROFITABILITY_PANELSfor the cause. - Use
REPLAY_PROFIT_GUARDto settle an argument about a past period: it reads the recorded facts, so it does not drift. - Ask
PLANNING_DASHBOARDfor named sections.