How to Create Cash Flow Statement Format in Excel

If you searching for a ready-to-make cash flow statement format, then you have definitely landed in the right place. Excel allows you to create cash flow statements on the basis of some gathered data. In this article, I will show you how to create a cash flow statement format in Excel.


Download Practice Workbook

You can download the practice book from the button below.


About Cash Flow Statement in Excel

A cash flow statement is actually a financial statement that describes the inflows and outflows of cash over a certain period of time for a specific organization or company. It usually shows how any change in the balance sheet affects the cash and cash equivalents. All the inflows a company receives while operating are described in that statement and all the corresponding expenses are also considered throughout the certain time period. The statement provides all the data to visualize the overall profit or loss a company is making. It is remarkable that the net amount of cash at the end of the concerning time is the beginning balance for the next time period. So, from the statement company gets the idea of what approach should be made to overcome the loss and what change should be made to increase the cash every time it runs the operation.


Steps to Create Cash Flow Statement Format in Excel

In this section, you will find an easy way to create a cash flow statement format in Excel. Let’s check it now!

Let’s say we have a dataset of the particulars for creating a company’s cash flow statement.

Dataset for Creating Cash Flow Statement

Here, want to create a cash flow statement for these particulars for the month of January. Now, to proceed with the cash flow statement, follow the steps below.


Step 1: Assign Number Format

  • First of all, assign the number format for your cash flow statement. Here, for our dataset, I have used the Accounting format. For formatting the number, just select the cells, go to the Home tab> select Accounting from the Number Format.

Create Cash Flow Statement Format in Excel

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


Similar Readings


Step 2: Enter Cash Amount & Calculate Net Change in Cash Flow

  • Here, enter the beginning balance for the first month.
  • Now, assign the corresponding amount of operating activities for your business. The particulars we have used for operating activities are:
  • Cash Receipts from Customers
  • Depreciation & Amortization
  • Account Receivables
  • Income Taxes

=SUM(C8:C11)

Here,

  • C8= Starting cell of operating activities
  • C11= Ending cell of operating activities

Create Cash Flow Statement Format in Excel

  • Then, hit ENTER and your cell will show the net amount of operating activities.

Create Cash Flow Statement Format in Excel

  • Now, enter the particulars for Investing Activities. Here, the particulars we have used are:
  • Capital Expenditures
  • Others
  • After that, enter the following formula to get the net amount of Investing Activities:

=SUM(C15:C16)

Here,

  • C15= Starting cell of Investing activities
  • C16= Ending cell of Investing activities

Create Cash Flow Statement Format in Excel

  • However, hit ENTER to get the result.

  • After that, assign variables for Financing Activities like:
  • Net Borrowings
  • Dividends Paid
  • Common Stock
  • Now, apply the formula below to get the net amount of financing:

=SUM(C20:C22)

Here,

  • C20= Starting cell of financing activities
  • C22= Ending cell of financing activities

  • After that, assign the following formula to calculate the net increase or decrease in the cash flow:

=SUM(C12,C17,C23)

Here,

  • C12= Net Operating activities
  • C17= Net Investing activities
  • C23= Net Financing activities

  • Hence, press ENTER to allow the cell to show the net change.

Read More: How to Calculate Operating Cash Flow in Excel (2 Easy Ways)


Step 3: Calculate Cash Flow at End Period

Now, it’s time to get the cash flow at the end of the month considering the total benefit or loss. However, you will know the balance at the end period of your concerning time.

  • Now, to get the total cash at the end of the month, apply the following formula to the concerning cell:

=SUM(C5,C24)

Here,

  • C5= Beginning Balance
  • C24= Net Increase/decrease in Cash Flow

  • After that, hit ENTER, and the cell will return the net balance for the concerning month (i.e. January).

  • Here, keep in mind that, the cash at the end of a month will be the beginning balance for the next month.

  • However, repeat the same steps to get the cash flow statement for the next months. You don’t need to apply the formula every time. Just use the Fill Handle tool to Autofill the formula every time.

  • Hence, your cash flow will let you know the cash at the end of a certain period you are concerned about.

Create Cash Flow Statement Format in Excel

By following the above steps, you can easily create a cash flow statement format in an Excel workbook. For better visualization, we have made a cash flow statement for 3 consecutive months. You can approach with as many months as you need. Just follow the same procedure and your statement will be ready.

Read More: How to Create Monthly Cash Flow Statement Format in Excel


Conclusion

In this article, you have learned how to form a cash flow statement format in Excel. I hope from now on you can quickly create a cash flow statement in an Excel workbook. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy. Have a great day!


Related Articles

Rafi

Rafi

Hey there! I am Md. Rafiul Hasan. Currently I am working as an Excel & VBA content developer. I like new ideas and want to explore the field of innovation. Excel has saved our worktime and made it easy for us to quick calculations. I am trying to make it easier for you to overcome the obstacles you face while working on Excel. Stay connected!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo