When you do a calculation, you have three components: inputs, formula, and result. The result is a function of the inputs and formula. In a more layman term, the result is calculated by a formula based on the input figures. In the below example, Sales Commission (result) is calculated by the Sales Amount (input 1) and Commission Rate (input 2). 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. Please note that you can only change one input figure at a time. You cannot change more than one input unless the inputs are related to each other by a function.
To initiate a goal seek command, please click the “Data” tab on the ribbon, then click the “What-if Analysis” and choose the “Goal Seek…” command.
Now our goal is to achieve a six-figure annual income ($100,000 or more). So we set the Sales Commission (cell B3) to be 100,000 by changing the Sales Amount (cell B1).
After we click the “OK” button, Excel calculates that the Sales Amount must be $2,000,000 or more for the sales person to achieve his goal.
The previous example is over-simplified for illustration purpose. Let’s look at a more realistic example. The below example shows a tiered Commission Rate structure. The higher the Sales Amount, the larger the percentage of Commission Rate is. In addition, the sales person entitles an annual Base Salary plus a Target Bonus if the sales person achieves a pre-defined Sales Amount ($1,000,000 in our example).
If you toggle Excel to view the formula (CTRL+`), you can see the below formula. In this case, it is not easy to back into the Sales Amount without the use of Goal Seek function.
Now our goal is to achieve six-figure annual income ($100,000 in Total Compensation). So we set the Total Compensation (cell B11) to be 100,000 by changing the Sales Amount (cell E1).
The Goal Seek command returns the result as $1,750,000 for the Sales Amount.
You can further analyze the result by using Data Table command in Excel. The Data Table command makes it easy to do scenario analysis. Please refer to this article (Use Data Table for Scenario Analysis) for more information.
