The Goal Seek tool is one of the powerful tools of MS Excel used to
determine the correct value for one of the arguments of a function that yielded
or returned a particular result or answer. It works on the basis of what-if
analysis and can also be used to solve practical business problems such as word problems in Mathematics, etc. In this tutorial, I will give you a step by step guide on how you can manipulate and use the MS Excel Business tool: the Goal Seek tool to solve business problems. Then I will give you some practical exercises on what-if analysis to test your knowledge.
One of the practical problems that can be solved using Goal Seek goes like this: If 720 is the answer gotten by multiplying 40 by a certain number, what must be this number or figure?
This is part 3 of chapter 6 of the Free Online Excel Tutorial Series at Microsoft Tutorial website. In the previous part (part 2), I introduced the what-if analysis tools and explained how use the scenario manager and data tables in ms excel. Also see the full tutorial course content of this Microsoft Excel Tutorial Series.
SOLUTION THE ABOVE PROBLEM USING THE GOAL SEEK TOOL IN MS EXCEL
To get the correct number using the Goal Seek tool, prepare your
worksheet like the one shown below.
Type 40 which is
the known or correct variable in cell B2, then type any number of your choice
for the unknown or incorrect variable in cell B3. Then for the expected answer,
type the following answer in cell B4: =B2*B3
and press the Enter key.
Click the Data tab,
then click the What-if Analysis drop
down arrow located in the Data tools group.
Then Select Goal Seek from the
This prompts the Goal
Seek dialogue box. Enter the values and cell references as shown below.
MS Excel requires you to provide three parameters or values
in the 3 text boxes shown above. The required parameters are:
1. SET CELL: This is
the cell that contains the formula that MS Excel uses for the Goal Seek
analysis. In the case of the above example, the set cell is cell B4 (the absolute cell reference is
2. TO VALUE: This
cell holds the desired answer. In this case your desired answer is 720.
3. BY CHANGING CELL: This
cell holds the cell reference of the cell containing the incorrect or unknown
variable. In this case, its cell B3 (the
absolute cell reference is $B$3).
Click OK twice.
Your worksheet should now resemble the one shown below with cell B3 now
containing the correct value for the specified answer in cell B4.
A MORE PRACTICAL
ILLUSTRATION OF GOAL SEEK
Now let me illustrate Goal Seek in MS Excel with a more practical
step by step example.
Joe-Links Business center made a total profit of $100000 as
at December 2016. The company’s Manage proposed a profit of $180000 for the
year 2017. Assuming they offer 50 services, at what price should they offer
each of their services to meet up with the proposed income?
Prepare your MS Excel worksheet like the one shown below.
Type 50 in cell
B2. Type in any value of your choice in cell B3 or leave it blank. Then enter
the formula: =B2*B3 in cell B4.
Activate the Goal
Seek dialogue box using the procedure given in the previous example (that
is Data tab > What-If Analysis (in Data tools panel) > Goal Seek).
Enter $B$4 in the
SET CELL text box.
Enter 180000 in
the TO VALUE text box.
Enter $B$3 in the
BY CHANGING CELL text box as shown
You can also use the bolts located at the extreme right of
each text box to specify the cells directly by first clicking on the bolt,
specifying the cell and then clicking on the bolt again to submit the
Your Excel worksheet should now look like the one shown
Cell B3 contains $3600, which is the amount the company
should offer each service in order to meet up with the proposed profit for the
Recommended MS Excel Textbook
Now let me test your knowledge on PMT function and What-if analysis
ON WHAT-IF ANALYSIS
Please try to practice these exercises and theoretical questions
on our own as it will go a long way to help you.
1. What is Amortization of loan?
2. Explain Scenario Manager as what-if analysis tool.
3. What are the functions of Scenarios?
4. Explain briefly the two types of data tables used in what if
5. Mr. Jerry is considering going for a loan of $500000 with
the interest rate of 6% to be paid back within a period of 5 months. If the
bank decides to offer him the following loan amounts:
$550000, $600000, $650000 and $700000 with the interest rate of 6.5%, 7%, 7.5%, and 8% respectively to be paid back within 6 months, 7 months, 8 months and 9 months respectively, calculate his monthly payment and total payment to amortize the loans.
Also calculate the bank’s profit at the end of each period for each loan. Also generate a scenario summary.
6. You are the Manager of ABC Business Center and you decided
to offer more services in order to increase the business profit from $90000 to
$150000. Assuming you offer each service for $100, use Goal Seek to find out
how many services you must offer in order to meet up with your proposal.
This is the end of part 3 of chapter 6 of this online tutorial series. In the next chapter (chapter 7), I will explain how to create excel worksheet template and data form in MS Excel and advanced Excel worksheet operations.
Was this tutorial helpful to you?
Inform your friends about this post by clicking the share button below. Comment below if you are hooked up along the installation process.
Also click Here to subscribe for free so that you will get our latest game updates in your email.
Labels: Microsoft Excel