The Trial-and-Error Problem
You own a small business selling custom coffee mugs. You have built an Excel spreadsheet that calculates your total profit based on three variables: the cost to make a mug, the price you sell it for, and the number of mugs you sell.
Your spreadsheet currently shows that if you sell 500 mugs, you make $2,000 in profit. However, your goal for this month is to make exactly $5,000 in profit so you can buy a new piece of equipment. You need to know exactly how many mugs you must sell to hit that $5,000 target.
If you don’t know how to use Excel’s advanced features, you will likely engage in painful trial and error. You will click the “Units Sold” cell, type 800, and see the profit jump to $3,200. You’ll type 1,000, and it jumps to $4,000. You’ll type 1,300, and it goes to $5,200. You waste time guessing numbers until you stumble upon the correct input.
To eliminate this guessing game, Microsoft built a powerful “what-if” analysis tool directly into Excel called Goal Seek. Instead of changing the inputs to see the result, Goal Seek allows you to define the final result you want, and Excel will mathematically calculate backwards to find the exact input required to achieve it.
How to Access Goal Seek
Goal Seek is hidden inside the data forecasting menus.
- Open your spreadsheet. (Ensure your final “Profit” cell contains a formula that references your “Units Sold” cell).
- Click the Data tab at the very top of the Excel ribbon.
- Look for the “Forecast” group on the right side of the ribbon and click What-If Analysis.
- Select Goal Seek… from the dropdown menu.
Configuring the Calculation
A very small dialogue box will appear with only three fields. These three fields tell Excel exactly how to run the backward calculation.
- Set cell: This is the cell containing the final number you want to change. In our scenario, this is your “Total Profit” cell (e.g., C10). Click inside this box, then click Cell C10.
- To value: This is your target number. You want your profit to be $5,000. Type
5000directly into this box. - By changing cell: This is the specific variable you want Excel to adjust to reach the target. You want to know how many mugs to sell. Click inside this box, then click your “Units Sold” cell (e.g., C4).
Executing the Calculation
Once the three fields are filled out, click OK.
Excel will instantly run hundreds of micro-calculations in the background, testing different numbers in the “Units Sold” cell until the formula in the “Profit” cell equals exactly $5,000. The numbers on your spreadsheet will visibly cycle for a split second, and then Goal Seek will announce that it has found a solution.
Your spreadsheet will now show that in order to hit your $5,000 profit goal, you must sell exactly 1,250 mugs. You achieved the perfect answer in three seconds without typing a single guess.
Conclusion
Stop playing guessing games with your financial models. By mastering Excel’s Goal Seek tool, you can define your target outcomes and force the software to calculate backwards, revealing exactly what inputs are required to achieve your business goals.