Use optimize to find the driver inputs needed to reach a target KPI, like revenue, margin, or cash, and to make what-if analysis easier by recalculating the required input changes. It supports multivariate scenarios by adjusting multiple data input measures to meet a target value for a selected result measure.
Set a target value, then run optimize to calculate the input values needed to reach that target. Plan recalculates the inputs and applies the updated values to achieve the specified outcome.
Set up the planning sheet with at least one data input measure that can be used as an input variable within Optimize. In the following example, Units Sold (Projection), COGS (Projection), and Revenue (Projection) are inserted as the data input measures.
Learn more about data input measure here.
Add at least one formula measure to the planning sheet. Optimize requires a formula measure to run. The formula measure acts as the target or 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 or driver that Optimize changes to reach the target.
Insert a formula measure by selecting Formula under the Insert Column dropdown of the Planning ribbon.
Enter a formula which depends on all three data input measures.
Set Row aggregation and Column aggregation as Formula and select Create.
The formula measure has been inserted, and this will be used as the objective for optimize.
Select the target cell in the formula measure. Then go to Planning>Optimize. In this example, select the target cell from the Profit per unit measure.
To achieve a specific target-based optimization, set Objective to Target and enter the target value. In this example, target value is set as 65.
Select Maximize or Minimize to use direction based optimization to achieve a maximum or minimum value.
Select the data input measures to optimize from Variables to Update to meet the target. In this case, the COGS(Projection) is adjusted to meet the target Profit per unit and select Next.
Select Add Constraint to define the minimum and maximum limits for the data input measure.
Choose the variable to which the constraint applies and select "Set Type" as Range or Equals. For example, COGS(Projection) is selected as Variable and Range is selected as Set Type.
Now enter the Min and Max value. Select Apply and then Run.
This step is optional. Select Run to skip defining constraints.
Review the adjusted value and select Apply to update the data input measure - in this case, COGS(Projection).
The Profit per unit is increased to the target value of 65 by changing the COGS(Projection) to 560k.
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 per unit measure uses multiple data input measures in the formula, as shown:
To optimize for multiple data input measures, select the measures from Variables to Update.
To constrain optimization results, specify the allowed adjustment range and limits for the data input measures. Each data input measure can have one constraint. In this scenario, constraints are specified for COGS(Projection) and Units Sold(Projection).
This step is optional.
The Units Sold (Projection) is adjusted to accommodate the limits on the other data input measures.
If optimize does not reach the target value, adjust Strategy, Tolerance, and Number of iterations, then re-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
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 updated value to the underlying editable child cells. In below example, 2 child cells of Revenue(Projection) are in locked state.
Optimize increased Revenue(Projection) at the parent level and distributed the value proportionally across the child rows to achieve the target Profit per unit.
NOTE : Values of locked child cells are not changed.
Optimize aligns forecast measures with business targets by calculating the adjustments needed to achieve the target.
Learn more about forecast measure here.
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.
Enterprise planning, Integrated with PowerTable and Intelligence, native to Microsoft Fabric. Co Engineered with Lumel.
