Cash flow management is the lifeblood of all sizable businesses. Most companies use accounting software or ERPs to project short term cash flows based on the values of sales & purchases made on credit. However, predicting long term cash flows (>1 quarter into the future), requires manual analysis, since accountants have not provided the ERP with data beyond the current quarter.
The manual analysis I’m talking about here is the creation of a cash flow statement.
Most companies use the indirect method to prepare their cash flow statement, which means they base it on figures from the income statement and balance sheet, rather than on data directly related to cash transactions (aka the direct method).
To prepare a 3 month and 12 month cash projection, therefore, you need to project the income statement and balance sheet fist. Since the three financial statements depend on each other, projecting cash flows is a significant undertaking — you need an integrated financial model.
How to Prepare a 3 Month and 12 Month Cash Flow Projection
Three month and 12 month cash projections are constructed by projecting the income statement, balance sheet, and cash flow statement simultaneously in a 3-statement, integrated financial model.
Almost all companies have an income statement and balance sheet of historical periods. Using them, you can create a base cash flow statement using the indirect method. Together, these three statements are the start of the projection.
In this article, we’ll go through the process of using these historical statements to create a forward-looking model. We’ll do it step-by-step with an example in Excel.
How to Prepare Cash Flows with the Indirect Method
The creation of a CFS alone is an involved process that requires some time to understand. I’ve written a separate article using Amazon’s 2020 financial statements to explain how to do it. We will use the three statements from that article as a base for our 3 month and 12 month cash flow projection below.
What We Need to Create
The cash flow statement portion of our financial model will look like this:
Download the Excel and Follow Along!
However, to build it we need to create an integrated financial model that looks like this:
This looks extremely complicated, I know. But relationships between the income statement, balance sheet, and cash flow statement are simple. With very little input, we’ll be able to build each month with simple logic. Let’s look at that now.
Steps to Prepare a Cash Flow Projection with an Integrated Financial Model
The first step to prepare our cash flow projection is to set up our Excel sheet in the order of the following sections:
- Assumptions
- Income Statement
- Balance Sheet
- Cash Flow Statement
- Supporting Schedules
Assumptions include information we presume about key line items on the P&L and balance sheet. For example, we need to assume a growth metric (in %) for revenue. It also includes working capital days assumptions for the balance sheet.
The income statement (1) includes historical figures and (2) uses the assumptions section to project revenue and expenses from operations. It also (3) uses the supporting schedules section for depreciation, amortization, and interest expenses.
The balance sheet (1) includes historical values and (2) projects working capital and retained earnings using the income statement and the assumptions section. It also (3) uses the supporting schedules section to project LT assets and LT liabilities.
The cash flow statement uses the income statement and balance sheet, as usual. However, for simplicity’s sake, it often uses net changes in working capital from the supporting schedule’s section rather than a complex formula referencing the balance sheet. Likewise, it often uses changes in LT assets and LT liabilities from the assumptions section to avoid complex formulas.
The supporting schedules section outlines (1) working capital from the balance sheet, (2) the depreciation schedule, (3) changes to principal debt amounts, and (4) an interest expense using the assumptions section.
In other words, the income statement and balance sheet are projected using assumptions and supporting schedules, then the cash flow statement is produced from them. The only exception is that the cash line item on the B/S comes from the CFS.
Download the Excel and Follow Along!
Order of Creation: 7 Steps
Since the 5 sections of the integrated financial model are interconnected, it’s difficult to envision in what order they should be created. The best approach is to:
- Input the historical P&L and balance sheet numbers
- Create the assumptions section historical values AND projections
- Create the supporting schedule’s historical values AND projections
- Create the income statement projections
- Create the cash flow statement historical values
- Create balance sheet projections
- Create cash flow statement projections
Limitations
The model I use in the Excel here is based on a system of assumptions and schedules, which has limitations. For example, it does not allow for the projection of gains (losses) on the sale of long term assets, since the lines for individual depreciating assets are grouped together in a standard financial model.
In order to include them, we would need to expand the schedule. However, this would also require source depreciation and amortization schedules. Since we’re using public company data, those details are rarely available. We’re obligated to simplify and exclude some elements from the projection.
Step 1: Input Historical Income Statement and Balance Sheet Data
The first and easiest step is to input historical P&L and balance sheet data by either referencing statements in another location or by hard coding numbers into the relevant year column of the model, which is most often the left most column.
Tips for Inputting Figures
- You should create references for line items but use formulas for totals
- Limit the number of line items to the minimum necessary for projections so it’s easy to understand and explore the model
It will look like this:
Step 2: Create the assumptions section historical values AND projections
The second step is to decide how you will project different items on the income statement and balance sheet.
P&L Assumptions
In most cases, revenue will grow at a steady rate, cost of sales will maintain the same percent of revenue, and operating expenses will be constant, unless there are specific plans to increase them that aren’t available in the financials.
Depreciation and interest expenses/income, however, are more complicated. We need to establish a percent of total opening LT assets that will constitute the monthly depreciation, and determine the same for interest expense of total liabilities. We can do so by using ratios in historical data. The P&L assumptions should look like the following (download the Excel above to explore each cell):
Balance Sheet Assumptions
Balance sheet assumption projections are more sophisticated than the P&L. Short-term assets and liabilities, also known as working capital, are projected using a methodology called “days outstanding.” In other words, you calculate how many days in a given period it takes for your current items to clear.
This is done by taking the percent of the current item of a related income statement item, then multiplying by the # of days in the period you want to asses.
The assumption with this calculation is that the outstanding balance of the current item represents a stable number throughout the year. We can consider that there are additions and subtractions, but that the total value remains constant and thus represents the time that any one entry in the account will enter and leave.
For example, accounts receivable days is calculated as [accounts receivable]/[revenue]*[days in period]. This is the base for projected values.
NOTE: when we establish this value based on historical figures in the case of Amazon, we’re looking at annualized numbers, so the [days in period] is 365. However, our projections are monthly, so we use 31 (or 30 or 28, depending on the month). You can see these calculations in the Excel.
LT assets and liabilities have a different calculation. Because these items are not directly correlated to the income statement, we need to know the expectations of the company for issuing or paying debt, purchasing and selling new assets (CAPEX), issuing equity, and investing in marketable shares.
In this picture we can see the breakdown of these items. Debt issuance and equity are projected as a straight line, CAPEX as minimal except for an investment in August, marketable shares as stable except for an investment in July, and no prepared sale of assets.
Step 3: Create the supporting schedule’s historical values AND projections
Now that we have the base for our simple projections of the balance sheet and income statement, we need to develop supporting schedules to understand changes in balance sheet items.
Remember, while the income statement works on a sum of values in given time periods, the balance sheet reflects a standing total of values over time. Supporting schedules allow us to identify changes to the balance sheet in each period.
More importantly for the reader of this article, the changes we show in the supporting schedule are used in the cash flow statement.
These schedules are created by showing the opening balance of each relevant balance sheet account, applying either an assumed change or one calculated using the “days outstanding” changes. Depreciation expenses and interest expenses are also created here. But since they are not balance sheet items, we project them as either a relative percentage or as a straight line, not a change over time.
Complications due to “Mensualized” Projections from Annualized Historical Data
Whenever possible, it is best to project your integrated financial model on the same period type as the historical data. In our case study, we can’t, which creates a challenge.
The reason is that short-term balance sheet items are based on a monthly [days outstanding] logic, and the month-over-month change calculation doesn’t work if the historical values are annual.
Therefore, we assume that the most recent monthly value is the total of the last year divided by 12. This is a weakness in the model.
Since changes in our long-term items are based on straight-line assumptions, however, they do not usually have this problem.
Step 4: Create the income statement projections
Now that we have the historical figures for assumptions, supporting schedules, and the income statement and balance sheet, we can project the income statement using the relevant values.
Notably, we project all line items on the P&L according to assumptions, except for depreciation and interest activities, which are linked to the supporting schedules. Use the Excel download above to explore this logic directly in the model.
Step 5: Create the cash flow statement historical values
Finally, we can begin to put the cash flow statement together using the indirect method. For a full description of how to do this, please see the article Excel Guide: How to Prepare Cash Flows with the Indirect Method.
A key point to remember about the cash flow projection is that the ending balance on the CFS statement must always be the reference for the balance sheet cash line.
Ensure that the balance sheet cash line matches the CFS ending cash balance in the historical number — that is, don’t create a reference. This will ensure your CFS logic is correct. But for balance sheet cash line projections, the CFS ending balance should be directly referenced.
Step 6: Create balance sheet projections
When the supporting schedules and cash flows have been created correctly, projecting the balance sheet is very easy. You start with ending cash balance from the cash flow statement, then reference line-by-line the ending balances located in the supporting schedules.
For the moment, the cash line will link to an empty cell because we haven’t projected the cash flow yet. This is a necessity since we need the balance sheet items to project cash flows, but go ahead and create the reference so that when we build the cash flows, the B/S will automatically be completed.
In many cases, the various equity accounts are not expected to change at all. An easy way to project them is with a straight line. No change, no impact to other statements. Just stability.
Step 7: Create cash flow statement projections
Download the Excel and Follow Along!
Projecting cash flows is the final step in this analysis. We now have all of the information necessary from projections on the income statement and B/S to complete the projection. The logic for its construction is explained in another article, but it’s important to note that the final cash line in the CFS will become the ending balance for cash on the the B/S.
Conclusion
If you found this article helpful, check out more content on data, finance, and business analysis at the AnalystAnswers homepage!
