ArticlePDF Available

Prioritizing risks for the future using Monte Carlo simulation in Microsoft Excel 2007 or higher

  • Kelly Partners LLP


The full article is appended below. Scroll down and click the blue button 'View full text". Widely used risk matrices produced using International Organization for Standardization (ISO) 31000 often convert discrete risks into alphanumeric scores. Typically, the underlying scores of the matrix follow a geometric progression both in terms of likelihood and consequence, yet the matrix layout can have the appearance of an arithmetic progression. As a result, the seemingly simple risk matrix can be problematic to interpret, as the built-in scaling may not be apparent at first glance. This paper outlines how Monte Carlo can be used to gain a deeper, and visual, perspective on best and worst case scenarios and the most likely probability distribution based on collective knowledge and estimation. Publication Name: Internal Auditor, Journal of the Institute of Internal Auditors US CITATION REF: Kelly, CC (2012), "Prioritizing risks for the future', (Internal Auditor), 70(5): 53-57
OctOber 2012 53
Internal audItor
hy did the auditors cross the road? Because
they did it last year.” The internal auditor’s
comfort zone is analyzing objective histori-
cal data. But when establishing a risk-based
plan of future audits under IIA Performance Standard 2010:
Planning, auditors are obliged to perform an annual risk
assessment, considering future subjective expectations held
by the board, management, and others. In the absence of
data, do auditors just accept those future expectations, or can
they subject them to critical analysis?
Although estimating the likelihood and impact of
future risks is subjective, it will not reflect well on the chief
audit executive (CAE) if the organization is sideswiped by
a system failure, significant cost overrun, or fraud while
internal audit’s plan was focused elsewhere. The audit plan
Auditors at
an Australian
transport company
use mathematical
analysis to assist
them in their audit
Christopher Kelly
Prioritizing Risks for the
OctOber 201254 Internal audItor
Prioritizing risks for the future
should demonstrate that the CAE has
thought broadly and deeply about
“unknown unknowns” and engaged
constructively with those who assess
and manage risk.
Widely used risk matrices pro-
duced using International Organiza-
tion for Standardization (ISO) 31000
often convert discrete risks into
alphanumeric scores. Typically, the
underlying scores of the matrix follow
a geometric progression both in terms
of likelihood and consequence, yet
the matrix layout can have the appear-
ance of an arithmetic progression.
As a result, the seemingly simple risk
matrix can be problematic to inter-
pret, as the built-in scaling may not be
apparent at first glance. Furthermore,
to view a five-year
bubble graph used
by an Australian
company to
identify risks.
Monte Carlo is a mathematical technique for creating a probabil-
ity distribution showing what might happen in the future based
on what can be foreseen today. It was not designed for internal
audit nor even risk management; instead, World War II weapons scientists
devised it to predict the probable range of uncertain outcomes when
developing atomic weapons.
To illustrate the concept, suppose your spouse asks whether you can
continue paying the mortgage over the next five years. Although the
future is unknown, you know your future contractual mortgage obliga-
tions, future pay raises are expected to be between -10 percent (worst
case) and +15 percent (best case), income tax will range from 25 percent
to 40 percent, and there could be a 2 percent chance of losing your job
through illness or layoff. Some of the risks are mutually exclusive while
others are interdependent. With inputs like these, Monte Carlo analysis
can help in answering your spouse’s question not with absolute assur-
ance but within a probable range of certainty. The analysis will accom-
modate any further risks you may need to add: Can you also afford next
year’s European vacation? And your child’s education fees?
Monte Carlo takes those multi-year/multi-risk estimates and creates a
probability distribution from thousands of future pseudo-random scenar-
ios. The scenarios are pseudo-random because they are all bounded by the
probability and impact estimations. Therefore, in the mortgage example
Monte Carlo would not predict a “black swan” event such as a lottery
win, unless it had been built into the estimation inputs. The power of the
method is that intelligent discussions about future risks can be held with
those most knowledgeable about probabilities and impacts.
many organizations’ risk matrices are
designed to grade even insignificant
and minor risks. If poorly constructed,
risk matrices can derail management
thinking away from the most critical
risks. For instance, at an Australian
transport company the safety depart-
ment had ignored the risks around
fatal accidents in the mistaken, and
untested, belief that preventive con-
trols were infallible. Instead, the
company’s matrix design gave priority
to frequently occurring minor risks.
The matrix would have been more
meaningful if its users had consulted
diligently with the organization’s risk
experts and its design had empha-
sized more important, rather than less
important, risks.
OctOber 2012 55
Internal audItor
Only 45% of executives are comfortable with how well their
organization’s most critical risks are being managed, according to a recent PricewaterhouseCoopers survey.
indicates individual risks by catego-
rizing them into predefined scaled
boxes, the bubble graph using the
same axes as a risk matrix plots
each individual risk without scaling.
Had it suited audit purposes, audi-
tors could have substituted other risk
dimensions as the y and z axes, such
as environmental, safety, or compli-
ance impact. But for the first version,
financial and reputational impacts
were those of highest concern.
This approach enabled a further
analytical step in the form of a prob-
ability distribution to show the likeli-
hood of best- and worst-case scenarios
in aggregate. To understand the distri-
bution of aggregate risk, it was helpful
to consider one risk at a time. One risk
that emerged during the management
consultations was failure to negoti-
ate with the unions future pay raises
When the transport company’s board
rejected the risk matrix and internal
audit’s plan on the grounds of missing
important risks, auditors turned to
Monte Carlo mathematical analysis to
analyze future risks both individually
and in aggregate (see “Monte Carlo
Explained” on page 54). Getting cred-
ible probability and impact inputs
required a thorough consultation
across the organization’s experts in risk
management, operations, insurance,
treasury, safety, and human resources.
Using the estimates arising from
these meetings, internal audit first
generated a bubble graph showing
the worst-case probabilities (x axis)
and worst-case cost impacts of each
individual risk (y axis), as well as
the reputation impact as the size of
each bubble (z axis). While a matrix
commensurate with board expecta-
tions, which management and audit
estimated could cost up to 5 percent
in excess payroll on an existing annual
payroll of AU $200 million. So the
worst-case scenario would be approxi-
mately AU $200 million x 5 percent
x four years (because the current year
costs were already known) = AU $40
million in today’s dollars. Management
considered the cost could be anywhere
between zero and AU $40 million.
This risk was mitigated by the fact
that the company’s most experienced
negotiators had been assigned to the
union discussions. So a negative out-
come was deemed 50 percent likely.
The probability distribution therefore
reflected the 50 percent likelihood of a
successful (zero) outcome and 50 per-
cent of scenarios with an unfavorable
outcome of variable cost between zero
12 23 60 84 108 132 156 180 204 228 252 276 300 324 348
Pre-mitigation excluding reputation
Post-mitigation excluding reputation
Cumulative post-mitigation excluding reputation
Cumulative pre-mitigation excluding reputation
OctOber 201256 Internal audItor
Prioritizing risks for the future
Probability Distribution — Multi-year
Projection” on page 55). Generating
thousands of scenarios is easy once the
model is set up, even with off-the-shelf
spreadsheet software.
As auditors added more risks,
the likelihood of a zero outcome (the
left tail of the distribution) across all
risks diminished. This was because for
each new risk added, the probability
that no risk occurs became less likely.
For instance, in the union negotiation
example the risk of occurrence versus
nonoccurrence was 50/50. By adding a
second risk with a similar 50/50 likeli-
hood, the probability of a zero outcome
in which neither risk occurs was 50 per-
cent x 50 percent = 25 percent. After
adding a third 50/50 risk, the zero risk
likelihood diminished to 50 percent x
50 percent x 50 percent = 12.5 percent.
The more risks added, the less likely
none of them will occur.
Simultaneously, as auditors added
more risks, the right tail grew toward
its upper limit, which was the possible,
albeit unlikely, scenario that all the risks
would occur at their worst estimated
outcome. So, while the left tail of the dis-
tribution shrank, the right tail grew. This
explains why the distribution as shown in
the “All Risks Probability Distribution
graph stretches out to the right.
Auditors then could give the board
more information about risk than was
available with only a risk matrix. The
probability distribution showed the
worst-case scenarios (i.e., that most of
the risks occurred at their worst cost
estimates), what was likely to occur,
and the best-case scenarios. Because it
and AU $40 million beyond business
plan expectations.
As the management consultations had
supplied credible predictive estimates
of future likelihood and impact ranges
across all known significant risks,
internal audit used the Monte Carlo
methodology to construct a prob-
ability distribution from thousands of
scenarios across all risks in aggregate.
Each scenario was one version of what
the future might bring. To create each
scenario, random numbers were used
in two ways: first, as a random trigger
as to whether or not a risk occurred
based on management’s probability
estimate, and second, as a random
impact between management’s upper
and lower impact estimates. In this
case, impact was described in dollars,
but it just as readily could have been
described as fatalities or injuries. Audi-
tors chose to perform calculations in
today’s dollars with the commitment
to update the analysis at least annually.
For example, one scenario was:
ɅA less-than-ideal union negotia-
tion in year one resulting in AU
$10 million in higher-than-
anticipated labor costs in each of
the subsequent four years.
ɅA labor strike plus adverse cur-
rency movement in year two total-
ing AU $8 million.
ɅA fire with injuries in year three
costing AU $4 million.
ɅNo risk events in year four.
ɅExtreme weather in year five cost-
ing AU $3 million.
The net cost to the organization of
this scenario after insurance recoveries
would be AU $55 million.
To stabilize the distribution,
internal auditors created 10,000 ran-
dom scenarios describing the future
costs the organization might expect
to incur split down both premitiga-
tion and postmitigation (see “All Risks
showed the distribution from best- to
worst-case scenarios and the most
likely spread in between, it was more
meaningful than merely calculating the
average value (expected cost x probabil-
ity for each individual risk) or slotting
individual risks into a risk matrix. The
shape of the distribution was uniquely
based on the probability and cost esti-
mates generated in the management
discussions. Auditors showed aggre-
gate dollar cost as the x axis, which is
intuitively more straightforward when
talking with management than trying
to describe the distribution in standard
deviations. Once the Monte Carlo
method was understood by all parties,
it helped open up insightful discussions
among board members, management,
and auditors around how the future
could be impacted by various risks.
For instance, the S-curve showed
the cumulative probability, which
enabled management to ascertain the
likely maximum cost at a given prob-
ability level. The mean dollar impact of
all risks was where the x-axis intercept
reached the S-curve at 50 percent,
which could be cross-checked by sum-
ming each individual risk multiplied
by its probability to compute average
aggregate risk. Another useful insight
was to look at the upper and lower
ranges of scenarios to determine the
most likely future impact range. From
the graph it was visually clear that most
of the company’s post-mitigation sce-
narios fell between AU $40 million and
AU $130 million. By viewing the graph
side-by-side with the bubble graph,
auditors could see which individual
Internal audit used the Monte Carlo
methodology to construct a probability
distribution of scenarios across all risks.
OctOber 2012 57
Internal audItor
risks had the most impact on the dis-
tribution, such as the AU $84 million
projected maintenance cost overrun.
These risks in turn generated the most
debate. Discussion was focused on what
action management needed to take to
minimize the impacts. Once the model
was set up, it also was useful to study
how the mean changed over time as
risks were re-estimated and as controls
were implemented.
What began as an audit planning exer-
cise developed into something greater.
With insights generated through
Monte Carlo analysis, the transport
company’s internal audit function
was able to have constructive discus-
sions about what management actions,
insurance limits, hedging strategies,
and audits needed priority.
According to one director, it was
the first time the board had debated the
organization’s most politically sensitive
risks. Within six months several under-
managed risks were mitigated, labor
negotiation and projected maintenance
cost overrun risks became standalone
board agenda items, management
changes ensured the best executives were
focused on the critical risks, insurance
coverage was reevaluated, probability
and impact estimates were updated as
controls took effect, and management
pointed to new areas of risk in safety and
engineering that also needed internal
audit analysis. Taken together, these
actions strengthened the link between
internal audit’s program and manage-
ment action in shrinking the impact of
risk on the company.
a partner in consulting firm Kelly & Yang,
located in Newcastle, Australia.
Auditors then could give the board
more information about risk.
TO COMMENT on this article,
EMAIL the author at
Full-text available
If the full article linked below helps your research, please give it a ‘Recommend’. The full thesis can be viewed and downloaded free of charge from the Middlesex University research repository at this link: -----PURPOSE----- The problem the allocator of financial capital has to deal with is that asset selection decisions need to be made today based on uncertain future expectations derived from accounting measurements and estimations produced in the past which are vulnerable to error and creative accounting. The research looks at how this problem has been dealt with in the academic and professional literature and develops a new tool leveraging both quantitative methods and the reflective practitioner’s experiential intuition. -----METHODOLOGY DESIGN----- A qualitative methodology based on real-world case study (Flyvbjerg 2011) and microanalysis (Strauss and Corbin 1998) is used to develop customised reflexive research tools to assess management success in allocating capital, and audit metrics to illuminate techniques used to conceal poor returns. -----FINDINGS----- Returns which failed to reach market indices or inflation were observed in the UK investment trust sector over the past ten years suggesting their customers’ capital lost value in real terms. Although Modern Portfolio Theory has useful insights, strong form Efficient Market Hypothesis is rejected as is the over-reliance on mathematical models most of which have been developed under non-realistic assumptions. Monte Carlo simulation was examined and used alongside experiential intuition (Burke and Miller 1999, Dane and Pratt 2007) to generate insights into future risk management priorities and also as a way of optimising portfolio weighting options. The use of Monte Carlo for risk analysis, while not new in the financial services industry, is less common in industry, which in turn served to generate client work and publication of findings during the research. In carrying out the research, data inquiry limitations and in some cases data, design and formulaic errors were found in the publicly available research databases. Therefore a customised accounting database was designed with which to carry out the real-world case studies, which in turn exposed usage of modified accounting bases, creative accounting (Griffiths 1992) and concealment of earnings fluctuations in the statement of comprehensive income (Athanasakou et al 2011). -----CONCLUSIONS----- A customised accounting research database (CARD) is developed to provide a basis for conducting structured quantitative analysis based on DuPont (Brealey et al 2006), Graham (1976) and my own experientially derived metrics. This quantitative analysis is further supported with experiential intuitive unstructured inquiries in such areas as the likelihood of future returns, debt structuring risks, management orientation and so forth. Monte Carlo is used for estimating probable future outcome distributions and in optimising portfolio weighting. To further reduce the risk of incorrect decisions, a capital allocation policy is developed drawing from both the literature review (mainly Hertz 1964, Modigliani and Miller 1958, Buffett 1977 – 2012, Stiglitz 2010) and my own experiences. At each step in the analysis the practitioner has the opportunity to reflect on the data gathered and to formulate questions needed to address the knowledge gaps arising. The findings expose the care needed when analysing corporate financial data due to the vulnerabilities of financial databases to error as well as the vulnerabilities of published financial data to earnings management (Nelson et al 2002). The tools developed in the project place particular emphasis on data integrity through the use of both existing and new analytical and triangulation formulae.
ResearchGate has not been able to resolve any references for this publication.