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 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.
Row and column aggregation for native measures must be set to sum in order to use Goal Seek.
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.
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.
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.
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.
Inforiver can adjust open forecasts to match a defined target value. Let’s consider a report that captures the projected revenue based on the quantity and sales. The report contains a forecasted quantity field as well as a projected quantity data input field. The formula uses the projected quantity for closed periods and the forecast quantity for open periods. When you set a goal for the Revenue measure, Inforiver will adjust the forecast quantity to align with the goal.
Suppose you have a calculated row that refers to a data input or forecast row. In that case, you can use Goal Seek to automatically adjust the input row to achieve the desired result based on the row-level formula.
In this report, we have a Total formula row based on a native row and a forecast row.
Let's use Goal Seek to update the total row value from -731.83 to 845.67.
Notice how the profit forecast cell has been changed to 6.01k based on the goal set for the calculated row.
Let us take the following example, where the product revenue for each region is calculated by multiplying the sale price by the number of units sold. This is accomplished by as shown below.
Input values in the native columns cannot be changed through Goal Seek. So we will create a to change it. In this example, a data input column has been created for the Units Sold driver.
You can either apply the changes to all input rows and columns or to meet the desired goal value. Let us first apply changes to all the input rows and columns.
In the example below, goal-seek is applied on the target cell which uses a formula with a column referencing function, .
