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
March 2009 – Strategy @ Risk

Strategy @ Risk

Month: March 2009

The Probability of Bankruptcy

This entry is part 3 of 4 in the series Risk of Bankruptcy

In the simulation we have for every year calculated all four metrics, and over the 250 runs their mean and standard deviation. All metrics is thus based on the same data set. During the forecast period the company invested heavily, financed partly by equity and partly by loans. The operations admittedly give a low but fairly stable return to assets. It was however never at any time in need for capital infusion to avoid insolvency. Since we now “know” the future we can judge the metrics ability to predict bankruptcy.

A good metric should have a low probability of rejecting a true hypothesis of bankruptcy (false positive) and a high probability of rejecting a false hypothesis of bankruptcy (false negative).

In the figures below the more or less horizontal curve gives the most likely value of the metric, while the vertical red lines indicate the 90% event space. By visual inspection of the area covered by the red lines we can get an indication of the false negative and false positive rate.

The Z-Index shows an increase over time in the probability of insolvency, but the probability is very low for all years in the forecast period. The most striking effect is the increase in variance as we move towards the end of the simulated period. This is caused by the fact that uncertainty is “accumulated” over the forecast period. However, according to the Z-index, this company will not be endangered inside the 15 year horizon.

In our case the Z-Index correctly identifies the probability of insolvency as small. By inspecting the yearly outcomes represented by the vertical lines we also find an almost zero false negative rate.

The Z-score metrics tells a different story. The Z’’-score starts in the grey area and eventually ends up in the distress zone. The two others put the company in the distress zone for the whole forecast period.

Since the distress zone for the Z-score is below 1.8, a visual inspection of the area covered by the red lines indicates that most of the outcomes fall in the distress zone. The Z-score metrics in this case performs type II errors by giving false negative judgements. However it is not clear what this means – only that the company in some respect is similar to companies gone bankrupt.

If we look at the Z metrics for the individual years we find that the Z-score have values from minus two to plus three, in fact it has a coefficient of variation ranging from 300% to 500%. In addition there is very little evidence of the expected cumulative effect.

The other two metrics (Z’ and Z’’) shows much less variation and the expected cumulative effect. The Z’-score outcomes fall entirely in the distress zone, giving a 100% false negative rate.

The Z’’-score outcome falls mostly in the distress zone below 1.1, but more and more falls in the grey area as we move forward in time. If we combine the safe zone with the grey we get a much lower false negative rate than for both the Z and the Z’ score.

It is difficult to draw conclusions from this exercise, but it points to the possibility of high false negative rates for the Z metrics. Use of ratios in assessing a company’s performance is often questionable and a linear metric based on a few such ratios will obviously have limitations. The fact that the original sample consisted of the same number of healthy and bankrupt companies might also have contributed to a bias in the discriminant coefficients. In real life the failure rate is much lower than 50%!

30/03/2009
Predicting Bankruptcy
This entry is part 2 of 4 in the series Risk of Bankruptcy

The Z-score formula for predicting bankruptcy was developed in 1968 by Edward I. Altman. The Z-score is not intended to predict when a firm will file a formal declaration of bankruptcy in a district court. It is instead a measure of how closely a firm resembles other firms that have filed for bankruptcy.

The Z-score is classification method using a multivariate discriminant function that measures corporate financial distress and predicts the likelihood of bankruptcy within two years. ((Altman, Edward I., “Financial Ratios, Discriminant Analysis and the Prediction of Corporate Bankruptcy”. Journal of Finance, (September 1968): pp. 589-609.))

Others like Springate ((Springate, Gordon L.V., “Predicting the Possibility of Failure in a Canadian Firm”. Unpublished M.B.A. Research Project, Simon Fraser University, January 1978.)), Fulmer ((Fulmer, John G. Jr., Moon, James E., Gavin, Thomas A., Erwin, Michael J., “A Bankruptcy Classification Model For Small Firms”. Journal of Commercial Bank Lending (July 1984): pp. 25-37.)) and the CA-SCORE model ((“C.A. – Score, A Warning System for Small Business Failures”, Bilanas (June 1987): pp. 29-31.)) have later followed in Altman’s track using step-wise multiple discriminant analysis to evaluate a large number of financial ratio’s ability to discriminate between corporate future failures and successes.

Since Altman’s discriminant function only is linear in the explanatory variables, there has been a number of attempts to capture non-linear relations thru other types of models ((Berg, Daniel. “Bankruptcy Prediction by Generalized Additive Models.” Statistical Research Report. January 2005. Dept. of Math. University of Oslo. 20 Mar 2009 <http://www.math.uio.no/eprint/stat_report/2005/01-05.pdf>.)) ((Dakovic, Rada,Claudia Czado,Daniel Berg. Bankruptcy prediction in Norway: a comparison study. June 2007. Dept. of Math. University of Oslo. 20 Mar 2009 <http://www.math.uio.no/eprint/stat_report/2007/04-07.pdf>.)). Even if some of these models shows a somewhat better predicting ability, we will use the better known Z-score model in the following.

Studies measuring the effectiveness of the Z-score claims the model to be accurate with >70% reliability. Altman found that about 95% of the bankrupt firms were correctly classified as bankrupt. And roughly 80% of the sick, non-bankrupt firms were correctly classified as non-bankrupt (( Altman, Edward I.. “Revisiting Credit Scoring Models in a Basel 2 Environment.” Finance Working Paper Series . May 2002. Stern School of Business. 20 Mar 2009 <http://w4.stern.nyu.edu/finance/docs/WP/2002/html/wpa02041.html>. )). However others find that the Z-score tends to misclasifie the non-bankrupt firms ((Ricci, Cecilia Wagner. “Bankruptcy Prediction: The Case of the CLECS.” Mid-American Journal of Business 18(2003): 71-81.)).

The Z-score combines four or five common business ratios using a linear discriminant function to determine the regions with high likelihood of bankruptcy. The discriminant coefficients (ratio value weights) were originally based on data from publicly held manufacturers, but have since been modified for private manufacturing, non-manufacturing and service companies.

The original data sample consisted of 66 firms, half of which had filed for bankruptcy under Chapter 7. All businesses in the database were manufacturers and small firms with assets of <$1million was eliminated.

The advantage of discriminant analysis is that many characteristics can be combined into a single score. A low score implies membership in one group, a high score implies membership in the other group, and a middling score causes uncertainty as to which group the subject belongs.

The original score was as follows:

where:

= Working Capital / Total Assets, = Retained Earnings / Total Assets
= EBIT/ Total Assets, = Sales/ Total Assets
= Market Value of Equity / Book Value of Total Liabilities

From about 1985 onwards, the Z-scores have gained acceptance by auditors, management accountants, courts, and database systems used for loan evaluation. It has been used in a variety of contexts and countries, but was designed originally for publicly held manufacturing companies with assets of more than $1 million. Later revisions take into account the book value of privately held shares, and the fact that turnover ratios vary widely in non-manufacturing industries:
1. Z-score for publicly held Manufacturers
2. Z’-score for private Firms
3. Z’’-score for Manufacturers, Non-Manufacturer Industrials & Emerging Market Credits
The estimated discriminant coefficients for the different models is given in the following table: [Table=3] and the accompanying borders of the different regions – risk zones – are given in the table below. [Table=4] In the following calculations we will use the estimated value of equity as a proxy for market capitalization. Actually it is the other way around since the market capitalization is a guesstimate of the intrinsic equity value.

In our calculations the Z-score metrics will become stochastic variables with distributions derived both from the operational input distributions for sale, prices, costs etc. and the distributions for the financial variables like risk free interest rate, inflation etc. The figures below are taken from the fifth year in the simulation to be comparable with the previous Z-index calculation that gave a very low probability for insolvency.

We have in the following calculated all three Z metrics, even when only the Z-score fits the company description.

Using the Z-score metric we find that the company with high probability will be found in the distress area – it can even have negative Z-score. The last is due to the fact that the company has negative working capital – being partly financed by its suppliers and partly to the use of calculated value of equity – which can be negative.

The Z’’-score is even more somber giving no possibility for values outside the distress area:

The Z’’-score however puts most of the observations in the gray area:

Before drawing any conclusions we will in the next post look at the time series for both the Z-index and the Z-scores. Nevertheless one observation can be made – the Z metric is a stochastic variable with an event space that easily can encompass all three risk zones – we therefore need the probability distribution over the zones to forecast the risk of bankruptcy.

References
20/03/2009
The Risk of Bankruptcy

This entry is part 1 of 4 in the series Risk of Bankruptcy

Investors should be skeptical of history-based models. Constructed by a nerdy-sounding priesthood using esoteric terms such as beta, gamma, sigma and the like, these models tend to look impressive. Too often, though, investors forget to examine the assumptions behind the symbols. Our advice: Beware of geeks bearing formulas. – Warren E. Buffett. ((Buffett, Warren E., “Shareholder Letters.” Berkshire Hathaway Inc. 27 February 2009,. Berkshire Hathaway Inc. 13 Mar 2009 <http://www.berkshirehathaway.com/letters/letters.html>.))

Historic growth is usually a risky estimate for future growth. To be able to forecast a company’s future performance you have to make assumptions on the future most likely values and their event space of a large number of variables, and then calculate both the probability of future necessary cash infusions and if they do not materialized – the risk of bankruptcy.

The following calculations are carried out using the Strategy& Risk simulation model. Such simulations can be carried out on all types of enterprises including the financial sector. There are several models in use for predicting bankruptcy and we have in our balance simulation model implemented two; Altman’s Z-score model and the risk index Z developed by Hannan and Hanweck.

Atman’s Z-score model is based on financial ratios and their relation to bankruptcy found from discriminant analysis. ((Altman, E. I.. “Financial Ratios, Discriminant Analysis and the Prediction of Corporate Bankruptcy.” The Journal of Finance 23(1968): 589-609. )) The coefficients in the discriminant function has in later studies been revised – the Z’-score and Z’’-score models.

Hannan and Hanweck’s probability of insolvency is based on the likelihood of return to assets being negative and larger then the capital-asset ratio. ((Timothy H., Hannan, Gerald A. Hanweck. “Bank Insolvency Risk and the Market for Large Certificates of Deposit.” Journal of Money, Credit and Banking 20(1988): 203-211.)) The Z index has been used to forecast bank insolvency ((Kimball, Ralph C.. “Economic Profit and Performance Measurement in Banking.” New England Economic Review July/August(1998): 35-53.)) ((Jordan, John S.. “Problem Loans at New England Banks, 1989 to 1992: Evidence of Aggressive Loan Policies.” New England Economic Review January/February(1998): 23-38.)), but can profitably be used to study large private companies with low return to assets.

We will here take a look at the Z-index and in a later post use the same data to calculate the Z-scores.

The following calculations are based on forecasts, EBITDA and balance simulations – not on historic balance sheet data. The Z-index is defined as:

Z=

where ROA is the pre-tax return on assets, K the ratio of equity to assets, and s the standard deviation of pre-tax ROA. The Z-index give pr unit of standard deviation of ROA the decline in ROA the company can manage before equity is exhausted and becomes insolvent.

We will in the simulation (250 runs) for every year in the 15 year forecast period – both forecast the yearly ROA and K, and use the variance in ROA to estimate s. For every value of Z – assuming a symmetric distribution – we can calculate the perceived probability (upper bound) of insolvency (p) from:

P =

where the multiplication by (1/2) reflects the fact that insolvensy occurs only in the left tail of the distribution. The relation of p to Z is inverse one, with higher Z-ratios indicating low probability of insolvency.

Since our simulation cover a 15 year period it is fully possible that multi-period losses, thru decline in K, can wipe out the equity and cause a failure of the company.

In year five of the simulation the situation is as follows, the pre-tax return on assets is low – on average only 1.3% and in 20% of the cases it is zero or negative.

However the ratio of equity to assets is high – on average 37% with standard deviation of only 1.2.

The distribution of the corresponding Z-Index values is given in the chart below. It is skewed with a long right tail; the mean is 32 with a minimum value of 16.

From the graph giving the relation between the Z-index and probability of insolvency it is clear that the company’s economic situation is far from being threatened. If we look at the distribution for the probability of insolvency as calculated from the estimated Z-index values this is confirmed having values in the range from 0.1 to 0.3.

Having the probability of insolvency pr year gives us the opportunity to calculate the probability of failure over the forecast period for any chosen strategy.

If it can’t be expressed in figures, it is not science; it is opinion. It has long been known that one horse can run faster than another — but which one? Differences are crucial. ((Heinlein, Robert. Time Enough for Love. New York: Putnam, 1973))

References

16/03/2009
The Risk of Spreadsheet Errors
This entry is part 1 of 2 in the series Spreadsheet Errors

Spreadsheets create an illusion of orderliness, accuracy, and integrity. The tidy rows and columns of data, instant calculations, eerily invisible updating, and other features of these ubiquitous instruments contribute to this soothing impression. The quote are taken from Ivars Peterson’s MathTrek Column written in back in 2005, but it still applies to day. ((Peterson, Ivars. “The Risky Business of Spreadsheet Errors.” MAA Online December 19, 2005 26 Feb 2009 .))

Over the years we have learned a good deal about spreadsheet errors we even have got a spread sheet risk interest group (EuSpRIG) ((EuSpRIG: http://www.eusprig.org/index.htm)).

Audits done shows that nearly 90% of the spreadsheets contained serious errors. Code inspection experiments also shows that even experienced users have a hard time finding errors succeeding in only finding 54% on average.

Panko (2009) summarized the results of seven field audits in which operational spreadsheets were examined, typically by an outsider to the organization. His results show that 94% of spreadsheets have errors and that the average cell error rates (the ratio of cells with errors to all cells with formulas) is 5.2%. ((Panko, Raymond R.. “What We Know About Spreadsheet Errors.” Spreadsheet Research (SSR. 2 16 2009. University of Hawai’i. 27 Feb 2009 . ))

Some of the problems stems from the fact that a cell can contain any of the following: operational values, document properties, file names, sheet names, file paths, external links, formulas, hidden cells, nested Ifs, macros etc. and that the workbook can contain, hidden sheets and very hidden sheets.

Add to this reuse and recirculation of workbooks and code; after cutting and pasting information, the spreadsheet might not work the way it did before — formulas can be damaged, links can be broken, or cells can be overwritten. How many uses version controls and change logs? In addition the spreadsheet is a perfect environment for perpetrating fraud due to the mixture of formulae and data.

End-users and organizations that rely on spreadsheets generally do not fully recognize the risks of spreadsheet errors: It is completely within the realms of possibility that a single, large, complex but erroneous spreadsheet could directly cause the accidental loss of a corporation or institution (Croll 2005) ((Croll, Grenville J.. “The Importance and Criticality of Spreadsheets in the City of London.” Notes from Eusprig 2005 Conference . 2005. EuSpRIG. 2 Mar 2009 .))

A very comprehensive literature review on empirical evidence of spreadsheet errors is given in the article Spreadsheet Accuracy Theory. ((Kruck, S. E., Steven D. Sheetz. “Spreadsheet Accuracy Theory.” Journal of Information Systems Education 12(2007): 93-106.))

EUSPRIG also publicises verified public reports with a quantified error or documented impact of spreadsheet errors. ((” Spreadsheet mistakes – news stories.” EuSpRIG. 2 Mar 2009 .))

We will in the following use publicised data from a well documented study on spreadsheet errors. The data is the result of an audit of 50 completed and operational spreadsheets from a wide variety of sources. ((Powell, Stephen G., Kenneth R. Baker, Barry Lawson. “Errors in Operational Spreadsheets.” Tuck School of Business. November 15, 2007. Dartmouth College. 2 Mar 2009))

Powell et alii settled for six error types:
1. Hard-coding in a formula – one or more numbers appear in formulas
2. Reference error – a formula contains one or more incorrect references to other cells
3. Logic error – a formula is used incorrectly, leading to an incorrect result
4. Copy/Paste error – a formula is wrong due to inaccurate use of copy/paste
5. Omission error – a formula is wrong because one or more of its input cells is blank
6. Data input error – an incorrect data input is used
And these were again grouped as Wrong Result or Poor Practise depending on the errors effect on the calculation.

Only three workbooks were without errors, giving a spreadsheet error rate of 94%. In the remaining 47 workbooks they found 483 instances ((An error instance is a single occurrence of one of the six errors in their taxonomy)) of errors; 281 giving wrong result and 202 involving poor practise.

The distribution on the different types of error is given in the instances table. It is worth noting that in poor practice hard-coding errors was the most common while incorrect references and incorrectly used formulas was the most numerous errors in wrong result.

The 483 instances involved 4,855 error cells, which with 270,722 cells audited gives a cell error rate of 1.79%. The corresponding distribution of errors is given in the cells table. The Cell Error Rate (CER) for wrong result is 0.87% while the CER for poor practise is 1.79%.

In the following graph we have plotted the cell error rates against the proportion of spreadsheets having that error rate (zero CER is excluded). We can se that most spreadsheets have a low CER and only a few a high CER. This is more evident for wrong result than for poor practise.

If we accumulate the above frequencies and include the spreadsheets with zero errors we get the “probability distributions” below. We find that 60% of the spread sheets have a CER giving a wrong result of 1% or more and that only 10% have a CER of 5% or more.

The high percentage of spreadsheets having errors is due to the fact that bottom-line values are computed through long cascades of formula cells. Because in tasks that contain many sequential operations error rates multiply along cascades of subtasks, the fundamental equation for the bottom-line error rate is based on a memoryless geometric distribution over cell errors. ((Lorge, Irving, Herbert Solomon. “Two Models of Group Behavior in the Solution of Eureka-Type Problems.” Psykometrika 20(1955): 139-148. )):

Here, E is the bottom-line error rate, e is the cell error rate and n is the number of cells in the cascade. E indicates the probability of an incorrect result in the last cascade cell, given the probability of an error in each cascade cell is equal to the cell error rate. ((Bregar, Andrej. “Complexity Metrics for Spreadsheet Models.” Proceedings ofEuSpRIG 2004. http://www.eusprig.org/. 1 Mar 2009 .))

In the figure below we have used the CER for wrong result (0.87%) and for poor practise (1.79%) to calculate the probability of a corresponding worksheet error, given the cascade length. For poor practice at a calculation cascade of 100 cells there is a probability of 84% an error and 65 cells it is 95%. For wrong result 100 cells give a probability of 58% for an error and at 343 cells it is 95%.

Now if we consider a net present value calculation over a 10 year forecast period in a valuation problem it will easily have more than 343 cells that with high probability contains error.

This is why S@R uses programming languages for simulation models. Of course will models like that also have errors, but it will not mix data and code, the quality control is easier, it will have columnar consistency, be protected by being compiled, having numerous intrinsic error checks, data entry controls and validation checks (see: Who we are).

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 (American Statistical Association )

References
03/03/2009