Cash and inventory cycle functions

See how fast stock turns into cash and how long the cash lasts.

Syntax

Write the function's name and its figures in major units. Days and months come back as plain numbers.

INVENTORY_TURNOVER(cogs, average_inventory)
DAYS_INVENTORY_OUTSTANDING(inventory_turnover)
DAYS_SALES_OUTSTANDING(accounts_receivable, revenue, days_in_period)
CASH_CONVERSION_CYCLE(dio, dso, dpo)
FREE_CASH_FLOW(operating_cash_flow, capital_expenditure)
CASH_BURN_RATE(net_cash_outflow, months)
RUNWAY_MONTHS(cash_balance, monthly_burn)
GMROI(gross_margin, average_inventory)

Parameters and arguments

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

  • INVENTORY_TURNOVER

    cogs

    Required: Yes
  • average_inventory

    Required: Yes
  • DAYS_INVENTORY_OUTSTANDING

    inventory_turnover

    Required: Yes
  • DAYS_SALES_OUTSTANDING

    accounts_receivable

    Required: Yes
  • revenue

    Required: Yes
  • days_in_period

    Required: Yes
  • CASH_CONVERSION_CYCLE

    dio

    Required: Yes
  • dso

    Required: Yes
  • dpo

    Required: Yes
  • FREE_CASH_FLOW

    operating_cash_flow

    Required: Yes
  • capital_expenditure

    Required: Yes
  • CASH_BURN_RATE

    net_cash_outflow

    Required: Yes
  • months

    Required: Yes
  • RUNWAY_MONTHS

    cash_balance

    Required: Yes
  • monthly_burn

    Required: Yes
  • GMROI

    gross_margin

    Required: Yes
  • average_inventory

    Required: Yes

Examples

A year with cost of goods sold of 720,000 on average stock of 120,000, customers owing 90,000 on sales of 1,200,000, and suppliers paid after 35 days.

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
INVENTORY_TURNOVERdecimalHow many times stock was sold and replacedINVENTORY_TURNOVER(720000, 120000)
DAYS_INVENTORY_OUTSTANDINGdecimal365 divided by the turnover: the days stock sits before it sellsDAYS_INVENTORY_OUTSTANDING(6)
DAYS_SALES_OUTSTANDINGdecimalThe days customers take to payDAYS_SALES_OUTSTANDING(90000, 1200000, 365)
CASH_CONVERSION_CYCLEdecimalDays from paying for stock to being paid: dio plus dso less dpoCASH_CONVERSION_CYCLE(61, 27, 35)
FREE_CASH_FLOWdecimalOperating cash flow less capital spendingFREE_CASH_FLOW(130000, 45000)
CASH_BURN_RATEdecimalThe net cash outflow each monthCASH_BURN_RATE(270000, 6)
RUNWAY_MONTHSdecimalThe months the cash lasts at that burnRUNWAY_MONTHS(480000, 45000)
GMROIdecimalGross margin earned for each 1 held in stockGMROI(280000, 120000)
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
HTQL-5005A figure is out of range, such as a zero or negative denominator. The message names the function and the rulePass values inside the range listed under Parameters and Limitations
HTQL-5004 Not enough paramsAn argument is missing. The message lists the names the function expectsGive every argument it lists
Limitations
  • DAYS_INVENTORY_OUTSTANDING always uses a 365 day year. Turnover, average stock, revenue and monthly burn must be above 0 or the call is refused. CASH_BURN_RATE needs months above 0.
  • A finance function's result cannot be an argument of another finance function, so type the figure in or name it with LET. The result can be compared in IF and used in ADD, SUBTRACT, MULTIPLY and DIVIDE.
  • The functions take figures you supply. They do not read your records.
Permissions and security

These functions read nothing from your tables and write nothing.

When a function is applied to a column or a variable, the statement that reads the data needs read access, as its own entry says.

Best practices
  • Use average stock over the period, not the closing balance.
  • Pass the same period to turnover and to the days that follow from it.