What-If Analysis and Decision Models

Until now, we've mostly treated spreadsheets like filing cabinets — places to store and analyze data that's already sitting in front of us. But here's where things get interesting: some of the most powerful spreadsheet work happens when you're building models — structures that let you ask "what would happen if...?" and actually explore the answer.

This is the moment spreadsheets stop being just data storage and become decision-making tools.

The Model Mindset

A decision model is fundamentally different from a data analysis. It's a spreadsheet where inputs are clearly separated from calculations, and the moment you change an input, everything downstream updates instantly. Think of a budget forecast: tweak your assumed revenue growth from 10% to 15%, and boom — you immediately see how that ripples through projected profit, headcount needs, cash position, everything.

The design principle is almost stupidly simple, but it's the difference between a usable model and a confusing mess: inputs in yellow cells, calculations in white cells, outputs in green cells (or whatever color scheme you pick — just be consistent about it). The whole thing should be set up so that someone who's never opened a spreadsheet in their life can still play with the inputs and understand what they're looking at.

This is one of the most valuable deliverables you can hand to a manager or client. Instead of "here are the numbers," you're saying "here's a tool that lets you explore the numbers yourself." That's a completely different thing.

Why This Matters: From Analysis to Insight

There's a real difference between a static report and an interactive model. A report shows you what is. A model lets you explore what could be.

Picture a nonprofit evaluating a new fundraising campaign. A static analysis might tell you: "Spend $50,000 on digital ads, raise $200,000 in donations, net $150,000." Useful. But a decision model? It lets the fundraising director ask: "What if we dial it back to $40,000? Push it to $60,000? What happens if conversion rates drop 20%? Where's the breakeven spend?"

Without rebuilding the spreadsheet from scratch each time, they can test dozens of scenarios in minutes. And that's when the interesting stuff happens — when you discover that jumping from $40k to $60k actually hurts ROI because platform costs don't scale linearly below $45k. Those kinds of insights beat static analysis every time.

The best models have a structure that's almost self-explanatory:

• Input section (top or side): clearly marked cells for users to change
• Calculation section (middle): formulas that reference the inputs
• Output section (bottom): the metrics that actually matter, in prominent cells

A well-built model can go to someone who's never touched spreadsheets, and they'll immediately know what to tweak and where to look for the answer.

Common Model Structure: A Worked Example

Let's make this concrete with a simple freelancer income model:

Inputs (yellow cells):

• Hourly rate: $75
• Hours billable per month: 120
• Project bonus (one-time): $5,000
• Tax rate: 25%

Calculations (white cells):

• Monthly billable income = Hourly rate × Hours billable = $75 × 120 = $9,000
• Monthly income after one-time project = Monthly billable income + Project bonus = $9,000 + $5,000 = $14,000
• Taxes = Monthly income after bonus × Tax rate = $14,000 × 25% = $3,500
• Net monthly income (green output cell) = $14,000 − $3,500 = $10,500

Here's the magic: change that hourly rate to $85, and the net income updates instantly. Drop billable hours to 100, same thing. The freelancer can ask "what if I raise my rate?" or "what if August is slow and I can only bill 100 hours?" and get an answer without touching a single formula. That's model thinking.

---

Goal Seek: Working Backwards from a Target

Most formulas work in one direction: inputs go in, output comes out. Goal Seek works backwards: you tell it what output you want, and it figures out what input gets you there.

Here's a classic scenario: "We need $500,000 in profit. Given our current cost structure, what revenue do we actually need?" You have a formula that calculates profit from revenue. Goal Seek adjusts your revenue input until profit hits exactly $500,000.

It's like having a spreadsheet algebra solver. Instead of manually working through "profit = revenue − costs, solve for revenue," you tell Excel "I want profit to be $500,000," and it solves for revenue.

How to Use Goal Seek

Navigate to Data → What-If Analysis → Goal Seek in Excel (or Tools → Goal Seek in Google Sheets). You'll specify three things:

1. "Set cell" — the cell with your formula (the result you're targeting)
2. "To value" — your target number (the goal)
3. "By changing cell" — the input you want Excel to adjust

Excel crunches the numbers iteratively, adjusting that input cell until the formula reaches your target.

Worked Example: Break-Even Analysis

You're launching an online course. Your model shows:

• Fixed costs (platform, hosting, development): $10,000
• Revenue per student: $97
• Expected students: 150

Your profit formula: Profit = (Students × Revenue per student) − Fixed costs

Right now: (150 × $97) − $10,000 = $4,550 profit.

But you ask the question everyone asks: "What's the break-even point? How many students do we need just to cover our costs?"

Instead of grabbing a calculator and solving the algebra, you use Goal Seek:

• Set cell: your profit formula
• To value: 0
• By changing cell: Students

Goal Seek tells you: 103 students. Check it: (103 × $97) − $10,000 = $9,991 ≈ $0. (The tiny difference is just rounding in Goal Seek's iterative process.)

Suddenly you know: you need just over 100 enrollments to break even. That's the kind of hard number that determines whether you even bother launching.

When Goal Seek Hits the Wall

Goal Seek is useful, but it shines best when there's one clear answer. If the relationship between input and output gets complicated — think nested IF statements with multiple breakpoints or non-linear relationships — Goal Seek can stumble. It might converge to a local minimum instead of the solution you actually expected.

Plus, Goal Seek only adjusts one input at a time. If you're dealing with multiple uncertain variables, you need something else.

---

Data Tables: Sensitivity Analysis at Scale

Data Tables (these are different from the Excel Tables we talked about in Chapter 3) let you run multiple what-if scenarios all at once. They're one of the most underused features in Excel, which is a shame because they're incredibly powerful.

Say you have a loan payment model and you want to see how monthly payments change across every combo of interest rate (3%, 4%, 5%, 6%, 7%) and loan term (15, 20, 25, 30 years). That's 20 different scenarios. A data table calculates all 20 in one shot and lays them out in a grid, giving you an instant picture of how sensitive your payments are to both variables.

One-Variable Data Tables

A one-variable data table tests a single input across multiple values. Let's say your revenue model is:

Annual revenue = Units sold × Price per unit = 1,000 × $50 = $50,000

You want to see what different unit volumes produce: 500, 750, 1,000, 1,250, 1,500, 2,000.

A one-variable data table gives you:

To build this in Excel:

1. List your input values (500, 750, ..., 2,000) down a column
2. Put your output formula in the cell to the right of the top input
3. Select the entire range (inputs + formula)
4. Go to Data → What-If Analysis → Data Table
5. Tell it which cell is your input (the "Units" cell)

Excel fills the rest in automatically.

Two-Variable Data Tables: The Sensitivity Matrix

This is where things get even more interesting. A two-variable data table tests two inputs at once and shows results in a grid.

Back to the loan example:

(For a $200,000 loan; numbers are illustrative.)

One table. That's all you need to see:

• How much interest rate matters (a lot — each 1% jump raises payment significantly)
• How much loan term matters (moderately — extending from 15 to 30 years cuts payment roughly 25%)
• Which combinations actually make sense (3% over 30 years gets you $549/month vs. 7% over 15 years at $817/month)

This is sensitivity analysis — testing how sensitive your outcome is to changes in your assumptions. In finance, engineering, anywhere data matters, this is gold. It shows you which inputs matter most and what the realistic range of outcomes actually looks like.

To create a two-variable table:

1. List one input's values down a column (interest rates)
2. List the other input's values across a row (terms)
3. Put your output formula in the top-left cell where they meet
4. Select the entire range
5. Data → What-If Analysis → Data Table
6. Specify your row input cell and column input cell

Practical Application: Product Pricing

You're a SaaS company deciding on pricing and feature tiers. Your profit formula is:

Profit = (Customers × Price) − (Customers × Support cost) − Fixed costs

Two variables are killing you with uncertainty: what price will customers actually pay (does demand drop at higher prices?), and how much support time per customer will you really need (how complex is this product?).

A two-variable data table with:

• Row: unit price ($29, $49, $79, $129)
• Column: support hours per customer (0.5, 1, 2, 4)

...reveals which combinations actually work and which ones are fragile. You might find "at $79, we're profitable in almost every support scenario" or "at $129, profitability craters if support needs go above 1 hour per customer." That's the conversation you need to have.

---

Scenario Manager: Saving Named Scenarios

If Goal Seek answers "what input produces this output?" and Data Tables answer "what's the output for all these combinations?", Scenario Manager tackles a different question: "Let me save a few distinct scenarios and switch between them."

Imagine three budget scenarios:

• Conservative: 5% revenue growth, 8% expense increase
• Base Case: 10% revenue growth, 5% expense increase
• Optimistic: 20% revenue growth, 2% expense increase (economies of scale kicking in)

Rather than manually tweaking inputs and writing down results every time, Scenario Manager lets you save each scenario by name and flip between them instantly.

How Scenario Manager Works

In Excel:

1. Build your model with labeled input cells
2. Data → What-If Analysis → Scenario Manager
3. Click "Add"
4. Name your scenario (like "Base Case")
5. Pick which cells are "changing cells" (your inputs)
6. Enter the values for this scenario
7. OK

Do that for each scenario. Now you can:

• Hit "Show" to instantly switch scenarios and watch the outputs update
• Generate a Scenario Summary report that shows all scenarios side by side

Worked Example: Business Plan Scenarios

A startup building a mobile app might set up:

Conservative Scenario:

• App launch delay: 3 months
• Year 1 user acquisition: 10,000 users
• Monetization: $0.50 ARPU (average revenue per user)
• Burn rate: $50,000/month

Base Case:

• App launch delay: 1 month
• Year 1 user acquisition: 50,000 users
• Monetization: $1.25 ARPU
• Burn rate: $45,000/month

Optimistic Scenario:

• App launch: on time
• Year 1 user acquisition: 150,000 users
• Monetization: $2.00 ARPU
• Burn rate: $40,000/month (scaling efficiently)

The Scenario Summary shows runway (months of cash left) for each:

This single report often does more work in investor pitches and board meetings than pages of detailed analysis. It shows leadership's realistic range and sets expectations clearly.

Scenario Manager vs. Data Tables

Pick your tool based on what you're trying to do:

• Data Tables: You're testing many combinations of 1–2 inputs; you want to see the full landscape. "Show me profit across all price and customer count possibilities."
• Scenario Manager: You have 3–5 distinct, named scenarios with multiple inputs each; you're telling a story with them. "Here's our upside, our plan, and our downside."
• Goal Seek: You have one specific target and one uncertain input. "What revenue gets us to $1M profit?"

---

Putting It Together: A Complete Decision Model

The spreadsheets that actually drive decisions combine these tools. Here's what that looks like:

1. Input section (top): Yellow cells with conservative, base, and optimistic values for the key assumptions. Switch between them with Scenario Manager.
2. Calculation section (middle): Formulas that feed on those inputs. Could be 50+ cells in a serious model.
3. Output section (bottom): Key metrics in green cells. This is what decision-makers care about.
4. Sensitivity analysis: A Data Table showing how your main outputs shift as the 1–2 most uncertain inputs change.
5. Scenario Summary: Scenario Manager output showing all three cases in one place.

That structure turns a spreadsheet into an actual decision engine. Stakeholders don't need to understand the formulas; they can tweak inputs, explore scenarios, and build real confidence in the plan.

---

This video walks through Goal Seek and Scenario Manager with real business examples — exactly the kind of decision modeling we've been talking about.