Optimize input values to meet a target

Optimize supports multivariate scenarios by adjusting multiple data input measures to meet a target value for a selected result measure. Use Optimize to determine the driver inputs required to reach a target KPI, such as revenue, margin, or cash, and to streamline what-if analysis by recalculating the required input changes.

Set a target value, then run Optimize to calculate the input values required to reach the target. Plan recalculates the inputs and applies the updated values to achieve the specified outcome.

Prerequisites

• Add at least one formula measure to the planning sheet. Optimize requires a formula measure to run. The formula measure acts as the target/output.

• Ensure the formula uses a data input or forecast measure. Optimize adjusts the data input values to meet the target. The data input measure acts as the input/driver that Optimize changes to reach the target.

• Set Row aggregation and Column aggregation to Formula for the formula measure.

Run Optimize on calculated fields

1. Select the target cell in the formula measure field, then select Optimize. In this example, the formula measure used is Profit per unit.

2. Choose an optimization goal: Maximize, Minimize, or Target. Optimize adjusts the data input measure values to meet the selected goal. To achieve a specific target, set Objective to Target and enter the target value.

3. Set Variables to Update to the data input measure to adjust to meet the target. In this case, the COGS will be adjusted to meet the target Profit per unit.

1. Select Add Constraint and define the minimum and maximum limits for the data input measure. For example, the cost of goods can only be adjusted based on achievable reductions. Select Apply, then Run.

This step is optional. Select Run to skip defining constraints.

1. Validate the adjusted value and select Apply to update the data input measure - in this case, COGS.

The Profit per unit is increased to the target value of 17.32 by changing the COGS to 1.0163m.

Configure multivariate optimization

Optimize works with multi-variate scenarios where a formula uses multiple data input measures. Specify ranges and limits for each data input measure; the other measures are adjusted to meet the target while respecting the specified constraints.

The Profit to Price Ratio measure uses multiple data input measures in the formula, as shown:

1. To optimize for multiple data input measures, select the measures from Variables to Update.

1. To constrain optimization results, specify the allowed adjustment range and limits for the data input measures. Each data input measure can have one constraint.

This step is optional.

1. In this scenario, constraints are specified for COGS and Units Sold. The Selling Price is adjusted to accommodate the limits on the other data input measures.

Adjust parameters to achieve targets

If Optimize does not reach the target value, adjust Strategy, Tolerance, and Number of iterations, then run Optimize again.

• Strategy controls the size of the adjustments made to the input value while trying to achieve the target. Lower values use smaller steps and may take longer to converge. Higher values use larger steps and may converge faster, but can overshoot.

• Tolerance defines the allowed error between the achieved value and the target value and determines how precise the Optimize result will be. For example, the target Profit per Unit = 0.50. If the tolerance = 0.01, Optimize stops when the achieved value is between 0.49 and 0.51.

• Number of iterations sets the maximum number of times to repeat the optimization loop. In each iteration, Optimize

  1. Tries an input value.

  2. Calculates the result.

  3. Compares the result to the target value.

  4. Adjusts the input value based on the comparison.

Run Optimize on parent cells

To meet a target at an aggregated level, apply Optimize on parent (total) cells. When Optimize runs on a parent cell, it recalculates the required change and distributes the update to the underlying editable child cells.

In this example, Optimize reduced COGS at the parent level and distributed the reduction proportionally across the child rows to achieve the target profit.

Using Optimize on forecast measures

Optimize helps align forecast measures to business targets by calculating the required adjustments to achieve the target.

• Optimize updates forecast values only for open periods. Closed periods are locked by default.

• To run Optimize on forecasts, configure the open period forecast measure as a data input measure so the values are editable.

In this example, the Implied Price is calculated using the formula shown below:

The steps to run Optimize on forecast measures are the same as the steps used for data input measures described earlier.

To reach the target implied price of 86.42, Optimize updates the revenue forecast at the parent level and distributes the change proportionally across the child rows.