Number
Numeric data can be entered and formatted in a variety of ways within Lumel EPM. You can insert a new empty series, copy from another series, perform an advanced copy*, or import from a file.
Let's first look at the steps to insert a numeric data input column.
1. Create a Data Input Number column
To create a Data Input Number Column, select the 'Number' option in the Home Tab.
You will see four different options to create a column:
Insert a new empty series
Copy from another series
Advanced copy
Import from file
Import from Infobridge - This option is enabled only when Infobridge is setup.
Let's go through each of these methods in detail below.
1.1 Insert a new empty Number series
If you select this option, a visual measure gets inserted as shown below. There is a side panel that provides several customization options.
The available properties are:
Insert as: A column or measure can be inserted. If there is a column hierarchy, the input column is inserted for each category. In case of no column hierarchy, a single column is inserted.
Input type: The type of data input column; can be changed from the initial selection before creating the column by clicking on 'Create'.
Row aggregation type: By default, totals and subtotals are defined as the sum of child rows, as shown here. There are several other options, such as average, minimum, maximum, etc., that are explained in detail here.
Distribute parent value to children: The values entered at a parent level are distributed to the child rows when enabled. This is very useful when creating budgets or forecasts.
Minimum Value: You can optionally specify the minimum threshold value for the leaf-level cells, which can be a static number or a value from the selected measure. When set, Lumel EPM ensures that both user input and the allocations to these cells are always more than this value.
Maximum Value: You can optionally specify the maximum threshold value for the leaf-level cells, which can be a static number or a value from the selected measure. When set, Lumel EPM ensures that both user input and the allocations to these cells are always less than this value.
Default Value: In cells where the user has not entered any value, instead of displaying empty cells, you can choose to display a default value. The default value can be selected from any native or formula measure, as explained here. If the underlying native or formula measure is updated, the default value will reflect the latest value.
Allow input: By default, inputs are enabled in both read and edit modes. But, based on your requirement, you can allow inputs only in edit mode or based on a formula.
Description: Option to add a note for reference.
To insert a Data Input number measure, change the title and go with the default for the other properties. Click 'Create'.
The measure gets inserted.
To learn to enter a value in an empty numeric column in the Enter a value section.
ii) Copy from another series
If you select this option, you will see a sub-option with all the measures/forecasts present in the report, even hidden measures. You can select any series to create a numeric column with all the data copied from the chosen series. The values from the measure chosen as the source will serve as initial values only. If there are changes to the source measure, these changes will not be reflected in the data input field.
A visual measure gets inserted as shown below. There is a side panel that provides several customization options. Change the title and go with the default for the other properties. Click 'Create'.
The newly created numeric measure will be pre-populated with the data of the chosen series.
In the below image, a numeric measure is created by copying the data from '2023 Actuals' and the created column is pre-populated with the copied data.
iii) Advanced Copy
When you select this option, a pop-up window opens where you can create measures for multiple time frames and populate them with various series and configurations.
You can also choose this option to fill in the blank forecast measures if you have already inserted them in your report.
The image below demonstrates the possible configurations (copy methods) you can set for each time frame of the measure. Let us look at the options available in detail.
Column: Define the specific period of the column here. You will be provided with the +Add new option beside the Column Selection if the end period chosen is not the last one in the report. You can create multiple columns by selecting different end periods for each column.
Source Series: This dropdown lists the available native /data input /forecast series from which you can choose the required one to be copied. Choose 'Blank' to leave the measures empty.
Copy Method: Here, you can select the copy method based on which the column is populated.
Period Range: Copies the data from a range of periods to the column, period-wise.
Single Period: A single period's data is copied to all the periods of the column.
Average of Period Range: The average of the data range is calculated and spread to the column periods.
Column Selection: This option lets you select a single period or a range of periods from the source series, based on the copy method chosen.
After setting up all the details, click Create.
Let us consider the example below, where we are creating a new measure called Advanced Copy.
Columns are split into 3 timeframes. Advanced Copy measures Q1 and Q2 copies the Column values of Gross Margin's Q1 and Q2, respectively, using the Period Range Copy Method.
Advanced Copy's Q3 Column copies the value of Gross Margin's Q3 using the Single-period Copy method.
Advanced Copy's Q4 Column copies the average value of Gross Margin's Q1, Q2, Q3, and Q4 quarters using the Average of Period Range Copy Method.
The inserted Measures are shown below.
iv) Import from file
You can also upload the data for the input columns by importing it through a .csv file. Choose Import from file.
Download the template, enter your data, and save the file. After entering and saving the data, click Browse and upload the saved file.
Click Preview, to preview the data input column, and click Create.
The measures are inserted as shown below.
2. Enter a value
To enter a value, double-click a cell and type in the formula bar that opens on the top.
You can also enter values directly on the cell as shown below.
The entered value is captured and it is also automatically rolled up to the parent as well as distributed to the child nodes if applicable.
3. Properties
You can modify the properties of a previously created data input column in the side panel. To open it, click on Manage Measures and the 'Pencil' icon as highlighted.
The side panel opens. The input type is greyed out. This is because once a data input column is created, you cannot change its type. You can make any necessary changes and click Update.
We have already discussed an overview of the properties here. Here we will discuss a few properties in detail.
i) Row aggregation type
Row aggregation type defines the aggregation method to be applied for the total and sub-total rows. By default, the subtotal and total rows are calculated as the sum of the child rows, as can be seen in the below image.
To change the aggregation method, click the Row aggregation type dropdown in the side panel. Choose one from the types available and click Update.
ii) Distribute parent value to children
When a value is entered in a subtotal or total row, the value can be distributed to the child rows.
In the image below, the 23,000 entered in the subtotal has been distributed equally to the subcategories.
There are several other ways to distribute values which are covered in Budgeting & allocations.
This distribution can be disabled if not required for certain scenarios. Uncheck the 'Distribute parent value to children' checkbox.
iii) Min and max range
When you need to define a range for the data input values, you can set the Minimum and Maximum value fields
In the dropdown, you can see the two ways of defining ranges 'Static' and 'Measures'.
Let's define a Static minimum value and maximum value using a measure. Select 'Measure' and 'COGS' from the dropdown list. Click 'Update'.
Now you can enter values within the defined range. When a value beyond the defined range is entered then an Error message is displayed.
iv) Default Value
When you create an empty series or column, the user has to enter the values manually. Instead of having those cells blank, you can display a default value.
The default value can be set as a Static value or selected from any native or formula measure. The default value will reflect the latest value of the native or formula measure.
The image below shows the inserted new measure whose default value is set as Gross Margin.
When you add new categories to your dataset, Lumel EPM automatically fills the data in the measure based on the default value configured while creating the measure.
4. Copy as data input
There might be cases where you want to create a budget for the current year based on the prior year's budget and then make finer adjustments. In such cases, Lumel EPM provides the 'Copy as data input' option.
Click on the column gripper whose measure you want to use as the base. Select 'Copy as data input' from the 'Insert' option. The values from the base measure will serve as initial values. The changes in the base measure will not be reflected in the data input field.
A measure gets created with the same values. You can rename it and customize other properties such as row aggregation type or allow input using the side panel and click Create.
In the next section, we'll be looking at dropdown input columns.
Last updated
Was this helpful?