3 - Optimize input values

The Optimizer feature is an automated, goal-seeking tool designed to eliminate manual trial-and-error when adjusting budget and planning numbers.

When you set a target for a calculated result (such as Gross Profit or Net Margin), the Optimizer automatically back-calculates and adjusts your editable data input measures (like Sales Growth % or COGS reduction) to reach that exact financial goal.

In this tutorial, you build a Gross Profit sheet, create a formula measure, and run the Optimizer to find the combination of revenue growth and cost reduction that achieves a $12.5M gross profit target.

Set up the Gross Profit sheet#

In this section, you create a Gross Profit sheet that pulls in the sales plan and COGS from the semantic model. This sheet is the starting point for running the Optimizer.

1. In the Home ribbon, select New Planning Sheet. Enter Gross Profit and select Create.
2. Configure the field assignments:

3. Double-click the Sum of 2026 Sales Plan label in the Values field and rename it to 2026 Sales Plan.

Create editable input columns#

In this section, you create editable copies of the Sales Plan and COGS columns. The Optimizer requires editable input columns, since the original semantic model measures can't be used as variables.

1. In the Planning ribbon, select Number > Copy from another series > 2026 Sales Plan. Enter Sales Plan as the title and select Create.
2. In the Planning ribbon, select Number > Copy from another series > 2025 COGS. Enter COGS as the title and select Create.
3. In the Planning ribbon, select Show Columns and hide the original 2026 Sales Plan and 2025 COGS columns. The editable Sales Plan and COGS input columns remain visible.

Calculate gross profit#

In this section, you add a formula column that calculates Gross Profit from the Sales Plan and COGS input columns. This calculated value becomes the base on which the optimizer is applied.

1. In the Planning ribbon, select Formula and configure it as follows, then select Create:

2. Gross Profit appears in the grid, calculated from the two input columns. Collapse the row hierarchy to the category level. In the Planning ribbon, select Totals and enable Column Grand Total on the left.

Run and apply the Optimizer#

In this section, you run the Optimizer to find the combination of Sales Plan and COGS values that achieves the $12.5M Gross Profit target. You then apply the optimized values to the sheet.

1. Select the Gross Profit grand total cell. In the Planning ribbon, Select Optimize.
2. In Optimizer—Objectives and Variables, configure as follows and select Next:

3. On the Add Constraints page, select Run without adding constraints.
4. On the Output screen, confirm Target Value shows 12.5M and Achieved shows 12.5M with a green check mark. Under Variables, observe that the optimizer has calculated the optimal combination of Sales Plan and COGS to reach the gross profit target.

5. Select Apply. The optimized values are written to the sheet.

6. In the Planning ribbon, select Show Columns and enable 2026 Sales Plan and 2025 COGS. The original and optimized columns appear side by side:

   • Sales Plan: $26.38m vs original $26.1m—an increase of $0.28m
   • COGS: $13.88m vs original $14.17m—a reduction of $0.29m

   Together, these two adjustments deliver the $12.5M gross profit target.