Use Goal Seek command to reverse engineer desired results
How to use Excel's Goal Seek command to reverse-calculate a required input value when you know the desired result.
When you do a calculation, you have three components: inputs, formula, and result. The result is a function of the inputs and formula. In some cases, you may want to calculate a desired result by backing into the input figures. For example, a sales person has an aggressive goal to achieve a six-figure income and wants to know the minimum sales amount he has to make to achieve his goal. In Excel, the Goal Seek command allows you to do such calculation. Note that you can only change one input figure at a time.
To initiate a Goal Seek command, click the Data tab on the ribbon, then click What-if Analysis and choose Goal Seek…
Simple Example
Set the Sales Commission (cell B3) to 100,000 by changing the Sales Amount (cell B1). After clicking OK, Excel calculates that the Sales Amount must be $2,000,000 or more.
Realistic Example: Tiered Commission Structure
The below example shows a tiered Commission Rate structure — the higher the Sales Amount, the larger the Commission Rate percentage. The sales person also earns a Base Salary plus a Target Bonus if they achieve a pre-defined Sales Amount ($1,000,000).
If you toggle Excel to view formulas (CTRL+`), you can see that it is not easy to back into the Sales Amount manually without Goal Seek.
Set the Total Compensation (cell B11) to 100,000 by changing the Sales Amount (cell E1). Goal Seek returns $1,750,000 as the required Sales Amount.
You can further analyze the result using the Data Table command to run scenario analysis across a range of Sales Amount values. See: Use Data Table for Scenario Analysis.