Creating and Editing Columns

Last updated on 2024-11-04

Overview

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.

Definitions

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

Column

Description

Name column (obligatory)

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.

Notation for a Name Column

Generally speaking, name columns follow the notation below:

name_sequential number­

 

Example: name_1

Value column

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

Notation for a Value Column

Generally speaking, value columns follow the notation below:

value_sequential number[_suffix]

 

Example: value_1_PAJ06

Program­ming column

(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

Generally speaking, 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.

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

Notation for a footnote column

In general, footnote columns follow the notation below:

footnote_sequential number_period ID

 

Example: footnote_1_AJ

  • 1 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.
Creating Columns

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:

  1. Select the desired column in MS Excel.
  2. Click Name in the Excel ribbon.
  3. 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:

  1. Select the desired column in MS Excel.
  2. 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.
  3. 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 x.
The "Insert column name" command is displayed, which appears if you click 'Insert' on the Excel ribbon Inserting a column name by means of the ribbon

To create a programming column:

  1. Select the desired column in MS Excel.
  2. Click Prog in the Excel ribbon. In the menu, menu entries are available for all the Periods that have been created. 
  3. Choose the desired entry:
    'Prog' button in the Excel ribbon
    'Prog' button on the Excel ribbon

To create a footnote column:

  1. Select the desired column in MS Excel.
  2. Click Other on the Excel ribbon. In the menu, menu entries are available for all the Periods that have been created. 
  3. Choose the desired entry:
    'Other' button on the Excel ribbon

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.
Index of Value and Programming Columns

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.

Entries Permitted in the Programming Column

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.

Programming

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.

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. 

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

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

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.

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.

Example of a Programming Column

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
Explanations for the Example

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

Row

Explanation

5

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.

12

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.

13

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.

25

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.

31, 32

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.