Master The Skills Of How To Use Goal Seek In Excel

How To Use Goal Seek in Excel

Excel is a very useful program that allows you to do math without actually having to do the calculations yourself. You do have to know what numbers to plug in, but if you have that information, Excel handles the math part for you. 

Of course, another part of the process is knowing which function or feature to use in Excel because the program has many different functions and features to allow you to do a variety of mathematical equations.

You could create a monthly budget or figure interest on a loan, but you need to decide what formula, function or feature in Excel will help you to get the desired end result.

One of your options is called Goal Seek. This is a feature that can be very useful, but it also takes a little work to understand how to use it. Let’s go over what Goal Seek is and how it is used so you can begin to use it yourself in your own spreadsheets.  

Goal Seek Explained

Goal Seek is one of the built-in features within Microsoft Excel. It also is called What If, and it can help you to create an equation where you can find a certain output by changing the variables. It allows you to use trial and error to reach a desired goal.

You can see how changing one variable may affect the output. It is similar to the idea of cause and effect, letting you know what happens if something in an equation changes. It is commonly used in equations for sales, forecasting and finance.

Image Source: Pexels

It is important to note that Goal Seek is not a formula like SUM or AVERAGE. It is a feature. You use formulas within your equation and then use Goal Seek in addition to the formulas to create the What If scenario. It can be a little confusing, but once you get used to using it, you will find it is actually easy to use and very helpful in a variety of situations.

What Is Goal Seek Used For?

Goal Seek is best used when you know the output you want but are unsure of the variables it takes to get that output. It works for this type of scenario because it is a What If statement. So, essentially, any equation where you want to know what would happen if you changed this variable could be a good one to use Goal Seek on.

Image Source: Unsplash

It is important to note that Goal Seek only works when you have one variable you are trying to find. If you need to find multiple variables, you need to use a different function, such as Solver. You will understand this better once you learn how to use Goal Seek.

How To Use Goal Seek

Image Source: Unsplash

Using Goal Seek is a little different depending on which version of Excel you are using. The exact tabs you need to choose to get to the feature are the main difference, but in all newer versions, you will generally find it under the same main tab. The following steps are based on the most current version of Excel.

Step 1: Set Up Your Spreadsheet

Image Source: Pixabay

You need to start with what you want to find out. For this example, you want to know the impact of the interest rate on your car payment. The variable will be the interest rate and what you want to find out is what interest rate it takes to get the payment amount you want.

To set up your spreadsheet, you need to enter the following information into your worksheet:

• Loan amount
• Interest rate
• Months you’ll pay
• Monthly payment amount

Enter this information as labels for row one in columns A, B, C and D. Now you will enter the number on row two for information that you know, which is the loan amount and number of months you will be paying on the loan.

You probably know what payment you want, but you won’t be putting that in because that is part of what Goal Seek will do. Remember you will be solving the What If statement “how much will the payment be if the interest rate is this,” so you won’t be entering the payment or interest rate at this time.

One note, make sure that you have at least formatted the interest cell to show a percentage. You can adjust the decimal points if you want on the Home tab. Look under the Number group and at the top, choose percentage. You can then adjust the decimal point.

Step 2: Add the Formula

Image Source: Unsplash

Now you need to create the formula to get the payment amount. This would go in D2, which is under the Payment heading and produces the payment for each month. Because you are figuring a payment, you should use the payment function, PMT. You want to enter the following formula:

• Interest/12,Months,Loan amount

You use the number 12 in the formula to denote the number of months in a year so you can get an answer that is the monthly payment. Enter the cell for each variable, except the 12. So, for example, your formula may look like this:

• =PMT(B2/12,C2,A2)

Step 3: Activate Goal Seek

Image Source: Pixabay

Now you can use Goal Seek to see what happens if you change the interest rate. To do this, start with the payment cell selected where you just entered your PMT formula. You then want to click the Data tab. Look for the Forecast group and click the arrow by What-If Analysis. In the drop-down menu, click Goal Seek.

A box will pop up that requires you to enter some information. The first box is set cell. This should already be populated with the cell you had selected that contains the PMT formula you just created. If not, then just put the correct cell in this box.

The next line in the box says To value. This is the payment you want, which will be a negative amount because it is a debit or subtraction because you are paying the money out. Enter whatever payment you want.

The next line says By changing cell. This is where Goal Seek will really work. It is the cell for the interest because you want to find the interest rate to get the payment amount you want. Enter the cell for the interest rate here.

Click the OK button. You will see Goal Seek start to work. It will find the interest rate that will give you your desired payment. A new pop up comes up that tells you what the program found out.

You can go back into Goal Seek and adjust the payment if you like. You can also adjust any of the numbers you had entered to reformulate the information. You will have to re-run Goal Seek and reenter the information to get it to work again using the new information.

Examples

As mentioned, Goal Seek works best for What If scenarios, or when you want to know what the outcome will be if X happens. There are many things you can use this feature for. Here are a few examples of ways you could use it:

• To forecast election results
• To figure interest rates
• To determine how much you must sell

Let’s look at these three examples more closely to see how Goal Seek would work if used. We’ve already gone over how it is used to determine interest rates in the step-by-step above.

Forecasting election results would allow you to input different results to see how many votes it would take to get the desired results. For sales forecasting, you can look at how much you would need to sell to reach your commission goals or a certain revenue.

A Word of Caution

Remember that Excel is a computer program. It cannot think for itself. It needs information you provide to do the calculations. Goal Seek is not magic. It cannot provide you with the result you want if you do not enter the information it needs.

Always double check the numbers you put in. Make sure you are entering the correct cells. If you make a mistake Goal Seek will calculate based on that misinformation. The results will not be accurate.

The key is creating the right formula. If you are unsure of what formula to use or how to create that type of formula, then you need to do a little brushing up on the basics of Excel formulas.

Using advanced features, such as Goal Seek, will never end well if you cannot get the basic formula correct. So, take time to familiarize yourself with the basic functions in Excel before you attempt to work with Goal Seek.  

The Bottom Line

Image via Pixabay

Goal Seek is a fun tool in Excel that allows you to forecast information. It provides you the chance to look at what happens if something else happens. You can find out a lot of information very easily because Goal Seek does the math for you. 

It saves you a lot of time and hassle. Plus, it eliminates many errors you could make if doing the calculations yourself. While it may seem intimidating at first, using this feature is actually pretty easy once you learn how to do it.