How to Use Goal Seek in Excel 2016

Edited 1 week ago by ExtremeHow Editorial Team

Microsoft OfficeExcelGoal SeekFunctionsSpreadsheetCalculationWindowsMacProductivitySimulation

Goal Seek is one of the most powerful and user-friendly tools available in Excel 2016. It allows you to find an unknown value using a known result. This makes it exceptionally useful for solving simple what-if analysis problems. Suppose you have an equation and you know the desired result of that equation, Goal Seek will help you find the missing number that will achieve that desired result.

Understanding goal attainment

Goal Seek is a part of the What-If Analysis tool in Excel, which is known for performing backward calculations. This means that if you know the result of a formula, but don't know one of the variables needed to get that result, Goal Seek can help fill that gap. It is commonly used in financial projections, budgeting, sales forecasting, and many other business areas where it is relevant to infer an unknown result from a known result.

Practical example of achieving a goal

Let's take a simple business sales example. You are planning a marketing campaign and have set a sales goal of $10,000 profit for the next month. You know the cost and selling price of your product, but you are unsure of how many units you need to sell to reach this profit goal. This is where goal seeking comes into play.

Steps to use to achieve goals

Using Goal Seek in Excel 2016 involves several simple steps. Each step is designed to set up and solve the equation so that you can find the missing value that satisfies the desired equation result.

Step 1: Set up your spreadsheet

First, you need to set up your data in a spreadsheet. In our example, let's fill the spreadsheet with values:

1. In cell A1, enter the label "Unit Price."
2. Enter the unit price of the product in cell B1, let's say $25.
3. In cell A2, enter the label "Total Units Sold."
4. In cell B2, enter the initial assumption, as this will change upon achieving the goal, so enter it as 200.
5. In cell A3, enter "Total Revenue."
6. In cell B3, enter the formula to calculate revenue: =B1*B2.
7. In cell A4, enter the label "Goal: Total Revenue."
8. In cell B4, enter the desired revenue target, $10000.

Step 2: Reach the goal

Once your data is set up, you can now use Goal Seek:

1. Go to the "Data" tab on the ribbon.
2. Click "What-If Analysis" in the Forecasting group.
3. Select "Goal Attainment" from the dropdown menu.

Step 3: Using the goal attainment dialog box

After reaching the target achievement, a dialog box will open asking you for three inputs:

1. "Set Cell": This should be the cell that contains your formula (for example, B3 in our example where your total revenue formula is).
2. "Pricing": Type your target, which is the price you want the formula to achieve. In this case, you would enter 10000.
3. "Changing cell": This is the cell that Excel will adjust to achieve your goal. Here, you would choose B2, "Total units sold."

Example:

Set cell: B3
Price: ₹10,000
By changing the cell: B2

Step 4: View the result

Once you've entered the correct cell, you click "OK," and Excel will calculate the necessary value in the variable cell to meet your desired goal. In this example, Excel will adjust the number of units sold to match the $10,000 revenue goal.

If successful, Excel will notify you and the value in your "Total Units Sold" cell will update to reflect the number needed to reach your goal. In our example, you will know how many units need to be sold to achieve $10000 in sales.

Step 5: Reviewing the options

While goal pursuit provides one way to achieve your goal, sometimes it is useful to explore alternatives. You may want to consider different unit prices or different conditions for the sale. You can run goal pursuit as many times as you need, adjusting your initial assumptions or objectives as you go.

Limitations of achieving goals

While goal seeking is powerful, it has some limitations. It can only work with one variable at a time, which means that for models with many changing factors, you may need to run multiple goal seeking operations. Also, goal seeking requires a linear approach, and it may not work optimally with more complex, non-linear models.

Complex situations and choices

If you encounter a situation where many variables need to be adjusted, consider using Solver, another optimization tool available in Excel. Solver allows multiple variables to be changed and is capable of running more complex analyses. Solver offers greater flexibility and analytical depth, making it more suitable for complex business models that require extensive variable manipulation.

Conclusion

Goal Seek remains a fundamental tool in Excel 2016 for understanding what values need to be adjusted to achieve a specific formula result. It is particularly beneficial in a business setting for simple scenarios such as forecasting, budgeting, and sales projections. You gain powerful insights without complex setup by using the intuitive Goal Seek steps outlined above. Keep in mind the limitations within its single-variable scope and linear solution capability. For more complex multi-variable or non-linear problems, consider using supplemental tools such as Solver to enhance your analytical power in Excel.

  Share This Article

If you find anything wrong with the article content, you can request an update