Microsoft Excel is a widely used worksheet, and it’s known that if your data changes, then all you need to do is change your input and you’ll get a new output. The spreadsheet will automatically update itself (Let’s say we’re talking about pivot tables).
However, there are times when you don’t want your data to change or at least some of it. You might want to freeze certain cells, so they stay static while any other value on the sheet changes.
Or perhaps you would like to find out what input you’ll need to get a specific output. This is where Microsoft Excel’s Goal Seek feature comes in.
You can find the goal-seek function under the “Data” tab, then click “What-If Analysis,” then select “Goal Seek” from the list of options on the right. It’s a fairly simple function used to discover a specific value or input that will yield the desired result.
To put it simply, if you’re an online business and your goal is to make 20 sales a day, and you’ve been doing well so far, but during one week you only managed to sell ten items, with excel goal seek function, we could find out what percentage of increase is needed for us to reach our original goal (20).
The Goal Seek window can be intimidating at first look because there are so many boxes and drop-down menus, but don’t worry, we’re going to break this down step by step:
What is Goal Seeking?
The term goal-seeking can be referred to as the process of finding the correct input value when only the output is known.
The destination search function can be integrated into different types of software programs, such as Microsoft Excel.
- Understand the pursuit of goals
- The goal is to find the correct input when only the output is known.
- This is usually done using the target search function in Microsoft Excel or a what-if analysis through a computer program.
- However, the Goal-seek software is only effective when there is only one input value.
Goal seeking is a term used to describe the process involved in calculating an input value based on a known output value.
This process involves the use of specific operators in formulas, which can be calculated by computer software.
Goal seeks a tool that is used in the “what-if analysis” of the software program. What-if analysis is changing the values in cells (Microsoft Excel) to see how these changes will affect the results of formulas in the spreadsheet.
When you search for a target, you perform what-if analysis on a given value or result. So, in essence, you will create a scenario that asks, “what happens if the result is X?” or a causal relationship.
For some more complex problems, people often use computer programs. Spreadsheet programs like Microsoft Excel have built-in tools for finding goals.
When the output value is known, the user can determine the input value required by the formula. If the user knows how much he can pay each month (outflow), this feature can help determine the interest rate (down payment) that the borrower should be eligible for.
But there is a limit when using the goal seek software: it is only valid when there is an input value. If you have to find two or more input values, it will not work.
If we take the above example, if we want to calculate the total loan amount and monthly repayment amount, the goal-seeking software will not work. You may need a plugin to discover different variables.
What is Goal Seek According to Excel Package
Goal seek is a built-in function of Microsoft Excel (and other spreadsheet programs such as Google Docs and Open Office Calc ) that allows the user to solve unknown variables in an equation.
The Goal Seek command changes one variable, called the “target cell,” until its value equals a certain value you specify (called the “goal”).
Excel’s goal seek uses non-linear programming (which means it can’t find roots, and there will be no easy formula like x^2+3x-5).
The important information about your model that goal seek needs to know is what cells contain your target values, which cells contain formulas that refer to those target values, and which cells contain formulas that refer to the cell containing the target value.
For example, you want to find out how many widgets you must manufacture per day if your fixed cost is $24000 and the price of each widget is $12.
You can use Goal Seek by clicking “Data,” then “What-If Analysis,” and finally clicking “Goal Seek.” This action opens up another window that allows you to input all the relevant values.
You start by entering the ‘set cell’ box (this will usually be an input variable). Next, enter what Excel refers to as “to value” (the goal). For this example, that would be 24000.
The following step is to enter all of the relevant formula cells, which are B, C4 & E4. Lastly, you need to specify which cell the Goal Seek function is allowed to change. In this example, it’s C5.
When all of this information has been entered, click “OK” and sit back as Excel calculates a solution for you!
How does Goal Geek Work in Microsoft Excel
As mentioned above, the target search software only works when you already know the output value (or result) but want to determine the input value. If you want to use the target search function in your program, you can take the following important steps:
Open a new spreadsheet: Name your column. This makes the entire content of the spreadsheet easier to read.
So, using the example above, the first column is “Loan Amount,” the second column is “Monthly Term,” the third column is “Interest,” and the last column is “Payment.”
Write down the values you know: Enter the formula for the calculation purpose, in this case, payment. You can ignore the interest rate for now. According to the formula, the interest rate is 0%.
Now you need to determine the interest rate: You can use the objective function in Excel to do this and enter the values you already have.
Goal Seek Example
Goal-seeking entrepreneurs are people who seek goals to determine how they achieve their ultimate goals. For example, a business owner might ask how much money he needs per hour to make $100,000 a year. They know the required production value ($100,000), so they must rework to find the ideal input.
This means they must determine how many hours they can (or want) to work in a year and how much money they can make per hour to achieve their goals.
In this box, you put the actual result that you’re looking for. Let’s say we want a monthly salary of $7000, and our current income is $6500 after-tax, then we would enter 7000 as a value for this box.
Ensure that the cell you select in the previous step has the formula that calculates the desired result. For example, if our formula was =B6*1.3, so B6 must be entered here.
Enter how many decimal places of accuracy do you want to show after the decimal point (4 or 5 usually works) and click OK
The boxes below are used to specify a condition or a “what if” situation for your desired result.
The first box is where you put the value that will change, and it should be to the right of your goal/desired result the cell range you want to study, the changing cell (the one that that could change), and the fixed cells (the ones that will stay static or don change). The default selection is what you would want.
Uses of Goalseek
The goal seeks excel function can determine how much additional effort you need to put in to reach the desired outcome.
You can only choose between different inputs. The amount of improvement has to be more than a certain number.
This is what we did here: assuming we could only choose between spending no extra effort (input number 0) and putting in all our efforts (input number 1), what would be the cost of having us achieve an extra ten items sold per day?
This works by trying out different input numbers until it finds one whose result matches your goal. In other words, Excel starts with zero (since this is typically used as a default value if not otherwise stated).
It uses it to calculate what would happen if no additional effort were involved, which gives you cell B4. Then it calculates what would happen if you put in all your efforts and cell B5.
Excel continues doing this until it finds a value different from your goal (in other words, it can’t find a single input number resulting in an outcome higher than the 15 units sold per day we want).
The result displayed at the bottom under the message box tells us that there’s no way of reaching our goal as long as we stick with zero as our input number.
In other words, without putting any additional effort into making those extra sales, we’re stuck with ten items sold per day, which just isn’t enough to reach our target by the end of this quarter.
This might not be very pleasant, but it makes perfect sense because ten items sold represent a 50% increase in sales compared to the six items we’re already getting.
Therefore it’s safe to assume that this would demand too much effort on our end, pushing us over the edge and making up for all our efforts thus far.
The Goal Seek option is typically used to get your input value into the desired result without changing any other values in your spreadsheet.
In other words, this means that no matter how many times you run the Goal Seek function, you’ll always be coming back with the same result as long as nothing changes in your worksheet, which makes it a great tool when trying to find out what exactly is needed to reach a certain goal or outcome.
In some cases, using goal seek can have a reverse effect, especially when you’re trying to solve for a single input value.
In other words, if running the Goal Seek function returns more than one possible outcome, then Excel will choose whichever is less intensive on your computer’s hardware or memory.
Limitations that May Make Its Use Difficult:
- You can only have one target cell, many formula cells, and one changing cell
- You must have an equal number of formula cells as target cells
- Calculated results from formulas in your model can’t be used as values
- If changing value results in a negative number being solved, the software will return #NUM! Error (which means there is no solution). In these cases, should consider using VLOOKUP
Goal Seek Formulas Explained:
- Input cell: this is the box where you enter your “known” value. This is the value you already have and want to predict or discover what could be changed. To use our example, if our business came out with a new ad that they expected would increase sales by 25%, we would put the original number of sales here, 10 in this case.
- Target cell: this is the box where you choose what result you are looking for. In our case, it will be 20, which was originally our goal when we started making sales. By choosing 20 as our target value, Excel calculates how far away from this goal we are currently at and then determines whether our input could help us get closer to it or not.
- Changing-cell: The “change” value can control the difference between our target and input values. If we wanted to be more aggressive and see what percentage increase in sales would take us to reach 15 items sold, we would put -5 here. This means that whatever difference is left once the Goal Seek calculation is done (-5 in this case), Excel will add up to find out what percentage of change is needed for us to get there. In the end, if you look below this box, at 100%, you will see that this tells Excel that anything above or equal to a 100% increase in sales is enough for us to reach our goal of 20 items sold per day.
- Outcome value: By default, Excel calculates the best possible result we could get if we put our input value equal to 0. This means that again if we use our example, Excel would calculate the best number of sales you can get if you stop advertising it. If this were an option for us, perhaps it would be better than nothing, but in most cases selling 0 items per day is not a feasible solution.
Conclusion
The knowledge of excel is important in every business today as it is useful for a large number of purposes ranging from creating payrolls, making projections, preparation of reports, a compilation of data, etc.; the list is nonexhaustive, therefore understanding the use case of the goal seek option assist in to measure goals setting for business as it allows you to seek a variable that alternates; hence you can easily calculate the additional variables to be added to achieve the desired result.
The goal seeks an option is an important tool for businesses, sales personnel, etc., as it allows them to monitor actualized goals easily and improve their overall business.
