Goal Seek
In the last section, we looked into what-if analysis, simulations, and scenarios, 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.
Goal Seek can be used on a formula cell, either in a formula row or a formula column. You can also specify which rows and columns need to be changed to achieve the target value.
Prerequisites
Goal seek can be applied only to target cells containing a mathematical formula or aggregations like sum, product, etc.
To use Goal Seek, row and column aggregation for native and data input measures (inputs for the goal) must be set to 'Sum'.
This section discusses other important considerations when using goal seek.
Example
Let us take the following example, where we have the revenue and COGS details for all beverages. The gross profit for each beverage category can be calculated by subtracting COGS from revenue. We will insert a formula measure as shown below.
The formula measure is inserted, and the grand total is also calculated.
In the image below, the gross profit for the Beer category is 15,555.28 million. Let us assume that the target gross profit for this category is 18,000 million.
The relationship here is, Gross Profit = Revenue - COGS
Given a known COGS, we will use the Goal Seek function to calculate the required revenue to achieve a gross profit of 18,000 million in the beer category.
Note that in Goal Seek, you can change only one driver at a time. So, let's keep the COGS constant and work with changing one of its drivers—revenue.
Configuring Goal Seek
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 revenue.
Click on Home > Number (Insert Column section) > Copy from another series > Revenue.
A side panel opens as shown below. After naming it, click on Create.
Let's update the formula accordingly by including this data input column instead of the native column (replace the 'Revenue' measure with the 'Revenue copy' measure in the formula).
Click on the required target cell (Beer > Grand Total > Gross Profit) and select Plan > Goal Seek.
In the pop-up window:
Enter the desired goal value of 18,000 million.
Verify the target cell and its underlying formula.
Choose which of its drivers needs to be changed from the drop-down. This dropdown displays a list of all available data input columns. We will choose 'Revenue copy' since we want to adjust the revenue to reach the desired profit.
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.
Click Apply to make the changes.
A new set of values is updated for the revenue to meet the specified profit for the beer category. Both 'Revenue' and 'Gross Profit' are automatically aggregated to the top as well as distributed to the child rows based on their updated values.
The new values are also distributed to the appropriate column hierarchical levels (here, Q1, Q2, Q3, and Q4).
Note: Goal Seek can also be applied to measures in rows. The example below shows the revised gross profit and revenue values when the beer category's gross profit target is set to 18,000 m.
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 these scenarios, you have the option to modify specific input rows and columns to achieve the intended outcome.
In the example below, we set the same desired gross profit of 18,000 million for the Beer category. However, this time we intend to achieve this goal by adjusting the revenue only for the following brands: Artisan Ale, Eclipse Light, and Summit Lager. 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
Above, we demonstrated how to modify a data input field to achieve the desired result. You can also adjust forecast drivers to achieve a specific target value.
The example below contains a forecasted revenue measure (Forecast) as well as a projected data input measure (Revenue copy).
The profit is calculated here by using the projected revenue for closed periods and the forecasted revenue for open periods.
Now, when you set a target 'Gross Profit' and choose 'Forecast' in the pop-up as below, the forecast values are adjusted to meet the goal.
Goal seek for formula rows
In addition to the target cell being in formula columns, goal seek can also be used on target cells that are present 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've included a data input row (Heisler) and a formula row (Heisler 10% growth)—which incorporates this input row in its formula.
There is also a forecast measure for estimating gross profit.
Let's set the 10% growth target for Q4 at 8 million instead of 5.29 million. We'll then use Goal Seek to calculate the Heisler brand's actual gross profit to accomplish this.
The values in the gross profit forecast measures are updated accordingly:
Key Considerations for Goal Seek
Single input at a time: Goal Seek can only adjust one input measure at a time.
Clear relationship between the input and the target goal: 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 with default values configured as data input).
No Formula Errors: The formula should not contain errors, such as dividing by zero, as this will prevent Goal Seek from finding a solution.
Convergence: If the relationship is highly nonlinear, or if there are many possible solutions, Goal Seek may not be able to determine a solution. This will be notified on the pop-up window when goal seek cannot be achieved.
Last updated