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

CUMIPMT and CUMPRINC function

CUMIPMT Cumulative interest payment function allows you to calculate the interest paid for a loan or from an investment from period A to period B. When getting a loan, CUMIPMT function can be used to calculate the total amount of interest paid in the first five months or from period 12 to period 20. A period can be a month, a week or two week. Loan Amount : 350,000.00 APR: 4.5% Down payment: 0.00 Years: 25 Payment per year: 12 From the above data, we can calculate the following: No of Period: 25 × 12 = 300 Periodic Rate: 4.5/12 = 0.375% Here is how you will substitute these values into the function. = CUMIPMT (periodic rate, No of period, vehicle price, start period, end period,  ) = CUMIPMT (0.375, 300, 350000, 1, 5, 0) In an excel worksheet, we use cell address instead of actual values as shown below: Here is the formula view of the worksheet: CUMPRINC Another related function is CUMPRINC. CUMPRINC function is used to calculate cumul
Post a Comment
Read more

Excel PMT Function

PMT function is very useful for calculating monthly payment required to payback a loan or mortgage at a fixed rate. This function require a minimum of three inputs, periodic rate, number of periods, present value or the loan amount. Here is a simple example. Home Loan: 350,000.00 Interest rate: 4.5% Number of years to repay the loan: 25 Note: To calculate monthly payment, we need to find the monthly rate and number of months as shown above. Then it is simply a matter of substituting the values into the payment function, as shown in the formula view below.
Post a Comment
Read more

BCG's Brand Advocacy Index

The Boston Consulting Group's (BCG) Brand Advocacy Index (BAI) is a metric developed to help companies measure the degree of customer advocacy for their brands. BAI focuses on the likelihood of customers to recommend a brand to others, which is a powerful indicator of brand strength and customer loyalty. Unlike other customer satisfaction or loyalty metrics, BAI emphasizes the importance of customer referrals and word-of-mouth marketing. BAI is calculated based on a survey where customers are asked about their willingness to recommend a brand to their friends, family, or colleagues. The responses are then used to compute a score, which ranges from -100 to 100. A higher BAI score indicates that a brand has more advocates who are likely to recommend the brand to others, while a lower score suggests that the brand has fewer advocates or even a higher number of detractors. BCG's research has shown that companies with higher BAI scores tend to experience higher growth rates and bett
Post a Comment
Read more