RAG Status Roll-up Builder: Program Colors Backed by Rules, Not Vibes

The problem this kills

Ask three project managers what "amber" means and you'll get three answers. One calls a project amber because a milestone slipped a week; another stays green right up until the day it goes red; a third paints it amber because the sponsor "seems nervous." By the time those colors flow up into a program or portfolio deck, the rainbow on the slide tells you almost nothing — because the colors aren't comparable. They're vibes.

The roll-up itself is usually worse. Someone copies a dozen project tabs into a master spreadsheet every reporting cycle, eyeballs the colors, and picks a program color by gut. Overdue milestones, a budget that's quietly 15% over, three open high-severity risks — all of it gets flattened into one cell that a tired PMO lead colored on a Friday afternoon. There's no record of why a project is red, no way to defend an override when a director challenges it, and nothing stopping the same project from being counted twice.

A RAG roll-up should be the most trustworthy slide in the building. It deserves to be a real, governed tool with consistent rules — not a hand-painted spreadsheet.

What you'll build

A simple internal web app for your PMO. You import your project statuses — each project's milestones (and which are overdue), budget (planned vs. actual or forecast), and open risks (with severity) — from a CSV or Google Sheet. You set your RAG rules once: e.g. "red if any milestone is more than 10 working days overdue, OR budget variance is over 10%, OR there's an open high risk with no mitigation."

The tool applies those rules consistently to every project to compute a red / amber / green color, then rolls the projects up into a program color and a portfolio view — using a roll-up rule you choose (worst-child wins, a weighted threshold, or count-based). The program lead reviews the computed colors, and where their judgment differs, overrides a color — but only with a written reason that's recorded. Once the lead approves, the roll-up is published, and you can export it as a clean CSV for the steering deck. The rules are editable without touching code, so when governance changes, you change a threshold, not the app.

What's inside the Implementation Plan

The downloadable plan is a step-by-step file you paste into an AI coding agent. It opens by interviewing you about your business — how you collect statuses today, what your project export actually looks like (the real column names, how milestones and risks are recorded, how you measure budget variance), how many projects and programs you roll up, exactly what should make a project red versus amber, how a program color is derived from its projects, and the messy exceptions (a project with no budget line, a paused project, a risk with no severity). It reads a short tailored spec back to you and waits for your thumbs-up before it builds anything, so the rules match your governance — not a generic template.

From there it walks the agent through the data model, the status import with duplicate guards, the editable RAG rule engine, the project-to-program-to-portfolio roll-up, the lead's review-and-override screen with required reasons, the approval gate, and the CSV export. Every step ends with a ready-to-copy prompt. There's a full "No API yet?" path that uses a Google Sheet / CSV as the data source and produces a clean CSV roll-up — so you can build and run the whole thing this afternoon, no matter what PPM tool (or no tool) you're on.

The governance it includes (this is the point)

This is a reporting tool that leadership acts on, so the controls aren't optional. The plan builds in login so only your PMO can use it, row-level security so you only ever see your own organization's portfolio, a complete audit trail of who changed which rule, who overrode which color and why, and who approved which roll-up, a hard human-approval gate so no roll-up is published until the program lead signs off, a required reason on every manual override so a color change can always be defended, and duplicate guards so the same project can't be counted twice in a single reporting period.

Who it's for

Program managers, PMO leads, and delivery directors — anyone who has to defend a portfolio color in a steering meeting and is tired of hand-painted spreadsheets where amber means whatever the author felt that morning. If you can describe what makes one of your projects red, you can build this.

You've got this — start with the plan, paste the first prompt, and answer the interview. You'll have rule-driven program colors on screen before the afternoon's out.