University of Kwazulu-Natal

Sensitivity Analysis What If Excel Tutorial Lesson

Bonisiwe Shabane
-
sensitivity analysis what if excel tutorial lesson

Step-by-Step Guide to Understanding Sensitivity Analysis (“What If” Analysis) and Data Tables in Excel Sensitivity Analysis: “What if” Analysis A financial model is a great way to assess the performance of a business on both a historical and projected basis. It provides a way for the analyst to organize a business’s operations and analyze the results in both a “time-series” format (measuring the company’s performance against itself over time) and a “cross-sectional” format (measuring... Typically, once an analyst inputs both historical financial results and assumptions about future performance, he/she can then calculate and interpret various ratio analyses, and other operational performance metrics such as profit margins, inventory turnover,... A scenario manager allows the analyst to “stress-test” the financial results because the reality is that expectations can and usually do change over time.

This tutorial demonstrates how to do a sensitivity analysis in Excel. A sensitivity analysis involves entering a variety of financial scenarios to see which one is the most economically viable. In Excel, you can use single- or double-input data tables for your analysis. For a single-input data table, you change one variable in a table to see the results, known as a What-If Analysis. For example, take a loan where the amount is $2,000, the interest rate is 6%, and the payments are spread out over 12 months. The monthly payment amount is calculated in cell C8 using the PMT Function:

You can calculate hypothetical payment amounts – based on a constant rate and variable terms (6, 9, 18, and 24 months) – to see which scenario is best for you. A double-input data table lets you adjust two variables. So, as well as changing the term in months, you can change the interest rate applied to the loan. You can therefore have both row and column input cells instead of just one or the other. Finance teams make decisions under uncertainty every day. Every budget, forecast, and business model depends on assumptions that might change.

Revenue could grow faster or slower than expected. Costs might spike. Interest rates fluctuate. A single change in any variable can transform a profitable plan into a costly mistake. This guide walks you through sensitivity analysis in Excel—from basic one-variable data tables to complex scenario modeling. You’ll learn the exact steps to build models that test multiple assumptions, identify which variables matter most, and make better decisions when the future is unclear.

Sensitivity analysis tests how different input values affect your outcomes. It answers a simple question: what happens to my results if my assumptions change? Think of it as stress-testing your financial models. You might build a budget assuming 10% revenue growth. But what if growth hits 15%? Or drops to 5%?

Sensitivity analysis shows you all these outcomes at once, so you can plan for multiple scenarios instead of betting everything on one prediction. When working with financial models or business data in Excel, it’s important to understand how small changes in key variables can impact your results. That is where sensitivity analysis comes in. It allows you to test different values and see how they impact your outcome, without changing your original model. In this guide, we will walk you through how to perform sensitivity analysis in Excel using tools like Data Tables, Goal Seek, and Solver. To use sensitivity analysis with Goal Seek in Excel, follow the steps below.

➤ Go to the Data tab, click What-If Analysis, and choose Goal Seek. ➤ In the Goal Seek dialog box: For “Set cell“, select the cell that contains your target outcome, which is B10 (Net Profit). For “To value“, enter your desired net profit, which is $15000. For “By changing cell“, select the input cell that Goal Seek should adjust to reach your target. ➤ Press OK, and Excel will automatically change the input(cell B5) to 150 to reach your goal with net profit of $15,000(cell B10). Sensitivity analysis is a method used to evaluate how different values of input variables affect the output.

In Excel, it helps you test various what-if scenarios by changing one or more inputs and observing how those changes impact your results. This technique is especially useful in business, finance, and forecasting, where decision-making often depends on uncertain or variable factors. In this section, we will perform a sensitivity analysis using the Data Table for a retirement fund. Suppose we have a dataset with the following inputs for retirement fund: Annual Contribution: $6,000 (in cell B2), Annual Interest Rate (%): 3% (in cell B3), Number of Years: 20 (in cell B4). Here, we will examine how changing one variable (interest rate) and two variables (interest rate and number of years) affect your total retirement fund amount. Excel is a great analytical tool and if you haven’t yet used it for sensitivity analysis, you’re missing out on a great feature of Microsoft Excel.

The sensitivity analysis tool of Excel allows you to see how variation in one or more input variables may impact the output of the model. You can systematically list down a range of input variables and try them all to see how sensitive the output to this input variation is. Helps you identify the critical factors so you can make informed decisions. Projecting it to the world of finance, sensitivity analysis has a broad scope of application. In this guide, I will take you through two methods to do sensitivity analysis in Excel like a breeze. Grab your free practice workbook for this guide now and tag along with me.

Read More: How to Use What If Analysis in Excel Let’s consider two variables: Units Sold and Selling Price. The two-variable data table will show different values for a single result (such as Net Profit) as the variables change. Read More: How to Build a Sensitivity Analysis Table in Excel Read More: What If Analysis Data Table Not Working You can download the practice workbook from here.

In this lesson, you’ll learn how to set up sensitivity tables in financial models, including the key requirements for inputs and outputs and the required steps, and you’ll get practice creating these tables in... All investing is probabilistic because you can’t predict exactly what will happen 5, 10, or 15 years into the future – but you can come up with a reasonable set of potential scenarios. For example, if a company you’re analyzing exceeds growth expectations and grows at 15% per year rather than 5-10%, that might be one scenario. If it grows in-line with expectations, that could be another scenario. And if it declines or grows at a negative rate, that could be a third scenario. You can use sensitivity analysis to look at how this company’s valuation changes as you move from one scenario to the next.

Sensitivity analysis, also known as what-if analysis, determines how different values of an independent variable affect a specific dependent variable under a given set of assumptions. In other words, sensitivity analysis studies how various sources of uncertainty in a mathematical model contribute to the model’s overall uncertainty. Excel provides us with a bunch of tools to perform Sensitivity Analysis very effortlessly. Let’s review the list. Excel has three tools for what-if analysis; Data Tables and Scenarios use sets of input values to calculate possible outcomes.

Goal-seek works differently; it takes a single result and calculates possible input values that produce that result. In addition to these three tools, you can install the Solver add-in to help you perform What-If Analysis. The Solver add-in is similar to Goal Seek, but it can accommodate more variables. In this article, we will start with a real-world situation with some challenges and then use the tools introduced in this section to deal with the challenges in a systematic way. The reader of this article is supposed to have a working knowledge of Excel and be familiar with basic math.

People Also Search

Step-by-Step Guide To Understanding Sensitivity Analysis (“What If” Analysis) And

Step-by-Step Guide to Understanding Sensitivity Analysis (“What If” Analysis) and Data Tables in Excel Sensitivity Analysis: “What if” Analysis A financial model is a great way to assess the performance of a business on both a historical and projected basis. It provides a way for the analyst to organize a business’s operations and analyze the results in both a “time-series” format (measuring the c...

This Tutorial Demonstrates How To Do A Sensitivity Analysis In

This tutorial demonstrates how to do a sensitivity analysis in Excel. A sensitivity analysis involves entering a variety of financial scenarios to see which one is the most economically viable. In Excel, you can use single- or double-input data tables for your analysis. For a single-input data table, you change one variable in a table to see the results, known as a What-If Analysis. For example, t...

You Can Calculate Hypothetical Payment Amounts – Based On A

You can calculate hypothetical payment amounts – based on a constant rate and variable terms (6, 9, 18, and 24 months) – to see which scenario is best for you. A double-input data table lets you adjust two variables. So, as well as changing the term in months, you can change the interest rate applied to the loan. You can therefore have both row and column input cells instead of just one or the oth...

Revenue Could Grow Faster Or Slower Than Expected. Costs Might

Revenue could grow faster or slower than expected. Costs might spike. Interest rates fluctuate. A single change in any variable can transform a profitable plan into a costly mistake. This guide walks you through sensitivity analysis in Excel—from basic one-variable data tables to complex scenario modeling. You’ll learn the exact steps to build models that test multiple assumptions, identify which ...

Sensitivity Analysis Tests How Different Input Values Affect Your Outcomes.

Sensitivity analysis tests how different input values affect your outcomes. It answers a simple question: what happens to my results if my assumptions change? Think of it as stress-testing your financial models. You might build a budget assuming 10% revenue growth. But what if growth hits 15%? Or drops to 5%?