How to Track Cash Flow in Excel (Step-by-Step Guideline)

Cash Flow is one of the basic fundamentals of financial statements. It is a frequently used tool to to insight into the performance of a company. If you work for a company, then you must need to track the cash flow. And no need to mention, you will try to make it in Excel. Don’t worry, there are ways for this. In this article, I will show you how to track cash flow in Excel.

Download Practice Workbook

You can download the practice book from the link below.

What Is Cash Flow?

Cash flow is one of the fundamental hacks to unlock the accounts and audits of a company or business organization. It is one type of financial statement that is used to describe the amount of inflows and outflows of balance over a certain period of time, the opening and the closing balance after that specified period of a company or business organization. 

The cash flow statement is so important for a company because it provides a clear idea about the loss and profits, in general, the net balance of an organization. A proper evaluation of this statement helps to insight into the overall condition of the company or organization, in which sector it is making a profit, and in which sector more priority should be given. Actually, this is a must needed key to unlock the doors of the ultimate success of the company and helps to achieve the vision and goal of that organization.

Stepwise Guideline to Track Cash Flow in Excel

Let’s say, we are dealing with XYZ Company Limited over a certain period of time. The company has started its journey with an opening balance of $750000. After the time period, we will calculate the cash flow on 01-09-22. At the concerned time, we will show the closing balance.

This section will find some simple and useful steps to track cash flow in Excel. I will demonstrate them one by one here. Let’s check them now!

⏩ Step 1: Creating Layout

First of all, you will need to create a layout for tracking the Cash Flow. In this layout, the factor and values will be input.

• Let’s assign some cells for allocating Date, Opening Balance, Net Cash Flow, and Closing Balance.
• Now, input the 3 main components of the Cash Flow Statement. The components are specified as:

The image below describes the layout in short.

• Then, input the Opening Balance of the company you are dealing with. Also, assign the Date on which you will be calculating the total cash flow.

Read More: How to Calculate Cash Flow in Excel (7 Suitable Examples)

⏩ Step 2: Calculating Net Cash Flow of Operations

After creating the primary layout of Cash Flow, you will need to assign factors to the input components. Let’s deal with Cash Flow Operations first. Your company may receive cash from different resources or also spend cash on different issues. 

• The factors encountered in the Cash Flow Operations here have been used are:

• Now, input the amount for each factor of Cash Flow Operations. Here, one thing is noticeable is that the amount that is paid will be input as a Negative Number.

• Now, calculate the total amount of Cash Flow Operations. Here, the SUM function is used to serve this purpose. Select a cell where you want to calculate the net amount and type the following formula in that cell in Excel.

Here,

• C9 = the first factor (Sales) of Cash Flow Operations
• C17 = the last factor (Other Incomes) of Cash Flow Operations

• After that, press ENTER, and the cell will show you the total amount of Cash Flow Operations.

Read More: How to Calculate Net Cash Flow in Excel (3 Suitable Examples)

⏩ Step 3: Determining Net Cash Flow of Investment Activity

Another component of Cash Flow is Investment Activity. The factors assigned here for this component are:

• After assigning the amounts, follow the same procedure as the previous step. Apply the following formula to calculate the net amount of cash flow for Investment Activity

Here,

• F9 = the first factor (Properties Sold) of Investment Activity
• F14 = the last factor (Purchased Services) of Investment Activity

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

Similar Readings

• How to Create a Real Estate Cash Flow Model in Excel
• Make a Restaurant Cash Flow Statement in Excel
• How to Create Cash Flow Projection for 12 Months in Excel
• Calculate Present Value of Future Cash Flows in Excel
• How to Calculate IRR in Excel for Monthly Cash Flow (4 Ways)

⏩ Step 4: Finding Out Net Cash Flow of Financial Activity

Just like the previous ones, now find out the net cash flow of Financial Activity.

• Here, apply the following formula in a selected cell to calculate the net amount of Financial Activity.

Here,

• I9 = the first factor (Stock Issuance) of Investment Activity
• I13 = the last factor (Dividends from Security) of Investment Activity

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

⏩ Step 5: Calculating Overall Net Cash Flow and Closing Balance

Here, we are in the last part of tracking the Cash Flow. You just need to determine the overall Net Cash Flow and the Closing Balance.

• In order to calculate the overall Net Cash Flow, just type the following formula in a selected cell just like in the image below.

Here,

• C18 = Net cash flow of Operations
• F15 = Net cash flow of Investment Activity
• I14 = Net cash flow of Financial Activity

• Now, press ENTER and the cell will calculate the Net Cash Flow on the basis of input parameters.

• After that, in order to get the closing balance, just add the Net Cash Flow to the Opening Balance.

Here,

• C6 = Net Cash Flow
• F15 = Opening Balance

• After that, press ENTER and you will get the amount of Closing Balance at the end of the time period.

So, these are the basic steps you should follow for tracking the Cash Flow.

Read More: How to Calculate Annual Cash Flow in Excel (with Easy Steps)

Conclusion

In this article, I have tried to show you some methods to track cash flow in Excel. I hope this article has shed some light on your way to this. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box. For more queries, kindly visit our website ExcelDemy. Have a great day!

Related Articles

• How to Create Weekly Cash Flow Statement Format in Excel
• How to Forecast Cash Flow in Excel (with Detailed Steps)
• Calculating Payback Period in Excel with Uneven Cash Flows
• How to Calculate Discounted Payback Period in Excel
• Calculate Discounted Cash Flow in Excel (with Easy Steps)
• How to Draw a Cash Flow Diagram in Excel (with Easy Steps)