How To Use Goal Seek Tool In Ms Excel – What If Analysis Business Tool

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 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.

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.