People often ask questions on “What-if ?” Say for example What if I had a few more votes would I have won? Which brings us to the next question, how many votes would concede a victory. At this point in time, a Goal Seek approach comes into play. Another instance, a manager wishes to know how many units must be sold in order to reach the desired profit level. The Excel’s Goal Seek feature can assist in answering these type of forecasting questions.
What is goal seek?
Goal Seek is an Excel functionality wherein the end result is desired but how to achieve it is the question. In simple terms, it is a way of solving for desired output by changing an assumption that drives it. This functionality uses a trial and error approach to back-solve the problem by entering the guesses until it reaches the desired output.
For example, If you have sales data which consists of revenue i.e. a product of quantity sold and selling price. This feature can direct you to know how many quantities you have to sell in order to reach the desired revenue.
Three simple steps to achieve the desired goal
- Set cell: Reference to a cell, where you want to change the desired output.
- To value: Enter the value you want to achieve in the future, i.e. the GOAL.
- By changing cell: Reference to a cell, that should be changed in order to achieve To value.
Simple Example Of Goal Seek
Let take a look at an example to know how goal seek works. Below is the data we are going to use for the example.
The data consists of revenue worth Rs. 25,000 followed by selling of1000 units each priced at Rs. 25, Now let us see how many units we have to sell in order to achieve a Revenue of Rs 43,000.
There are many ways to find it as follows.
- We can change the units cell value until we reach the desired value.
- We can divide the desired value with the price to get the units sold.
- We can use goal seek.
Recommendation: The best way is to use the goal seek, as the other ways are time-consuming and it saves the need to use your geeky math calculations. Follow the steps as below;
Step1: Select goal seek option, there are two ways to select.
Manual method: Go to Data tab > What-If-Analysis > Goal seek.
Shortcut key: Alt + A + W + G
Step2: A goal seek dialog box will be displayed as below .
Step3: Follow the instructions as below :-
- Set cell: Give a reference where you want to enter your desired future value, in this case, it is D15.
- To value: under this point enter the desired future value, in this case, it is Rs. 43,000.
- By changing cell: Give a reference of cell value to be changed to get the desired output in this case we need to change units sold which is in D11.
Click on ok after entering the values.
Step4: It displays goal seek status dialog box showing the target value and current value which should be Rs. 43,000 then click on OK.
Step5: In this step, the value will change according to the inputs entered in the goal seek dialog box, as shown in the image.
So in order to achieve a revenue of Rs. 43,000 you have to sell 1720 units.
- Set cell value should be given a reference to a cell.
- To value should be your future desired goal.
- By changing cell should be given a reference.
- It doesn’t disturb the formulas in the cell during the calculation.
To know more on Excel visit our YouTube Channel https://www.youtube.com/channel/UCD0p7s8LCEUl5c_bP2ErFnA