What-if analysis & simulations
Everyday business decision-making involves stakeholders asking a lot of ‘what-if’ questions. However, typical business intelligence and reporting tools fail to provide this very capability that decision-makers and analysts need most. As a result, users end up exporting report data to Excel to create their own models for offline analyses.
Lumel EPM provides two ways to model & simulate outcomes for your ‘what-if’ questions. Let us look at them one by one.
1. Edit a cell directly
This method involves overwriting existing data in your report directly. However, business intelligence & analytics tools typically do not provide this capability, as there is no standard option to persist or write back the modified data.
With Lumel EPM, you receive the option to not only edit data directly but also to write back to a database or export the data to Excel/PDF files.
To simulate outcomes in Lumel EPM 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 an edit 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.
Lumel EPM also allows you to use expressions with scaled values (e.g., 11250 + 0.5k) and percentages (e.g., 11250 + 10%). You can use this in both the in-cell editor and the formula bar.
2. Use a simulation slider
Another method to create projections in Lumel EPM is to use its intuitive simulation capability. Compared to the previous method, this approach has the benefit of automatically tracking variances for each record.
Simulations can be performed by inserting a new simulation column and then moving the simulation slider to simulate the desired what-if scenarios.
i) Inserting a 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 or a visual column.
c) Simulation based on: Select the measure based on which the simulation series is created. (Select the measure to apply the simulations to).
d) Variance formatting style: With this, you can define the formatting style that indicates whether an increase is good or undesirable. For example, a simulated increase is good for revenue measures but undesirable for cost-based measures like COGS and expenses.
e) Show slider: This option allows you to enable or disable the slider. If the slider is disabled, simulation can still be performed 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: Configure a formula to trigger 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 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 and 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.
In the example below, we increased revenue for the EU region by 3%, yielding a 1% overall revenue increase. When a parent cell is simulated, the change is distributed to the child levels by the same percentage.
Multiple cells can be simulated 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 bulk edit to simulate a large number of cells at once.
On the simulation slider, you can see three options - lock, edit and delete. Let's look at them one by one.
a) Edit - A simulation percentage can be defined by clicking on the pencil icon.
In the dialog box that opens, type the desired percentage and click Apply.
The value is updated based on the entered percentage.
b) Lock - Specific cells can be locked from simulations. Locked cells are not affected if their parent values are updated. 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.
c) Delete - Simulations can be cleared by clicking on the 'Delete' icon.
If the simulation slider is disabled (as shown in the side panel inlay), you can double-click on the cell to edit directly, as shown below.
3. Bulk editing a simulation measure
Bulk edit feature is also available for simulation measures, enabling you to simulate entire row/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: Use the filter option to filter specific regions or subregions, categories/subcategories, etc. from both row and column dimensions. As you select, they are filtered in the report.
Apply to Row and Column Levels: Choose the row and column levels where the simulation should be applied, as well as the simulation percentage. Click Apply.
The results are shown below.
4. Variance for simulations
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.
Last updated