IT budget and PMO

Project RAG status: criteria you can compute instead of a feeling

Published 20 October 2026 · 6 min read

A project RAG status you can trust is computed from written criteria, not declared. Four measurable criteria are enough to start: schedule delay against the original end date, budget drift (share of budget spent minus share of work complete), open high or critical risks, and decisions overdue. Each criterion gets an amber and a red threshold, the project takes the worst colour among the four, and a spreadsheet formula applies the rule the same way to every project, every week. The thresholds are management choices: what matters is that they are written down before the colours are read.

Why declared status drifts

A status chosen by the project manager mixes facts with hopes. Three mechanisms push it towards green: the date is re-planned, and the delay disappears because it is measured against the new plan; the overspend is real but "will be recovered"; the colour is negotiated before the committee because red is read as a judgement on the person. The result is the familiar watermelon project, green outside and red inside, until the day it cannot be hidden. A computed status removes the negotiation: the discussion moves from "which colour" to "what do we do about the cause".

For a reference text on project governance, ISO 21502:2020, Project, programme and portfolio management — Guidance on project management, gives guidelines for project management applicable to any type of project. It does not define RAG thresholds; the rule below is one way to make status reporting factual.

Four measurable criteria

Criterion	Measure	Amber (example)	Red (example)
Schedule delay	Forecast end − baseline end, in days	15 days or more	30 days or more
Budget drift	% of budget spent − % complete, in points	5 points or more	10 points or more
Open risks	Open risks on the project, score = probability × severity (1 to 25)	At least one open risk scoring 12 or more	At least one open risk scoring 16 or more
Decisions overdue	Status date − due date of the decision required	Past its due date	30 days or more past its due date

The thresholds shown are examples. Delay and drift thresholds of 15 and 30 days and 5 and 10 points are the defaults of the cockpit described below; the risk and decision thresholds are a proposal. Calibrate them on your own portfolio, then leave them alone for a quarter.

The combination rule

Worst colour wins: any red criterion makes the project Red; otherwise any amber criterion makes it Amber; otherwise Green.
No status for closed projects: completed or cancelled projects show nothing.
Show the driver: next to the colour, display which criterion triggered it ("Red: drift 11 points"). The committee discusses causes, not colours.
Overrides are written, never silent: if the project manager disagrees, a separate column records the declared status and the reason; the computed status stays visible next to it.

The spreadsheet formula

Assume one row per project with status in G, % spent in K, % complete in L and delay in days in R, and named thresholds in a Settings tab. The two-criterion version, delay and drift, is:

=IF(OR(G7="Completed",G7="Cancelled"),"",IF(OR(N(R7)>=DelayRed,N(K7)-N(L7)>=DriftRed),"Red",IF(OR(N(R7)>=DelayAmber,N(K7)-N(L7)>=DriftAmber),"Amber","Green")))

To add the two other criteria, use three helper columns fed by the risk register and the decision log:

W, open critical risks: =COUNTIFS(Risks_Project,$A7,Risks_Status,"<>Closed",Risks_Score,">="&RiskRed)
X, open high or critical risks: the same with RiskAmber.
Y, days a decision is overdue: =IF(U7="","",StatusDate-U7), where U holds the due date of the decision required.

The four-criterion status is then:

=IF(OR(G7="Completed",G7="Cancelled"),"",IF(OR(N(R7)>=DelayRed,N(K7)-N(L7)>=DriftRed,W7>=1,N(Y7)>=30),"Red",IF(OR(N(R7)>=DelayAmber,N(K7)-N(L7)>=DriftAmber,X7>=1,N(Y7)>0),"Amber","Green")))

N() turns an empty cell into zero, and a decision not yet due gives a negative Y, which triggers nothing. COUNTIFS is available in Excel 2016 and in Google Sheets.

Example on five projects (fictitious data)

Status date 20 October 2026, thresholds as in the table above.

Project	Delay (days)	Drift (points)	Highest open risk score	Decision overdue (days)	Declared	Computed
A, intranet redesign	0	+2	8	—	Green	Green
B, telephony migration	18	−3	12	—	Green	Amber (delay, risk)
C, payroll replacement	5	+11	9	10	Amber	Red (drift)
D, backup platform	0	+1	16	—	Green	Red (risk)
E, document management	12	+4	6	6	Green	Amber (decision)

Declared, the portfolio shows four greens and one amber. Computed, it shows one green, two ambers and two reds. Project D is the useful surprise: on time and on budget, but carrying a critical risk that nobody has brought to the committee. Project C's 10-day-old decision would only have made it amber; its drift makes it red. Project E is amber for a single reason, a decision six days overdue, which the committee can clear at its next meeting.

Presenting status to a steering committee

Sort from red to green, then by priority. For each red or amber project, the project manager gives the cause in one sentence and what is asked of the committee; green projects are not presented. Keep the same rule for alerts elsewhere: budget lines alert on their forecast variance, as in the IT budget template, and contracts alert on their notice dates, as in the IT contract register. A computed signal everywhere makes the committee's attention go where the numbers point.

What the tool does for you

The IT PMO Cockpit is an Excel workbook of ten tabs, with native formulas and no macros, for Excel 2016 and later or Google Sheets. Its Project portfolio tab computes the RAG status from the first two criteria above, delay against the baseline end and budget drift, with amber and red thresholds set once in Settings (defaults: 15 and 30 days, 5 and 10 points); completed and cancelled projects have no status. Risks and decisions are tracked in their own tabs rather than in the project colour: a RAID register with score, level (moderate from 6, high from 12, critical from 16) and a 5 × 5 matrix, and a decisions and actions log that flags overdue items. The KPIs tab counts red projects, open high or critical risks and overdue actions; the four-criterion version above is an extension you add with the helper columns. It ships with a fictitious data set, a 17-page method guide and an 8-slide steering committee template. Unlimited use within the licensed company, 12 months of updates.

IT PMO Cockpit: $490 excl. tax

Launch code LANCEMENT: 30% off, for the first 20 orders or until 30 November 2026.

FAQ

Should Amber require two criteria instead of one?

It can, but every extra condition makes the rule harder to explain. Start with "worst colour wins"; if too many projects turn amber, raise the thresholds at the quarterly review rather than complicating the formula.

Can a project manager override the computed colour?

Yes, in a separate column with a written reason, shown next to the computed status. Replacing the formula with a typed colour defeats the purpose.

Budget drift or forecast variance?

Both measure money. Drift compares spending with progress and works while the project runs; forecast variance compares the expected final cost with the budget. Use drift in the RAG rule and show the forecast on the budget slide.