Comparison Report With Results-based Presentation The Change

1 Introduction / Disambiguation

Balance sheet Under Comparison report, an evaluation and profit and loss report (Report type: E) which has entered either a comparison period or a comparison data type (or both) in the report header.

Insightsoftware supplies column options as the default, which provide two value columns and one change column. These column options always start with a # and end in D, for example, #ALTD, #BUCD, #NEW , #SUMD, #KOND, #KTKD.

However, the sign of the change column is not result-related. The change is only shown as the difference from the current period minus the previous period. Thus, an amount that increased in the current period compared with the comparative period is always reported positive, while a decreased amount is always reported negative, regardless of whether it is an expense or income (in the case of profit and loss representations).

This mathematically correct, but economically unreasonable representation appears even more visually with the column options, which additionally represent the change as a relative change (in %) and with bar graph. These column options also start with # and end with DG, e.g. #ALTDG, #BUCDG, #NEUDG, #SUMDG, #KONDG, #KTKDG.

Here, the bar changes to the right in green color, regardless of whether sales revenue or material expenditure has increased.

2 Formulation of the requirement

The recipient of the report should be able to identify the most important information correctly at a glance, without having to first consider or think about "how it is meant". Positive changes should be reported with a positive sign, negative changes with a negative sign. The premise is always: "From the point of view of the result".

Balance sheet Consequently, the profit and loss indicator of the respective reporting position plays a role, because an increase in income has a positive effect, while an increase in expenses has a negative effect on earnings.

The presentation of percentage data becomes problematic when the sign rotates in the two comparison columns. The following example illustrates this in a very illustrative way:

The simultaneous presentation of an absolute change with a positive sign and a percentage change with a negative sign is mathematically, but not economically explainable. If, in the first example, the sign of the percent indication was simply turned so that both variation variables would again be indicated with a uniform sign, then the meaning would also be falsified, because the positive 200% variation is, of course, correctly indicated in the second example, but with different basic amounts.

Therefore, where the sign of the two columns differs, the presentation of a percentage change should be omitted altogether, as its significance is limited at best.

3 Implementation in IDL Konsis

3.1 Requirements To The report Row Definition

To distinguish between an expense line and an income line (or expense or income account) in the respective report line, we use the column condition from the report line definition to help. In the example, this is empty (= 0) for income lines, but 2 for expenditure lines. If (business) expenditure items are (technically) defined as income items, the column options discussed here work in the same way, it is only unnecessary to mark the (technical) expenditure items with column condition 2, since no (technical) expenditure items exist.

Likewise, it is not a problem if income accounts are assigned to expense lines (and vice versa), because the column condition is correctly evaluated not only on the respective report line, but also on the assigned accounts.

3.2 Transposition In The report Column Definitions

See also the Excel file with the report column definitions and Xlslink formulas for the following information to upload the definitions to a Konsis database.

The current report column is always populated with columns 01 and 02 from the report results table, the comparison column with columns 05 and 06.

The absolute deviation shall be reported in the third reporting column. For this purpose, the column condition 0, i.e. yield positions, is first queried in the rows 1 to 4, and the associated calculation rule is shown in the rows 5 to 9. In rows 10 to 14, the expenditure line check is followed by the column condition 2 request. In lines 15 to 19 we see the calculation rule for the deviation for expenditure items.

Lines 5 to 9 and 15 to 19 are identical except for the signs used.

The percentage changes are shown in column 04 of the report. First, a request is made as to whether the sign of the two report columns is identical. This is implemented by checking the quotient of the two columns to be greater than or equal to zero (row 10). The special case where the quotient is zero can occur if the current reporting column is 0, the previous year's column is not 0. Then you get to 100% (or - 100%) change.

If the quotient is not greater than 0, that is, if it is less than 0, the two report columns show different signs and no next computation is performed in this column.

The column condition is then used to determine whether the line is an income line. This is followed by an examination as to whether the sign in the prior year column is positive (row 22). If this is the case, the calculation of the percentage value is carried out by the calculation rule of lines 24 to 33. This is followed by the check for a negative sign in the previous year's column (row 38) and the corresponding calculation rule for the percentage value in rows 40 to 49.

From row 53 onwards, the corresponding calculation is carried out if the position is effort. This is followed by an examination as to whether the sign in the prior year column is positive (row 62). If this is the case, the calculation of the percentage value is carried out by the calculation rule of lines 64 to 73. This is followed by a check for a negative sign in the previous year's column (row 78) and the corresponding calculation rule for the percentage value in rows 80 to 89.

Picture 1: Detail from the report result display. The absolute sign and the percentage change always show the same sign. Negative values are also displayed correctly.

Picture 2: Detail from the report result display. The calculation on expenditure items also works correctly. We see that no calculation takes place if the prior year column is 0 (division by 0!), or if the sign in the two reporting columns differs.

4 extradition

Release 23.2 provides new report column options for the results-based presentation of the change. These start with # and end on DB, e.g. #ALTDB, #BUCDB, #NEUDB, #SUMDB, #KONDB, #KTKDB.