Sensitivity Analysis in Excel using a VBA Macro Plugin
Usage and Reference
The "Sensitivity Analysis Knoll" Add-In for Microsoft Excel is a handy tool that was specifically developed for life cycle cost assessments (LCC / LCCA) in Excel spread sheets. The basic idea of varying model parameters in input cells of the spread sheet and to observe / draw the resulting output changes similarly to the "What-If" functions already provided within Excel has been extended in this Add-In macro for further convenience.
Once the self-installing Add-In has been opened up in Excel, it is installed and appears with its own control buttons in the Add-in button bar. It provides simple single input cell sensitivity analysis with single output cell monitoring as well as complex multi-cell input and multi-cell output analysis runs.
The Add-In allows for menu based single input or multiple input sensitivity analysis of up to 20 inputs and 20 outputs. Inputs can be varied separately (one at a time) or in all combinations. Input and output cells are not bound to one worksheet but can be spread across worksheets within the same file.
It also includes the option to search for zero crossing output values and their corresponding input settings, which is often required for break-even analysis.
You are free to install and use the Add-In privately or commercially, but are requested to include a statement about the usage and a reference to the "Sensitivity Analysis Knoll" Excel Add-In source. Such as:
Reference
In the documentation of your work:
"The work has been carried out using the "Sensitivity Analysis Knoll" Add-In for Microsoft Excel [1]."
In the reference section:
[1] Knoll, Thomas M.; "Sensitivity Analysis Add-In for Microsoft Excel"; URL: https://www.life-cycle-costing.de/sensitivity_analysis/
Download restrictions
Although the Add-In is provided free of charge, it is not meant to be published on any other web page for downloading.
Provide a link to https://www.life-cycle-costing.de/sensitivity_analysis/ instead for direct download of the newest version.
Download Add-In ( Save the downloaded file first to a folder, where it will remain for long (not being moved or deleted) and then double click the file at this chosen location)
Hints:
- If the download happens to store the Add-In as "Sensitivity Analysis Knoll.zip" instead of "Sensitivity Analysis Knoll.xlam", please rename the zip file to xlam. This effect is reported by some users for the Internet Explorer browser.
- Office 2016 / Excel 2016: The execution and thus installation of the Add-In in Excel 2016 fails due to missing execution rights. Right click on the downloaded file, go to preferences and allow execution. The Add-In should now be allowed to install itself. Thanks goes to Laura K. for the solution.
No Guarantee / No Liability
The Add-In is provided free of charge to be used on your own risk. No liability is taken for instance - but not limited - for data loss or damage.
Use the software at your own risk and as precaution, backup any data before use of the add-in.
Installation and Documentation
Please see the Installation and Documentation document.
Use of the Sensitivity Add-In on Excel sheets with user created macros
In order to guarantee the correct operation of sensitivity calculations in Excel sheets with user created macros, please mark the respective input cells of the user created macro to autmatically update themself.
The Microsoft document "How to run a macro when certain cells change in Excel" explains the necessary steps, where the input cells ("KeyCells") are to be adopted to the user specific ones as well as the call to the user created macros needs to be added in the section below ( MsgBox "Cell " & Target.Address & " has changed." ).
Screenshots
 |
 |
|
|
|
|
 |
The business modelling software "STEM" is an excellent tool to model strategic business issues and to create targeted cost and revenue models in a professional way. The software provides generic simulation model elements (such as market, service, ressource, location, function and transformation elements), which allow for an intuitive creation of business models incorporating the technical and dimensional complexity together with the cost, depreciation and revenue figures of the monetary valuation. This way, complex - mainly telecommunication - systems and networks can be modelled and evaluated in a clearly structured and understandable way. It reveals the technical and business dependencies and allows for sensitivity analysis of potentially every model parameter.
From an example model for a mobile network operator rolling out a LTE service in a country, the following screenshots have been taken out from.
For more information, do not hesitate to contact us. |
The modelled demand and cost parameters of that LTE roll-out business model are based on assumptions and forecast values. This naturally includes uncertainty and could potentially lead to wrong modelling results. In order to challenge the model with varying parameters, the sensitivity analysis is used to determine the impact of input parameter changes onto output results. This way, the most influential parameters can be derived and in turn modelled in more rigorous accuracy.
Exemplarily, the parameters for eNodeB capital and maintenance expenditures are varied by +/- 10% of uncertainty, which yields the output as shown in the tornado and time series graph below.
As a second example, a +/- 10% uncertainty in the market size of all roll-out city types varied independently results in the following figures for the input market size and the resulting Net Present Value (NPV).
|
