The Goal Seek tool can be used to determine an unknown numerical figure to seek a particular goal number. Microsoft Excel can be used for analyzing highly utilitarian metrics like demand and sales forecasting, predicting, mathematical calculations, and more.
Steps to use Goal Seek in Excel
Let’s get started with this ultimate step-by-step guide to using the Goal seek option correctly for achieving different objectives.
For instance, you have a set of sales data for 3 months of four products. Every month you reach your minimum sales goal of 75% for all four products collectively. In the third month, you add a fifth product to your store and want to predict the number of units to be sold in a month to achieve the minimum 75% sales. We use goal seek to predict this data.
Applying Goal Seek for prediction
- First, find the percentage sales of all five products even though the fifth product column will be empty (refer to the image).
- Our current result is 62.42% of total sales as there is no entry in the fifth product.
- Now, go to the Data tab, under the Forecast section pull down What-if Analysis.
- Select Goal Seek and a new window pop up showing Goal Seek settings.
- In the Set cell box, select the cell with total sales percentage for the 3rd month (calculated in Point 1).
- In the To value box enter the goal sales percent you are looking i.e., 75.
- By changing the cell box, select the empty cell i.e., fifth product, within the same row.
- Press OK.
You now get a random predicted figure that will be required to achieve a minimum of 75% sales per month keeping all others figures the same.
Limitations: Note that you would require to determine manually for every unknown figure every time you perform a Goal Seek in excel. Dragging the cell data upward or downward will not predict the right figures for you.
Also, Goal Seek can be performed to find out only one unknown figure of a row or column in a database.
More Examples of Goal Seek
Goal Seek in Excel can be used to find lost, uncalculated or predictable data in a database.
For example, in the COVID-19 era, your school runs examinations for 6 subjects. But examinations of only 5 subjects were given by all students after which a lockdown was imposed.
You are now supposed to reckon a predicted mark for students in the 6th subject. Here is how Goal Seek can help-
There are two types of students here- an average and a nerd. You have to predict a figure for a mark lost based on the marks obtained in other 5 subjects, for the 6th subject. Let’s begin-
- Find a total percentage for all cells with and without marks for both students.
- Also, find a percentage for 5 subjects for both students to understand their performances in the attempted tests solely.
This figure will help you determine a number in Goal seek settings to set as the goal TOTAL to be achieved to predict a mark for the 6th subject.
- Go to the Data tab, under the Forecast section, pull down on What-if Analysis.
- Select Goal Seek which opens a new window.
- In the Set cell box, select the cell with a Total percentage for 6 subjects.
- In To value, enter the total percentage you found for 5 subjects.
- By changing cells, select the empty cell without marks i.e., the marks of the 6th subject.
- Hit OK. You now have the predicted marks with you.
This is how Goal Seek in Excel can help you in different ways to analyze different types of data.
This article was a detailed guide on Goal Seek in Excel and how it could be used in different scenarios. If you have any questions, drop a comment below and we will answer them!