Goal Seek Function

Using Goal Seek to Calculate Breakeven

For our term assignment, as a group we decided to open a bubble tea store at the BCIT Burnaby Campus. By using the "Goal Seek” Function of Microsoft Excel 2007, we created a formula to find out our target sale and expected monthly income from this business.

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)

In the image above we have the simple formula = Revenue-Costs. We have put this formula in cell H23 to get our net income figure. Now, We want to know how many bubble teas we have to sell in order for our store to break even(make enough profit to just cover our expenses and costs).

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

We can see the boxe as shown below:

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.

"To Value" is to instruct Goal Seek to set the net profit to zero by changing the value in a certain cell, which in our example is the target quantity (we can use goal seek to find out other net profit targets too).

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.

So Goal Seek has given us the answer we wanted. If we want to make it breakeven, we need to sell at least 1005 bubble teas per month.