How does a spreadsheet facilitate sensitivity analysis

Eva O. Lantsoght, Editor. Author information Article notes Copyright and License information Disclaimer. Competing Interests: The authors have declared that no competing interests exist. Received Oct 3; Accepted Feb This is an open access article distributed under the terms of the Creative Commons Attribution License , which permits unrestricted use, distribution, and reproduction in any medium, provided the original author and source are credited.

This article has been cited by other articles in PMC. Abstract An important segment of the reliability-based optimization problems is to get access to the sensitivity derivatives. Step 1 Derivatives of performance function with respect to involved variables are evaluated at the mean values. Step 2 Performance function is evaluated at the mean value.

Open in a separate window. Fig 1. Table 1 Means and standard deviations of random variables. Fig 2. Fig 3. Fig 4. Table 2 Probabilistic characteristics of the variables in example 2. Fig 5. Fig 6. Fig 7. Fig 8. Sensitivity derivatives for the first example considering the model uncertainty. Fig 9. Sensitivity derivatives for the second example considering the model uncertainty. Table 3 New means and standard deviations of random variables after applying perturbations.

Example Case No. Table 4 Safety index and failure probability for each of the ten cases. Fig Table 5 Comparison between the results obtained from analytical calculations and the FFD method.

Supporting information S1 File Detailed data of Fig 7. XLSX Click here for additional data file. XLSM Click here for additional data file. Acknowledgments The authors would like to appreciate the Department of Geotechnical Engineering of Tongji University for providing the required facilities to conduct this research.

Funding Statement The authors received no specific funding for this work. Data Availability All relevant data are within the manuscript and its Supporting Information files. References 1. Wang Pan, Lu Zhenzhou, Tang Zhangchun A derivative based sensitivity measure of failure probability in the presence of epistemic and aleatory uncertainties. Computers and Mathematics with Applications. Hamedifar Hamed, Bea Robert G.

Role of probabilistic methods in sustainable geotechnical slope stability analysis. Procedia Earth and Planetary Science. Zhang Yishang, Liu Yongshou, Yang Xufeng Parametric sensitivity analysis for importance measure on failure probability and its efficient Kriging solution.

Mathematical Problems in Engineering. Chen Shong-Loong, Cheng Chia-Pang Influence of failure probability due to parameter and anchor variance of a freeway dip slope slide—A case study in Taiwan. Kiureghian Armen Der Analysis of structural reliability under parameter uncertainties. Probabilistic Engineering Mechanics. Faridafshin Farzad, Grechuk Bogdan, Naess Arvid Calculating exceedance probabilities using a distributionally robust method.

Structural Safety. Fenton Gordon A. On a unified theory for reliability-based geotechnical design. Computers and Geotechnics. Reliability-based Structural Design. Springer-Verlag London. Valdebenito M. Sensitivity estimation of failure probability applying line sampling. Reliability Engineering and System Safety. Xiao Sinan, Lu Zhenzhou Structural reliability sensitivity analysis based on classification of model output.

Aerospace Science and Technology. Helton J. Survey of sampling-based methods for uncertainty and sensitivity analysis. Applied Mathematical Modelling. Uryasev Stanislav Derivatives of probability functions and some applications. Annals of Operations Research. Mechanical Systems and Signal Processing. Apress; Nowak Andrzej S.

Reliability of Structures. Computers and Structures. Guo Jia, Du Xiaoping Reliability sensitivity analysis with random and interval variables. International Journal for Numerical Methods in Engineering. Structural and Multidisciplinary Optimization. December; 88 — Djordjevic N. Two-component of blast fragmentation. Esen S. Modelling the size of the crushed zone around a blasthole. Holicky Milan, Retief Johan V. Reliability analysis of the induced damage for single-hole rock blasting.

August; 1— Application of weibull statistics to the tensile strength of rock aggregates. Journal of Geotechnical and Geoenvironmental Engineering. June; 6 — Perras Matthew A. A review of the tensile strength of rock: concepts and testing. Millwater H. Probabilistic structural analysis of fatigue and fracture. Support Center Support Center. External link. Please review our privacy policy.

Lognormal [ 27 ]. Rent this content from DeepDyve. Rent from DeepDyve. If you think you should have access to this content, click to contact our support team. Contact us. Please note you do not have access to teaching notes. Other access options You may be able to access teaching notes by logging in via your Emerald profile. In our example, inputs are the expected rate of return ke.

Also, please note that there is a blank row colored in blue in this exercise below the table heading. This blank row serves an important purpose for this one-dimensional data table, which you will see in Step 2. The space provided by the blank row is now used to provide input expected return Ke and the output formula. Why is it done like this?

It is important to note that this is sub-divided into two steps. The dialog box seeks two inputs — Row Input and Column Input. Since there is only one input Ke under consideration, we will provide a single column input. In our case, all input is provided in a column, and hence, we will link to the column input. Column input is linked to the Expected return Ke. Please note that the input should be linked from the original source and Not from the one that is inside the table.

Data tables are very useful for Sensitivity analysis in excel, especially in the case of DCF. Once a base case is established, DCF analysis should always be tested under various sensitivity scenarios. Testing involves examining the incremental effect of various changes in assumptions cost of capital, terminal growth rates, lower revenue growth, higher capital requirements, etc.

Let us take the sensitivity analysis in excel with a finance example of Alibaba Discounted Cash Flow Analysis.

You may want to change the assumptions and access the impact on valuations. One way is to change the assumptions manually and check the results of each change. However, we are here to discuss a much better and efficient way to calculate valuation using sensitivity analysis in excel that not only saves time but also provides us with a way to visualize all the output details in an effective format.

It enables one to examine how a change in values influences the outcomes in the sheet. With this background, let us now look at how we can prepare such a sensitivity analysis in excel using two-dimensional data tables. The point of intersection of the two inputs should be used to link the desired output. In this case, we want to see the effect of these two variables WACC and growth rate on Equity value Equity Value Equity Value, also known as market capitalization, is the sum-total of the values the shareholders have made available for the business and can be calculated by multiplying the market value per share by the total number of shares outstanding.

Hence, we have linked the intersecting cell to the output. With its huge size and network, Alibaba IPO may look at international expansion beyond China and may lead to price wars and intensive competition in the US.

As we know from DCF DCF Discounted cash flow analysis is a method of analyzing the present value of a company, investment, or cash flow by adjusting future cash flows to the time value of money. This analysis assesses the present fair value of assets, projects, or companies by taking into account many factors such as inflation, risk, and cost of capital, as well as analyzing the company's future performance. Increasing growth rates increases the share price of the stock.

As always, we can do this manually by changing the growth rates to continue to see the impact on the share price. This will again be a tedious process.