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

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.

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

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.

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

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