Budgeting & allocations

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.

1. Creating a series

Let's first look at the ways of creating a new series.

i. From scratch (ZBB)

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.

Blank measure inserted

ii. Based on an existing series

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.

Measure created based on 2022 Plan

2. Allocate totals

In this example, let's consider a zero-based budget for 2023.

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.

Entering a value at the total level

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.

i) Equal

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).

Equal distribution

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.

Update grand total value

c) Once you click 'Enter', you can see the value distributed equally across categories, subcategories and quarters.

Equal distribution at grand total level

ii) Weight

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’.

Distribution based on prior year values

b) Once this option is selected, the 2023 Budget values are updated automatically across product categories & quarters by their relative contribution to 2022 Actuals.

Value gets redistributed based on weights

3. Lock values

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.

i) Lock this cell

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.

Editing a cell

b) Once you press enter, click on the context menu and select 'Lock this cell'.

Locking a cell

c) The cell is greyed out indicating that it is locked from further changes.

Cell locked

d) Update the budgets for Soda and lock the cell. Note that the overall budget has now become 1.28b.

Budget for soda locked

e) Let’s enter 1.2b again as the overall budget. The budgets get redistributed as shown below.

Changes to the budget doesn't affect locked cells

ii) Lock all children

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.

Lock all children

b) All the child cells get locked as shown in the below image.

Child cells are locked

c) Let's update the overall budget and see the effect on the child cells.

Updating the budget

d) The increase gets applied to Water and its subcategories across all quarters.

Changes to the budget doesn't affect locked cells

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.

Lock all children at a subcategory level

f) The four quarters are locked including the grand total cell.

Juices across quarters locked

iii) Lock row

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.

Lock row on Measure in Rows

The entire row is now greyed out, indicating that it is locked and updates are disabled:

Row locked for editing

iv) Lock multiple cells

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.

Lock Selected Cells

The cells that are locked are highlighted:

Locked Cells Highlighted

v) Unlock

a) To unlock a locked cell, select 'Unlock this cell' from the context menu.

Unlock cell

b) The selected cell gets unlocked.

Cell unlocked

c) All children of a locked cell can be unlocked by selecting the 'Unlock all children' option from the context menu.

Unlock all children

d) All the cells including the quarters are unlocked.

All children unlocked

e) To unlock a row when measure in rows in enabled, click on the row gripper and select Unlock Row.

Unlock Row

f) To unlock multiple cells, ctrl + click the cells and click on Unlock selected cells.

Unlock selected cells

4. Edit period values

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.

Options to edit period values

i) 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

Note that the options shown in the below image are enabled only when there are two or more categories in the rows and columns.

Copy options

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.

Copy to all rows

b) If you only want to copy a particular value within the category, you can select the option highlighted below.

Copy to all rows in the category

c) Value can be copied until the last column using the highlighted option.

Copy until the last column

ii) 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.

Graphical slider

b) If you want to enter a percentage precisely, you can click on the 'Input custom value' option.

Input custom value

c) Enter a percentage and click 'Apply'.

Custom value

d) The values for Soda and Tea & Coffee are updated as shown in the below image.

Copy until last row with trend

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.

Lock cells and copy to other columns

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.

Trend applied to unlocked cells

In the next section, we'll be covering set version.

Resources

Forecasting Multiple Items with Seasonality in Power BI

5 ways to enhance your budgeting & forecasting process for better data-driven decisions