📌 Stage 4 · Aging & Credit Control · Invoicing & AR · The Aging Report · 40–50 min

Buckets that tell you where the collection risk sits

Learning goals

  • Build an aging grid: one row per customer, columns for Not yet due, 0-30, 31-60, 61-90, and 90+ days
  • Write a two-sided SUMIFS that buckets open balances by days overdue
  • Cross-foot the grid's grand total against the register's open receivables

Concepts

Why aging beats a single overdue total

'$22,000.00 is overdue' is true and useless. An aging report splits that number by how late it is, because 5 days late and 95 days late are different species: the first needs a polite email, the second needs a decision. Banks, factors, and any buyer of your receivables will ask for exactly this grid.

The standard US buckets are 0-30 / 31-60 / 61-90 / 90+ days past due. This workbook adds one column on the left - Not yet due - so the grid accounts for every open dollar, not just the late ones. Nine open invoices spread across the six customers at the September close.

💡 Tips:

  • Some industries age by invoice date instead of due date; pick one convention and label the header - this workbook ages by days past due.
  • The 90+ column is where bad-debt risk lives; every dollar in it deserves a named owner and a next step.

The two-sided SUMIFS

Each grid cell asks one question: of this customer's open balance, how much is between two fences? SUMIFS takes any number of criteria pairs, and nothing stops both pairs from pointing at the same column - one fence at the bottom, one at the top.

The customer key comes from column B of the grid row, and the fences are text versions of numeric comparisons built from the Days Overdue column you built in Lesson 9.

=SUMIFS(Invoices!$K$5:$K$1004,Invoices!$D$5:$D$1004,$B5,Invoices!$M$5:$M$1004,">30",Invoices!$M$5:$M$1004,"<=60")

Sum the open balances (Invoices column K) where the customer code (column D) equals this row's customer ($B5) AND days overdue (column M) is greater than 30 AND less than or equal to 60. Two criteria on the same column M fence the 31-60 bucket on both sides. For C01 this returns 3,500.00 - INV-1101, 48 days late.

=SUMIFS(Invoices!$K$5:$K$1004,Invoices!$D$5:$D$1004,$B5,Invoices!$M$5:$M$1004,"<0")

The Not yet due column needs only one fence: days overdue below zero. Paid rows never interfere because Lesson 9 made them empty text, and text never matches a numeric criterion.

Reading the grid

The September grid tells a clear story. Not yet due holds $14,600.00 across three invoices - healthy forward work. The 0-30 bucket holds $10,700.00 - the email-and-phone zone. The 31-60 bucket holds exactly one invoice: Summit Ridge Dental's $3,500.00 at 48 days, a firm-notice case. The 61-90 bucket holds Pinewood Legal's $3,000.00 at 70 days. The 90+ bucket holds Marigold Interiors' $4,800.00 at 97 days - the escalation.

By customer, exposure concentrates: Kestrel owes $13,000.00 (mostly not yet due), Marigold $8,000.00 (all of it late), Pinewood $8,800.00. The TOTAL row cross-foots to $36,600.00 - which must equal the register's open receivables. When it does, the aging is provably complete; when it does not, an invoice row has lost its customer code or a day count has gone text.

💡 Tips:

  • Make the cross-foot a habit: grand total 36,600 must equal Collections' open receivables. It is the cheapest integrity check in the workbook.
  • Read columns before rows: a tall 90+ column is a process failure; a fat 0-30 column is merely a busy month.

Practice

Open on Aging. Cell F5 (the 31-60 bucket for Summit Ridge Dental) is blank - type the two-sided SUMIFS, then read the grid like a lender.

  1. Type the bucket formula — Cell F5 is blank. Type =SUMIFS(Invoices!$K$5:$K$1004,Invoices!$D$5:$D$1004,$B5,Invoices!$M$5:$M$1004,">30",Invoices!$M$5:$M$1004,"<=60") and press Enter. F5 returns 3,500.00 - INV-1101, 48 days past due.
  2. Compare the fence patterns — Read E5 (0-30: two fences, >=0 and <=30), D5 (Not yet due: one fence, <0), and H5 (90+: one fence, >90). Same skeleton, different fences - five formulas, one idea.
  3. Read the TOTAL row — Row 11: 14,600 / 10,700 / 3,500 / 3,000 / 4,800, grand total 36,600.00. Cross-foot it mentally: not-yet-due plus all four overdue buckets equals every open dollar in the register.
  4. Find the concentrations — Marigold Interiors (row 9): 3,200 in 0-30 and 4,800 in 90+ - late everywhere. Kestrel (row 7): 7,400 not yet due and 5,600 in 0-30. One customer is a crisis; the other is a calendar.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

  • ☐ Typed the two-sided SUMIFS into F5 and got 3,500.00 for Summit Ridge Dental's 31-60 bucket
  • ☐ Verified the TOTAL row reads 14,600 / 10,700 / 3,500 / 3,000 / 4,800 across the five buckets
  • ☐ Cross-footed the grand total 36,600.00 against the register's open receivables - they match
  • ☐ Noted the baseline: overdue buckets sum to 22,000.00, with 4,800.00 sitting in 90+ for Marigold Interiors