How to use Planning Visual

Last updated About 1 month ago

Overview

Once your tailored Planning Visual is published in BI Book, you can start planning. In this article, you'll learn the following:

  • Basics

  • How to use Suggestions

  • How to use Allocations

  • How to add calculated columns

Basics

  • You can manually input the values to the Planning cells.

  • The values are saved to database only after you press "Save":

  • To reflect the saved changes on your Planning visual you need to refresh the visual after pressing "Save":

  • You can undo ALL the changes you've done before pressing the "Save" button by clicking "Clear" button:

Note: you can't "Clear" the changes that have been saved to database.

  • You can undo the some changes in the order they were done by either using Ctrl+Z or clicking the undo button:

  • You can delete values from your Planning visual even if they were saved to the database by just pressing Backspace.

  • When inputting values to the total cells (rows or/and columns) and providing that all the underlying cells are empty (empty cell is not the same as a cell containing "0") - the total value will be equally allocated between the underlying cells. For example, we input value "1000" to the row subtotal and all the underlying cells were empty - 1000 is allocated among the cells equally so that each cell get "100":

  • When inputting the values to the total cells and some of the cells are not empty - total value is allocated among not empty cells:

  • You can copy/pastes the Planning cell(s) by selecting the cell or range of cells - pressing Ctrl+C - navigating to a cell where you would like to insert the values - pressing Ctrl+V.

Note: In order for the visual to behave in accordance with the defined logic, please copy/paste the values within the same context. Practically it means that you can copy and paste the underlying cells freely, but If you attempt to insert copied values into a range of cells that includes a total cell in the middle, it may lead to unexpected results due to the automatic calculations in the total cells.

  • The totals/subtotals can be aggregated either as an Average or Sum of rows. You can choose the aggregation method here:

  • Note: this feature is available only in report's edit mode. If you do not have access to the edit mode, you can ask your assigned analyst to change the aggregation method for you.

  • Our planning visuals also supports cell functions. You can manipulate cell values with basic functions (e.g., - + /). For example, in a cell with a value you should just start typing "=*2" and the cell value will be multiplied by 2:

Suggestions

Suggestions are pre-programmed calculations that can be used to populate your Planning Visual with, for example, last year’s actuals or rolling averages. Your assigned analyst will program suggestion measures based on your preferences and needs. Suggestions are a good jumping off point when the budgeting process starts.​

Use case example:

Sara estimates that this year’s salaries will be approximately the same as last year’s, so she decides to use Suggestions to populate values faster. She right-clicks the total-column of the salaries row and selects Last Year ACT –suggestion measure. The values in the salaries row are filled in automatically. Sara can now, if needed, adjust the values for this year or save the values as they are.

How to apply suggestions:

There are several ways you can apply suggestions in your planning.

  • Populating the entire Planning visual with suggestions. In the top-left corner you see Suggestions-button - press on the button - select the suggestion measure from the dropdown - press "OK":

  • Populating a single column or row with suggestions. Scroll to the Total column or row -right-click the Total cell of the wanted row/column - click Suggestion - select the suggestion measure:

Both the total cell and the underlying cells will be filled in with the suggested values:

Similarly with rows:

  • Populating a single cell with suggestion. Right-click the cell - click "Suggestion" - select the suggestion measure.

  • Populating a range of cells with suggestions. Select the range of cells you want to apply the suggestions to - right-click on the range - click "Suggestions" - select the suggestion measure:

  • Populating multiple totals with suggestions at the same time. Similarly, to the previous case, you case select multiple totals - right-click on the totals range - click "Suggestions" - select the suggestions measure.

Allocations

Allocations are measures that allow you to allocate a manually entered total number to columns or rows based on a pre-programmed calculation​. Allocations use the same calculations as suggestion measures but they are only used to allocate total numbers, whereas suggestions will edit the total numbers too.​

How to apply allocations:

Enter the total number to the total column/row - right-click the cell - click "Allocation" - select the allocation measure​:

Use case example 1:

Chris sets the target sales to 1.2M€ and wants to allocate the target to months based on last year’s sales. Chris enters 1.2M€ to the totals > right-click the cell > chooses the allocation measure:

Use case example 2 (suggestions & allocations):

Markus estimates that marketing costs will be approximately the same as last year but he wants to allocate the total sum evenly to all 12 months. First, Markus fills in the suggestion by right-clicking the total column of marketing costs – row. ​​Then, He right-clicks the cell again and allocates the suggested amount to months by selecting 12M evenly measure.

General note: the measures maybe be named differently in each model as the Planning visual is tailored to each customer's case. ​​

Calculated columns

You can create and manage calculated columns in your Planning visual. Saving calculated columns to database is not currently supported. The calculated columns are saved in the visual itself adding more clarity to your data.

How to add and manage calculated columns:

  • Press “Calculated Columns” button - press “+” to add a new column:

  • Enter "Column name" and select the "Column Type":

  • Available columns section contains the fields from your planning visual that can actually be used in a formula depending on the column type ( 2 types are available at the moment – numeric and text). To add a column to the formula window double-click the column name under "Available columns":

  • Depending on the column type, the list of available operators varies. For example, for numeric calculated columns, the following mathematical operators are available:

  • For text calculated columns only concatenation is available (“&”). If you want to add some custom text to an existing column in calculated column formula wrap your text with “”. For example:

  • Press "OK" - calculated column will be added to your visual immediately.

  • You can edit and delete the calculated columns in the "Manage calculated columns" window: