How to Track Net Operating Cash Flow
Month by Month in Excel

Learn how to clearly track your cash inflows, cash outflows, and net operating cash flow month by month using Excel. In this lesson, you’ll see how to spot cash shortages early, understand why a business can look profitable but still struggle with cash, and visualize cash performance with two simple charts.

Download the Excel file used in this tutorial:

Step By Step Instructions

How to Calculate Net Operating Cash Flow in Excel

1. Prepare the Month Column

Create a column labeled Month
Type January in the first cell
Drag the fill handle down until December appears
This establishes the time structure for all calculations

2. Set Up Cash In and Cash Out Columns

Create two columns:
- Operating Cash In
- Operating Cash Out
These will summarize inflows and outflows by month

3. Calculate Operating Cash In Using SUMIF

Use the SUMIF() function
Sum the Cash Amount column
Set the condition where Direction = “Cash In”
Add a second condition where Month = January
Use quotation marks for text values
Format results as currency using Ctrl + Shift + 4
Double-click the fill handle to copy the formula through December

4. Calculate Operating Cash Out

Copy the Cash In formula
Change the condition from “Cash In” to “Cash Out”
Keep all other references the same
Format as currency
Double-click to fill down for all months

5. Calculate Net Operating Cash Flow

Create a column labeled Net Operating Cash Flow
Subtract Operating Cash Out from Operating Cash In
Center the values
Drag the formula down to calculate each month
Negative values immediately highlight cash-constrained months

6. Create the Net Operating Cash Flow Chart

Highlight Month
Hold Ctrl and highlight Net Operating Cash Flow
Press Alt + F1 to instantly create a column chart
Alternatively:
- Go to Insert
- Choose Column Chart or Recommended Charts
Apply formatting (color theme, labels) as needed

7. Create the Cash In vs Cash Out Chart

Highlight Month, Operating Cash In, and Operating Cash Out
Go to Insert
Select a column or line chart
Rename the chart to Cash Inflows vs Cash Outflows
This visual clearly shows liquidity pressure over time

8. Final Review and Interpretation

Review months with negative net values
Compare inflow vs outflow trends visually
Use both charts together for operational cash insight

FAQ

Net Operating Cash Flow Analysis in Excel

Q1. What is net operating cash flow?
Net operating cash flow shows how much cash a business generates from its core operations after accounting for cash coming in and cash going out. It’s a critical KPI for understanding whether a company can pay its bills and sustain day-to-day operations.

Q2. Why is net operating cash flow important for HVAC and service businesses?
Many HVAC and service companies appear profitable on paper but still experience cash shortages. Tracking net operating cash flow helps you identify months where expenses outpace cash inflows and avoid surprises that impact payroll, vendors, or growth plans.

Q3. How does this video help me understand my cash position?
This tutorial walks through a simple month-by-month view of cash inflows, cash outflows, and the resulting net cash position. You’ll learn how to visualize this data so trends, problem months, and improvements are easy to see at a glance.

Q4. What charts are best for analyzing cash flow trends?
A column or line chart works well for tracking net operating cash flow over time, while a second chart comparing cash inflows versus cash outflows helps explain why certain months perform better or worse than others.

Q5. Can I use this approach with QuickBooks or accounting exports?
Yes. This method works well with exported data from accounting systems like QuickBooks, but it can also be used with a simple dataset that includes dates, cash direction (in or out), and dollar amounts.

Q6. Where can I get the sample file used in this lesson?
The sample Excel file is linked in the video description. You can download it to follow along step by step and recreate the same cash flow visuals shown in the tutorial.

Get more lessons like this
by joining our newsletter

Receive weekly Excel tutorials, tips, and KPIs straight to your inbox

Databoards

Analysis & Development

How to Track Net Operating Cash Flow Month by Month in Excel