Sensitivity Analysis in Excel

Sensitivity Analysis in Excel

Sensitivity analysis is the study of how the uncertainty in the output of a model (numerical or otherwise) can be apportioned to different sources of uncertainty in the model input. Under a given set of assumptions, if an input parameter results in drastic variation to outcome while other variables keep unchanged, the parameter is said to be highly sensitive, and vice versa. Sensitivity analysis can be very useful for management accountants to plan and control production and operating system. For example, in budgeting process, material cost, labour cost, freight cost, scrap cost, depreciation costs and many other costs are incurred in inventory making process. Sensitivity analysis can help accountants to testing which of these costs is the most sensitive variable of cost of goods.

Generally, sensitivity analysis can be used for a range of purposes, including:

1. Support decision making or the development of recommendations for decision makers (e.g. testing the robustness of a result);

2. Enhancing communication from modelers to decision makers (e.g. by making recommendations more credible, understandable, compelling or persuasive);

3. Increased understanding or quantification of the system (e.g. understanding relationships between input and output variables); and

4. Model development (e.g. searching for errors in the model).

Performing Sensitivity Analysis in Standard Excel

Data Table

Here is the case of one variable table to perform sensitivity analysis. Use Excel's Data Table command to perform sensitivity analysis for ranges of values of a model input, not just specific points.

(1) Enter a list of input values in a column (cells E3:E11).

(2) Enter a reference to an output formula (=C13) in cell F2

(3) Highlight the entire table (E3:F13)(Note: cell E3 must be blank).

(4) Choose Data>Analysis>What-if>Data table, select C7 (variable) in column input cell.

Then excel...