Goal Seek for Target Outcomes

Every lesson so far has moved in one direction: given these assumptions, what is the forecast? Goal Seek reverses the question. Instead of asking "if growth is 8%, what is revenue?", it asks "what growth rate do we need to hit $5 million in revenue?" You define the target outcome and the single input you're willing to change — Excel finds the exact input value that produces it.

How Goal Seek Works

Goal Seek is a single-input back-solver. It takes three parameters:

• Set cell — the formula cell containing the output you want to hit. Must contain a formula, not a value;
• To value — the specific target number you want that formula to reach;
• By changing cell — the single input cell Excel is allowed to adjust. Must contain a value, not a formula.

Excel iterates — adjusting the changing cell up and down in progressively smaller steps — until the formula in the Set cell reaches the target value, or until it determines no solution exists.

1. Data → What-If Analysis → Goal Seek;
2. Set cell: the output formula cell (e.g. total forecast revenue);
3. To value: your target (e.g. 5000000);
4. By changing cell: the assumption cell to adjust (e.g. Growth_Rate).

Practical Business Questions Goal Seek Answers

Goal Seek is most valuable when a business target is fixed and you need to know whether it is achievable — and what it would take. Common applications in forecasting and planning:

Running a Revenue Target Analysis

Scenario: Your 2025 revenue target is 15% above 2024 actual revenue. What annual growth rate does the model need to achieve this?

Step 1 — Calculate the target:

In a named cell called Revenue_Target: =SUMIF(Business_Data[Year], MAX(Business_Data[Year]), Business_Data[Revenue]) * 1.15

Step 2 — Identify the output cell:

Your total 2025 forecast revenue: =SUM(Forecast_2025[Assumption_Forecast])

Step 3 — Run Goal Seek:

• Data → What-If Analysis → Goal Seek;
• Set cell: SUM(Forecast_2025[Assumption_Forecast]);
• To value: [value of Revenue_Target cell];
• By changing cell: Growth_Rate;
• Click OK.

Excel returns the exact growth rate required. If the required rate is within a plausible range — say, between 5% and 25% — the target is achievable under realistic conditions. If it requires 60% growth, the target needs to be revisited.

Running a Breakeven Analysis

Breakeven analysis is one of the most common Goal Seek applications — finding the exact revenue level, volume, or price at which profit equals zero.

Breakeven revenue (what total revenue makes profit exactly zero):

• Set cell: Total Forecast Profit =SUM(Forecast_2025[Projected_Profit]);
• To value: 0;
• By changing cell: Base_Revenue.

Breakeven growth rate (minimum growth to avoid a loss):

• Set cell: Total Forecast Profit;
• To value: 0;
• By changing cell: Growth_Rate.

The breakeven growth rate is particularly useful for risk communication — it answers the question "how bad does growth have to get before we start losing money?" Present this number alongside your Expected Case forecast in every stakeholder report.

Goal Seek Limitations

Goal Seek is powerful but has three hard constraints you need to know before relying on it:

• It only changes one input at a time. If hitting your revenue target requires both higher growth and better margins, Goal Seek cannot find that combination. Use Solver (covered briefly below) for multi-input optimization;
• It finds a local solution, not necessarily the best one. If the relationship between input and output is non-linear, Goal Seek may converge on the nearest solution rather than the global optimum. For simple revenue and profit formulas this is rarely a problem, but be aware of it for complex models;
• It does not respect constraints. Goal Seek will happily tell you that a −95% growth rate solves your breakeven problem, even though that is not a real business scenario. Always sanity-check the result against your Data Validation boundaries.

A Brief Introduction to Solver

When Goal Seek's single-input limitation becomes a problem, Excel's Solver add-in handles multi-input, constrained optimization. It is the power tool version of Goal Seek.

• Enable Solver: File → Options → Add-ins → Manage Excel Add-ins → check Solver Add-in;
• Access it: Data → Solver.

Solver takes the same three concepts as Goal Seek — objective cell, target value, changing cells — and adds constraints: rules that limit what values the changing cells can take.

A Solver setup for maximizing forecast profit subject to realistic constraints:

• Objective: MAX SUM(Forecast_2025[Projected_Profit]);
• Changing cells: Growth_Rate, Gross_Margin, Discount_Rate;
• Constraints:
  • Growth_Rate >= -0.10;
  • Growth_Rate <= 0.25;
  • Gross_Margin >= 0.30;
  • Gross_Margin <= 0.65;
  • Discount_Rate >= 0.05;
  • Discount_Rate <= 0.20.

Solver finds the combination of all three inputs that maximizes profit while respecting every constraint simultaneously. This is the foundation of optimization modeling — a topic explored further in Section 4.

Task 2: Run a Revenue Target Analysis

Goal: Find the exact growth rate required to hit a specific 2025 revenue target.

1. In your Assumptions sheet, add a cell called Revenue_Target containing the formula =SUMIF(Business_Data[Year],MAX(Business_Data[Year]),Business_Data[Revenue])*1.15. Name the cell Revenue_Target.
2. Note the current value of Growth_Rate — write it in a nearby cell labeled "Original Growth Rate" so you can restore it after Goal Seek runs.
3. Open Data → What-If Analysis → Goal Seek. Set the output cell to your total 2025 forecast revenue formula, set the target to the value of Revenue_Target, and set the changing cell to Growth_Rate.
4. Click OK and note the growth rate Goal Seek returns. Is it within a realistic range for your business context?
5. Press Cancel to discard the change and restore Growth_Rate to its original value. Confirm the forecast returns to its previous output.
6. Repeat the analysis for two additional targets: +20% above 2024 and +25% above 2024. Record all three required growth rates in a small summary table.

Task 2: Run a Breakeven and Profit Target Analysis

Goal: Identify the minimum growth rate that avoids a loss and the growth rate required to hit a specific profit target.

1. Add a cell called Profit_Target set to 15% of total 2024 actual profit: =SUMIF(Business_Data[Year],MAX(Business_Data[Year]),Business_Data[Profit])*1.15.
2. Run Goal Seek to find the breakeven growth rate: Set total forecast profit to 0 by changing Growth_Rate. Record the result — this is your downside threshold.
3. Run Goal Seek again to find the growth rate required to hit Profit_Target. Record the result.
4. Run a third Goal Seek: find the Gross_Margin required to hit Profit_Target while keeping Growth_Rate fixed at its Expected Case value. Record the result.
5. Build a small summary table in KPI_Calculations with three rows: Breakeven Growth Rate, Revenue Target Growth Rate, and Profit Target Margin. Label it "Goal Seek Results".
6. Add a fourth row manually: "Feasibility Check" — flag each result as "Realistic" or "Stretch" based on whether it falls within the boundaries you set in your Data Validation rules in Lesson 3.1.