Cash Flow Mapping Report

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

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;
Edit the cash flow mapping records stored in the Cash Flow Mapping cube;
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)	Selects 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)	Displays 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 Calculation` cube.
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 depend on selection made in the Combobox 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.	Shows 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 it are shown. Otherwise, all descendants of the selected element are shown. The resulting elements are filtered through a regular expression. The elements named above can 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 accounts. 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 if the `Mapping Type` measure is set to `Auto`.
Row number	Displays row number 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 Type`, `Transaction Type`, `CF 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 to be handled: `1`: Value is mapped to cash flow account with original sign. `-1`: Value is mapped to cash flow account with reverted sign. `Auto`: Automatic handling of the sign reversal; 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 the `Cash Flow Mapping` cube on the `Type` measure.
Transaction Type	Only valid for mappings from balance sheet accounts.	Changed value is stored in the `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 the `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 the `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 of 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.

Updated November 4, 2024