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

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • FORECAST_PROFIT_GUARD

    monthsAhead

    Required: Yes
  • scenario

    Required: Yes
  • PLANNING_PROJECT_PRO_FORMA

    period

    Required: Yes
  • periodEnd

    Required: No
  • projectName

    Required: Yes
  • investment

    Required: Yes
  • monthlyRevenue

    Required: Yes
  • monthlyOperatingCost

    Required: Yes
  • horizonMonths

    Required: Yes
  • taxRate

    Required: Yes
  • FREIGHT_VOLATILITY

    supplierId

    Required: Yes
  • SHIPMENT_DELAY_IMPACT

    poId

    Required: Yes

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.

FunctionReturnsBehaviourForm
FORECAST_CASHFLOWrecordDaily cash inflows and outflows, the running balance, a 30 day net cash projection with a band, and its discounted valueFORECAST_CASHFLOW("ACCOUNT_ID", "ROLLING_90_DAYS")
FORECAST_PROFIT_GUARDrecordMonthly revenue, profit, asset value and enterprise value projections over the next months for one scenarioFORECAST_PROFIT_GUARD(12, "BASE")
PLANNING_PROJECT_PRO_FORMArecordThe net present value, internal rate of return, payback and a decision for a project, with its monthly cash flowsPLANNING_PROJECT_PRO_FORMA("ROLLING_90_DAYS", "New warehouse", 10000000, 1200000, 500000, 36, 2500)
FREIGHT_VOLATILITYrecordThe latest shipment's freight cost per unit against the 6 month average for a supplier, and which way it is movingFREIGHT_VOLATILITY("SUPPLIER_ID")
SHIPMENT_DELAY_IMPACTrecordDelay days, units affected and value at risk for each shipment on a purchase orderSHIPMENT_DELAY_IMPACT("PO_ID")

FORECAST_CASHFLOW answers

FieldTypeMeaning
dateslist of textThe days in the range
inflows, outflowslist of whole numbersCash in and cash out for each day
cogs, fees, opexlist of whole numbersThe cost of goods, gateway fees and prepaid expense charge that make up the outflows
netlist of whole numbersInflows less outflows for each day
netFitted, ewmaNetlist of whole numbersThe trend line and a smoothed series of net cash
cumulativelist of whole numbersThe running total of net cash
forecastDateslist of text+1d to +30d
forecastNet, upperBand, lowerBandlist of whole numbersProjected net cash and its 95 per cent band
netCashSlope, inflowSlope, outflowSlopetextDaily change in net cash, inflows and outflows
npvProjected30dtextThe projection discounted at 10 per cent a year
cumulativeInterest, cumulativePrincipaltextThe interest and principal of repaying the latest day's outflow over 12 months at 10 per cent
currencytextThe account currency

FORECAST_PROFIT_GUARD answers

FieldTypeMeaning
oktrue or falseFalse when there is too little history
tenantId, scenario, monthsAheadtext and whole numbersYour account, the scenario in upper case and the months projected
waccpercent numberThe account's cost of capital
historyMonthswhole numberMonths of history used, from the last 12
revenueSlope, revenueInterceptmoneyThe monthly revenue trend
projectionslist of recordsperiodAhead, 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

FieldTypeMeaning
projectNametextThe name you gave
npvmoneyNet present value of the project
irrdecimalThe internal rate of return as a yearly fraction
paybackMonthswhole numberThe month in which the running total turns positive
roic, waccdecimalThe return on invested capital and the cost of capital
nopat, economicProfitmoneyProfit after tax and the profit above the cost of capital
decisiontextAPPROVE when NPV is positive and ROIC beats the cost of capital, REVIEW when only NPV is positive, otherwise REJECT
investment, horizonMonths, taxRateas givenThe inputs, echoed back
monthlyCashflowslist of recordsmonth, revenue, cost, netCashFlow and cumulative

FREIGHT_VOLATILITY answers

FieldTypeMeaning
supplierIdtextThe supplier
currentFreightPerUnitnumberFreight cost per unit on the most recent shipment, in the stored whole number form
avgFreightPerUnitnumberThe average over the last 6 months of shipments
variancepercent numberHow far the latest shipment is from the average, as a percentage (the engine writes it as varianceBps and returns it as a percent)
trendtextup, down or stable. A move of more than 1 per cent either way counts as up or down

SHIPMENT_DELAY_IMPACT answers

FieldTypeMeaning
poIdtextThe purchase order
shipmentCount, delayedCountwhole numbersShipments on the order and how many are late
totalDelayDaysnumberDays late summed over the late shipments
shipmentslist of recordsshipmentId, 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
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
  • FORECAST_CASHFLOW needs 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_CASHFLOW is fixed.
  • PLANNING_PROJECT_PRO_FORMA accepts whole numbers only: a decimal such as 12.50 is cut to 12. Write amounts in the smallest unit of the currency.
  • PLANNING_PROJECT_PRO_FORMA accepts 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 lowerBand for the cash you can count on, not forecastNet.
  • Run FORECAST_PROFIT_GUARD with BASE and WORST together and compare the two.
  • Call SHIPMENT_DELAY_IMPACT for each open purchase order to see which delays tie up the most value.