8.1 - Target-based optimization

The Optimize feature enables target and direction-driven planning by automatically adjusting input values used in calculations. Instead of manually testing different combinations, optimization simulates input values to achieve a specified target outcome—such as revenue, profit, or cost. By defining the target and selecting the input drivers, Optimize computes the optimal values that satisfy the formula constraints. This approach accelerates budgeting and forecasting, improves accuracy, and supports data-driven decision-making across planning scenarios.

In this tutorial, you learn to use target based optimization, define constraints, and apply optimized values to your planning sheet.

Prerequisites

1. A planning sheet is created with measures, row, and column dimensions assigned.

   

   In this example, PY Profit is the difference between PY Revenue, PY Purchase, and PY Transportation. These measures are reported prior year actuals from the semantic model.

   
2. Create data input measures (Independent variables). Native measures from the semantic model can't be edited. Before running optimization, create copies of the native measures that require optimization.

   In the Planning ribbon, go to Insert Column > Number. Select Copy from another series. Select the native measure to copy, in this case, PY Revenue.

   

   Enter the Revenue Plan as the measure name. Set Insert as to Visual Measure. Ensure Input type is Number, row and column aggregation types are Sum, and Distribute parent value to children is selected.

   

   Select Create. In this example, the same steps are repeated to create Purchase Plan from PY Purchase, and Transportation Plan from PY Transportation.

3. Create a formula measure (Dependent variable). The formula measure is the dependent variable. The independent variables are optimized to achieve the target value assigned to the dependent variable.

   In the Planning ribbon, go to Insert Column > Formula.

   

   Enter the Profit Plan as the measure name and enter the formula. Set Insert as to Visual Measure. Ensure the input type is Number. Row and column aggregation types must be set to Formula.

   

   Select Create.

4. Manage Column Display

   To view all the measures in the planning sheet, in the Planning ribbon, go to Show Columns and select the settings icon.

   

   To focus on the measures required for optimize, deselect the native measures from the semantic model. In this example, the visual measures are Profit Plan, Revenue Plan, Purchase Plan, and Transportation Plan. Only the selected measures are displayed in the planning sheet.

   
5. Configure Aggregation Settings. Select the aggregation icon(Σ). Set Row Grand Total and Row Subtotal to Top.

   

Configure optimizer (Target-based)

In this example, the total profit plan is 50.61 million. The steps outline how to achieve a target of 55 million in the profit plan.

1. Select the cell that shows the total Profit Plan.

2. Select Optimize.

   
3. Set Objective to Target.

   
4. Set the Target Value to 55,000,000 (or 55m).

5. Select the independent variables, that is, the data input measures (Revenue Plan, Purchase Plan, and Transportation Plan) in the Variables to Update section.

   

Add constraints

In real-world scenarios, the independent variables often have constraints. This step isn't required when data input measures can be optimized without restrictions.

1. Select +Add Constraints to configure constraints.

   
2. Set up thresholds for each data input measure. Select Revenue Plan as Apply to Variable. Select Range for Set Type. Configure the minimum and maximum values and select Apply.

   
3. Select +Add to configure constraints for the other independent variables and repeat the same steps.

   
4. Select Run to execute the optimizer.

Run optimizer and apply values

The optimizer auto-simulates the values of the independent variables within the specified thresholds to find the optimum value closest to the target value.

To achieve the target value of 55m in Profit Plan (the dependent variable), the optimizer auto-simulated values for Revenue Plan, Purchase Plan, and Transportation Plan (the independent variables).

Review the simulated values and select Apply to replace the values of the data input measures with the optimized autosimulated values.

The simulated values replace the original data input values in the planning sheet.