Creating and Editing Columns

Getting Started

Global Functions

Lucanet Lume

Data Hub

Administration

Extended Planning & Analysis (xP&A)

Consolidation & Financial Planning

ESG Reporting

Disclosure Management

The Lucanet Disclosure Management User Interface

All Steps at a Glance

Basic Configuration

Dashboard

Configuring a Document

Cockpit

Creating and Editing Chapters

Creating and Editing Periods

Importing Data

Working with MS Office Files

MS Office Variants

Ribbons for Disclosure Management

Creating and Editing Excel Files

Mode of Excel Files

Configuring an Excel File

Names and Name Extensions in MS Excel

Creating and Editing Columns

Inserting Report Values in MS Excel

Defining Outareas

Programming References

Using Layouts in MS Excel

Inserting Tables From ESG Reports

Creating and Editing Word Files

Versioning and Recovery of MS Office Files

Creating and Inserting Document Variables

Creating and Editing Validations

Using the Data View

Using the XBRL Tagger

Creating Result Document

Rolling Forward or Copying a Document

Editing Documents in the XBRL Tagger

XBRL Tagger

Lease Accounting

Banking & Cash Management

Data Collection

Tax Compliance & Reporting

Glossary

An Excel file for Disclosure Management must always contain the following three column types to ensure that values are inserted in the Excel file or existing values are updated:

Name columns

Value columns

Programming columns

A footnote column can also be defined. Values written in this column are then added to the Word documents in the Outareas of Excel files.

This section explains what the individual column types mean and how to create them. You will also learn what entries can be used for the programming column.

The following table shows the various column types and their meaning:

Name columns contain the row labels, i.e. the names of the individual report values, such as the names of items and accounts.

A name column has the following Properties:

The name of a name column begins with

name_

The name of a name column is given a

sequential number

The name of a name column can optionally be supplemented with a language abbreviation (e.g., name_1_en).

Notation for a Name Column

In general, name columns follow the notation below:

name _sequential number[_language]

Example: name_1, name_1_en

(obligatory)

Value columns contain the report values of the current document. In columns that are defined as value columns, values are added and updated after each data import and whenever the Excel file is updated.

A value column has the following properties:

The name of a value column begins with

value_

The name of a value column is given a

sequential number

and a

PeriodID

The name of a value column can optionally be supplemented with a language abbreviation (e.g., name_1_en).

Notation for a Value Column

In general, value columns follow the notation below:

value_ sequential number[_suffix]

Example: value_1_PAJ06, value_1_PAJ06_de

(obligatory)

The programming column is responsible for programming the contents of the value_ column. It contains information such as item numbers or account numbers from the source system and other details for calculating values in value columns.

A programming column has the following properties:

The name of a programming column begins with

prog_

The name of a programming column is given a

sequential number

and a

PeriodID

Notation of a programming column

In general, programming columns follow the notation below:

prog_ sequential number_Suffix

Example: prog_2_PAJ

Additional information on the entries permitted in programming columns can be found in Entries Permitted in the Programming Column.

(optional)

The footnote column can be used to add footnotes to values in the Outareas of Excel files in the Word documents. Footnotes defined for a value in Excel are also displayed in the data view and can be edited there.

A footnote column has the following properties:

The name of a footnote column begins with

footnote_

The name of a footnote column is assigned a

sequential number

and a

period ID

The name of a footnote column can optionally be supplemented with a language abbreviation (e.g., name_1_en).

Notation for a footnote column

In general, footnote columns follow the notation below:

footnote_ sequential number_period ID

Example: footnote_1_AJ, footnote_1_AJ_en

stands for the value column to which the

footnote column is to apply.

The

value column 1 (value_1)

for a specific period must be assigned

footnote column 1

for the same period

(footnote_1)

The

value column 2

for the same period

(value_2)

is then assigned the

footnote column 2 (footnote_2)

etc.

The desired footnote number is then written in the footnote column behind the value configured before it in the Excel file.

The footnote number and the text for the footnote can then be written manually in the Word file under the relevant table.

To delete a footnote, the number can be simply deleted in the cell.

In Office for the Web and Office Native, you create columns using the Mark Area task pane. You create name columns on the Name column tab and value and programming columns on the Period columns tab.

To create a name column:

In MS Excel, select the column that contains the row labels.

In the Excel ribbon, click Mark Area and open the Name column tab in the task pane.

Click + Add name column.

The New Name Column dialog is displayed. Enter a name and click Save.

When adding a name column, the 'New Name Column' dialog is displayed. — 'New Name Column' dialog

To insert programming and value columns, use the Period columns tab.

In MS Excel, select the desired column.

In the Excel ribbon, click Mark Area and open the Period columns tab in the task pane.

Click + Add period column.

The context menu of the + Add period column button provides menu items for the periods that have been created in the Manage Periods workspace. For each period, a value column (value_) and a programming column (prog_) are available.

The context menu of the '+ Add period column' button shows a value and a programming column for each period. — '+ Add period column' context menu with entries for each period

From the menu, select the desired programming or value column.

You can create all columns using the Excel ribbon and the buttons shown in the following figure:

Displays part of the Excel ribbon. The buttons for inserting columns are highlighted in a red border. — Buttons in the Excel ribbon for inserting columns

To create columns:

To create a name column:

Select the desired column in MS Excel.

Click Name in the Excel ribbon.

Click the desired entry in the menu displayed:

The menu of the 'Name' button is displayed in the Excel ribbon. — 'Name' button with menu on the Excel ribbon

To create a value column in an Excel file:

Select the desired column in MS Excel.

Click Value in the Excel ribbon. In the Excel ribbon, menu items are available for the periods that have been created in the Manage Periods workspace.

Click the desired entry in the menu displayed:

'Value' button in the Excel ribbon — Value' button with menu on the Excel ribbon

To display a column name instead of the period ID in the data view, a column name can be additionally assigned to each configured value column in Excel.

To do so, click the cell that contains the column name and do one of the following:

Choose

Disclosure Management | Insert column name x

from the context menu of the cell.

Click

Insert

on the Excel ribbon and choose the menu item

Insert column name

To create a programming column:

Select the desired column in MS Excel.

Click Prog in the Excel ribbon. In the menu, menu entries are available for all the Periods that have been created.

Choose the desired entry:

'Prog' button in the Excel ribbon — 'Prog' button on the Excel ribbon

To create a footnote column:

Select the desired column in MS Excel.

Click Other on the Excel ribbon. In the menu, menu entries are available for all the Periods that have been created.

Choose the desired entry:

Alternatively, the footnote column can also be created as follows:

Using the context menu of a column in Excel

In the detail view of a value in the

data view

The indexes (= sequential number) of value and programming columns are interdependent. The prog_1 column contains the instructions for filling the value_1 column (e.g. by specifying the corresponding item numbers); prog_2 contains the instructions for value_2, and so on.

For that reason, there must always be the same amount of programming columns as there are value columns.

The following table lists the entries permitted in the programming column. Other entries are not permitted in the programming columns because every entry could be interpreted as a "fill instruction" for the value column(s) and could therefore overwrite contents.

Entry	Description
#s[name] or sequential number	Selection as an Excel sum formula (only addition, subtraction, multiplication, and division as well as the Excel sum formula =SUM()), e.g. #s1, #s2, #s[EBIT].
#sf[name] or sequential number	Excel sum formula with a freely definable number of decimal places. Note: Sums that have been programmed using #sf are not rounded to the number of decimal places that were specified when checking in the Excel file. Instead, exactly the number of decimal places calculated as the result is applied.
#m[name] or sequential number	Selection as a manual report value, e.g. #m1, #m2, #m[numberemployees].
#mf[name] or sequential number	Manual report value with a freely definable number of decimal places. Note: Numbers that have been programmed using #mf are not rounded to the number of decimal places that were specified when checking in the Excel file. Instead, exactly the number of decimal places that the user has entered is applied.
#mt[name] or sequential number	Manual text value. Note: Contents that have been programmed using #mt are saved as text and are not available for further calculation.
#l and sequential number	Selection as a blank row, e.g. #l1, #l2. Disclosure Management does not expect a value here and leaves the row blank in the rounding view. This is mainly used before headings in tables (e.g. assets).
#r[sequential number]	Reference to another report data record
#rf[sequential number]	Reference with a freely definable number of decimal places. Note: Numbers that have been programmed using #rf are not rounded to the number of decimal places that were specified when checking in the Excel file. Instead, exactly the number of decimal places that the reference source contains is applied.
30768000	Value of account number 30768000.
-30768000	Inversion of the sign for the value of account number 30768000. Note: Depending on the formatting of the cell, MS Excel may not accept a leading minus sign. You can solve this by starting with an apostrophe, e.g. '-30768000.
30768000;30769000	Sum of the values from accounts 30768000 and 30769000
; (semicolon)	Separates item/account numbers
30768000;-30769000	30768000 minus 30769000
30111000#9999#CC-EV, 53290000#SEK1	Subitems/transaction types/exchange rates, etc. are entered using # after item numbers.

Subsequent changes to designations in #m or #s programming can lead to problems with validations and references. Validations and references refer to the unique #m or #s programming. For example, if the sequential index referred to by a reference is changed from #m6 to #m7, the value of the cell in which the #m6 programming is stored will remain assigned to the reference.

An example for programming the prog_ column is shown in the following figure:

Displays a P&L table containing accounts and values. — Example of a P&L table

The following list contains explanations of the entries in the example table:

The entry 30100000 in row 5 of the prog_ column means that the cell [the report value] of the value_ columns in row 5 is filled with the value recorded at account/item number 30100000 in the source system.

The minus sign in front of the item number inverts the sign in the table view.

In the example, the entry -30100000 denotes the sum of revenue recorded as -59,882 units [here € million] in the source system and presented as €59,882 million in cell G5 for the current year.

The entry #s12 in cell K12 indicates that the corresponding report values of the value_ columns [so cells G12 and I12] are calculated using a sum formula.

The formula to be calculated – in this case =SUM(G7:G11) and =SUM(I7:I11) – is entered in cells G12 and I12 of the value_ columns.

The #s programming is the only possible entry in the prog_ column that requires an additional entry (input in the value cell) for calculation in the value_ column.

The entry -31010000;-31020000 in cell K13 instructs Disclosure Management to invert the sign of the values of accounts 31010000 and 31020000 to a minus sign in the corresponding cells of the value_ columns ( G13 and I13), to add them together (separation of item numbers using ;), and to then present the sum as a rounded figure in accordance with standard commercial practice if necessary. In this way, as many item numbers as required can be added together or subtracted from each other to then present the resulting amount in the value_ column.

The account numbers to be added together are always separated by a semicolon.

If only a certain transaction type or the value of a subaccount is to be presented instead of the total amount of an account, this is done by specifying the desired limitation. The limitation [e.g. transaction type 300] is entered behind the item number using the # symbol as a separator, e.g. 30200000#300].

The transaction type must have been imported with the corresponding designation and stored in the Disclosure Management database.

The entry #m25 in cell K25 defines the entries in cells G25 and I25 as numbers to be entered manually by the user. This means that these report values will stay at 0 until a user defines and saves the value for the report values in the Rounding View or in Excel.

The rows of the Excel table will only be displayed in the rounding form if a programming instruction has been entered in their prog_ column. If the rounding form is to include a heading or blank row in which no report value is generated or manually entered, this can be done using the entry #l. In this case too, the instructions must be given a sequential number like in the #s and #m programming instructions.

In the example presented, a special calculation operation is performed in rows 31 and 32, which is not to be recorded and managed by Disclosure Management. For that reason, the entries #l31 and #l32 are used in the programming column for these two rows. All entries in the corresponding rows of the value_ columns of the Excel table will not be displayed or evaluated in the Rounding View (e.g. not included in sums).

Sums programmed using #s cannot be read in via #l programming. In this case, Disclosure Management generates an error message because the values cannot be calculated as a result of the blank row.