Skip to main content

🔍 What-If Analysis

Exploring Scenarios and Making Predictions

What-if analysis is a powerful spreadsheet feature that allows you to explore different scenarios by changing input values and seeing how they affect results. This technique helps with decision-making, planning, and understanding the relationships between variables in your data.

🧩 Understanding What-If Analysis​

🔮 What is What-If Analysis?​

  • A process for changing values in cells to see how those changes affect the outcome of formulas
  • A way to test different scenarios without permanently changing your data
  • A method for sensitivity analysis and forecasting
  • A tool for answering "what would happen if..." questions

🎯 When to Use What-If Analysis​

  • When planning budgets and forecasts
  • When making business decisions with multiple variables
  • When optimizing processes or resources
  • When evaluating risks and opportunities
  • When testing the sensitivity of models to changes in assumptions

📊 Benefits of What-If Analysis​

  • Make more informed decisions based on potential outcomes
  • Understand which variables have the greatest impact on results
  • Prepare for different scenarios and develop contingency plans
  • Optimize inputs to achieve desired outcomes
  • Identify potential risks and opportunities

🛠️ What-If Analysis Tools​

📋 Data Tables​

  • Show how changing one or two variables affects a formula result
  • Create a table of results for different input values
  • Useful for sensitivity analysis and finding optimal values
  • Can be one-dimensional (one input variable) or two-dimensional (two input variables)

One-Variable Data Table Example:​

// Formula in cell B1: =PMT(rate/12,years*12,loan)
// Where: loan=$100,000, years=30

// Data table showing monthly payment at different interest rates:
Rate | Payment
-----|--------
3.0% | $421.60
3.5% | $449.04
4.0% | $477.42
4.5% | $506.69
5.0% | $536.82

Two-Variable Data Table Example:​

// Formula in cell B1: =PMT(rate/12,years*12,loan)
// Where: loan=$100,000

// Data table showing payment with different rates and loan terms:
     | 15 Years | 20 Years | 25 Years | 30 Years
-----|----------|----------|----------|----------
3.0% | $690.58  | $554.60  | $474.21  | $421.60
3.5% | $714.88  | $579.96  | $500.62  | $449.04
4.0% | $739.69  | $605.98  | $527.84  | $477.42
4.5% | $764.99  | $632.65  | $555.83  | $506.69
5.0% | $790.79  | $659.96  | $584.59  | $536.82

🔄 Scenario Manager​

  • Create and save multiple sets of input values (scenarios)
  • Quickly switch between different scenarios to see results
  • Compare scenarios side by side in a summary report
  • Preserve your base case while exploring alternatives

Example scenario setup:

// Base Case Scenario:
Revenue: $1,000,000
Expenses: $800,000
Tax Rate: 25%
Profit: $150,000

// Best Case Scenario:
Revenue: $1,200,000
Expenses: $750,000
Tax Rate: 22%
Profit: $351,000

// Worst Case Scenario:
Revenue: $850,000
Expenses: $825,000
Tax Rate: 28%
Profit: $18,000

🎯 Goal Seek​

  • Find the input value needed to achieve a desired result
  • Works backward from a result to determine required inputs
  • Solves for one variable given a specific outcome
  • Useful for target-based planning and break-even analysis

Example goal seek:

// Current formula: =Revenue-Expenses-((Revenue-Expenses)*Tax_Rate)
// Current values: Revenue=$1,000,000, Expenses=$800,000, Tax_Rate=25%
// Current Profit: $150,000

// Goal Seek to find Revenue needed for $200,000 profit:
Set cell: Profit
To value: 200000
By changing cell: Revenue

// Result: Revenue needs to be $1,066,667 to achieve $200,000 profit

📊 Creating Effective What-If Models​

📝 Model Structure​

  • Clearly separate input cells, calculation cells, and output cells
  • Use named ranges for key variables
  • Document assumptions and formulas
  • Create a dashboard or summary area for key results

Example model structure:

// Input Section:
A1: "Price per Unit" | B1: 100
A2: "Units Sold"     | B2: 1000
A3: "Cost per Unit"  | B3: 60
A4: "Fixed Costs"    | B4: 20000

// Calculation Section:
A6: "Total Revenue"  | B6: =B1*B2
A7: "Variable Costs" | B7: =B2*B3
A8: "Total Costs"    | B8: =B7+B4
A9: "Profit"         | B9: =B6-B8
A10: "Profit Margin" | B10: =B9/B6

🔗 Formula Relationships​

  • Ensure all formulas correctly reference input cells
  • Create clear mathematical relationships between variables
  • Use appropriate functions for complex calculations
  • Test formulas with simple values to verify accuracy

Example of formula relationships:

// Break-even analysis formulas:
Break-even quantity = Fixed Costs / (Price - Variable Cost)
Break-even revenue = Break-even quantity * Price

// In Excel:
=Fixed_Costs/(Price-Variable_Cost)
=Fixed_Costs/(Price-Variable_Cost)*Price

📈 Sensitivity Variables​

  • Identify which variables have the greatest impact on results
  • Focus what-if analysis on these high-sensitivity variables
  • Consider ranges of possible values based on historical data or expert judgment
  • Test extreme cases to understand model limitations

🔍 Advanced What-If Techniques​

📊 Monte Carlo Simulation​

  • Run thousands of scenarios with randomly generated input values
  • Analyze the distribution of possible outcomes
  • Understand the probability of different results
  • Identify the range of likely outcomes

Example setup:

// Instead of fixed values, use random values within ranges:
Price: =RANDBETWEEN(90,110)
Units: =RANDBETWEEN(900,1100)
Costs: =RANDBETWEEN(55,65)

// Run this calculation multiple times to see distribution of results

🔄 Circular References with Iteration​

  • Create models where outputs affect inputs in a feedback loop
  • Enable iterative calculations in spreadsheet settings
  • Set appropriate maximum iterations and convergence
  • Useful for certain financial and engineering models

Example of iterative calculation:

// Loan with interest added back to principal:
A1: "Initial Principal" | B1: 10000
A2: "Interest Rate"     | B2: 5%
A3: "Current Principal" | B3: =B1+B5
A4: "Interest"          | B4: =B3*B2
A5: "Interest Added"    | B5: =B4

// This creates a circular reference that will iterate

📈 Optimization with Solver​

  • Find the optimal value for variables given constraints
  • Maximize or minimize a target value
  • Apply multiple constraints to create realistic scenarios
  • Solve complex business problems with many variables

Example solver problem:

// Product mix optimization:
Maximize: Total Profit
By changing: Units produced of Product A, B, and C
Subject to constraints:
- Total production hours ≤ 2000
- Material usage ≤ 5000 kg
- Product A production ≥ 100 units

🔗 Linking Models​

  • Connect what-if analyses across multiple worksheets
  • Create integrated business models (sales → production → finance)
  • Ensure consistent assumptions across departments
  • See how changes in one area affect the entire organization

💼 Practical Applications​

💰 Financial Planning​

  • Budget forecasting with different revenue and expense scenarios
  • Investment analysis with varying returns and time periods
  • Loan calculations with different interest rates and terms
  • Cash flow projections with timing variations

Example financial model:

// Retirement calculator:
Starting age: 30
Retirement age: 65
Starting salary: $60,000
Annual salary increase: 3%
Savings rate: 15%
Investment return: 7%
Inflation rate: 2.5%

// What-if analysis: How does changing savings rate affect retirement income?

📊 Business Analysis​

  • Pricing strategies and profit margin analysis
  • Break-even calculations for new products
  • Resource allocation optimization
  • Market share scenarios and competitive analysis

🏭 Operations Management​

  • Production planning with variable costs and demand
  • Inventory optimization with different holding costs
  • Staffing models with varying productivity assumptions
  • Supply chain scenario planning

🎯 Project Management​

  • Budget scenarios with different cost assumptions
  • Schedule analysis with varying task durations
  • Resource allocation across multiple projects
  • Risk assessment with different probability scenarios

What-if analysis transforms spreadsheets from static calculation tools into dynamic decision-making platforms. By exploring multiple scenarios, you can make more informed decisions, prepare for different futures, and optimize your strategies based on a deeper understanding of the factors that drive your results.