Excel Dashboards and Phase 1 Integration

💡 Key concepts

• Dashboard design: Answer the top 3 leadership questions above the fold. KPI cards → trend → exceptions table. Use color to flag exceptions, not to decorate.
• Charts: Combo charts (planned vs. actual obligations with a cumulative line), sparklines, conditional-formatting data bars and icon sets, an S-curve for cumulative BCWS/BCWP/ACWP.
• Workbook architecture: Data (queries) → Model → Calc → Dashboard. Lock and protect the presentation sheets.
• When to graduate to Power BI: Many users, row-level security, scheduled refresh, larger data, interactive drill-down, a single source of truth.

🛠 Hands-on exercise: EVM S-Curve

Data: evm_monthly for PRG-002 (PeriodEnd, BCWS, BCWP, ACWP, BAC). Use your SCAN cumulative spills from 1.3 to plot cumulative BCWS, BCWP, and ACWP lines, plus a horizontal BAC line. Add a CPI/SPI KPI card with your RAG() LAMBDA driving conditional formatting.

📦 Mini-project: PEO-FS Monthly Excel Dashboard (Phase 1 Deliverable)

One refreshable workbook with:

• Power Query ingestion of status_drops/ and the core datasets
• A Data Model with measures
• KPI cards (portfolio CPI, SPI, obligation rate vs. plan, # late milestones)
• An S-curve for the selected program (dropdown)
• A watch list (dynamic arrays) of late milestones and top risks
• Documentation sheet: data sources, refresh steps, LAMBDA definitions