top of page

Know your facts Part 3: Power BI, Financial Statements & Kimball

Updated: Apr 18, 2022

Statement of Cash flows

With the Statement of Comprehensive Income (SOCI) and Statement of Financial Position (SOFP) there are no options; it is a requirement under IFRS and US Gaap to produce the accounts in a certain way. However, with the Statement of Cash Flows (SOCF), it is permissible under both accounting standards to use either the:

  • Direct Method

  • Indirect Method

Both methods are explored below along with key considerations:


Direct Method

The direct method of cash flow is achieved by adding together all the cash inflows and outflows of the organisation. For instance, there could be cash paid by a client, cash paid to employees and cash used to pay off long-term debts. The SCOF direct method sums all these movements together to arrive at a cash balance for a period.

Given the additive nature, it is advisable to use a TRANSACTIONAL fact table; like the SOCI, the SOCF can add the elements (i.e., Ending Cash Balance) from one month to the next and get the cash flow across the 2 periods. This is because the ending position of cash flow for one period is the beginning for the next. It is also possible to use the SNAPSHOT or ACCUMULATING SNAPSHOT approach, but as mentioned in the SOFP article, the DAX is more complicated. Moreover, you need a separate record recording the point in time for the opening and closing balance each month meaning that you could create potential integrity issues where the opening for one period doesn't match the closing for the previous.


Indirect Method

Of all the statements, the SOCF from an indirect perspective is the most challenging. The approach requires calculating cash flows for:

  1. Operating activities

  2. Financing activities

  3. Investing activities

Some of these cash flows come from the SOCI and others from the SOFP. The SOCFs, therefore, contains all the complexities of the approaches discussed in these articles. Being dependent on these two financial statements also means you cannot start to produce the SOCF until the SOCI and SOFP are produced. It is also advisable to check the outcome of the SOCFs using the SOFP cash movement position; the change from one period to the next in cash terms on the SOFP should match the cash flow total from the SOCFs. To highlight this, let's look at a SOCF example:

SOCF inputs from the SOCI

For "Cash Flow from Operating Activities" the first 3 lines are

  1. Profit Before Interest and Tax

  2. Amortisation

  3. Depreciation

All 3 of these lines come from out SOCI and thus our TRANSACTIONAL fact table

SOCF inputs from the SOFP

For "Cash Flow from Operating Activities" the following 3 lines are:

  1. Change in Inventory

  2. Change in Account Receivables

  3. Change in Current Liabilities

All 3 of these lines come from our SOFP and thus either the SNAPSHOT or ACCUMULATING SNAPSHOT fact table. The calculation requires taking the Opening and Closing Balances for each month from the SOFP in order to calculate the change required for the cash flow.

SOCF inputs from the SOCI and SOFP simultaneously

"Cash Flow from Investing Activities" requires data from both the SOCI and SOFP position. For instance, in calculating the "Property" it is not possible to simple calculate the change in these balances from the SOFP. In doing so, the "Depreciation" will be double counted as it is already a feature of the "Operating Activities". Therefore, the "Change in Property" needs to be calculated based on the change in SOFP then further adjusted using the SOCI.



During this series, I've tried to outline why a strong understanding of both Kimball and Accounting principles is required to achieve success when creating reports pertaining to the financial statements. During the next article, now that we've understood the fact tables rules as outlined by Kimball, we'll start exploring some of the problems that we encounter as we look to create more flexible reporting solutions.

NEXT: Mixed Grain Fact Tables

402 views0 comments


bottom of page