Creating and Configuring Formulas

Getting Started

Global Functions

Administration

Extended Planning & Analysis (xP&A)

Consolidation & Financial Planning

User Interface of Lucanet Consolidation & Financial Planning

General Functions

Basic Configuration

Dashboards

Reporting & Analyzing

Elements in Ledgers and Schedules

Creating and Configuring Items

Creating and Configuring Accounts

Creating and Configuring Formulas

Creating and Configuring Total Lines

Creating and Configuring References

Show Postings

Functions in Ledgers and Schedules

Display in Ledgers and Schedules

Views in Ledgers and Schedules

Elements in Directory Structures

Creating a Postings Report

Import and Journals

Planning

Consolidation

Dimensions

Excel-Integration

Workspace Configuration

ESG Reporting

Disclosure Management

XBRL Tagger

Lease Accounting

Banking & Cash Management

Data Collection

Tax Compliance & Reporting

Glossary

Controlling indicators such as the absolute liquidity ratio, the equity ratio, and the return on sales can be created in Lucanet using formulas. Formulas can be used in all ledgers and schedules to calculate any values. A formula comprises variables and mathematical operators. Each variable of a formula can apply to an item, an account, a different formula, a time series, or a total line in the Lucanet database.

The following variables and operators can be used in formulas:

Element	Description
Variables	The variables of a formula can be an item, an account, a different formula, a time series, or a total line. Variables can be named arbitrarily.
Operators	The following operators can be used in a formula: = > < <> >= <= ( ) AND OR IF + - / * LM LAST_MONTH

How to use IF, LM and LASTMONTH is described in detail in Special formulas.

How to create and configure a formula is described in the following section. Alternatively, you can find the information in the following video:

To create a formula:

Open the ledger (general ledger, subledger, or statistical ledger) and navigate to the item where you want to create the formula.

Right-click the item and choose Create | Formula from the context menu.

The following options are available in the Create formula dialog:

The General tab is displayed as follows, for example:

The 'General' tab of the 'Create formula' dialog is displayed. — Dialog 'Create formula' - 'General' tab

The following options are available on the General tab:

Option	Description
Type	Element type
Name	Name of the formula
Formula	Variables, operators, and, if necessary, values of the formula. The entered variables are transferred to the following table.
Formatting	Formatting options for display: Font color, background color, icon and if the element is to be formatted bold.

The table contains the configuration of the variables:

Column	Description
Variable	All variables entered in the Formula field are automatically transferred to this column.
Elements	Elements that represent the variables. Possible elements are items, accounts, time series, total lines, and formulas.
Transaction type	Transaction type to be used for the calculation
Period type	The period type of the chosen account or chosen item is preset by default. The following options are available: - Start balance of the period: The balance at the beginning of the period is used. - End balance of the period: The balance at the end of the period is used. - Transaction figure of the period: The transaction figure of the period is used. - Transaction/balance mode set by user — If the button Display transaction figures is activated, the transaction figure of the period is used. If the button Display transaction figures is deactivated, the balance at the end of the period is used.
Year-to-date	Year-to-date value from that the calculation is to be made up to the current period. Otherwise the value will be calculated for the current period.

The Value type tab is displayed as follows, for example:

Value type

Specification of the value type for the output value of the formula. The following options are available:

Number

:The output value is displayed as a number.

Percent values

: The output value is displayed as a percentage value.

Checksum

: The output value is displayed as a checksum.

The checksum in the event of a successful check must be 0. In this case the output value is displayed in green. Otherwise, the value is displayed in red:

Es wird gezeigt, wie die Anzeige von Prüfsumme, wenn die Prüfsumme gleich null ist und wenn Sie von null abweicht. — Display of checksums

Decimal places

Specification of the decimal places for the output value of the formula. The following options are available:

Dependent on variable

: The display of the decimal places is taken over by the variable.

Note: This value is available only for the Number and Checksum value types.

User-defined

: Enter the number of decimal places in the displayed input field.

The Number format tab is displayed as follows, for example:

Dialog 'Create formular' - 'Number format' tab — Dialog 'Create formula' - 'Number format' tab

On the Number format tab, the way numbers are displayed for a formula can be configured. The following options are available:

Option	Description
Apply number format	Applies the number format that is different from the display settings
Number display	Formatting of the displayed values
Decimal places	Number of displayed decimal places

The Number format tab is only displayed for formulas in statistical ledgers.

Configurations on the Number format tab overwrite the number format defined in the display settings.

As soon as a deviating number format has been configured, the icon is displayed in the row of the formula.

The Attributes tab is displayed as follows, for example:

Shows the attribute configuration of a ledger element — Attributes

Attributes are used to define multilingual or customer-specific names for Lucanet elements.

The Attributes tab displays all attributes that have been defined in the Attributes workspace and are enabled for this dimension.

You can edit the value of an attribute by opening the element's edit mode and clicking on the corresponding row in the Value column.

With LM or LAST_MONTH, previous period values can be considered in a cumulative form (e. g. the last six months). The general structure of a formula with LM or LAST_MONTH is as follows:

Example 1

Calculation of the revenue of the last six months, with the variable a standing for the revenue item. In this case, the formula is as follows:

LM(6;a)

An IF formula can be used, for example, to calculate interest if a different interest rate is to be applied to positive than to negative balances on the account. The general structure of an IF formula is as follows: