• James Richard

Leveraging Excel in Cash Flow Planning

Updated: Apr 12

Cash flow planning is of paramount importance to the financial well being of an organization. Your company can be profitable but still fail due to poor cash management. Recent volatility has only further emphasized the criticality of understanding your cash flow drivers, planning ahead to gain the flexibility to respond to unforeseen circumstances. Properly executing cash flow calculations requires the following: proficiency in Microsoft Excel and its specific capabilities in regards to cash flow, knowing cash flow planning techniques that addresses today’s volatility, and the adoption of high tech systems that offer powerful insights in this crucial financial planning process.

Cracking Free Cash Flow with Excel

One of the more important specific cash flow measures that you can calculate well with Excel is Free cash flow (FCF). Free cash flow is used in fundamental analysis to measure how much cash an organization generates, after accounting for its capital expenditures. To measure a company's FCF, one would refer to its balance sheet and subtract its capital expenditures from its total cash flow from operating activities.

Excel is a comprehensive and universally well understood tool in FP&A for calculating different formulas and any other computational work in general. You can leverage Excel to efficiently execute solid FCF calculations.

(Below: Example FCF statement)

FCF is a metric that is a crucial part in fundamental financial analysis, demonstrating how well or poorly an organization is managing its cash position. To calculate this metric, you must read the company's balance sheet and pull out the numbers for capital expenditures and total cash flow from operating activities, then subtract the first data point from the second. This can be calculated by hand or by using Microsoft Excel, as in the example included in the following story.

For example, according to their cash flow statement for the end of the fiscal year on September 26th, 2020, Apple Incorporated reported a total cash flow from operating activities of $80.67 billion. Additionally, Apple reported capital expenditures of $7.31 billion for the same period. On the other hand, Apple's competitor, Alphabet Incorporated, reported total cash flow from operating activities of $65.12 billion and capital expenditures of $22.28 billion for the period ending Dec. 31, 2020.

Comparing FCFs with Excel

To compare the FCFs between Apple and Alphabet using Excel, enter the words "Apple Incorporated" in cell B1 and "Alphabet Incorporated" in cell C1.

Next, enter the date "Sept. 26, 2020" into cell B2. Enter "Total Cash Flow From Operating Activities" into cell A3, "Capital Expenditures" into cell A4, and "Free Cash Flow" into cell A5. Then, enter "=80670000000" into cell B3 and "=7310000000" into cell B4.

To calculate Apple's FCF, enter the formula "=B3-B4" into cell B5. The resulting FCF of Apple is $73.36 billion.

Following those steps, enter the date "Dec. 31, 2020" into cell C2. Enter "=65120000000" into cell C3 and "=22280000000" into cell C4. Next, enter the formula "=C3-C4" into cell C5. The result demonstrates that Alphabet's FCF is $42.84 billion.

In this scenario, you would now be provided with both companies' free cash flow numbers, Apple's in cell B5, of $73.36 billion, and Alphabet's in cell C5, of $42.84 billion. One can also enter the numbers into each cell differently. For example, in cell B3, one wouldn't have to enter all the numerals to get a billion-dollar value. Instead, one could simply enter "=80.67," and in a separate cell, clarify that the numbers are in billions. For example, one can note "numbers in billions."

Key Takeaway

Microsoft Excel is an essential tool when working with numbers, as it provides automatic calculations and a simple layout for mathematical work. The formula for free cash flow is a basic one, as it requires only two numbers, both of which can be found easily on a company's financial statement. Once free cash flow is obtained, it helps understand how much available cash a company has for various uses, such as paying dividends or making investments that contribute to the health and growth of the organization.

(Below: Cash Flow Statement with FCF and Net Calculations)

Cash Flow Planning Techniques in a Contemporary Context

There are several critical elements in executing a successful transition from the new normal to next normal to never normal in Cash Flow Planning. The key elements of this sort of transition is as follows:

  1. Growth = Pain + Reflection. In the growth environment, it is crucial to be able to synthesize information and transform it into actionable insights.

  2. Team = Talent + Technology. The abilities of computers and humans are rapidly improving during and after pandemic and effective teams should include both to understand the business better and plan its future better.

  3. From Cash Management to Cash Leadership, which is more center-stage, more frequent and more decision-ready.

  4. Adopting Red-teaming principles, which involve rigorous challenging of underlying assumptions to achieve a higher quality of cash flow plans.

  5. ESG > Sustainability Agenda. The primary focus now is to pursue sustainable cash flow to ensure business sustainability regardless of the size of the organization which you work for.

Dealing with the consequences of the COVID-19 pandemic thus far has been a journey from leadership to a complete leader shift. Inclusive leadership fused with situational leadership has become a crucial framework to rely upon and can live up to the expectations that the business has from finance.

Reacting to Uncertainty with a Proper Approach to Cash Flow Planning

In a recent FP&A trends poll, hundreds of respondents working in FP&A stated whether they changed their approach to cash flow planning during or after the pandemic. Around 31% of organizations should not have done this because they were already cash-flow focused, and another 67% have made changes or plan to do so within the year. Additionally, 77% of respondents admitted that they use Excel as a primary tool for cash flow planning and through their questions, they have demonstrated a clear interest to learn more about Integrated Performance Management Platforms combining FP&A software with Excel.

2,560 views0 comments