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:

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.

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:

You have to enable marketing cookies to play this Vimeo 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 of the 'Create formula' dialog is displayed. — Dialog 'Create formula' - 'General' tab

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:

All variables entered in the Formula field are automatically transferred to this column.

Elements that represent the variables. Possible elements are items, accounts, time series, total lines, and formulas.

Transaction type to be used for the calculation

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 value from that the calculation is to be made up to the current period. Otherwise the value will be calculated for the current period.

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

. 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.

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

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

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

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

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.

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: