How to Prepare a Cash Flow Statement Model That Actually Balances

Bondada Najaraju: Create Cash Flow Statement Model

I’ve worked on several financial due diligence projects for mergers and acquisitions where data provenance was an issue. For starters, it raises doubts and concerns in the buyer’s mind: “How can we trust the accuracy of the numbers if different sources produce different results?” This can be a deal breaker or it can erode trust in the team’s ability to execute. Second, it incurs extra costs as a result of the extra work required to locate the missing pieces, resulting in extra labour hours on both sides of the transaction. All of this can be avoided if a strict but simple methodology is followed:

Create financial models that correctly interconnect the three primary accounting statements: income statement, balance sheet, and profit and loss.

The following is a step-by-step procedure for ensuring that your cash flow is always balanced and tallies. I’ll also explain how the different lines of the cash flow statement are interconnected and why balance sheet accounts, particularly Net Working Capital, are critical to making it all work. I’ve also created an example spreadsheet that demonstrates the required interconnectivity to aid your learning.

Bondada Najaraju: How to Prepare a Cash Flow Statement

A cash flow statement can be constructed in one of two ways. The direct method begins with actual cash inflows and outflows from the company’s operations, whereas the indirect method begins with the profit and loss statement and balance sheet. The latter is the most commonly encountered method because the direct method necessitates granular reporting, which can be time-consuming.

What we hope to achieve is summarized below. It may appear simple, but each line represents a number of previous calculations.

Step 1: Remember the Interconnectivity Between P&L and Balance Sheet

While this is obvious, it is worth remembering that total assets must always equal total liabilities (and equity). The equity account in the balance sheet connects the P&L and balance sheet. Any debit or credit to a P&L account has an immediate impact on the balance sheet because it is recorded on the retained earnings line.

Step 2: The Cash Account Can Be Expressed as a Sum and Subtraction of All Other Accounts

Because total assets and total liabilities are inextricably linked, we know:

Fixed Assets + Receivables + Inventory + Cash = Equity + Financial Debt + Payables + Provisions

Basic arithmetic then allows us to deduce that:

Cash = Equity + Financial Debt + Payables + Provisions – Fixed Assets – Receivables – Inventory

This also implies that the movement of cash (i.e., net cash flow) between two dates will be equal to the sum and subtraction of all other accounts’ movements (the delta):

Net Cash Flow = Δ Cash = Δ Equity + Δ Financial Debt + Δ Payables + Δ Provisions – Δ Fixed Assets – Δ Receivables – Δ Inventory

Step 3: Break Down and Rearrange the Accounts

EQUITY

As previously discussed, the equity account will include the current year’s net income if we are looking at a balance sheet before any dividend payments are made. As a result, we will need to break down the account more granularly in order to see the current year’s net income more clearly.

NET INCOME

Net income is made up of several components, the most prominent of which are EBITDA less depreciation and amortisation (D&A), interest, and tax.

NET WORKING CAPITAL MOVEMENTS

Working capital is made up of three components: inventory and receivables on the asset side, payables on the liability side, and debt. When they are netted against one another, they equal the net working capital position, which is the day-to-day capital balance needed to run the business.

It goes without saying that an increase in the balance movement of a working capital asset represents a cash outflow, whereas the inverse is true for their liability counterparts.

PUT TOGETHER A NEW VIEW OF THE BALANCE SHEET ITEMS

If we add up all of the changes we’ve just made, they’ll appear in the following order:

To an accountant, this may appear haphazard, so re-order in a manner more akin to a traditional cash flow statement format:

Step 4: Convert the Rearranged Balance Sheet Into a Cash Flow Statement

You may have noticed that we have only used one balance sheet position at this point: a position at a fixed point in time (December 31, 2019 in our example). A second balance sheet from a different date would be required to calculate cash flow from here. We will use the balance sheet below, which is dated December 31, 2018, prior to the distribution of FY18 dividends, in this example.

There are two points to consider here:

1.  
2. Because the FY19 fiscal year had not yet begun as of December 18, 2018, all FY19 P&L-related accounts will be equal to zero.
3. The retained earnings figure will include the net income for fiscal year 2018.
4. We will need to look at the movements between December 19 and December 18 in order to calculate a statement of cash flows. We already know that the net cash flow will be equal to 20 – 30 = -10 because of the equality demonstrated in Step 2.
5. We have now created a dynamic and balanced cash flow statement by simply taking the movement between the two balance sheet positions and adding subtotals for clarity of presentation:

How to Improve Your Cash Flow Statement Processes?

This is where having classical accounting knowledge will come in handy, though it is not required. The goal of creating a cash flow format like the one above is to better assess and understand the business’s cash inflows and outflows by category (e.g., operating, financing, and investing). Now that you have a cash flow statement that dynamically links to the balance sheet, it’s time to delve a little deeper. To do so, consider the following questions:

1. Are All Accounts Correctly Categorized?

This is a forensic exercise that will require you to review every line account in your accounting software. After the data has been analysed, a discussion with the financial controller, or CFO, can take place to clarify any disagreements about the correct classification of items.

Trade payables on CapEx are a classic example in this scenario (i.e., outstanding payments due to fixed asset providers). This account is frequently included in trade payables (in current liabilities) and thus classified as net working capital. If this is the case, you must delete it from NWC and include it in the cash flows from investing (CFI) section.

Assuming a +1 movement in trade payables on CapEx between December 18 and December 19, we would make the following changes to our cash flow statement from the preceding example:

2. Is the Presentation Representative of Actual Cash Inflows and Outflows?

The distinction between cash and non-cash can be perplexing to the uninitiated. For example, what should you consider “cash EBITDA” if Company A sold an item for $40 that it purchased for $10 in cash last year, but its customer has yet to pay for it? Should it be $30 (revenue minus COGS, no other OpEx)? Or should it be $0 (given that the item was paid for last year and no proceeds have yet been collected)?

When analysing cash generation, people frequently overlook the importance of combining NWC and EBITDA. When a so-called “non-cash item” affects EBITDA, keep in mind that a balance sheet account is also impacted. It is your responsibility as a cash flow builder to determine which one. And the answer is frequently found within the accounts included within net working capital!

Provisions are a common example of “non-cash items.” Remember that provisions are intended to impact today’s P&L in anticipation of a future expense. Based on that definition, it is safe to conclude that such an item had no true cash implication during the fiscal year and that we should remove it from our cash flow statement.

Provisions appear to have been booked above EBITDA in the previous P&L example. As a result, if we want to mitigate the impact of a change in the provision, we could do the following:

However, we have a problem with this presentation because we would like FY19 EBITDA to correspond to EBITDA as reported in the P&L. As a result, we would prefer to present our cash flow statement as follows:

I would also suggest including a footnote explaining what the removed non-cash items referred to. It may also be appropriate to highlight the business’s “cash” EBITDA component, which would include the following:

Obviously, this can be time-consuming because it necessitates a correct match of all NWC accounts linked to EBITDA items. I don’t believe that this added complexity provides a clearer picture of the company’s cash-generating capabilities, but it may help to provide as much descriptive help to the numbers as possible to your stakeholders.

Bondada Nagaraju: Take the Rules and Apply Them Practically

I hope this has given you the tools you need to create an effective cash flow statement and that you now have a better understanding of the relationships between P&L and balance sheet accounts. Once you understand this methodology, it is up to you to rearrange the various accounts and present them in the manner that makes the most sense for your specific needs and business.

Of course, real-world applications may be slightly more difficult due to the number of accounts in your trial balance, the complexity of accounting principles, and any unusual events, such as an M&A transaction. However, the underlying principles I’ve used in this cash flow statement model remain exactly the same, and if thoroughly followed, will allow you to use your time proactively rather than wasting countless hours balancing!