Image by Editor

Scenario analysis is a powerful technique for decision-making. It is a core part of financial modeling, budgeting, and forecasting because it evaluates how different inputs affect outcomes in a model. It helps you explore “what-if” questions by adjusting variables to see potential results.  Microsoft Excel provides two powerful tools for this purpose — Goal Seek and Solver — for performing scenario analysis. In this tutorial, we perform scenario analysis with Goal Seek and Solver in Excel, along with real-life examples.

What is Scenario Analysis in Excel?

Scenario analysis means evaluating models by changing one or more inputs to see how the outputs respond. Before making any decision, you may ask:

• “What price do I need to charge to reach a profit of $20,000?”
• “How many units of each product should I produce to maximize profit if I only have 1,000 machine hours?”
• “What happens to my profit if the cost increases by 10%?”

Goal Seek: It is ideal for simple, single-variable scenarios where you want to find the input value that achieves a desired output in a formula. Goal Seek answers “what value of a single input gives me this target result?”

Solver: It is known as an optimization tool. It is more advanced, allowing for multi-variable optimization with constraints, making it suitable for complex scenarios like maximizing profits or minimizing costs. Solver answers “what combination of inputs maximizes or minimizes a result, subject to constraints?”.

Using Goal Seek to Perform Scenario Analysis

Goal Seek is Excel’s simplest reverse-calculation tool. It adjusts one input cell to make a formula cell reach a specific target value. Goal Seek automatically computes the missing value. It works with only one variable, making it ideal for break-even analysis, price planning, loan payments, or profit forecasting.

Example 1: Break-Even Analysis

Goal Seek is also ideal for break-even scenarios. Let’s say you’re selling a product and want to know how many units you need to sell to cover your fixed and variable costs, so that profit becomes exactly zero.

You need to use a formula in the Profit column.

=B2*B5 - (B4 + B5*B3)

Goal Seek Setup:

• Click on cell B6 (Profit)
• Go to the Data tab >> select What-If Analysis >> select Goal Seek

• Fill in the dialog box:
  • Set cell: B6 (your profit cell)
  • To value: 0 (break-even means zero profit)
  • By changing cell: B5 (units sold)
• Click OK

Excel will calculate the required number of units, update B5, and set B6 to exactly 0 (within rounding). That is your break-even quantity. Excel will calculate that you need to sell more than 181 units to break even.

If you change the price or cost, you can rerun Goal Seek to see how the break-even point moves. In practice, you may try multiple scenarios: one with a higher price but lower expected demand, another with a lower price and higher demand, and compare.

Example 2: Loan Payment Calculation

Let’s find the loan amount you can afford based on a specific monthly payment.

Monthly Payment formula:

=PMT(B3/12, B4*12, -B2)

Goal Seek Setup:

1. Select cell B5 (Monthly Payment)
2. Go to the Data tab >> select What-If Analysis >> select Goal Seek
3. Fill in the dialog box:
   • Set cell: B5 (monthly payment)
   • To value: 1500 (your affordable payment)
   • By changing cell: B2 (loan amount)
4. Click OK

Excel calculates the maximum loan amount you can afford with a $1,500 monthly payment.

Using Solver to Perform Scenario Analysis

Solver is Excel’s advanced optimization engine. Unlike Goal Seek, Solver can adjust multiple variables and add constraints such as limited resources, budgets, or capacity. It can maximize profit, minimize cost, or reach a target value within limits. Solver is widely used in operations research, resource allocation, supply chain planning, finance, and engineering.

Example 1: Maximize Profit by Choosing Optimal Production Quantities

A factory produces two items, Product A and Product B. Each requires machine hours and labor hours, and the facility has limited resources. With limited resources, you want to maximize profit.

Enabling Solver:

Solver is an add-in that may not be active by default.

• Go to File tab >> Options
• Select Add-ins from the left menu
• At the bottom, choose Excel Add-ins from the Manage dropdown and click Go

• Check the box for Solver Add-in and click OK

Set up the product table with the corresponding formulas.

Total Label Hours:

=B4*B2 + C4*C2

Total Material Used:

= B2*B5 + C2*C5

Total Profit:

=B2*B3 + C2*C3

Solver Setup:

• Go to the Data tab >> select Solver
• Configure the Solver Parameters:
  • Set Objective: B13 (Total Profit)
  • To: Max
  • By Changing Variable Cells: B2:C2 (units produced)
• Add Constraints by clicking Add

• B8 (labor used) <= B9 (labor available)
• Add other constraints by clicking on Add

• B10 (material used) <= B11 (material available)
• B2:C2 >= 0 (can’t produce negative units)
• Click OK
• Select a Solving Method: Simplex LP because the entire model is linear
• Click Solve

Now Solver will determine the optimal production mix to maximize profit within your constraints.

Notice we got a fraction as a unit, which is not practical to handle such a situation, so we will add an Integer constraint.

• Select B2:C2 = int
• Click OK

Solver Result:

Here, Solver optimized the result and returned updated Units Produced.

This scenario demonstrates how Solver produces an optimal production plan under real-world constraints.

Example 2: Budget Allocation for Marketing

Consider a situation where a company is distributing advertising funds across online, print, and social media channels. Each channel has a different cost and expected reach. The user wants to allocate a marketing budget across channels to maximize ROI.

Expected Return:

• Social Media

=B2*B3

Drag this formula to the right for Email, Search Ads, and TV.

Total Budget Allocated:

=SUM(B2:E2)

Total Expected Return:

=SUM(B4:E4)

Solver Setup:

• Go to the Data tab >> select Solver
• Set up:
  • Set Objective: B10 (Total Expected Return)
  • To: Max
  • By Changing Variable Cells: B2:E2 (budget allocations)
• Click Add to add Constraints:
  • B8 (total budget) <= B9 ($30,000 available)
  • B2:E2 >= B5:E5 (meet minimum requirements)
  • B2:E2 <= B6:E6 (don’t exceed maximums)
• Select a solving method: GRG Nonlinear works well for this type
• Click Solve

Now the solver will display the optimized budget for each channel, which will maximize ROI.

Solver Methods Explained:

• Simplex LP: For linear problems (best for most business scenarios)
• GRG Nonlinear: For smooth nonlinear problems
• Evolutionary: For non-smooth or complex problems

Conclusion

By following this tutorial, you will be able to perform scenario analysis with Goal Seek and Solver. Goal Seek and Solver transform Excel from a simple spreadsheet tool into a complete analytical modeling platform. Goal Seek is ideal for quickly finding input values that achieve a specific target, while Solver can handle complex optimization involving many variables and constraints. These examples show the real-world scenarios you may perform for decision-making and how these tools can be applied to real business problems. By mastering both techniques, users can confidently perform scenario analysis and make data-driven decisions.