📌 Stage 4 · Aging & Credit Control · Invoicing & AR · Credit Holds · 35–45 min

Deciding who gets new work on credit

Learning goals

  • Compute each customer's open and overdue exposure with SUMIFS
  • Apply the two-part hold rule: over the credit limit, or any invoice 60+ days late
  • Count the customers on hold and connect the flag to an operational decision

Concepts

Exposure per customer

The Customers sheet gains its computed half. Open Balance answers 'how much does this customer owe me right now?' - a plain SUMIFS over the register keyed on the customer code. Overdue Balance narrows it to the part past due, by adding a second criterion on the register's Status column.

These two numbers turn a contact list into a credit report. Kestrel's $13,000.00 open is large but calm ($5,600.00 of it overdue); Marigold's $8,000.00 is smaller and entirely late. Size and risk are different axes, and only computed columns keep them separate.

=SUMIFS(Invoices!$K$5:$K$1004,Invoices!$D$5:$D$1004,$B5)

Sum open balances (column K) where the register's customer code (column D) equals this row's code. C01 returns 3,500.00; C03 returns 13,000.00.

=SUMIFS(Invoices!$K$5:$K$1004,Invoices!$D$5:$D$1004,$B5,Invoices!$L$5:$L$1004,"Overdue")

Same sum, one more fence: only rows whose Status (column L) is exactly Overdue. The text criterion "Overdue" must match the ladder's wording character for character.

The hold rule

Cedar & Co.'s policy, stated once in the Read Me: a customer goes on credit hold when its open balance exceeds its credit limit, or any of its invoices is more than 60 days past due. The first half protects capacity - no client silently becomes the agency's biggest lender. The second half enforces responsiveness without being twitchy: 60 days allows for a missed check run, a disputed line, a vacation.

The flag writes itself as an OR of the two tests, with the 60-day test expressed as a SUMIFS over the Days Overdue column being greater than zero dollars.

=IF(OR($H5>$F5,SUMIFS(Invoices!$K$5:$K$1004,Invoices!$D$5:$D$1004,$B5,Invoices!$M$5:$M$1004,">60")>0),"HOLD","OK")

$H5>$F5 tests open balance against the credit limit. The SUMIFS sums open balances for this customer whose days overdue (column M) exceed 60; if that sum is positive, an old invoice exists. OR either condition and the flag reads HOLD, otherwise OK.

What HOLD means on Monday morning

At the September close, two flags read HOLD. Pinewood Legal Group LLC ($8,800.00 open, under its $15,000.00 limit) trips the 60-day rule through INV-1094, 70 days late. Marigold Interiors LLC ($8,000.00 open, under its $12,000.00 limit) trips it through INV-1082, 97 days late. Nobody is over a credit limit - the 60-day rule is doing all the work, which is typical.

Operationally, HOLD means: no new deliverables on credit. The next piece of work requires a deposit or prepayment, the account gets mentioned in the Monday meeting, and the dunning ladder (Lesson 12) keeps running. The flag is a decision trigger, not a punishment - Kestrel at $13,000.00 open reads OK because its receivables are young.

The Collections sheet counts the flags with =COUNTIF(Customers!$J$5:$J$104,"HOLD") - two at this close - so the number reaches the monthly report without a manual recount.

💡 Tips:

  • Pick the day threshold (60 here) once, write it into the Read Me, and never bend it for a favorite client - exceptions are how policies dissolve.
  • Lifting a hold is as easy as the payment arriving: the flag recomputes the moment a receipt is recorded.

Practice

Open on Customers. Cell H5 (Open Balance of Summit Ridge Dental) is blank - type the exposure SUMIFS, then audit both hold flags.

  1. Type the exposure formula — Cell H5 is blank. Type =SUMIFS(Invoices!$K$5:$K$1004,Invoices!$D$5:$D$1004,$B5) and press Enter. H5 returns 3,500.00 - Summit Ridge's single open invoice INV-1101.
  2. Read both computed columns — Scan H and I: Kestrel 13,000.00 open / 5,600.00 overdue; Marigold 8,000.00 open / 8,000.00 overdue; Two Rivers 1,400.00 open / 0.00 overdue after its partial payment.
  3. Audit the hold flags — Column J: C04 Pinewood and C05 Marigold read HOLD - both via the 60-day rule (70 and 97 days). The other four read OK even where overdue balances exist, because nothing is 60+ days or over limit.
  4. Reconcile with the report — Add the six Open Balance cells: 3,500 + 1,900 + 13,000 + 8,800 + 8,000 + 1,400 = 36,600.00 - the same open receivables the Collections report carries.

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 exposure SUMIFS into H5 and got 3,500.00 for Summit Ridge Dental PC
  • ☐ Verified Pinewood Legal Group LLC and Marigold Interiors LLC read HOLD via the 60-day rule, four customers read OK
  • ☐ Confirmed the six Open Balance cells total $36,600.00 - matching the aging grand total and the Collections report
  • ☐ Noted the baseline: 2 customers on credit hold at the September close