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

Prerequsite

Row and column aggregation for native measures must be set to sum in order to use Goal Seek.

Setting row and column aggregation

Configuring Goal Seek

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

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

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

Click 'Goal Seek'

The goal seek option gets enabled only when you click on a cell containing a formula or aggregation like sum, product, etc.

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

Enter the goal value for revenue

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

Select the driver to be changed

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

2. Click Run and then Apply to make the changes.

Apply Goal Seek

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

Note: Goal Seek can also be applied to measures in rows.

Goal Seek applied on measures in rows

Applying Goal Seek Results to Specific Rows and Columns

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.

Applying Goal Seek to selected rows and columns

In the result below, only the selected rows and columns have been modified to meet the goal.

Results of Goal Seek

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.

Goal Seek is applied on a target cell referencing a column

Referencing forecasts

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.

Forecast measures in goal seek

Goal seek for calculated rows

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.

Calculated total row

Let's use Goal Seek to update the total row value from -731.83 to 845.67.

Updating the total row using Goal Seek

Notice how the profit forecast cell has been changed to 6.01k based on the goal set for the calculated row.

Goal seek applied on the calculated row