|Join Nairaland / LOGIN! / Trending / Recent / New|
Stats: 2,864,888 members, 6,874,344 topics. Date: Saturday, 21 May 2022 at 09:18 PM
|Guides On How To Use Goal Seek Tool In Ms Excel – What If Analysis Business Tool by microsofttutor(m): 2:34pm On Apr 12, 2017|
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.
step by step practical tutorial on how to use the goal seek tool in ms excel
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) at http://www.microsofttut.com/2017/04/learn-what-if-analysis-tools-scenario-manager-and-data-tables.html, 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 at http://www.microsofttut.com/p/microsoft.html.
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.
First goal seek worksheet
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.
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.
Click http://www.microsofttut.com/2017/04/how-to-use-goal-seek-tool-in-ms-excel.html to study full tutorial on goal seek with screen to screen images.
|Sections: politics (1) business autos (1) jobs (1) career education (1) romance computers phones travel sports fashion health |
religion celebs tv-movies music-radio literature webmasters programming techmarket
Nairaland - Copyright © 2005 - 2022 Oluwaseun Osewa. All rights reserved. See How To Advertise. 48