Skip to main content

Excel What-if Analysis - Goal Seek

 
Excel Goal Seek is a tool that is available in most versions of Excel, including Excel 2010, Excel 2013, Excel 2016, and Excel 2019. Excel Goal Seek is a tool that allows users to find the input value required to achieve a desired output value. It is particularly useful when dealing with complex formulas that have multiple variables. Instead of manually adjusting the input value to achieve the desired output value, Goal Seek automates the process by doing the calculations for you.

How to Use Excel Goal Seek

Using Excel Goal Seek is a simple process that involves three steps:
  1. Set up your worksheet: Enter the formula that you want to use and set the cell containing the formula as the output cell. Then, identify the cell that contains the input value that you want to adjust.
  2. Open the Goal Seek dialog box: To do this, go to the Data tab in the Excel ribbon and click on the "What-If Analysis" button. From the dropdown menu, select "Goal Seek".
  3. Enter your values: In the Goal Seek dialog box, you will see three fields: "Set cell", "To value", and "By changing cell". In the "Set cell" field, enter the cell that contains the output value that you want to achieve. In the "To value" field, enter the desired output value. In the "By changing cell" field, enter the cell that contains the input value that you want to adjust.Once you have entered your values, click "OK". Excel will automatically calculate the input value required to achieve the desired output value.
 
Examples of Excel Goal Seek

Let's say that you run a small business selling custom t-shirts. You have a formula that calculates the profit you will make on each t-shirt based on the cost of materials, the price of the t-shirt, and the number of t-shirts sold. You want to use Goal Seek to determine how many t-shirts you need to sell to make a profit of $1000.
  • Set up your worksheet: Enter your formula for calculating profit in a cell and set it as the output cell. Enter the number of t-shirts sold in another cell and set it as the input cell.
  • Open the Goal Seek dialog box: Go to the Data tab in the Excel ribbon, click on the "What-If Analysis" button, and select "Goal Seek".
  • Enter your values: In the Goal Seek dialog box, enter the cell containing the output value (profit), the desired output value ($1000), and the cell containing the input value (number of t-shirts sold).Excel will automatically calculate the number of t-shirts you need to sell to make a profit of $1000.

Another example of using Goal Seek is calculating the interest rate required to pay off a loan in a specific time frame. Let's say you have a loan of $10,000 that you want to pay off in two years. You want to use Goal Seek to determine the interest rate required to achieve this goal.
  • Set up your worksheet: Enter the formula for calculating interest in a cell and set it as the output cell. Enter the loan amount, the time period, and the interest rate in separate cells.
  • Open the Goal Seek dialog box: Go to the Data tab in the Excel ribbon, click on the "What-If Analysis" button, and select "Goal Seek".
  • Enter your values: In the Goal Seek dialog box, enter the cell containing the output value (interest), the desired output
 
Let's say that you are planning to buy a house and you want to find out what you can afford. Using goal seek you can calculate house price that you can afford based on monthly payment that you can afford. 

But first, we need a model as shown in the diagram below. This is because goal seek uses the model (formulas and functions) that you have created to do the calculations. 


Note that to calculate Periodic Rate, Number of Payment Periods and Monthly Payment use the formula shown below: 


Now you will have the following model using which you can find monthly payment by giving the loan amount, interest rate, years (to repay loan) and payment per year. 










If you are making bi-weekly payment, simply change the payments per year to 24. Above model can be used to calculate monthly payment for any loan amount, interest rate, years to repay, number of payments per year. 

This model allows you to calculate Monthly Payments, but what if you want to find out the amount of loan that you can afford if you are able to pay $3000 per month. This is were goal seek come handy. 





To access goal seek, select Data ribbon, click on What-If Analysis and select Goal seek.


The following dialog appears.

In set cell field give the cell address monthly payments B10. In To Value field type 3000 which is the monthly value we can afford. In By Changing Cell field, type B2, this refers to loan amount that we want to alter to get a monthly payment of 3000.
Now click OK. Excel find the loan amount you can afford. 
 
 
Self Check Questions

1. What is Excel Goal Seek?
a. A tool that helps you find input value required to achieve a desired output value
b. A tool that performs complex calculations in Excel
c. A tool that helps you analyze data in Excel
d. A tool that helps you create charts and graphs in Excel

2. Which tab in the Excel ribbon contains the Goal Seek function?
a. Home
b. Insert
c. Data
d. Review

3. How many steps are involved in using Excel Goal Seek?
a. One
b. Two
c. Three
d. Four

4. What is one benefit of using Excel Goal Seek?
a. Increased accuracy
b. Increased complexity
c. Increased flexibility
d. Increased speed

5. Which of the following is a limitation of Excel Goal Seek?
a. It can adjust multiple input variables at once
b. It can only find one solution at a time
c. It can only be used in Excel 2019
d. It cannot be used for financial analysis

6. When using Goal Seek, which field do you enter the desired output value?
a. Set cell
b. To value
c. By changing cell
d. None of the above
Answer: b

7. Which tool should you use if your formula has multiple input variables?
a. Excel Goal Seek
b. Solver
c. VBA
d. None of the above

8. What is one example of using Goal Seek in a business setting?
a. Finding the square root of a number
b. Calculating the area of a triangle
c. Determining the number of t-shirts to sell to make a profit
d. None of the above

9. Which of the following versions of Excel does not have Goal Seek?
a. Excel 2010
b. Excel 2013
c. Excel 2016
d. Excel 2019

10. What is one benefit of using Excel Goal Seek in financial analysis?
a. Increased complexity
b. Increased flexibility
c. Increased accuracy
d. Increased speed

Answer for the quick check questions : 
1:a, 2:c, 3:c, 4:a, 5:b, 6:b, 7:b, 8:c, 9:d, 10:c

Comments

Post a Comment

Popular posts from this blog

A Comprehensive Evaluation of the Internal Consulting Process: Steps and Considerations

Introduction Internal consulting has emerged as a critical function within organizations, offering in-house expertise to solve complex business problems and drive change. It closely mirrors external consulting in methodology but is differentiated by the consultant's intimate knowledge of the organization and a vested interest in its long-term success. This article aims to evaluate the key steps involved in the internal consulting process, offering insights into each phase's significance and challenges. Steps in the Internal Consulting Process The internal consulting process can generally be segmented into five distinct stages: Initial Assessment, Data Collection and Analysis, Solution Development, Implementation, and Evaluation. Below is an evaluation of each step: Step 1: Initial Assessment Objective: To understand the problem or opportunity area and define the scope of the project. Significance: A well-defined scope ensures that the consulting project stays focused and manage...
Post a Comment
Read more

The Evolving Landscape of Consulting Practice: Changes and Implications

Introduction Consulting is a field that thrives on its ability to adapt to market demands and emerging trends. As businesses evolve due to technological advancements, shifts in consumer behavior, and fluctuations in global markets, consulting practices must keep pace. This article explores some of the significant changes currently transforming the consulting industry and discusses their implications for both consultants and clients. Technological Disruption Data Analytics and Artificial Intelligence Consulting firms are increasingly integrating data analytics and artificial intelligence into their service offerings. These technologies allow consultants to offer data-driven insights that can significantly enhance strategic decision-making. This evolution means consultants now need skills in data interpretation and analysis, alongside their traditional expertise in business strategy. Virtual Consulting Platforms The advent of digital platforms enables consulting services to be offered re...
Post a Comment
Read more

The Skillset of Internal Consultants: A Comparative Analysis

Introduction In the organizational landscape, the role of internal consultants has gained prominence due to the increasing complexity of business problems and the need for specialized in-house expertise. While many skills required for internal consulting overlap with those of external consultants, there are distinct abilities that set them apart. This article aims to compare and contrast these skill sets to provide a clearer understanding of what makes an effective internal consultant. Skills Common to Both Internal and External Consultants Problem-Solving Both types of consultants need to excel at identifying issues and creating viable solutions. Critical thinking and analytical skills are paramount for dissecting complex situations and recommending actionable strategies. Communication Excellent communication skills are a must for any consultant. Whether it’s making a presentation to stakeholders, writing a report, or simply discussing ideas with a team, effective communication is key...
Post a Comment
Read more