Skip to content
Jonathan Ryan RichardCase study
All cases How I Work
Resourcing & analytics

An excess-cost algorithm, and the dashboard I deployed it on

Six teams reported how stretched they were six different ways, and three hand-built monthly reports disagreed with each other. I wrote an algorithm that turns a target profit margin into a budget, and any overspend into a number of people, then built and deployed the dashboard it runs on.

RolePMO Executive · algorithm design, build, deployment
ScopeSix delivery teams, 100+ staff visible
ToolsPython, pandas, Dash/Plotly, server deployment
New to these terms? Plain-English key
GPM (gross profit margin)
The share of revenue left after the direct costs of delivering the work.
Allowable cost
The most a project can cost and still hit its target margin.
Excess headcount
The overspend expressed as a number of people, so a resourcing decision can act on it.
Rate card
A price list giving every person a cost by grade and country of origin.
YTD
Year to date: the actual figures so far this financial year.
4Reports consolidated into one deployed app
−50%Manual analysis effort removed
100+Staff visible on the live dashboard
MonthlyActuals and forecast refresh cycle
Why the old view failed

Everyone knew cost was too high. Nobody could say by how much.

Resourcing decisions ran on advocacy: the team that argued most persuasively about being stretched tended to get the next hire.

Not comparable

Teams calculated utilisation on different bases. Leave, support duty and internal work were treated inconsistently, so a high number and a low number could describe the same reality.

Not forward-looking

Only closed actuals were counted, so a team was judged on what had already happened while the commitments that would decide the year sat outside the measure.

Not actionable

Even where an overrun was visible in ringgit, nothing converted it into heads, grades or origins, so the finding never became a resourcing instruction.

Not current

Every scenario question meant a new manual extract and a wait of days, so scenarios were rarely asked and almost never compared.

The model, live

The algorithm, then the view it ships in.

Set a target margin and the model works out the most the project can cost. Whatever it costs beyond that is the overspend, shown in ringgit and as a number of people.

Try this: Pull the target margin down to 20% and watch the overspend disappear.

Revenue · YTD + forecast
Actual cost · YTD + forecast
Excess costover the allowable budget
Excess headcountthe same overspend, in people
More figures

On the dashboard
On the dashboard

Built and deployed in-house, refreshed monthly.

Forecasted GPM

The same figures as the dashboard shows them: one source, one period, four views (performance, forecast, resources, optimisation).

Illustrative model · figures anonymised.
Approach

Work back from the target margin, then ship it where people can use it.

Step 01

Invert the margin target

Margin is reported after the fact, so the algorithm runs it backwards: allowable cost = revenue × (1 − target margin). Cost becomes a budget you can be measured against before the year closes.

Step 02

Cost the forecast off a rate card

Every person is priced from their grade and country of origin against a fixed manday month, summed into a monthly burn rate and run to year end, beside the secured and unsecured revenue forecast.

Step 03

Refresh monthly

Each month actuals replace the forecast they supersede and the forward view is recalculated, so the measure moves with the portfolio.

Step 04

Express the gap in heads

Excess cost, divided by the cost of a head over the remaining months, becomes excess headcount: a number a resourcing conversation can act on directly.

Step 05

Build the dashboard and deploy it

I built it in Python and deployed it to the server myself: four views over one reconciled source, with hires, leavers, grades and origins as inputs, so a scenario is answered in the meeting.

What changed

Before, and after.

BeforeAfter Six incomparable utilisation figuresOne algorithm: allowable cost from target GPM Closed actuals only, always in arrearsActuals plus forecast, refreshed monthly Forward cost estimated by feelBurn rate priced per head from the rate card Three hand-built monthly reportsOne dashboard, built and deployed in-house Scenario questions became data requestsScenarios answered live in the meeting
“A number nobody can interrogate is a number nobody will act on.”
What I took from it
Expose the fragile assumption rather than burying it. The forecast horizon and the origin mix are the weakest inputs, so both are controls a reviewer can move.
Define the measure in the unit the decision runs in. Ringgit of overrun starts a debate; 29 heads of overrun ends one.
An algorithm nobody can reach is a document. Building and deploying the dashboard myself is why the measure is used monthly rather than quoted once.
Other case studies
Quality diagnosticsDefect Escape Rate: ending the guesswork in Exco reviewsKPI designFrom SLA scores to DPMR: measuring quality, not the clock
Quality diagnostics

Defect Escape Rate: ending the guesswork in Exco reviews

Quality in executive review meetings ran on phrases like “we think this might be a testing issue.” The missing piece was not another count of bugs. It was a diagnosis: did the problem come from how the software was built, or from how it was tested?

RolePMO Executive · proposed and implemented
AudienceExco, Project Directors, Head of PMO
ToolsJIRA, JQL, Python, Excel, PowerPoint
Repository: PMO-Quality-Transformation ↗
New to these terms? Plain-English key
Defect
A fault in the software.
Exco
The executive committee that reviews project health.
VSIT
Vendor System Integration Testing: the vendor’s own testing of how the parts of the system work together. A defect found here was caught in test.
SIT/UAT
System Integration Testing and User Acceptance Testing: the later testing stages. A defect found here slipped past VSIT, so it escaped.
Test cases per man-day
How much testing is done for each day of tester effort. The standard here is 7.
90%Project teams on standardised defect definitions
15+ hrsManual collection removed each month
40Historical projects analysed for thresholds
4Phases from chaos to diagnostic
Why the old view failed

Why “we think” was not good enough.

Executive decisions were being taken on inference, with no metric able to settle the disagreement.

Inconsistent

Different PMs reported different numbers for comparable situations because the classifications were understood differently across teams.

Undiagnostic

Comparing defects caught in test against those found later raised the question but could not answer it: many late defects could mean weak testing or a weak build.

Manual

Data was collected by email each cycle: 15+ hours a month of chasing, and a pack that was out of date the moment it was assembled.

The model, live

Run the diagnostic yourself.

Set how many defects were caught in testing and how many escaped. The escape rate lands in a colour band, and the diagnosis changes with how much testing was done.

Try this: Drag test cases per man-day below 7 and watch the diagnosis flip from a build problem to a testing problem.

Escape rate
Diagnosis

How the rate is calculated

DER = escaped ÷ (caught + escaped) × 100

In words: the share of all defects that escaped testing and only surfaced later.

Illustrative model · figures anonymised.
Approach

Standardise, introduce the missing diagnostic, then benchmark it on real data.

Phase 01

Standardise the definitions

I trained project managers on what each defect classification actually means and corrected where team practice differed from the standard. Tracking moved into JIRA with shared definitions, reaching 90% of teams.

Phase 02

Introduce Defect Escape Rate

DER is the share of all defects that escaped testing and only surfaced later. It uses data already collected, so adoption cost nothing. Read against how much testing was done, it points at the build or at the testing.

Phase 03

Benchmark against 40 projects

I analysed 40 past projects to see the normal range, then agreed bands with the Head of PMO: green below 40%, amber 40 to 50%, red above 50%, each with a defined action.

Phase 04

Automate the reporting

Dashboards built on JIRA data replaced the email chase, cutting preparation from 15+ hours a month to around two, and letting teams see their own number before Exco did.

What changed

Before, and after.

BeforeAfter “We think this might be a testing issue”“DER of 65% indicates a testing effectiveness issue” Six competing readings of the same defectOne taxonomy, 90% of teams reporting on it 15+ hours a month collecting data by emailJQL dashboards, around two hours a month No trigger for Exco interventionGreen, amber and red bands with defined actions
“Credibility came from bringing data to the conversation, not from years of experience.”
What I took from it
Use the data the organisation already collects. A metric that needs new inputs will not be adopted, however elegant it is.
One number rarely diagnoses anything. DER only works read against coverage. Pairs of metrics beat single ones.
Fix the definitions before building the dashboard. The other order looks faster and is not.
Other case studies
Resourcing & analyticsAn excess-cost algorithm, and the dashboard I deployed it onKPI designFrom SLA scores to DPMR: measuring quality, not the clock
KPI design

From SLA scores to DPMR: measuring quality, not the clock

Software quality was scored on how fast problems were fixed, not on how many problems there were. A team could fix quickly, keep producing defects, and still score well. The replacement counts defects per million ringgit (RM) of project cost.

RolePMO Executive · KPI design, P&C context
CollaborationHead of CPMO set the improvement target
ToolsExcel, descriptive statistics, sensitivity testing
Repository: Production-Defect-KPI-Redesign ↗
New to these terms? Plain-English key
SLA
Service level agreement: a promised time to fix a problem. Meeting it counted as good quality.
DPMR
Defects per million ringgit: production defects divided by project cost in millions.
Benchmark
The target a project is compared against.
R²
How well one number predicts another, from 0 (not at all) to 1 (perfectly).
CPMO
Central Project Management Office.
DPMRDefects per million ringgit, the final metric
−10%Improvement built into the benchmark
1 to 5Quality score, explainable in one sentence
5Methods tested and rejected before DPMR
What the redesign had to satisfy

A metric that measured speed and called it quality.

A fast fix on a system that kept producing defects still scored well, and client-set deadlines could make a late fix count as on time. Three requirements, all at once:

Fairness

It had to reflect defect performance rather than response speed, and compare projects of very different sizes on the same axis.

Adoptability

Simple enough for organisation-wide use. A metric that needs a footnote does not get quoted correctly.

Defensibility

It had to be explainable and defensible in front of Exco and to the project being scored, including when the score is bad.

The model, live

Score a project under both lenses.

The same project, scored two ways. The SLA lens only counts how many fixes met the deadline. The DPMR lens compares defects with project cost, against a target.

Try this: Raise production defects to 60. The SLA score stays put; the DPMR score drops.

SLA lens
DPMR lens

How the scores are given

SLA lens: 5 = 95% or more of fixes on time, 4 = 90%, 3 = 80%, 2 = 70%, 1 = below 70%.

DPMR lens: 5 = at most 60% of the target, 4 = up to 85%, 3 = at the target, 2 = up to 140%, 1 = above 140%.

Illustrative model · figures anonymised.
Approach

Iterative, and willing to throw work away.

Step 01

Challenge the assumption

The working idea was that bigger projects naturally have more defects, so project value could be the benchmark. Plotting three financial years gave R² = 0.25: some large projects were clean, some small ones were not. Project value predicts nothing, and the simple version died there.

Step 02

Test candidates against real data

Four statistical methods were tried and dropped. Each failed a practical test: could a project manager set a target, and could it be explained in a review?

Raw defect countsProject size does not predict defects, so it cannot be the yardstick
Z-score / bell curveAssumes defects spread evenly around an average. Ours are lopsided: most projects have few, a handful have many
Ranking on the curveEach project is graded against the others that year, so the same result could pass one year and fail the next, and managers could not set a target in advance
Grouping by sizeSize bands gave fuzzy targets, and projects kept moving between bands
MAD scoreStatistically sound, but nobody could explain it in a review or turn it into a 1 to 5 score
Step 03

Normalise by cost

DPMR = production defects ÷ project cost in millions of RM. Cost is already agreed, audited and independent of negotiation, and testing confirmed the score stayed stable across project sizes.

Step 04

Set an aspirational benchmark

Benchmarks came from the historical average. Then the Head of CPMO made a strategic call: build in a 10% improvement, so target = average × 0.9. That turned a descriptive metric into one that pushes for better.

Step 05

Wrap it in a 1 to 5 score

Managers want a rating, not a rate. Bands around the target produce a 1 to 5 quality score a project can explain without a statistician.

What changed

Before, and after.

BeforeAfter Time-based SLA scoringDPMR, defects per million ringgit Response speed rewardedDefect prevention rewarded Historical baseline acceptedBenchmark set 10% below the average Management frustrated with the metricManagement confident enough to decide on it
“Statistics were used to validate decisions, not to dictate them. It is not about the most sophisticated model. It is about the right model that actually gets used.”
What I took from it
Simple usually beats complex. The most statistically defensible method was the least defensible in the room, and knowing why each option was dropped is what made the final metric defensible.
Test the assumption before designing around it. “Bigger projects have more defects” felt obvious enough to build on, and R² = 0.25 saved a year of arguing about the wrong benchmark.
A benchmark is a policy choice, not just an average. The 10% adjustment is where analysis handed over to leadership, correctly.
Other case studies
Resourcing & analyticsAn excess-cost algorithm, and the dashboard I deployed it onQuality diagnosticsDefect Escape Rate: ending the guesswork in Exco reviews
← All case studies Full resume →