Profitability and margin functions
Find how much of each sale is kept, how many units cover the fixed costs and how sharply profit moves with sales.
Syntax
Write the function's name and its figures in major units. Margins come back as full fractions.
GROSS_MARGIN(revenue, cogs)
CONTRIBUTION_MARGIN(revenue, variable_costs)
BREAK_EVEN_QTY(fixed_costs, price_per_unit, variable_cost_per_unit)
OPERATING_LEVERAGE(contribution_margin, ebit)
EBITDA(revenue, operating_expenses)
EBITDA_MARGIN(ebitda, revenue)
NET_PROFIT_MARGIN(net_income, revenue)Parameters and arguments
Arguments are written in brackets, separated by commas. An argument in square brackets is optional.
GROSS_MARGIN
Required: Yesrevenue
Required: YescogsCONTRIBUTION_MARGIN
Required: Yesrevenue
Required: Yesvariable_costsBREAK_EVEN_QTY
Required: Yesfixed_costs
Required: Yesprice_per_unit
Required: Yesvariable_cost_per_unitOPERATING_LEVERAGE
Required: Yescontribution_margin
Required: YesebitEBITDA
Required: Yesrevenue
Required: Yesoperating_expensesEBITDA_MARGIN
Required: Yesebitda
Required: YesrevenueNET_PROFIT_MARGIN
Required: Yesnet_income
Required: Yesrevenue
Examples
A year of sales of 1,000,000 with cost of goods sold of 640,000, operating expenses of 820,000 and net income of 70,000.
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 |
|---|---|---|---|
GROSS_MARGIN | decimal | The share of sales left after the cost of goods: (revenue less cogs) divided by revenue | GROSS_MARGIN(1000000, 640000) |
CONTRIBUTION_MARGIN | decimal | Revenue less variable costs, in money | CONTRIBUTION_MARGIN(1000, 350) |
BREAK_EVEN_QTY | decimal | The units to sell to cover the fixed costs | BREAK_EVEN_QTY(24000, 50, 30) |
OPERATING_LEVERAGE | decimal | Contribution margin divided by operating profit: how many percent profit moves for each percent sales move | OPERATING_LEVERAGE(650, 200) |
EBITDA | decimal | Revenue less operating expenses | EBITDA(1000000, 820000) |
EBITDA_MARGIN | decimal | EBITDA divided by revenue | EBITDA_MARGIN(180000, 1000000) |
NET_PROFIT_MARGIN | decimal | Net income divided by revenue | NET_PROFIT_MARGIN(70000, 1000000) |
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
GROSS_MARGINneeds revenue that is not 0.EBITDA_MARGINandNET_PROFIT_MARGINneed revenue above 0.OPERATING_LEVERAGEneeds operating profit that is not 0.EBITDAis revenue less the operating expenses you pass in, so leave depreciation and amortisation out of them.- 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.
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
- Keep revenue and costs on the same period and currency.
- Pair BREAK_EVEN_QTY with a forecast of volume to see how much room there is above break even.