How to use Goal Seek tool to find breakeven point in Excel
Goal Seek is a very useful tool in Excel for finding the breakeven point. Follow the article below to learn how to use the Goal Seek tool in detail.
How to use Goal Seek tool to find breakeven point in Excel
For example, let’s say we have 1,000 products to sell with a regular price of 90,000 and a promotional price of 60,000. The breakeven point we need to achieve is 80,000,000. We need to find the number of products sold at the high price and the number of products sold at the low price to achieve the breakeven point. From there, we can calculate how many products we need to sell at the regular price of 90,000 to make a profit.
How to use Goal Seek tool to find breakeven point in Excel
At this point, we need to create a table as shown in the picture above. Set the formula in B3 to be B4-B2. This means we need to find the quantity sold at the high price, subtract it from 1,000 to get the quantity sold at the low price to meet the total revenue of 80,000.
Now, we use the Goal Seek tool to find the quantity sold at the high price that meets the breakeven point of 80,000 by selecting the Data tab on the toolbar. Then, you select What-If Analysis under the Forecast section. The scroll bar appears and you choose Goal Seek.
How to use Goal Seek tool to find breakeven point in Excel
At this point, the Goal Seek dialog box appears.
In the Set cell field, you enter a reference to the total revenue cell, which is D4. In the To value field, you enter the breakeven revenue point of 80,000,000. In the By changing cell field, you enter a reference to the cell that contains the quantity sold at the high price, which is B2. Finally, click OK to complete.
How to use Goal Seek tool to find breakeven point in Excel
After Excel has finished calculating, the Goal Seek Status dialog box will appear. Here, there will be a notification that the total revenue value of 80,000,000 has been achieved in cell D4. You can see that the value in the cell for the quantity sold at the high price is 667 and the value in the cell for the quantity sold at the low price is 333. So, to achieve revenue of 80,000,000, we need to sell 667 products at a price of 90,000 and 333 products at a price of 60,000. Therefore, to make a profit, we need to sell more than 667 products at the regular price of 90,000.
How to use Goal Seek tool to find breakeven point in Excel
In this way, the above article has guided you on how to use the Goal Seek tool to find the breakeven point in Excel. We hope this article is useful to you in your work. Good luck!
