skip to Main Content


Cash-Flow Conundrum Banner

 

Many accountants consider that cash flow statements are the most important indicator of a company’s performance, as they do not involve estimates or judgments of the preparers.

While indirect method cash flow statements can be prepared easily from P&L and opening and closing balance sheets, they do not tell us much about where the money is coming from or going to. Because of this, users of financial statements and the accounting standard setters alike have always preferred direct method rather than indirect method cash flow statements.

A little bit of accountancy

Although it may take you to the Land of Nod, a bit of accountancy information here may help non-accountants.

Typically, cash flow statements bundle a company’s cash flow into the following three categories:

  • Operating
  • Investing
  • Financing

Although the direct method is the preferred method, there is basically no difference between the direct and indirect method of cash flow statements except for operating cash flows: while the former properly identifies collections from customers and payments to suppliers, the latter only uses operating profits from P&L and add back non-cash items such as depreciation.

For a direct method cash flow statement, the three categories are typically comprised of the following components in subcategories which can be compiled from a journal entry fact table:

There are three principal modules in bookkeeping: AP, AR, and GL. While all the transactions have GL account codes, only AP transactions have associated vendor codes, and AR transactions have customer codes in the transaction fact table. Not surprisingly, bookkeeping best practice recommends that as many bank transactions as practicable should be recorded through the AR or AP modules so that there is a proper record of exactly where the money is coming from and going to, details of which can be found in the customer and vendor master tables. Because of this, most of the bank transactions can be analyzed by using supplier and customer codes.

The operating-investing-financing groupings can be mapped in the customer, vendor and GL chart of account tables with ease, so that, for example, most of the customer codes are mapped to operating except for purchasers of fixed assets who are mapped to investing, while most of the supplier codes are also mapped to operating except for capex suppliers who are mapped to investing, while loan GL, share capital GL and dividends payable GL are mapped to financing.

Self-Service BI empowerment

Despite their importance, direct method cash flow statements do not usually form part of the standard reports generated from ERP systems like BS and PL, and it usually requires additional configuration work to produce them. Before the advent of Power Pivot, accountants had to depend on ERP programmers to properly prepare direct method cash flow statements as they usually do not possess the technical skills to configure ERP by themselves. Typically, a modification like that required much communication between an accountant and a programmer and could have resulted in a time-consuming project. However, Power Pivot has ended this less than optimal situation and empowered accountants to prepare direct method cash flow statements by themselves.

Tables required

All that is needed to prepare this report are the following five tables usually available from any standard ERP system:

  1. a journal entry transaction fact table (such as FBL3N in SAP), which contains fields for
    • transaction amounts (which should always sum up to zero because of double-entry bookkeeping)
    • GL account codes
    • customer codes (for AR transactions)
    • vendor codes (for AP transactions)
    • transaction dates
    • document numbers (i.e., journal entry identifiers)
  2. a GL chart of accounts
  3. a customer master table
  4. a vendor master table
  5. a calendar table

We then create a star-schema relationship between the journal entry transaction fact table and the dimension tables: the GL chart of accounts, customer master table, vendor master table, and a calendar table. The Diagram View below shows the four dimension (lookup) tables above the journal entry fact (data) table as best practice taught by P3.

4 dimension tables

Although most of the bank transactions’ opposite side in double-entry bookkeeping are recorded as either AP clearings or AR clearings, there are some instances such as with bank direct debits, interest payments and interest income from banks where we do not record the other side of the double entry bookkeeping of bank transactions as AP or AR clearings but instead use GL accounts directly.

🔑The Key: The other side of bank journal entries🗝

In order to create direct method cash flow statements, we need to use the other side of the bank GL in the bank journal entries from the fact table. In fact, the key to solving the direct method cash flow conundrum is getting the other side of the bank journal entries.

We can do this in the following three steps:

1) Identify the rows relating to the bank GL accounts using the calculated column

JournalEntry[Bank GL] =
IF ( JournalEntry[GL] = “Bank”“Bank”BLANK () )

Bank GL accounts
For simplification purposes, instead of using proper GL account codes, customer codes, and supplier codes which typically are numerical codes in the normalized journal entry fact table such as FBL3N, I will use human-readable text coding such as “Bank” for Bank GL, “AP” for AP GL, and so on in the column, JournalEntry[GL]. As companies usually have multiple bank accounts, these bank accounts are all mapped as “Bank” in the chart of accounts and labeled as such in this example.

2) Identify all the double-entry bookkeeping relating to the bank journal entries using the calculated column.

JournalEntry[Bank JE] =
CALCULATE (
    LASTNONBLANK ( JournalEntry[Bank GL], 1 ),
    FILTER (
        ALL ( JournalEntry ),
JournalEntry[DocumentNo] = EARLIER ( JournalEntry[DocumentNo] )
&& JournalEntry[Year] = EARLIER ( JournalEntry[Year] )
    )
)

The calculated column above identifies document numbers (i.e., bookkeeping journal entry identifiers) which contain “Bank” in the calculated column, JournalEntry[Bank GL].  Therefore, journal entries for AP clearing, AR clearing, bank direct debits and payments to the tax authorities, and so on, will be labeled as “Bank” as they will all contain bank GL in their journal entries.

It is always useful to have multiple years of data for comparative and historical trend analysis purposes. As document numbers are unique sequential numbers in a given year, we filter the document number with “Bank” in the calculated column, JournalEntry[Bank GL] for the respective years.

Bank Document Numbers

3) Sum up the amount on the other side of the bank journal entries in a DAX measure.

Bank other side :=
CALCULATE (
    SUM ( [Amount] ),
    FILTER (
JournalEntry,
JournalEntry[Bank JE] = “Bank”
&& JournalEntry[Bank GL] <> “Bank”
    )
)

The DAX measure above sums up the other side of the double-entry bookkeeping in the cash journal entries from the accounting transaction fact table by filtering out rows for bank GL and the non-cash journal entries such as those relating to the recording of AR, AP, depreciation, amortization, stock movements, accruals and deferred income, and provisions, etc. The reason that a negative sign precedes the formula is because the DAX formula above is taking the opposite side of bank GL in the double-entry bookkeeping, therefore, cash from a customer will be credited to the customer account (a negative sign in bookkeeping), while a payment to a supplier will debit the supplier account (a positive sign in bookkeeping) which needs to be shown using the opposite sign in the bank GL account movements.

Well, I hope this wasn’t too much of an accountancy and bookkeeping snoozefest for you. For other P3 posts on cash flow statements, please refer to earlier articles by David Churchward and Kasper de Jonge.

Now it’s time to move on to my favorite topic: working smartly.

Art of Leveraging: work smartly, not laboriously

One of the things I really like about reporting is the ability to leverage. Bookkeeping converts unstructured accounting documents which come in all sorts of shapes and forms, ranging from scribbled handwritten taxi receipts from overseas trips to invoices from various suppliers, into a structured database format with proper vendor codes, cost centers, employee codes, GL codes, tax codes, and a text description of the transaction, through recording in ERP which tends to be quite labor intensive. In contrast, reporting is based on data that are already (well, mostly) converted into a structured database format, and therefore, instead of spending hours toiling over a pile of invoices and receipts, you can sit back and do it as smartly as you wish. We are indeed really lucky to be working with data as Rob blogged here.

Direct method cash flow statements can be retrospectively reconstructed as long as there is a proper bookkeeping journal entry fact table. (Needless to say, if bookkeeping data has to be reconstructed retrospectively, it requires a lot of labor drudgery, so this is based on the assumption that proper bookkeeping records are maintained which I believe all companies should already have at any time.)

Using the method above, I reconstructed direct method cash flow statements starting from a date as far back as over ten years ago which are precisely reconciled with all the bank statements. Power Pivot can handle data with hundreds of millions of rows, which means that direct method cash flow statements can be prepared that span across a decade that identify all the payments to suppliers and receipts from customers as long as the journal entry data exist in the ERP system. The effect of leveraging is exponential, and not linear.

Beyond calculated columns

A couple of cautionary remarks should be added here, however:

  1. The DAX measure above does not follow the general best practice guideline advising us not to use a filter against a fact table, and rather to use it against a lookup table.
  2. Also, DAX maestros advise us to refrain from using calculated columns where possible and to use measures instead because of their more efficient use of RAM.

While DAX measures can perform real acts of magic which were hitherto impossible using traditional Excel, we all know that most things that can be done with calculated columns can also be done using the traditional method. So, what is so special about the technique above? Well, it is special, because it liberates us from the traditional Excel constraint of being limited to 1,048,576 rows, and this is before even mentioning about its smart refresh ability. My data model contains multi-million rows of journal entry fact table data, and it is growing every day. This humongous amount of data could not have been handled by using traditional Excel.

Having said that, we all know that calculated columns are not that great and magical as measures, so why am I using a calculated column to get the other side in the double-entry bookkeeping of bank journal entries instead of using a measure? This is a question which hasn’t been answered yet, so if any of you can come up with a more efficient DAX measure to achieve the same effect as the calculated column above and get the other side of the double entry bookkeeping for bank GL transactions, I’d like to hear about your technique in the comments section 🙂

Forecasting cash flow

Historical cash flow statements can be reconstructed in this way as long as you have a journal entry fact table, but what about forecasting future cash flows? Can this also be done systematically using bookkeeping data? Certainly! We can use the AP and AR open balance fact tables (FBL1N and FBL5N in SAP for example) which contain detailed snapshots of BS balances, and fields such as “Due date” to identify when the money will be paid and collected. In the open balance lists for AP and AR, we will create relationships between the “Due date” and the calendar table (instead of “posting dates” and the calendar table as in the journal entry transaction table used for preparing historical cash flow statements.)

If bookkeepers record AP and AR in a timely manner as transactions occur, and we plan ahead and do not allow unplanned payments not recorded into the system, and if we can get customers to pay on time, we can generate accurate cash flow forecasts 1 month into the future using the open balance list of AP and AR. What’s more, this forecast report can be updated as often as you like, for example, on a daily basis to get the most up to date cash flow forecast with the use of Power Update.

Microsoft’s platform is the world’s most fluid & powerful data toolset.  Get the most out of it.

No one knows the Power BI ecosystem better than the folks who started the whole thing – us.

Let us guide your organization through the change required to become a data-oriented culture while squeezing every last drop* of value out of your Microsoft platform investment.

* – we reserve the right to substitute Olympic-sized swimming pools of value in place of “drops” at our discretion.

Sakiko Stickley

Sakiko is a Fellow of the Institute of Chartered Accountants in England and Wales (ICAEW). She became one of the first PowerPivotPro converts in Japan, and since that time, she has been happily automating her daily reporting work using DAX and M.

This Post Has 10 Comments
  1. Hola genial a medida de ejemplo, aunque no se trabaje con SAP pero se tiene esta base creo que es un muy buen ejemplo para poder realizar y hechar andar el proyecto de automatizacion del flujo de efectivo. una consulta en Microsoft Dinamics que es un ERP, como se haria? jejej. muchas gracias Sakiko muy interesante todo. feliz dia. 🙂

  2. Hi Osiel,

    Although I’ve never used Microsoft Dynamics, I am certain that you can extract a similar standard journal entry fact table which contains the 6 fields, i.e., customer codes, supplier codes, GL codes, transaction amounts which should add up to zero, transaction posting dates, and document numbers (i.e., journal entry identifiers) and generate a direct method cash flow statement in a similar manner by getting the other side of the bank journal entries. Muchas gracias por tu comentario 🙂

  3. It is a good idea to use a calculated column in this case because it improves the speed of the measure (you use the result of the calculated column as a filter).
    You need only this one:

    JournalEntry[Bank JE] =
    LOOKUPVALUE (
    JournalEntry[GL],
    JournalEntry[DocumentNo], JournalEntry[DocumentNo],
    JournalEntry[Year], JournalEntry[Year],
    JournalEntry[GL], “Bank”
    )

    Which can be written this way, but the LOOKUPVALUE is probably better:

    JournalEntry[Bank JE] =
    VAR CurrentDocumentNo = JournalEntry[DocumentNo]
    VAR CurrentYear = JournalEntry[Year]
    VAR Result =
    CALCULATE (
    VALUES ( JournalEntry[GL] ),
    ALL ( JournalEntry ),
    JournalEntry[DocumentNo] = CurrentDocumentNo,
    JournalEntry[Year] = CurrentYear,
    JournalEntry[GL] = “Bank”
    )
    RETURN
    Result

    The measure can be simplified this way (I included KEEPFILTERS to provide the same result of the existing table filter).

    Bank other side :=
    -CALCULATE (
    SUM ( [Amount] ),
    KEEPFILTERS ( JournalEntry[Bank JE] = “Bank” ),
    KEEPFILTERS ( JournalEntry[GL] “Bank” )
    )

  4. I like this article because its theme is cash flow statement which is my field of expertise.
    It’d be great if you could release more articles about finance and accounting.

  5. Hello Sakiko.

    Great article, helping to reinforce my DAX understanding gained from P3’s online DAX training course.

    Only question I have is what is the logic for the calculated column ‘Cash movement’ in the last screenshot above? It seems to show the customer/vendor code but only when Bank_JE = “Bank” and Bank_GL = “”; it seems simple enough but please let me know what logic you used.

    Many thanks
    Ian

  6. Hi Ian,

    Thanks for your question. If fact, I omitted the discussion of the calculated column, JournalEntry[Cash movement] in my article because it is not really a smart formula. Basically, JournalEntry[Cash movement] combines the information from lookup tables (customer and vendor master tables).

    In the screen print, I used the following DAX formula:
    JournalEntry[Cash movement] =if(calculate([Bank other side])=blank(),blank(),JournalEntry[Vendor]&JournalEntry[Customer])

    However, as you may notice, although most of the cash movements can be explained by AP and AR clearings, there are some transactions which do not go through AP or AR and that just have GL account codes as offsetting accounts for bank transactions, so in real life, we will have to incorporate these by including GL in the formula. Nevertheless, there may be more efficient ways to combine the details of the 3 different lookup tables (a customer master table, a vendor master table, and a GL chart of accounts) rather than bringing them all into a calculated column with more than a million rows.

Leave a Comment or Question