✅ Deliverable tracker
⬇ Practice data for this module
Scenario: The (fictional) PEO-FS Deputy PEO wants a monthly portfolio review that (1) updates itself when program teams submit data, (2) alerts leadership when cost or schedule performance crosses thresholds, and (3) produces the standard Word status report package automatically. You're the analyst building it.
Estimated Hours: 20 (Weeks 30–33)
Architecture
┌────────────────────┐ ┌─────────────────────────┐ ┌────────────────────────┐
│ 1. RAW DATA │ │ 2. POWER BI │ │ 3. POWER AUTOMATE │
│ SharePoint library │──► │ Power Query (M) clean │──► │ Data alert: CPI < 0.95 │
│ status_drops/*.xlsx│ │ Star schema + dim_Date │ │ or slip > 30 days │
│ + D1–D7 datasets │ │ DAX EVM/budget measures │ │ → Teams + email + log │
└─────────┬──────────┘ │ Published app + RLS │ │ → Approval: PM ack │
│ └─────────────────────────┘ └───────────┬────────────┘
│ │
│ ┌─────────────────────────┐ │
└─────────────► │ 4. PYTHON │ ◄──────────────┘
│ pandas → EVM engine │ (flow notifies when the
│ openpyxl workbook │ report package lands)
│ python-docx reports │
└─────────────────────────┘
5. AI (approved tools, schema-only): drafting, review, docs
Requirements and Deliverables
| # | Component | Requirements | Deliverable |
|---|---|---|---|
| 1 | Data intake | status_drops library + core datasets. Data call log flow (Module 3.2) records submissions. |
SharePoint library + list, intake flow |
| 2 | Transformation | Parameterized Power Query with a custom cleaning function, a QA errors query, and a fiscal date dimension | PBIP/PBIX with documented queries |
| 3 | Model | Star schema, documented grain, marked date table, inactive relationships for milestone dates | Model design doc (Module 2.3 format) |
| 4 | Measures | Cumulative EVM (CPI, SPI, CV, SV, EAC, VAC, TCPI), EAC Gap, FYTD obligations vs. plan, late milestones, risk exposure | Measure library with descriptions |
| 5 | Report | Overview, Program Detail (drill-through), Execution, Schedule/Milestones, Risk pages. Theme, accessibility, "data as of". | Published report/app with RLS by portfolio |
| 6 | Alerting | Dashboard tile alert on Portfolio CPI Cum (< 0.95) + a scheduled flow that checks for milestone slips > 30 days through a DAX query. Teams + email, Alert Log list, PM acknowledgment through Approvals, try/catch error handling. | Two flows in a Solution + README |
| 7 | Report generation | Python package produces 12 program .docx reports, a portfolio summary .docx, and a formatted portfolio .xlsx. Validation gates. Numbers reconcile with Power BI to the dollar. |
Repo/folder + sample output for 2026-09 |
| 8 | Hand-off | Output to a SharePoint folder triggers a flow notifying reviewers (with an approval to "release" the package) | Flow + screenshots |
| 9 | AI usage | Document where AI helped, with sanitized prompts and verification evidence (golden dataset tests) | AI use log (1 page) |
| 10 | Briefing | 10-minute walkthrough for your supervisor: problem, architecture, demo, hours saved, risks, next steps | 5–7 slide deck or a live demo |
Suggested Capstone Plan
| Week | Focus | Hours |
|---|---|---|
| 30 | Data intake, Power Query pipeline, model, measures (reuse Phase 2 work, harden it) | 5 |
| 31 | Report pages, publishing, RLS, tile alert. Alert + slip-check flows with error handling. | 5 |
| 32 | Python pipeline: reconcile with Power BI, generate the package, hand-off flow | 5 |
| 33 | Testing with a "new month" of data end to end, documentation, AI use log, briefing | 5 |
Capstone Rubric (100 points)
| Criterion | Points | "Excellent" looks like |
|---|---|---|
| Data pipeline robustness | 15 | New month's files process with zero manual edits. Bad rows are captured in QA, not silently dropped. |
| Model and DAX correctness | 20 | Star schema with stated grain. EVM measures match the golden dataset and Python to the dollar. |
| Report design and usability | 15 | A leader can answer "what needs my attention?" in under 30 seconds. Accessible, consistent, data-as-of shown. |
| Automation reliability | 15 | Alerts fire correctly, are logged, and handle failures. Flows documented and co-owned. |
| Python report quality | 20 | Professional docx/xlsx output from templates, validation gates, one-command run, logging. |
| Security and AI governance | 10 | Fictional data only, documented AI use, sanitized prompts, RLS tested. |
| Communication | 5 | Clear briefing that ties the work to time saved and decisions supported. |
Stretch goals: incremental refresh, a Power BI paginated report version of the status report, a Teams adaptive card with "Acknowledge" buttons, unit tests (pytest) for the EVM engine, a fictional data generator script that produces a fresh month on demand.