MS Excel Goal Seek
HOME
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:
Comments
Post a Comment