Tuesday, July 26, 2016


MS Excel Goal Seek


Goal Seek is used to get a particular result when you are not too sure of the starting value.  For example, if the answer is 56, and the first number is 8, what is the second number?  Is it 8 multiplied by 7 or 8 multiplied by 6?  You can use Goal Seek to find out.  We will try that example to get you started, and then have go at a more practical example.

Create the following Excel spreadsheet:

In the spreadsheet above, we know that we want to multiply the number in B1 by the number in B2.  The number in cell B2 is the one we are not too sure of.  The answer is going in cell B3.  Our answer is wrong at the moment, because we has a goal of 56.  To use Goal Seek to get the answer, try the following:

From the Excel menu bar, click on Data.
Locate the Data Tools panel and then What if Analysis item.  From the What if Analysis menu, select Goal Seek.
The following dialogue box appears:

The first thing Excel is looking for is “Set cell.”  This is not very well named.  It means “Which cell contains the Formula that you want Excel to use.”  For us, this is cell B3.  We have the following formula in B3:

= B1 * B2

So enter B3 in to the “Set cell” box, if it is ot already in there.

The “To value” box means “What answer are you looking for?”  For us, this is 56.  So just type 56 in to the “To value” box.

The “By changing cell” is the part you are not sure of.  Excel will be changing this part.  For us, it was cell B2.  We were not sure which number when multiplied by 8 gave the answer 56.  So type B2 in to the box.

Your Goal See dialogue box should look like ours below:

Click OK and Excel will tell you if it has found a solution:

Click OK again, because Excel has found the answer.  Your new spreadsheet will look like this one:

As you can see, Excel has changed cell B2 and replaced the 6 with a 7 – the correct answer.

We will now try a more practical example.

Goal Seek Number Two

Consider this problem:

Your business has a modest profit of 25,000.  You have set yourself a new profit Goal of 35,000.  At the moment, you are selling 1000 items at 25 each.  Assume that you will still sell 1000 items.  The question is to hit your new profit of 35,000, by how much do you have to raise your prices?

Create the spreadsheet below, and we will find a solution with Goal Seek.

The spreadsheet is split in to two:  Current sales and future sales.  We will changing the Future sales with Goal Seek.  But for now, enter the same values for both sections.  The formula to enter for B4 is this:

=B2 * B3

And the formula to enter for E4 is this:

=E2 * E3

The current price per item is 25.00.  We want to change this with Goal Seek, because our prices will be going up to hit our new profits of 35,000, So try this:

From the Excel menu bar, click on Data
Locate the Data tools panel and the What if Analysis item.  From the What if Analysis menu, select Goal Seek
The following dialogue box appears:

For “Set cell”, enter E4.  This is wehre the formula is.  The “To Value” is what we want our new profits to be.  So enter 35,000.  The “By changing cell” is the part we are not sure of.  For us, this was the price each item needs to be increased by.  This was coming from cell E3 on our spreadsheet.  So enter E3 I the “By changing cell” box.  Your Goal Seek dialogue box should now look like this:

Click OK to see if Excel can find an answer:

Excel is now telling that it has indeed found a solution.  Click OK to see the new version of the spreadsheet:

Our new Price per item is 35.  Excel has also changed the profits cell to 35,000.

No comments:

Post a Comment

Total Pageviews