What-If Analysis – Goal Seek

What-If Analysis

On the Data ribbon in Microsoft Excel is the What-If Analysis collection of tools, which include Scenario Manager, Goal Seek, and Data Table. Each of these tools offers a unique way to approach data analysis that includes unknown variables or where you want to see a number of different outcomes.

Goal Seek

Another tool in the What-If Analysis suite is Goal Seek which allows you to determine the best value for a variable to produce a desired result. One example of using the Goal Seek tool is to calculate the number of units you need to sell in order to break even in a scenario where production has both fixed and variable costs. In this example, the fixed cost is $500, the variable cost per widget is $0.25, and you make revenue of $1.00 per widget sold. The total cost in cell E9 =E5+E6*E8, while the total revenue in cell E10 =E7*E8, and the profit in cell E11 =E10 – E9. The number of widgets sold is left blank, since it is what you want to determine using Goal Seek.

What-If Analysis - Goal Seek

Go to Scenario Manager > Goal Seek to determine how many widgets you need to sell to break even. You are then presented with three choices to define your Goal Seek. First, you select the cell that you want to have a desired value. In this case it is the Profit cell, so enter E11 in Set cell. The next thing you need to select is what value you want the cell you selected to have. In this case, for a break even analysis, you want to see when Profit = 0, so enter 0 for the To value. The final thing to enter is By changing cell which tells Excel which cell you want to change to achieve the goal you specified in your first two entries. In this case you want to change cell E8, the number of widgets sold.

What-If Analysis - Goal Seek

When you press OK, it may take a moment for Excel to calculate, but if there is a possible value that will meet your goal it will appear. In this example, the break even point is 666.67 units, which you could round up to 667 units.

What-If Analysis - Goal Seek

Was this article helpful?

Related Articles

Leave a Reply