Goal Seek
Last updated
Last updated
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.
The formula measure gets inserted and the grand total is also calculated.
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.
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.
You can also update the formula accordingly by including this data input column (Units Sold) in place of the native column.
Click on the required cell (Canada>Grand Total>Revenue) and select Insert -> Goal Seek.
The goal seek option gets enabled only when you click on a cell containing a formula or aggregation like sum, product, etc.
In the pop-up window, enter the desired goal value of 400m.
Verify the target cell and its underlying formula. Then choose which of its drivers needs to be 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.
You can either apply the changes to all input rows and columns or choose which rows and column measures should be changed to meet the desired goal value. Let us first apply changes to all the input rows and columns.
Click Run and then Apply to make the changes.
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.
If you expand the column hierarchy, you can see that Inforiver also distributes the new values to the periods accordingly.
Note: Goal Seek can also be applied to measures in rows.
Assume you want to meet the goal value by modifying the values of specific products or business units during a particular time of the year. In such cases, you can choose which input rows and columns should be changed to achieve the desired result.
In the example below, we set the same desired revenue of 400 million for the Canada region. However, this time we intend to achieve this goal by increasing the sales of only the following products - Amarilla, Carretera, and Montana. We also want to change these values for the third and fourth quarters, excluding the rest.
This can be done by selecting the above-specified rows and columns as below.
In the result below, only the selected rows and columns have been modified to meet the goal.
Note: The goal seek function can also be used on formula cells referencing a column in their formulas - such as COLUMN.PARENT, COLUMN.TOTAL, etc.
In the example below, goal-seek is applied on the target cell which uses a formula with a column referencing function, COLUMN.PARENT.
