Have you ever had a question like how many of Product X would I have to sell per week to make $50,000? Or how much would I have to sell my house for so that I have $20,000 for a down payment on my new place? Excel makes this kind of question easy to answer and goal seek makes it faster. We will be using Excel 2013 in this example but the process is the same for Excel 2010 or 2007.
In the first example above you could take $50,000, divide by the amount you earn from selling Product X, then divide that number by 52. That would get you there. But what if you wanted to know the number you would have to sell to make $45,000? Or another amount?
Let’s see how we could use goal seek to make this easy.
Now, in this simple example, you could simply keep changing the number of units until you have your target annual income achieved. Let me show you how Goal Seek gets you there faster.
First click on the Date tab at the top of your screen. Then select What-If Analysis
In the pull down menu select Goal-Seek
In the space next to “Set cell” type D6 (or highlight it and click on D6). This is where we want our desired income to be calculated. In “To value:” enter the amount you want to see in D6. In our example $50,000. In “By changing cell:” Enter D2. This is the variable we want the computer to change. In this case the number of units sold per week.
When you click OK the computer will then try several values until it either finds the value you are looking for or decides that one may not exist. You can then repeat this for as many different values as you desire.
If you would like to see the spreadsheet I created for this example you can download it here