How to Use Excel Goal Seek and Solver for What-If Analysis

Excel is fantastic at taking inputs (like price and quantity) and calculating an output (total revenue). However, what if you know the exact output you need, but you don’t know the input required to get there? For example, “I need my total profit to be exactly $50,000; how many units do I need to sell to achieve that?” Instead of manually typing in different numbers until you get close, you can use Excel’s built-in What-If Analysis tools: Goal Seek and Solver.

Part 1: Using Goal Seek (Simple Scenarios)

Goal Seek is perfect for simple, one-variable problems. It works backwards from a formula to find the missing input.

The Scenario:
You have a basic financial model. Cell A1 is “Units Sold” (e.g., 100). Cell A2 is “Price per Unit” (e.g., $50). Cell A3 contains the formula =A1*A2 (Total Revenue = $5,000). Your boss wants to know exactly how many units you must sell to hit $8,500 in revenue, assuming the price stays the same.

1. Navigate to the Data tab on the ribbon.
2. In the “Forecast” group, click What-If Analysis and select Goal Seek…
3. The dialog box asks for three things:
   • Set cell: Click on your formula cell (A3). This is the goal.
   • To value: Type the number you want to achieve (8500).
   • By changing cell: Click on the variable you want Excel to manipulate (A1, “Units Sold”).
4. Click OK.

Excel rapidly iterates through thousands of numbers in the background and instantly changes cell A1 to 170, displaying the perfect solution.

Part 2: Using Solver (Complex Scenarios)

Goal Seek is great, but it has a fatal flaw: it can only change one variable at a time. What if you need to hit $50,000 in profit by changing the price of three different products, but Product A cannot be priced higher than $20 due to market constraints? For this, you need the Solver add-in.

Step 1: Enable the Solver Add-in
Solver is built into Excel but is turned off by default.

1. Go to File > Options.
2. Click Add-ins on the left pane.
3. At the bottom, ensure “Manage” is set to “Excel Add-ins” and click Go…
4. Check the box next to Solver Add-in and click OK. A new “Solver” button will appear on your Data tab.

Step 2: Configure the Solver Parameters

1. Click the new Solver button on the Data tab.
2. Set Objective: Select the cell containing your final profit calculation.
3. To: Select Value of and type your goal (e.g., 50000). Alternatively, you can ask Solver to simply find the Max or Min possible value.
4. By Changing Variable Cells: Highlight the range of cells you want Excel to manipulate (e.g., the prices of Product A, B, and C).

Step 3: Add Constraints (The Magic of Solver)
This is where Solver shines. You can force it to play by the rules of reality.

1. Click the Add button next to the “Subject to the Constraints” box.
2. Select the cell for Product A’s price.
3. Set the operator to <= (less than or equal to).
4. In the Constraint box, type 20. Click OK.
5. Add another constraint to ensure none of the prices drop below the cost of manufacturing (e.g., Price B >= 5).

Step 4: Solve the Model
Once your constraints are set, click the Solve button. The algorithm will process the multi-variable equation and present the optimal pricing strategy that hits your $50,000 goal without breaking any of the business rules you defined. You can choose to keep the solver solution or restore your original values.