Goal Seek is a kind of What-if analysis used to specify the target output or goal value to calculate by changing the necessary input value needed to achieve the goal value. This feature is available in both Reporting Matrix and Writeback Matrix and it is very similar to the Goal Seek feature in MS Excel.
Let us understand this feature with an example.
1) Let us say that we have a Calculated Column called Product Revenue which calculates the product revenue per Country by multiplying the Sales Price with the number of units sold per Country:
2) In the below example the Product Revenue for Amarilla product under country France is 64 million:
Let us assume that we are seeking a target revenue of 80 million for Amarilla product under country France
Now, for the target revenue to be achieved through Goal Seek, we need to change one of the drivers of the Product Revenue (values used to calculate Product Revenue). In our case it was Sales price into Units Sold so we will choose Units Sold. Please Note: that you can change only one driver at a time and hence let us keep the Sale Price fixed.
3) Now a very important point when using Goal Seek is that the 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 our case we will create a data input column for Units Sold:
4) Next, we need to update the Product Revenue Formula column with the data input column we had created using Units Sold native column:
5) Now let us click on the required cell in our case it is Amarilla product under country France and select Goal Seek:
5) In the pop-up window, enter the desired goal value of 80m:
6) Now choose which of its drivers (values used to calculate the Product Revenue Column) 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:
7) We 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. In our case we will apply the changes to all the input rows and columns:
8) We can now click Run and Apply to make the changes:
9) A new set of values is updated in Units Sold to meet the specified revenue (80m):
FAQs:
Can Goal Seek work for Native Column values?
Input values in Native columns cannot be changed through Goal Seek. However, we can convert Native columns to Data Input columns and then use that for Goal Seek
Do we have to update the formula column with the updated Data Input Column (using Native column)?
Yes, we need to update Formula column with the updated Data Input Column (using Native column)
Do we need Formula column for Goal Seek?
Yes, without Formula Column we cannot create Goal Seek.
Can we apply Goal Seek to selected rows and columns?
Yes, we can.
