Introduction
We live in a world where we are faced with having to make decisions where the outcome has an uncertainty. Understanding and evaluating the uncertainty can often help us to make the best decision. Any decision that we take has an element of politics in it. This means that we often think about the consequences of our decisions on human alife and society in general before making them. This important but daunting task of identifying, understanding and assessing uncertainty is generally called risk analysis (Vose, 2008). There are many tools and frameworks which can be used for risk analysis.
Performing analysis on a model is a proven method in understating uncertainty of a situation, problem or a project. So the idea of performing a series of experiments on a model will give the answers we are looking by saving time, money, effort and sometimes risks to our valuable lives (Kwak & Ingall, 2007). One such tool we can use to perform risk analysis is what-if analysis.
What-if analysis provides you with a quantitative analysis of risks on a given model. It identifies the specific threats and hazards by questioning what could go wrong and judging the likelihood and impact of those situations (Ayyub, 2003). The ease of use and flexibility of what-if analysis makes it one of the most popular quantitative risk analysis tools.
This paper is aimed at discussing the process of risk analysis through what-if analysis. It attempts to investigate the limitations and issues associated with what-if analysis and justify the importance of it as a quantitative risk analysis tool. We will also discuss a basic model created using excel and perform analysis on this model with excel solver tool.
What is Risk and what is a model?
Risks are everywhere we look. It has been there since the beginning and evolved into an issue of growing concern both to individuals and the society. So it is imperative to identify it and manage it carefully especially for decision makers to prevent catastrophic situations from happening. Risks can have many meaning hence there are many definitions. It is most commonly defined as an event of uncertainty and if it occurs will have a negative or a positive effect a particular situation. Kaplan and Garrick (1981) definition to risk is the combinations of the answers to the following questions: what can go wrong, how likely and what are the consequences?
There maybe different views on risk according to the perspective of the observer. What may be perceived as a small risk to someone might have a greater impact on someone else life or livelihood. However the difference between the real risk and the perceived risk can be either minimal or can have huge similarities (Ansell et al, 1992). Identifying risks are important during a risk assessment process. Risk modelling can assist immensely during this process.
A Model can be described as mathematical or statistical representation of a system or a scenario most likely created with assistance of computer software or an engineering product. This is extremely popular in areas such as finance and accounting as it can be used heavily in predicting profits and break even points with ease (Nadler et al, 2009). Success of a risk model may depend on finding the right balance between either incorporating all available data or choosing the few critical factors that matters to a particular situation. Also you need to consider whether to go with statistical data or expert judgment when considering facts.
The real world situations are much more complicated so capturing them in a model is a daunting task. Properly constructed risk model can come close to the real thing, but at the end of the day it is only a model. So care should be taken when relying heavily on a model.
Microsoft excel can be used as a basis for creating a good model of a system. Many tools such as what-if analysis through solver and Monte Carlo analysis can work well with an excel model as a base (Kwak et al, 2007).
What is what-if analysis?
Some risk is involved in everything that we do in today’s competitive environment. However with proper risk analysis and risk management we are able to identify these risks and take precautionary actions. There are many methods which can be used in effective risk an analysis. “what-if analysis” is one such popular method most people use.
What-if analysis is a flexible risk analysis tool which can be used for quantitative analysis of risk through a model designed in Microsoft Excel through an add-in called solver. It involves generating values for the probabilistic inputs and computing the resulting value for the output which can be used to generate either the worst case or the best case scenario (Harvey, 2006). These outcomes can create the platform for modelling risks which can be used to identify the risks with most potential impact on a project.
What-if analysis which is sometimes called as sensitivity analysis can be helpful in determining how sensitive a system or a project is to changes to its operating conditions. Just imagine we are simulating the operations of a bank. Once the simulation model is developed we can execute it with number of changing values which are known as what-if questions. An example of a what-if question is what will happen if we double the number of tellers in the main branch when business increased by 10%? What would be the average queue length be? There are many advantages to model based what-if analysis. For example we can run many such what-if questions without having to implement a physical system. This has the potential to save money and time. Also the fact that it is built in to a popular application such as Microsoft Excel will help in reducing the complexity of the software hence making it easy to learn and use even for novice users.
Excel based what-if analysis can be used to choose the project with best returns with lowest risk from a list of projects. This is heavily used by many due to its ease of use and cost effectiveness (Albright et al, 2008).
Solver example and explanation
Solver is an excel add-in which can be used for quantitative risk analysis. It uses an “optimisation model” consists of three parts to determine the most suitable or desired outcome. These parts are called target cell, changing cells and the constraints (Introduction to optimisation with excel solver tool, 2009). Below is a sample Microsoft Excel model we have created for a given scenario with constraints. We will run what-if analysis using solver on this as a demonstration. The full description of the scenario is on Appendix 1.
The sample model is for a scenario where we are asked to reach the 800 million potential customers via advertisements in publications within a budget limit of 12 million dollars.
The example model was constructed using the following
o Target Cell : The total expenditure for advertisements (this is what we want to optimise)
o Changing cells ( These are the number of advertisements to be placed on publications)
o Constraints (rules)
� All Advertisements need to be an integer value as we can’t have fractions for advertisements.
� Each publication should have more than six advertisements
� Sum of each publication cost should be less than 12 million
� Pub3 and Pub 4 should be less than 7.5 million
� Each publication shouldn’t take no more than 1/3 of advertisements
� Total audience should be at least 800 million
Following is solver representation of the above.
As you can see the desired outcome varies depending on the changing constraints. Solver will give you the optimise target value (Total Budget) for each scenario by changing values for number of advertisements per publication. We managed to change the model depending on the scenario with ease. This is one of the best characteristics of what-if analysis with Excel solver. It can be adapted to different scenarios with minimum effort.
Issues with what-if analysis
The What-If Analysis technique is simple to use and can be applied effectively to a variety of processes. It has ability to solve many realistic business problems hence making it one of the most popular risk analysis tools (Albright, 2008). However it has limitations as well.
What-if analysis will work at its best mostly on simple scenarios. It is less likely to perform well in complex situations. In real world we face scenarios where we have to optimize multiple parameters of a problem or a system. The fact that only single value can be optimised seems to be an issue as well. In the example above, we could only optimise total budget nothing else. Accuracy of the excel model depends on the skill level and experience of the staff who are working on it. So as the case with many other analysis tools if we don’t have the model correctly setup the analysis will return flawed results. We can conclude that what-if analysis is not error proof and is vulnerable.
Non-linear problems are inherently more difficult to solve. So it likely that solver what-if analysis or sensitivity analysis might fail to find solutions for a non-linear problems or when integer constraints are included. Since our example contained integer constraints and seems to be non-linear solver fails to complete a sensitivity report (Albright, 2008). It also has issues developing a sensitivity analysis report when multiple input changes are involved as with our example model.
The other issue we have found it that the default tolerance level on solver what-if analysis is 5%. So the answer that we receive is not the ‘real’ optimal value. Due to this tolerance level setting solver seems to find the answer fairly quickly however if you change it to zero in hope of finding the ideal solution it takes an unacceptable time to return any value. This was the case for the example model we have used. This model only had few changing variables. This will become a bigger issue when the model becomes complex. Most likely what-if analysis using solver will not find a solution in this case and a more powerful quantitative analysis tools might need to be used to generate a solution.
Conclusion
What-if analysis is an important tool in the model building and analysing process. If we can show that the system or the problem does not respond greatly to a change in an input value, it reduces the uncertainty associated with the scenario. It also increases our chances of understanding the uncertainty and the changing behaviour of the situation.
Creating models and keeping them up to date with changing conditions can be difficult and time consuming. But this is an exercise can be beneficial in a risk analysis scenario. As many say, “no pain no gain”. However we have to keep in mind that a model, sometimes can be very close to the real scenario but it is only a model. So it needs to be treated as such.
As proven in the example model what-if analysis has many limitations. The biggest one been failure to find a solution when the problem is non linear or when constraints include of integers (Albright, 2008). So this leaves out many real world scenarios from been properly analysed using what-if analysis. Although sensitivity or a what-if analysis using a model allows a decision maker to make a better judgement call based on the produced results it is not guarantee a perfect decision. The quality of the analysis is directly related to quality of techniques and input tools used. Too optimistic or pessimistic inputs may cause the model and consequently the analysis to be biased.
We can conclude that what-if analysis can be seen as an easy to use method in risk analysis but it with many limitations when difficult and complex scenarios are presented. So it is best suited for solving simple problems.
References
Albright, C., Winston, W. & Zappe, C. (2008) Data Analysis and Decision Making with Microsoft Excel, Cengage Learning
Ansell, J. & Wharton, F. (1992) RISK Analysis Assessment and Management, John Wiley and Sons Ltd, England
Ayyub, B.M. (2003) Risk analysis in engineering and economics, CRC Press
Barkeley B.T. (2004), Project Risk Management, McGraw-Hill, New York
Blocher, E. (2006) Cost management: A Strategic Emphasis, McGraw-Hill Education (India) Pvt Ltd
Chapman C. and Ward S. (2003), Project Risk Management, John Wiley & Sons, West Sussex
Chapman, C. & Ward, S. (1997) Project Risk Management Process, techniques and Insights, John Wiley and Sons Ltd, England
Daniell, M. H. (2004) World of Risk: A new approach to global strategy and leadership, World Scientific Publishing, Singapore
Harvey, G. (2006) Excel 2007 for Dummies, Wiley Publishing, Hoboken, NJ
Introduction to optimization with the Excel Solver tool (2010) Retrieved from http://office.microsoft.com/en-us/excel/HA011245951033.aspx
Jovanovic P (1999) “Application of sensitivity analysis in investment project evaluation under uncertainty and risk”, International Journal of Project Management, 17 (4), 217-222.
Kaplan, S. and B. J. Garrick (1981), “On the Quantitative Definition of Risk,” Risk Analysis, vol. 1, no. 1, pp. 11-27.
Muhlbauer, K.W. (2005) Risk Model Building Process Retrieved April 10, 2010 from http://www.pipelinerisk.com/pdf/Risk_Model_2005.pdf
Nadler, D. & Slywotzky A. (2009) Risk and the enterprise Retrieved April 12, 2010 from http://www.mmc.com/knowledgecenter/viewpoint/Risk_and_the_Enterprise.php
Project Management Institute (2008), A Guide to the Project Management Body of Knowledge, Fourth Edition, SAI Global, Pennsylvania
Sweeney, D., Anderson, D., Williams, T., Camm, J. & Martin, K. (2009) Quantitative Methods for Business, Cengage Learning
Tversky, A. and D. Kahnemen (1992), “Advances in Prospect Theory: Cumulative Representation of Uncertainty,” Journal of Risk and Uncertainty, vol. 5, pp. 297-323.
Vose, D. (2008) Risk analysis: a quantitative guide, John Wiley and Sons.