DAY 13: Excel - What-If Analysis (Goal Seek, Data Tables, Scenarios)

Exercise 1: Use Goal Seek to Find Break-Even Point

Goal: Learn to use Goal Seek to find the input value needed to achieve a target result.

Setup - Create a simple profit model:

1. Cell A1: "Units Sold" | B1: 1000
2. Cell A2: "Price per Unit" | B2: 50
3. Cell A3: "Cost per Unit" | B3: 30
4. Cell A4: "Fixed Costs" | B4: 15000
5. Cell A5: "Profit" | B5: =B1*(B2-B3)-B4

Step-by-Step Instructions:

1. Verify your formula works: B5 should show 5000 (profit with current inputs)
2. Open Goal Seek: Data tab → What-If Analysis → Goal Seek
3. In the Goal Seek dialog:
   • Set cell: B5 (the profit formula cell)
   • To value: 0 (we want to break even, zero profit)
   • By changing cell: B1 (units sold)
4. Click OK - Goal Seek will calculate the result
5. Review the solution: B1 should now show 750 units - that's your break-even point
6. Click OK to accept or Cancel to revert

💡 Tips:

• Set cell must contain a formula - Goal Seek adjusts inputs to make the formula reach target
• By changing cell must be a value cell - cannot be a formula
• Goal Seek finds one solution - there might be other solutions, but it finds the first one
• Undo available: Press Ctrl+Z if you want to revert after accepting
• Iterative calculation: Goal Seek tries different values until it finds the answer (may take a few seconds)

✅ Expected Result: Goal Seek finds that you need to sell 750 units to break even (Profit = $0)

Exercise 2: Create One-Variable Data Table for Loan Payments

Goal: Use Data Tables to test multiple scenarios simultaneously.

Setup - Create a loan calculator:

1. Cell A1: "Loan Amount" | B1: 200000
2. Cell A2: "Interest Rate" | B2: 5%
3. Cell A3: "Loan Term (years)" | B3: 30
4. Cell A4: "Monthly Payment" | B4: =PMT(B2/12,B3*12,-B1)

Step-by-Step Instructions:

1. Create the Data Table structure:
   • In cell D1, type: =B4 (reference to monthly payment)
   • In cells D2:D12, type different interest rates: 3%, 3.5%, 4%, 4.5%, 5%, 5.5%, 6%, 6.5%, 7%, 7.5%, 8%
2. Select the Data Table range: Select D1:E12 (formula cell + rates + empty column for results)
3. Go to Data tab → What-If Analysis → Data Table
4. In the Data Table dialog:
   • Row input cell: (leave blank)
   • Column input cell: B2 (the interest rate cell)
   • This tells Excel: "Test each value in column D by putting it in B2"
5. Click OK - Excel fills column E with monthly payments for each rate
6. Format the results: Select E2:E12 → Format as Currency

💡 Tips:

• Data Table creates array formulas - you can't edit individual cells, must edit the whole table
• Column input = variable in column: Use column input when your test values are in a column
• Don't delete the reference formula (D1): Data Table needs it to know what to calculate
• Updates automatically: Change B1 (loan amount) and watch all payments recalculate
• Can add more formulas: Put more references in F1, G1, etc. for multiple outputs

✅ Expected Result: A table showing monthly payments at different interest rates (e.g., 3%: $843, 4%: $955, 5%: $1,074, etc.)

Exercise 3: Create Two-Variable Data Table for Sensitivity Analysis

Goal: Analyze how two variables interact using a two-variable Data Table.

Using the same loan calculator from Exercise 2:

Step-by-Step Instructions:

1. Create the table structure:
   • Cell G1: =B4 (reference to monthly payment formula)
   • Column G2:G12: Interest rates 3%, 3.5%, 4%...8% (one variable)
   • Row H1:L1: Loan terms 15, 20, 25, 30, 35 years (second variable)
2. Select the entire range: G1:L12 (includes formula, row headers, column headers, and empty grid)
3. Data → What-If Analysis → Data Table
4. In the dialog:
   • Row input cell: B3 (loan term - because terms are in the row)
   • Column input cell: B2 (interest rate - because rates are in the column)
5. Click OK - Excel fills the entire grid with monthly payments for each combination
6. Format and analyze: Use Conditional Formatting to highlight high/low payments

💡 Tips:

• Two-variable = matrix of results: Shows all combinations of your two inputs
• Corner cell (G1) must be formula: This is what gets calculated for each combination
• Row input = horizontal values, Column input = vertical values
• Powerful for sensitivity analysis: Quickly see how two factors interact
• Add conditional formatting: Home → Conditional Formatting → Color Scales to visualize the data

✅ Expected Result: A matrix showing monthly payments for all combinations of interest rates and loan terms

Exercise 4: Create Business Scenarios with Scenario Manager

Goal: Use Scenario Manager to save and compare different sets of assumptions.

Setup - Create a simple revenue model:

1. Cell A1: "Units Sold" | B1: 10000
2. Cell A2: "Price" | B2: 50
3. Cell A3: "Cost" | B3: 30
4. Cell A4: "Revenue" | B4: =B1*B2
5. Cell A5: "Total Cost" | B5: =B1*B3
6. Cell A6: "Profit" | B6: =B4-B5

Step-by-Step Instructions:

1. Open Scenario Manager: Data → What-If Analysis → Scenario Manager
2. Click "Add" to create first scenario:
   • Scenario name: "Best Case"
   • Changing cells: B1:B3 (select all three input cells)
   • Click OK
   • Enter values: Units: 15000, Price: 55, Cost: 28
   • Click OK
3. Click "Add" again for second scenario:
   • Scenario name: "Worst Case"
   • Changing cells: B1:B3
   • Enter values: Units: 7000, Price: 45, Cost: 33
   • Click OK
4. Create third scenario:
   • Scenario name: "Most Likely"
   • Changing cells: B1:B3
   • Enter values: Units: 10000, Price: 50, Cost: 30
   • Click OK
5. Click "Summary" button in Scenario Manager
6. Choose "Scenario Summary"
7. Result cells: Select B4:B6 (Revenue, Cost, Profit)
8. Click OK - Excel creates a new sheet with scenario comparison

💡 Tips:

• Scenarios are named sets of inputs: Save multiple "what-if" assumptions
• Easily switch between scenarios: Select a scenario and click "Show" to apply those values
• Scenario Summary is a report: Creates a formatted table comparing all scenarios
• Can have up to 32 changing cells: Complex models supported
• Protection available: Lock scenarios to prevent accidental changes

✅ Expected Result: A scenario summary report showing Best Case profit, Worst Case profit, and Most Likely profit side-by-side

Exercise 5: Advanced - Combining Goal Seek with Scenarios

Goal: Use multiple What-If tools together for comprehensive analysis.

Step-by-Step Instructions:

1. Using the revenue model from Exercise 4: Ensure you have Best Case, Worst Case, Most Likely scenarios saved
2. Show the "Most Likely" scenario: Data → What-If Analysis → Scenario Manager → Select "Most Likely" → Show
3. Use Goal Seek to find target units:
   • Set cell: B6 (Profit)
   • To value: 250000 (target profit)
   • By changing cell: B1 (Units)
   • Note the result (e.g., 12500 units needed)
   • Click Cancel (don't save yet)
4. Now try with "Best Case" scenario:
   • Show Best Case scenario
   • Run Goal Seek again with same target ($250K profit)
   • Compare: Fewer units needed in Best Case due to better margins
5. Create a summary table:
   • In a new area, create columns: Scenario | Units Needed for $250K Profit
   • Manually record the Goal Seek results for each scenario
   • This shows how your scenarios affect the path to your goal

💡 Tips:

• Combine tools for deeper insights: Scenarios set the environment, Goal Seek finds the path
• Document your analysis: Create summary tables to show management
• What-if questions answered: "How many units do we need to sell in each scenario to hit our profit target?"
• Real-world application: Budget planning, sales forecasting, resource allocation

✅ Expected Result: Understanding of how different scenarios require different actions to achieve the same goal

🔄 Connection to SQL: What-If Analysis is like SQL CASE statements and hypothetical queries - testing different conditions!