What-If Analysis in Excel Practice Questions with Solutions

Introduction

What-If Analysis is a powerful Excel feature that helps you understand how changing input values affects the final result. In this chapter, you will practice Goal Seek, Scenario Manager, One-Variable Data Tables, and Two-Variable Data Tables using practical examples. These questions are designed to help you test different values, compare possible outcomes, and use What-If Analysis for business, financial, and everyday calculations. What-If Analysis in Excel – Practice questions with solutions to help you understand the concepts.

Question 1: Goal Seek – Required Sales to Reach a Target Profit

Problem Statement

A company has a monthly fixed cost of ₹80,000. The selling price of each product is ₹500 and the variable cost is ₹300 per unit.

The company wants to achieve a monthly profit of ₹1,20,000.

Use Goal Seek to calculate how many units the company needs to sell to achieve the target profit.

Excel Data

Excel Formula

Calculate profit using:

=(B2-B3)*B5-B4

Excel Solution

1. Create the profit formula.
2. Go to Data → What-If Analysis → Goal Seek.
3. Set the Profit cell as the Set cell.
4. Enter 120000 as the To value.
5. Select the Units Sold cell as the By changing cell.
6. Run Goal Seek.

Expected Result

Required units:

1,000 units

Concepts Covered

• What-If Analysis
• Goal Seek
• Target-based calculation
• Profit calculation

Question 2: Goal Seek – Required Selling Price for a Target Profit

Problem Statement

A company sells 800 units. The variable cost is ₹350 per unit and the fixed cost is ₹1,00,000.

The company wants to earn a profit of ₹1,40,000.

Use Goal Seek to find the required selling price per unit.

Excel Data

Excel Formula

=(B3-B4)*B2-B5

Excel Solution

1. Create the profit formula.
2. Go to Data → What-If Analysis → Goal Seek.
3. Select the Profit cell as the Set cell.
4. Enter 140000 as the To value.
5. Select the Selling Price cell as the By changing cell.
6. Run Goal Seek.

Expected Result

Required selling price:

₹650 per unit

Concepts Covered

• Goal Seek
• Changing input values
• Target profit
• Selling price calculation

Question 3: Scenario Manager – Compare Business Sales Scenarios

Problem Statement

A business wants to compare three possible sales situations:

• Low Sales
• Normal Sales
• High Sales

Use Scenario Manager to create these three scenarios and compare their profits.

Excel Data

Profit Formula

=(B2-B3)*B1-B4

Scenario Values

Excel Solution

1. Enter the original data.
2. Create the Profit formula.
3. Go to Data → What-If Analysis → Scenario Manager.
4. Create three scenarios:
   • Low Sales
   • Normal Sales
   • High Sales
5. Select Units Sold and Selling Price as the changing cells.
6. Enter the values for each scenario.
7. Create a Scenario Summary.

Expected Result

Scenario Manager will show the profit under different sales conditions.

Concepts Covered

• Scenario Manager
• Multiple assumptions
• Scenario comparison
• Profit analysis

Question 4: One-Variable Data Table – Different Interest Rates

Problem Statement

A person takes a loan of ₹5,00,000 for 5 years.

Compare the monthly EMI at different interest rates using a One-Variable Data Table.

Excel Data

Monthly EMI Formula

=PMT(B3/12,B4*12,-B2)

Interest Rate Values

Excel Solution

1. Enter the loan amount, interest rate, and loan period.
2. Calculate EMI using the PMT formula.
3. Enter different interest rates in a column.
4. Create the Data Table using the EMI formula.
5. Select the complete range.
6. Go to Data → What-If Analysis → Data Table.
7. Select the Interest Rate cell as the Column input cell.
8. Click OK.

Expected Result

Excel will calculate the monthly EMI for each interest rate.

Concepts Covered

• One-Variable Data Table
• PMT function
• Loan analysis
• Interest rate comparison

Question 5: One-Variable Data Table – Different Product Prices

Problem Statement

A company expects to sell 2,000 units. The variable cost is ₹250 per unit and the fixed cost is ₹2,00,000.

Compare the profit when the selling price changes from ₹300 to ₹500.

Excel Data

Profit Formula

=(B2-B4)*B1-B5

Selling Price Values

Excel Solution

1. Create the Profit formula.
2. Enter the different selling prices in a column.
3. Add the Profit formula reference for the Data Table.
4. Select the complete range.
5. Go to Data → What-If Analysis → Data Table.
6. Select the Selling Price cell as the Column input cell.
7. Click OK.

Expected Result

Excel will calculate the profit for each selling price.

Concepts Covered

• One-Variable Data Table
• Profit sensitivity
• Selling price analysis
• What-If comparison

Question 6: Two-Variable Data Table – Sales and Selling Price

Problem Statement

A company wants to compare expected profit using different numbers of units sold and different selling prices.

Use a Two-Variable Data Table to create a profit comparison table.

Excel Data

Profit Formula

=(B2-B4)*B1-B5

Units Sold

Selling Prices

Excel Solution

1. Create the Profit formula.
2. Enter selling prices across the row.
3. Enter units sold down the column.
4. Place the Profit formula reference in the top-left corner of the table.
5. Select the complete table.
6. Go to Data → What-If Analysis → Data Table.
7. Set the Row input cell to Selling Price.
8. Set the Column input cell to Units Sold.
9. Click OK.

Expected Result

Excel will calculate the profit for different combinations of Units Sold and Selling Price.

Concepts Covered

• Two-Variable Data Table
• Sensitivity analysis
• Multiple input combinations
• Profit comparison

Question 7: Goal Seek – Required Marks to Achieve a Target Average

Problem Statement

A student has the following marks in four subjects:

• English = 72
• Mathematics = 68
• Science = 75
• Computer = 70

How many marks are required in the 5th subject to achieve an overall average of 75?

Use Goal Seek.

Excel Data

Average Formula

=AVERAGE(B2:B6)

Excel Solution

1. Enter all the marks.
2. Initially enter 0 for the fifth subject.
3. Apply the AVERAGE formula.
4. Go to Data → What-If Analysis → Goal Seek.
5. Set the Average cell as the Set cell.
6. Enter 75 as the To value.
7. Select the fifth subject marks cell as the By changing cell.
8. Run Goal Seek.

Expected Result

Required marks:

90

Concepts Covered

• Goal Seek
• AVERAGE
• Target average
• Student marks analysis

Question 8: Scenario Manager – Monthly Expense Planning

Problem Statement

A person wants to compare three possible monthly expense situations:

• Low Expense
• Normal Expense
• High Expense

Use Scenario Manager to compare the total monthly expenses.

Excel Data

Total Expense Formula

=SUM(B2:B8)

Scenario Values

Excel Solution

1. Enter the Normal expense values.
2. Create the Total Expense formula.
3. Open Scenario Manager.
4. Create Low, Normal, and High scenarios.
5. Select the expense cells as the changing cells.
6. Enter the values for each scenario.
7. Generate a Scenario Summary.

Expected Result

Excel will compare the total monthly expense for all three scenarios.

Concepts Covered

• Scenario Manager
• Expense planning
• Multiple changing cells
• Scenario Summary

Question 9: Two-Variable Data Table – Loan EMI Comparison

Problem Statement

A bank customer wants to compare EMI for different loan amounts and interest rates.

The loan period is fixed at 5 years.

Excel Data

EMI Formula

=PMT(B3/12,B4*12,-B2)

Loan Amounts

Interest Rates

Excel Solution

1. Create the EMI formula.
2. Enter different interest rates across the row.
3. Enter different loan amounts down the column.
4. Place the EMI formula reference in the top-left cell of the table.
5. Select the complete table.
6. Go to Data → What-If Analysis → Data Table.
7. Set the Row input cell to Interest Rate.
8. Set the Column input cell to Loan Amount.
9. Click OK.

Expected Result

Excel will calculate the EMI for different combinations of loan amounts and interest rates.

Concepts Covered

• Two-Variable Data Table
• PMT function
• Loan comparison
• Sensitivity analysis

Question 10: Complete What-If Analysis – Business Target Planning

Problem Statement

A company has the following monthly data. Management wants to find out how many units must be sold to achieve the target profit.

Excel Data

Profit Formula

=(B2-B3)*B4-B5

Excel Solution

1. Enter the Selling Price, Variable Cost, Units Sold, and Fixed Cost.
2. Create the Profit formula.
3. Calculate the current profit.
4. Go to Data → What-If Analysis → Goal Seek.
5. Set the Profit cell as the Set cell.
6. Enter 300000 as the To value.
7. Select Units Sold as the By changing cell.
8. Run Goal Seek.
9. Test different unit values to understand how the profit changes.
10. Record the required units for the target profit.

Expected Result

Required units to achieve the target profit:

2,333.33 units

Since the company cannot sell a fraction of a unit, the practical target is:

2,334 units

Concepts Covered

• Goal Seek
• Target profit
• Business planning
• What-If Analysis
• Financial modeling

Key Takeaways

• Goal Seek in Excel is used when you know the desired result and want Excel to find the required input value.
• Scenario Manager is useful for comparing multiple possible situations.
• One-Variable Data Table shows how changing one input affects the result.
• Two-Variable Data Table shows how combinations of two inputs affect the result.
• What-If Analysis can be used for sales planning, pricing, profit calculations, loan analysis, expense planning, and financial modeling.
• Goal Seek finds an input required to reach a specific target, while Data Tables compare multiple possible input values.
• Scenario Manager allows you to save and compare different sets of assumptions.
• The main purpose of What-If Analysis is to analyze possible results under different assumptions.

FAQs

1. What is What-If Analysis in Excel?

What-If Analysis is an Excel feature that allows you to change input values and analyze how those changes affect the calculated result.

2. When should I use Goal Seek?

Use Goal Seek when you know the desired result and want Excel to determine the input value required to achieve that result.

3. What is the difference between Goal Seek and Scenario Manager?

Goal Seek finds an input value needed to reach a target result. Scenario Manager stores and compares different sets of input values.

4. What is a One-Variable Data Table?

A One-Variable Data Table shows how changing one input value affects a calculated result.

5. What is a Two-Variable Data Table?

A Two-Variable Data Table compares the results produced by different combinations of two input variables.

6. Can Goal Seek change multiple cells?

Goal Seek is designed to adjust one changing cell to reach a target result. For multiple changing inputs, Scenario Manager or other Excel modeling techniques can be used.

7. How many scenarios can I create in Scenario Manager?

You can create multiple scenarios in a worksheet and compare different sets of assumptions.

8. How is What-If Analysis used in business?

It can be used for sales forecasting, pricing, profit planning, expense analysis, loan calculations, and financial modeling.

9. Does What-If Analysis replace Excel formulas?

No. What-If Analysis works with formulas and input cells to analyze how different assumptions affect the results.

10. Where can I find What-If Analysis in Excel?

You can generally find it under:

Data → What-If Analysis

This menu includes tools such as Goal Seek, Scenario Manager, and Data Table.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar