Lab 3, Data Sources: AR, Aging, and Cash Application

0.1, The origin of the data

Summit Gear Co. is a fictional wholesale outdoor-equipment distributor based in Salt Lake City. Four customer segments, Outdoor Retail, School District, Resort, Outfitter. The CFO reads an AR aging report on Monday morning to decide which customers to call. You are building that report.

A few details worth knowing up front:

• Net terms are a customer-level attribute. A given customer always invoices on the same terms, terms_id lives on customers, not on invoices. To find an invoice's terms you join through the customer.
• Invoice status is stored on the invoice. Someone in accounting flips it when they think an invoice has been paid (or cancelled). This stored status is not always correct. Finding the disagreements is Part 4.
• Most payments are 1:1 with an invoice. A handful are edge cases, underpayments, overpayments, and one split, that you discover when something in your aging report looks weird.

0.2, Pre-lab interactives

Five pieces, all in your browser. Strongly recommended before you start the lab if SQL is new to you.

• SQL 1/4SQL Fundamentals trainer, SELECT, FROM, WHERE, ORDER BY, LIMIT. Foundation for everything that follows.
• SQL 2/4JOIN trainer, INNER vs LEFT, the Venn diagram, the VLOOKUP/XLOOKUP parallel, and the 1:n trap demo (see below). Walk this before the Aggregate trainer: the GROUP-BY-through-a-JOIN lesson there assumes the join already makes sense.
• SQL 3/4Aggregate trainer, GROUP BY, SUM, COUNT, AVG, HAVING. Animated row-to-summary collapse.
• SQL 4/4Aging builder, the headline trainer. Builds on joins and aggregates. Lands exactly the query you'll recreate in HW 3.
• ExcelExcel concepts trainer, eight lessons covering SUMIFS at 20K rows, single XLOOKUP, chained XLOOKUP through three tables, IFS for aging buckets, the $A$1 cutoff-cell pattern, and pivots. Each lesson shows the formula plus a parallel Copilot-for-Excel prompt. Walk this if any of the Part 3–4 formulas are new to you.

0.3, Cheat sheets

Keep these open in tabs while you work.

• HTMLSQLite cheat sheet, one-page reference covering every syntax in this lab.
• MDSQLite cheat sheet (Markdown), the same content in a form you can paste into ChatGPT.

0.4, The kickstart prompt (TLDR)

If you'd rather start in the LLM and pull context as you go, the button below copies a minimal-essentials prompt to your clipboard, the lab's idea, the five-table schema in compact form, the single Excel deliverable, and a link to the cheat sheet. Paste it into ChatGPT, Claude, or Gemini and you have enough to land the headline parts of the lab even if you have not walked the trainers.

Need a kickstart? Click here to copy the prompt

Summit Gear's AR covers 2024-01-01 → 2026-04-30, about 28 months. Five tables, ~20,000 invoices, ~18,700 payments. You work the whole lab in one pre-loaded Excel template, which you download in Part 1, with the five source tables already loaded as named sheets. The raw CSVs are also hosted if you want to inspect a table on its own, but you do not need them.

The schema

About PK and FK. A primary key (PK) is the column that uniquely identifies each row of a table. customer_id is the PK of customers: every customer has one, and no two customers share it. A foreign key (FK) is a column that points to another table's PK. invoices.customer_id is an FK pointing at customers.customer_id; that is how you know which customer an invoice belongs to. PK and FK are the glue that turns a pile of tables into a relational database. The arrows in the diagram below show those FK relationships.

                    invoice_terms                invoice_statuses
                    +-----------+                +-----------+
                    | terms_id  |                | status_id |
                    | terms_name|                | status_name|
                    | net_days  |                | description|
                    +-----+-----+                +-----+-----+
                          |                            |
                          | (terms_id FK)              | (status_id FK)
                          v                            v
   +-----------+    +-----+-----+              +-----+-----+
   | customers +----> customer_id|              | invoices  |
   +-----------+    | terms_id   |<-------------+ customer_id|
                    | segment    |              | status_id  |
                    +-----+-----+               | amount     |
                          ^                     | due_date   |
                          |                     +-----+-----+
                          |                           |
                          |                           | (invoice_id FK)
                          |                           v
                          |                     +-----+-----+
                          |                     | payments  |
                          +---------------------+ invoice_id|
                                                | amount    |
                                                | pay_date  |
                                                +-----------+

As-of dates that matter in this lab

Download the data

One pre-loaded Excel template is all you need. The raw CSVs are also hosted if you want to inspect a table on its own or take the CSV-import path described in Part 1.

• XLSXlab_03_template.xlsx, the template workbook you fill in. Nine sheets: a parameters sheet (cutoff), five source tables (customers, invoices, payments, invoice_terms, invoice_statuses), and three empty target sheets for pivots (aging_summary, discrepancies, top_customers).
• CSVcustomers.csv · invoices.csv · payments.csv · invoice_terms.csv · invoice_statuses.csv, the raw tables, one per CSV. Not needed if you use the template.

How to introspect the tables

In Excel, open the template (or a CSV): the row count is in the bottom-left of the window, the column headers are row 1, and clicking a cell shows the raw value.

Download lab_03_template.xlsx and open it. Save it as lab_03.xlsx right away, this one workbook is the entire Excel deliverable. Do not put your name or university ID in the filename; Canvas attaches your identity automatically when you upload.

The nine sheets

The template ships with nine sheets, in this order. Do not rename them. The grader reads the workbook by sheet name.

The cutoff sheet

One cell drives the whole workbook. cutoff!$B$1 is the as-of date. Every formula you write in Part 2 references it. When you change it, every formula on invoices recomputes, and every pivot built on tbl_invoices follows on refresh. Hard-coding 2026-04-30 into formulas instead misses the lesson. In HW 3 you recreate these same as-of numbers in SQL, so a cleanly parameterized workbook is what lets the two tie out.

On the invoices sheet, columns G through O are headers only, the body is empty. You write one formula per column in the first data row; the Excel Table auto-fills the column down all ~20,000 rows. Use structured references everywhere: tbl_invoices[@[invoice_id]], not A2. Structured references survive table extensions and reorderings; cell references do not. Note the double brackets: [@[column_name]] is the safe form for the current row's value, use it consistently.

2.1, Cash application

Three columns that turn the raw payments into per-invoice cash applied, outstanding balance, and a derived status. Write the formula once in the first data row; the Excel Table auto-fills the column down all ~20,000 rows.

At 20,000 rows the paid_to_date SUMIFS is slow. Expect Excel to pause for several seconds when you fill the column. That pause is the point. You are doing by hand what SQL does in three lines.

2.2, Date math and aging

Two columns that turn the due date into a bucket label. These formulas assume due_date is stored as a real Excel date. If it came through as text, the subtraction will throw #VALUE!. See the data-type heads-up above for the fix.

2.3, Lookup columns (display polish)

Setup step, add payment_terms to the customers sheet

The customers table only stores terms_id (a foreign key into invoice_terms), not the actual terms name. Rather than nesting two lookups every time you want the terms name on an invoice row, add a helper column called payment_terms to the customers sheet first. With it in place, every invoices-side lookup below stays single-hop.

Now fill the four lookups on the invoices sheet

Four columns to fill via lookups against tbl_customers and tbl_invoice_statuses. Two worked XLOOKUP examples below; you write the other two yourself, following the same pattern with a different return column or lookup table.

On the aging_summary sheet, insert a pivot table from tbl_invoices.

The buckets will sort themselves, the "0. " / "1. " prefixes from your IFS formula in Part 2.2 mean the rows appear in order automatically (no manual reorder). Format the SUM column as currency, no decimals.

Note for the curious: the workbook's cutoff!$B$1 cell drives every analytical formula in Part 2, so you could change it (say, to 2025-12-31) and watch the pivot recompute. That's the parameterization payoff. The lab doesn't ask you to change it here, but that as-of-date discipline is what keeps your numbers reproducible when you rebuild them in SQL for HW 3.

Discrepancy Check: We often see mismatches between data that can have a material impact. We want to get in the habit of auditing outputs and ensuring they meet expected outcomes. The stored invoice status is what a person in the ERP recorded. The payments table is what actually happened. When the two disagree, the analyst's job is to surface the difference and the dollars at stake.

On the discrepancies sheet, insert a pivot from tbl_invoices in a matrix layout: stored status across one axis, derived status across the other:

You should see a 4×4 grid: four stored statuses down the rows, four derived statuses across the columns, a count in every cell. The diagonal cells (Open/Open, Paid/Paid, Partial/Partial, Void/Void) are where the stored status agrees with the payment-derived state, most rows live there. The off-diagonal cells are the discrepancies.

The three off-diagonal cells you should expect to see populated:

• stored = Paid, derived = Open, the ERP thinks it's paid; the cash says no payment came in. (Paid-but-not.)
• stored = Open, derived = Paid, the cash arrived; nobody flipped the status. (Open-but-paid.)
• stored = Void, derived = Paid (or Partial), the ERP says this invoice is cancelled, yet cash came in against it. (Void-with-payment.) From the data alone we can't say whether the void came before or after the payment (no voided_at timestamp, see the §0.1 callout). The diagnostic move: flag for the controller and pull the supporting documents.

You do not need to filter the pivot to highlight the mismatches. The grid layout does the work. The diagonal is the agreement. The off-diagonal cells are the discrepancies.

One more sheet on the workbook before you submit. The summary sheet consolidates your headline numbers in one place, counts, totals, the open-balance figure, the top-10 customers, so you have a validation target when you write SQL for HW 3. Each row of this sheet maps to a homework question: when you run the SQL, your result should match the number on this sheet, or you have a bug somewhere.

5.1, Build a summary sheet

Add a new sheet named summary (right-click any tab, then Insert or New Sheet). Lay out two columns: Label in column A, Value in column B. Use formulas that reference your named tables and the Part 2 columns, not hard-coded numbers. If you change cutoff!$B$1, every value here should follow.

The per-bucket aging totals (HW-07) and per-flavor discrepancy counts (HW-08) already live on your aging_summary and discrepancies pivots from Parts 3 and 4, no extra cells needed; just know which numbers to cite when validating.

5.2, Build the top_customers pivot

The template ships with an empty top_customers sheet. Build a PivotTable there to give yourself the top 10 customers by open balance, the HW-06 validator.

• Source: tbl_invoices (which has customer_name, segment, and balance after Part 2).
• Rows: customer_name (or customer_id if you prefer the ID-driven view).
• Values: Sum of balance.
• Filter: Use Value Filter → Greater Than → 0 on the row labels so closed invoices don't pull the totals down.
• Sort: Descending by Sum of balance.
• Top N: Value Filter → Top 10 Filter, by Sum of balance.