Blog

How to create Break Even Point In Excel using Goal Seek

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.