The Goal Seek Excel function (also known as What-if-Analysis) is a way of determining a desired outcome by altering an underlying assumption. The function effectively back-solves the issue by trial and error, entering in guesses until it finds the answer. If the revenue formula is the number of units sold multiplied by the selling price, Goal Seek may calculate how many units must be sold to generate $1 million in revenue if the selling price is known. The function comes in handy when it comes to sensitivity analysis in financial modelling.
Utilize this step-by-step tutorial to learn how to use Goal In an Excel model, try to understand the cause-and-effect relationships between the inputs and outputs:
- Select the cell containing the output or result you wish to examine. In this scenario, we have a basic model that looks at how much profit or loss a shoe merchant earns in relation to the number of shoes they sell. We’re trying to figure out how many shoes they’ll need to break even (make precisely zero profit/loss). We’ll start by putting the cursor in cell C8.
- By going to the Data area of the ribbon, selecting What-If Analysis, and then clicking Goal Seek, you may access Goal Seek. The Windows keyboard shortcut is Alt, A, W, G. The Goal Seek Excel dialogue box will popup once this is done.
- When the Goal Seek Excel dialogue box displays, connect the cell whose value you want to change and then type the value you want to change it to. We want to set the profit/loss value to zero in this situation. It entails setting the Set Cell to C8 and the To Value to 0 in the To Value field.
- To produce the solution, link to the cell that you want Excel to update. Then hit the OK button. The answer will be presented in the By Changing Cell and the Set Value Cell at this point.
- If you want to keep the test results in the spreadsheet, click OK. Note that this will update your model to include the updated values. Press Cancel if you just want to take note of the answer but go back to your original numbers (this is the most common way to use Goal Seek).
The Goal Seek Excel function is useful for a variety of tasks, including financial modelling and analysis. Among the most common explanations are:
- Analysis of the break-even point
- Scenario analysis
- Sensitivity analysis
- Understanding cause and effect
- Responding to particular inquiries
- Model stress testing