Lesson series

Quantitative Risk Register

Monte Carlo simulation in native Excel Quantitative Risk Register
Write your awesome label here.

The Quantitative Risk Register Workbook

A Monte Carlo model inside a spreadsheet you already have.

Built to the IQRM Quantification Readiness Standard. Two thousand trials, run on native Excel formulas. No macros, no add-in, no licence to buy and nothing for IT to approve. Enter your ranges, press F9, and read a P80 you can put in front of somebody.

GET THE WORKBOOK – £39
Instant download. Opens in Excel, LibreOffice or Google Sheets. 14 day refund.
Approved Saudi Aramco vendor ADNOC approved consultant CPD certified 200+ professionals trained

The gap this closes

Most people never build their first model because of procurement, not ability.

You clean up the register. It is genuinely better. Then somebody asks what the P80 is, and the honest answer is that you would need software your organisation has not bought, on a machine you are not allowed to install it on, with a business case you do not have time to write.

So the register goes back in the drawer, and the next report says three weeks late again.

The first model is the one that changes how you are treated. It should not be gated behind a procurement cycle.

This is a workbook. It opens on any machine that opens a spreadsheet. It samples, it simulates, and it draws the two charts that carry a risk conversation. That is enough to change what you can say in a meeting.

What it produces

Full quantification on cost. Driver ranking on schedule.

OutputCostSchedule
Distribution and histogramYesNo
S-curve, cumulative confidenceYesNo
P10, P50, P80 and P90YesNo
Contingency table against your base estimateYesNo
Tornado, ranked driversYesYes
Share of variance per lineYesYes
Driving path index, merge biasn/aYes

The schedule side deliberately stops at ranking. A defensible completion date needs network logic, calendars and critical path recalculation, and a spreadsheet cannot do that honestly. What it can do is tell you which schedule risks actually matter and which chain is most likely to finish the job late, which is most of the value and none of the fiction.

Inside the workbook

Twelve tabs. Four of them are the engine and you never touch them.

  • Twenty cost lines and twenty schedule linesEach one typed as a risk or an uncertainty. Type it as an uncertainty and the probability disappears, because it is happening in every trial. That single switch is the difference between a range that is honest and one that is too narrow.
  • PERT, triangular or uniform, chosen per lineNot one distribution imposed across the whole register. You pick the shape that matches what you actually know about each item.
  • A register health check on the results tabIt counts your risks and your uncertainties before it shows you a single number. If the register is all risks it says so, in red, and tells you the range below is too narrow to present.
  • Up to five parallel pathsYou name the chains that run to the same milestone. Every schedule line is pointed at one. In each trial the model takes the longest chain, which is how merge bias shows up at all.
  • The driving path indexThe percentage of trials in which each chain finished last. On the worked example the shortest chain on paper drives completion more than a third of the time. That is the effect a qualitative register cannot see.
  • Manual calculation, on purposeRandom sampling in a spreadsheet normally resamples on every keystroke, which makes it unusable. This one is set to manual, so F9 is one clean run of two thousand trials and nothing moves in between.
  • Six worked cost lines and six worked schedule linesReal ones from capital programme work, so every chart is alive the moment you open it. Marked EX and ES. Delete them and it is yours.

What it looks like

Four screens, and you have a number.

Schematics of the four things you will actually look at. Every one of them is drawn by native Excel formulas from the lines you type in, and every one updates when you press F9.

Tab 2 · Cost register

IDRISK OR UNCERTAINTYTYPEP%MINM.L.MAXEX-01RISK35%EX-02UNCERT.—EX-03RISK25%EX-04UNCERT.—EX-05RISK40%EX-06UNCERT.—UNCERTAINTY CARRIES NO PROBABILITY

You type here, and nowhere else.Twenty cost lines and twenty schedule lines. Type a line as an uncertainty and the probability disappears, because it is happening in every trial. That one switch is the difference between an honest range and one that is far too narrow.

Tab 5 · Cost results

P50P80P90

2,000 trials, drawn two ways.The bars are how often each outcome came up. The curve is cumulative confidence, which is where P50, P80 and P90 are read off. A contingency table against your own base estimate sits directly underneath.

Tab 6 · Cost tornado

Ground conditionsLabour productivityThird party interfaceCurrency movementPackage escalationClient scope changeCORRELATION WITH THE OUTCOME

Which three lines are actually moving the number.Ranked by correlation with the total, with each line's share of the variance beside it. This is the chart that turns a register of sixty entries into three things worth a director's attention.

Tab 7 · Driving path index

Path A Civils and structures28.1%Path B Mechanical packages29.8%Path C Electrical and instrumentation7.0%Path D Permits and utilities35.1%

Merge bias, made visible.How often each chain finished the job last. On the worked example the shortest chain on paper drives completion more than a third of the time. No qualitative register can show you that.

The workbook arrives with six worked cost lines and six worked schedule lines already in it, so all four of these are alive the moment you open the file. Delete them and it is yours.

What it will not do

Read this before you decide it is worth thirty nine pounds.

There is a tab in the workbook called "read before presenting". It is not a disclaimer. It is the part that keeps you out of trouble, and it says this.

No correlation

Every line is sampled independently. Where two lines share a common cause, bad ground driving both civils cost and piling duration, the true range is wider than this shows.

No network

Paths, not logic. No lags, no calendars, no resource levelling, no critical path recalculation. Which is why there is no completion date in it.

No parameter library

It asks you for your ranges and will not suggest any. A tool that invents a range produces something that looks like analysis and is not.

No conclusion

It gives you a distribution and a ranking. What you do about the top three drivers is the part that is worth money, and that part is still yours.

Where this stops and the next thing starts

Correlation, distribution selection, tail behaviour, merge bias across a real network and how to defend an output in front of a programme board are taught properly in the QRM Professional Programme.

Turning your existing reporting into a maintained parameter library, so that ranges stop being opinions, is the Risk Data Engine. That is a build, not a download.

This workbook is the step in between, and it is honest about being that.

Who this is for

People who need a number this month.

  • You have a register and no softwareAnd you are tired of that being the reason nothing quantitative ever gets produced.
  • You want to see the mechanicsEvery formula is visible. Nothing is hidden in a macro. If you want to understand how sampling actually works, open the trials tab and read it.
  • You need to make a case internallyA contingency table against your own base estimate, with a P80 and a tornado, is a much better argument for buying real software than asking for it.
  • You are teaching someone elseThe register health check and the driving path index make the two arguments that are hardest to make in words.

Not for you if you need a completion date out of it, or if you are running a programme large enough that a spreadsheet model would be the wrong answer. In that case the conversation is a QSRA engagement, not a download.

Two ways to get this

The workbook on its own, or the workbook taught.

IQRM sells two things with almost the same name, and the difference matters, so here it is plainly.

This page. The Workbook.

The file. Twenty cost lines, twenty schedule lines, 2,000 trials, every formula visible.

You open it, enter your ranges and read the output. Nothing is explained to you.

For people who already know what a P80 is.

Quantitative Risk Register. The course.

Two and a half hours of recorded teaching, a presentation, the written guide, a review and a certificate of completion, with the workbook included.

Same tool, with somebody walking you through why each part of it does what it does.

For people who want it explained.

Buy the workbook here and you are not locked out of the course. Everyone who owns the course already has this workbook, because it was issued to them as a free update.

The Quantitative Risk Register Workbook

£39

One payment. One file. Use it on every project you work on, for as long as you have Excel.

RefundFourteen days. Build one model with it, and if you cannot say something at your next review that you could not have said before, reply to the receipt and I will refund it.
GET THE WORKBOOK – £39
Instant download. Licensed to you for internal use.

Questions

Before you buy.

Does it need macros or an add-in?
No. It is native formulas, start to finish. There is nothing to enable, nothing to install and nothing for IT to approve. It will open on a locked down corporate machine.
Will it work in Google Sheets or LibreOffice?
Yes. The formulas are deliberately restricted to functions both support. Charts render slightly differently outside Excel, and the numbers are identical.
Two thousand trials. Is that enough?
For P50 and P80, comfortably. P90 will move a little between runs because the tail is where sampling error lives, and the workbook says so on the results tab. Press F9 a few times and you will see the movement yourself, which is worth understanding before you quote a P90 to anybody.
Why is there no schedule P80?
Because it would not be defensible. Schedule confidence needs a logic linked network that recalculates the critical path in every trial. A spreadsheet cannot do that, and anything that claims otherwise is showing you a number with nothing behind it. The tornado and the driving path index are what a spreadsheet can honestly produce, so that is what it produces.
Can I add more than twenty lines?
The grid is built for twenty each side, and twenty modelled lines is more than most first models carry. If you need more, the structure extends by copying down, and by that point you are close to needing purpose built software anyway.
What ranges do I put in?
Your own. The workbook will not suggest any and that is deliberate. Where the ranges come from, and how to find them in reporting you already produce, is a separate piece of work.
Do I need the Quantification Readiness Standard first?
Not technically, and in practice it helps a great deal. The workbook is built to the same standard, so a register that has been through the audit drops straight in without being rewritten again. This workbook models what you feed it. If the register feeding it is ninety percent risks and no uncertainty, the range it produces will be too narrow and it will tell you so on the results tab.
GET THE WORKBOOK – £39
Created with