Submitted by: Submitted by sara2115
Views: 1336
Words: 1324
Pages: 6
Category: Business and Industry
Date Submitted: 05/14/2012 06:47 AM
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...