The image below shows our Bubble tea store’s output information. As you can see, we need to pay fixed costs of $2,334.72 per month and variable costs of $2352.25 per month. In this case, the net income will be $2,313.03, which was calculated by taking the fixed and variable costs and substracting it from our revenue. (Please click below picture to view excel worksheet)
Here is how to use Goal Seek to find the breakeven point.
-From the menu, select Data
-Under Data Tools,
-Click What-If Analysis
-Select Goal Seek
The dialogue box should now look like the image on your left. In this dialogue box, "Set cell" is the answer we're looking for, this is our Goal- net income . "Set cell" needs a formula or function to work with. Our formula is in cell $H$23.
"To Value" is the actual answer we're looking for. With "Set cell", we're just telling Excel where the formula is. With "To Value" you have to tell Excel what answer you're looking for. We wanted an answer of zero for our formula. So click inside the "To Value" text box and type zero.
"By Changing Cell" is the missing bit. This is the part of the formula that needs to change in order to get the answer we want.
Click OK and Excel will return the status of our test. In our example, a solution was found. The quantity is 1005. We also get a dialogue box like the one on your left.