Sensitivity Analysis Table Template
This sensitivity analysis table template helps you predict sales revenue based on changes in input variables including traffic growth, unit price, and sales volume.
Below is a screenshot of the sensitivity analysis table template:
Download the Free Template
Enter your name and email in the form below and download the free template now!
Sensitivity Analysis is a tool used in financial modeling to analyze how the different values of a set of independent variables affect a specific dependent variable under certain specific conditions. In general, Sensitivity Analysis is used in a wide range of fields – from biology and geography to economics and engineering.
A Financial Sensitivity Analysis, also known as a What-If analysis or a What-If simulation exercise, is most commonly used by financial analysts to predict the outcome of a specific action when performed under certain conditions. It is done within defined boundaries that are determined by the set of independent (input) variables.
The analysis is performed in Excel under the Data section of the ribbon and the “What-if Analysis” button, which contains Goal Seek and Data Table.
More Free Templates
For more resources, check out our business templates library to download numerous free Excel modeling, PowerPoint presentation, and Word document templates.
- Excel Modeling Templates
- PowerPoint Presentation Templates
- Transaction Document Templates
- See all financial modeling resources
- See all Excel resources
- Share this article
- Financial Analysis
- Budgeting Consultants
- Financial Reporting
- Financial Planning
- Financial Modeling
- Accounting & Bookkeeping
- Taxation services
- Investor Ready Business Plan
- Professional Business Plan Revision
- Investor Ready Pitch Deck Presentation
- Investor Ready Executive Summary
- Investor Ready One Page Project Overview
- Professional Business Plan Review
- Pro Forma Statement of Financial Position/Balance sheet
- Cash Flow Analysis
- Full time CFO
- Special Purpose CFO
- Interim CFO
- Virtual CFO
- Marketing KPI
- Growth Plan KPI
- Break Even Analysis
- Financial Metrics KPI
- Unit Metrics Analysis
- Sales Performance KPI
- Cash Management KPI
- Inventory Management KPI
- Business Plan Packages
- Automotive Industry
- Blockchain Industry Financial Model New
- Cosmetics Industry
- Consulting Business
- Education Industry
- Entertainment Industry
- Fintech Industry
- Real Estate
- View All Financial Models
- Healthcare Industry
- Restaurant Business
- View All Business Plan
- Automotive Business
- Blockchain Industry Pitch Deck New
- Education Pitch
- View All Pitch Decks
- Testimonials
- Case Studies
Sensitivity Analysis Model – Free Excel Template
- My Wishlist
Sensitivity Analysis – Excel Template
Antoniya Baltova
One of the best ways to model uncertainty is by creating a sensitivity analysis. It represents a table that shows how a given end result would change based on one or two of the variables that are used as an input for its calculation. It is rather easy to create a sensitivity table. The tool that you need to use is located in the Data tab under ‘What if analysis’. Its Excel name is Data table.
Some other related topics you might be interested to explore are Pivot Tables , GETPIVOTDATA , and Slicers .
This is an open-access Excel template in XLSX format that will be useful for anyone who wants to work as a Financial Analyst, Business Analyst, Consultant, Corporate Executive, or everyone preparing a corporate presentation.
You can now download the Excel template for free.
Most Popular Resources
Check out our most helpful downloadable resources according to 365 Finance Analyst’s students and expert team of instructors.
Balance Sheet – Excel Template
If the P&L statement shows how profitable a company was over a given timeframe, we can say that the Balance...
3-Statement Model – Excel Template
The P&L, Balance sheet, and Cash flow statements are three interrelated parts. The P&L feeds net income on the l...
Cash Flow – Excel Template
The cash flow statement shows how a company generated and spent cash throughout a given timeframe. An important truth th...
Time Series Analysis – Excel Template
Performing time series analysis consists in studying data points that are organized chronologically and equally spaced i...
Profit & Loss (P&L) – Excel Template
The Profit & Loss statement gives an idea about the profitability of a business. We subtract revenues from different...
Make Data Invisible – Excel Template
From time to time you might want to hide source data in your worksheet. One way to do that is...
Welcome to Wall Street Prep! Use code at checkout for 15% off.
- All Self-Study Programs
- Premium Package
- Basic Package
- Private Equity Masterclass
- Project Finance Modeling
- FP&A Modeling Certification (FPAMC©)
- Bank & FIG Modeling
- Oil & Gas Modeling
- Biotech Sum of the Parts Valuation
- The Impact of Tax Reform on Financial Modeling
- VC Term Sheets & Cap Tables
- Corporate Restructuring
- The 13-Week Cash Flow Model
- Real Estate Financial Modeling
- REIT Modeling
- Accounting Crash Course
- Advanced Accounting
- Crash Course in Bonds
- Analyzing Financial Reports
- Interpreting Non-GAAP Reports
- Fixed Income Markets Certification (FIMC©)
- Equities Markets Certification (EMC©)
- ESG Investing
- Excel Crash Course
- PowerPoint Crash Course
- Ultimate Excel VBA Course
- Investment Banking "Soft Skills"
- The Investment Banking Interview Guide ("The Red Book")
- Virtual Boot Camps
- 1:1 Coaching
- Corporate Training
- On-Campus Training
- Free Content
- Support/Contact Us
- About Wall Street Prep
Sensitivity Analysis (“What if” Analysis)
Using data tables to power-charge your financial models
View Modeling Courses
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 the company’s performance against industry peers).
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, cash collections, leverage and interest coverage ratios, among others.
General Rule of Thumb in Sensitivity Analysis
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.
In previous articles, we discussed the fact that these forward-looking assumptions may not always hold true, and that the use of a scenario manager is a great way to incorporate several performance possibilities into your financial model. This allows the analyst to “stress-test” the financial results because the reality is that expectations can and often do change over time. Because the future cannot be predicted with any certainty, it’s never a good idea to take your financial model’s results and claim, either to your boss or to your client, that the results are final.
So what can you do if the financial model’s results are not the final results? Isn’t that why you build a model in the first place — to get some clarity or answer as to the future performance of the business? Yes and no. The purpose of the financial model is to provide some insight into future performance, but there is no one correct answer. Clients and managing directors like to see a range of possible outcomes, and this is where the sensitivity analysis, or “what-if” analysis comes into play.
The Wharton Online and Wall Street Prep Private Equity Certificate Program
Level up your career with the world's most recognized private equity investing program. Enrollment is open for the May 1 - Jun 25 cohort.
Data Table Format: Endless Possibilities in Analysis
It’s not unusual for a client to never even look at a financial model and opt to see the results presented in a data table format.
A sensitivity analysis, otherwise known as a “what-if” analysis or a data table, is another in a long line of powerful Excel tools that allows a user to see what the desired result of the financial model would be under different circumstances. It allows the user to select two variables, or assumptions, in the model and see how a desired output, such as earnings per share (a common metric used) would change based on the new assumptions. It is the perfect complement to a scenario manager, adding even more flexibility to one’s financial and valuation models when it comes to analysis and presentation.
In fact, it’s not unusual for a client to never even look at a financial model and opt to see the results presented in a data table format along with select financial data. This is why it’s important for the analyst to understand the mechanics of creating the data table and be able to interpret its results to make sure the analysis is working properly. We shall go over the mechanics of the data table next.
Sensitivity Analysis Data Table – Excel Template
Use the form below to download our sample Data Table:
Building the Data Table: Initial Set-Up
Let’s say, for example, that you have built a dynamic financial statement model in order to predict future earnings per share (EPS) for your business. Your model is flawlessly constructed and gives you an EPS result of $2.63 for the year 2009. Now, instead of presenting to your client that the answer to the question “What will EPS be in 2009?” is unquestionably going to be $2.63, it makes more sense to present a range of possibilities for 2009 EPS that depend on sensitizing certain assumptions in the model. Let’s look at an actual example below to illustrate our point:
Constructing the Matrix in Excel
- In a cell on the worksheet, reference the formula that refers to the two input cells that we would like to sensitize. In cell D208, we have referenced our EPS for 2009.
- Type one list of input values in the same column, below the formula. In the example, we have input a range of revenue growth assumptions.
- Type the second list in the same row, to the right of the formula. In the example, we have input a range of EBIT margin assumptions.
- Select the range of cells that contains the formula and both the row and column of values. In the example below, you would select the range D208:I214.
- Hit the keys Alt-D-T on your keyboard. This will pull up the “Data Table” box as shown to the right of the data table, below. Note: This “shortcut” works in both Excel 2003 and 2007, although an alternative would be to hit Alt-A-W-T for the 2007 version, which will direct you to the data table box through the “What-If Analysis” menu.
- In the Row input cell box, enter the reference to the input cell for the input values in the row. In the example below, you would type cell E35 in the Row input cell box.
- In the Column input cell box, enter the reference to the input cell for the input values in the column. In the example below, you would type E33 in the Column input cell box.
Sensitivity Analysis: Functioning Data Table Results
We will finally get our various diluted EPS results as seen in cells E209 through I214 in the data table. The only thing left to do now is to sanity check the results. As revenue growth increases, we should see an increase in diluted EPS, and we do. We should also see diluted EPS increase as EBIT margin improves, and we do. It looks as though we have constructed a well-functioning data table!
Aside from our data table matrix, another method to perform a sanity check on a forecasted figure like revenue is the compound annual growth rate (CAGR) . The annualized growth rate metric can be determined to confirm it is reasonable, which is based on the company’s historical growth rate and the industry average among comparable companies.
One thing to know is that sometimes Excel is set to calculate automatically, except for data tables. If it looks as though your data table is not working, try hitting “F9” to recalculate the entire worksheet. You can also adjust how Excel is set up by hitting Alt-T-O and then going to the “Calculations” tab in Excel 2003 or the “Formulas” section in Excel 2007. You can also hit Alt-M-X in Excel 2007 to make your selection.
In conclusion, performing sensitivity analysis via a data table is an effective and easy way to present valuable financial information to a boss or client. It provides a range of possible outcomes for a particular piece of information and can highlight the margin of safety that might exist before something goes terribly wrong. For example, how low can revenue growth or EBIT margins get before EPS becomes negative? Once you have constructed several data tables, you’ll realize that it takes no time at all and that there is no excuse for not incorporating them into your financial modeling arsenal.
Everything You Need To Master Financial Modeling
Enroll in The Premium Package : Learn Financial Statement Modeling, DCF, M&A, LBO and Comps. The same training program used at top investment banks.
- Google+
- 100+ Excel Financial Modeling Shortcuts You Need to Know
- The Ultimate Guide to Financial Modeling Best Practices and Conventions
- What is Investment Banking?
- Essential Reading for your Investment Banking Interview
So the analysis can be carried with maximum 2 variables? How can we test if we want to try 3 variables (EBIT margin, revenue growth plus another variable)?
Can the sensitivity analysis data table be on a different sheet from where the basic model is? When I try that, Excel gives me an error “Input cell reference is not valid.” However, the same data table works fine on the same sheet as the basic model. Thank you for … Read more »
Hi there, thanks for the resource!
I’m having some trouble getting the results. I used Alt A W T and input the two cells, however, the results I got are all dash lines. Could you help with that please? Thanks so much
Hi, may I know where is the excel file for download? Can’t find the link, or can you send me? Thanks!
Thank you for the knowledge sharing.
Please how do I apply this technique on a financial statement that is built on monthly basis.
Warm regards.
Great tutorial you couldn’t have made it easier. Thank you:)
I applied the above to one sensitivity exercise that i was working on, however, at times, the same values appear throughout the table. Why is that so? and how should i rectify that?
We're sending the requested files to your email now. If you don't receive the email, be sure to check your spam folder before requesting the files again.
Everything you need to master financial and valuation modeling: 3-Statement Modeling, DCF, Comps, M&A and LBO.
The Wall Street Prep Quicklesson Series
7 Free Financial Modeling Lessons
Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.
Welcome to Breaking Into Wall Street .
You are currently running a browser that we do not support. As a result - our website will not display the best. We recommend revisting our site using an updated browser.
Continue to site
- Video Tutorials
- Knowledge Base
- Group Licenses
- Why Choose Us?
- Certificates
- Excel Tutorials
Sensitivity Analysis Excel: How to Set It Up [Tutorial Video] (17:58)
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 the Walmart valuation.
- Tutorial Summary
- Files & Resources
- Premium Course
Sensitivity analysis in Excel lets you vary the assumptions in a model and look at the output under a range of different outcomes.
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 Excel: Data Tables and How to Build Them
Internally in Excel, sensitivity analyses are known as “data tables,” and you can access them in the ribbon menu under the Data tab and “What-If Analysis”:
In PC/Windows Excel, the shortcut is Alt, D, T or Alt, A, W, T (there is no shortcut in Mac Excel):
A properly set-up and formatted sensitivity table looks like this (taken from the Walmart DCF, where we vary the Discount Rate and Terminal Growth Rate to assess the company’s implied value):
To create this table, use the following steps:
Sensitivity Tables, Part 1: Building an Appropriate Financial Model
Before you do anything else, you must have a financial model or other analysis where several key inputs or assumptions directly affect the output.
For example, in our setup for this Walmart DCF, it’s easy to see that assumptions such as the Discount Rate and the Terminal Multiple affect the company’s implied share price.
The Terminal Multiple affects its Terminal Value, and then the Discount Rate affects the Present Value (PV) of the Terminal Value and the PV of the Free Cash Flows in the projection period, and those flow into the Implied Enterprise Value and then the Implied Equity Value and Share Price:
If you do not have a setup like this, where a few key cells affect the output of your model, sensitivity analyses will not work.
Sensitivity Tables, Part 2: Formatting
Next, start by formatting the cells the way you want. We usually pick blue colors for the background/fill of the outer cells, with white font color on top, and then a standard white background with black font color for the middle area:
This is just formatting, so you should look at our tutorial on how to color code in Excel for more.
Sensitivity Tables, Part 3: Enter the Row and Column Numbers You Want to Sensitize
This step is very important: when you enter the numbers in the row and column of the table that you want to sensitize, you cannot link them directly to anything in the model!
So, you must hard-code all these numbers in Excel, or you must start the row and column with a hard-coded number and then add or subtract in each column or row after that.
Here they are for Walmart:
Sensitivity Tables, Part 4: Link to the Output Variable in the Top-Left Corner
Next, go to the top-left corner of the table – cell D102 here – and enter a direct link to the output variable that you want to display in the table.
In this case, it’s the company’s Implied Share Price under the Perpetuity Growth Rate method of calculating Terminal Value:
This is the only part of the sensitivity table that should be linked to something in the model.
Sensitivity Tables, Part 5: Select the Entire Range and Create the Table
Now, select the entire range and go to Alt, D, T or Data, What-If Analysis, and Data Table, and enter the row and column input cells (the Terminal Growth Rate and Discount Rate here):
If the table does not refresh right away, press F9 to force a spreadsheet update and see the results.
Sensitivity Tables, Part 6: Check Your Work
The results should look like this:
You should check your work by reviewing the table and looking at the following points:
-Do the numbers change in each cell? If not, you’re doing something wrong. You should not see rows or columns where the output is the same.
-In valuation tables, does the company’s implied value increase as its revenue, revenue growth, or margins increase? Higher numbers should mean a higher valuation.
-As the Discount Rate increases, the company’s implied value should decrease .
The list goes on: make sure you understand how each assumption should affect the output and then see if it works that way in your table.
Sensitivity Analysis Excel: Key Requirements to Set Up the Tables
To recap and summarize this article, here are the key requirements for Excel-based sensitivity analysis:
1) The input variables and output must be on the same spreadsheet as the table . You cannot use assumptions or drivers from other sheets, such as the 3-statement model, in this table.
2) The numbers in the input row and column cannot be linked to or from anything that’s in the model. Start each input row or column with a hard-coded number and then hard-code the rest or make them change by simple percentages or numbers.
3) The row and column inputs and the output must be related in some way. If the inputs do not affect the output, the table will show no changes as you vary the numbers.
4) Set “Workbook Calculation” in Options or Preferences to “Automatic except for data tables” or your spreadsheet will slow down, especially with many tables. You can then press F9 to refresh or update the tables.
5) Enter a direct link to the output you want to sensitize in the top-left-hand corner of the table . And then, select everything and go through the steps shown above. “Row Input Cell” should be a direct link for the input in the top row, and “Column Input Cell” should be a direct link for the input in the left column.
6) You cannot modify individual cells in the table once it has been created. If you want to change something or select different inputs or outputs, you must delete and re-enter the entire table.
You will see sensitivity tables in almost every financial model of intermediate complexity and beyond, so you must know how to use them.
They aren’t that difficult, but many students make mistakes with the points above.
Tutorial Summary & Description
Financial modeling spreadsheets and templates in Excel & Google Sheets
- Your cart is empty.
Sensitivity Analysis
Food Truck Financial Model – 5 Year Financial Forecast
This Financial Model presents an advanced 5-year financial plan for a startup or operating Food Truck business and is a…
- Financial Model - Standard Version – $69.00 Version 1
- Financial Model - Premium Version – $89.00 Version 1
- PDF Free Demo – $0.00 Version 1
Vessel Voyage Calculator (Dry Cargo)
This financial model allows the user to calculate and compare 3 different vessel voyage estimations for dry cargoes ships.
- Free PDF Demo – $0.00 Version 1
- Full Excel Model – $119.00 Version 1
Social Media Platform – Dynamic 10 Year Financial Model
Financial Model providing a dynamic up to 10-year financial forecast for a startup Social Media Platform.
- Excel Financial Model – $129.00 Version 1
Household Service Marketplace Financial and DCF Valuation Model
A complete and fully dynamic financial and DCF valuation model for a household service marketplace business based on a 5-…
- EXCEL PAID VERSION – $85.00 x
- FREE PDF VERSION – $0.00 x
Oilseeds Crushing Plant Financial Model
Setting up an oilseed crushing plant requires a comprehensive and executed business strategy. This thorough 30-year financial plan helps business…
- Excel Version – $119.95 Version 1.1
- PDF Version – $0.00 Version 1.1
Cruise Ship Financial Forecasting Template
Fully loaded financial model that can be used for up to a 10 year forecast. Includes financial statements and granular…
- Full Version – $45.00 Version 1
Convert Vacant Office Space into Residential Apartment Financial Model
The redevelopment model caters to selling off apartments as well as holding them for leasing out.
- PDF Explainer – $0.00 Version 1
- Full Open Excel – $127.00 Version 1
“Netflix for Gadgets” – Consumer-Tech Subscription/Rental Business Revenue and DCF Valuation Model
An integrated, dynamic and ready-to-use financial model for Consumer-Tech Subscription/Rental Business which is a brand new and emerging segment market…
- Full Excel Model – $95.00
- Free PDF Demo – $0.00
Cloud Kitchen Financial Model – 5 Year Financial Forecast
Financial Model presenting a business scenario for a Cloud/Ghost Kitchen.
- Financial Model - Standard Version – $79.00 Version 1
- Financial Model - Premium Version – $99.00 Version 1
Veterinary Clinic Financial Model – 5 Year Forecast
Financial Model providing an advanced 5-year financial plan for a startup or operating Veterinary Clinic.
- Financial Model - Standard Version – $89.00 Version 1
- Financial Model - Premium Version – $109.00 Version 1
Value-Add Single-Family (SFR) Build to Rent (B2R) Real Estate Investment Model with Waterfall
A financial model to help calculate and analyze the pro forma indicative return from a Value-Add Single-Family (SFR) Build to…
- Full Excel Model – $50.00 Version 1
Core Single-Family (SFR) Build to Rent (B2R) Real Estate Investment Model with Waterfall
Pro Forma Models created this financial model to calculate and analyze the pro forma indicative return from a Core Single-Family (SFR)…
- Excel Full Model – $50.00 Version 1
eCommerce Monthly 3-Statement Business Plan with Return Calculations
A model to prepare and analyze a start-up or existing eCommerce business. It incorporates the pre-sale launch period using a…
Ultra Fashion Revenue and DCF Valuation Model
In this model, we presented a completed revenue and DCF valuation model for an Ultra-Fashion project on a 5-year basis…
- Full Excel Version – $65.00
- Free PDF Version – $0.00
Electrician Business Financial Model – 5 Year Forecast
Financial Model providing an advanced 5-year financial plan for an Electrician Business scenario.
Software as a Service (SaaS) Monthly 3-Statement Business Plan with Return Calculations
Pro Forma Models created this model to prepare and analyze a start-up or existing Software as a Service (SaaS) business…
Start Up Martial Arts Center Financial Model
Start Up Martial Arts Center Financial Model presents the case of investing in opening a start-up martial arts center and…
- Full Excel Model – $99.00 Version 1
Multi Sports Complex Financial Model – Dynamic 10 Year Forecast
Financial Model presenting a development and operating scenario of an Indoor Multi Sports Complex.
- Excel Financial Model – $109.00 Version 1
Economic Model for Oil and Gas Extraction Feasibility
This model runs for 20 years and makes it easy to create a financial plan and feasibility study for the…
- Full Version – $75.00 Version 1
Medical Clinic Financial Model – 5 Year Forecast
Financial Model providing an advanced 5-year financial plan for a startup or operating Medical Clinic.
Seaweed Farming Financial Model
The Seaweed Farm Financial Model aims to comprehensively develop a business plan of 40 years forecast to analyze the financial…
- Full Excel Version – $119.95 Version 1.1
Student Hostel Development – Dynamic 10 Year Financial Model
Financial model presenting a development scenario for a Student Hostel Project.
Dental Office Financial Model – 5 Year Forecast
Financial Model providing an advanced 5-year financial plan for a General Dental Office.
- Financial Model - Standard Version – $109.00 Version 1
Pre-Launch & Operating General Monthly 3-Statement Business Plan with Return Calculations
Pro Forma Models created this model to prepare and analyze a start-up business plan forecast that incorporates a pre-sale launch…
- Full Model – $50.00 Version 1
- PDF Demo – $0.00 Version 1
Dynamic Lender Debt Investment Model
Pro Forma Models created this financial model to calculate and analyze the financial return of a debt investment from the perspective…
- Excel Model – $10.00 Version 1
Property Management Business Financial Model
Create a wide range of scenarios for the financial forecasting of a property management business. Includes an integrated 3 statement…
Principal Residence Real Estate Investment Model
This Pro Forma Model is designed to analyze Principal Residence Real Estate Investments with the ability to model income components…
- Excel Model – $40.00 Version 1
Real Estate Brokerage Business Plan with Return Calculation
Pro Forma Models created this model to analyze the financial return of acquiring an existing real estate brokerage company (M&A), operating…
Escape Room Financial Model – 5 Year Forecast
Financial Model providing an advanced 5-year financial plan for a startup or operating Escape Room Business.
Property Manager Plan with Return Calculations & M&A Model
Pro Forma Models created this model to analyze the financial return of acquiring an existing property management company (M&A), operating an…
- Excel Model – $50.00 Version 1
Capital Budgeting Model with NPV, IRR, & RoC Calculation
This model was developed to complete capital budgeting and analysis before undertaking a project of investment. The model can handle…
Operating Lease Converter
This model was developed to convert a company's operating lease commitments to debt and restate earnings before interest and taxes.…
Leveraged Buyout (LBO) Financial Projection Model
User-friendly financial model to project and analyse the financial outcomes (IRR, projected financials, key ratios, sources & uses of funds,…
- Excel Model - Populated – $89.00 Version 1
- Excel Model - Not Populated – $89.00 Version 1
- PDF Example – $0.00 Version 1
Dynamic Franchise Business Plan – 3 Statement Model with DCF & Returns
Pro Forma Models created this model to analyze the financial return of a franchise business using a 3 statement model.…
- Excel Model – $40.00 Version 1 x
- PDF Demo – $0.00 Version 1 x
Cleaning Business – 5 Year Financial Model
Financial Model providing an advanced 5-year financial plan for a Cleaning Business.
General Hospital Financial Model (Development, Operation, & Valuation)
Financial Model presenting a development and operating scenario of a General Hospital.
- Excel Financial Model – $149.00 Version 1
Artisan Contractor Business – 5 Year Financial Model
Financial Model providing an advanced 5-year financial plan for an Artisan Contractor Business scenario.
Auto Repair Shop Financial Model – 5 Year Financial Forecast
Financial Model providing an advanced 5-year financial plan for a startup or operating Auto Repair Shop.
Energy Project Finance (Infrastructure Model)
This is a special project vehicle (SPV) for an Energy Project which includes a dynamic 10-year infrastructure model built using…
- Excel Model – $55.50 Version 1
Project Finance Model for a Highway SPV Project
This Project Finance Model for a Highway SPV Project is an infrastructure model that is built using FAST standards.
Business Acquisition (M&A) Model with Monthly Business Plan & Returns
Pro Forma Models created this model to analyze the financial return of acquiring an existing operating business (M&A), regardless of industry,…
Hotel (AirBnB) Financial Model
This model provides a framework to forecast the next 12 years and correctly value the business in case of any…
- Excel Model – $30.00 Version 1
Hospital (Health Centre) Financial Model
Financial Model to calculate Profit/Loss, accurately forecast financial statements and do valuation of hospital business.
Wind/Solar Power Generation (Renewable Energy) Financial Model
Financial Model to calculate Profit/Loss, accurately forecast financial statements and do valuation of Solar/Wind Power Generation Business.
Sports Agency Startup 3 Statement Forecasting Template
This 10-year financial forecasting template works great for any sort of professional agency / sports agency / talent agency where…
Equipment Leasing Company Financial Model – Dynamic 10 Year Forecast
Financial model providing a dynamic 10-Year Financial Plan for an Equipment Leasing Company
Retail Store Financial Model (FMCG/Clothing/Electronics, etc.)
Financial Model to calculate Profit/Loss, accurately forecast financial statements and do valuation of retail business.
- 3 Excel Models – $30.00 Version 1
Port Terminal Operator – Project Finance Model
Financial Model presenting a business scenario of a Port Operator with an existing terminal for Bulk Cargo and the development…
Airport Financial Model (Development, Operation & Valuation)
Financial Model presenting a development and operating scenario of an Airport under a Public-Private Partnership (PPP) agreement.
Baby Swimming School – Dynamic 10 Year Financial Model
Financial model providing a dynamic 10-Year Financial Plan for a Baby Swimming School for children up to 4 Years old.
Blue Hydrogen Production Financial Model
The objective of this financial model is to develop a comprehensive business plan of 40 years forecast to analyze the…
- Full Excel Version – $149.00 Version 1.6
- Full Excel Null Version – $149.00 Version 1.6
- PDF Version – $0.00 Version 1.6
Dance Studio Financial Model – 5 Year Financial Forecast
Provides an advanced 5-year financial plan for a startup or operating Dance Studio and is a flexible tool for owners…
Student Housing Real Estate Investment Model with Returns & Waterfall
Pro Forma Models created this financial model to calculate and analyze the pro forma indicative return from a student housing…
MOBILE APP FINANCIAL MODEL – Team Strategy FM
A modelling template that covers every aspect of the vast industry of mobile applications and development, all the way from…
- Excel Model – $89.00 Version 1
Shoes Production Factory – Dynamic 10 Year Financial Model
Financial model providing a dynamic 10-Year Financial Plan for a Shoes Production Factory.
- Financial Model - Premium Version – $119.00 Version 1
Construction Contractor Business – Cash Flow Forecast
This financial model lets the user plan out cash requirements and expected returns of running a construction business. 10 Year…
- Full Version – $75.00
Golf Course – Membership Only Model
Create a financial forecast for a members-only golf course. There are industry-specific caveats here that you need to understand if…
- Full Version – $125.00 Version 1
Recycling Company Financial Model – Dynamic 10 Year Forecast
Financial model providing a dynamic 10-Year Financial Plan for a Recycling Company.
- Full Excel Model – $99.00
- PDF Free Demo – $0.00
Waste Management Company – Dynamic 10 Year Financial Model
Financial model providing a dynamic 10-Year Financial Plan for a Waste Management Company.
- Full Excel Model – $89.00
Energy Recovery Facility (ERF) – Dynamic 10 Year Financial Model
Financial model presenting a business scenario of a company owning and/or leasing Energy Recover Facilities and ERF Plants for the…
- Full Excel Model – $129.00
Financial Planning and Analysis
Identifying the differences between types of analysis.
- What-if analysis : What-if analysis is the process of testing to see “what would happen if” you change something in the model.
- Sensitivity analysis : Sensitivity analysis is the process of tweaking one key input or driver in a financial model and seeing how sensitive the model output (e.g. the NPV or the IRR) is to the change in that variable.
- Scenario analysis : Scenario analysis is the process of tweaking a whole series of inputs or drivers in a financial model and seeing what happens with the models result.
What is Sensitivity Analysis
Steps Used to Conduct a Sensitivity Analysis
- Basic structure – includes a combination of assumptions that are to be varied, ensuring which and how many assumptions are needed at a period, assigning values before the simulation, analyzing the correlations of the values, etc.
- What to vary – set of different assumptions that can be chosen to vary in the model, such as the number of activities, objective in relation to the risk assumed and the projected profits, technical ratios, number of constraints and limits, etc.
- What to observe – include the value of the variable as per the regular business plan, the value of the decision variables, and value of the variable after going through between two different strategies applied
- Input the base case output to be put under sensitivity analysis. (e.g. V1)
- Calculate the new value of the input by measuring through the sensitivity test while all other inputs in the model are kept constant. (e.g. V2)
- Calculate the percentage change in the output and input respectively.
- The sensitivity is then calculated by dividing the percentage change in output by the percentage change in input.
Uses of a Sensitivity Analysis
- Indicating the sensitivity of simulation to assumptions of the input values in a model
- Predicting the effects of different scenarios and decisions
- Testing the robustness of the values
- Assessing the risks of a strategy or the business plan
- Determining if the optimal value criteria are reached or still have space to grow
- Identifying the dependency of the outputs to certain inputs, whether it is helpful or risky
- Acting as an error checker of the model
- Building informed and appropriate decisions or strategy
- Removing redundancy in the model structure
- Representing as a tool to communicate between entities
Sensitivity Analysis Example Models
- Sensitivity Analysis
- Amortization & Depreciation Schedule
- Balance Sheet
- Bank Financial Models
- Black-Scholes Models
- Capital Budgeting
- Cash Flow Statement
- Corporate Finance
- Cost Analysis
- Debt Schedule
- Discounted Cash Flow (DCF)
- Excel Add-Ins
- Financial Forecasting Models
- Financial Markets
- Financial Modeling
- Financial Projections Models
- Income Statement
- Industry Specific Financial Models
- Inventory Management
- Investment Banking
- Leveraged Buyout (LBO)
- Mergers & Acquisitions (M&A)
- ModelOff Samples
- Monte Carlo Simulation
- Net Present Value (NPV)
- Options Pricing & Valuation
- Private Equity
- Project Finance Models
- Real Estate Financials
- Renewable Energy Financials
- Restaurant Finance
- Retail Finance
- Return On Investment (ROI)
- Scenario Analysis
- Solar Energy Project Finance
- Stock Valuation
- Three Statement Financial Models
- Valuation Models
- Venture Capital
- Weighted Average Cost Of Capital (WACC)
- Wind Energy Project Finance
- 2x2 Four Quadrant Matrix
- Ansoff Matrix
- Asset Management
- Balanced Scorecard
- BCG Frameworks
- BCG Growth-Share Matrix
- Business Development
- Business Model Canvas -
- Business Plans
- Change Management
- Competitor Analysis
- Consulting Interview
- Consulting Proposals
- Crisis Management
- Due Diligence
- Executive Summary
- KPI Dashboards
- Logistics & Supply Chain Management
- Macroeconomics
- Management Consulting
- Market Analysis
- Market Research
- Market Sizing
- McKinsey 7-S Framework
- McKinsey Frameworks
- Microeconomics Excel
- Mission Statement
- Nine-Box Matrix
- Operations Management
- Porter's Five Forces
- Post-Merger Integration (PMI)
- Problem Solving
- SMART Goals
- Strategic Analysis
- Strategic Management
- Strategic Partnerships
- Strategic Planning
- SWOT Analysis
- Value Chain Analysis
- Value Creation
- Waterfall Charts
- Cohort Analysis
- Design Thinking Process
- E-Commerce Financial Models
- Entrepreneurship
- Growth Hacking
- Lean Startup
- Lifetime Value (LTV)
- Marketplaces
- Mobile App Finance
- Raising Capital
- Software-as-a-Service (SaaS)
- Startup Boards
- Startup Business Plans
- Startup Cap Tables
- Startup Financial Models
- Startup Investors
- Startup Lifestyle
- Startup Pitch Decks
- Startup Studios
- Startup Valuation
- 4Ps & 7Ps Marketing Mix
- Advertising
- Branding & Brand Management
- Business Email
- Business Networking
- Corporate Communication
- Customer Acquisition
- Customer Centric Design
- Customer Success
- Digital Marketing
- Growth Marketing
- Inbound Marketing
- Industry Landscape Analysis
- Lead Generation
- Marketing Plans
- Net Promoter Score (NPS)
- Outbound Sales
- Personal Branding
- Product Launch
- Product Life Cycle
- Public Relations
- Public Speaking
- Search Engine Optimization (SEO)
- Social Media
- Strategic Marketing
- Advisor Agreements
- Articles Of Incorporation
- Code Of Conduct
- Employee Contracts
- Intellectual Property (IP) Agreements
- Internal Audit
- Non-Disclosure Agreements
- Operating (or Founder) Agreements
- Sales & Purchase Agreements (SPA)
- Shareholder Agreements
- Startup Term Sheets
- Career Advancement
- Chief Of Staff
- Corporate Event Management
- Cross Cultural Management
- CV (Curriculum Vitae) / Resume
- Decision Making
- Effective Communication
- Gantt Charts
- Health & Safety
- Human Resources
- Job Descriptions
- Organizational Charts
- Organizational Culture
- Performance Reviews & Appraisals
- Personal Development
- Productivity
- Project Management
- Remote Work
- Task Management
- Team Management
- Training Excel
- Artificial Intelligence (AI)
- Blockchain Technology
- Cryptocurrency
- Cybersecurity
- Data Analysis
- Data Mining
- Data Science
- Digital Privacy
- Electrical Engineering
- Genetic Engineering
- Google Sheets Models
- Machine Learning
- Mathematical
- Neural Networks
- NFT Investment
- Product Management
- Research And Development (R&D)
- Statistical
- Dissertation Writing
- Essay Writing
- Exam Revision
- Student Life
- Study Note-Taking
- University Research
- Coronavirus (COVID-19)
- Dynamic Arrays
- Eloquens Authors
- Miscellaneous
- Politics & Politicians
Sensitivity Analysis Excel Templates Start the discussion!
What is sensitivity analysis?
Sensitivity analysis is a practice used frequently in the field of financial modeling . This kind of analysis is performed in order to estimate how various figures relating to an independent variable can affect the dependent variable of the model. This sensitivity analysis is always done with models that depend upon one or more input variable . This form of analysis is often referred to as a “What-If” simulation and is often done in order to predict outcomes of different decisions based on different variables.
As such, sensitivity analysis allows you to analyze how the different figures within sets of independent variables affect the end result in different conditions. This also allows you to analyze how changing these independent variables will affect the overall outcome of the dependent variable, enabling you to identify the most important independent variables.
What is the purpose of a sensitivity analysis?
Sensitivity analysis can be very important in business. Conducting sensitivity analysis allows you to project the impact of specific changes you could make within a project. This allows you to identify the key variables that will have a significant influence on the success or failure of your projects.
Further to this, sensitivity analysis allows you to identify and plan for these impacts that changes in these variables could have, Therefore, you can determine the potentially negative impacts that changes in variables could cause.
By conducting a sensitivity analysis, you can also identify the changes in variables that could have a positive influence over the progress of a project. Thus, it will help you to identify changes that could be made to improve performance.
Performing Sensitivity Analysis
To conduct sensitivity analysis most effectively, it needs to be done in an incredibly systematic manner . There are three key steps to be followed when performing sensitivity analysis:
- Identifying the specific variables which will affect the project
- Calculating the effects that changes to these variables will have
- Giving consideration to these variables in different potential combinations
Sensitivity analysis is often conducted in excel via different formulas.
Why is Sensitivity Analysis Important?
Sensitivity Analysis is incredibly important whenever calculations need different estimations, the effects of which are difficult to project. Sensitivity Analysis will allow you to project the implications of changes to different variables. This will help you to plan for different possibilities and potential implications of certain changes.
Most popular Excel Templates
Unlocking Full Potential of Excel Data Tables (Part 2)
Van Westendorp Price Sensitivity Meter Excel Template
Analyzing Sales and Product Performance
Sensitivity analysis Excel Model with indifference curves (unlocked)
DCF & Sensitivity - Business Valuation Model
Cost per Unit (Manufacturing) Sensitivity Analysis Financial Excel Model
Have a Sensitivity Analysis Excel Template to share?
Learn more about digital publishing
Can't find what you are looking for?
Or browse through the Catalog
Think of someone who could publish?
%product_add_cart_title%
Contribute: $USD help %product_add_cart_label%
No thanks, I just want to %product_skip_link%
View all Eloqoons
New to Eloquens? Create an Account
Create an account
Why should I sign up with LinkedIn? help
Are you using this Best Practice for...
- Business help
- Consulting help
- Student / Non-Profit help
Sensitivity Analysis
Definition of Sensitivity Analysis
It is a situation similar to “what-if” analysis or the use of simulation analysis. It is used to predict the outcome of a decision based on a certain range of variables. It is especially very useful in cases where investors and stake-holders are evaluating the projects and proposals from the same industry or from different industries but driven by similar factors.
Uses of Sensitivity Analysis
Here we discuss the uses of sensitivity analysis:
Start Your Free Investment Banking Course
Download Corporate Valuation, Investment Banking, Accounting, CFA Calculator & others
- Developing Recommendations for the Decision-makers
- Feasibility testing of an optimal solution
- Identifying the sensitive variables
- Identifying critical values and break-even point where the optimal strategy changes
- Assessing the degree of risk involved in strategy or scenario
- Communication
- Allows the decision-makers to make assumptions
- Making recommendations
- Quantifying the parameters
- Developing a hypothesis for testing different scenarios
- Estimating the requirements of the input and output variables
- Understanding the relationship between input variables and the outcome
Forms of Outcomes in Sensitivity Analysis:
- Pessimistic
- Expected or the Original
Examples of Sensitivity Analysis (With Excel Template)
Let’s take an example to understand the calculation of Sensitivity Analysis in a better manner.
Sensitivity Analysis – Example #1
The expected Cash Flow forecast for the next 12 years is provided (see below). The cost of capital is 8 %, assuming the variables remain constant and determine the project’s Net Present Value (NPV). More details of the calculation are in the attached excel sheet.
Explanation
Sensitivity Analysis is used to know and ascertain the impact of a change in the outcome with the inputs’ various projected changes.
- Develop the forecasted income statement
- Determine the fixed costs and the variable costs on analyzing all the costs involved in the process
- Determine the range of Sales Factors percentages
- Increase and decrease the sales volume based on the various Sales Percentage factors
- Increase and decrease the Variable costs based on the various Sales Percentage factors
- The Fixed costs remain constant irrespective of the sales volume and changes to it.
- Determine the Net Income Before Taxes for the various sales volume. This can be arrived at by simply reducing the sales by variable and fixed costs aligned to it.
- Determine the Net Income After Taxes by applying the Tax Rate as applicable
Sensitivity Analysis – Example #2
Example of J&B Inc. The original or expected Sales Volume is $582,401 arising out of 7882 units and at the rate of $73.89.To conduct the sensitivity analysis – J&B Inc conducted two models with different input variables for the Pessimistic Model and the Optimistic Model, as seen highlighted below:
Based on these pessimistic and optimistic values of the sales and variable costs, the net income after taxes can be seen as varying. Hence, on creating the two models, we aim to arrive at a conclusion or analysis of the input factors to reach the desired Net Income figures.
The estimation of sales and, therefore, the variable costs helps decide and has a great impact on the NPV of given projects. The fixed costs and any changes to it become insignificant but remain constant across different levels of sales volume.
Relevance and Use
As per the requirement of the decision-making area, the variables and their types would differ. Accordingly, the parameters are decided, and the sensitivity analysis is conducted. Sensitivity Analysis, among other models, is put much more to use as a decision support model than merely a tool to reach one optimal solution. However, this form of analysis becomes ambiguous when the terms “pessimistic” and “optimistic” become subjective to the user and the levels considered are set as per the user.
The uncertainty of the output due to the different sources of inputs and their uncertainty results to uncertain analysis. This, in turn, leads to the quantification of uncertainty and therefore, the need to run the sensitivity analysis comes to the picture. This is a method, again, to re-calculate the output based on different alternative assumptions. The common areas of application of the models of sensitivity analysis are:
- Business: In resource allocation, guide the future data flows, identify critical assumptions
- Environmental: Impact of water purifying plant, global climate models
- Social Sciences: Econometric Models
- Engineering: Testing designs
- Chemistry: Measurement positions
- Meta-Analysis: To check whether the results are sensitive to the restrictions included Time-critical decision making.
- Multiple: Criteria decision making – Selecting the best alternative from the pool of the available different alternatives
Recommended Articles
This is a guide to Sensitivity Analysis. Here we discuss how to calculate sensitivity analysis along with practical examples. we also provide a downloadable excel template. You may also look at the following articles to learn more –
- Discounted Cash Flow Analysis
- What is the Cost-Benefit Analysis?
- Difference Between Hard Cost vs Soft Cost
- Examples of Cost of Goods Manufactured
Related Courses
*Please provide your correct email id. Login details for this Free course will be emailed to you
By signing up, you agree to our Terms of Use and Privacy Policy .
Download Sensitivity Analysis Excel Template
Forgot Password?
This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy
Explore 1000+ varieties of Mock tests View more
Submit Next Question
IMAGES
VIDEO
COMMENTS
More Free Templates. For more resources, check out our business templates library to download numerous free Excel modeling, PowerPoint
Sensitivity Analysis Excel Model analyzes how different values of a ... We are having ready to use Financial Models, Business Plan & Pitch decks for you.
This is an open-access Excel template in XLSX format that will be useful for anyone who wants to work as a Financial Analyst, Business Analyst, Consultant
Use this template to show the impact of particular changes on your business plan. Sample figures are included in the version displayed below. 2, Sensitivity
Sensitivity Analysis in Excel is an effective way to present data tables to ... A financial model is a great way to assess the performance of a business on
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
Get a financial model with a sensitivity analysis that aims to better understand the valuation effect on any change in key business plan assumptions.
Sensitivity analysis is a practice used frequently in the field of financial modeling. This kind of analysis is performed in order to estimate how various
Guide to Sensitivity Analysis. Here we discuss how to calculate sensitivity analysis along with practical examples and excel template.
Some of the gap analysis template excel are listed below. Break even analysis: The financial predictions in the business plan for a new company must include a