Budgeting & Allocations
The Lumel Planning sheet supports both top-down distribution and bottom-up aggregation while performing simulations, scenario creation, or goal seeking, as discussed in the previous sections. In this section, we will specifically look into the budgeting use cases using the planning sheet.
Organizations use both top-down and bottom-up approaches to budgeting, depending on their needs.
Top-down approach: In this approach, the management sets the overall targets for all departments or units. These target values are then distributed downward to the individual teams (as well as across a timeline) for planning and budgeting.
Bottom-up approach: Here, the individual departments or teams build their budgets based on their specific needs, forecasts, and resource plans. These detailed inputs are then consolidated upward to form the organization-wide budget.
Lumel Planning supports both approaches—allowing you to start with top-level budget allocations and distribute them across departments and also aggregate detailed plans from multiple teams into a consolidated company budget.
This involves two steps:
Step 1: Create a data input series for your new plan or budget
You can create an empty data series from scratch (zero-based budgeting), or
Copy an existing data series and then modify it wherever required.
Step 2: Allocate totals to rows and columns
Enter the totals at the top level, and then distribute them either by using weights from other measures, equally, or by applying an increasing or decreasing trend.
Other features that you can use:
Lock values: You can lock one or more cells, a row, or child cells in a parent category if you want them to remain unchanged during a distribution or copy operation.
Copy values: You can use this feature to quickly copy a cell value to all the cells in the same category or until the end. You can also apply an increasing or decreasing trend while copying.
Below is a detailed demonstration of all the above-mentioned steps:
1. Creating a series
Let's first look at how to create a new series.
1.1. From scratch (zero-based budgeting)
Let’s assume you would like to create a zero-based budget for the year 2026.
This can be achieved by creating an empty data input column, as discussed in this section. The result is shown in the images below.
1.2. Based on an existing series
Let’s take another example where we would like to create a 2026 plan based on the 2025 plan (or the most recent actuals/forecast).
This can be achieved by creating a copy of any existing data series, as described in this section.
The result is shown in the image below.
2. Distribute totals
We will start with the zero-based budget for 2026 and then enter values.
Enter a value of 3700 million as the Q1 2026 Budget in the formula bar by double-clicking on the cell.
Now, there are three ways to allocate values entered in a total or subtotal—equally, based on weights, or using a trend. These are discussed below.
The three distribution options (equal, by weights, and by trend) are only displayed on a total cell and are not available when you click on a cell at the child level.
Sum, Average (all types) and Weighted Average are the only row aggregation types that allow allocations/distributions from total cells to the child rows. Other aggregation methods do not allow entry in the total cells.
2.1. Equal
After entering the value in the formula bar, press 'Enter.' By default, the values are distributed equally across all the categories and brands. The updated values are highlighted for a while.
Note that the values have been aggregated in the grand total (Grand total > 2026 Budget).
Let's consider another example where we enter the budget first at the grand total level. Click on the highlighted cell and enter 18,000 million.
Once you click 'Enter,' you can see the value distributed equally across categories and brands (row-wise), as well as quarters (column-wise).
2.2. Weight
Instead of an equal distribution, we can also allocate the budget proportionally based on an existing data series. Here, we will use the 2025 Plan series to derive the weights for the new budget.
Click on the context menu appearing on the grand total cell with the value 18,000 million and choose ‘Distribute by weights of 2025 Plan’.
Once this option is selected, the 2026 budget values are completely updated based on the relative contribution of the 2025 Plan series values.
2.3. Trend
In some cases, you may need to allocate the total along with a growth or decline rate. In such cases, you can distribute values to rows and columns based on an increasing or decreasing trend.
To accomplish this, specify the percentage by which each row (or column) value should increase or decrease as the value spreads across rows/columns.
Distribute to rows with trend
Let's consider the example below. We will now distribute the 2026 budget to all of its rows, with a 7% increase month-on-month.
Select the 2026 Budget's total cell and then choose 'Distribute to rows with trend'. Using the slider, set the trend to 7%. You can also directly input the value using the option 'Input custom value' and then click Apply.
The values are distributed across rows, increasing successively by 7% until the end.
Distribute to columns with trend
Let us distribute the 2026 annual budget across each quarter of the year (all columns—Q1, Q2, Q3, and Q4) with a 5% increase applied to each subsequent quarter.
Select the 2026 Budget's Grand Total cell and then choose 'Distribute to columns with trend'. Use the slider to set the trend, or directly enter the value, 5, using the 'Input custom value' option, and then click Apply.
The values are distributed across columns, increasing successively by 5% until the end.
To apply a decreasing trend, drag the slider to the left or enter a negative input value.
3. Lock values
In some cases, you may need to preserve a few cell values during allocation or copying. You can accomplish this by locking these data input cells either at the total/subtotal level (locking all children) or at the cell level. The locked cells are not affected by any of the allocation methods.
3.1. Lock a cell
Let’s consider a business case—there is an overall budget for 2026, which is distributed to the beverage categories based on the 2025 Plan. Certain categories and brands have a fixed budget: for example, annual budgets for Hard Seltzer and Summit Lager are 5000 m and 700 m, respectively.
The overall budget must now be redistributed, while the budgets of the two regions mentioned above should remain unchanged. This can be easily achieved as shown below.
Note that we have inserted a numeric data input column for the 2026 budget, entered the budget of 16,000 m, and distributed it by 2025 Plan.
Click on the Beer > Summit Lager > Grand Total > 2026 Budget cell and update the value as shown below.
Once you press enter, click on the context menu and select Lock this cell.
The cell is greyed out, indicating that it is locked from further adjustments.
Similarly, update the budget in the Hard Seltzer > Grand Total > 2026 Budget cell and then lock the cell.
This value is automatically distributed to its children (all the Hard Seltzer brands) based on previous weights. Also, note that the overall budget has become 16,446.08 m.
Let’s enter 16,000 m again as the overall budget. This budget is now redistributed as shown below without affecting the locked cells.
3.2. Lock multiple cells
To lock multiple cells, Ctrl + click the required cells. Click on the lock icon and select Lock selected cells from the drop-down.
The locked cells are greyed out, indicating that they cannot be edited or updated.
3.3. Lock all children
In some cases, you may want to lock all of the children in a specific cell. For example, all children across rows (subcategories/brands) and columns (quarters/months) may need to be locked. You can accomplish this by selecting this option.
Let's click on a grand total that has child cells across rows and columns. Then, we will select Lock all children from the context menu.
All the child cells are locked, as shown in the image below.
Let's update the overall budget and see the effect on the child cells.
The increase is applied to all others, except the locked category and its brands in all quarters.
You can also lock children at a brand level or for a specific quarter by clicking on the relevant total cell and selecting the Lock all children option.
3.4. Lock row
The 'Lock row' option is available in the Measures In Rows layout. This is used to lock an entire row when measures are displayed as rows.
Click on the row gripper next to the row you want to lock and select Lock Row to lock the entire row or measure.
The entire row is now greyed out, indicating that it is locked and updates to that row are disabled.
3.5. Unlock
a) To unlock a cell, select Unlock this cell from the context menu. The selected cell is unlocked.
b) To unlock all the child cells of a locked cell, select the Unlock all children option.
c) To unlock a row when the 'Measures In Rows' layout is enabled, click on the row gripper and select Unlock Row.
d) To unlock multiple cells, Ctrl + click on the locked cells and click on Unlock selected cells.
4. Copy values
In addition to allocations, you can also replicate a cell's value to other cells. When you click on a child cell, you can see the different copy options available.
Broadly, there are two preset options to update cell values:
You can copy the same cell value across rows or columns (or)
apply a trend across rows or columns
If these options do not satisfy your requirements, you can update the cells manually.
4.1. Copy
You can copy the current cell value and apply it to other cells using the following options:
Copy until the last row in the row category
Copy to all rows in the row category
Copy to all rows
Copy until the last column in the column category
Let's take the example below to demonstrate these options.
a) Copy until last row in the category: When you choose 'Copy until last row in Beer', the value of Q1 > 2026 Budget > Eclipse Light is copied until the last row in the Beer category, but not any other rows above it (Artisan Ale and Cosmic Craft Brews). The total and grand total are updated.
b) Copy to all rows in the category: If you want to copy the value to all the beer brands in that period (Q1), select 'Copy to all rows in Beer'.
c) Copy until last column: To copy the value until the last column (here, it is until Q4), use the 'Copy until last column' option.
d) Copy to all rows: To copy the value to all rows in the period until the end (all categories and brands), select the 'Copy to all rows' option.
4.2. Trend
While copying, you can apply an increasing or decreasing trend to the current cell value subsequently and then fill them up till the last row or column.
a) Copy until last row with trend in the category: Choose this option to copy until the last row in the category with a trend. After selecting, you will see a graphical slider that allows you to set an increasing or decreasing trend.
Adjust the slider to set a positive or negative trend.
If you want to enter a percentage precisely, you can click on the 'Input custom value' option. Then, enter a percentage and click Apply.
The values are updated till the last row in the Beer category. The total and grand total are updated.
b) Copy until last column with trend: Select this option to copy the value until the last column with an increasing or decreasing trend.
Use the slider to set an increasing or decreasing trend, or click on 'Input custom value' to enter a precise trend value and click Apply.
The selected value is copied with an increasing trend till the last column, Q4. The total and grand total are updated.
4.3. Lock and Copy
Similar to locking cells before allocation as discussed in this section, you can use 'Copy to rows/Copy with trend' in conjunction with 'Lock this cell' to achieve various use cases.
In the following example, we have used the Lock this cell and Lock all children options to lock a few key values that we do not want to alter. The annual budgets for two beer brands are locked. Also, the 2026 Q4 budget for the beer category is locked.
If we copy the 2026 Q1 budget value of the Artisan Ale brand to all rows in the Beer category, all brands except two are updated because their total is fixed.
Similarly, applying an increasing trend until the last column has no effect on Q4 because it has been locked.
Last updated
Was this helpful?