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.
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”.
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:
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.
- Secondly, write down the components under their corresponding major group.
- Also, give the amount of cash in Column C.
- 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.
Read More: How to Calculate Payback Period in Excel (With Easy Steps)
2. Applying 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:
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.
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.
- Secondly, write down the components under their corresponding major group.
- Also, give the amount of cash in Column C.
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)
This is the amount of net operating cash in this example.
Read More: How to Calculate Payback Period with Uneven Cash Flows
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:
That means,
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.
- Later, select cell C17 and paste the formula below.
=SUM(C5:C15)
Thus, cell C17 indicates the amount of net free cash in this example.
Read More: Calculating Payback Period in Excel with Uneven Cash Flows
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:
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.
- Secondly, write down the components under their corresponding major group.
- Also, give the amount of cash in column C.
- At this moment, select cell C15 and write down the following formula.
=SUM(C5:C13)
Thus, cell C15 indicates the amount of net forecasted cash in this example.
Read More: How to Calculate Discounted Payback Period in Excel
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice yourself.
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.
