Vertex42 The Excel Nexus

Sensitivity analysis is concerned with the extent of change in a cost analysis resulting from variations in one or more elements of a cost study. It shows the influence of possible changes of significant variables upon profitability. From this analysis, those variables that have a critical effect are identified. Especially important are those variables that might alter a decision when only small changes occur.

The ordinary practice is to make a number of computations of profitability, varying each significant cost element over the most likely range of values. This process can be tedious unless a computer is used. A visual aid, e.g., a plot or graph, depicts the most sensitive variables in a cost study. Managers prefer a graphic illustration rather than tables of numerical values because significant results can be overlooked in tables.

Break-even analysis is a simple form of sensitivity analysis, and is a useful concept that can be of value to managers when a certain level of uncertainty exists. Break-even refers to the point at which operations break even, where income just equals expenses (see Fig. 10.1 for a typical break-even plot). It is widely used for financial studies because it is simple and extracts useful insights from a modest amount of data. In Figure 10.1, the revenue or expenses are plotted as a function of the production rate or production capacity. The production capacity at which the revenue line intersects the total expense line is the breakeven point. Management, of course, wants to do better than break even; therefore, the break-even plots may be used as a profit planning tool, for product pricing, production capacity, incremental costing of equipment, etc. The shutdown point occurs where the revenue line crosses the fixed expense line. This says that if a company cannot make fixed expenses, it should shut down operations. For a short period of time a company may operate between the break-even point and the shutdown point to maintain customers.

Break-even analysis can be used to show, for example, the effect of various selling prices of a product on profit. Figure 10.2 is such a plot. Other possible uses may be to study fixed and variable expenses and production level scenarios.

The slopes at any given point of the total expense and total revenue curves measure the marginal cost (MC) and the marginal revenue (MR), respectively. A line drawn from the origin of a break-even plot to any point on the revenue curve will have a slope equal to the average selling price (total $/units sold). A line drawn from the origin to any point on the expense curve will have a slope equal to the average unit expense (total $/unit produced). If a vertical line intersects both curves at points where the slopes of the two curves are equal, MR equals MC and profits are at a maximum.

In Figures 10.1 and 10.2, straight lines represent revenue and expenses, whereas in actual practice, these might be curves. Figure 10.3 is an example of a more realistic break-even plot.

Richard Strauss [2] developed a method for plotting the results of a sensitivity analysis in which the ordinate is the measure of profitability and the abscissa is

Lower Maximum Upper break-even profit break-even point point

Figure 10.3 A realistic break-even plot.

Lower Maximum Upper break-even profit break-even point point

the change in a variable greater or less than the base case. Where the abscissa crosses the ordinate is the base case value of the return, net present worth, net annual worth, etc. (see Fig. 10.4). This plot is also known as the "spider" plot due to its shape.

The slope of the line on this plot is the degree of change in the profitability resulting from a percentage change in a variable such as selling price or investment. The length of the line represents the sensitivity of the variable and its degree of uncertainty. The plot has been designed so that positive slopes are associated with variable related to income like selling price, sales volume, yields, market share. Negative slopes are related to cost or expense items such as investment, fixed and variable expenses.

In preparing a sensitivity plot, it is advisable to have the base case on a spreadsheet. Then one can determine the effect on profitability by varying the items in the study. One should be aware that variables, such as sales volume are reflected in operating expenses as raw material requirements, general

% Return

% Return

overhead expenses, etc. Numerous scenarios may be prepared from the basic spreadsheet. A plot then may be developed from the spreadsheet results. A sensitivity analysis of the purchase of wire-line trucks and the resulting sales scenarios for a petroleum production application are found in Table 10.1 and Figure 10.5.

Agarwal and Klumpar [3] developed a method of graphically presenting the results of a sensitivity study. The first step is to solve a base case to determine the profitability. Each variable's affect on profitability is calculated and indexed to the same variable and the profitability of the base case. Figure 10.6 is an example

Variable |
% Change |
Decrease in DCFROR percentage points |

Sales |
+ 10 |
+ 2.4 |

volume |

Was this article helpful?

## Post a comment