Blog

How to create Break Even Point In Excel using Goal Seek

When you are considering an investment or launching a new product you want to know at what point you will "break even." The Goal Seek tool in Excel is perfect for this scenario. In this case, we want the resulting value in our formula to return "0" - our break even point.

Watch how we can able to create multiple scenarios with Goal Seek to help me to set the proper selling price and cost structure.

 New Product Details Selling Price 5.5 Units Sold 15000 Revenue (Selling Price*Units Sold) 82500 Cost Per unit 1 Variable Cost (Units Sold*Cost Per Unit) 15000 Fixed Cost 70000 Profit/Loss (Revenue -Fixed Cost-Variable Cost) -2500

As per the above calculation if we sell the Product for Rs 5.5 we will make a loss of Rs 2500 for 15000 units, so here I need to fix the rate which will make no loss no profit, In this case, we can use the option of Goal Seek.

The condition for the Goal Seek is we can only able to change a constant value in the Data, as per the above data we can able to change  Selling Price, Units Sold, Cost Per Unit, and Fixed Cost.

In the Goal Seek option, we have to update three option

 Options As per above case Set Cell Profit To Value Zero (For BEP) BY Changing Cell Selling Price Cells

Case 1

Changing Selling Price

Case 2

Changing Units to be Sell

Case 3

Changing Fixed Cost

Video Link: Watch Video

Phone : +91 8606648045
Email : info@excelskillcorner.com
Excel Skill Corner
Kerala, India.