Lab 5, Summit Gear AR: Charts That Answer the Controller

Foundational data work

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.

1. Open the template. The 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.
2. Use XLOOKUP to attach customer_name, segment, and net_terms from the Customers sheet (customers.csv if you built from scratch) onto each invoice, matching on customer_id.
3. Compute the due date = invoice date plus 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.
4. Assign each invoice an aging bucket from its days past due, using these exact labels: 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.
5. Compute days to pay for the paid invoices only: paid date minus the invoice date, not the due date. Measured from the due date you get negative values for early payers; that is a different metric (days past due), and the grader checks for the invoice-date version. Days to pay is how long they took, not how late they were.

The six charts

Build each as a native Excel chart object, on its numbered template sheet, placed over the framed box. What each one shows:

• 1Two pie charts on one sheet. Commercial vs Consumer by invoice count, and by dollars. The two pies tell different stories. Filter: none. Every invoice in the window.
• 2Column chart. One column per aging bucket, showing the count of invoices in each bucket. Filter: invoices billed in January 2026 only (invoice date in 2026-01); no other filters.
• 3Stacked column. Aging dollars by segment. The x axis is the aging buckets (days past due, including 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).
• 4Line chart with a trendline. Count of invoices created per week (weeks start Sunday). Filter: none. Every invoice in the window. Add a linear trendline. Then fill the 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. Ask the AI tutor to pressure-test your read, but write it in your own words.
• 5XY scatter. Invoice amount on the x axis vs days to pay on the y axis. Filter: paid invoices only (non-blank paid date). Days to pay is from the invoice date; if your scatter shows negative days, you measured from the due date.
• 6Histogram. Days to pay. Default Excel bins are fine. Filter: paid invoices only, same rows as the scatter.

Rubric (100)

Notes

• A chart is graded as an object in the file. A screenshot pasted as a picture earns nothing. If you can click it and Excel shows the Chart Design tab, it is real.
• Use the Ask-AI helper on this page when a chart will not say what you mean. It will not hand you a finished chart before you have tried one.
• Resubmit as many times as you like. The latest attempt is kept.
• A worked answer key is released after the deadline.