What-If Analysis

What-If Analysis helps you answer questions like:

What if I increase the price by 10%?
What if sales drop by 20%?
What score do I need on the final exam to get an A?

Instead of guessing, Excel calculates the answer for you.

The Most Useful Tool: Goal Seek

Goal Seek is the easiest What-If tool. It answers: "What input do I need to get this result?"

Example: Final Exam Score

You have three test scores: 85, 78, 92. Your current average is 85.

You want a final average of 90. What score do you need on the final exam?

Your spreadsheet:

	A	B
1	Test 1	85
2	Test 2	78
3	Test 3	92
4	Final Exam	?
5	Average	=AVERAGE(B1:B4)

5 rows

Using Goal Seek:

Step 1: Go to Data tab

Step 2: Click What-If Analysis

Step 3: Click Goal Seek

Step 4: Fill in the dialog:

Set cell: B5 (your average formula)
To value: 90 (the average you want)
By changing cell: B4 (the final exam score)

Step 5: Click OK

Excel tells you: You need 105 on the final exam. (Not possible, so you know 90 is not achievable.)

Example: Loan Payment

You want a monthly payment of $1,500. How much can you borrow?

Your spreadsheet:

	A	B
1	Loan Amount	200000
2	Interest Rate	6%
3	Years	30
4	Monthly Payment	=PMT(B2/12, B3*12, -B1)

4 rows

Using Goal Seek:

Set cell: B4 (payment formula)
To value: 1500
By changing cell: B1 (loan amount)

Excel calculates the maximum loan you can afford.

How Goal Seek Works

You have a formula that calculates a result
You tell Excel what result you want
You tell Excel which input to change
Excel tries different values until it finds the answer

When to Use Goal Seek

Situation	What You Know	What You Want to Find
Break-even analysis	Costs, current price	Price needed for zero profit
Loan planning	Rate, term, desired payment	How much you can borrow
Grade calculation	Current grades	Score needed on final
Sales target	Costs, target profit	Units to sell

4 rows

Data Tables: Test Multiple Values

Data Tables let you test many values at once.

Example: You want to see monthly payments for different loan amounts.

Instead of changing the loan amount 10 times, you list all amounts and Excel fills in the payments.

Step 1: List the values you want to test in a column (100000, 150000, 200000, etc.)

Step 2: Put your formula in the cell above the first value

Step 3: Select all the cells (values and formula)

Step 4: Data > What-If Analysis > Data Table

Step 5: Enter the input cell (the cell your formula uses)

Step 6: Click OK

Excel fills in the results for all values.

Scenario Manager: Compare Situations

Scenario Manager saves different sets of inputs so you can compare them.

Example: Business Planning

Best case: Sales = 100,000, Costs = 50,000
Worst case: Sales = 60,000, Costs = 70,000
Expected: Sales = 80,000, Costs = 60,000

You can switch between scenarios to see how profit changes.

To use:

Data > What-If Analysis > Scenario Manager
Click Add
Name your scenario
Select the cells that change
Enter the values
Repeat for other scenarios
Click Summary to compare all scenarios

Which Tool Should You Use?

Tool	Use When
Goal Seek	You know the result you want, need to find the input
Data Tables	You want to test many values at once
Scenario Manager	You want to save and compare different situations

3 rows

Common Mistakes

Mistake 1: Goal Seek on a cell without a formula Solution: The "Set cell" must contain a formula

Mistake 2: Wrong "By changing cell" Solution: This must be a cell that your formula depends on

Mistake 3: Goal not achievable Solution: Sometimes there is no answer (like needing 150% on an exam)

Summary

What-If Analysis tests scenarios before you make decisions
Goal Seek: Find input needed for a specific result
Data Tables: Test multiple values at once
Scenario Manager: Save and compare different situations
Data tab > What-If Analysis

These tools help you plan better by seeing results before taking action.