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 totals.

Goal Seek is a also 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.

Let us take the following example where the product revenue for each region is calculated by multiplying the sale price with the number of units sold. This is accomplished by inserting a formula measure as shown below.

Revenue Calculation

The formula measure gets inserted and the grand total is also calculated.

Formula measure for Revenue inserted

In the example above, the total revenue for the Canada region is 219.40m. Let us assume we are seeking a goal of 400m as the desired revenue.

Now, for the desired revenue to be achieved through Goal Seek, we will choose to change one of its drivers - the Units Sold. Note that you can change only one driver at a time. Therefore, let us keep the Sale Price fixed.

1. Input values in the native columns cannot be changed through Goal Seek. So we will create a copy of it as a data input column to change it. In this example, a data input column has been created for the Units Sold driver.

Create Data Input Column for Units Sold

2. You can also update the formula accordingly by including this data input column (Units Sold) in place of the native column.

Data input column in the formula

3. Click on the required cell (Canada>Grand Total>Revenue) and select Insert -> Goal Seek.

Click 'Goal Seek'

Goal Seek option gets enabled only when you click on a cell that has a formula or aggregation like sum, product, etc.

4. In the pop-up window, enter the desired goal value of 400m.

Enter the goal value for revenue

5. Verify the target cell and its underlying formula. Then choose which of its drivers need to changed from the drop-down as shown below. We will choose Units Sold since we want to adjust the quantity sold to reach the desired revenue.

Select the driver to be changed

6. Click Run. You can check the preview of the results and click Apply.

Click 'Apply'

A new set of values is updated for the Units Sold to meet the specified revenue. The new values of both measures - the units sold and the revenue are distributed to the child rows automatically.

Results of Goal Seek

If you expand the column hierarchy, you can see that Inforiver also distributes the new values to the periods accordingly.

Period-wise Distribution of the results of Goal Seek

Goal Seek can also be applied to measures in rows.

Goal Seek applied on measures in rows