How to Prepare a Cash Flow Statement Model That Actually Balances

How to Prepare a Cash Flow Statement Model That Actually Balances

When a cash flow statement model doesn’t balance, it can cause immense frustration and wasted time. The root cause of this problem most commonly resides in models being built with inconsistent and contradictory data sources.

To download the example cash flow statement used throughout this post, click here.

Whether I’m looking at acquisition opportunities at HoriZen Capital or building best practices models, I often see cash flow statements that don’t reconcile with the balance sheet.

The most common reason is the wide range of data sources used by the company: the sales teams’ tracking software, CapEx files maintained by the CFO, and inventory reporting metrics from the procurement team, to name a few. When something falls out of line between all these sources, it very quickly causes critical imbalances in a model.

I have worked on several financial due diligence projects for M&A deals where data provenance was a problem. First, it creates doubts and worries in the buyer’s mind: “How can we trust the accuracy of the numbers if different sources give different results?” This can be a dealbreaker or can taper confidence in the team’s ability to execute. Second, it creates unnecessary costs arising from the extra work required to dig out the missing pieces, generating extra labor hours on both sides of the transaction. All of this can be avoided by following a strict but simple methodology:

Build financial models with correct interconnectivity between the three primary accounting statements: income statement, balance sheet, and P&L.

Below is a step-by-step method to ensure your cash flow always balances and tallies. I will also explain the interconnectivity between the different lines of the cash flow statement and demonstrate why balance sheet accounts and, in particular, Net Working Capital have a central role in making it all work. To help your learning, I have also put together an example spreadsheet which demonstrates the required interconnectivity.

Building a Cash Flow Statement

There are two widespread ways to build a cash flow statement. The direct method uses actual cash inflows and outflows from the company’s operations, and the indirect method uses the P&L and balance sheet as a starting point. The latter is the most common method encountered since the direct method requires a granular level of reporting that can prove more cumbersome.

Below is a snapshot of what we aim to achieve. It may look straightforward, but each line represents a number of precedent calculations.

Building a Cash Flow Statement

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

While basic, it’s worth reminding ourselves that total assets must always be equal to total liabilities (and equity). The P&L and balance sheet are interconnected via the equity account in the balance sheet. Any debit or credit to a P&L account will instantly impact the balance sheet through being booked on the retained earnings line.

Building a Cash Flow Statement

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

Due to the inalterable equality of total assets and total liabilities, we know that:

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

Building a Cash Flow Statement

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

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

Step 3: Break Down and Rearrange the Accounts


As discussed earlier, assuming that we are looking at a balance sheet before any payment of dividends, the equity account will include the current year’s net income. As such, we will have to break down the account more granularly to make the current year’s net income appear clearer.

Building a Cash Flow Statement

Net Income

The line item of net income is made of constituent parts: most prominently, EBITDA less depreciation and amortization (D&A), interest, and tax.

Building a Cash Flow Statement

Net Working Capital Movements

Working capital comprises three elements: inventory and receivables on the asset side and payables on the liabilities. When netted off against one another, they subsequently equal the net working capital position, which is the day-to-day capital balance required for running the business.

Building a Cash Flow Statement

It goes without saying that an increased balance movement on a working capital asset constitutes an outflow of cash, while the inverse applies to their liability counterparts.

Put Together a New View of the Balance Sheet Items

If we aggregate all of the changes we have just made, they will come together in the following order:

Building a Cash Flow Statement

To an accountant, this may look quite haphazard, so its best to re-order in the manner of a traditional cash flow statement:

Building a Cash Flow Statement

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

At this stage, you may notice that we have only been using one balance sheet position: a position at a fixed point in time (December 31, 2019 in our example). To calculate cash flow from here, we would need a second balance sheet at a different date. In this example, we will use the balance sheet below, which is dated December 31, 2018, before the distribution of FY18 dividends.

Building a Cash Flow Statement

There are two points to consider here:

  1. As of Dec-18, the FY19 fiscal year had not started—therefore, all FY19 P&L-related accounts will be equal to zero.
  2. The retained earnings figure here will include the FY18 net income.

In order to calculate a statement of cash flows, we will need to look at the movements between Dec-19 and Dec-18. Thanks to the equality that we demonstrated in Step 2, we already know that the net cash flow will be equal to 20 - 30 = -10.

By simply taking the movement between the two balance sheets positions and adding subtotals for clarity of presentation, we have now created a dynamic and balanced cash flow statement:

Building a Cash Flow Statement

How to Improve Your Cash Flow Statement Processes?

This is now the part where having classical accounting knowledge will prove useful, although it is not a prerequisite. The objective of creating a cash flow statement like the one above is to better assess and understand the cash inflows and outflows of the business by their category (e.g., operating, financing, and investing). Now that you have a cash flow statement that links dynamically to the balance sheet, it’s time to dig a bit further. To do so, here are a few questions to ask yourself:

1. Are All Accounts Correctly Categorized?

This is quite a forensic exercise that will essentially require you to look over every line account used in your accounting software. Once analyzed, a discussion with the financial controller, or CFO, can then take place to question any discrepancies of opinion over the correct classification of items.

A classic example in this scenario is trade payables on CapEx (i.e., outstanding payments due to fixed asset providers). It is quite common that this account gets included in the trade payables (in current liabilities) and, as such, gets classified as net working capital. If this is the case, you will need to remove it from NWC and add it to the cash flows from the investing (CFI) section.

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

Building a Cash Flow Statement

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

The notion of cash and non-cash can be quite confusing to the uninitiated. For example, if Company A sold an item for $40 that it purchased for $10 in cash last year, but its customer still has not paid for it, what should you consider “cash EBITDA”? Should it be $30 (revenue less COGS, assuming no other OpEx)? Or should it rather be $0 (considering that the item purchased was paid for last year and no proceeds have been collected yet)?

What people often miss is that NWC and EBITDA should be analyzed together when looking at cash generation. When EBITDA is impacted by a so-called “non-cash item,” remember that there is always a balance sheet account concomitantly impacted. Your responsibility as a cash flow builder is to understand which one. And the answer quite often lies within the accounts included inside net working capital!

A common example of “non-cash items” are provisions. Let’s remember that provisions intend to impact today’s P&L in anticipation of a likely expense in the future. Based on that definition, it is safe to say that such an item has not truly had any cash implication over the fiscal year, and it would make sense to remove it from our cash flow statement.

In the P&L example we’ve used so far, it seems that provisions were booked above EBITDA. Hence, if we want to remove the impact of a change in the provision, here is how we could proceed:

Building a Cash Flow Statement

However, the issue we find with this presentation is that we would like FY19 EBITDA to reconcile to EBITDA as per the P&L. To that end, we would rather present our cash flow statement as follows:

Building a Cash Flow Statement

I would also recommend that you include a footnote explaining what the removed non-cash items were referring to. It may also be appropriate to showcase the “cash” EBITDA component of the business, which would comprise the following:

Building a Cash Flow Statement

Obviously, this can get quite cumbersome, as it requires a correct match of all NWC accounts linked to EBITDA items. I don’t believe, though, that this added complexity gives a clearer view of the company’s cash-generative abilities, but it may help to at least provide your stakeholders with as much descriptive help to the numbers as possible.

Take the Rules and Apply Them Practically

I hope that this provides you with the tools to effectively create a cash flow statement and that you now have a clearer understanding of the interconnections between P&L and balance sheet accounts. Once you understand this methodology, it is up to you to rearrange the different accounts and present them in a way that makes the most sense for your particular needs and your particular business.

Of course, real-life applications may be slightly trickier due to the number of accounts in your trial balance, the complexity of accounting principles, and any exceptional events, like an M&A transaction, for example. However, the underlying principles remain exactly the same, and if followed thoroughly, will allow you to use your time proactively instead of pouring countless hours into a thankless balancing exercise!

Total notes of this article: 673 in 166 rating

Ranking: 4.1 - 166 vote
Click on stars to rate this article

New Posts

Google Apps Script Tutorial for Mastering Macros

Google Apps Script Tutorial for Mastering Macros

Google Apps Script may appear familiar to those with knowledge of Microsoft’s Visual Basic for Applications (VBA) language. However, Google’s coding...

Related posts

What Makes for a Successful Management Buyout Process?

What Makes for a Successful Management Buyout...

Buying out the business you work for and then running it yourself doesn’t have to be a pipe dream. Management buyouts (MBOs) have been popular since...

Experts’ Corner: Pitch Deck Tips for Fundraising Success

Experts’ Corner: Pitch Deck Tips for Fundraising...

Fundraising, for companies at any stage, is undoubtedly a challenging process. Even for seasoned entrepreneurs and startups already experiencing...

Reorganizing for Survival: Building Scenarios

Reorganizing for Survival: Building Scenarios

In the “new normal,” how should business leaders ensure that their business is well-equipped to survive and then thrive? Scenario analysis is a handy...

Analyzing the Effects of Negative Interest Rates Across Five Economies

Analyzing the Effects of Negative Interest Rates...

What happens when interest rates drop below zero - are you really paid to borrow money? Japan, Sweden, Denmark, Switzerland, and the Eurozone have all...

Will Spotify"s Non-IPO Pave the Way for Tech Companies?

Will Spotify"s Non-IPO Pave the Way for Tech...

This year, Spotify will move its headquarters from Manhattan"s Chelsea neighborhood downtown to the Financial District. Beyond its physical presence,...

C Corp vs. S Corp, Partnership, Proprietorship, and LLC:  What Is the Best Business Entity?

C Corp vs. S Corp, Partnership, Proprietorship,...

Choosing between the different business entity options is one of the most common questions (and frustrations) entrepreneurs face. In this article,...

Pearls of Wisdom - The Best Shareholder Letters Nobody Is Reading

Pearls of Wisdom - The Best Shareholder Letters...

Equity analysts often leave out an important source of wisdom when analyzing companies—the annual shareholder letter. This article intends to...

Amazon vs. Walmart: Bezos Goes for the Jugular With Whole Foods Acquisition

Amazon vs. Walmart: Bezos Goes for the Jugular...

Through its acquisition of Whole Foods, Amazon is not only likely to disrupt the grocery shopping experience but is perhaps embarking on a grander...

Bringing an Outsourced Finance Team In-house: A Case Study

Bringing an Outsourced Finance Team In-house: A...

LendEDU, a Y Combinator-backed startup, spent more than $50,000 on its outsourced finance department to create professional monthly financial...

Building the Next Big Thing – A Guide to Business Idea Development

Building the Next Big Thing – A Guide to Business...

Business idea development for new venture concepts is a mixture of art and science. There are playbooks to follow that ensure ideas are validated...

You did not use the site, Click here to remain logged. Timeout: 60 second