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.

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.

Create Cash Flow Statement Format in Excel: 3 Steps

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.

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.

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

After that, apply the SUM function to calculate the net cash flow from the operating activities. For this, insert a new row and apply the formula below to get the net amount of operating cost:

=SUM(C8:C11)

Here,

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

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

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

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.

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.

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.

Download Practice Workbook

You can download the practice book from the button below.

Cash Flow Statement Format.xlsx

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.