Create Cash Flow Statement Format with Indirect Method in Excel

What Is a Cash Flow Statement?

A cash flow statement is a type of financial statement that shows the inflow and outflow of cash and cash equivalents of a company during a period of time. It shows the cash flow by going through the main 3 components: Operations, Investment, and Financing. It creates a connection between the Income Statement and the Balance Sheet.

What Is a Cash Flow Statement Indirect Method?

The cash flow statement indirect method is created using the increase and decrease of cash in the Balance Sheet and Income Statement. It can show how much and from where cash is spent or earned in a certain period. The Cash Flow Statement Indirect Method is easy to make as the information needed to make it is normally maintained by accounts in a business.

Create a Cash Flow Statement Format with the Indirect Method in Excel: 4 Steps

We have the Income Statement and Balance Sheet of a company. This is the Income Statement of the company.

This is the Balance Sheet of the company.

Step 1 – Adjusting the Net Income for Non-Cash Expenses

• Insert the Net Income value in Cell C8 from the Income Statement sheet using the following formula.

• Add back the Non-Cash Expenses from your dataset. We will add the value of Amortization and Depreciation from the Income Statement sheet by using the following formulas in Cell C10 and Cell C11, respectively.

As the values of Amortization and Depreciation are negative, we added a negative sign to add these values.

• Subtract Gains and add Losses from your dataset.  We will add the value of Loss on Sales of Franchise Rights from the Income Statement sheet by using the following formula in Cell C13.

As the value of Loss on Sales of Franchise Rights is negative, we added a negative sign to add this value.

Read More: How to Prepare Daily Cash Flow Statement Format in Excel

Step 2 – Adjusting Assets and Liabilities for Working Capital Adjustments

• Subtract all the Increases in Current Assets from the Balance Sheet from your dataset. We will subtract the value of the Increase in Account Receivable from the Balance Sheet with the following formula in Cell C15.

We used a negative sign to subtract the increased value of the Account Receivable.

• Add all the decreases in Current Assets from the Balance Sheet from your dataset. We will add the values of decrease in Inventory and Prepaid Insurance from the Balance Sheet by using the following formulas in Cell C17 and Cell C18, respectfully.

We used a negative sign to add the decreased values of the Inventory and Prepaid Insurance.

• Add all the Increases in Current Liabilities from the Balance Sheet from your dataset. We will add the values of Increase in Account Payable and Income Tax Payable from the Balance Sheet. Use the following formulas in Cell C20 and Cell C21.

• Subtract all the decreases in Current Liabilities from the Balance Sheet from your dataset. We will subtract the values of decrease in Wages Payable and Unearned Revenue from the Balance Sheet. We will use the following formulas in Cell C23 and Cell C24.

• Select cell C25 and insert the following formula.

In the SUM function, we added the values of Cell range C8:C24 to get the value of Cash Flow from Operations.

• Press Enter.

Step 3 – Adding or Subtracting Cash from Investing Activities

• Subtract the increase in the Cash used and add the decrease in the Cash got from Investments from the Balance Sheet. We will subtract the increase in the Cash used to Purchase Equipment and add the decrease in the Cash From Sale of Land. We will use the following formulas in Cell F8 and Cell F9, respectively.

• Select Cell F10 and insert the following formula.

In the SUM function, we added the values of Cell range F8:F9 to get the value of Cash Flow from Investments.

• Press Enter.

Read More: Cash Flow Statement Format in Excel for Construction Company

Step 4 – Adding or Subtracting Cash from Financing Activities

• We will add the value of Increase in LT Note Payable, Insurance of Common Stock, and Cash from Paid in Capital from the Balance Sheet. To do this, we will use the following formulas in Cell F13, Cell F14, and Cell F15, respectively.

• Insert the Cash to Pay Dividends value in Cell F16 from the Income Statement sheet with the following formula.

• Select Cell F17 and insert the following formula.

• Hit Enter.

• Select Cell C27 and insert the following formula.

We added the values of Cell C25, Cell F10, and Cell F17 to get the value of Total Change in Cash.

• Press Enter.

• You will get a cash flow statement indirect method format in Excel.

Read More: Create Cash Flow Statement Format Using Direct Method in Excel

Practice Section

We are giving you the dataset to practice on your own and learn to use these methods.

Download the Practice Workbook

Related Articles

• How to Create Cash Flow Statement Format in Excel
• How to Create Monthly Cash Flow Statement Format in Excel
• How to Create Weekly Cash Flow Statement Format in Excel

<< Go Back to Cash Flow Template | Finance Template | Excel Templates