Cash flow and cost exposure functions
See cash, margin and cost risks coming before they land.
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.
FORECAST_PROFIT_GUARD takes the number of months and a scenario name. FREIGHT_VOLATILITY and SHIPMENT_DELAY_IMPACT take one id.
FORECAST_CASHFLOW(accountId, period, [periodEnd])
FORECAST_PROFIT_GUARD(monthsAhead, scenario)
PLANNING_PROJECT_PRO_FORMA(
period,
[periodEnd],
projectName,
investment,
monthlyRevenue,
monthlyOperatingCost,
horizonMonths,
taxRate
)
FREIGHT_VOLATILITY(supplierId)
SHIPMENT_DELAY_IMPACT(poId)Parameters and arguments
Arguments are written in brackets, separated by commas. An argument in square brackets is optional.
FORECAST_CASHFLOW
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEndFORECAST_PROFIT_GUARD
Required: YesmonthsAhead
Required: YesscenarioPLANNING_PROJECT_PRO_FORMA
Required: Yesperiod
Required: NoperiodEnd
Required: YesprojectName
Required: Yesinvestment
Required: YesmonthlyRevenue
Required: YesmonthlyOperatingCost
Required: YeshorizonMonths
Required: YestaxRateFREIGHT_VOLATILITY
Required: YessupplierIdSHIPMENT_DELAY_IMPACT
Required: YespoId
Examples
Daily inflows and outflows for the last 90 days, the running balance, and a 30 day projection of net cash with a band.
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 |
|---|---|---|---|
FORECAST_CASHFLOW | record | Daily cash inflows and outflows, the running balance, a 30 day net cash projection with a band, and its discounted value | FORECAST_CASHFLOW("ACCOUNT_ID", "ROLLING_90_DAYS") |
FORECAST_PROFIT_GUARD | record | Monthly revenue, profit, asset value and enterprise value projections over the next months for one scenario | FORECAST_PROFIT_GUARD(12, "BASE") |
PLANNING_PROJECT_PRO_FORMA | record | The net present value, internal rate of return, payback and a decision for a project, with its monthly cash flows | PLANNING_PROJECT_PRO_FORMA("ROLLING_90_DAYS", "New warehouse", 10000000, 1200000, 500000, 36, 2500) |
FREIGHT_VOLATILITY | record | The latest shipment's freight cost per unit against the 6 month average for a supplier, and which way it is moving | FREIGHT_VOLATILITY("SUPPLIER_ID") |
SHIPMENT_DELAY_IMPACT | record | Delay days, units affected and value at risk for each shipment on a purchase order | SHIPMENT_DELAY_IMPACT("PO_ID") |
FORECAST_CASHFLOW answers
| Field | Type | Meaning |
|---|---|---|
dates | list of text | The days in the range |
inflows, outflows | list of whole numbers | Cash in and cash out for each day |
cogs, fees, opex | list of whole numbers | The cost of goods, gateway fees and prepaid expense charge that make up the outflows |
net | list of whole numbers | Inflows less outflows for each day |
netFitted, ewmaNet | list of whole numbers | The trend line and a smoothed series of net cash |
cumulative | list of whole numbers | The running total of net cash |
forecastDates | list of text | +1d to +30d |
forecastNet, upperBand, lowerBand | list of whole numbers | Projected net cash and its 95 per cent band |
netCashSlope, inflowSlope, outflowSlope | text | Daily change in net cash, inflows and outflows |
npvProjected30d | text | The projection discounted at 10 per cent a year |
cumulativeInterest, cumulativePrincipal | text | The interest and principal of repaying the latest day's outflow over 12 months at 10 per cent |
currency | text | The account currency |
FORECAST_PROFIT_GUARD answers
| Field | Type | Meaning |
|---|---|---|
ok | true or false | False when there is too little history |
tenantId, scenario, monthsAhead | text and whole numbers | Your account, the scenario in upper case and the months projected |
wacc | percent number | The account's cost of capital |
historyMonths | whole number | Months of history used, from the last 12 |
revenueSlope, revenueIntercept | money | The monthly revenue trend |
projections | list of records | periodAhead, projectedRevenue, projectedContribution, projectedProfit, depreciationImpact, nbvAfter, discountedProfit and cumulativeEv, all money except periodAhead |
With fewer than 3 months of history the answer is ok false with error and message and no projections. An unknown scenario is refused.
PLANNING_PROJECT_PRO_FORMA answers
| Field | Type | Meaning |
|---|---|---|
projectName | text | The name you gave |
npv | money | Net present value of the project |
irr | decimal | The internal rate of return as a yearly fraction |
paybackMonths | whole number | The month in which the running total turns positive |
roic, wacc | decimal | The return on invested capital and the cost of capital |
nopat, economicProfit | money | Profit after tax and the profit above the cost of capital |
decision | text | APPROVE when NPV is positive and ROIC beats the cost of capital, REVIEW when only NPV is positive, otherwise REJECT |
investment, horizonMonths, taxRate | as given | The inputs, echoed back |
monthlyCashflows | list of records | month, revenue, cost, netCashFlow and cumulative |
FREIGHT_VOLATILITY answers
| Field | Type | Meaning |
|---|---|---|
supplierId | text | The supplier |
currentFreightPerUnit | number | Freight cost per unit on the most recent shipment, in the stored whole number form |
avgFreightPerUnit | number | The average over the last 6 months of shipments |
variance | percent number | How far the latest shipment is from the average, as a percentage (the engine writes it as varianceBps and returns it as a percent) |
trend | text | up, down or stable. A move of more than 1 per cent either way counts as up or down |
SHIPMENT_DELAY_IMPACT answers
| Field | Type | Meaning |
|---|---|---|
poId | text | The purchase order |
shipmentCount, delayedCount | whole numbers | Shipments on the order and how many are late |
totalDelayDays | number | Days late summed over the late shipments |
shipments | list of records | shipmentId, carrierName, expectedArrival, actualArrival, status, delayDays, isDelayed, unitsAffected and valueAtRisk |
A shipment that has not arrived and is past its expected date counts its days so far as delay.
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
FORECAST_CASHFLOWneeds at least 3 days of cash flow data in the range, and answers empty lists below that.- The 10 per cent discount rate in
FORECAST_CASHFLOWis fixed. PLANNING_PROJECT_PRO_FORMAaccepts whole numbers only: a decimal such as12.50is cut to12. Write amounts in the smallest unit of the currency.PLANNING_PROJECT_PRO_FORMAaccepts the period but models the project from the amounts you give, not from the period.
Permissions and security
These functions read your cash flow summary, cost of goods, prepaid expenses, order history, fixed assets, purchase order shipments and shipment allocations. 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
- Read
lowerBandfor the cash you can count on, notforecastNet. - Run
FORECAST_PROFIT_GUARDwithBASEandWORSTtogether and compare the two. - Call
SHIPMENT_DELAY_IMPACTfor each open purchase order to see which delays tie up the most value.