The Sensitivity Analysis Tool extends Excel by allowing quick and easy generation and graphing of data sets for a virtually unlimited range of functions.
Sensitivity analysis is the process of observing how an output varies over a range of inputs. Entering these inputs manually can be a tedious and error-prone activity. The Sensitivity Analysis Tool greatly simplifies this task. Simply enter the desired range of values, indicate the input and output cells and hit a button. The Sensitivity Analysis Tool will iterate over the given values and produce a data set containing the output for each value. It will then create a graph so you can see at a glance how varying the input values affects the value of the output.
The tool allows users to model various scenarios and save them for use at a later date.
Using the Sensitivity Analysis Tool
The Sensitivity Analysis Tool is simple to use. Firstly, you need to specify a ranges of values to iterate over. The range is specified by a high value, a low value and a count of the number of points (including the high and low values). You will also need to specify the cell into which these values will be input. This should be an input cell for the formula you are analyzing. For instance, if you were analyzing an option portfolio, you might specify the Volatility input as the X value cell.
If you want to undertake a two variable analysis then click the 'Z Values' checkbox and supply values for the second dimension of the analysis - for instance, continuing the example above you might choose to iterate over the RiskFree Rate for the Z values.
Next, you need to specify the value that will be measured at each iteration. This should be the output of the function being analyzed. For example, the cell containing the calculated option value.
Finally, select where the output should be generated and specify if you want a graph created, then hit the 'Create Data Set' button. The Sensitivity Analysis Tool will run the analysis, generate the set of output values and optionally create a graph.
Copyright 2013 Hedgebook Ltd.