Advanced formula input is used when you want to use more complicated formula calculations, for example, when calculating margins.
Please note that raw data is displayed, and this can sometimes produce unexpected results. Also, do not use the plus sign + to sum accounts, but instead follow the examples below for summing accounts or ranges for the best possible outcome.
Negative and positive values
In the accounting files, values are stored the opposite way from what we usually expect. The value of a revenue account, for example, account 3041, is therefore negative, even though it is a revenue account. Conversely, an expense is stored as a positive value. Therefore, in the income statement, a minus sign is needed to “flip” the value correctly. This is not required for balance sheet accounts.
Below are some examples of how to use profit and loss accounts and balance sheet accounts.
| Profit and Loss Accounts | Balance Sheet Accounts | |
| Principle | Minus and curly brackets -{} | Square brackets [] |
| Individual account | -{3041} | [2081] |
| Account range | -{3000-3999} | [2000-2999] |
| Combination | -{3041,3700-3999} | [2081,2300-2399] |
Arithmetic calculations
You can use the standard mathematical operations (addition, subtraction, multiplication and division) as well as parentheses in formulas. This allows you to calculate margins, key ratios, etc. You can also combine these calculations with KPIs.
Below are some examples of how to use arithmetic calculations. To obtain percentages, you must change either the row settings so that the Unit is %, or change the unit in the column settings.
| What is calculated? | Formula input |
| Gross margin | (-{3000-4999})/(-{3000-3999}) |
| 50% of turnover | -{3000-3999} / 2 |
| Profit margin | (-{3000-8999}/-{3000-3999})Quick ratio[1500-1999]/[2400-2999] |
| Cost per employee | (-{4000-7999}) / employees |
Employees are a KPI that does not belong to the accounts and is imported via Excel.
Functions
A function is a predefined formula that calculates based on the values you want to use, whether for accounting or KPIs.
You can combine functions, e.g., by combining the accumulated and the budget for turnover to get the accumulated budget. If you want to combine several functions, you enter the second function inside the first function’s (Expression)
Below are some examples of functions you can use in data sources or rows.
| Function | Explanation | Formula input / Add data source |
| Accumulated | Gives the period’s value accumulated | Ack(Expression) |
| Budget | Gives the period’s budget | Budget(Expression) |
| Previous period | Gives the value for the previous period | Prev(Expression) |
| Accumulated Budget | Gives the accumulated value so far of this year’s budget | Ack(Budget(Expression)) |
The last example shows how you can combine the accumulated and the budget.
If you want to know which functions are available in the system, you can see the next guide: