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

Get FREE Advanced Excel Exercises with Solutions!

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 DateOpening Balance, Net Cash Flow, and Closing Balance.
  • Now, input the 3 main components of the Cash Flow Statement. The components are specified as:
  • Cash Flow Operations
  • Investment Activity
  • Financial Activity

The image below describes the layout in short.

Creating Layout to Tack Cash Flow in Excel

  • 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:
  • Sales
  • Account Receivable
  • Inventory
  • Administrative Expenses
  • Salary & Wages
  • Income Tax Paid
  • Depreciation
  • Incomplete Contract
  • Other Incomes

Input Factors to Track Cash Flow in Excel

  • 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.

Input Amount to Track Cash Flow of Operations

📃Note: The red marked section is formatted to Accounting. In this format, the Negative Number is shown as enclosed with parenthesis.
  • 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.
=SUM(C9:C17)

Here,

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

Total Amount of Cash Flow Operations to Tack Cash Flow in Excel

  • 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:

  • Properties Sold
  • Principal of Loans
  • Investment Securities Sold
  • Equipment Purchased
  • Loans Given
  • Purchased Services
  • 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
=SUM(F9:F14)

Here,

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

Total Investment Activity to Track Cash Flow in Excel

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


Similar Readings


⏩ 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.
=SUM(I9:I13)

Here,

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

Total Financial Activity to Track Cash Flow in Excel

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.
=C18+F15+I14

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.

Net Flow to Track Cash Flow in Excel

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

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.

Closing Balance to Track Cash Flow

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

Rafiul Hasan
Rafiul Hasan

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 work time and made it easy for us to perform 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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo