RAG Status Calculator: Turn Evidence into a Reviewable Colour

RAG summarises project health or delivery confidence against agreed expectations; teams use thresholds, evidence-informed professional judgement or both. A RAG status calculator is useful only when another person can follow its reasoning. A green cell without a source, an evidence date or an agreed rule is still an opinion. This scorecard workflow shows how to carry evidence through to the colour, expose uncertainty and prepare a reviewable status statement without hiding a serious exception inside an average.

Definition

RAG calculator: A RAG calculator applies agreed project status rules to current evidence, producing a colour and an explanation that people can check before using it in a report.

In this method: Keep each required dimension visible, retain unresolved exceptions, give Red precedence, then check required-evidence completeness before taking the worst colour.

Not the same as: A probability of project success or permission to proceed with delivery.

PMOs usually invent their own RAG rules; this calculator does not replace that. InsightSpotter’s worksheet is for education — a reviewable way to carry evidence into a colour so you can adapt or rewrite the rules for your board. It is not a new industry standard.

Start with the existing criteria, then add evidence discipline

The RAG status criteria guide already explains thresholds, worst-of aggregation and a simple one-page calculator. Use that guide to agree the rules. This companion focuses on a different problem: making each assessment traceable when evidence arrives at different times, owners disagree or someone proposes an override. It is a worksheet workflow, so you can use it without installing a web widget.

As of October 2026, this is InsightSpotter’s editorial scorecard method for local adaptation, not an industry scoring standard or a validated prediction model. Computable rules and conservative worst-of aggregation are choices in this method, rather than requirements that define all RAG reporting. Agree the dimensions, thresholds, authority and review cadence with the people who will use the report. Do not treat a copied example as approval to change your organisation’s governance arrangements.

Treat the sheet as local reporting practice under your own governance, not a substitute for it. The APM definition of governance is a useful check that roles, escalation and reporting stay clear; UK government delivery teams can align language with GovS 002: Project Delivery.

Agree the questions before designing the sheet

Choose the dimensions that genuinely describe this project’s delivery confidence. Time, cost, scope, quality and risk may be useful starting points; benefits or critical dependencies may need their own rows. Choose the material dimensions before the cycle and mark every included dimension as required. Record any agreed exclusion, its rationale and approver; an excluded dimension is not Green. Do not drop a row to improve the result or hide an open exception. Where mandatory acceptance matters, include quality as a required dimension.

For each dimension, write a rule that distinguishes green, amber and red. Define the input, unit, baseline and boundary conditions. If two rules could apply, state the precedence. If the rules overlap or leave a gap, resolve that before calculating. Keep the rule version with the cycle so a later reviewer can reproduce the result.

Use complete bands before writing formulas

Use the criteria pillar’s starter table or agree a replacement rule set. For its time example, use signed calendar days late, d = forecast finish minus approved baseline finish, for the same named project finish or material milestone. Green is d <= 5; Amber is 5 < d <= 15; Red is d > 15. Exactly 5 is Green, exactly 15 is Amber, and 16 is Red. On-time and early forecasts are Green on time alone. Remove time-of-day values: the baseline day is zero and the next calendar day is one day late; weekends and holidays count. Zero float is supporting context, rather than a second trigger. Working-day rules need their own agreed calendar, holidays, counting convention and matching formula.

For cost, v = (EAC – approved budget) / approved budget, using the same currency and contingency basis. Green is v < 5%; Amber is 5% <= v <= 10%; Red is v > 10%, or exhausted agreed contingency with further overspend against the approved budget forecast. Test the Red condition first. Exactly 5% and 10% are Amber; a valid underspend or an overrun below 5% is Green unless the contingency Red condition applies. Classify unrounded variance and round only for display. A Green cost result says nothing by itself about scope or benefits.

A missing, zero or negative budget makes this percentage rule unusable. A missing, negative or unverifiable EAC is also unusable. An unknown contingency position must be flagged rather than treated as false. Use Not assessed for unusable current evidence unless an independently established adverse exception is retained. Never turn a blank, error or Not assessed value into zero. Retain exceptions and validate inputs before applying any formula. These bands are local teaching examples, not recommended universal tolerances.

Separate risk exposure from control completeness

Use the project’s agreed residual-risk scale, issue severity and appetite to assess delivery exposure. Under the pillar’s starter rule, any critical item, or one or more high items blocking the next material milestone, is Red. With no Red condition, any high item, exposure outside agreed appetite or issue threatening the next material milestone is Amber. Green requires no high or critical item, residual exposure within appetite and no issue threatening the next material milestone. Missing scale definitions or unusable exposure evidence prevent a complete assessment.

Record owner, response, next review date and overdue actions separately as control completeness. A high residual risk with an owner and a review date remains Amber; a critical item remains Red even if the exposure has been accepted. Acceptance records authority to carry exposure, rather than evidence that exposure has fallen. Missing control fields trigger Review required, while the exposure colour remains visible. If the exposure itself is unknown, show current Not assessed and retain any open adverse exception with its last-supported severity. Only reviewed evidence supporting a changed exposure can change that classification.

Collect the inputs that make the colour defensible

The input checklist is deliberately broader than a metric and a dropdown. Record the cycle date, dimension, criterion version, approved baseline reference, current observation, unit and source reference. Add the evidence date, named evidence owner, next review date and maximum acceptable evidence age. Use the same date convention throughout the pack, including working-day calendars where schedule rules depend on them.

Add confidence factors alongside those inputs: is the source direct or an estimate, has the owner confirmed it, and is there contradictory evidence? Record a short explanation rather than an unexplained confidence score. Identify the affected decision, action owner and due date when a result needs attention. Restricted source material can remain in its controlled location; the shared scorecard needs a usable reference, not a copy of sensitive details.

Check evidence before assigning a colour

Agree an evidence-age limit for each dimension. A cost forecast and a deployment readiness check may need different limits. Measure age in calendar days from the evidence date to the cycle cut-off; evidence exactly at the age limit is within it. A source and observation must also be verifiable and unresolved contradictions must be reviewed. Freshness alone does not make evidence usable. Missing, stale or contradictory evidence needs an explicit review state; it must not silently count as green.

Use “Not assessed” for a required dimension whose current evidence or rule is not usable. Keep a last-known favourable assessment in the historical record with its date, rather than presenting it as current. Preserve unresolved adverse facts separately: an open failure does not become merely historical when its evidence passes an age limit. A provisional estimate can still inform discussion if clearly labelled. It should not quietly pass the publication check as a verified observation.

For every open adverse exception, retain an exception reference, fact, last-supported severity, original evidence date and source, owner, closure requirement and current review state. A failed mandatory acceptance check stays Red until a passed retest or other authorised resolution satisfies the acceptance rules. Record closure evidence, date and reviewer; neither an expired evidence date nor a colour override closes the failure. If current evidence is stale, show “Red — open failed acceptance check; current evidence stale”. For a retained Amber with missing current evidence, preserve the Amber fact while showing that the current assessment is Not assessed.

Calculate the dimension colours and the overall result

Apply each agreed rule to usable evidence. Encode Green as 0, Amber as 1 and Red as 2 only to make ordering convenient in a spreadsheet. Those codes do not measure distance, likelihood or progress. Never average them: several green rows cannot cancel the red row that needs a decision. Keep the individual colours beside the overall result.

Use the same exception and completeness rules as the pillar:

  1. Preserve every unresolved adverse exception with its fact, last-supported severity, original evidence date and source, owner, closure requirement and current review state. Evidence ageing alone cannot close or downgrade it. A failed mandatory acceptance check stays Red until a passed retest or other authorised resolution satisfies the acceptance rules; record the closure evidence, date and reviewer. A colour override alone is not closure.
  2. Assess current evidence separately. A required dimension with missing, stale, contradictory or otherwise unusable evidence has a current assessment of Not assessed. Where an exception is retained, show both: for example, “Red — open failed acceptance check; current evidence stale”. For dimension roll-up, use the worse of the retained exception colour and usable current colour; if only one exists, retain that colour. If neither exists, the roll-up state is Not assessed.
  3. If any required dimension is Red, including a retained open Red exception, computed overall is Red. Keep every evidence gap visible and mark the assessment incomplete where required current evidence is unusable.
  4. If no dimension is Red but any required current assessment is Not assessed, computed overall is Not assessed. Retain known Amber facts beside it; do not replace the overall state with Amber.
  5. If all required current assessments are usable, take the worst dimension colour, including retained exceptions. Overall Green requires every required dimension to be Green and no unresolved Amber or Red exception. An empty or undefined required set is Not assessed.

Show completeness separately as Complete or Incomplete. Complete means every required current assessment is usable; it does not mean Green or that all exceptions are closed. Report review state is Ready for review when the assessment is complete, owners have confirmed it, confidence is adequate, required controls/actions are recorded and overrides are valid. Otherwise use Review required. A complete Red assessment can be Ready for review with the exception and response documented. Escalate urgent exceptions while the pack is being checked.

Report review state is an editorial control for the pack, not permission to deliver or an instruction to suppress a material risk.

Worked example: a rehearsal that may slip

Illustrative example only: all names, records and dates in this scenario are fictional. A small service project is preparing a rehearsal. For this example, time is green when the forecast meets the approved rehearsal date, amber when it misses that date but meets the agreed fallback date, and red when it misses the fallback too. These are local teaching rules, not recommended universal tolerances.

At the 24 October 2026 reporting cycle, the approved rehearsal is 2 November and the fallback is 5 November. The current forecast is 4 November, supported by the delivery lead’s 23 October plan update. Time is therefore Amber. Scope is Green because the scope owner confirms the current build matches the approved scope record. Quality is Red because mandatory acceptance check Q-07 failed on 22 October and has no accepted resolution. Its source is the fictional Q-07 test record, owned by the quality lead; closure requires a passed retest reviewed against the mandatory acceptance rules. For this example the maximum evidence age is one calendar day, so the failure evidence is already stale at the 24 October cut-off. Record the stale-evidence warning and missing current retest evidence alongside the retained Red failure. Time, cost, scope and quality are the agreed required dimensions; all other dimensions are excluded by prior agreement with a recorded rationale.

The cost owner has not supplied the current forecast, so cost is Not assessed. The computed result is Red because the unresolved quality exception remains established despite its evidence age; the missing cost forecast and stale quality evidence remain visible. Completeness is Incomplete and report review state is Review required. Nobody can infer that the budget is safe from this result. The pack must state both the quality failure and the outstanding forecast.

The next steps are specific: the quality lead will provide retest evidence by 27 October; the cost owner will confirm the forecast by 26 October; and the sponsor will consider a revised rehearsal approach if the retest fails. A later passed retest, accepted and recorded by the authorised reviewer, can close the quality failure. If cost is still missing and no other Red remains, overall then becomes Not assessed, with the known Amber time result still visible. The reviewer must recompute the whole result and resolve all required evidence gaps before describing the assessment as complete.

Treat overrides as records, not repairs

Show computed and declared status side by side. If someone proposes a different declared colour, record the reason, evidence, authorised approver, approval date and expiry cycle. Keep the original computation intact. An override changes what is declared under an agreed authority; it does not fix a missing input, erase a failed check or update the delivery baseline by itself.

The default declared status should follow the computed result. Where an approved exception allows a different declaration, label it as an override in the narrative. Require a reviewer to check the approval and its continuing validity each cycle. An expired or unsupported override blocks the pack’s readiness check, while any known red exception still requires attention through the agreed escalation route.

Use confidence to direct questions

Low confidence should tell you where to investigate. A supplier promise without a confirmed plan may justify a follow-up question even if the currently entered date meets the rule. Ask what evidence would strengthen or change the assessment. Link the follow-up to the RAID log using the project’s chosen expansion, or to a separate action log, so a narrative concern becomes owned work.

Do not multiply confidence by a colour code to produce a reassuring composite score. Keep the dimensions and the reasons visible. Confidence can be low because a source is old, a forecast is volatile or two owners disagree. Those are different problems with different responses. The worksheet should preserve that distinction instead of making all uncertainty look like one small number.

Turn the result into a useful status paragraph

A concise report states the computed colour, evidence date, main cause, consequence and next decision. For the example: “Computed Red at 24 October: mandatory acceptance check Q-07 failed on 22 October and remains open; its evidence is stale. Cost remains unassessed pending the current forecast. Retest evidence is due 27 October; the sponsor will review the rehearsal approach if the retest fails.” If AI helps draft that paragraph, use the AI project status-report checklist to check every date, owner and claim against the inputs.

When a single number is the wrong answer

Do not compress safety, legal compliance, a mandatory acceptance condition or an irreversible decision into an average. Use the relevant specialist governance and make the unresolved condition explicit. Where programmes combine dissimilar workstreams, show their separate colours and dependencies. The weakest dimension may be a useful escalation signal, but it is not a complete account of business value or the decision required.

Pause automatic classification when the baseline is disputed, criteria no longer fit the decision or evidence cannot support the rule. Resolve the underlying question with the authorised owner. A transparent Not assessed result can be more useful than a polished colour, provided the gap has an owner, deadline and escalation route. Keep unresolved adverse exceptions in the roll-up even while automatic assessment of current evidence is paused. Uncertainty must lead to action rather than disappear from the pack.

Review one reporting cycle before wider use

Run the scorecard alongside an existing report for one cycle and compare the explanations, not just the colours. Ask another person to reproduce each classification from its source and rule. Correct ambiguous boundaries and missing evidence fields, then agree the version to use next time. Return to the RAG criteria pillar when setting or changing thresholds; keep this worksheet focused on evidence and reviewability.