EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tools What If Analysis in Excel
Secondary Sidebar
Excel Functions
  • Excel Tools
    • Excel Shortcut Redo
    • Reduce Excel File Size
    • Quick Analysis in Excel
    • Goal Seek in Excel
    • Compare Two Lists in Excel
    • Excel Quick Analysis
    • Estimate Template in Excel
    • Pivot Table Count Unique
    • CSV Files into Excel
    • Excel Business Plan Template
    • Excel Export to PDF
    • Free Excel Template
    • Excel Repair
    • Color in Excel
    • Form Controls in Excel
    • Timecard Template in Excel
    • How to Unhide All Sheets in Excel?
    • Power Query in Excel
    • Power View in Excel
    • XML in Excel
    • Excel Evaluate Formula
    • Examples of Excel Macros
    • Consolidation in Excel
    • Ribbon in Excel
    • Excel Conditional Formatting for Dates
    • Protect Sheet in Excel
    • Data Model in Excel
    • Pivot Table Examples
    • Pivot Table Slicer
    • Pivot Table Filter
    • Watch Window in Excel
    • Slicer in Excel
    • Print Gridlines in Excel
    • Convert Numbers to Text in Excel
    • Freeze Columns in Excel
    • Spelling Check in Excel
    • Name Box in Excel
    • Solve Equation in Excel
    • Excel Spreadsheet Examples
    • What If Analysis in Excel
    • How to Print Labels From Excel
    • Excel Named Range
    • Excel TRANSPOSE Formula
    • Excel Merge and Center
    • Excel Freeze Rows
    • HLOOKUP Formula in Excel
    • Excel Create Database
    • Excel Gridlines
    • Excel Spreadsheet Formulas
    • Excel Sort By Number
    • Excel Concatenating Columns
    • Excel AutoCorrect
    • Conditional Formatting For Blank Cells
    • Excel Icon Sets
    • Excel CTRL D
    • Excel Accounting Number Format
    • Excel Regression Analysis
    • Excel Import Data
    • Excel Freeze Panes
    • Excel Calendar
    • Excel Developer Tab
    • Excel Enable Macros
    • Excel Autosave
    • Heat Map in Excel
    • Excel Toolbar
    • Excel Error Bar
    • Excel Status Bar
    • Excel Unprotect Sheet
    • Check mark in Excel
    • Excel Column Filter
    • Excel Header and Footer
    • Excel Drawing
    • Range in Excel
    • Timeline in Excel
    • Excel Lock Formula
    • Excel Table styles
    • Insert New Worksheet in Excel
    • Excel Column Lock
    • Excel Forms for Data Entry
    • QUOTIENT in Excel
    • Excel Sorting
    • Excel Sort by color
    • Excel Data Bars
    • Excel Tool for Data Analysis
    • Excel Flash Fill
    • Excel Auto Fill
    • Excel Quick Access Toolbar
    • Excel Wrap Text
    • Excel Exponential Smoothing
    • Excel ANOVA
    • Excel Merge Two Tables
    • Excel Conditional Formatting in Pivot Table
    • Dynamic Tables in Excel
    • Excel Sort by date
    • Excel Dynamic Range
    • Record Macro in Excel
    • Two Variable Data Table in Excel
    • Merge Cells in Excel
    • One Variable Data Table in Excel
    • Excel Fill Handle
    • CheckBox in Excel
    • Excel Table
    • Excel Combo Box
    • Auto Format in Excel
    • Advanced Filter in Excel
    • Excel AutoFilter
    • Excel Data Filter
    • Excel Data Validation
    • Excel Radio Button
    • Data Table in Excel
    • Text to Columns in Excel
    • Excel List box
    • Excel Solver Tool
    • Scrollbar in Excel
  • Excel Functions (12+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (36+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (220+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training
  • EXCEL Training COURSE

What If Analysis in Excel

By Madhuri ThakurMadhuri Thakur

What If Analysis in Excel

What If Analysis In Excel (Table of Contents)

  • Overview of What-if Analysis in Excel
  • Examples of What-if Analysis in Excel

Overview of What If Analysis in Excel

What-if analysis in Excel is used to test more than one value for a different formula on the basis of multiple scenarios. For this, we must have data of such kind where, for a single parameter, we would have 2 or more values for comparison. Go to the Data menu tab and click on the What-If Analysis option under the Forecast section. Select the scenario manager and give a scenario name and select the cell which contains the scenario value. By this, we can enter multiple scenarios. Now from the Goal Seek option from What-If Analysis, select the value we want to compare.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

What if the analysis is available in the “forecast” section under the “Data” tab.

what if analysis tab

There are three different kinds of tolls in the What-if analysis. Those are:

1. Scenario manager

2. Goal Seek

3. Data table

We will see each one with related examples.

Examples of What If Analysis in Excel

Here are some examples given below:

You can download this What if Analysis Excel Template here – What if Analysis Excel Template

Example #1 – Scenario Manager

The scenario manager helps to find the results for different scenarios. Let’s consider a company that wants to buy raw materials for their organization’s needs. Due to the scarcity of funds, the company wants to understand how much cost will happen for different possibilities of buying.

In these cases, we can use the scenario manager for applying different scenarios to understand the results and make the decision accordingly. Now consider Raw material X, Raw material Y, and Raw material Z. We know the price of each, and we want to know how much amount need for different scenarios.

What If Analysis in Excel Example 1-1

Now we need to design 3 scenarios like High volume purchase, Medium volume purchase, and Low volume purchase. For that, click on What if analysis and select Scenario manager.

What If Analysis in Excel Example 1-2

Once we select the scenario manager, the following window will open.

What If Analysis in Excel Example 1-3

As shown in the screenshot, currently, there were no scenarios; if we want to add scenarios, we need to click on the “Add” option available.

What If Analysis in Excel Example 1-4

Then it will ask for the Scenario name and changing cells. Give scenario name whatever you want as per your requirement. Here I am giving “High volume.”

scenario manager 3

Changing cells is the range of cells that your scenario values for different scenarios. Suppose if we observe the below screenshot. No. of units will change in each scenario; that is the reason for changing cells; we used C2:C4, which means C2, C3, and C4.

scenario manager 4

Once you give the change values, click on “OK”, then it will ask for the changing values for the High volume scenario. Input the values for high volume scenario and then click on “Add” to add another scenario “, Medium Volume.”

scenario manager 5

Give name as “Medium volume” and give the same range and click Ok then it will ask for values.

scenario manager 6

Again, click on “Add” and create one more scenario “, Low volume”, with low values like below.

scenario manager 7

Once all scenarios have done, click on “Ok” You will find the below screen.

scenario manager 8

We can find all the scenarios on the “Scenarios” screen. Now we can click on each scenario and click on Show; then you will find the results in excel; otherwise, we can view all the scenarios by clicking on the option “Summary.”

If we click on the scenario wise, the results will be changing in excel as below.

scenario manager 9

Whenever you click on the scenario and show the results at the back will change. If we want to see all the scenarios at a time to compare with others, click on the summary the following screen will come.

scenario manager 10

Select ‘Scenario Summary” and give the “Results Cells” here; the total results will be in D5; hence I given D5, click on ‘Ok’. Then a new tab will be created with the name “Scenario summary.”

scenario manager 11

Here the columns in grey color are the changing values, and the column in white color is the current value which was the last selected scenario results.

Example #2 – Goal Seek

Goal seek helps to find the input for the known output or required output.

What If Analysis in Excel Example 2-1

Suppose we will take a small example of product sale. Suppose we know that we want to sell the product at an additional price of 200 than product cost then we want to know what is the percentage we are earning the profit.

Goal Seek 1

Observe the above screenshot product cost is 500, and I have given the formula for finding the percentage profit, which you can observe in the formula bar. In one more cell, I gave the formula for the additional price which we want to sell.

Goal Seek 2

Now use Goal Seek to find the profit percentage of the different additional prices on the product’s product cost and selling price.

Goal Seek 3

When we click on the “Goal seek”, the above pop up will come. In the set, the cell gives the cell position where we are going to give the output value here the additional price amount we know which we are giving in cell C4.

Goal Seek 4

The “To Value” is the value at what additional price we want to cell 150 rupees additional to the product cost, and the changing cell is B2 where percentage changes. Click on “Ok” and see how much percentage profit if we sell an additional 150 rupees.

Goal Seek 5

The profit percentage is 30, and the selling price should be 650. Similarly, we can check for different targeted values. This goal seeks to help to find the EMI calculations etc.

Example #3 – Data Tables in What If Analysis

Now we will see the Data table. We will consider a very small example to understand better. Suppose we want to know the 10%, 20%, 30%, 40% and 50% of 5000 similarly, we want to find the percentages for 6000, 7000, 8000, 9000 and 10000.

What If Analysis in Excel Example 3-1

We have to get the percentages in each combination. In these situations, the Data table will help to find the output for a different combination of inputs. Here in Cell B3 should get 10% of 6000 and B4 10% of 7000 and so on. Now we will see how to achieve this. First, create a formula to perform this.

Data Table 2

If we observe the above screenshot, the part marked with a box is the example. In A3, we have the formula to find the percentage from A1 and A2. So inputs are A1 and A2. Now take the result of A3 to A1 as shown in the below screenshot.

Data Table 3

Now select the entire table to apply the Data table of What if Analysis as shown in the below screenshot.

Data Table 4

Once selected, click on the “Data” then “What If Analysis” from that dropdown select data table.

Data Table option

Once you select “Data table”, the below pop-up will come.

Data Table 5

In “Row Input cell”, give the cell address where the row inputs should input, which means here row inputs are 10, 20, 30,40, and 50. Similarly, give “Column input cell” as A2 here column inputs are 6000, 7000, 8000, 9000, and 10,000. Click on “Ok”, then results will appear in the form of a table as shown below.

Data Table 6

Things to Remember

  • What if Analysis is available under the “Data” menu on the top.
  • It will have 3 features 1. Scenario manager 2. Goal seeks and 3. Data table.
  • Scenario manager helps to analyze different situations.
  • Goal seek helps to know the right input value for the required output.
  • Data table helps to get results of different inputs in row-wise and column-wise.

Recommended Articles

This is a guide on What if Analysis in Excel. Here we discuss three different tools in What if Analysis and the examples and downloadable excel template. You may also look at the following articles to learn more –

  1. Pareto Analysis in Excel
  2. Excel Quick Analysis
  3. Excel Regression Analysis
  4. Excel Tool for Data Analysis
Popular Course in this category
All in One Excel VBA Bundle (120+ Courses)
  120+ Online Courses |  500+ Hours |  Verifiable Certificates |  Lifetime Access
4.9
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
Excel Training (23 Courses, 9+ Projects)4.8
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

Download What if Analysis Excel Template

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download What if Analysis Excel Template

EDUCBA

डाउनलोड What if Analysis Excel Template

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more