← Back to Resources

What-If Analysis

Decisions & Projections

Detailed guide on how to work backwards with Goal Seek, project models with Data Tables, and solve constrained mixes using Solver.

📥 Download What-If Practice Workbooks

Follow along with our tutorials using these prepared business templates:

  • Day 5 Workbook: Basic Goal Seek and Solver layouts.
  • What-If Mastery Workbook: Specialized marketing, SaaS, & logistics cases.
Day 5 Workbook What-If Case Book

1. Goal Seek (Backward Solving)

Goal Seek is used when you know the final outcome of a formula, but you need to discover what single input value is required to achieve that result.

How to run Goal Seek

  1. Go to the Data Tab on the main ribbon.
  2. Click on What-If Analysis, and select Goal Seek...
  3. In the dialog box, set the following parameters:
    • Set cell: Enter the coordinate of your formula cell (e.g., C16).
    • To value: Type the exact target number you want to hit (e.g., 5000).
    • By changing cell: Select the input cell that Excel should alter to hit the target (e.g., C12).
  4. Click OK. Excel will instantly run calculations and show if a solution is found.
📌 Important Rule: The "Set Cell" MUST contain a formula, and that formula must be linked directly or indirectly to the "Changing Cell". If they are not linked, Goal Seek will output an error stating that the cell must contain a reference.

2. Data Tables (Sensitivity Matrices)

Data Tables let you test hundreds of different inputs in your formulas simultaneously, displaying the outputs in a neat matrix.

1-Variable Data Table

Tests one variable (e.g., how changes in interest rates affect a monthly loan payment).

  1. Set up your inputs vertically in a column (e.g., rates: 3%, 4%, 5%, 6%).
  2. In the cell directly above and to the right of your inputs, link to your main formula (e.g., type =C15 where C15 is the payment formula).
  3. Highlight the entire range of inputs and the formula reference (e.g., E12:F17).
  4. Go to Data > What-If Analysis > Data Table.
  5. Leave the Row input cell empty. Set the Column input cell to the original cell your formula uses (e.g., C13 interest rate). Click OK.

2-Variable Data Table

Tests two variables at once (e.g., interest rates in rows vs. loan terms in columns).

  1. Set up your column inputs (e.g., interest rates) down the left column.
  2. Set up your row inputs (e.g., loan terms: 15, 20, 30 years) across the top row.
  3. In the top-left corner cell of the matrix, link to your main formula (e.g., =C15).
  4. Highlight the entire matrix table (including the top row, left column, and linked formula).
  5. Go to Data > What-If Analysis > Data Table.
  6. Set Row input cell to the term cell (e.g., C14) and Column input cell to the interest rate cell (e.g., C13). Click OK.

3. Solver (Multi-Variable Optimizer)

Goal Seek only allows you to change one cell. Solver is used when you need to optimize multiple changing cells subject to specific business constraints (e.g. inventory capacity or budget limits).

Step-by-Step Solver Setup

  1. Enable Solver: Go to File > Options > Add-ins. Select Excel Add-ins from the Manage dropdown, click Go..., check the box for Solver Add-in, and click OK. The Solver button will appear on the far right of the Data Tab.
  2. Open Solver: Go to Data > Solver.
  3. Set Objective: Select the cell you want to optimize (e.g. Total Profit in D18). Select whether you want to **Max**, **Min**, or set to a specific **Value**.
  4. By Changing Variable Cells: Select the cells containing variables that can change (e.g. production quantities in C12:D12).
  5. Subject to Constraints: Click Add to define rules. For example, select the total wood used in E16, choose <=, and select the maximum limit in F16. Repeat for other limits.
  6. Run Solver: Click Solve. Excel will find the mathematically optimal mix!

4. What-If Business Case Studies

Open the **What-If Case Book** workbook and practice solving these real business challenges:

Case 1: Marketing Campaign Break-Even Workbook Tab: Case 1 - Marketing Goal Seek

You are launching a new product. You have variable costs per unit and fixed overhead costs (advertising spend + salaries). You need to hit a net target profit of exactly **$35,000**.

📋 Analytics Requirements:

  • Compute the formulas for Total Revenue (Row 17), Variable Costs (Row 18), and Net Profit (Row 20).
  • Find the exact **Units Sold** volume needed to clear all costs and secure the $35,000 profit.

💡 Solution Steps:

  1. In cell C17, enter: =C12*C13 (Units * Price).
  2. In cell C18, enter: =C12*C14 (Units * Variable Cost).
  3. In cell C20, enter: =C17-C18-C15-C16 (Revenue - Var Cost - Ad Spend - Overhead).
  4. Go to Data > What-If Analysis > Goal Seek.
  5. Set cell: C20 (Net Profit), To value: 35000, By changing cell: C12 (Units Sold). Click OK.
  6. *Result: Units Sold automatically changes from 100 to 617.*
Case 2: SaaS Retention Sensitivity Workbook Tab: Case 2 - SaaS Data Table

You run a subscription business and want to see how variations in monthly subscription prices ($19 to $99) and customer retention rates (75% to 95%) impact overall yearly revenue.

📋 Analytics Requirements:

  • Link the Data Table corner cell to the Projected Yearly Revenue formula.
  • Generate a 2-Variable Data Table to build a sensitivity matrix showing all 25 possible outcomes.

💡 Solution Steps:

  1. Ensure cell C15 contains the calculation: =C12*C14*C13*12 (Base * Retention * Price * 12).
  2. Link cell E12 (top-left of matrix table) to your formula by typing: =C15.
  3. Select the entire matrix area E12:J17.
  4. Go to Data > What-If Analysis > Data Table.
  5. Set **Row input cell** to the retention rate: C14.
  6. Set **Column input cell** to the subscription price: C13. Click OK.
  7. *Result: Excel populates the grid showing yearly projections (ranging from $1.7M at $19/75% to $10.8M at $99/95%).*
Case 3: Logistics Logistics Cost Minimization Workbook Tab: Case 3 - Logistics Solver

You have two warehouses with max inventory capacities and three stores with fixed inventory demands. You need to distribute stock to all three stores while minimizing the total shipping cost.

📋 Analytics Requirements:

  • Complete the warehouse shipping total calculations and store received total formulas.
  • Configure Solver to find the shipping volume distribution that minimizes total cost without exceeding capacities or failing store demands.

💡 Solution Steps:

  1. Ensure Row 20 calculates column sums: C20 = SUM(C18:C19), drag to column E.
  2. Ensure Col F calculates row sums: F18 = SUM(C18:E18), drag to F19.
  3. Ensure cell C23 contains the cost objective: =SUMPRODUCT(C13:E14, C18:E19).
  4. Go to Data > Solver.
  5. Set Objective: C23, select **Min**.
  6. By Changing Variable Cells: select C18:E19 (shipped volumes).
  7. Click **Add** to enter Constraints:
    • Capacity limit: F18:F19 <= H18:H19 (Total shipped <= Max Capacity).
    • Store satisfaction: C20:E20 = C21:E21 (Total received == Required Demand).
  8. Set Solving Method to **Simplex LP** and click **Solve**.
  9. *Result: Total shipping cost is optimized at exactly $5,150.*