If you’ve ever wondered how to determine the exact number of units you need to sell to hit a sales target, what price to charge to achieve a revenue goal or even how much you can afford to borrow for a car, then Goal Seek is the tool you need.
What Is Goal Seek?
Goal Seek is an Excel feature that works backwards from a desired outcome to find the input value needed to achieve that outcome. It’s part of the What-If Analysis tools in Excel and is perfect for solving problems without complex formulas.
Essentially, it lets you set a target value in a formula and ask Excel to adjust a specific input value to reach that target.
Example 1: Determining Sales Units to Hit a Revenue Target
Imagine you’re running a sales campaign with a revenue target of £10,000 for the month. Each unit sells for £50, and you need to figure out how many units to sell to reach the target.
Example 2: Determining the Required Price to Hit a Revenue Target
Now, let’s look at the same scenario from a different angle. This time, the revenue target is £15,000, and you estimate you can sell 100 units. How much should you charge per unit to hit the target?
Example 3: Determining Loan Amount Based on Monthly Repayment
You’re considering buying a car and need to figure out how much you can afford to borrow based on a set monthly repayment amount.
You’ve decided that you can afford to pay £200 per month. The interest rate is 10% annually, and the loan term is 3 years.
The question is…what price of car can you afford given the above figures? Let Goal Seek work it out for you!
- Click the Data tab on The Ribbon
- Select What-If Analysis > Goal Seek
- Enter the Goal Seek Parameters:
- Set Cell: B4
- To Value: 200
- By Changing Cell: B1
- Click OK and Excel will calculate the maximum loan amount you can afford.
Why Use Goal Seek?
Goal Seek is perfect for quick calculations where you know the desired outcome but not the required input. It eliminates the need for trial and error and can be applied to a wide range of scenarios, from budgeting to sales forecasting to loan analysis.
Download a copy of the file(s) used in this video: https://share.zight.com/Z4uBrYkZ