Goal Seek

In the last section, we looked into what-if analysis and simulations, which involved modifying or simulating one or more input values to obtain new output values or the row grand total.

Goal Seek is also a type of What-if analysis, except in this method, we specify the desired output or goal value to calculate the necessary input value to achieve the goal.

Configuring Goal Seek

Let us take an example, where the product revenue is calculated by multiplying the unit price by the number of units sold. To apply Goal Seek, we need a calculated measure on which we set a target value and a data input measure that can be changed to achieve the target. In this case, we have 3 measures involved.

  • Revenue: Calculated measure

  • Unit Price: Native measure

  • Units Sold: Data input measure

Calculated measure on which Goal Seek can be applied

The revenue for the 'Preseware Air Conditioner' cell highlighted in the report is 1.27m. Let's look at how to apply Goal Seek and set a target of 1.75m for this cell.

Now, for the desired Revenue to be achieved through Goal Seek, we will choose to change one of its drivers - the Units Sold, which is a data input column. Note that you can change only one driver at a time. The Unit Price is a native measure and cannot be changed with Goal Seek.

STEP 1: Click on the required cell and select Insert -> Goal Seek. The Goal Seek pop-up window opens.

Goal seek window

The Goal Seek option is enabled only when you click on a cell containing a formula or aggregation like sum, product, etc.

STEP 2: In the pop-up window, enter the desired goal value of 1.75m.

Enter the goal value

STEP 3: Verify the target cell and its underlying formula. If you have multiple data input measures being used in the formula, you need to select the driver measure. In this case, we only have a single data input measure; let's choose Units Sold since we want to adjust the quantity sold to reach the desired revenue.

Select the data input measure to be changed

STEP 3: Click Run and then Apply to make the changes.

Apply Goal Seek

Notice how the units sold measure is updated from 92 to 127 to achieve the target revenue of 1.75m.

Goal Seek applied

Last updated