Budgeting & allocations
Last updated
Last updated
Inforiver provides an intuitive way of performing planning, budgeting & allocations through an editable table interface. You can create a new plan/budget based on an existing data series or from scratch (Zero-based Budgeting). While using hierarchies, you can spread data entered on a parent cell to its descendants (rows & columns), based on weights from other measures.
Let's first look at the ways of creating a new series.
Let’s assume you would like to create a zero-based budget for the year 2023.
This can be achieved by creating a data input column as mentioned in the section create a numeric input column. The result is shown in the below image.
Let’s take an example where we would like to create a 2023 Plan based on the 2022 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 below image.
In this example, let's consider a zero-based budget for 2023.
Enter a value of 290m as the Q1 2023 Budget in the formula bar by double-clicking on the cell.
There are two ways to allocate a value entered in a total or subtotal - equally or based on weights.
The equal and weight options are not shown when you click on a cell at the child level.
Sum and Weighted Average are the only row aggregation types that allow allocations/distributions from total cells to the child rows.
a) After entering the value in the formula bar, press 'Enter'. By default, the values get distributed equally across all the categories and subcategories. Inforiver automatically highlights the updated values.
Note that the values have been rolled up to the grand total (Grand total -> 2023 Budget).
b) Let's consider another example where we enter a budget at the grand total level. Double-click on the highlighted cell and enter 1.2b.
c) Once you click 'Enter', you can see the value distributed equally across categories, subcategories and quarters.
In this case, we don't need an equal distribution. We need to allocate the budget proportionally based on 2022 actuals.
a) Click on the context menu appearing on the cell with the value 1,200.0 and choose ‘Distribute by weights of 2022 Actuals’.
b) Once this option is selected, the 2023 Budget values are updated automatically across product categories & quarters by their relative contribution to 2022 Actuals.
Values in data input columns can be locked either at a cell level or including all children (at a subtotal/total level). The cells that are locked are not affected by any of the allocation methods.
A child node can be locked only when row aggregation type is set to Sum or Average.
Let’s consider a business case – There is an overall budget for 2023 which is distributed to the categories based on 2022 Actuals. Certain categories and their sub-categories have a fixed budget, and the budget has to be reallocated once these changes are made. Budgets for Juices and Soda are 500m and 350m respectively.
This can be easily achieved using Inforiver as shown below.
Note that we have inserted a numeric data input column for 2023 Budget, entered the budget 1.2b and distributed by 2022 Actuals.
a) Double-click on the Juices -> 2023 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 changes.
d) Update the budgets for Soda and lock the cell. Note that the overall budget has now become 1.28b.
e) Let’s enter 1.2b again as the overall budget. The budgets get redistributed as shown below.
In some cases, you might want to lock all the children for a particular cell. For example, all the children across rows such as sub-regions and/or across columns such as quarters might need to be locked.
Lock all children option is not shown when you click on a cell at the child level.
a) Click on Beverages -> 2023 Budget, and select 'Lock all children' from the context menu.
b) All the child cells get locked as shown in the below image.
c) Let's update the overall budget and see the effect on the child cells.
d) The increase gets applied to Water and its subcategories across all quarters.
e) You can also lock at the sub-category level using the lock all children option. Note that only in the case of the grand total, the lock all children option is available.
f) The four quarters are locked including the grand total cell.
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 titled 2023 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:
In order 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 highlighted:
a) To unlock a locked cell, select 'Unlock this cell' from the context menu.
b) The selected cell gets unlocked.
c) All children of a locked cell can be unlocked by selecting the 'Unlock all children' option from the context menu.
d) All the cells including the quarters are unlocked.
e) To unlock a row when measure in rows in enabled, click on the row gripper and select Unlock Row.
f) To unlock multiple cells, ctrl + click the cells and click on Unlock selected cells.
On clicking a child cell, you can see that there is different context menu. There are two preset options to update cell values. You can copy the same cell value across rows or columns or apply a trend.
If these options do not satisfy your requirements, you can customize your cells manually.
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
Note that the options shown in the below image are enabled only when there are two or more categories in the rows and columns.
Let's take a simpler example - with two categories (Product category and subcategory) in the rows and Quarters in the column.
a) On selecting 'Copy to all rows', the value for Soda is copied to all other subcategories.
b) If you only want to copy a particular value within the category, you can select the option highlighted below.
c) Value can be copied until the last column using the highlighted option.
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) If you want to enter a percentage precisely, you can click on the 'Input custom value' option.
c) Enter a percentage and click 'Apply'.
d) The values for Soda and Tea & Coffee are updated as shown in the below image.
e) You can use 'Copy with trend' in combination with 'Lock this cell' to achieve different business cases. Let's consider an example where the value for Q3 is fixed and the trend needs to be applied to the other quarters. Click on the 'Lock this cell' for the cell highlighted.
f) On applying a trend of 20% as shown in the image, Q2 and Q4 values are updated. Q3 is greyed out as it is locked.
In the next section, we'll be covering set version.
Forecasting Multiple Items with Seasonality in Power BI
5 ways to enhance your budgeting & forecasting process for better data-driven decisions
