📌 Stage 1 · Set Up the System · Invoicing & AR · Customer Master Data · 35–45 min

Master data that every later formula trusts

Learning goals

  • Explain why a stable customer code beats typing customer names into journals
  • Read the Customers sheet: terms as a label, terms as a number, credit limit, billing contact
  • Preview how the workbook will later compute each customer's open balance, overdue balance, and credit-hold flag

Concepts

Master data first

Every journal in this workbook keys on a customer code, never a typed name. The reason is drift: after six months of manual entry, 'Summit Ridge Dental PC', 'Summit Ridge Dental', and 'Summit Ridge' are three different customers as far as any formula is concerned. A code like C01 is short, stable, and impossible to spell two ways.

The Customers sheet carries six businesses Cedar & Co. currently bills. Each row has a code, a legal name (the exact name that appears on invoices and W-9s), terms, terms in days, a credit limit, and a billing contact - the email address every dunning message will go to.

💡 Tips:

  • Never delete a customer row that has invoice history; receivables formulas would silently drop the open balance.
  • Pick a code format now (two letters plus a number works fine) and never change it mid-year.

Terms twice: the label and the number

Terms appear in two columns on purpose. The Terms column (NET 30, NET 15) is what humans read and what prints on the invoice. The Days column (30, 15) is what formulas consume: NET 30 is a convention, but '30' is the number the due-date formula will add to the invoice date in Lesson 5.

Five of the six customers are NET 30, the US small-business default. Blue Fern Bakery LLC is NET 15: a small, thin-margin business that asked for short terms in exchange for fast payment. The credit limits range from $8,000 (Blue Fern) to $30,000 (Kestrel Outdoor Gear) - the maximum Cedar & Co. is willing to have outstanding at once.

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

A preview of Lesson 11: sum every open balance (Invoices column K) whose customer code (column D) equals this row's code ($B5). Both ranges are pinned to rows 5:1004 so new invoices are always included. For C01 this returns 3,500.00.

What this sheet will compute

The three pale-green columns on the right (Open Balance, Overdue Balance, Credit Hold) are formulas, and they are the reason master data pays for itself. Open Balance totals everything this customer currently owes. Overdue Balance isolates the part past its due date. Credit Hold applies the agency's policy in one word: HOLD or OK - the signal for whether new work goes out on credit.

You will build all three formulas yourself in Lesson 11. Today the goal is only to trust the inputs: accurate terms, sensible limits, working billing contacts.

💡 Tips:

  • The six Open Balance cells sum to $36,600.00 - exactly the open receivables figure from the Collections report. When those two numbers ever disagree, an invoice row is missing its customer code.
  • A billing contact that bounces is a collections problem in disguise; verify emails when you set up the customer, not on day 60.

Practice

Open on Customers. Six customers, five NET 30 and one NET 15; the three right-hand columns are formulas you will build in Lesson 11.

  1. Audit the six rows — Confirm codes C01 through C06, one legal name per row, and a billing email in every Contact cell. Notice each name carries its business suffix (LLC, PC, Inc) exactly as it should appear on invoices.
  2. Compare the terms — Verify Blue Fern Bakery LLC (C02) shows NET 15 and 15 days while the other five show NET 30 and 30 days. Read the Terms column aloud as a human would; read the Days column as the due-date formula will.
  3. Sanity-check the limits — Credit limits run from $8,000.00 (Blue Fern) to $30,000.00 (Kestrel Outdoor Gear). Against those limits, read the computed Open Balance column: Kestrel owes $13,000.00 of its $30,000.00; nobody is over its limit yet.
  4. Spot the early warning — Look at Overdue Balance: Pinewood Legal Group LLC and Marigold Interiors LLC already show HOLD in the Credit Hold column. Remember why - Lesson 11 derives it from the 60-day rule.

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:

  • ☐ Verified six customers C01-C06 with billing contacts, NET 30 for five and NET 15 for Blue Fern Bakery LLC
  • ☐ Confirmed credit limits range from $8,000.00 to $30,000.00 and Kestrel's $13,000.00 open balance is within its limit
  • ☐ Noted the baseline: the six Open Balance cells total $36,600.00, matching the Collections report's open receivables