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

Need to learn how to calculate operating cash flow in Excel? If you are looking for such unique tricks, you’ve come to the right place. Here, we will take you through 2 easy and convenient methods of calculating operating cash flow in Excel.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


What Is Operating Cash Flow in Excel?

Operating cash flow is the chunk of the money that a business earns through its routine business operations over a specific time frame. The operating cash flow of a business reveals whether it can consistently produce enough money to maintain and expand its operations.

Other features of operating cash flows are:

  • The operating cash flow represents the first section of the cash flow statement of a certain organization.
  • The operating cash flow provides a precise illustration of the current situation of the company.
  • We often refer to operating cash flow as “cash flow from operating activities”.

2 Methods to Calculate Operating Cash Flow in Excel

There are two formulas to calculate operating Cash Flow. They are given below.

  • Direct method
  • Indirect method

Now, we’ll discuss these two methods. Also, we’ll give a clear picture of calculating operating cash flow with these two methods in Excel. So, let’s be with us.


1. Using Direct Method to Calculate Operating Cash Flow in Excel

This process is incredibly straightforward and precise. With this technique, a business keeps track of each financial inflow and outflow over a predetermined time frame.
The generic formula for that approach is as follows:

Operating Cash Flow = Cash Inflows – Cash Outflows
Here, Cash Inflows involve the cash revenues earned in numerous ways by a company. On other hand, Cash Outflows include all types of operating expenses paid in cash.

Now, we’ll calculate operating cash flow using the direct method in Excel. So, without further delay, let’s jump into the method step-by-step.

📌 Steps

  • First of all, create a layout of the Operating Cash Flow Statement (Direct Method) as in the image below.
  • It should include the major particulars like Cash Inflows, Cash Outflows, and Net Operating Cash.

How to Calculate Operating Cash Flow in Excel

  • Secondly, write down the components under their corresponding major group.
  • Also, give the amount of cash in Column C.

How to Calculate Operating Cash Flow in Excel

  • At this moment, select cell C14 and write down the following formula.
=SUM(C6:C12)

Here, we added up all the amounts in the B6:B12 range. The amounts in the B9:B12 range are shown in parentheses. Because they are negative in value. As a result, they got subtracted while using the SUM function.

How to Calculate Operating Cash Flow in Excel

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


2. Utilizing Indirect Method to Calculate Operating Cash Flow in Excel

Companies utilize the indirect approach to calculate operating cash flow significantly more often than they do the direct method. They do this because they can simply calculate operating cash flow from the financial statement that is already available.
The generic formula for that approach is as follows:

Operating Cash Flow = Net Income + Non-Cash Expenses +/- Changes in Assets and Liability
So, the main components required in this formula are:
Net Income: We take net income into account as a starting point.
Non-Cash Expenses: It includes Depreciation, Amortization, Stock-Based Compensation, Deferred Income Tax, and other non-cash items.
Assets and Liability: It contains the elements like Account Receivable, Inventory, Accounts Payable, Accrued Expenses, and Deferred Revenue.
As a result, the full form of the above formula looks like the one below:
Operating Cash Flow = Net Income + Depreciation + Amortization + Stock-Based Compensation + Deferred Income Tax + Other non-cash items – Increase in Account Receivable – Increase in Inventory + Increase in Accounts Payable + Increase in Accrued Expenses + Increase in Deferred Revenue

Now, we’ll calculate operating cash flow using the indirect method in Excel. So, follow the steps below.

📌 Steps

  • First, create a layout of the Operating Cash Flow Statement (Indirect Method) as in the image below.
  • It should include the major particulars like Net Income, Non-Cash Expenses, Change in Working Capital, and Net Operating Cash.

How to Calculate Operating Cash Flow in Excel

  • Secondly, write down the components under their corresponding major group.
  • Also, give the amount of cash in Column C.

How to Calculate Operating Cash Flow in Excel

Note: Here, Inventory, Accounts Receivable and Prepaid Expenses are given in negative. Because the increase in these elements makes subtract them in the formula.

  • At this moment, select cell C15 and write down the following formula.
=SUM(C5:C13)

How to Calculate Operating Cash Flow in Excel

This is the amount of net operating cash in this example.

Read More: Create Cash Flow Statement Format with Indirect Method in Excel


Similar Readings


How to Calculate Free Cash Flow in Excel

The free cash flow calculates the available amount of cash in hand. The calculated amount can be used at this moment. So, it’s very much helpful in budgeting and planning.

The generic formula to calculate free cash flow is given below:

Free Cash Flow = Net Income + Non-Cash Expenses +/- Changes in Assets and Liability – Capital Expenditure

That means,

Free Cash Flow = Operating Cash Flow (Indirect Method) – Capital Expenditure

So, now using the above formula, we’ll calculate the free cash flow in Excel. Let’s follow us.

📌 Steps

  • Firstly, take the previous worksheet and add some new rows.
  • Then, add a major particular Capital Expenditure.
  • Later, under this group, make an entry of Purchasing New Workstation.
  • Afterward, give the corresponding amount in Column C.

How to Calculate Operating Cash Flow in Excel

  • Later, select cell C17 and paste the formula below.
=SUM(C5:C15)

How to Calculate Operating Cash Flow in Excel

Thus, cell C17 indicates the amount of net free cash in this example.

Read More: How to Apply Discounted Cash Flow Formula in Excel


How to Calculate Cash Flow Forecast in Excel

The cash flow forecast is a good practice to plan for the future. It forecast the amount of cash that someone will have in hand in the future.
The generic formula to calculate cash flow forecast looks like the below:

Cash Flow Forecast = Beginning Cash + Projected Inflows – Projected Outflows
Beginning Cash: It’s the amount of cash on hand today.
Projected Inflows: It is the amount of money you anticipate getting during the allotted time.
Projected Outflows: These are the costs and extra payments you’ll have to make within the specified time frame.

So, now using the above formula, we’ll calculate the cash flow forecast in Excel. Let’s follow us.

📌 Steps

  • Initially, create a layout of the Cash Flow Forecast as in the image below.
  • It should include the major particulars like Beginning Cash, Cash Inflows, and Cash Outflows.

Forecasting Available Funds in Future

  • Secondly, write down the components under their corresponding major group.
  • Also, give the amount of cash in Column C.

Forecasting Available Funds in Future

  • At this moment, select cell C15 and write down the following formula.
=SUM(C5:C13)

Forecasting Available Funds in Future

Thus, cell C15 indicates the amount of net forecasted cash in this example.

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


Practice Section

For doing practice by yourself we have provided a Practice section like below in each sheet on the right side. Please do it by yourself.

Practice Section


Conclusion

This article provides easy and brief solutions to calculate operating cash flow in Excel. Don’t forget to download the Practice file. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website Exceldemy to explore more.


Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo