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.

Prerequisite

To use Goal Seek, row and column aggregation for native and data input measures (inputs) must be set to 'Sum'.

Configuring Goal Seek

Let us take the following example, where the gross profit for each region is calculated by subtracting COGS from revenue. 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 gross profit for the APAC region is 2139.03m. Let us assume we are seeking a goal of 2141m as the desired profit.

Now, for the desired profit to be achieved through Goal Seek, we will choose to change one of its drivers - the COGS. Note that you can change only one driver at a time. Therefore, let us keep the Revenue 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 COGS.

1. Update the formula accordingly by including this data input column in place of the native column ('COGS copy' instead of 'COGS' in the formula).

1. Click on the required cell (APAC>Grand Total>Gross Profit) and select Plan > Goal Seek.

1. In the pop-up window:

• Enter the desired goal value of 2141 m.

• Verify the target cell and its underlying formula.

• Choose which of its drivers needs to be changed from the drop-down. We will choose 'COGS copy' since we want to adjust the cost of goods sold to reach the desired profit.

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

1. Click Apply to make the changes.

A new set of values is updated for the COGS to meet the specified profit for APAC. Both COGS and gross profit are automatically aggregated to the top as well as distributed to the child rows based on their updated values.

If you expand the column hierarchy, you can see that Lumel EPM also distributes the new values to the periods accordingly (here, it is Q1, Q2, Q3, and Q4).

Note: Goal Seek can also be applied to measures in rows. In the example below, you can observe the updated grand total for Gross Profit and COGS when a goal of 2143 m is set for APAC's Gross Profit.

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 gross profit of 2141 million for the APAC region. However, this time we intend to achieve this goal by adjusting the COGS only for the following countries: China and Vietnam. We also want to change these values for the first and second 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.

Referencing forecast measures

We demonstrated above how to adjust a data input field to achieve the desired result. You can also adjust open forecasts to meet a defined target value.

The example below contains a forecasted COGS field as well as a projected data input COGS field. The formula uses the projected COGS for closed periods and the forecasted COGS for open periods. When you set a 'Gross Profit' goal, Lumel EPM adjusts the forecast measures to meet that goal.

Goal seek for formula rows

In addition to target cells in formula columns, goal seek can also be used to target cells in formula 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 (data input or forecast) to achieve the desired result based on the row-level formula.

In this sheet, we have a formula row (Subregion with 10% growth) that uses a data input row (New Subregion) in its formula.

There is also a forecast measure to predict gross profit. If the 10% growth value is altered from 458.28 m to 461 m, let's use Goal Seek to determine the expected gross profit for Q3.

Notice how the profit forecast cell has been changed to 419.09 m from 416.62 m based on the goal set for the calculated row.

Key Considerations

• Single input at a time: Goal Seek can only adjust one input measure at a time.

• Clear Relationship: The relationship between the input and the target must be clear and well-defined for the iterative process to work.

• No circular references in the formula: Goal Seek does not work on target cells that contain formulas with circular references.

• Editable input cells: While the target cell must be a formula, the input cells whose values must be changed to meet the goal should be editable (for example, data input columns and open-period forecast columns).

• No Formula Errors: The formula should avoid errors, such as dividing by zero, as this will prevent Goal Seek from finding a solution.

• Convergence: If the relationship is highly non-linear, or if there are many possible solutions, Goal Seek may not be able to determine a solution. This will be flagged on the pop-up window when goal-seeking cannot be achieved.