What-if analysis & simulations
Business users regularly encounter a lot of what-if questions while making decisions. This requires simulating the situations by adjusting the data on the fly. Most business intelligence and planning tools can't do this, so users often have to export their report data to Excel to run simulations or create models for offline analysis.
With Lumel EPM, you can directly simulate the required measures on the planning sheet in real time, eliminating the need for cumbersome steps or workarounds.
Key features
To simulate, you can edit the value directly on the Excel-like interface or use an intuitive slider.
Simulate multiple cells at one go, directly or using the Bulk Edit option.
Lock a few cells to retain key values during simulation
Trigger specific actions based on simulation values using the On Change formula.
Quickly calculate the differences between the simulated and original values.
Various customization options are available, such as modifying the simulation range, specifying conditions on when simulation is allowed to a user, configuring the variance formatting style, etc.
1. Edit a cell directly
In this method, you can directly overwrite the existing data in your report and simulate the outcome. In most BI & analytics tools, there is no standard option to persist or write back the modified data.
With Lumel EPM, you have the option to not only edit data directly but also write back the changes to a database or export the data to Excel/PDF files.
To simulate outcomes by editing data, you select a specific cell and start typing in a new value. Similar to Excel, a cell editor appears directly above the value.
Type in the new value and press enter to update the values and table totals automatically. There is also a pencil icon that appears next to the value, indicating that the cell has been edited.
You can see how the values in the totals, subtotals, and other dependent cells change.
An alternate method to achieve the same outcome is to double-click a specific cell. This opens a formula bar at the top where you can make similar adjustments.
You can also use expressions with scaled values (e.g., 11250 - 2.5k) and percentages (e.g., 11250 + 10%). You can use this in both the in-cell editor and the formula bar.
Note: When there is a grand total involved, ensure that the aggregations are properly configured to achieve the desired outcome.
2. Use a simulation slider
Another method to create projections in Lumel EPM is to use its intuitive simulation slider. Compared to the previous method, this approach has the advantage of automatically tracking variances for each record (simulated measure vs. original measure).
Simulations can be performed by inserting a new simulation column and then moving the simulation slider to simulate the desired what-if scenarios. It has two steps:
i) Inserting a simulation column
To use this feature, select the field you want to simulate and click on the Plan > Simulate. This creates a simulation output field based on the input field (2025 Revenue, in this case).
A side panel opens below, allowing you to customize the options for the scenario measure:
a) Title: Provide an appropriate title to the newly created simulation measure, or use the default title.
b) Insert as: Select Visual Measure or Visual Column. Choose to insert it as visual measures for each column level or as a single visual column.
c) Simulation based on: Select the measure for which the simulation measure should be created. (Select the measure to apply the simulations to).
d) Variance formatting style: This option allows you to specify the formatting style to show whether an increase is desirable or undesirable using green and red colors. When you select 'Positive,' you will see the simulated increases in green and decreases in red. E.g., an increase is favorable for revenue (use 'Positive') but unfavorable for cost-based measures such as COGS and expenses (use 'Negative').
e) Show slider: This option allows you to enable or disable the slider. If the slider is disabled, simulation can be performed only by cell-editing.
f) Value range: By default, each cell can be simulated from -100% to +100% of its value. You can change this range here.
g) Simulation Value: Select whether you want to display the simulation value to the left or right of the original value.
h) On Change Formula: You can configure a formula here that triggers specific actions based on the simulation values. Refer to this section to learn more.
i) Allow Input: Choose whether users can enter data into the simulation column in both read and edit modes, or only in the edit mode. You can also enter a formula that returns a Boolean output (TRUE/FALSE), allowing users to enter data only if certain conditions are met (when the formula returns TRUE).
j) Description: Provide an optional brief description of the simulation measure.
ii) Simulation slider
Once the field has been created, you can click on any cell in this new simulation-enabled field. This will display a small slider icon next to it. Hovering over this icon reveals a slider. Clicking and dragging the slider changes the value in the cell. It also shows a percentage change as you keep moving the slider to the right or to the left. The values and totals are updated once you release the slider.
You can also click the pencil icon beside the slider and enter the simulation % directly.
In the example below, we simulated an increase in the revenue for the Beer category by 5% while decreasing the revenue for the Hard Seltzer category by 3%, yielding a 4% overall revenue increase for the period Q1.
When a parent cell is simulated, the change is distributed to the child levels by the same percentage. Similarly, when a child cell is simulated, the total and grand total cell values are aggregated.
3. Simulating multiple cells at once
You can simulate multiple cells simultaneously.
Use Shift + click (or) Ctrl + click to select cells and use the slider on any one cell to simulate.
Use Shift + Down (or) Shift + click on the last cell in the range to select a range of cells and click on the slider on any one cell to simulate.
You can also use the bulk edit feature to simulate a large number of cells at once.
Hover over the slider to access the following options: Lock cell, Edit simulation, and Delete simulation. These are explained below in detail.
4. Edit Simulation
The applied simulation percentage can be modified by clicking the pencil icon near the slider.
In the dialog box that opens, type the desired percentage and click Apply.
The value is updated based on the entered percentage.
If the simulation slider is disabled (as shown in the side panel inlay), you can double-click on the cell to edit the simulation, as shown below.
Multiple cells can be edited at the same time by using Shift+Click or Ctrl+Click as explained here.
5. Lock Cells during Simulation
Specific cells can be locked from simulations. Locked cells are not affected when their parent values are updated.
To lock a specific cell, hover over the slider and then click on the 'Lock' icon.
The cell is greyed out to indicate that it is locked from simulations.
In the example below, you can see that running simulations does not affect locked cells.
A lock icon appears when you select a locked cell. Click on it to unlock the cell.
Multiple cells can be locked at the same time by using Shift+Click or Ctrl+Click as explained here.
6. Delete Simulation
Simulations can be cleared by clicking the 'Delete' icon beside the slider.
7. Bulk editing a simulation measure
Bulk edit feature is available for simulation measures, enabling you to simulate multiple row or column categories simultaneously.
Click Bulk Edit from the Plan toolbar.
In the pop-up,
Measure: Select the simulation measure to be edited in bulk.
Select Filter: Click on the Add Filter option to add criteria and filter specific categories, brands, etc. You can filter both row and column dimensions. As you select, they are filtered in the report.
Apply to Row and Column Levels: Here you can choose the required row and column hierarchical level to apply the simulation.
Simulation: Use the slider or enter the simulation percentage directly. Click Apply.
The results are shown below.
8. Variance (Simulation vs. Original)
After simulating the values in a measure, you would also need to compare the original and simulated values and determine the variance. Use Formula to quickly insert a formula measure and calculate the variance for the simulated fields.
Now, let’s say you want to explore multiple scenarios, each with its own set of simulated values, and compare them with the original data to understand the differences or variance between them. You can use the Scenarios feature to do that.
While Simulations let you explore countless what-if outcomes instantly, Scenarios allow you to organize and save a group of simulations across multiple data series. They help you capture different possibilities, compare them side by side, and analyze the results in depth.
In the next section, we’ll look at how to create scenarios and work with them effectively.
Last updated