Part four of the four-step financial forecasting model in Excel
Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.
This article on forecasting cash flow is the last part of the four-step financial forecasting model in Excel. Having completed our income statement and balance sheet forecasts, we can now turn to the cash flow statement to complete the four-step forecast modeling framework.
By the end of this article, you will be able to:
Understand the importance of incorporating error checks into forecasting cash flow
Derive a forecast cash flow statement based on a forecast income statement or balance sheet
Derive a free cash flow statement that can be used for equity valuation
Forecasting Financial Statements
A cash flow forecast can be derived from the balance sheet and income statement. We begin by forecasting cash flows from operating activities before moving on to forecasting cash flows from investing and financing activities. Operating activities include revenues and operating expenses, while investing activities include the sale or purchase of assets and financing activities with the issuance of shares and raising debt. From forecasting all three activities, we will arrive at the forecast net cash movement.
Cash Flows From Operating Activities
The first step in our cash flow forecast is to forecast cash flows from operating activities, which can be derived from the balance sheet and the income statement.
From the income statement, we use forecast net income and add back the forecast depreciation. We then use the forecast balance sheet to calculate changes in operating assets and liabilities. For each operating asset and liability, we must compare our forecast year in question with the prior year. In this example, changes in receivables and inventory have the effect of increasing the total cash flows. In other words, receivables and inventory in our forecast year are both lower than the prior year.
Cash Flows From Investing Activities
Changes in trade and other payables have a reverse effect – decreasing total cash flows from operating activities. In other words, the payables figure must be lower in our forecast year than the prior year.
Now that we have learned how to calculate cash flows from operating activities, let’s look at investing activities. All investing activity items come from specific fixed assets or property plant & equipment (PP&E) forecasts.
Our model forecasts fixed assets in detail in the “Supporting Schedules” section, where we assume:
Assets are fully depreciated when disposed of and no cash flows are associated with the disposals
No purchase or sale of businesses
As a result, the only item we will forecast in our model will relate to the acquisition of fixed assets or property, plant & equipment (PP&E). It is often referred to as CAPEX, short for capital expenditures.
Cash Flows From Financing Activities
After forecasting investing activities, we will now learn how to calculate cash flows from financing activities. Most financing activity items are calculated by simply comparing the forecast year with the prior year. In our model, we included dividends in our financing activity. In practice, some organizations include dividend cash flows in operating activities. The choice should reflect how dividends are reported in financial statements.
Forecasting Free Cash Flow
Free cash flow to the firm (aka Unlevered Free Cash Flow) forecast is the preferred approach when valuing equities using discounted cash flows. Free cash flows to the firm can be defined by the following formula:
FCF to the firm is Earnings Before Interests and Taxes (EBIT), times one minus the tax rate, where the tax rate is expressed as a percent or decimal. Since depreciation and amortization are non-cash expenses, they are added back. Net capital expenditures and increases in net working capital are then deducted. Note that decreases in working capital will be added to the equation.
Forecasting Free Cash Flow to Equity
Although FCF to the firm is the preferred approach to equity valuation, it is not the only FCF calculation used. There is another FCF variant that is used called FCF to equity.
Free cash flows to equity are used to determine how much cash is available to equity investors after paying off debt interest and satisfying sustainable obligations. In simple terms, FCF to equity is cash flow from operations, minus capital expenditures, plus net debt issued.
Reconciling Free Cash Flows
Since there are only two major differences between FCF to the firm and FCF to equity, it is relatively easy to reconcile the two.
Starting with FCF to equity, we simply deduct the net debt issued, add back the interest expense, and deduct the tax shield on interest. The tax shield on interest is the difference between taxes calculated on EBIT and taxes calculated on earnings before tax.
Thank you for reading CFI’s guide to Forecasting Cash Flow. To keep learning and developing your knowledge of financial analysis, we highly recommend the additional CFI resources below:
Below is a break down of subject weightings in the FMVA® financial analyst program. As you can see there is a heavy focus on financial modeling, finance, Excel, business valuation, budgeting/forecasting, PowerPoint presentations, accounting and business strategy.
A well rounded financial analyst possesses all of the above skills!
Additional Questions & Answers
CFI is the global institution behind the financial modeling and valuation analyst FMVA® Designation. CFI is on a mission to enable anyone to be a great financial analyst and have a great career path. In order to help you advance your career, CFI has compiled many resources to assist you along the path.
In order to become a great financial analyst, here are some more questions and answers for you to discover:
Take your learning and productivity to the next level with our Premium Templates.
Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.
Already have a Self-Study or Full-Immersion membership? Log in
Access Exclusive Templates
Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.