The Goal Seek Function is the bread and butter of any industry using Excel to keep track of their yearly sales. For example, if you own a Redbox dispensing machine and you want to know how many sales for a new release you’ll need to make to achieve your set sales goal by a certain month, the best way to calculate this information is by using a Goal Seek Function.
How to Use the Goal Seek Function
Start by downloading the free file below to receive a sample spreadsheet to follow along.
You will see a pretty straightforward table that appears to be tracking the number of sales and the total revenue of a small business. The cost of each item is listed as a dollar and the template tracks the number of sales for that item until “Month 3.”
At the end of the sample template the total revenue is calculated from the number of sales.
Let’s say you want to make 200 dollars from this one item by the end of “Month 3” but you’re only just entering the third month. You need to know how many sales you have to get in order to reach your goal of 200 dollars (the sales numbers are small to follow along easier. The Goal Seek Function is more useful for larger figures).
In “Month 3” of the example document, you’ll notice the sales amount is set to zero because that month is not quite through. However, you want to calculate the number of sales you’ll need to reach your goal anyway.
Simply click the “Data” tab at the top of your screen and click the “What-If Analysis” button in that ribbon at the top. This will bring up a drop-down menu and then you will click on the “Goal Seek” option.
In the Goal Seek menu, select the cell that contains your formulated total revenue figure, in this case, it will be G4. The “To value” space will be the goal you want to achieve, which is 200 dollars for this example. Finally, in the “By changing cell” space, enter the “Month 3” cell that contains a value of zero, E4.
This Function will calculate the total number of sales you’ll need to make 200 dollars in E4. Now you can easily set your goals and achieve them by using the Goal Seek Function.
Download: Goal Seek Function