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.

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.

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

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.

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

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.

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

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

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.

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.

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

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

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

• Let's enter 125 million as the total budget to reallocate. The budget is redistributed, as shown below, without affecting the 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.

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.

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.

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

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.

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

1. Enter a percentage and click Apply.

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

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.