Week 5: Excel Analytics & Visualization

The chart becomes the deliverable, told through Summit Gear's accounts receivable. The controller wants two answers: who pays late, and how much cash is tied up in aging receivables. You do the data work, choose each chart on purpose, and read it honestly.

Lab 5 and Homework 5 are due Wednesday, June 17, 12:00 PM (noon) Mountain.

Lecture

• Reading the AR Story (interactive lecture)
  Self-paced deck. Each slide carries an in-deck AI tutor and a self-check. Aging by days past due, the six charts and what each argues, and how a chart can mislead.

Lab 5

• Lab 5: Summit Gear AR, charts that answer the controller
  The guided build. Attach customer terms with XLOOKUP, compute due dates, days past due, and aging buckets, then build the six required charts in Excel. Start here. You submit the finished workbook to the Lab 5 quiz in Canvas.
• Download the Lab 5 template (lab_05_template.xlsx)
  The workbook you fill in and submit. Data pre-loaded, amber cells for your formulas, and one framed sheet per chart so each chart has a home.
• Download invoices.csv (lab data) GITHUB
  One row per invoice: invoice_id, customer_id, invoice_date, amount, paid_date. A blank paid_date means the invoice is still open.
• Download customers.csv (lab data) GITHUB
  One row per customer: customer_id, customer_name, segment, net_terms. You XLOOKUP name and terms onto each invoice.
• Chart-Mechanics Primer
  How to build every required chart, step by step in Excel: shaping with a PivotTable, the pie pair, the January aging column, the stacked column, the line with a trendline, the scatter, and the histogram.

Homework 5

• Summit Gear AR Dashboard
  The finished dashboard the homework asks you to read. Its data is separate from the lab's, so lab numbers will not match. Change the as-of date to re-age the book. Your answers go in the Homework 5 quiz in Canvas; it allows a single attempt.
• Data assumptions GITHUB
  How the AR data is built and how aging is defined: days past due, the Not due bucket, no voids, no partials. If a number is ever in question, this doc decides it.
• Download invoices.csv (homework data) GITHUB
  Optional. The dashboard's underlying invoices, if you want to rebuild it yourself. Reading the dashboard is enough.
• Download customers.csv (homework data) GITHUB
  Optional. The dashboard's customer table, paired with the homework invoices above.

Think it through

• Building an Analytic Argument
  A chart is an argument: claim, evidence, read. The rationalization trap, with a hands-on exercise where a manager's memo primes your read and the full data contradicts it. Do this before the homework.
• Trendline Explorer
  Four datasets with the same trendline and very different stories. What a fitted line and R-squared do and do not tell you.

From Sean

• Mid-Course Check-in: What You Said, What Happens Next
  Preliminary read of the mid-class survey: what stays, what might change, and why this class is hard by design. The survey round closes Sunday, June 14 at 11:59 PM Mountain.