Warning: define(): Argument #3 ($case_insensitive) is ignored since declaration of case-insensitive constants is no longer supported in /home/u742613510/domains/strategy-at-risk.com/public_html/wp-content/plugins/wpmathpub/wpmathpub.php on line 65
Modeling – Strategy @ Risk

Strategy @ Risk

Category: Modeling

The Estimated Project Cost Distributions and the Final Project Cost
This entry is part 2 of 2 in the series The Norwegian Governmental Project Risk Assessment Scheme
Everybody believes in the exponential law of errors: the experimenters, because they think it can be proved by mathematics; and the mathematicians, because they believe it has been established by observation. (Whittaker& Robinson, 1967)

The growing use of Cost Risk Assessment models in public projects has raised some public concerns about its costs and the models ability to reduce cost overruns and correctly predict the projects final cost. We have in this article shown that the models are neither reliable nor valid, by calculating the probabilities of the projects final costs. The final cost and their probabilities indicate that the cost distributions do not adequately represent the actual cost distributions.

Introduction

In the previous post we found that the project cost distributions applied in the uncertainty analysis for 85 Norwegian public works projects were symmetric – and that they could be represented by normal distributions. Their P85/P50 ratios also suggested that they might come from the same normal distribution, since a normal distribution seemed to fit all the observed ratios. The quantile-quantile graph (q-q graph) below depicts this:

As the normality test shows, it is not exactly normal ((As the graph shows is the distribution slightly skewed to the right)), but near enough normal for all practical purposes ((The corresponding linear regression gives a value of 0.9540 for the coefficient of determination (R).)). This was not what we would have expected to find.

The question now is if the use of normal distributions representing the total project cost is a fruitful approach or not.

We will study this by looking at the S/P50 ratio that is the ratio between the final (actual) total project cost – S and the P50 cost estimate. But first we will take a look at the projects individual cost distributions.

The individual cost distributions

By using the fact that the individual project’s cost are normally distributed and by using the P50 and P85 percentiles we can estimate the mean and variance for all the projects’ the cost distributions (Cook, 2010).

In the graph below we have plotted the estimated relative cost distribution (cost/P50) for the projects with the smallest (light green) and the largest (dark green) variance. Between these curves lie the relative cost distributions for all the 85 projects.

Between the light green and the blue curve we find 72 (85%) of the projects. The area between the blue and the dark green curve contains 13 of the projects – the projects with the highest variance:

The differences between the individual relative cost distributions are therefore small. Average standard deviation for all 85 projects is 0.1035 with a coefficient of variation of 48%. For the 72 projects the average standard deviation are 0.0882 with a coefficient of variation of 36%. This is consistent with what we could see from the regression of P85 on P50.

It is bewildering that a portfolio of so diverse projects can end up with such a small range of normal distributed cost.

The S/P50 ratio

A frequency graph of the 85 observed ratios (S/P50) shows a pretty much symmetric distribution, with a pronounced peak. It is slightly positively skewed, with a mean of 1.05, a maximum value of 1.79, a minimum value of 0.41 and a coefficient of variation of 20.3%:

At first glance this seems as a reasonable result; even if the spread is large, given that the project’s total cost has normal distributions.

If the estimated cost distribution(s) gives a good representation of the underlying cost distribution, then – S – should also belong to that distribution. Have in mind that the only value we know with certainty to belong to the underlying cost distribution is – S, i.e. the final total project cost.

It is there for of interest to find out if the S/P50 ratio(s) are consistent with the estimated distributions. We will try to investigate this by different routes, first by calculating at what probability the deviation of S from P50 occurred.

What we need to find is, for each of the 85 projects, the probability of having had a final cost ratio (S/P50):

i. less or equal to the observed ratio for projects with S > P50 and
ii. Greater or equal to the observed ratio for projects with S < P50.

The graph below depicts this. The maroon circles give the final cost ratio (S/P50) and their probabilities:

A frequency graph of these probabilities should give a graph with a right tail, with most of the projects close to the 0.5 fractile (the median or P50 value), tapering off to the right as we move to higher fractiles.

We would thus anticipate that most projects have been finished at or close to the quality assurance schemes median value i.e., having had a probability of 0.5 for having had this or a lower (higher) value as final cost ratio, and that only a few would have significant deviations from this.

We will certainly not expect many of the final cost ratio probabilities above the 0.85 percentile (P85).

The final cost probability frequency graph will thus give us some of the completing information needed to assess the soundness of using methods and simulation techniques ending up with symmetric project cost distributions.

Final project cost ratio probability

The result is given in the graph below, where the red bars indicates projects that with probabilities of 85% or more should have had lower (or higher) final cost ratios:

The result is far from what we expected: the projects probabilities are not concentrated at or close to 0.5 and the frequency graph is not tapering off to the right. On the contrary, the frequency of projects increases as we move to higher probabilities for the S/P50 ratios, and the highest frequency is for projects that with high probability should have had a much less or a much higher final cost:
1. The final project cost ratio probabilities have a mean of 0.83, a median at 0.84 and a coefficient of variation of 21%.
2. Of the 85 projects, 51 % have final cost ratios that had a probability of 84% or less of being lower (or higher) and 49% have final cost ratios that had a probability of 85% or more of being lower (higher).
Almost fifty percent of the projects have thus been seriously under or over budgeted or have had large cost over- or underruns – according to the cost distributions established by the QA2 process.

The cumulative frequency distribution below gives a more detailed description:

It is difficult to say in what range the probability for the S/P85 ratio should have been for considering the estimated cost distributions to be “acceptable”. If the answer is “inside the quartile range”, then only 30% of the projects final cost forecasts can be regarded as acceptable.

The assumption of normally distributed total project costs

Based on the close relation between the P50 and P85 percentiles it is tempting to conclude that most if not all projects has had the same cost estimation validation process; using the same family of cost distributions, with the same shape parameter and assuming independent cost elements – ending up with a near normal or normal distribution for the projects total cost. I.e. all the P85/50 ratios belong to the same distribution.

If this is the case, then also the projects final costs ratios should also belong to the same distribution. In the q-q graph below, we have added the S/P50 ratios (red) to the P85/P50 ratios (green) from the first q-q graph. If both ratios are randomly drawn from the same distribution, they should all fall close onto the blue identity line:

The ratios are clearly not normaly distributed; the S/P50 ratios ends mostly up in both tails and the shape of the plotted ratios now indicates a distribution with heavy tails or may be with bimodality. The two ratios is hence most likely not from the same distribution.
A q-q graphn with only the S/P50 ratios shows however that they might be normaly distributed, but have been taken from a different distribution than the P85/P50 ratios:

The S/P50 ratios are clearly normaly distributed as they fall very close onto the identity line. The plotted ratios also indicates a little lighter tails than the corresponding theoretical distribution.

That the two sets of ratios so clearly are different is not surprising, since the S/P50 ratios have a coeficient of variation of 20% while the same metric is 4.6% for the P85/P50 ratios ((The S/P50 ratios have a mean of 1.0486 and a standard deviation of 0.2133. The same metrics for the P85/P50 ratios is 1.1069 and 0.0511.)) .

Since we want the S/P50 ratio to be as close to one as possible, we can regard the distribution of the S/P50 ratios as the QA2’s error distribution.This brings us to the question of the reliability and validity of the QA2 “certified” cost risk assessment model.

Reliability and Validity

The first that needs to be answered is then the certified model’s reliability in producing consistent results and second if the cost model really measures what we want to be measured.
1. We will try to answer this by using the S/P50 probabilities defined above to depict:
  The precision ((ISO 5725-Accuracy of Measurement Methods and Results.)) of the forecasted costs distributions by the variance of the S/P50 probabilities, and
2. The accuracy (or trueness) of the forecasts, or the closeness of the mean of the probabilities for the S/P50 ratio to the forecasts median value – 0.5.
The first will give us an answer about the model’s reliability and the second an answer about the model’s validity:
A visual inspection of the graph gives an impression of both low precision and low accuracy:
- the probabilities have a coefficient of variation of 21% and a very high density of final project costs ending up in the cost distributions tail ends, and
- the mean of the probabilities is 0.83 giving a very low accuracy of the forecasts.
The conclusion then must be that the cost models (s) are neither reliable nor valid:

Summary

We have in these two articles shown that the implementation of the QA2 scheme in Norway ends up with normally distributed project costs.

i.   The final cost ratios (S/P50) and their probabilities indicate that the cost distributions do not adequately represent the actual distributions.
ii.   The model (s) is neither reliable nor valid.
iii.   We believe that this is due to the choice of risk models and technique and not to the actual risk assessment work.
iv.   The only way to resolve this is to use proper Monte Carlo simulation models and techniques

Final Words

Our work reported in these two posts have been done out of pure curiosity after watching the program “Brennpunkt”. The data used have been taken from the program’s documentation. Based on the results, we feel that our work should be replicated by the Department of Finance and with data from the original sources, to weed out possible errors.

It should certainly be worth the effort:

i.   The 85 project here, amounts to NOK 221.005 million with
ii.   NOK 28.012 million in total deviation ((The sum of all deviations from the P50 values.)) from the P50 value
iii.   NOK 19.495 million have unnecessary been held in reserve ((The P85 amount less the final project cost > zero.)) and
iv.   The overruns ((The final project cost less the P50 amount > zero)) have been NOK 20.539 million
v.   That is, nearly every fifth “krone” of the projects budgets has been “miss” allocated
vi.   And there are many more projects to come.

References

Cook, J.D., (2010). Determining distribution parameters from quantiles.
http://biostats.bepress.com/mdandersonbiostat/paper55

Whittaker, E. T. and Robinson, G. (1967), Normal Frequency Distribution. Ch. 8 in The Calculus of Observations: A Treatise on Numerical Mathematics, 4th ed. New York: Dover, pp. 164-208, 1967. p. 179.
08/02/2015
The role of events in simulation modeling
This entry is part 2 of 2 in the series Handling Events

“With a sample size large enough, any outrageous thing is likely to happen”

The law of truly large numbers (Diaconis & Mosteller, 1989)

Introduction

The need for assessing the impact of events with binary[i] outcomes, like loan defaults, occurrence of recessions, passage of a special legislation, etc., or events that can be treated like binary events like paradigm shifts in consumer habits, changes in competitor behavior or new innovations, arises often in economics and other areas of decision making.

To the last we can add political risks, both macro and micro; conflicts, economic crises, capital controls, exchange controls, repudiation of contracts, expropriation, quality of bureaucracy, government project decision-making, regulatory framework conditions; changes in laws and regulations, changes in tax laws and regimes etc.[ii] Political risk acts like discontinuities and usually becomes more of a factor as the time horizon of a project gets longer.

In some cases when looking at project feasibility, availability of resources, quality of work force and preparations can also be treated as binary variables.

Events with binary outcomes have only two states, either it happens or it does not happen: the presence or absence of a given exposure. We may extend this to whether it may happen next year or not or if it can happen at some other point in the projects timeframe.

We have two types of events: external events originating from outside with the potential to create effects inside the project and events originating inside the project and having direct impact on the project. By the term project we will in the following mean; a company, plant or operation etc. The impact will eventually be of economic nature and it is this we want to put a value on.

External events are normally grouped into natural events and man-made events. Examples of man-made external events are changes in laws and regulations, while extreme weather conditions etc. are natural external events.

External events can occur as single events or as combinations of two or more external events. Potential combined events are two or more external events having a non-random probability of occurring simultaneously, e.g., quality of bureaucracy and government project decision-making.

Identification of possible external events

The identification of possible events should roughly follow the process sketched below[iii]:
1. Screening for Potential Single External Events – Identify all natural and man-made external events threatening the project implementation (Independent Events).
2. Screening for Potential Combined External Events – Combining single external events into various combinations that are both imaginable and which may possibly threaten the project implementation (Correlated Events).
3. Relevance Screening – Screening out potential external events, either single or combined, that is not relevant to the project. By ‘not relevant’, we will understand that they cannot occur or that their probability of occurrence is evidently ‘too low’.
4. Impact Screening – Screening out potential external events, either single or combined, that is not relevant to the project. By ‘not relevant’, we will understand that no possible project impact can be identified.
5. Event Analysis – Acquiring and assessing information on the probability of occurrence, at each point in the future, for each relevant event.
6. Probabilistic Screening – To accept the risk contribution of an external event, or to plan appropriate project modifications to reduce not acceptable contributions to project risk.
Project Impact Analysis; modelling and quantification

It is useful to distinguish between two types of forecasts for binary outcomes: probability[iv] forecasts and point forecasts. We will in the following only use probability forecasts since we also want to quantify forecast uncertainty, which is often ignored in making point forecasts. After all, the primary purpose of forecasting is to reduce uncertainty.

We assume that none of the possible events is in the form of a catastrophe. A mathematical catastrophe is a point in a model of an input-output system, where a vanishingly small change in an exogenous variate can produce a large change in the output. (Thom, 1975)

Current practice in public projects

The usual approach at least for many public projects[v] is to first forecast the total costs distribution from the cost model and then add, as a second cost layer outside the model, the effects of possible events. These events will be discoveries about: the quality of planning, availability of resources, the state of corporation with other departments, difficulties in getting decisions, etc.

In addition are these costs more often than not calculated as a probability distribution of lump sums and then added to the distribution for the estimated expected total costs. The consequence of this is that:
1. the ‘second cost layer’ introduces new lump sum cost variables,
2. the events are unrelated to the variates in the cost model,
3. the mechanism of costs transferal from the events are rarely clearly stated and
4. for a project with a time frame of several years and where the net present value of project costs is the decisive variable, these amounts to adding a lump sum to the first years cost.
Thus using this procedure to identify project tolerability to external events – can easily lead decision and policy makers astray.

We will therefor propose another approach with analogies taken from time series analysis – intervention analysis. This approach to intervention analysis is based on mixed autoregressive moving average (ARMA[vi]) models introduced by Box & Tiao in 1975. (Box and Tiao, 1975) Intervention models links one or more input (or independent) variates to a response (or dependent) variate by a transfer function.

Handling Project Interventions

In time series analysis we try to discern the effects of an intervention after the fact. In our context we are trying to establish what can happen if some event intervenes in our project. We will do this by using transfer functions. Transfer functions are models of how the effects from the event are translated into future values of y. This implies to:
1. Forecast the probability p_tthat the event will happen at time – t,
2. Select the variates (response variable) in the model that will be affected,
3. Establish a transfer function for each response variable, giving expected effect (response) on that variate.
The event can trigger a response at time T[vii] in the form of a step[viii] (S_t) (i.e. change in tax laws) or a pulse (P_t) (i.e. change in supplier). We will denote this as:

S_t = 0, when t <T and =1, when t > T

P_t = 0, when t ≠T and =1, when t = T

For one exogenous variate x and one response variate y, the general form of an intervention model is:

y_t = [w(B) / d(B)] x _t-s + N(e_t)

Where B^s is the backshift operator, shifting the time series s steps backward and N(e_t) an appropriate noise model for y. The delay between a change in x and a response in y is s. The intervention model has both a numerator and a denominator polynomial.

The numerator polynomial is the moving average polynomial (MA)[ix]. The numerator parameters are usually the most important, since they will determine the magnitude of the effect of x on y.

The denominator polynomial is the autoregressive polynomial (AR)[x]. The denominator determines the shape of the response (growth or decay).

Graphs of some common intervention models are shown in the panel (B) below taken from the original paper by Box & Tiao, p 72:

As the figures above show, a large number of different types of responses can be modelled using relatively simple models. In many cases will a step not give an immediate response, but have a more dynamic response and a response to a pulse may or may not decay all the way back. Most response models have a steady state solution that will be achieved after a number of periods. Model c) in the panel above however will continue to grow to infinity. Model a) gives a permanent change positive (carbon tax) or negative (new cheaper technology). Model b) gives a more gradual change positive (implementation of new technology) or negative (effect of crime reducing activities). The response to pulse can be positive or negative (loss of supplier) with a decay that can continue for a short or a long period all the way back or to a new permanent level.

Summary

By using analogies from intervention analysis a number of interesting and important issues can be analyzed:
- If two events affects one response variable will the combined effect be less or greater than the sum of both?
- Will one event affecting more than one response variable increase the effect dramatically?
- Is there a risk of calculating the same cost twice?
- If an event occurs at the end of a project, will it be prolonged? And what will the costs be?
- Etc.
Questions like this can never be analyzed when using a ‘second layer lump sum’ approach. Even more important is possibility to incorporate the responses to exogenous events inside the simulation model, thus having the responses at the correct point on the time line and by that a correct net present value for costs, revenues and company or project value.

Because net present values are what this is all about isn’t it? After all the result will be used for decision making!

REFERENCES

Box, G.E.P. and Tiao, G.C., 1975. Intervention analysis with application to economic and environmental problems. J. Amer. Stat. Assoc. 70, 349: pp70-79.

Diaconis, P. and Mosteller, F. , 1989. Methods of Studying Coincidences. J. Amer. Statist. Assoc. 84, 853-861.

Knochenhauer, M & Louko, P., 2003. SKI Report 02:27 Guidance for External Events Analysis. Swedish Nuclear Inspectorate.

Thom R., 1975. Structural stability and morphogenesis. Benjamin Addison Wesley, New York.

ENDNOTES

[i] Events with binary outcomes have only two states, either it happens or it does not happen: the presence or absence of a given exposure. The event can be described by a Bernoulli distribution. This is a discrete distribution having two possible outcomes labelled by n=0 and n=1 in which n=1 (“event occurs”) have probability p and n=0 (“do not occur”) have probability q=1-p, where 0<p<1. It therefore has probability density function P(n)= 1-p for n=0 and P(n)= p for n=1, which can also be written P(n)=pⁿ(1-p) ^(1-n).

[ii] ‘’Change point’’ (“break point” or “turning point”) usually denotes the point in time where the change takes place and “regime switching” the occurrence of a different regime after the change point.

[iii] A good example of this is Probabilistic Safety Assessments (PSA). PSA is an established technique to numerically quantify risk measures in nuclear power plants. It sets out to determine what undesired scenarios can occur, with which likelihood, and what the consequences could be (Knochenhauer & Louko, 2003).

[iv] A probability is a number between 0 and 1 (inclusive). A value of zero means the event in question never happens, a value of one means it always happens, and a value of 0.5 means it will happen half of the time.

Another scale that is useful for measuring probabilities is the odds scale. If the probability of an event occurring is p, then the odds (W) of it occurring are p: 1- p, which is often written as W = p/ (1-p). Hence if the probability of an event is 0.5, the odds are 1:1, whilst if the probability is 0.1, the odds are 1:9.

Since odds can take any value from zero to infinity, then log (p/(1- p)) ranges from -infinity to infinity. Hence, we can model g(p) = log [(p/(1- p)] rather than p. As g(p) goes from -infinity to infinity, p goes from 0 to 1.

[v] https://www.strategy-at-risk.com/2013/10/07/distinguish-between-events-and-estimates/

[vi] In the time series econometrics literature this is known as an autoregressive moving average (ARMA) process.

[vii] Interventions extending over several time intervals can be represented by a series of pulses.

[viii] (1-B) step = pulse; pulse is a 1^st differenced step and step = pulse /(1-B) step is a cumulated pulse.

Therefore, a step input for a stationary series produces an identical impulse response to a pulse input for an integrated I(1) series.

[ix] w(B) = w₀ + w₁B + w₂B² + . . .

[x] d(B) = 1 + d₁B + d₂B² + . . . Where -1 < d < 1.
01/12/2013
Distinguish between events and estimates
This entry is part 1 of 2 in the series Handling Events

Large public sector investment projects in Norway have to go through an established methodology for quality assurance. There must be an external quality assurance process both of the selected concept (KS1) and the projects profitability and cost (KS2).

KS1 and KS2

Concept quality control (KS1) shall ensure the realization of socioeconomic advantages (the revenue side of a public project) by ensuring that the most appropriate concept for the project is selected. Quality assurance of cost and management support (KS2) shall ensure that the project can be completed in a satisfactory manner and with predictable costs.

I have worked with KS2 analysis, focusing on the uncertainty analysis. The analysis must be done in a quantitative manner and be probability based. There is special focus on probability level P50, the project’s expected value or the grant to the Project and P85, the Parliament grant. The civil service entity doing the project is granted the expected value (P50) and must go to a superior level (usually the ministerial level) to use the uncertainty reserve (the difference between the cost level P85 and).

Lessons learnt from risk management in large public projects

Many lessons may be learned from this quality assurance methodology by private companies. Not least is the thorough and methodical way the analysis is done, the way uncertainty is analysed and how the uncertainty reserve is managed.

The analogy to the decision-making levels in the private sector is that the CEO shall manage the project on P50, while he must go to the company’s board to use the uncertainty reserve (P85-P50).

In the uncertainty analyses in KS2 a distinction is made between estimate uncertainty and event uncertainty. This is a useful distinction, as the two types of risks are by nature different.

Estimate uncertainty

Uncertainty in the assumptions behind the calculation of a project’s cost and revenue, such as
- Prices and volumes of products and inputs
- Market mix
- Strategic positioning
- Construction cost
These uncertainties can be modelled in great detail ((But remember – you need to see the forest for the trees!)) and are direct estimates of the project’s or company’s costs and revenues.

Event Uncertainties

These events are not expected to occur and therefore should not be included in the calculation of direct cost or revenue. The variables will initially have an expected value of 0, but events may have serious consequences if they do occur. Events can be modeled by estimating the probability of the event occurring and the consequence if they do. Examples of event uncertainties are
- Political risks in emerging markets
- Paradigm Shifts in consumer habits
- Innovations
- Changes in competitor behavior
- Changes in laws and regulations
- Changes in tax regimes
Why distinguish between estimates and events?

The reason why there are advantages to separating estimates and events in risk modeling is that they are by nature different. An estimate of an expense or income is something we know will be part of a project’s results, with an expected value that is NOT equal to 0. It can be modeled as a probability curve with an expected outcome and a high and low value.

An event, on the other hand, can occur or not, and has an expected value of 0. If the event is expected to occur, the impact of the event should be modeled as an expense or income. Whether the event occurs or not has a probability, and there will be an impact if the event occurs (0 if it doesn’t occur).

Such an event can be modeled as a discrete distribution (0, it does not occur, 1if it occurs) and there is only an impact on the result of the project or business IF it occurs. The consequence may be deterministic – we know what it means if it happens – or it could be a distribution with a high, low and expected value.

An example

I have created an example using a private manufacturing company. They have an expected P&L which looks like this:

The company has a high export share to the EU and Norwegian cost (both variable and fixed). Net margin is expected to fall to a level of 17% in 2018. The situation looks somewhat better when the simulated – there is more upside than downside in the market.

But potential events that may affect the result are not yet modeled, and what impact can they have? Let’s look at two examples of potential events:
1. The introduction of a duty of 25% on the company’s products in the EU. The company will not be able to lift the cost onto the customers, and therefore this will be a cost for the company.
2. There are only two suppliers of the raw materials the company uses to produce its products and the demand for it is high. This means that the company has a risk of not getting enough raw materials (25% less) in order to produce as much as the market demands.
As the table shows the risk that the events occur increase with time. Looking at the consequences of the probability weighted events in 2018, the impact on the expected result is:

The consequence of these events is a larger downside risk (lower expected result) and higher variability (larger standard deviation). The probability of a 0 result is
- 14% in the base scenario
- 27% with the event “Duty in the EU”
- 36% with the event “Raw material Shortage” in addition
The events have no upside, so this is a pure increase in company risk. A 36% probability of a result of 0 or lower may be dramatic. The knowledge of what potential events may mean to the company’s profitability will contribute to the company’s ability to take appropriate measures in time, for instance
- Be less dependent on EU customers
- Securing a long-term raw materials contract
and so on.

Normally, this kind of analysis is done as a scenario. But a scenario analysis will not provide the answer to how likely the event is nor to what the likely consequence is. Neither will it be able to give the answer to the question: How likely it is that the business will make a loss?

One of the main reasons for risk analysis is that it increases the ability to take action in time. Good risk management is all about being one step ahead – all the time. As a rule, the consequences of events that no one has thought of (and thus no plan B is in place) are greater than that of events which have been thought through. It is far better to have calculated the consequences, reflected on the probabilities and if possible put in place risk mitigation.

Knowing the likelihood that something can go horribly wrong is also an important tool in order to properly prioritize and put mitigation measures in at the right place.
07/10/2013
The risk of planes crashing due to volcanic ash
This entry is part 4 of 4 in the series Airports

When the Icelandic volcano Eyafjallajøkul had a large eruption in 2010 it lead to closed airspace all over Europe, with corresponding big losses for airlines. In addition it led to significant problems for passengers who were stuck at various airports without getting home. In Norway we got a new word: “Ash stuck” ((Askefast)) became a part of Norwegian vocabulary.

The reason the planes were put on ground is that mineral particles in the volcanic ash may lead to damage to the plane’s engines, which in turn may lead to them crashing. This happened in 1982, when a flight from British Airways almost crashed due to volcanic particles in the engines. The risk of the same happening in 2010 was probably not large, but the consequences would have been great should a plane crash.

Using simulation software and a simple model I will show how this risk can be calculated, and hence why the airspace was closed over Europe in 2010 even if the risk was not high. I have not calculated any effects following the closure, since this isn’t a big model nor an in depth analysis. It is merely meant as an example of how different issues can be modeled using Monte Carlo simulation. The variable values are not factual but my own simple estimates. The goal in this article is to show an example of modeling, not to get a precise estimate of actual risk.

To model the risk of dangerous ash in the air there are a few key questions that have to be asked and answered to describe the issue in a quantitative way.

Variable 1. Is the ash dangerous?

We first have to model the risk of the ash being dangerous to plane engines. I do that by using a so called discrete probability. It has a value 0 if the ash is not dangerous and a value 1 if it is. Then the probabilities for each of the alternatives are set. I set them to:
- 99% probability that the as IS NOT dangerous
- 1% probability that the ash IS dangerous
Variable 2. How many planes are in the air?

Secondly we have to estimate how many planes are in the air when the ash becomes a problem. Daily around 30 000 planes are in the air over Europe. We can assume that if planes start crashing or get in big trouble the rest will immediately be grounded. Therefore I only use 2/24 of these planes in the calculation.
- 2 500 planes are in the air when the problem occurs
I use a normal distribution and set the standard deviation for planes in the air in a 2 hour period to 250 planes. I have no views on whether the curve is skewed one way or the other. I assume it may well be, since there probably are different numbers of planes in the air depending on weekday, whether it’s a holiday season and so on, but I’ll leave that estimate to the air authority staff.

Variable 3. How many people are there in each plane?

Thirdly I need an estimate on how many passengers and crew there are in each plane. I assume the following; I disregard the fact that there are a lot of intercontinental flights over the Eyafjallajøkul volcano, likely with more passengers than the average plane over Europe. The curve might be more skewed that what I assume:
- Average number of passengers/crew: 70
- Lowest number of passengers/crew: 60
- Highest number of passengers/crew: 95
The reason I’m using a skewed curve here is that the airline business is constantly under pressure to fill up their planes. In addition the number of passengers will vary by weekday and so on. I think it is reasonable to assume that there are likely more passengers per plane rather than fewer.

Variable 4. How many of the planes which are in the air will crash?

The last variable that needs to be modeled is how many planes will crash should the ash be dangerous. I assume that maybe no planes actually crash, even though the ash gets into their engines. This is the low end of the curve. I have in addition assumed the following:
- Expected number of planes that crash: 0, 01%
- Maximum number of planes that crash: 1, 0%
Now we have what we need to start calculating!

The formula I use to calculate is as follows:

If(“Dangerous ash”=0;0)

If(“Dangerous ash”=1;”Number of planes in the air”x”Number of planes crashing”x”Number of passengers/crew per plane”)

If the ash is not dangerous, variable 1 is equal to 0, no planes crash and nobody dies. If the ash is dangerous the number of dead is a product of the number of planes, number of passengers/crew and the number of planes crashing.

Running this model with a simulation tool gives the following result:

As the graph shows the expected value is low; 3 people, meaning that the probability for a major loss of planes is very low. But the consequences may be devastatingly high. In this model run there is a 1% probability that the ash is dangerous, and a 0, 01% probability that planes actually crash. However the distribution has a long tail, and a bit out in the tail there is a probability that 1 000 people crash into their death. This is a so called shortfall risk or the risk of a black swan if you wish. The probability is low, but the consequences are very big.

This is the reason for the cautionary steps taken by air authorities. Another reason is that the probabilities both for the ash being dangerous and that planes will crash because of it are unknown probabilities. Thirdly, changes in variable values will have a big impact.

If the probability of the ash being dangerous is 10% rather than 1% and the probability of planes crashing is 1% rather than 0,01%, as much as 200 dead (or 3 planes) is expected while the extreme outcome is close to 6 400 dead.

This is a simplified example of the modeling that is likely to be behind the airspace being closed. I don’t know what probabilities are used, but I’m sure this is how they think.

How we assess risk depends on who we are. Some of us have a high risk appetite, some have low. I’m glad I’m not the one to make the decision on whether to close the airspace or not. It is not an easy decision.

My model is of course very simple. There are many factors to take into account, like wind direction and – strength, intensity of eruption and a number of other factors I don’t know about. But as an illustration both of the factors that need to be estimated in this case and as a generic modeling case this is a good example.

Originally published in Norwegian.
23/09/2013
The Most Costly Excel Error Ever?
This entry is part 2 of 2 in the series Spreadsheet Errors

Efficient computing tools are essential for statistical research, consulting, and teaching. Generic packages such as Excel are not sufficient even for the teaching of statistics, let alone for research and consulting (ASA, 2000).

Introduction

Back early in 2009 we published a post on the risk of spreadsheet errors. The reference above is taken from that post, but it seems even more relevant today as show in the following.

Growth in a Time of Debt

In 2010, economists Reinhart and Rogoff released a paper, “Growth in a Time of Debt.” Their “main result was:
1. Average growth rates for countries with public debt over 90% of GDP are roughly 4% lower than when debt is low (under 30% of GDP).
2. Median growth rates for countries with public debt over 90% of GDP are roughly 2.6% lower than the when debt is low (under 30% of GDP).
3. Countries with debt-to-GDP ratios above 90 percent have a slightly negative average growth rate (-0.1%).
The paper has been widely cited by political figures around the world, arguing the case for reduced government spending and increased taxes and ultimately against government efforts to boost the economy and create jobs. All based on the papers conclusion that any short-term benefit in job creation and increased growth would come with a high long-term cost.

Then in 2013, Herndon, Ash and Pollin (Herndon et. al., 2013) replicated the Reinhart and Rogoff study and found that it had:
1. Coding errors in the spreadsheet programming,
2. Selective exclusion of available data, and
3. Unconventional weighting of summary statistics.
All this led to serious errors that inaccurately estimated the relationship between public debt and GDP growth among 20 advanced economies in the post-war period. Instead they found that when properly calculated:

That the average real GDP growth rate for countries carrying a public-debt-to-GDP ratio of over 90 percent is actually 2.2 percent, not -0:1 percent as published in Reinhart and Rogoff.

That is, contrary to the Reinhart and Rogoff study – average GDP growth at public debt/GDP ratios over 90 percent is not dramatically different than when debt/GDP ratios are lower.

Statistics and the use of Excel

Even if the coding error only accounted for a small part of the total error, “everyone” knows that excel is error-prone in a way that any programming language or statistical package is not; it mixes data and code and makes you do things by hand that would be automatically done in the other settings.

Excel is good for ad-hoc calculations where you’re not really sure what you’re looking for, or for a first quick look at the data, but once you really start analyzing a dataset, you’re better off using almost anything else.

Basing important decisions on excel models or excel analysis only is very risky – unless it has been thoroughly audited and great effort has been taken to ensure that the calculations are coherent and consistent.

One thing is certain, serious problems demands serious tools. Maybe it is time to reread the American Statistical Association (ASA) endorsement of “Guidelines for Programs and Departments in Undergraduate Mathematical Sciences”

References

Herndon, T., Ash, M. and Pollin, R. (April 15, 2013). Does High Public Debt Consistently Stifle Economic Growth? A Critique of Reinhart and Rogoff, PERI, University of Massachusetts, Amherst. http://www.peri.umass.edu/fileadmin/pdf/working_papers/working_papers_301-350/WP322.pdf

American Statistical Association (ASA) (2000). Endorsement of the Mathematical Association of America (MAA): “Guidelines for Programs and Departments in Undergraduate Mathematical Sciences” http://www07.homepage.villanova.edu/michael.posner/sigmaastated/ASAendorsement2.html

Baker, D. (16 April 2013) How much unemployment did Reinhart and Rogoff’s arithmetic mistake cause? The Guardian. http://www.guardian.co.uk/commentisfree/2013/apr/16/unemployment-reinhart-rogoff-arithmetic-cause

Reinhart, C.M. & Rogoff, K.S., (2010). Growth in a time of Debt, Working Paper 15639 National Bureau of Economic Research, Cambridge. http://www.nber.org/papers/w15639.pdf
22/04/2013
Working Capital Strategy Revisited
This entry is part 3 of 3 in the series Working Capital

Introduction

To link the posts on working capital and inventory management, we will look at a company with a complicated market structure, having sales and production in a large number of countries and with a wide variety of product lines. Added to this is a marked seasonality with high sales in the years two first quarters and much lower sales in the years two last quarters ((All data is from public records)).

All this puts a strain on the organizations production and distribution systems and of course on working capital.

Looking at the development of net working capital ((Net working capital = Total current assets – Total current liabilities)) relative to net sales it seems as the company in the later years have curbed the initial net working capital growth:

Just by inspecting the graph however it is difficult to determine if the company’s working capital management is good or lacking in performance. We therefore need to look in more detail at the working capital elements and compare them with industry ‘averages’ ((By their Standard Industrial Classification (SIC) )).

The industry averages can be found from the annual “REL Consultancy /CFO Working Capital Survey” that made its debut in 1997 in the CFO Magazine. We can thus use the survey’s findings to assess the company’s working capital performance ((Katz, M.K. (2010). Working it out: The 2010 Working Capital Scorecard. CFO Magazine, June, Retrieved from http://www.cfo.com/article.cfm/14499542
Also see: https://www.strategy-at-risk.com/2010/10/18/working-capital-strategy-2/)).

The company’s working capital management

Looking at the different elements of the company’s working capital, we find that:

I.   Day’s sales outstanding (DSO) is on average 70 days compared with REL’s reported industry median of 56 days.

II.   Day’s payables outstanding (DPO) is the difference small and in the right direction, 25 days against the industry median of 23 days.

III.   Day’s inventory outstanding (DIO) on average 138 days compared with the industry median of 23 days, and this is where the problem lies.

IV.   The company’s days of working capital (DWC = DSO+DIO-DPO) (( Days of working capital (DWC) is essentially the same as the Cash Conversion Cycle (CCC). Se endnote for more.)) have on average according to the above, been 183 days over the last five years compared to REL’s median DWC of 72 days in for comparable companies.

This company thus has more than 2.5 times ‘larger’ working capital than its industry average.

As levers of financial performance, none is more important than working capital. The viability of every business activity rests on daily changes in receivables, inventory, and payables.

The goal of the company is to minimize its ‘Days of Working Capital’ (DWC) or which is equivalent the ‘Cash Conversion Cycle’ (CCC), and thereby reduce the amount of outstanding working capital. This requires examining each component of DWC discussed above and taking actions to improve each element. To the extent this can be achieved without increasing costs or depressing sales, they should be carried out:

1.   A decrease in ‘Day’s sales outstanding’ (DSO) or in ‘Day’s inventory outstanding’ (DIO) will represent an improvement, and an increase will indicate deterioration,

2.   An increase in ‘Day’s payables outstanding’ (DPO) will represent an improvement and an decrease will indicate deterioration,

3.   Reducing ‘Days of Working Capital’ (DWC or CCC) will represent an improvement, whereas an increasing (DWC or CCC) will represent deterioration.

Day’s sales- and payables outstanding

Many companies think in terms of “collecting as fast as possible, and paying as slowly as permissible.” This strategy, however, may not be the wisest.
At the same time the company is attempting to integrate with its customers – and realize the related benefits – so are its suppliers. A “pay slow” approach may not optimize either the accounts or inventory, and it is likely to interfere with good supplier relationships.

Supply-chain finance

One way around this might be ‘Supply Chain Finance ‘(SCF) or reverse factoring ((“The reverse factoring method, still rare, is similar to the factoring insofar as it involves three actors: the ordering party, the supplier and the factor. Just as basic factoring, the aim of the process is to finance the supplier’s receivables by a financier (the factor), so the supplier can cash in the money for what he sold immediately (minus an interest the factor deducts to finance the advance of money).” http://en.wikipedia.org/wiki/Reverse_factoring)). Properly done, it can enable a company to leverage credit to increase the efficiency of its working capital and at the same time enhance its relationships with suppliers. The company can extend payment terms and the supplier receives advance payments discounted at rates considerably lower than their normal funding margins. The lender (factor), in turn, gets the benefit of a margin higher than the risk profile commands.

This is thus a form of receivables financing using solutions that provide working capital to suppliers and/or buyers within any part of a supply chain and that is typically arranged on the credit risk of a large corporate within that supply chain.

Day’s inventory outstanding (DIO)

DIO is a financial and operational measure, which expresses the value of inventory in days of cost of goods sold. It represents how much inventory an organization has tied up across its supply chain or more simply – how long it takes to convert inventory into sales. This measure can be aggregated for all inventories or broken down into days of raw material, work in progress and finished goods. This measure should normally be produced monthly.

By using the industry typical ‘days inventory outstanding’ (DIO) we can calculate the potential reduction in the company’s inventory – if the company should succeed in being as good in inventory management as its peers.

If the industry’s typical DIO value is applicable, then there should be a potential for a 60 % reduction in the company’s inventory.

Even if this overstates the true potential it is obvious that a fairly large reduction is possible since 98% of the 1000 companies in the REL report have a value for DIO less than 138 days:

Adding to the company’s concern should also be the fact that the inventories seems to increase at a faster pace than net sales:

Inventory Management

Successfully addressing the challenge of reducing inventory requires an understanding of why inventory is held and where it builds in the system.
Achieving this goal requires a focus on inventory improvement efforts on four core areas:
1. demand management – information integration with both suppliers and customers,
2. inventory optimization – using statistical/finance tools to monitor and set inventory levels,
3. transportation and logistics – lead time length and variability and
4. supply chain planning and execution – coordinating planning throughout the chain from inbound to internal processing to outbound.
We believe that the best way of attacking this problems is to produce a simulation model that can ‘mimic’ the sales – distribution – production chain in necessary detail to study different strategies and the probabilities of stock-out and possible stock-out costs compared with the costs of doing the different products (items).

The costs of never experience a stock-out can be excessively high – the global average of retail out-of-stocks is 8.3% ((Gruen, Thomas W. and Daniel Corsten (2008), A Comprehensive Guide to Retail Out-of-Stock Reduction in the Fast-Moving Consumer Goods Industry, Grocery Manufacturers of America, Washington, DC, ISBN: 978-3-905613-04-9)) .

By basing the model on activity-based costing, it can estimate the cost and revenue elements of the product lines thus either identify and/or eliminate those products and services that are unprofitable or ineffective. The scope is to release more working capital by lowering values of inventories and streamlining the end to end value chain

To do this we have to make improved forecasts of sales and a breakdown of risk and economic values both geographically and for product groups to find out were capital should be employed coming years (product – geography) both for M&A and organic growth investments.

A model like the one we propose needs detailed monthly data usually found in the internal accounts. This data will be used to statistically determine the relationships between the cost variables describing the different value chains. In addition will overhead from different company levels (geographical) have to be distributed both on products and on the distribution chains.

Endnote

Days Sales Outstanding (DSO) = AR/(total revenue/365)

Year-end trade receivables net of allowance for doubtful accounts, plus financial receivables, divided by one day of average revenue.

Days Inventory Outstanding (DIO) = Inventory/(total revenue/365)

Year-end inventory plus LIFO reserve divided by one day of average revenue.

Days Payables Outstanding (DPO) = AP/(total revenue/365)

Year-end trade payables divided by one day of average revenue.

Days Working Capital (DWC): (AR + inventory – AP)/(total revenue/365)

Where:
AR = Average accounts receivable
AP = Average accounts payable
Inventory = Average inventory + Work in progress

Year-end net working capital (trade receivables plus inventory, minus AP) divided by one day of average revenue. (DWC = DSO+DIO-DPO).

For the comparable industry we find an average of: DWC=56+39-23=72 days

Days of working capital (DWC) is essentially the same as the Cash Conversion Cycle (CCC) except that the CCC uses the Cost of Goods Sold (COGS) when calculating both the Days Inventory Outstanding (DIO) and the Days Payables Outstanding (DPO) whereas DWC uses sales (Total Revenue) for all calculations:

CCC= Days in period x {(Average inventory/COGS) + (Average receivables / Revenue) – (Average payables/[COGS + Change in Inventory)]

Where:
COGS= Production Cost – Change in Inventory

Footnotes
27/11/2012