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
Required: Yescogs
Required: Yesaverage_inventoryDAYS_INVENTORY_OUTSTANDING
Required: Yesinventory_turnoverDAYS_SALES_OUTSTANDING
Required: Yesaccounts_receivable
Required: Yesrevenue
Required: Yesdays_in_periodCASH_CONVERSION_CYCLE
Required: Yesdio
Required: Yesdso
Required: YesdpoFREE_CASH_FLOW
Required: Yesoperating_cash_flow
Required: Yescapital_expenditureCASH_BURN_RATE
Required: Yesnet_cash_outflow
Required: YesmonthsRUNWAY_MONTHS
Required: Yescash_balance
Required: Yesmonthly_burnGMROI
Required: Yesgross_margin
Required: Yesaverage_inventory
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.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
INVENTORY_TURNOVER | decimal | How many times stock was sold and replaced | INVENTORY_TURNOVER(720000, 120000) |
DAYS_INVENTORY_OUTSTANDING | decimal | 365 divided by the turnover: the days stock sits before it sells | DAYS_INVENTORY_OUTSTANDING(6) |
DAYS_SALES_OUTSTANDING | decimal | The days customers take to pay | DAYS_SALES_OUTSTANDING(90000, 1200000, 365) |
CASH_CONVERSION_CYCLE | decimal | Days from paying for stock to being paid: dio plus dso less dpo | CASH_CONVERSION_CYCLE(61, 27, 35) |
FREE_CASH_FLOW | decimal | Operating cash flow less capital spending | FREE_CASH_FLOW(130000, 45000) |
CASH_BURN_RATE | decimal | The net cash outflow each month | CASH_BURN_RATE(270000, 6) |
RUNWAY_MONTHS | decimal | The months the cash lasts at that burn | RUNWAY_MONTHS(480000, 45000) |
GMROI | decimal | Gross margin earned for each 1 held in stock | GMROI(280000, 120000) |
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 |
HTQL-5005 | A figure is out of range, such as a zero or negative denominator. The message names the function and the rule | Pass values inside the range listed under Parameters and Limitations |
HTQL-5004 Not enough params | An argument is missing. The message lists the names the function expects | Give every argument it lists |
Limitations
DAYS_INVENTORY_OUTSTANDINGalways uses a 365 day year. Turnover, average stock, revenue and monthly burn must be above 0 or the call is refused.CASH_BURN_RATEneeds 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 inIFand used inADD,SUBTRACT,MULTIPLYandDIVIDE. - 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.