Optimize overview

Use Optimize to find the input values needed to reach a target KPI, like revenue, margin, or cash, and to make what-if analysis easier by recalculating the required input changes. Optimize supports multivariate goal seek by adjusting multiple data input measures simultaneously to meet a target value for a selected result measure.

The most common use cases for Optimize in planning include:

• Revenue planning: Determine the sales volume, average selling price, or product mix required to achieve a target revenue.
• Profit margin optimization: Calculate the changes in pricing, costs, or sales mix needed to reach a target gross or operating margin.
• Expense budgeting: Identify the reductions or reallocations across multiple expense categories required to meet a budget target.
• Cash flow forecasting: Determine the changes in collections, payments, or capital expenditures needed to achieve a target cash balance.

The Optimize feature adjusts one or more independent input measures within user-defined constraints to maximize, minimize, or achieve a target value for a dependent formula measure. For example, it can vary Purchase Plan and Transport Expense Plan to optimize a calculated Profit measure.

• Independent variables – Select one or more input or forecast measures that the optimizer can adjust, such as Purchase Plan, Revenue Plan, or Transport Expense Plan.
• Dependent measure – Create a formula measure that represents the objective to optimize—for example, Profit = Revenue - (Purchase + Transport Expense).
• Optimization goal – Specify whether to maximize, minimize, or achieve a target value for the dependent measure.
• Constraints – Define upper and lower limits, fixed values, or other restrictions on the independent variables to control how they can be adjusted.
• Optimization result – The optimizer iteratively adjusts the independent variables within the defined constraints to produce the optimal value for the dependent measure.

Prerequisites#

1. Create independent variables

Set up the planning sheet with at least one data input or forecast measure. Optimize adjusts the independent variables to achieve a target value or maximize or minimize the objective value.

In the following example, Revenue PY, Purchase Expense PY, Transport Expense PY, and Advertising Expense PY are the native measures.

Create forecasts for each native measure: Revenue Forecast, Purchase Forecast, Advertising Forecast, and Transport Forecast. For more information, see Create a Forecast.

2. Create the dependent variable

Add at least one formula measure to the planning sheet. The formula measure is the dependent variable. Optimize adjusts one or more independent variables to achieve a target, minimum, or maximum value for a dependent variable.

• To insert a formula measure, in the Planning ribbon, go to Insert Column and select Formula. Enter a formula that uses the data input measures created in the previous step.

• Set Row aggregation and Column aggregation to Formula and select Create.

The following screenshot shows the forecast and calculated measures.

Optimization modes#

Optimize supports two modes:

• Target-based optimization calculates the input values required to achieve a specified target for a result measure.
• Direction-based optimization calculates the input values that maximize or minimize a result measure while honoring the defined constraints.

Both modes support multivariate optimization by adjusting one or more input measures simultaneously.

Configure optimization thresholds#

Constraints are essential in optimization because they ensure the solution is realistic and feasible. Define constraints to ensure the solution satisfies business rules and operational limits, such as budget caps, production capacity, or minimum staffing requirements.

• In manufacturing, to maximize profit, machine hours cannot exceed 500 hours, raw material is limited to 10,000 units, and production quantities cannot be negative.
• In budget planning, to minimize operating costs, department budgets cannot fall below mandatory spending levels; total headcount must remain above a minimum.
• In sales planning, to maximize revenue, sales discounts cannot exceed 20%; inventory availability limits the number of units sold.

Range-based constraint:

Specify a minimum and maximum value for each data input measure to constrain the optimization. During optimization, the recalculated values for each input measure remain within the specified range, ensuring that the resulting solution is both feasible and aligned with business requirements.

Fixed-value constraint

Apply a fixed-value constraint to a data input measure to keep its value unchanged during optimization. The optimizer adjusts the remaining input measures to achieve the specified objective while honoring the fixed constraint.

Suppose Sales Price and Sales Volume are the input measures, and Revenue is the calculated measure. Constrain Sales Price to 0.5m so it remains unchanged during optimization. The optimizer adjusts Sales Volume to achieve the target revenue while keeping the sales price fixed.

Adjust parameters to achieve targets #

If Optimize doesn't 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 can take longer to converge. Higher values use larger steps and can 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 is. 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 performs these operations:

  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.

  

Related content#

Optimizer solver in Fabric planning