Create Cash Flow Statement Format with Indirect Method in Excel

Looking for ways to know how to create a cash flow statement indirect method format in Excel? We can create a cash flow statement in both direct and indirect method format in Excel. Here, you will find step-by-step explained ways to create a cash flow statement indirect method format in Excel.

What Is Cash Flow Statement?

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. That’s how it creates a connection between the Income Statement and Balance Sheet.

What Is 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. 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 Cash Flow Statement Format with Indirect Method in Excel: 4 Steps

We have the Income Statement and Balance Sheet of a company. If you want you can download the Balance Sheet Temple from Microsoft Excel.

This is the Income Statement of the company.

Additionally, this is the Balance Sheet of the company.

Now, we will show you how to create a cash flow statement indirect method format in Excel using the information provided in these sheets. Go through the steps given below to do it on your own.

Step-01: Adjusting Net Income for Non-Cash Expenses

In the first step, we will adjust the Net Income for all Non-Cash Expenses provided in the Income Statement sheet.

• Firstly, insert the Net Income value in Cell C8 from the Income Statement sheet using the following formula.

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

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

• After that, subtract Gains and add Losses from your dataset.  Here, we will add the value of Loss on Sales of Franchise Rights from the Income Statement sheet using the following formula in Cell C13.

Here, 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-02: Adjusting Assets and Liabilities for Working Capital Adjustments

Now, we will adjust Assets and Liabilities from the Balance Sheet for working capital adjustments. Follow the steps given below to do it on your own dataset.

• In the beginning, subtract all the Increases in Current Assets from the Balance Sheet from your dataset. Here, we will subtract the value of the Increase in Account Receivable from the Balance Sheet using the following formula in Cell C15.

In the formula, we used a negative sign to subtract the increased value of the Account Receivable.

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

Here, we used a negative sign to add the decreased values of the Inventory and Prepaid Insurance.

• Afterward, add all the Increases in Current Liabilities from the Balance Sheet from your dataset. Here, we will add the values of Increase in Account Payable and Income Tax Payable from the Balance Sheet. To do this, we will use the following formulas in Cell C20 and Cell C21.

• Then, subtract all the decreases in Current Liabilities from the Balance Sheet from your dataset. Now, 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.

• Next, select Cell C25.
• After that, insert the following formula.

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

• Then, press ENTER.
• Finally, you will get the Cash Flow from Operations.

Step-03: Adding or Subtracting Cash from Investing Activities to Create Cash Flow Statement with Indirect Method

In the third step, we will add or subtract Cash from Investing Activities from the Balance Sheet. Go through the steps to do it on your own dataset.

• Firstly, subtract the increase in the Cash used and add the decrease in the Cash got from Investments from the Balance Sheet. Here, we will subtract the increase in the Cash used to Purchase Equipments and add the decrease in the Cash From Sale of Land. To do this, we will use the following formulas in Cell F8 and Cell F9 respectively.

• Then, select Cell F10.
• After that, 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.

• Then, press ENTER.
• Finally, you will get the Cash Flow from Investments.

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

Step-04: Adding or Subtracting Cash from Financing Activities

Now, in the final step, we will add or subtract Cash from Financing Activities from the Income Statement and Balance Sheet. Follow the steps given below to do it on your own dataset.

• In the beginning, add all the Increases in Note Payable and Shareholder’s Equity or subtract all the decreases in Note Payable and Shareholder’s Equity from the Balance Sheet from your dataset. Now, 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.

• Then, insert the Cash to Pay Dividends value in Cell F16 from the Income Statement sheet using the following formula.

• Then, select Cell F17.
• After that, insert the following formula.

In the SUM function, we added the values of Cell range F13:F16 to get the value of Cash Flow from Financing.

• Then, press ENTER.
• Next, you will get the Cash Flow from Financing.

• Then, select Cell C27.
• After that, insert the following formula.

In the SUM function, we added the values of Cell C25, Cell F10, and Cell F17 to get the value of Total Change in Cash.

• Now, press ENTER.
• Next, you will get the Total Change in Cash.

• Finally, 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

In this section, we are giving you the dataset to practice on your own and learn to use these methods.

Download Practice Workbook

Conclusion

So, in this article, you will find a step-by-step way to create a cash flow statement indirect method format in Excel. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here.

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