Project Status Dashboards in Excel: The 6 Metrics Executives Actually Read Before Steering Committee

A project status dashboard in Excel is a single-page spreadsheet that summarises schedule, budget, scope, risk, and resource health for a portfolio of initiatives, sized so an executive can read it in under two minutes before a steering committee meeting. The best ones show six metrics: schedule variance, cost performance index, milestone hit rate, risk exposure, resource utilisation, and benefit realisation to date. Everything else is supporting evidence.

Steering committees do not read your 47-tab workbook. They scan the top of page one, ask three questions, and move to the next agenda item. If your project status dashboard in Excel cannot answer those questions in the first fifteen seconds, the meeting decays into narration, which is exactly what the sponsor wanted to avoid by scheduling the review.

What follows is the six-metric layout used by PMOs at organisations like Deloitte and PwC, mapped to formulas you can build in a blank workbook today, plus the reporting cadence that survives contact with a real board.

Why most Excel project dashboards fail the steering committee test

The Project Management Institute's 2024 Pulse of the Profession survey, which polled 3,903 project professionals, found that only 43% of projects were completed within budget and 48% within schedule, and cited "poorly defined goals and objectives" as a top driver of failure. Dashboards inherit that ambiguity. If the plan is fuzzy, the dashboard reports fuzz in twelve colours.

The Standish Group's CHAOS reports, tracked since 1994, put the successful-project rate for large initiatives at roughly 13% (Standish reported this range across its 2015 and 2020 editions). The gap between "on track" as reported and "on track" as measured is where most steering committees get ambushed. A green tile on slide one, a red root cause on slide fourteen, and a sponsor who now distrusts every number in the deck.

Three failure modes recur across the workbooks I audit:

Traffic-light RAG status set by the project manager's mood, with no formula behind the colour.
Percent complete measured as "tasks done divided by tasks planned", which ignores whether the remaining tasks are the hard ones.
Budget shown as spend to date against total budget, with no reference to what the plan said the spend should be at this point.

Fix these three and the dashboard starts telling the truth. The six metrics below are the fix.

The 6 metrics executives actually read

Order matters. A steering committee reads left to right, top to bottom, and gives up around the fold. Put the metric with the highest information content first.

1. Schedule Variance (SV) and Schedule Performance Index (SPI)

Earned Value Management is the vocabulary the US Office of Management and Budget mandates for federal capital projects above $50m, per OMB Circular A-11. It is the vocabulary any executive who has run a large programme already speaks. Use it.

Planned Value (PV): budgeted cost of work scheduled by today.
Earned Value (EV): budgeted cost of work actually completed by today.
Schedule Variance: SV = EV - PV. Negative means behind schedule.
Schedule Performance Index: SPI = EV / PV. Below 1.0 means behind.

In Excel, if your work breakdown sits in a table called WBS with columns Planned_Cost, Percent_Complete, and Baseline_End_Date, then EV is =SUMPRODUCT(WBS[Planned_Cost], WBS[Percent_Complete]) and PV is =SUMIFS(WBS[Planned_Cost], WBS[Baseline_End_Date], "<="&TODAY()). Two cells. That is your top-left tile.

If you show one schedule metric, show SPI as a decimal to two places, with a spark bar next to it showing the last eight weeks. A drifting SPI is the earliest honest signal a programme is losing time.

2. Cost Performance Index (CPI)

CPI = EV / AC, where AC is the actual cost incurred to date. Below 1.0 means you are spending faster than you are earning value. Above 1.0 means the opposite, though on infrastructure programmes an unexplained CPI above 1.1 often means the vendor has not billed yet, not that the project is ahead of plan.

The Government Accountability Office's 2020 GAO Cost Estimating and Assessment Guide (GAO-20-195G) treats a sustained CPI below 0.9 as a formal early-warning threshold on federal programmes. Copy that threshold. Anything under 0.9 for three consecutive reporting periods gets a red tile and a written mitigation, no exceptions, no colour override from the sponsor.

Pair CPI with Estimate at Completion (EAC = Budget at Completion / CPI). Executives care less about the current burn than the forecast landing point.

3. Milestone hit rate

Percent complete lies. Milestone hit rate does not. Count the milestones due in the reporting window, count the ones that landed on their baselined date, divide.

McKinsey's 2020 study "Delivering Large-Scale IT Projects on Time, on Budget, and on Value" (McKinsey Digital, October 2020), based on more than 5,400 projects, found that projects on average run 45% over budget and 7% over schedule, while delivering 56% less value than predicted. Milestone hit rate over a rolling twelve-week window is the leading indicator of that value gap. When it drops below 70%, the schedule figures on the same dashboard are three weeks away from red.

Excel build:

Table called Milestones with columns Name, Baseline_Date, Actual_Date, Status.
Hit rate over last 12 weeks: =COUNTIFS(Milestones[Status], "Delivered", Milestones[Baseline_Date], ">="&TODAY()-84, Milestones[Actual_Date], "<="&Milestones[Baseline_Date]) / COUNTIFS(Milestones[Baseline_Date], ">="&TODAY()-84, "<="&TODAY()).
Format as a percentage, red below 70%, amber 70 to 85%, green above.

Publish the milestone list with baseline dates locked at project kickoff. Rebaselining is a governance event that goes on the change log, not a silent edit inside the workbook.

4. Risk exposure (probability-weighted impact)

Most risk registers list twenty items, three of which matter. Boards want the top-three list with a number attached, not a heat map.

Risk exposure = sum of (probability * financial impact) across the register. In Excel, if your risk register table has Probability (0 to 1) and Impact_USD, then exposure is =SUMPRODUCT(Risks[Probability], Risks[Impact_USD]). The top-three tile is the same formula filtered to the three highest products.

The Association for Project Management's Body of Knowledge (7th edition, 2019) recommends distinguishing gross exposure (before mitigation) from net exposure (after). Show both. The gap between them is the value of your risk management, and a steering committee that sees the gap shrink over time can tell your PMO is doing something.

Any risk with a net exposure above 5% of remaining budget gets its own line in the paper. Everything else stays in the register.

5. Resource utilisation and key-person concentration

Executives ask two resource questions. Are we staffed, and can any one person walk out the door and cost us the plan? Answer both on one tile.

Utilisation: =SUMIFS(Timesheets[Hours], Timesheets[Week], ">="&TODAY()-28) / (COUNTA(Team[Name]) * 40 * 4). Sustained above 95% predicts attrition, sustained below 70% predicts scope creep because idle hands invent work.
Key-person concentration: the percentage of critical-path tasks assigned to a single named resource. Above 25% is a governance issue and belongs in front of the sponsor.

Deloitte's 2023 Global Human Capital Trends report, which surveyed 10,000 leaders, flagged workforce concentration as one of the top three underreported programme risks. Most Excel dashboards do not report it at all. Adding this one column separates a PMO that runs projects from one that runs a portfolio.

Publish the key-person list quarterly with the sponsor. Do not wait for a resignation to discover the concentration.

6. Benefit realisation to date

The metric that closes the loop. Projects exist for benefits, not for delivery. If your dashboard reports SPI, CPI, and milestones but not benefits, the steering committee has no way to decide whether to keep funding the programme.

Benefit realisation = actual benefits captured to date, divided by the benefits the business case promised by this point. Track it monthly against the case that was approved at gate zero. If the case said $2m of run-rate savings by month twelve and you are at month twelve with $600k, that is a 30% realisation rate and it is the number the CFO wants on the first page.

The UK Infrastructure and Projects Authority's 2023 Annual Report on Major Projects, covering 244 government programmes worth GBP 805bn, rated 8% of projects red and 32% amber on delivery confidence. The IPA's public methodology, published on gov.uk, ties confidence ratings directly to benefit-tracking discipline. Borrow it.

Freeze the business case at approval. Report against that frozen version. Rebaselining benefits mid-flight is how programmes claim success after quietly moving the goalposts.

Dashboard layout that survives a steering committee

Six tiles on a single sheet, in a 3x2 grid, at the top of the page. Below the grid, three supporting blocks: milestone timeline, top-three risks, and a two-line commentary in the project manager's own words. Everything else goes on hidden tabs the executive can drill into if asked.

Formatting rules that keep the sheet legible:

Absolute values in bold, deltas versus last period in a smaller font underneath.
Conditional formatting driven by numeric thresholds, not manual colour picks. If someone can override the colour without changing the formula, the colour is a story, not a status.
One font, two sizes, three colours. Grey ink for context, black for numbers, one accent colour for the current status.
Print area set to A4 landscape. Executives still print things.

The dashboard is rebuilt weekly, published Monday at 09:00 local time, and steering committee papers are frozen 48 hours before the meeting so the sponsor can read them on the plane. The frozen version goes to a read-only PDF; the live workbook keeps moving.

Common Excel traps and how to avoid them

Three failures I see in almost every audit:

Volatile formulas. INDIRECT, OFFSET, and TODAY() recalculate on every keystroke and turn a 12MB workbook into a 40-second wait. Use structured table references and a hard-coded Report_Date cell that you update weekly.
Hidden manual overrides. Someone types "Green" into a status cell because the formula said "Amber". Lock the formulas, protect the sheet, and log every override to a change tab.
Baselines stored as values, not as a versioned snapshot. When the plan changes, the original baseline vanishes and SPI becomes meaningless. Freeze the baseline on a separate hidden sheet, dated and initialled.

If the workbook is not auditable back to the approved plan, the dashboard is a work of fiction. Version control turns a project file into a governance artefact rather than a marketing artefact.

Putting it together

A steering committee has ninety minutes and eight projects to review. That gives your programme roughly eleven minutes of airtime, of which two are spent on the dashboard and nine on the discussion the dashboard triggered. If the six metrics are on the page, calculated correctly, and unspun, the discussion goes to decisions. If they are not, it goes to interrogation.

Building the six-metric layout from scratch is a week of work: two days on the WBS and baseline structure, two days on the earned-value formulas, one day on the risk and benefit tabs, and a day of pilot with a real project manager. A pre-built template compresses that to an afternoon of parameterisation, which is the point of buying one. The formulas above are the same ones in the ModelStack Project Status Dashboard template; if you would rather adapt than build, that is the shortcut.