Single-cell goal-seeking (also known as backsolving) represents a rather simple concept. Excel determines what value in an input cell produces the desired result in a formula cell.
For example, you want to have a minimum of $20,000.00 of income in sales this year. Your income consists of a unit price (now you think about $25 per unit), a discount of every unit (e.g., 10%), bank fees (2,5% per unit), a sales value (about 200 units), and taxes.
On the Data tab, in the Forecast group, click What-If Analysis, and then click Goal Seek...:
The following list describes the entries for each of the items in the dialog box:
- Set cell specifies the location of the formula you use to get the result. In this case, the formula is in cell C8 and multiplies the number of units sold by the unit price.
- Type the target value in the To value box, which in this case, is $20,000.
- In the By changing cell box, specify the cell location of the variable that you want to change to reach your goal:
As soon as you click OK or press Enter, Excel begins seeking the specified goal.
- The first case: the goal is to seek the correct value of the selling units - cell C5, how many units you need to sell to achieve your $20,000 sales goal.
The solution indicated is 960 total units at the current price of $25.00:
- The second case: the goal is to find the correct unit price - cell C2, what the unit price should be to sell 500 units to reach your sales target of $20,000.
The solution indicated is $48.00 unit price to sell fixed 500 units:
See also this tip in French: Comment utiliser la Valeur cible.