📌 Stage 3 · Cash Receipts · Invoicing & AR · Cash Receipts Journal · 30–40 min

One row per payment, always against an invoice

Learning goals

  • Record a receipt with the five facts the register needs: payment number, date, invoice number, amount, method
  • Pull the customer onto the receipt with VLOOKUP instead of typing it
  • Explain why the journal references invoice numbers, never customer names

Concepts

The journal

The Cash Receipts sheet is deliberately boring: one row per payment, numbered PMT-4021 onward in its own strict series, exactly like invoice numbers. The Date is the day cash actually arrived - not the day you noticed, not the invoice date. The Amount is the gross payment as received. The Method dropdown carries the four ways US small businesses actually get paid: Check, ACH, Card, Wire.

The journal currently holds eight payments spanning Aug 14 to Oct 2, 2026. Six of them landed inside September and total $12,950.00 - that figure will matter in every report from here on.

💡 Tips:

  • Record the gross amount and put processor fees in Notes; net-of-fees amounts make application arithmetic lie.
  • A check number (like Check #2081) belongs in Notes - it is how you match the journal to the bank statement.

Reference the invoice, not the customer

The only link the register needs is the Invoice #. The customer name on a receipt is decoration - so the workbook refuses to let you type it. A VLOOKUP pulls the name from the register row, which means it can never disagree with the register.

This is the deepest habit in the whole course: every journal row keys on a document number (invoice number here, PO number on the buy side). Names, emails, and terms can all be rebuilt from master data; a broken document link cannot.

=VLOOKUP($D5,Invoices!$B$5:$E$1004,4,FALSE)

Find this receipt's invoice number ($D5) in the register's first column and return column 4 of that range - the Customer name, which is itself a VLOOKUP onto Customers. One source of truth, chained twice. FALSE keeps the match exact so a typo surfaces as #N/A instead of the wrong customer.

What a receipt sets in motion

One typed row moves the entire workbook. The moment PMT-4030 ($1,000.00 against INV-1119, Sep 29) entered the journal, the register's Paid Amount rose, the open balance fell, the status became Partial, the aging shifted $1,000.00, Two Rivers' exposure on Customers dropped, and the collection report's September collections grew. No other cell was touched.

That cascade is the payoff for the discipline of Lessons 2 and 4: codes in master data, invoice numbers in journals, formulas everywhere else.

💡 Tips:

  • Money in with no invoice to apply it to is a customer prepayment - this workbook does not track credits; note it and handle it in your accounting system.
  • If a receipt references an invoice number that does not exist, the Customer column shows #N/A - treat that as a stop-sign, not a cosmetic problem.

Practice

Open on Cash Receipts. Cell E5 (Customer of PMT-4021) is blank - retype the lookup and read the journal end to end.

  1. Type the customer lookup — Cell E5 is blank. Type =VLOOKUP($D5,Invoices!$B$5:$E$1004,4,FALSE) and press Enter. E5 returns Pinewood Legal Group LLC - the customer behind INV-1094.
  2. Audit the eight rows — PMT-4021 through PMT-4031, dated Aug 14 - Oct 2, 2026. Check the Methods: four ACH, three Check, one Card - a realistic mix for a US agency.
  3. Spot the partials — PMT-4021 ($2,000.00) and PMT-4029 ($1,200.00) both reference INV-1094; PMT-4030 ($1,000.00) references INV-1119. The Notes column tells the story in words the formulas cannot.
  4. Cross-check September — Add the six receipts dated Sep 1 - Sep 30, 2026: 2,600 + 2,800 + 1,350 + 4,000 + 1,200 + 1,000 = 12,950. That is the collected-in-period figure the report will show in Lesson 13.

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 VLOOKUP into E5 and got Pinewood Legal Group LLC
  • ☐ Verified eight payments PMT-4021 - PMT-4031 dated Aug 14 - Oct 2, 2026 across ACH, Check, and Card
  • ☐ Confirmed the September-dated receipts total $12,950.00 - the period's collections figure