Capstone — From Raw Data to Briefing
Format. Four weeks (Weeks 12–15), solo. Submit
CAP_LastFirst.pbix + the one-page insight memo + (if your
tenant allows) the published link. Worth 20% of the course. This is the whole loop, on a
company you have never seen, with nobody holding your hand — which is the point.
Scenario
Bramblewood Roasters — twelve cafés in three regions plus a web store, thirty coffee SKUs, a 2,500-member subscription program — has never had an analyst. You receive their estate: two years of orders in two differently-formatted exports, a product catalog, locations, the member list, and finance's 2026 regional targets. Leadership asks exactly one thing: "Tell us what we should do, and show us why."
Deliverables
- The model (the machine): orders combined into one fact (mind the date formats — the two files disagree, and one of them will fight your locale), a proper star with a marked date spine, conformed dimensions as needed, and the targets joined. Document every cleaning decision in the file (a "Data Notes" page or the memo's appendix) — including your decision about the 29.8% of order lines with no MemberID: what does a blank member mean, what did you do about it, and what does your choice do to any per-member analysis?
- The measure family: at minimum — Total Revenue, Total Cost, Gross Profit, Gross Margin %, Order Count, AOV, % of Total Revenue, Revenue YTD, YoY Growth %, Target Revenue, Attainment %, and Subscription Revenue % (new — define it precisely and defend the definition in the memo). Explicit, named, formatted, DIVIDE-guarded.
- The report: at minimum a Briefing page (answer page — L10 rules in full), an Explore page (cockpit with reset), and a Detail drillthrough page with dynamic title. Design pass applied everywhere: finding titles, alt text, tab order, the one-in-twelve test.
- One RLS role (a regional manager of your choice), View-as verified, with the verification figure recorded in the memo.
- The insight memo (the point): one page to leadership. Three findings maximum, each stated as a claim, supported by a number from your model, and ending in a recommendation or a named decision. One honest limitation of the analysis. Every figure in the memo must be traceable to a visual in the report — the grader checks.
Scoring (100 points)
| Component | Points |
|---|---|
| Model: combined fact (31,655 rows), star shape, marked spine, targets joined, no fact-to-fact, guest-member decision documented | 25 |
| Measures: the family present by name, values verified against the capstone manifest, composition and DIVIDE discipline | 20 |
| Report: three pages passing their kind's rules; mechanical checks (alt text 100%, tab order, no default page names) | 20 |
| RLS role verified with figure recorded | 5 |
| Insight memo: claims → numbers → recommendations, one limitation, full traceability (instructor-graded) | 25 |
| Submission hygiene | 5 |
What "good" looks like
The data contains real stories — a strong winter seasonality (this is coffee), a
subscription program near a quarter of revenue, a Web channel that will vanish from any
map, and targets that 2026 is beating or missing region by region. We are not telling you
which stories are the three best. Choosing them — and saying what you'd want to know next —
is the analyst's job, and it is most of the memo's 25 points.
Milestones (instructor-checked, ungraded)
| Week | Checkpoint |
|---|---|
| 12 | Estate loaded, fact combined, row count reconciled (31,655) |
| 13 | Star complete, measure family computing, three candidate findings listed |
| 14 | Pages built, design pass done, memo drafted |
| 15 | RLS verified, memo final, submitted |