How do you use Goal Seek in Excel?
Goal Seek in Excel is a seldom used tool that can help you find the number of units of a product you need to sell to break even for example. It can also be used to find the proper price for a product to hit a desired product margin. Below I will show some steps to use Goal Seek in Excel.
Lets start with a small example of trying to find the number of units that need to be sold to break even given a product price, product cost and some business overhead.
In the above example we see that we have some fixed overhead of $245. Our product cost is $5 and we can sell each product for $8.50. We can now use the Excel Goal Seek feature to calculate what our break even amount of products we need to sell.
Calculate Break Even for Units Sold
We first start by copying our data (including the formulas) and pasting it either on the same worksheet or new worksheet in Excel as shown in the below screen shot.
After we have a copy of our data to work with, click on the Data tab at the top of the Excel window and then find the Forecast icon group. Next click on the What If Analysis and then Goal Seek.
This will bring up the Goal Seek Popup window where we can select the necessary cells. I would like to calculate the number of Units Sold to hit break even so for the "Set Cell" box I select the E10. For the "To Value" set this to 0 to indicate 0 profit and 0 loss (break even point). The last box "By Changing cell" select our Units Sold cell (E3) and then click the OK button.
In this example the Excel Goal Seek calculation should take only a few seconds but if your formula is more complex it may take a bit longer.
As you can see from above given the information we have it will take 70 units sold to cover the product costs and overhead.
Example Two - Break Even by adjusting Product Price
In the next example we will use the Goal Seek feature in Excel to find our break even point by adjusting our product price. Based on our scenario we assume we will only sell 50 units this month which is less than the 70 we calculated from above. Knowing that, we will not break even by selling only 50 we would need to raise our product price. See our data below.
As we did in first example we click on the Data tab at the top of the Excel window and then find the Forecast icon group. Next click on the What If Analysis and then Goal Seek. Similarly we are leaving our "Set Cell" value as E10 and our "To Value" as 0. This time we need to set the "by changing cell" value to our price per unit cell E4.
After clicking OK Excel will take a few seconds and calculate our Price per Unit to find our break even point for profit. As shown below our Break Even Price is $9.90.
As you can see from the two examples above using Goal Seek can be quite useful. You are not limited to just quantity or product price. Your formula could have solved for product cost or even overhead if your situation called for it. Please have a look at some of our other Excel Tips and Tricks. Post any questions or comments below.