Cash Flow Mapping Report

image_pdfimage_print

Return to Cash Flow Model Overview

This article explains how to administrate cash flow calculations using the following steps:

  1. Update the PnL and BS Account dimension to the current chart of accounts defined by the PnL Account as well as the BS Account dimensions
  2. Edit the cash flow mapping records stored in the Cash Flow Mapping cube
  3. Update an auxiliary dimension and the rules in the Cash Flow Calculation cube. This cube does not contain any physical values but is entirely populated by rules which actually perform the calculation of the cash flow statement.

Report Heading

The screenshot below points out the heading area of the Cash Flow Mapping report. The report heading updates dynamically as various reporting options are selected. The logo can be changed in Administration; see Configuring Design Elements for more on report design.

The heading area of the report displays the following fields:

Field

Description 

Implementation

PnL and BS Account (combobox)

Select a starting entry element to display accounts on rows

Selected consolidated elements of the PnL and BS Account dimension. By default, these are selected by the regular expression pattern^(PnL.All PnL Accounts|BS.All BS Accounts)$. This can be changed in Administration – Cash Flow – Configuration with the key pnlandbs_account_regex1.

Show Account No (checkbox)

Display the account number along with the account name in the row labels.

Switch between Name and ID_Name attribute of PnL and BS Account dimension.

Execution of Integrator Jobs

The screenshot below points out the job execution area of the Cash Flow Mapping report.

This area displays the following fields:

Job (combobox)

Selects the update job to start with the Update button.

Update Dimension PnL and BS Account: Updates the accounts available as rows on this report for the mapping from the profit and loss and the balance sheet accounts.

Update Cash Flow Calculation: Updates the cash flow calculation from the mapping entered in this report.

Update Dimension PnL and BS Account: Run Integrator job xccf01_50_Update PnL and BS Account: Updates the PnL and BS Account dimension with the content of the PnL Account and the BS Account dimensions.

Update Cash Flow Calculation: xccf03_60_Update Cash Flow Calculation: Updates the CF Account Calculation dimension with the cash flow mapping records stored in the Cash Flow Mapping cube and updates the rules in the Cash Flow Calculationcube

Update

Starts the import procedure selected in the job combobox

Runs the selected Integrator job

Get Job Status

Visible if the job is currently running.

 

Status message and icon

Empty: job has not been started yet

Running: job has been started and did not finish within a short timeout period. Use Get Job Status button to check whether the job has terminated.

Failed: job has failed. Check the execution log in Integrator – Monitor  for detailed information.

Completed with Warnings: job has succeeded, but one or more warnings have been raised during the execution. Check the execution log in Integrator – Monitor  for detailed information.

Completed successfully: job succeeded without any warnings or errors.

 

Report Columns

The columns displayed in the report depends on selection made in the combo box for selecting hierarchy for PnL Account and BS Account. The screenshot below shows a report displaying hierarchy out of dimension BS Account. 

Below are descriptions of the columns in the Cash Flow Mapping.

Field

Description

Implementation

Row label

Account shown as a localized name

Shown elements from the PnL and BS Account dimension depending on the selection of the PnL and BS Account combobox. When PnL.All PnL Accounts has been selected, all descendants of PnL.300000 in  are shown. Otherwise all descendants of the selected element are shown. The resulting elements are filtered through a regular expression

This elements named above be changed in Administration – Cash Flow – Configuration with the keys pnlandbs_account1, pnlandbs_account2 and configuration.pnlandbs_account_regex2 for the text filter.

Type

Displays account type (Valid values are Income, Expenses, Assets, Liability or empty for KPI´s)

Type attribute of the PnL and BS Account dimension

Plus sign

Each row may be unmapped (all mapping fields are left empty), mapped to exactly one cash flow account or to multiple cash flow account. A click on the plus signs adds another mapping row for the PnL and BS Account.

 

Adds another mapping record to the Cash Flow Mapping cube on the next unused Cash Flow Mapping No. The new mapping record will be initialized the the Mapping Type measure set to Auto.

Row number

Displays row number for of cash flow mapping record within the PnL and BS Account.

 

Element of the Cash Flow Mapping No dimension implemented with data filter on the following elements:

  • Element of the DynaRange on the PnL and BS Account dimension
  • Mapping TypeTransaction TypeCF Account elements of the Cash Flow Mapping_measure dimension
  • Value for slice operator <> 0

Mapping Type

Defines how the sign of values from the source cubes Profit and Loss or  Balance Sheet is be handled:

  • 1: Value is mapped to cash flow account with original sign
  • -1: Value is mapped to cash flow account reverted sign
  • Auto: Automatic handling of the sign reversal, see For more details on the sign calculation see the Sign measure in cash-flow-mapping-cube.
  • -Auto: The same as Auto but with reverted sign.

Changed value is stored in cube Cash Flow Mapping cube on the Type measure,

Transaction Type

Only valid for mappings from balance sheet accounts.

Changed value is stored in cube Cash Flow Mapping cube on the Transaction Type measure.

CF Account

Target cash flow account of the mapping

Valid values are descendants of a CF Account element which can be changed in Administration – Cash Flow – Configuration with the key cf_account.

Changed value is stored in cube Cash Flow Mapping cube on the CF Account measure.

Name

Cash flow account shown as a localized name

 

Name attribute of CF Account dimension

Type

Displays type of cash flow account

Type attribute of CF Account dimension

Sign

Effective sign of the mapping, depending on the mapping type.

This value is computed by the Update Cash Flow Calculation procedure and stored in Cash Flow Mapping cube on the Sign measure. For more details on the sign calculation see the Sign measure in cash-flow-mapping-cube.

Useful tips for the mapping

To set up a cash flow statement using the indirect method make sure, the changes to every balance sheet account with the exception of the cash accounts (in current assets) and the retained earnings (in equity) are mapped exactly once. The retained earnings account will be substituted with the profit and loss statement. You want to make sure every profit and loss account affecting cash is mapped exactly once. (Depreciation is an example of a profit and loss account not affecting cash)

Try to set up the mapping with as few mapping records as possible – this will ease the future maintenance and keep the mapping robust against some simple changes in the chart of accounts.

When a consolidated profit and loss or balance sheet account with all descendants runs into the same cash flow account you can simply map the consolidated account instead of all individual accounts on the base level. When almost all descendants with only a few exceptions run into the same cash flow account you can simply map the consolidated account. You have to deduct the exception accounts with the reversed Mapping Type from the cash flow account and add another mapping for these onto another cash flow account, where the values of the exception accounts should be shown.

When all transaction types of a balance sheet account map to the same cash flow account you can simply map the Delta transaction type instead if all individual transaction types on the base level. To split increases and decreases you can simply map the Decrease and Delta w/o Decrease transaction types.

Take advantage of the Auto mapping type where possible. Make sure to setup the Type attributes in the PnL Account, BS Account and CF Account dimensions and the sign standard setting correct.

image_pdfimage_print