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

    revenue

    Required: Yes
  • cogs

    Required: Yes
  • CONTRIBUTION_MARGIN

    revenue

    Required: Yes
  • variable_costs

    Required: Yes
  • BREAK_EVEN_QTY

    fixed_costs

    Required: Yes
  • price_per_unit

    Required: Yes
  • variable_cost_per_unit

    Required: Yes
  • OPERATING_LEVERAGE

    contribution_margin

    Required: Yes
  • ebit

    Required: Yes
  • EBITDA

    revenue

    Required: Yes
  • operating_expenses

    Required: Yes
  • EBITDA_MARGIN

    ebitda

    Required: Yes
  • revenue

    Required: Yes
  • NET_PROFIT_MARGIN

    net_income

    Required: Yes
  • revenue

    Required: Yes

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.

FunctionReturnsBehaviourForm
GROSS_MARGINdecimalThe share of sales left after the cost of goods: (revenue less cogs) divided by revenueGROSS_MARGIN(1000000, 640000)
CONTRIBUTION_MARGINdecimalRevenue less variable costs, in moneyCONTRIBUTION_MARGIN(1000, 350)
BREAK_EVEN_QTYdecimalThe units to sell to cover the fixed costsBREAK_EVEN_QTY(24000, 50, 30)
OPERATING_LEVERAGEdecimalContribution margin divided by operating profit: how many percent profit moves for each percent sales moveOPERATING_LEVERAGE(650, 200)
EBITDAdecimalRevenue less operating expensesEBITDA(1000000, 820000)
EBITDA_MARGINdecimalEBITDA divided by revenueEBITDA_MARGIN(180000, 1000000)
NET_PROFIT_MARGINdecimalNet income divided by revenueNET_PROFIT_MARGIN(70000, 1000000)
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
  • GROSS_MARGIN needs revenue that is not 0. EBITDA_MARGIN and NET_PROFIT_MARGIN need revenue above 0. OPERATING_LEVERAGE needs operating profit that is not 0.
  • EBITDA is 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 in IF and used in ADD, SUBTRACT, MULTIPLY and DIVIDE.
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.