Sensitivity Analysis in Excel

Article byWallstreetmojo Team
Reviewed byDheeraj Vaidya, CFA, FRM

What is Sensitivity Analysis in Excel?

Sensitivity analysis in excel helps us study the uncertainty in the model’s output with the changes in the input variables. It primarily does stress testing of our modeled assumptions and leads to value-added insights.

In the context of DCF valuation, Sensitivity Analysis in excel is especially useful in finance for modeling share price or valuation sensitivity to assumptions like growth rates or cost of capital.

In this article, we look professionally at the following Sensitivity Analysis in Excel for DCF Modeling.

You can download this Sensitivity Analysis in Excel Template here – Sensitivity Analysis in Excel Template

Key Takeaways

  • Sensitivity analysis in Excel tests how input variable changes affect the model’s output. Stress testing assumptions can provide valuable insights and enhance research.
  • Excel’s sensitivity analysis is essential for valuing companies using discounted cash flow. It helps to determine how changes in assumptions impact share prices or valuation.
  • Excel’s sensitivity analysis helps understand financial and operational behavior. It can be beneficial for valuations like DCF or DDM in finance. By creating scenarios, you can see how changes in factors like interest rates or GDP affect the valuation. It’s important to use common sense when developing sensitivity cases.

#1 – One-Variable Data Table Sensitivity Analysis in Excel

Let us take the Finance example (Dividend discount modelDividend Discount ModelThe Dividend Discount Model (DDM) is a method of calculating the stock price based on the likely dividends that will be paid and discounting them at the expected yearly rate. In other words, it is used to value stocks based on the future dividends' net present value.read more) below to understand this in detail.

Constant growth DDM gives us the Fair value of a stock as a present value of an infinite stream of dividends growing at a constant rate.

Gordon Growth formulaGordon Growth FormulaGordon Growth Model derives a company's intrinsic value if an investor keeps on receiving dividends with constant growth forever. The formula for Gordon growth model: P = D1/r-g (P = stock price, g = constant growth rate, r = rate of return, D1 = value of next year's dividend) read more is as per below –

DDM Formula - Constant Growth Rate

Where:

  • D1 = Value of dividend to be received next year
  • D0 = Value of dividend received this year
  • g   = Growth rate of dividend
  • Ke = Discount rate
DDM

Now, let’s assume that we want to understand how sensitive the stock price is concerning the Expected Return (ke). There are two ways of doing this –

  • Donkey way :-)
  • What if Analysis

Now you can Master Financial Modeling with Wallstreetmojo’s premium courses at special prices

Best Financial Modeling Courses by Wallstreetmojo

Financial Modeling Course
* McDonalds Step by Step Modeling from Scratch
* 12+ Hours of Video
* Certificate
Rating 4.91/5
Financial Modeling & Valuation
* McDonalds Step by Step Modeling from Scratch
* Valuations
* 25+ Hours of Video
* Certificate of Completion
Rating 5/5
Valuation Course
* Valuations (DCF, DDM, Comparable Comps)
* 12+ Hours of Video
* Certificate
Rating 4.89/5

#1 – Donkey Way

Sensitivity Analysis in Excel using Donkey’s way is very straightforward but hard to implement when many variables are involved.

DDM - Senstivity Analysis in Excel

Do you want to continue making this given 1000 assumptions? Not!

Learn the following sensitivity analysis in excel technique to save yourselves from trouble.

#2 – Using One Variable Data Table

The best way to do sensitivity in excel is to use Data Tables. Data tables provide a shortcut for calculating multiple versions in one operation and a way to view and compare the results of all variations on your worksheet.

Below are the steps that you can follow to implement a one-dimensional sensitivity analysis in excel.

  1. Create the table in a standard format.

    In the first column, you have the input assumptions. In our example, inputs are the expected rate of return (ke). Also, please note 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.
    DDM - Senstivity Analysis in Excel - Part 1

  2. Link the reference Input and Output as given the snapshot below.
    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? We will use What if Analysis; this is a way to instruct Excel that for the Input (ke), the corresponding formula provided on the right-hand side should be used to recalculate all the other input.

  3. Select the What-if Analysis tool to perform Sensitivity Analysis in Excel.

    It is important to note that this is sub-divided into two steps.
    1. Select the table range starting from the left-hand side, starting from 10% until the lower right-hand corner of the table.
    2. Click Data – What if Analysis – Data Tables

  4. Data Table Dialog Box Opens Up.

    The dialog box seeks two inputs – Row Input and Column Input. Since there is only one input ,Ke, is under consideration, we will provide a single column input.
    Data Table Dialog Box

  5. Link the 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 source and Not from the one inside the table.

    DDM - Senstivity Analysis in Excel - Part 3

  6. Enjoy the Output

    DDM - Senstivity Analysis in Excel - Part 4

#2 – Two-Variable Data Table Sensitivity Analysis in Excel

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 cumulative effect of changes in assumptions (cost of capital, terminal growth rates, lower revenue growth, higher capital requirements, etc.) on the fair value of the stock.

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

With the base assumptions of the Cost of Capital as 9% and constant growth rate of 3%, we arrived at the fair valuation of $191.45 billion.

Alibaba Valuation Summary

Let us assume that you do not fully agree with the Cost of Capital Assumptions or the growth rate assumptions I have taken in Alibaba IPO Valuation. You may want to change the assumptions and assess the impact on valuations.

One way is to change the assumptions and check each change’s results manually. (codeword – Donkey method!)

Alibaba Valuation Summary - manual sensitivity analysis

However, we are here to discuss a much better and more efficient way to calculate valuation using sensitivity analysis in excel that saves time and provides us with a way to visualize all the output details in an effective format.

If we perform the What-if analysis in excelWhat-if Analysis In ExcelWhat-If Analysis in Excel is a tool for creating various models, scenarios, and data tables. It enables one to examine how a change in values influences the outcomes in the sheet. The three components of What-If analysis are Scenario Manager, Goal Seek in Excel, and Data Table in Excel.read more professionally on the above data, then we get the following output.

Two Dimensional Data Table - Sensitivity Analysis

With this background, let us now look at how we can prepare such a sensitivity analysis in excel using two-dimensional data tables.

Step 1 – Create the Table Structure as given below
  • Since we have two sets of assumptions – Cost of Capital (WACC) and Growth Rates (g), you need to prepare a table given below.
  • You are free to switch the row and column inputs. Instead of WACC, you may have growth rates and vice-versa.
Sensitivity Analysis Table

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 valueEquity ValueEquity 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.read more. Hence, we have linked the intersecting cell to the output.Sensitivity Analysis - What if excel

Step 3 – Open Two Dimensional Data Table
  • Select the table that you have created
  • Then click on Data -> What if Analysis -> Data Tables
Two Dimensional Data Table - Sensitivity Analysis in excel
Step 4 – Provide the row inputs and column inputs.
  • Row input is the Cost of Capital or Ke.
  • The column input is the growth rate.
  • Please remember to link these inputs from the original assumption source and not from inside the table.
Two Dimensional Data Table - Row and column inputs
Step 5 – Enjoy the output.
Senstivity Analysis excel output

#3 – Goal Seek for Sensitivity Analysis in Excel

Let us have a look at the DCF of Alibaba IPO ValuationAlibaba IPO ValuationAlibaba is the most profitable Chinese e-commerce company and its IPO is a big deal due to its size. 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.read more.

Goal Seek Senstivity Excel

As we know from DCFDCFDiscounted 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.read more that growth rates and valuation are directly related. Increasing growth rates increase the share price of the stock.

Let’s assume that we want to check at what growth rate will the stock price touches $80.

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. In our case, we may have to input growth rates many times to ensure that the stock price matches $80.

However, we can use a function like Goal Seek in excelGoal Seek In ExcelThe Goal Seek in excel is a “what-if-analysis” tool that calculates the value of the input cell (variable) with respect to the desired outcome. In other words, the tool helps answer the question, “what should be the value of the input in order to attain the given output?” read more to solve this in easy steps.

Step 1 – Click on the cell whose value you wish to set. (The Set cell must contain a formula)
Goal Seek - Step 1
Step 2 – Choose Tools, Goal Seek from the menu, and the following dialog box appears:
  • The Goal Seek command automatically suggests the active cell as the Set cell.
  • This can be over-typed with a new cell reference, or you may click on the appropriate cell on the spreadsheet.
  • Now enter the desired value this formula should reach.
  • Click inside the “To Value” box and type in the value you want your selected formula to equal.
  • Finally, click inside the “By Changing Cell” box and either type or click on the cell whose value can be changed to achieve the desired result.
  • Click the OK button, and the spreadsheet will alter the cell to a value sufficient for the formula to reach your goal.
Goal Seek - Sensitivity in Excel - Step 2
Step 3 – Enjoy the output.

Goal Seek also informs you that the goal was achieved.

Goal Seek - Sensitivity in Excel - Step 3

Conclusion

Sensitivity analysis in excel increases your understanding of the financial and operating behavior of the business. We learned from the three approaches – One Dimensional Data Tables, Two Dimensional Data Tables, and Goal Seek- that sensitivity analysis is extremely useful in finance, especially in the context of valuations – DCF or DDM.

You can develop cases to reflect valuation sensitivity to changes in interest rates, recession, inflation, GDP, etc., on the valuation. However, you can also get a macro-level understanding of the company and industry. Thought and common sense should be employed in developing reasonable and useful sensitivity cases.

What Next?

If you learned something about Sensitivity Analysis in Excel, please leave a comment below. Let me know what you think. Many thanks, and take care. Happy Learning!

Frequently Asked Questions (FAQs)

What are the problems with sensitivity analysis?

It is important to note that the sensitivity analysis results will only be accurate if the model has correct assumptions. Additionally, sensitivity analysis might need to consider the interconnections among input variables. Lastly, the model’s beliefs could be based on past data.

Is sensitivity analysis reliable?

Engineers use sensitivity analysis to improve the reliability and robustness of their designs. It involves evaluating potential uncertainties and variations in input variables to determine their impact on the model’s effectiveness.

Which Excel chart is best for sensitivity analysis?

The Excel Tornado chart helps analyze data and make decisions. It is beneficial for conducting sensitivity analysis, which shows how changes in one variable affect the behavior of another. Essentially, it illustrates how inputs influence outputs.

How do I interpret the results of sensitivity analysis?

Interpreting sensitivity analysis involves identifying which input variables have the most significant influence on the output. Variables with a high sensitivity can significantly impact the outcome, while those with low sensitivity have minimal effects.

You may also have a look at these articles below to learn more about Valuations and Corporate Finance –

Reader Interactions

Comments

  1. Stephen says

    Hi Dheeraj, thanks for being resourceful. You’re a star.

  2. Gordon says

    Hi Dheeraj, I find this blog most informative and helpful. Thank you for your time! Do you ever think about starting a youtube channel :)

    • Dheeraj Vaidya says

      thanks Gordon for the encouragement! youtube channel is a nice idea. Hopefully, I should be able to do something here in the near future.

  3. Ali says

    hi Dheeraj, this blog of yours is very informative, could you please share any excel sheet for financial projections of startup business.

    • Dheeraj Vaidya says

      Hi Ali,

      Unfortunately, I don’t have one.

      Thanks,
      Dheeraj

  4. Jai Sharma says

    Hi Dheeraj,

    I’m interested to learn How do you do valuation of any stock of a company starting from it’s basics like what are the different methods? most adopted method , how to use, etc.
    Please provide me that. Will be thankful to you.

    Best Regards

    • Dheeraj Vaidya says

      hey Jai, there are various valuation tools like Dividend Discount Model, DCF & FCFF, Relative Valuation (PE, PBV, Price to Cash Flows etc) among others. You can have a look at these in detail.

      Best,
      Dheeraj

  5. Divya Mishra says

    Thank you so much! This is really helpful.
    Could you please also provide an example of the Usage of Macros and Pivot Table in financial analysis?

    • Dheeraj Vaidya says

      Hello Divya,

      Thakns for the suggestion. Will keep this in mind for a future blog post on usage of macros/pivot tables.

      Best,
      Dheeraj

  6. Ajinkya Jadhav says

    Hey Dheeraj,

    Thanks a lot for the share. Having spent 2 years in the industry, one does not use all the M&A concepts on a daily basis, and it’s not always time-saving to visit one of the reference books when you have some concepts to be revised. Given this backdrop, your succinct and crisp explanation helps a great deal

    • Dheeraj Vaidya says

      Thanks Ajinkya. I am glad you find this useful :-)
      Best,
      Dheeraj

  7. Manjeet says

    Great Sir, learned a lot and one thing more I want to ask whether after ca can I get admission in CFA based on 4 years experience and have no graduation degree apart from IGNOU which will be over in may next yeqt

    • Dheeraj Vaidya says

      Hi Manjeet,

      minimum pre-requisite for appearing in CFA exam is graduation. Best way would be to seek guidance from CFA insitute from their contact us page.

      thanks,
      Dheeraj

  8. megha says

    very nice ..

    • Dheeraj Vaidya says

      thanks Megha!

  9. Daniella says

    Hi Dheeraj! These excel tutorials are the most helpful! I’m going through the CFA curriculum right now and it is so nice to see the excel gap bridged here in your blog!

    • Dheeraj Vaidya says

      thanks Daniella!