Budgeting & Allocations
The editable table interface of the Lumel Planning sheet supports both top-down distribution and bottom-up aggregation while performing simulations, scenario creation, or goal seeking, as demonstrated 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 organization's management sets 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, as well as aggregate detailed plans from multiple teams into a consolidated company budget.
You can create a new plan/budget from scratch (zero-based budgeting) or based on an existing data series. While using hierarchies, you can spread data entered on a parent cell to its descendants (both rows & columns), based on weights from other measures.
1. Creating a series
Let's first look at how to create a new series.
1.1. From scratch (ZBB)
Let’s assume you would like to create a zero-based budget for the year 2026.
This can be achieved by creating a data input column, as discussed in this section. The result is shown in the image below.
1.2. Based on an existing series
Let’s take an example where we would like to create a 2026 plan based on the 2025 plan (or the most recent forecast).
This can be achieved by creating a data input column as mentioned in the section: copy as data input. The result is shown in the image below.
2. Allocate totals
In this example, let's consider a zero-based budget for 2026.
Enter a value of 40m as the Q1 2026 Budget in the formula bar by double-clicking on the cell.
Now, there are two ways to allocate values entered in a total or subtotal - equally or based on weights. These are discussed below.
2.1. Equal
a) After entering the value in the formula bar, press 'Enter'. By default, the values are distributed equally across all the categories and subcategories. The updated values are highlighted for a while.
Note that the values have been rolled up to the grand total (Grand total -> 2026 Budget).
b) Let's consider another example where we enter a budget at the grand total level. Click on the highlighted cell and enter 160 m.
c) Once you click 'Enter,' you can see the value distributed equally across regions, subregions (row-wise), and quarters (column-wise).
2.2. Weight
Instead of an equal distribution, we can also allocate the budget proportionally based on 2025 actuals.
a) Click on the context menu appearing on the cell with the value 160 m and choose ‘Distribute by weights of 2025 Actuals’.
b) Once this option is selected, the 2026 budget values are automatically updated quarterly based on their relative contribution to 2025 Actuals.
3. Lock values
Values in data input columns can be locked at the cell level or for all children (subtotal/total level). The cells that are locked are not affected by any of the allocation methods.
3.1. Lock this cell
Let’s consider a business case—there is an overall budget for 2026, which is distributed to the regions based on 2025 Actuals. Certain regions and countries have a fixed budget: for example, annual budgets for India and LA are 7 m and 30 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 160m and distributed it by 2025 Actuals.
a) Click on APAC > India > Grand Total > 2026 Budget cell and update the value as shown below.
b) Once you press enter, click on the context menu and select Lock this cell.
c) The cell is greyed out, indicating that it is locked from further adjustments.
d) Similarly, update the budget for LA > Grand Total > 2026 Budget cell and lock the cell. Note that the overall budget has now become 162.7 m.
e) Let’s enter 160 m again as the overall budget. The budgets are redistributed as shown below.
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 cells that are locked are greyed out:
3.3. Lock all children
In some cases, you may want to lock all of the children in a particular cell. For example, all the children across rows, such as sub-regions (countries), and across columns, such as quarters, might need to be locked.
a) Click on APAC > Grand Total > 2026 Budget, and select Lock all children from the context menu.
b) All the child cells are locked, as shown in the image below.
c) Let's update the overall budget and see the effect on the child cells.
d) The increase is applied to all others, except the locked region and its children (APAC and its countries), in all quarters.
e) You can also lock at the country level by selecting the Lock all children option. It is important to note that the lock all children option is only available for the grand total.
f) The four quarters are locked, including the grand total cell.
3.4. Lock row
This option is available in the 'Measures In Rows' layout. You may need to lock an entire row when measures are displayed as rows. Consider a business case wherein you need to add a new measure to set the budgets for the next year.
After creating a new measure in the row, titled 2026 Budget, click on the row gripper and select Lock Row to lock the entire row.
The entire row is now greyed out, indicating that it is locked and updates are disabled.
3.5. Unlock
a) To unlock a locked cell, select Unlock this cell from the context menu. The selected cell gets unlocked.
b) All children of a locked cell can be unlocked by selecting the Unlock all children option from the context menu.
All child cells, both top-down and left-to-right—both countries and the quarters—are unlocked.
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. Edit or Copy Period values
When you click a child cell, you can see that there is a different context menu.
Primarily, 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 customize your 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
Copy to all columns in the column category
Let's take the example below to demonstrate these options.
a) When you choose 'Copy until last row in APAC', the value of Q2 > 2026 Budget > China is copied until the last row in APAC, but not any row above it (Australia), and the total and grand total are updated.
b) If you want to copy this value to all APAC countries within that period/measure, select 'Copy to all rows in APAC'.
c) Value can be copied until the last column using the 'Copy until last column' option.
d) To copy the value to all rows in the period (all regions and countries), select the 'Copy to all rows' option.
4.2. Trend
You can apply a trend based on the current cell value and apply it until the last row or column.
a) As you click on the '>', you can see a graphical slider where you can set a trend.
b) Adjust the slider to set a positive or negative trend.
c) If you want to enter a percentage precisely, you can click on the 'Input custom value' option.
d) Enter a percentage and click Apply. The values are updated.
4.3. Lock and Copy
Similar to the locking cells before allocation discussed in this section, you can use 'Copy to rows/Copy with trend' in conjunction with 'Lock this cell' to achieve various business cases.
Consider the following example: the value for China, as well as all of the Q4 values, is fixed, and the trend must be applied to the other quarters. Click on the Lock this cell option to lock all required cells.
On applying a trend of 20% to all APAC rows, all countries except China are updated. Similarly, applying the trend to all columns has no effect on Q4, which has been locked.
Last updated