Question

Create your own example(s) to illustrate the difference between sensitivity analysis, standard deviation and coefficient of variance. Ensure that you explain how these indicators are interpreted.

Answer 1

ANSWER

Sensitivity Analysis is used to understand the effect of a set of independent variables on some dependent variable under certain specific conditions. For example, a financial analyst wants to find out the effect of a company’s net working capital on its profit margin. The analysis will involve all the variables that have an impact on the company’s profit margin, such as the cost of goods sold, workers’ wages, managers’ wages, etc. The analysis will isolate each of these fixed and variable costs and record all the possible outcomes.

Building a Data Table

Let’s say, for example, that you have built a dynamic financial statement model in order to predict future earnings per share (EPS) for your business. Your model is flawlessly constructed and gives you an EPS result of $2.63 for the year 2009. Now, instead of presenting to your client that the answer to the question “What will EPS be in 2009?” is unquestionably going to be $2.63, it makes more sense to present a range of possibilities for 2009 EPS that depend on sensitizing certain assumptions in the model. Let’s look at an actual example below to illustrate our point:

Constructing the Matrix

1. In a cell on the worksheet, reference the formula that refers to the two input cells that we would like to sensitize. In cell D208, we have referenced our EPS for 2009.
2. Type one list of input values in the same column, below the formula. In the example, we have input a range of revenue growth assumptions.
3. Type the second list in the same row, to the right of the formula. In the example, we have input a range of EBIT margin assumptions.
4. Select the range of cells that contains the formula and both the row and column of values. In the example below, you would select the range D208:I214.
5. Hit the keys Alt-D-T on your keyboard. This will pull up the “Data Table” box as shown to the right of the data table, below.Note: This “shortcut” works in both Excel 2003 and 2007, although an alternative would be to hit Alt-A-W-T for the 2007 version, which will direct you to the data table box through the “What-If Analysis” menu.
6. In the Row input cell box, enter the reference to the input cell for the input values in the row. In the example below, you would type cell E35 in the Row input cell box.
7. In the Column input cell box, enter the reference to the input cell for the input values in the column. In the example below, you would type E33 in the Column input cell box.
8. Click OK!

Coefficient of variation

Another way to describe the variation of a test is calculate the coefficient of variation, or CV. The CV expresses the variation as a percentage of the mean, and is calculated as follows:

CV% = (SD/Xbar)100

In the laboratory, the CV is preferred when the SD increases in proportion to concentration. For example, the data from a replication experiment may show an SD of 4 units at a concentration of 100 units and an SD of 8 units at a concentration of 200 units. The CVs are 4.0% at both levels and the CV is more useful than the SD for describing method performance at concentrations in between. However, not all tests will demonstrate imprecision that is constant in terms of CV. For some tests, the SD may be constant over the analytical range.

The CV also provides a general "feeling" about the performance of a method. CVs of 5% or less generally give us a feeling of good method performance, whereas CVs of 10% and higher sound bad. However, you should look carefully at the mean value before judging a CV. At very low concentrations, the CV may be high and at high concentrations the CV may be low. For example, a bilirubin test with an SD of 0.1 mg/dL at a mean value of 0.5 mg/dL has a CV of 20%, whereas an SD of 1.0 mg/dL at a concentration of 20 mg/dL corresponds to a CV of 5.0%.

Standard deviation

The dispersion of values about the mean is predictable and can be characterized mathematically through a series of manipulations, as illustrated below, where the individual x-values are shown in column A.

Column A	Column B	Column C
X value	X value-Xbar	(X-Xbar)2
90	90 - 87.7 = 2.30	(2.30)2 = 5.29
91	91 - 87.7 = 3.30	(3.30)2 = 10.89
89	89 - 87.7 = 1.30	(1.30)2 = 1.69
84	84 - 87.7 = -3.70	(-3.70)2 = 13.69
88	88 - 87.7 = 0.30	(0.30)2 = 0.09
93	93 - 87.7 = 5.30	(5.30)2 = 28.09
80	80 - 87.7 = -7.70	(-7.70)2 = 59.29
90	90 - 87.7 = 2.30	(2.30)2 = 5.29
85	85 - 87.7 = -2.70	(-2.70)2 = 7.29
87	87 - 87.7 = -0.70	(-0.70)2 = 0.49
X = 877	(X-Xbar) = 0	(X-Xbar)² = 132.10

• The first mathematical manipulation is to sum () the individual points and calculate the mean or average, which is 877 divided by 10, or 87.7 in this example.
• The second manipulation is to subtract the mean value from each control value, as shown in column B. This term, shown as X value - Xbar, is called the difference score. As can be seen here, individual difference scores can be positive or negative and the sum of the difference scores is always zero.
• The third manipulation is to square the difference score to make all the terms positive, as shown in Column C.
• Next the squared difference scores are summed.
• Finally, the predictable dispersion or standard deviation (SD or s) can be calculated as follows:

= [132.10/(10-1)]1/2 = 3.83

PLEASE APPRECIATE THE WORK