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?
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
resulting options.
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
$B$4).
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.
QUESTION:
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?
SOLUTION:
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
below.
NOTE:
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
selection.
Click OK twice.
Your Excel worksheet should now look like the one shown
below.
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
year 2017.
Recommended MS Excel Textbook
Get this book (Kindle format): Designing Professional Spreadsheet Management Systems Using Microsoft Excel 2013 and 2016.
Click Here to know more about the book.
Get this book (Kindle format): Designing Professional Spreadsheet Management Systems Using Microsoft Excel 2013 and 2016.
Now let me test your knowledge on PMT function and What-if analysis
tools.
PRACTICAL EXERCISES 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
analysis.
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.
No comments:
Post a Comment
WHAT'S ON YOUR MIND?
WE LOVE TO HEAR FROM YOU!