Preparing Direct Method Cash Flow Analysis using Dax

Although it may lull you to sleep, 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 essentially no difference between the direct and indirect methods of cash flow statements, except for operating cash flows: while the former properly identifies collections from customers and payments to suppliers, the latter relies on operating profits from the P&L and adds 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 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 practices recommend that as many bank transactions as practicable be recorded through the AR or AP modules to ensure a proper record of exactly where the money is coming from and going to. The details of these transactions can be found in the customer and vendor master tables. Because of this, most bank transactions can be analyzed 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, which are mapped to investing, while most of the supplier codes are also mapped to operating, except for capex suppliers, which are mapped to investing. Meanwhile, 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 don't usually form part of the standard reports generated by ERP systems like the balance sheet (BS) and profit and loss statement (PL), and producing them typically requires additional configuration work. Before the advent of Power Pivot, accountants had to rely on ERP programmers to properly prepare direct method cash flow statements, as they usually couldn’t configure ERP systems themselves. A modification like that typically involved a lot of back-and-forth between an accountant and an ERP developer, often turning into a time-consuming project that could drag on for several months, or even years in slower cases. However, Power Pivot has put an end to this less-than-ideal situation, empowering accountants to prepare direct method cash flow statements on their own.

 

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 FAGLL03H in SAP S/4HANA or FBL3N in ECC), 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 for easier understanding of data model.  

 

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

 

🗝️️ The Key: The other side of bank journal entries 🔓

To create direct method cash flow statements, we need to use the opposite 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 puzzle lies in identifying the other side of the bank journal entries.

We can achieve this in three steps:

1) Identify the rows related to the bank GL accounts using a calculated column.

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

 For simplification purposes, instead of using actual GL account codes, customer codes, and supplier codes, which are typically numerical in the normalized journal entry fact table (such as FBL3N), I will use human-readable text codes, such as “Bank” for Bank GL, “AP” for AP GL, and so on in the column JournalEntry[GL]. Since 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.

 

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 non-cash journal entries, such as those related to AR, AP, depreciation, amortization, stock movements, accruals, deferred income, provisions, etc. The reason a negative sign precedes the formula is because the DAX measure is capturing the opposite side of the bank GL in the double-entry bookkeeping. For example, cash from a customer is credited to the customer account (a negative sign in bookkeeping), while a payment to a supplier debits the supplier account (a positive sign in bookkeeping). These need to be shown with the opposite sign in the bank GL account movements.

Well, I hope this wasn’t too much of an accounting and bookkeeping snoozefest for you.🥱

Now, it’s time to move on to my favorite topic: working smart!

 

Art of Leveraging: 💡 Work smart, not hard ⛏️

One of the things I absolutely love about reporting is the ability to leverage. Bookkeeping is all about taking unstructured accounting documents—everything from scribbled taxi receipts from overseas trips to supplier invoices—and converting them into a structured database format. This includes vendor codes, cost centers, employee codes, GL codes, tax codes, and transaction descriptions. Recording all of this into an ERP system can be pretty labor-intensive (although, who knows, with AI evolving, we might soon see it converting all sorts of messy data into neat databases). On the other hand, reporting deals with data that’s already (well, mostly) structured. So, instead of spending hours slogging through piles of invoices and receipts, you can sit back and work as smartly as you want. We’re pretty lucky to be working with data.

 

Direct method cash flow statements can be reconstructed retrospectively, as long as there’s a proper bookkeeping journal entry fact table. (Of course, if bookkeeping data needs to be reconstructed retrospectively, that’s a whole lot of labor-intensive work. But for the sake of argument, let’s assume proper bookkeeping records are being maintained, as they should be in any company.)

 

Using this method, I was able to reconstruct direct method cash flow statements dating back over ten years, fully reconciled with all bank statements. Power Pivot can handle data with hundreds of millions of rows, meaning you can prepare direct method cash flow statements that span multiple years, identifying all payments to suppliers and receipts from customers—provided the journal entry data exists in the ERP system. The effect of leveraging is exponential, not linear.

 

Since I first implemented this direct method cash flow statement many years ago, I have reused the formula across multiple companies, and it has always produced the required output—even when the bank GL uses intermediary clearing GL accounts for cash inflows and outflows.

 

Forecasting Cash Flow

Historical cash flow statements can be reconstructed 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? Absolutely! We can use the AP and AR open balance fact tables (like FBL1N and FBL5N, or Line Item Display in SAP, for example), which contain detailed snapshots of balance sheet balances and fields like “Due date” to determine when money will be paid or collected. In the open balance lists for AP and AR, we create relationships between the “Due date” and the calendar table (instead of using "posting dates" as we do for historical cash flow statements).

 

If bookkeepers record AP and AR in a timely manner as transactions occur, if we plan ahead to avoid unplanned payments that aren’t recorded in the system, and if customers pay on time, we can generate accurate cash flow forecasts for future payments and collections based on the data currently recorded in the ERP. What’s more, this forecast report can be updated as frequently as needed—daily, for example—using Power Automate to provide the most up-to-date cash flow forecast.