Sensitivity Analysis Excel Tutorial Video And Template

Bonisiwe Shabane

-Dec 28, 2025, 9:12 AM

sensitivity analysis excel tutorial video and template

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.

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. 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. Sensitivity analysis gives you insight into how the optimal solution changes when you change the coefficients of the model. After the solver found a solution, you can create a sensitivity report. 1. Before you click OK, select Sensitivity from the Reports section. Below you can find the optimal solution and the sensitivity report.

It is optimal to order 94 bicycles and 54 mopeds. This solution gives the maximum profit of 25600. This solution uses all the resources available (93000 units of capital and 101 units of storage). You can find these numbers in the Final Value column. The reduced costs tell us how much the objective coefficients (unit profits) can be increased or decreased before the optimal solution changes. If we increase the unit profit of Child Seats by 20 or more units, the optimal solution changes.

When I first delved into financial modeling and data analysis, one of the concepts that stood out to me was sensitivity analysis. Sensitivity analysis is crucial in understanding how the output of a model is affected by changes in input variables. Whether you’re assessing the potential profitability of a business venture or forecasting future sales, being able to analyze how sensitive your results are to changes in key assumptions is invaluable. At its core, sensitivity analysis involves tweaking one or more input variables in a model to observe how these changes impact the output. This process helps to identify which variables have the most significant effect on the outcome, and it allows you to better understand the risks and opportunities associated with your decisions. When we delve into financial modeling, sensitivity analysis emerges as an indispensable part of the process.

Its importance cannot be overstated, as it adds layers of credibility to our models. By allowing us to project and understand the impact of varying conditions on different financial variables, sensitivity analysis stands as a beacon of informed decision-making. For instance, it helps us comprehend how a change in interest rates might affect bond prices – providing us with the flexibility to run simulations over multiple scenarios with surgical precision. With sensitivity analysis, I can create data-driven narratives that have the persuasive power to guide corporate strategies and investment choices. It’s the lens that brings into focus the tangible effects of the abstract variables that we, as analysts, often juggle with.

Sensitivity Analysis Excel Tutorial Video And Template

People Also Search

In This Lesson, You’ll Learn How To Set Up Sensitivity

Excel Is A Great Analytical Tool And If You Haven’t

Grab Your Free Practice Workbook For This Guide Now And

You Can Download The Practice Workbook From Here. Step-by-Step Guide

A Scenario Manager Allows The Analyst To “stress-test” The Financial