How to Calculate the Operating Cash Flow in Excel

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

The generic formula is:

Operating Cash Flow = Cash Inflows – Cash Outflows

Steps

Create a layout of the Operating Cash Flow Statement (Direct Method) as shown below.
Include Cash Inflows, Cash Outflows, and Net Operating Cash.

Detail Cash Inflows and Outflows.
Enter the amount of cash in Column C.

Select C14 and use the following formula.

=SUM(C6:C12)

The amounts in B6:B12 were added. The amounts in B9:B12 are shown in parentheses (they have a negative value). They are subtracted while using the SUM function.

Method 2 – Applying the Indirect Method to Calculate the Operating Cash Flow in Excel

The generic formula is:

Operating Cash Flow = Net Income + Non-Cash Expenses +/- Changes in Assets and Liability

The required components are:

Net Income: It is taken 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 Account Receivable, Inventory, Accounts Payable, Accrued Expenses, and Deferred Revenue.

The full form of the above formula is:

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

Steps

Create a layout of the Operating Cash Flow Statement (Indirect Method) as shown below.
Include Net Income, Non-Cash Expenses, Change in Working Capital, and Net Operating Cash.

Enter details.
Enter the amount of cash in Column C.

Note: Here, Inventory, Accounts Receivable and Prepaid Expenses are negative. An increase in these elements subtracts them in the formula.

Select C15 and use the following formula.

=SUM(C5:C13)

This is the output.

How to Calculate the Free Cash Flow in Excel

The generic formula to calculate free cash flow is:

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

, which means:

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

Steps

Add rows to the previous worksheet.
Add Capital Expenditure and detail Purchasing New Workstation.
Enter the amounts in Column C.

Select C17 and enter the formula below.

=SUM(C5:C15)

C17 indicates the amount of net free cash.

How to Calculate the Cash Flow Forecast in Excel

The generic formula is:

Cash Flow Forecast = Beginning Cash + Projected Inflows – Projected Outflows

Beginning Cash: The amount of available cash.
Projected Inflows: The amount of money you anticipate within the specified time frame.
Projected Outflows: The costs and extra payments you’ll have to make within the specified time frame.

Steps