How to create my company's cash flow in Excel?

A cash flow spreadsheet in Excel is an essential document that any company must manage. 

Its importance lies in the fact that it allows us to know the liquidity that the organization has at a given moment and this is vital to comply with obligations such as the payment of salaries or suppliers, in the short term.

What is cash flow?

We can define cash flow as the figure that results from the relationship between the income and expenses of the company and that determines the net balance it has. In that sense, we understand income as the money that comes in as a result of sales or services provided. For their part, expenses represent the outflows of money generated by the fulfillment of obligations in a short period of time.

In this way, the additions and subtractions that are generated result in the current balance that the organization has.

What is cash flow for?

We can see this clearly if we take it to the personal level. Our cash flow is the figure we see on the balance of our bank account and this number is the product of a series of inflows and outflows of money. So, when we go somewhere, we most likely review this balance in order to determine the commitments we can afford.

The same thing happens in companies, although on a much larger scale. There are various bank accounts and a whole number of payments to be made in different terms. This implies the need to know the state of cash flow in order to evaluate the range of financial maneuver that the company has. 

Thus, from a cash flow in Excel you will see the current liquidity of the business and will allow administrators to ensure the payment of salaries, suppliers, taxes and other obligations. From this, it is also possible to establish investment or retrenchment strategies in order to improve cash availability.

Types of cash flows

We know that cash flow shows the balance of the company in a certain period of time. However, there are some variants of this document that allow us to make more detailed analyzes of the liquidity handled.

Operational flows

With an Operating Cash Flow in Excel you will obtain the balance resulting from the income and expenditure of money that are solely related to the core activity of the company. That is, if you sell cell phones, this type of cash flow will give you the possibility of filtering movements based on sales transactions and payments aimed at restocking stock, for example.

Investment flows

This type of cash flow provides information on the inflows and outflows of money for investments aimed at the growth of the company. Continuing with the previous example, this can include purchases of display cases, shelves and other furniture necessary in a store.

Financial flows

It is also possible to create a financial cash flow in Excel and this will give you the possibility of knowing the movements of financial investments oriented to business activity. In that sense, the loans contracted or the money that comes in through shares, enter into this document.

How to make a cash flow in Excel?

If you are looking for how to create a cash flow in Excel, you should know that it is a fairly simple process. However, from the technical aspect, we must take into account that there are two accounting methods to do so: direct and indirect.

Likewise, if you need to know more about using Excel to create your own cash flows, you can use our Basic Excel Course.

Calculate Cash Flow by Direct Method

Calculating cash flow with the direct method implies that the movements will be distributed similar to a bank statement. In this sense, collection and payment activities are divided into categories where the inflows and outflows of money must be added. In this way, it will be necessary to manage the data of: receipts, payments, expenses, interests and taxes.

So, we can define the formula for calculating cash flow with the direct method in this way: 

Receipts – Payments – Expenses – Interest – Taxes = Cash Flow.

Calculate Cash Flow by Indirect Method

The indirect method, for its part, takes into account other accounting elements to determine cash flow. In that sense, this technique takes utility as its main data and to do so, we must resort to accounting books. Thus, knowing the net profits and the variations in the balance sheet, we can obtain the cash flow.

The formula for calculation with the indirect method is: 

Net income + Gains and Losses from Investments and Financing + Non-cash charges + Changes in operating accounts 

Advantages of making a Cash Flow spreadsheet in Excel 

• Easy to do.
• You can use templates for the design of the document.
• Allows you to know the current balance of the company.
• It provides a starting point to propose investment strategies.
• It allows knowing the company's limits to meet financial obligations.
• The different types of cash flow invite more detailed analysis of the liquidity statement.
• It is possible to take advantage of the different cash flow models to better study the company's cash management.

Related questions