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