Budgeting & allocations

Inforiver Premium Table provides an intuitive way of performing planning, budgeting, and 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). You can spread data entered on a grand total cell to all the rows, based on weights from other measures.

1. Creating a series

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

1.1. From scratch (ZBB)

Let’s assume that we need to create a zero-based budget.

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 image below.

Creating an empty data input measure

1.2. Based on an existing series

Let’s take an example where we would like to create a budget based on the plan or the most recent forecast.

This can be achieved by creating a data input column as shown below.

Creating a data input column based on existing data

A column is created as shown below based on the forecast.

Data input column based on existing data

2. Allocate totals

In this example, let's consider a zero-based budget. Enter a value of 125m in the formula bar by double-clicking on the grand total cell.

Enter grand total to allocate

There are two ways to allocate the value entered in the total cell - equally or based on weights.

Sum is the only row aggregation type that allow allocations/distributions from total cells to the child rows.

2.1. Equal

After entering the value in the formula bar, press Enter. By default, the values get distributed equally across all rows.

Note that the values have been rolled up to the grand total cell.

Equal distribution

2.2. Weight

In this case, we don't need an equal distribution. We need to allocate the budget proportionally based on the actual sales.

• Click on the context menu appearing on the grand total cell with the value 125m and choose ‘Distribute by weights of Actual Sales’.

Distribution based on prior data values

• Once this option is selected, the budget values for each row are automatically updated based on their relative contribution to 'Actual Sales'.

Value gets redistributed based on actual sales' weights

3. Lock values

Values in data input columns can be locked at a cell level. The cells that are locked are not affected by any of the allocation methods.

Report users will be able to lock and unlock cells in read view. They will not however be able to unlock cells that were locked by the report author.

3.1. Lock a cell

Consider the following business case: There is an overall budget for the upcoming year that must be distributed to the categories based on actual sales. However, certain subcategories have a fixed budget, whose values must be updated and retained whenever any allocations are made.

Let's say the budgets for Bluetooth headphones and laptops are 1m and 20m, respectively.

This can be easily achieved using the Premium Table as shown below.

Note that we added a numeric data input column for the budget, entered it as 125 million, and then distributed the values based on the weights of actual sales.

• Double-click on the Bluetooth headphones' budget cell and update the value as shown below.

Updating a cell value

• After you press enter, go to the context menu and select Lock this cell.

Locking a cell

• The cell is greyed out, indicating that it is locked from further changes.

Locked cell

• Similarly, update the budget for laptops and other cells if required, and then lock them. The total budget has been updated to 132.82 million.

Cells locked

• Let's enter 125 million as the total budget to reallocate. The budget is redistributed, as shown below, without affecting the locked cells.

Changes to the budget doesn't affect locked cells

3.2. Unlock a cell

To unlock a locked cell, select Unlock this cell from the context menu. The selected cell gets unlocked.

Unlock cell

4. Copy values to rows

On clicking any row, you can see that there is a different context menu.

There are primarily two preset options for updating cell values through copy. You can either copy the same value across rows or use a trend.

Note: If these options do not satisfy your requirements, you can customize each of your cells manually.

Copy options

4.1. Copy

Using the options below, you can copy the current cell value and apply it to other cells.

• Copy until the last row

• Copy to all rows

1. The Copy until last row option copies the cell value to the rows following the copied cell but does not affect the values before it.

Copy until last row

1. The Copy to all rows option applies the cell value to all rows in the selected column.

Copy to all rows

The locked cell is not affected by the copy action. This way, you can lock the cells before copying a value to ensure that they do not change.

4.2. Trend

You can apply a trend based on the current cell value and apply it until the last row.

1. As you click on the '>', you can see a graphical slider where you can set a trend.

Graphical slider

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

Input custom value

1. Enter a percentage and click Apply.

You can use a negative value to establish a decreasing trend.

Custom trend value

1. The row values are updated based on the set trend until the last row.

Copy until last row with trend

As shown in the image above, you can achieve various business cases by combining the 'Copy with trend' and 'Lock this cell' options.

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