You are the analyst at Summit Gear Co. The controller wants two answers from the accounts receivable: who pays late, and how much cash is tied up in aging receivables. The snapshot is as of March 31, 2026. The only way to answer is to do the data work and read the charts carefully.
Start here: Download the Lab 5 template (lab_05_template.xlsx)
The data is already loaded and typed. Amber cells are yours, and each chart has its own numbered sheet with a framed box where it goes. The raw files are invoices.csv and customers.csv if you want to inspect them, but submit the template, because the grader reads each chart from its numbered sheet.
Customers are on different terms (Commercial net 60, 90, or 180;
Consumer net 30), so you cannot age by days since the invoice and compare fairly. You age by days past
due, against each invoice's own due date. That is why you attach net_terms.
Invoices sheet has one row per invoice; the Customers
sheet has one row per customer. The data covers January 2025 through March 2026. A blank
paid_date means the invoice is still open (unpaid). Every amber column needs a formula.
The snapshot date is the named cell SNAPSHOT_DATE.customer_name, segment, and net_terms
from the Customers sheet onto each invoice,
matching on customer_id.net_terms. Then compute days past
due = (paid date if paid, otherwise the snapshot date) minus the due date. Zero or less means not yet due.Not due (zero or less), then 0-30, 31-60, 61-90,
91-120, 121-150, 151-180, 181+. Do this for both paid and open invoices.Build each as a native Excel chart object, on its numbered template sheet, placed over the framed box. What each one shows:
Not due), the column height is total dollars in the
bucket, each column stacked by segment (Commercial and Consumer). Filter: none. All invoices, paid
and open, the same rows you bucketed. Paid invoices belong here because this chart reads payment
behavior: a paid invoice sits in the bucket for how late it was when it was paid, an open invoice
for how late it is as of the snapshot (March 31, 2026).Trendline read: cell, the amber cell waiting below the chart box on the
4 Trend sheet, with one or two sentences on what the trendline shows and one thing it
does not tell you. Ask the AI tutor to
pressure-test your read, but write it in your own words.| Section | Points | Graded |
|---|---|---|
| Foundational data work | 30 | XLOOKUP of customer_name, segment, net_terms; due date and days past due; aging bucket (including Not due) for paid and open invoices; days to pay for paid invoices, recomputed from your own data |
| The six charts | 60 | each numbered chart is the right type, reads the right data range, and meets the four criteria (10 per numbered chart, read from the chart objects; the two pies together are chart 1) |
| The trendline read | 8 | the line chart carries a linear trendline and the Trendline read: labeled cell states, in one or two sentences, what it shows and one thing it does not tell you |
| Closing questions | 2 | the short questions at the end of the Lab 5 quiz: how long the lab took and how you used AI |
<uID>_lab_05.xlsx by Sunday, September 27, 11:59 PM MT.