Owner Cash Flow After Debt Service for HVAC Companies (Month-by-Month in Excel)

Build the Cash Flow Model in Excel

1. Copy the template layout and set up the month columns

• Start from the prepared template layout (categories on the left, months across the top).
  
• If you are building from scratch, use UNIQUE() on the Category field to pull all categories, then paste values.
  
• Paste the category list into your model area.
  
• Create the month headers: type January, then drag across through December.
  
• Adjust column widths so the table is readable.
  

2. Pull monthly totals for inflows using SUMIFS

• For revenue type rows (example: Customer Collections), use SUMIFS() to sum the Cash In column.
  
• Use three criteria inside SUMIFS():
  
  • Sum range: Cash In
    
  • Criteria 1: Category equals the category label cell on the left
    
  • Criteria 2: Month equals the month header cell at the top
    
• Format as currency and remove decimals.
  

3. Fix the cell locking so you can drag across and down

• Update the formula references so they behave correctly when copied:
  
  • Lock the Cash In or Cash Out ranges fully with F4.
    
  • Lock the Category criteria range fully with F4.
    
  • For the Category label reference, lock the column only (so it stays in column B as you drag across, but changes row as you drag down).
    
  • For the Month header reference, lock the row only (so it stays in the header row as you drag across months, but does not shift when you drag down).
    
• After locking is correct, copy the formula across all months and down all category rows.
  

4. Pull monthly totals for expenses using SUMIFS

• For expense rows, repeat the same SUMIFS() structure but sum the Cash Out column instead of Cash In.
  
• Keep the same criteria:
  
  • Category equals the category label cell
    
  • Month equals the month header cell
    
• Copy across and down to populate the full expense section.
  
• In the sample model, some months may be blank to show it updates as you add data.
  

5. Calculate OPEX as the sum of expense rows

• Create an OPEX row that sums all operating expense lines beneath the inflow section.
  
• Use a simple sum across the expense rows for each month.
  
• Copy across all months.
  

6. Calculate COGS from the vendor payments line

• Use the vendor payments line (labeled as COGS or Vendor Payments) already calculated via SUMIFS().
  
• If needed, copy the same expense SUMIFS() formula into the vendor payments row and keep the label consistent.
  
• Copy across all months.
  

7. Calculate Gross Profit

• For each month:
  
  • Gross Profit equals Customer Collections minus OPEX.
    
• Copy across all months.
  

8. Calculate EBITDA

• For each month:
  
  • EBITDA equals Gross Profit minus COGS.
    
• Copy across all months.
  
• If a month looks unusually high or low, it often indicates missing expense entries in the dataset for that month.
  

9. Pull debt principal and debt interest

• Use the same expense SUMIFS() approach to calculate:
  
  • Debt Principal
    
  • Debt Interest
    
• These rows work the same because they are also cash outflows categorized in the dataset.
  
• Copy across all months.
  

10. Calculate Total Debt Service

• Total Debt Service equals Debt Principal plus Debt Interest.
  
• Copy across all months.
  

11. Calculate Owner Cash Flow After Debt Service

• Create the final row labeled Owner Cash Flow After Debt Service.
  
• For each month:
  
  • Owner Cash Flow After Debt Service equals EBITDA minus Total Debt Service
    
  • Or EBITDA minus Debt Principal minus Debt Interest
    
• Copy across all months to complete the KPI line.
  

12. Create the first chart for cash flow after debt service

• Select the Month header row plus the Owner Cash Flow After Debt Service row.
  
• Insert a chart from Recommended Charts.
  
• Resize and position the chart on the sheet.
  
• Optional: add data labels from the chart elements menu and move individual labels if needed.
  

13. Create the second chart comparing EBITDA, debt service, and cash flow

• Select the Month header row.
  
• While holding Ctrl, also select:
  
  • EBITDA row
    
  • Total Debt Service row
    
  • Owner Cash Flow After Debt Service row
    
• Insert a chart and choose a Combo chart.
  
• Set series types to match the layout you want (often line series for readability).
  
• Optional: apply a dark style and adjust the title to fit on one line.
  
• Optional: add data labels to one series only to reduce noise.