Excel Guide: How to Prepare Cash Flows with the Indirect Method

Cash is king — financial analysts and accountants know this well. But when a company grows to more than its founding team, understanding where cash is disbursed and received is not an easy task.

One way to tackle this is to directly track all cash transactions, such as receipts from customers or payments to employees, in an accounting software. We call this the direct method, and most financial institutions such as the FASB prefer it because it provides extensive details on the types of cash flows.

However, hardly any companies use it for the simple reason that accounting information is not collected in this way, and to do so would be very costly.

Instead, most companies use the indirect method to prepare the statement of cash flows. The indirect method requires combining information from the company’s income statement (or profit and loss statement) and its balance sheet.

Creating the cash flow statement using the indirect method is considered one of the most challenging exercises in finance since it requires thorough knowledge of accounting methodologies, the company’s business model, debt calculations, tax calculations, and the way in which these items fit together.

This article will use Amazon’s 2020 financial statements to show you, step-by-step, how to prepare the statement of cash flows using the indirect method.

We will use a free Excel template so you can interact with the process and apply it to other examples in your work.

Additionally, we will explore some basic concepts about the income statement and balance sheet. If you think you already have a strong understanding of these, I still encourage you to read them because we all need a reminder of the fundamentals from time to time. Nevertheless, you can skip them as needed.

NOTE: this article is more than 4000 words long and will require more than one sitting to finish. I’ve also provided a PDF version of it here so you can print it out.

Excel Cash Flow Calculator Template

If you’re just looking for a calculator you can use to quickly prepare a cash flow statement, you can download my Excel calculator here.

You just need to replace all of the hard-coded numbers in blue on the income statement and balance sheet with your statements’ numbers and remove the values of any items not present on your statements.

However, even if you’re only looking for a calculator, you have to know which values to input. If you have any doubts about those, read this article through one time, then come back to the calculator.

Make Sure You Learn the Steps to Creation

This article explains how to build a cash flow statement from Amazon’s SEC filings with a template. To see detailed instructions on how to build cash flow statements from ANY source, sign up now to receive the checklist upon release.

Accrual vs Cash Accounting

The reason why we need the indirect method is a result of the accrual basis of accounting. The accrual basis of accounting stipulates (1) that financial income should be recorded when the service is delivered (vs when cash is received), (2) that costs should be recorded at the same time that the related revenue is earned, and (3) that gains should only be recorded when they are certain, but losses should be recorded when they are likely.

This means that a company’s revenue does not accurately reflect its cash receipts, and that costs and expenses do not accurately reflect the cash we have paid out. Instead, the balance sheet records non-cash income and expenses as payables and receivables.

Moreover, we do not record cash received from loans on our profit & loss (P&L) statement. These amounts are not related to operations, so they’re only present on the balance sheet. Interest payments, however, are located on the income statement.

Likewise, when we record gains or losses from the sale of an asset on the P&L, this does not represent the money we’ve received or disbursed, but the difference between the sale price and the asset’s book value.

These principles of accrual-based accounting are why we need to use the elements of the P&L and balance sheet to show in what activities we affect our cash flow statement.

If you would like to learn more about financial principles, check out this article on financial analytics.

Receipts & Disbursements

When we’re talking about movements of cash, we use the terms receipts and disbursements. A receipt is incoming cash whereas a disbursement is outgoing cash.

The reason we use these terms is a question of lexical clarity. When we deliver a product or service per the accrual method of accounting, we might say “payment,” “income,” or “revenue.” Likewise, when the company receives an invoice, we might say “payment,” “cost,” “expense.” It’s very common, since these terms naturally come to mind.

But if you say “income” or “payment” when talking about cash, it’s unclear whether you are referring to accrued amounts or cash transactions. As a rule of thumb, use “cash receipt” and “cash disbursement.”

Sample Cash Flow Statement: What We Need to Create

A cash flow statement should look like the following Excel snippet. It’s format is important, as we’ll discuss in depth in the next section. For the moment, however, take time to look at each of the items below.

Note that we’re using numbers from the Amazon Inc’s SEC 10-k filings in 2020 as a base. However, official cash flow statements rarely reconcile with official balance sheets and income statements due to a number of reasons, including non-operational write-offs in current assets and liabilities, the exclusion of affiliate businesses in the cash flow statement, difference in FX adjustment among the statements, and balance sheet adjustments for doubtful accounts.

For this reason, I’ve manually adjusted the numbers for the educational purpose of this article. Moreover, I have added several line items that Amazon excluded but are important to our understanding of cash flow.

Without further ado, here’s a sample cash flow statement:

---

Movements Over Time as a Key Concept

First thing’s first, we always note the starting and ending dates of our analysis at the top of the cash flow statement. This allows us to easily reference the period we’re examining if we have any doubts. In this case, it’s December 31, 2019 to December 31, 2020. One full year.

Cash flow statements always show the movement of cash over a period of time. Unlike the balance sheet, which shows holdings at a given date in time, cash flow statements show total movements in various activities during the course of two accounting closings.

Accounting Closing Dates as Starting and Ending Periods

The start and finish dates on a CFS must correspond to the dates that accounting periods end. The reason why they must match is that we’re using net profit on the P&L as the base for our cash flows, and all accounts on the P&L are cleared to zero at the end of an accounting period. Why? Because like the CFS, the P&L shows performance over a fixed period of time.

If you attempt to construct the cash flow statement in the middle of an accounting period, you must reset the P&L accounts to zero, and in theory this is possible. The problem is that accountants will not have booked all relevant invoices and adjustments, so your accrual basis will not reflect the reality…. even if cash reconciles with the balance sheet.

Operating Cash Flows, Investing Cash Flows, and Financing Cash Flows

Take another look at the cash flow statement above. You’ll see that it’s broken down into 3 main sections:

1. Cash flow from operations
2. Cash flow from investing activities, and
3. Cash flow from financing

But what do these mean?

Cash Flow from Operations Overview

Cash flow from operations consists of cash receipts from customers and cash disbursements to suppliers, employees, and overhead expenses. In the indirect method, we don’t see these items broken down. Instead, we adjust net profit by adding back (or reversing the expense of) non-cash expenses, namely depreciation.

In the operating section, we also remove gains (or losses) from the sale of long-term assets because these are investing activities that we address in investing section for the CFS and should not be “counted twice” by leaving them in net profit.

For example, if we sold equipment for $6K, and the gain on sale was $4k, then we would have a total cash movement of $10k, which is not correct. We only received $6k. We’ll explore this thoroughly in the detailed breakdown below.

Cash Flow from Investing Activities Overview

Cash flow from investing activities consist of proceeds from the sale of long-term (LT) assets and the purchase of new LT assets, as well as the purchase of any marketable securities such as bonds and stocks.

Simply put, cash flow from investing includes all activities that involve the sale and purchase of LT assets (not inventory, which is a current asset). It excludes all activities involving loans and equity injections, as well as cash receipts form the company’s operations in exchange for goods and services.

Cash Flow from Financing Activities Overview

Cash flow from financing activities consists of four core transactions: (1) receipts for increases in principle loan amounts, (2) disbursements for reductions in loan amounts, (3) receipts of increases in paid in capital or stock issuance, and (4) disbursements for dividends.

In other words, financing activities deal with loans and equity accounts.

However, interest payments on loans are not a financing activity! They are an operating activity. This may seam counterintuitive, but it makes sense when we think about liabilities as financing tools. The loan principle is what we use to finance the purchase of assets, and the interest payment is the expense we’re charged to use that financing.

Some people draw connections between interest payments and current depreciation. They think — “if current depreciation is an adjustment to the value of a loan and it’s included on the balance sheet as an expense, then shouldn’t we also see the details of the adjustment to a loan on the balance sheet as well?”

The key word here is “adjustment.” Interest expense is not an adjustment to loans. It’s an acquisition cost. Current depreciation, on the other hand, is simply a way of recognizing the cost of an asset over time as we wear it out with use. The two ideas are separate.

What do you start with?

One of the most common questions about the indirect method of cash flows is where to start. It’s simple. You always start with net profit or loss. Net profit is found on the income statement. Let’s look at it now.

Income Statement (Profit & Loss Statement)

To construct the cash flow statement using the indirect method, we combine information from the two fundamental financial statements. The first of these is the Income Statement, also known as the Profit & Loss Statement (P&L). You’ve probably heard about this on multiple occasions.

The P&L shows the profitability of a company, i.e the services and good it delivers and the costs and expenses it incurs to do so. Here is the P&L we’ll use to construct our statement of cash flows:

The first line of the P&L is revenue. Revenue is the ultimate cash receipt operating activity. When we refer to how the “business” performs, we’re talking about this line. Revenue may be earned while the cash is not yet received. To find out how much is “unpaid,” we’ll need information from the balance sheet later.

Just under revenue is cost of sales (CoS), which is the direct costs associated with the business.

In the case of Amazon, CoS are “consumer products, inbound and outbound shipping costs, including costs related to sortation and delivery centers and where we are the transportation service provider, and digital media content costs.”¹

Thereafter, we have a list of operating expenses including fulfillment, technology and content, marketing, and SG&A. While it would be useful to understand these items from an investor’s perspective, we do not need to know them in order to construct the cash flow statement.

Along with CoS, operating expenses may be incurred without Amazon having disbursed any cash. To find out how much is “unpaid,” we’ll need information from the balance sheet.

The last three items under operating expenses are depreciation and amortization expenses. These are wholly non-cash items that represent the current portion of the cost of an asset that loses value over time. We had a cash disbursement for them up-front on the balance sheet but only see a portion here.

Finally, we have interest income, interest expense, Interest income is money that Amazon earns either through money generating interest in a bank or on loans it provides to employees and other companies. Interest expense is the cost of its loans.

There’s also a unique line called “equity investment share of profit.” This unusual account refers to dividends that Amazon received as a result of its minority equity participation in other companies.

“Additional Information”

You’ll see in the picture above a sentence mentioning the proceeds of $6,000 on the sale of an asset. Normally, this information is only available in sections titled “additional information” of a company’s filing, or through someone with knowledge of the company in a private company.

It’s important to seek out even non-financial information when building a cash flow statement, since we might not be able to easily determine what the total proceeds of the sale of a fixed asset is if not. As you can see, the gain from the sale is $4,000, suggesting that the remaining value of the asset on the balance sheet before sale was $2,000.

We’ll look at this closer when we build the CFS, but keep in mind that not all the information you need is in the financial statements!

Balance Sheet

To construct the cash flow statement using the indirect method, we need to combine information from the P&L with the balance sheet (B/S).

The balance sheet shows all of the companies assets, liabilities, and equity accounts. Three things to always remember about the balance sheet are:

1. the total amount of assets must always equal the total amount of liabilities + equity, a dynamic commonly notated as A = L + E and referred to as capital structure,
2. liabilities and equity are means of financing assets — nothing more, and
3. the values in all non-cash B/S accounts always change in relationship to the cash account (asset) and the retained earnings (equity).

Since the balance sheet is a “snapshot” of the company’s holdings at a given period in time, the only way to know what’s happened during a specific period is to calculate the changes in account values during a given period. In our example, we’ll calculate the change (or “delta”) from December 31, 2019 to December 31, 2020. Here’s the B/S we’ll use in our cash flow analysis:

As you can see, I’ve highlighted asset accounts in blue, liabilities in red, and equity in green. You can see that the total balances of assets is equal to the total balance of liabilities + equity on both December 31, 2019 and December 31, 2020. Our balance sheet is in equilibrium.

Current vs Non-current Assets and Liabilities

You can also see from the picture that the assets and liabilities are separated into current and long-term sections. Current assets are those that are expected to be cash or turned into cash in less than one year, whereas long-term (or “non-current”) assets carry value for more than one year, and must be depreciated. The one exception to depreciation is land, which never gets worn-out with use.

Current liabilities are obligations that must be met within one year, whereas long-term liabilities are those that must be met over the course of more than one year.

NOTE: long-term liabilities are paid in the current period, but their total value is not repaid within one year. A monthly payment on a 5 year loan, for example, is the current portion of a long-term liability.

Steps to Prepare the Cash Flow Statement

The steps to prepare a cash flow statement with the indirect method follow the structure of the statement.

In the following sections I will go through the steps, one by one, in a list format. I’ve also included them in the PDF version of this article that you can download. For links and references, feel free to follow along by downloading the Excel below. Let’s build Amazon’s cash flow statement for 2020!

Download the Excel Here!

1. Adjust Net Income for Non-Cash Items

1. Put Income Statement and Balance Sheet in separate tabs, then create a blank tab for your cash flow statement.
2. List the start and end dates of your analysis at the top of the cash flow tab.
3. List the starting and ending cash balances from the balance sheet’s assets. See figure 1.

4. Title your first section “Cash flow from Operating Activities”
5. In the first line, create a reference to net income from the income statement tab.
6. Remove depreciation (for tangible assets) and amortization (for intangible assets) expenses on the income statement from the net income. Why? Depreciation represents non-cash portions of a LT asset we will or have already accounted for in the investing section of the cash flow statement. These items need to be added back since they do not represent cash expenses.
7. Remove gains (losses) from the sale of assets on the income statement. The gains Amazon has on its income statement are calculated as [proceeds from the sale – net asset value]. The proceeds from the sale were $6,000 and the net asset value was $2,000, after depreciation in 2020, so the gains were $4,000.
   
   Explanation: at the moment of the sale, this means we has $6,000 cash receipts. But we had to remove the remaining value of the asset on our balance sheet of $2,000 since the asset no longer belongs to us. By adding 6k and removing 2k from our assets, the total net asset change is +$4,000. That’s why we record only $4,000 on the income statement, which will later be reflected in equity account retained earnings, creating equilibrium. (This is a tough concept. We won’t dwell on it now, but you can learn more about retained earnings in this article.)
   
   In short, we remove the gains by subtracting them from net profit. Had it been a loss, we would add it back. Don’t forget, we’re building a cash statement, so we’ll include the $6,000 cash proceeds under investing activities.

2. Adjust Net Income For Changes in Non-Cash Working Capital

1. Create a line titled “Working capital adjustments”
2. Create a line titled “Assets”
3. In your balance sheet, make sure you’ve created a column that calculates the delta, or change, between the values in the accounts from December 31, 2019 to December 31, 2020. These are the values we’ll use next.

4. Subtract increases in accounts receivable (AR). AR is the portion of income that customers purchased on credit. They have an obligation to pay Amazon because Amazon delivered a service. However, they will do so in the future — no cash has come been received yet. An increase in this account must therefore be subtracted from net profit, whereas a decrease must be added back.
5. Subtract increases in prepaid insurance. Prepaid insurance is a cash disbursement for coverage over the course of the year. Similar to depreciation, we only include the current portion of this prepayment on the income statement as an expense. If the balance has increased, we need to remove it from net profit since we paid this out.
6. Subtract increases in inventory. Inventory is the balance sheet equivalent of our cost of sales. When we make a sale and deliver the product, we move the relevant inventory to the income statement according to the matching principle. However, increases in inventory means we have disbursed cash, but not yet recorded this on the income statement. Increases, therefore, must be subtracted from net profit.
7. Create a line titled “Liabilities”
8. Add increases in accounts payable (AP). Accounts payable are obligations Amazon has towards others. When an expense, for example, is recorded on the income statement but we don’t disburse cash for it, then we record the value under accounts payable. But AP doesn’t only include income statement expenses. When we increase inventory, some of this value may be recorded as AP. Any non-cash increases in inventory, therefore, are offset by increases in AP.
   
   Moreover, in theory, AP can also include amounts that fund long-term assets, but this is extremely rare. Big asset purchases usually require one up-front payment.
   
9. Add increases in salaries payable. Salaries payable accumulates over the course of the month as employees work but are not paid. At the end of the month, the account is debited to zero, and we credit cash to pay salaried workers. An increase in salaries payable thus means that the amounts accrued on the income statement are non-cash. We thus add increases in salaries payable and subtract reductions.
10. Add increases in unearned revenue. Unearned revenue, also known as deferred revenue, is a liability account that represents cash the company has received but not earned. This means increases in the account represent cash receipts that aren’t present on the income statement. We must add increases to net profit.
11. Add increase in interest payable. While we don’t see it in our example of Amazon, it’s important to look out for interest payable as a current liability adjustment to cash. Interest payable accrues when the interest on the current portion of a loan is unpaid. The company may be low on cash and waiting to pay, or simply waiting until the end of a period to disburse the cash. In either case, increases in interest payable must be added to net profit.
12. Add increase in tax payable. Taxes payable are similar to accounts payable, but specific to taxes. Increases in this account should be added back to net profit. In our example of Amazon, there are no taxes payable, but you should know to look for this in other cases.

3. Add or Subtract Cash From Investing Activities

1. Create a line titled “Cash from investing activities”
2. Increase cash for cash receipts on the sale of assets. In step 1.7 (7th step under adjust net income for non-cash items), we removed any gains or losses on the sale of an asset from the P&L. This is the step in which we account for the incoming cash on the sale of the asset. The details of sold assets are often located in “additional information” schedules since on the balance sheet these values can be included in total changes in LT assets. In our case, it’s mentioned on the income statement, and we increase net profit by $6,000 to account for cash receipts on the sale.
3. Decrease cash for disbursements on the purchase of new LT assets. When we purchase new assets, we almost always pay up front. The going assumption in a cash flow statement is that increases in long term assets decrease cash, even if in some cases they are partially funded by accounts payable. We decrease cash for increases in LT assets.
4. Decrease cash for the purchase of marketable securities. Although they are a current asset, marketable securities are an investing activity. They’re current because we could sell them very quickly for cash, but they have nothing to do with operations. Record cash disbursements for increases in marketable securities, as is the case with Amazon. However, in the case of decreases, we actually have a sale of an asset and must follow the same procedure for removing gain/loss on the income statement and recording the sale value through details provided in an “additional information” section.

4. Add or Subtract Cash From Financing Activities

1. Create a line titled “Cash from financing activities.”
2. Record cash receipts for increases in principle loan amounts. We don’t have any on our balance sheet for Amazon, but increases in loans means the company has taken on additional debt to finance either the purchase of a large asset or to working capital obligations. The first is often called a LT loan and the latter a rolling credit facility. In either case, increases in these accounts means cash receipts for Amazon.
3. Record cash receipts for interest received on loans we provide. In some cases, companies provide small loans to employees or other companies. This is most common in companies with a lot of “float” — what Warren Buffet calls excess cash. When we provide these loans, there is a cash disbursement for the entirety of the loan. In periods thereafter, we earn interest on those amounts. This is an asset account, and Amazon does not have any. However, it’s important to keep this in mind when building a cash flow statement.
4. Record cash receipts for increases in paid-in capital. Paid-in capital consists of initial cash contribution as well as subsequent contributions of shareholders. In private companies, money that entering shareholders provide in exchange for shares is recorded here. In the case of Amazon, paid/in capital increases mean the company has issued new shares on the public markets. This is an influx of money, and increases cash.
5. Record cash disbursements for increases in dividends. Dividends are cash disbursements paid to existing shareholders as a means of rewarding them for their participation. These are often paid out on a quarterly basis in public companies. An increase in dividends means the company disbursed cash, so we need to subtract these changes.

12 Month and 3 Month Cash Flow

To project cash flow into the future, we need an integrated financial model with all three financial statements. The income statement and its revenue growth are the base for the projection. From there, we can predict working capital items based on a set of basic ratios. Other current assets can be related to the income statement based on the relevant metric, or projected with the current trend.

Long-term assets need to be projected based on the depreciation or amortization schedule and planned CAPEX, and any loan activity can be determined using assumptions for debt issuance or repayments. A full explanation is outside the scope of this article, but you can learn more about projections in articles on AnalystAnswers.com.

If you found this article helpful, share it on social media and check out more at the AnalystAnswers.com homepage!

1. Amazon 10k 2020 [↩]