Using Excel’s Goal Seek for data analysis

Q. In your November Tech Q&A article on Excel’s Scenario Manager, you mentioned two other “what-if” tools: Goal Seek and Data Table. Can you show how those work like you did with Scenario Manager?

A. Goal Seek, Data Table, and Scenario Manager are powerful tools that can be found in a dropdown menu by going to Data > Forecast > What-if Analysis. After covering Scenario Manager last month, it makes sense to cover Goal Seek this month and Data Table in January.

Goal Seek allows you to find the necessary input value to achieve a specific goal or outcome in a formula. This feature is particularly useful when the desired result is known but the input value required to reach that result is not. For example, you can calculate the interest rate needed to meet specified loan payments, determine how much you need to save monthly to reach a future financial goal, determine how much budget is required to complete a project within a certain time frame, and calculate the number of resources needed to finish tasks by specific deadlines.

Before using Goal Seek, it’s crucial to have a clear understanding of your intended outcome. This clarity ensures that you set realistic and achievable goals within your data model. Knowing precisely what result you need allows you to effectively use Goal Seek’s capabilities, thereby enhancing your decision- making process and improving productivity.

Let’s explore Goal Seek using a couple of examples. To follow along, download this Excel workbook. A video demonstration is available at the bottom of this article on the JofA website. Note that the following content is based on Microsoft Excel 365 for PCs. Other versions of Excel may work differently.

For the first example, we will determine what quantity of a certain product we need to sell to hit a target revenue of $50,000. The price per unit is $200. In the downloaded Excel document on the first sheet, I have defined the Price per Unit in cell B1 as 200, the Quantity Sold in cell B2 as 1 (this is the starting value but will change as we goal seek), and the Total Revenue in cell B3 as =B1*B2. See these inputs in the screenshot below. Note that I have the formula displayed in cell B3 as a formula in the screenshot instead of the result.

Go to Data, click the dropdown arrow next to What-if Analysis within the Forecast group, and choose Goal Seek. The Goal Seek window will open. Select cell B3 for Set cell:, as this is the cell that will contain your target revenue. Enter 50000 for To value:, as this is your target revenue. Select cell B2 for By changing cells:, as this is the quantity you want adjusted to meet your target revenue. Click OK. The quantity sold in cell B2 will now display the quantity needed to sell to earn $50,000 of revenue, which is 250 units. See the screenshot below for the Excel spreadsheet after using Goal Seek for this example.

Now, let’s look at another example. We want to know how much we need to save annually to reach a retirement savings goal of $1 million by the time we retire in 30 years. We know we can contribute a fixed amount each year, and the account earns 5% interest annually. Goal Seek can help us determine the required annual savings contribution.

In the attached Excel document on the second sheet, I have defined the Retirement Goal in cell B1 as 1000000, the Annual Contribution in cell B2 as 1 (this is the starting value but will change as we goal seek), the Years in cell B3 as 30, the Interest Rate in cell B4 as 0.05, and the Total Savings at Retirement in cell B5 as =B2*((1+B4)^B3 – 1)/B4. See these inputs in the screenshot below. Note that I have the formula displayed in cell B5 as a formula in the screenshot instead of the result.

Go to Data, click the dropdown arrow next to What-if Analysis, and choose Goal Seek. Select cell B5 for Set cell, as this is the cell that will contain the total retirement savings you want to reach. Enter 1000000 for To value:, as this is your target retirement savings. Select cell B2 for By changing cell:, as this is the annual contribution amount you need to adjust. Click OK. The annual contribution will be adjusted in cell B2 and shows the result 15051.44, which is the annual contribution we need to make to reach our retirement savings goal of $1,000,000 in 30 years at a 5% interest rate. See the screenshot below for the Excel spreadsheet after using Goal Seek for this example.

Learning to use Goal Seek can significantly enhance your data analysis skills. Goal Seek improves decision-making and productivity by allowing you to identify the precise input values required to reach a desired result.

About the author

Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org.