Plan

Plan is a comprehensive solution for financial planning and analysis. It offers top-down and bottom-up calculation in single and dual KPI mode as well as budgeting, forecasting and other features such as comparison columns.

The Plan extension comes in two versions: Plan Single KPI and Plan Dual KPI.

Check out a YouTube video for an example Plan use case.

This part of Write! documentation explains the following aspects of Plan usage:

• Basic Configuration—to get you started with the Plan extension.
• Descriptions of Write! Plan-specific options in the Property Panel, including Dimensions, Measures, and more.
• Operation—instructions for the end users explaining how to use configured extensions.

Basic Configuration

This section provides a step-by-step guide for configuring the Plan Extension.

Configure Dimensions and Measures

You need to start with configuration of Dimension and Measures in your extension. To do this, follow the below instruction:

1. Load Source Data using the Data load editor. Source Data is financial data that you want to build your app on.

2. Go to Qlik app > Custom Objects and drag and drop the Plan extension into your sheet.

   

3. Configure initial Dimensions. By default, the extension searches for dimensions and, if available, selects the Year field for the year and the Month for period.

   1. Go to Property Panel > Dimensions.
   2. Set the Dimension field year property to a value that represents year in your data.
   3. Set the Dimension field period property to a value that represents period in your data.
4. Configure at least one more Dimension (required). For more information, see Dimensions.

5. Add the Measure. The measure is the KPI you want to analyze.

   1. Go to Property Panel > Measure.
   2. Select the Add Measure option and enter a suitable expression.

You have configured dimensions and measures in your Plan app.

Configure Budget Name

The Plan extension distinguishes different budgets by their name on the basis of the Qlik Field - Budget Name. To configure the field, you need to go to Property Panel > Write! - Plan Settings and find the Field - Budget Name field. The field value needs to read planId. If this name is available, the software automatically fills in the budget name. If such a name isn't available, you can rename the field that names your budgets in the data load script. For more information, see Renaming fields in Qlik Documentation.

When you configure the budget name, you need to setup writeback and perform a reload.

Setup Writeback and Reload

To setup writeback and perform a reload you need to do the following:

1. Create the Plan endpoint—for information on creating endpoints, see Endpoints.
2. Update Qlik Load Editor.
3. Add the endpoint to the Plan extension—for more information, see Server Settings.

Update Qlik Load Editor

The endpoint provides a snippet that you can paste into the data load editor. For more information, see Management Console.

You also need to configure a Qlik data connection that the Qlik load editor can use to load the endpoint data. It needs to point to the same database as the storage connection in which the Endpoint was created. For more information, see Storage Connections.

Finally, you need to merge your source data needs with the endpoint's data in the data load editor. To do so, adapt the following snippet to your needs:

where the SourceData object refers to your source data and EndpointName refers to the loaded endpoint data.

Dimensions

The following sections explain the Plan-specific settings in the Dimensions section of the Property Panel.

Key-Value Mapping

Key-value mapping in Plan is similar to the key-value mapping in the Edit and Form extensions. For reference, see Key-Value Mapping . Activating key-value mapping for a dimension replaces the displayed values with a configurable key during writeback. You can activate the feature with the Enable Dimension Key checkbox . Then, select the key field in the Dimension Key dropdown.

Internally, key-value mapping attaches the =MaxString({1}DimensionName) attribute expression to the Dimension field.

Figure 1:  Properties for activating key-value mapping for a Dimension

In Plan dimensions, similarly to the Edit extension, you can activate the Developer Mode feature. For reference, see Developer Mode. When you activate the feature, the software displays the the keys next to the values in the dimension value cells for the mapped dimension

Expand/Collapse by default

With the Expand/Collapse by default feature, you can configure default behaviour for expanding or collapsing the period on initial rendering. The options are Expand, Collapse or Conditional Expand. To use the Conditional Expand option, you need to provide a Qlik expression to determine the behaviour.

Measures

The following sections explain the Plan-specific settings in the Measures section of the Property Panel.

Single KPI

In Plan Single KPI, you can define exactly one measure, which is your KPI. Plan creates several measures automatically if certain feature, such as for example Client Hybrid, are used. When you add a measure, the software prompts you to add an expression for it.

The expression can't have a set analysis if the Forecast feature is used. Forecast also uses set analysis in the background and can't map the customer-specific set analysis together.

Example of set analysis in measure expression:

Calculation Strategy

Calculation Strategies determine how Plan calculates values of a given measure. The Plan extension dimensions create a tree structure, across which the calculations run. They can run either top-down or bottom-up. On every initial render, the software executes the chosen strategy bottom-up to determine all values in the Plan table. When you edit entries in the table, the software executes the configured calculation strategy both bottom-up and top-down from the edited cell to update the values of all affected cells.

You can deactivate the top-down calculation in Write! Plan Settings. For more information, watch this YouTube video.

There are two strategies available:

• Sum—In the bottom-up mode, the Sum strategy sums up all values of a given dimension and puts the result in the parent dimension cell.

• Average—In the bottom-up mode behaves similarly to Sum, but instead calculates the average of the values of a given dimension.

In top-down mode the changes of a cell are distributed down in the hierarchy.

If activated, the total column can also be edited. The top-down calculation distributes the updated value first among its periods and then downwards from there. For more information, see Show Total Column.

The average calculation strategy comes in two types: In the Property Panel you can select either the Equal or Weighted distribution of values for the top-down calculation.

Figure 2:  Top-Down Average Distribution in the Property Panel

Weighted

The Weighted distribution takes the values of the next lower hierarchy level as distribution keys. The difference in the edited value is then split proportionally by that distribution and added to the cells. If there's a residue due to rounding errors, it is added to the last cell of that level. The Sum calculation strategy uses this calculation method.

Equal

The Equal distribution simply distributes the edited value equally among the cells of the lower hierarchy. If there's a residue due to rounding errors, it is added to the last cell of that level. The Sum calculation uses this method if the next lower hierarchy of a changed value only contains cells with value 0.

Figure 3:  Top-down equal distribution (Sum)

Figure 4:  Entering 200.000 in a cell

Figure 5:  Top-down weighted distribution (Sum)

Figure 6:  Entering 200.000 in cell

For more information on Plan calculation strategies, see YouTube video.

Field Type

Two Field Types are available in Plan: Number and Currency. For more information on Field Types, please check out the Field Type section in Edit and Form.

Figure 7:  Field Types available for Plan

Comparison columns

Comparison columns enhance the analytical capabilities of your Plan application by allowing users to place the total values of different budgets directly next to the current budget. These columns are conveniently situated adjacent to the dimension column for easy access and analysis.

Configuration

Important: Qlik Sense needs to be updated to at least one of the following versions: February 2024, November 2023 Patch 2, August 2023 Patch 8 or May 2023 Patch 11.

You can configure Comparison columns in the Property Panel as either public or private, providing two distinct levels of user interaction and customization.

Public Columns

Configured by the application developer, public columns are universally accessible to all users. They offer the highest degree of customization and are defined within the Comparison columns section of the Property panel.

To add a new column:

1. Navigate to the Comparison columns section.

2. Click on the Add Column [1] button.

3. Inside the added entry, set the Label [2], Expression [3], Number Formatting [4] and Show Column if. The expressions used for the Label and Expression need to return a single value to ensure that the column displays correctly. Show Column if should return a boolean value indicating whether the column is displayed or not.

Figure 8:  Comparison columns section inside the Property Panel

Private Columns

If the application developer permits this functionality, private columns can be added individually by each user inside the configuration dialog. Inside the dialog, users can opt for:

• Basic Mode: A user-friendly option allowing the selection of pre-set budget and year combinations. The system auto-generates the columns label unless specified manually.

• Advanced Mode: Only recommended for experienced users. Allowing the user to define custom expressions.

Interaction

When at least one public column is configured or adding private columns is allowed, a new button is added to the navigation bar.

Figure 9:  Empty Configuration Dialog

Clicking the button reveals a configuration dialog where users can:

• Toggle the visibility [1] of every comparison column.

• Add private columns [2], if the feature is enabled.

• Remove private columns [3].

All modifications made by the user are stored locally and are only available inside the user's context.

Comparison Columns Styling

You can customize the appearance of comparison columns in the Plan extension to make them more visually distinct and easier to identify. You can manage the styling in Property Panel > Appearance > Comparison Columns.

You can customize the following visual elements for comparison columns:

• Header background color - Change the background color behind the header text.

• Header font color - Adjust the text color of the header.

• Header font size - Modify the size of the header text.

• Dim Cell Text - Dim the comparison column text, making editable columns stand out more clearly.

• Cell Background Color - configure independent background and text colors. You can assign a distinct background or text color to comparison columns, making them stand out (or recede) relative to the editable area of your table. This is useful when working with large tables where quickly distinguishing column types at a glance is important.

If a cell can't be edited, hovering over it cell displays a Not-allowed cursor icon.

These styling options help you create visual hierarchy and make comparison columns stand out from other data columns in your extension.

Appearance

The following sections explain the Plan-specific settings in the Appearance section of the Property Panel.

General Styling

Styling Dimensional Hierarchy allows you to colorize the rows in the Plan extension table such that the hierarchy levels are more easily distinguishable.

Three options are available:

• Standard Write! colorization—offers a greyscale default.
• Hierarchical colorization—enables you to choose a color, which results in the hierarchy levels' background colors to blend between that color and a white background.
• Fixed colorization—omits the color blending.

Figure 10:  General Styling

Figure 11:  Hierarchical colorization

Show Footer (Aggregated Column Values)

If the Show Footer option is enabled, each column has an aggregated column value which shows the sum or (weighted) average of the respective column, depending on the selected Calculation Strategy.

Footer cells can also be edited as long as the Activate Top-down Algorithmis activated. Editing a footer cell is analogous to editing the top-layer of the Plan extension. If there are multiple top-layers, the entered value is distributed among these top-layers according to the calculation strategy.

Row Settings

Set the font size of table rows.

Styling Edited Cells

You can find information on styling settings under Styling in the Common Features chapter.

Styling Navigation Bar

You can find information on styling settings under Styling in the Common Features chapter.

Styling Progress Bar

In the Styling Progress Bar section, you can decide whether the progress bar is active. It is active by default. The progress bar shows progress when loading or saving data and re-rendering the extension. Useful with large datasets.

Figure 12:  Styling Progress Bar in the Property Panel

Styling Editable Columns

You can find information on styling settings under Styling in the Common Features chapter.

Styling Non Editable Columns

You can find information on styling settings under Styling in the Common Features chapter.

Write! Conditions Settings

The Conditional Rendering feature limits the cardinality of a selected field to a maximum number. If this number is exceeded, the extension doesn't render. You can activate the feature in the Write! Conditions Settings.

In the example below, only one Region may be selected for the Plan extension to load. If more than one region is selected, Plan doesn't render and informs the user to make a proper selection.

Figure 13:  Conditions - Settings

Figure 14:  The message displayed at the bottom due to the selection of more than 1 region

Write! Hybrid

For more information on Write! Hybrid, see Write! Hybrid section in the Common Features chapter.

Write! Plan Settings

Figure 15:  Plan Settings

Budget Name

Set the measure or dimension that the budgets presented by the Plan extension should be based on.

Base Year and Base Period

The Base Year field returns a number. The field returns one of the Period field periods. Year-period combinations up to and including the Base Year - Base Period combination can't be edited in Plan. For example, if base year is 2024 and base period was March, any column prior to- and including March 2024 isn't editable.

You can define Base Year and Base Period in the data load editor as follows:

Using the field defined above as Base Year and Base Period makes all table columns before April 2024 non-editable.

Row Freezing

Row Freezing allows for each row (or node) of the pivot table to be locked. Rows in locked state can't be edited and aren't affected by any algorithms running on the pivot table.

Figure 16:   Plan extension with frozen rows

The circle icons in the Dimension column represent the frozen state of the respective row. By clicking on the circle icon, a user with the necessary permissions can change the frozen state. A full circle indicates that the current row and all of its child rows are locked, that is, have the frozen state. An empty circle indicates the opposite—that the current row and all of its child rows are editable.

Each half-filled circle symbolizes a blended row that exhibits either:

• a combination of locked and unlocked child rows

• or, in the case of multiple selected years, columns may be locked for certain years while being unlocked for others.

Blended rows remain editable, but changes to them don't affect the locked columns. Changes to the freezing state are written into the mentioned field inside the database. Therefore, all Plan endpoints have been extended to include a new default column called frozen. The generated Qlik load scripts have been adjusted and can be used to update the apps' Qlik load scripts.

Example load script

This section contains an example load script of a Plan endpoint called 'IBP_SalesPlan_V1'. The mentioned table names therefore pertain to that name.

Figure 17:  Sample load script

1. The first statement loads the temporary table tmp_salesPlan_full_data from the database. It is a temporary table because it merely serves as in-memory data source, leading to faster responses in subsequent queries.

2. In the next step, only the dimensions are loaded together with the key Key_Dimension. The key is a combination of all dimensions in the respective endpoint. Also for performance reasons, it is transformed using the Qlik autonumber script function, which returns a unique integer value for each distinct evaluated combination of the dimension values.

3. The following two cross table load statements transform the cross table structure of the temporary table tmp_salesPlan_full_data into a straight table format. That is, a wide table with many columns turned into a tall table, with the column headings being placed into a single attribute column.

4. The values and the frozen states are then joined to the table holding the dimensions (IBP_SalesPlan_V1). The resulting table therefore has a value column and a frozen column that both contain values (NULL-values are also possible) for each combination of dimension values and periods.

Extension settings

You can select the field containing the frozen state in the Write!- Plan Settings under Field - Row Freeze. The row freezing permission settings are available there, too. Under Allow Row Freezing, you can set the permission to change the frozen state through a dropdown menu. The available options are: No one, Everyone and Conditional. The latter option provides a Qlik formula to set the condition.

Figure 18:  Field - Row Freeze feature on the UI

After setting the permissions, the Row Freeze column appears in the extension and a measure is added in the property panel.

Its default expression reads:

where RowFreezeFieldName is the name of the field containing the frozen state.

Usually editing its value isn't necessary. Exceptions would be, for example, the usage of set-expressions in the other measures. The expression can be reset to its default value by disabling and enabling Field - Row Freeze in the Write! - Plan Settings.

Budget Name as Free Text

Enabling this feature enables you to choose the budget name yourself. If it's disabled, the budget name can only be selected from a dropdown of existing names.

Selecting Conditional Free Text enables you to enter a Qlik Expression, for example:

if(UserRole='Manager',1,0)

This expression allows users with the Manager user role to create new budgets as free text. All others may only use existing budgets or budgets previously created by managers.

If Budget Name as Free Text is Disabled or Conditional Free Text is selected, two additional dropdowns appear:

• Budget Name - Source Field: After selection, budget names are limited to the values of this field. By default, the Budget Name is restricted to already existing budgets.

• Associated Budget Year: When enabled, the software limits year choice to a dropdown containing the years already available for the currently selected budget. This means that only the year(s) associated with the current budget can be selected. Enabling this restriction can be bound to a Qlik expression by selecting the Conditional Association option.

Activate New Budget

With the Activate New Budget feature, you can decide whether the option to create a new budget appears in the navigation bar.

Prefill Missing Periods

If the Prefill Missing Periods feature is active, periods that don't exist when a new budget is created are created and automatically filled with 0.

Activate New Forecast

With the Activate New Forecast feature, you can decide whether the option to create a new forecast appears in the navigation bar.

Show Total Column

With the Show Total Column feature, you can decide whether the total column is displayed in the table.

Total Column Name

The Total Column Name property enables you to define a name for the total column. It's visible only if the Show Total Column feature is active.

Activate Top-down Algorithm

When the Top-Down Algorithm is enabled, you can enter values in a top-down format. If disabled, data entry will be restricted to a bottom-up format, and editing of footer cells and the total column will be disabled.

Paging

The Paging feature enhances the Fiplana Plan Single KPI extension as it changes the way data is rendered and loaded. When enabled, the extension switches to a new underlying engine that provides fast rendering of Plans regardless of the size of the underlying data.

The feature implements intelligent data loading, which means that only the visible (scrolled-to) data is loaded initially, and during editing operations, only the data necessary for the specific edit is retrieved. This significantly improves performance, especially when working with large datasets in Qlik Sense.

Enabling Paging

To enable the Paging feature, follow these steps:

1. Navigate to Qlik Sense > Plan > Property Panel > Fiplana Plan Settings.

2. Locate the Enable Paging option.

3. Activate the feature by clicking on the checkbox.

Once Paging is enabled, the software automatically moves the Total column to the first column position. Also, the expand and collapse functionality aligns with native Qlik Pivot table behavior. Data loading becomes optimized for the visible viewport.

Paging Limitations

The following limitations apply when Paging is enabled:

• The Default expand-collapse by dimension feature is discontinued when Paging is active. The property panel no longer offers default dimension-based expand/collapse options.

• New budgetsand forecasts are no longer immediately editable after creation. Users must save and reload the application in Qlik before new budgets and forecasts become editable.

• Private comparison columns are restricted to published applications only. Private comparison columns cannot be used in unpublished app environments.

• For the Row Freezing feature to correctly work for columns with Paging enabled, you need to update your load script. The script needs to load "frozen" as 0 even when it is null. The copyable load script has been updated to include this change with appropriate hints for implementation.

Allow Column Collapse

The Allow Column Collapse option is visible only when Enable Paging is active.

When enabled, users can collapse and expand year-columns in the Plan extension. When disabled, year-columns remain permanently expanded—users can only expand a previously collapsed column, but cannot collapse it again.

Disabling this setting is useful for use cases where periods represent non-time-based dimensions, such as currencies. In those scenarios, collapsing columns would aggregate values that should not be combined (for example, mixing USD and EUR figures).

Automatic Preload

The Automatic Preload option is visible only when Enable Paging is active.

When enabled, the Plan extension preloads the full dataset for the current selection in the background. A loading indicator appears in the toolbar while data is being downloaded. Once preloading is complete, the indicator changes to a speedometer icon, indicating that Fast editing is enabled.

With fast editing active, user input triggers calculations using the locally cached data instead of querying Qlik for each change. This results in significantly faster calculation times during editing.

Manual Edit Mode

With the Manual Edit Mode feature, you can turn the edit mode on and off manually. When this edit mode is active, the extension doesn't reload. If this checkbox is active, the symbol appears in the Navigation Bar. When you click on it, you can either stop or continue the reload.

Activate Developer Mode

When you activate the Developer Mode, the software displays both the name of the record in the extension and the entry written back to the database. The Developer Mode active example figure shows how the software represents the customer name displayed in Qlik and the customer number written back to the database when the Developer Mode is activated.

Figure 19:  Developer Mode active example

insightsoftware recommends deactivating this functionality after implementation.

Ignore Decimals in Calculation

With the Ignore Decimals in Calculation mode enabled, the Plan algorithm ignores any decimals settings configured in the measures settings. The algorithms always calculates and saves values with maximum accuracy, and only uses decimals settings for display. This mode is off by default. You can enable the mode using the Ignore calculation decimals option in the Property Panel > Write!- Plan Settings. When the option is deactivated, the Plan algorithm uses the decimals settings configured in the measures settings and rounds calculation values accordingly when propagating results bottom up and top down.

Server Settings

For more information on Server Settings, see the Server Settings section in the Common Features chapter.

Write! Info

For more information on Write! Info tab, see the Write! Info section in the Common Features chapter.

Dual KPI

The Plan Dual KPI extension allows for two KPIs to be displayed, calculated and edited inside the Plan table. You can drag the extension onto the sheet like any other Write! extension.

The Plan Dual KPI extension can operate in three different modes:

• Calculated Result Column—Multiplies the two KPIs in a third column, creating the possibility to, for example calculate revenues from prices and amounts.

  You can enable the Calculated Result Column option in the Property Panel under Measures. Of the two measures from which the result column is calculated, one needs to use the Sum and the other needs to use the Average calculation strategy. Now, an additional measure is automatically added to the existing measures. It holds the result column. One consequence of calculating a third column is that one column out of the tree needs to be locked for the other values to be computable. The locked KPI can be selected using the KPI Lock option.

• Independent KPIs—Two KPIs are displayed next to each other without interaction.

  In the Independent KPIs mode, the two KPIs can be any combination of Average and Sum calculation strategy. In this mode, the table acts like a standard Plan table with two independent KPIs visible at the same time.

Example of Calculated Result Column:

In one row, two KPI columns Price and Amount are valued at 1 and 3 respectively. Therefore, the calculated result column called Revenue reads 1*3=3. Now, the KPI Amount is locked and the value of Revenue edited to 10. Because Amount is locked, the calculation is solved with Price changing to 3.33.

Price 3.33 | Amount 3 | Revenue 9.99

Aggregate Calculation Strategy Formulas

This section explains the strategies' calculation formulas in the Calculated Result Columns Plan Dual KPI when applied to aggregate values. In this case, aggregate values refer to the total values of each row and the result values in higher hierarchy levels for each column.

For the Price and Amount KPIs where the Price uses the Average strategy and Amount uses the Sum strategy, the formula reads:

The price is calculated as a weighted mean of the individual prices given the individual amounts.

The Amount column uses the Sum strategy. Its aggregate values are therefore simply sums over the individual rows and columns:

Property Panel - Dual KPI

The property panel of Plan Dual KPI extends that of Plan Single KPI. Therefore, only additional options specific to Plan Dual KPI are listed here.

KPI Lock

As explained above, Plan Dual KPI needs one measure in the Calculated Result Column mode to be locked. You can select the default locked KPI by using the dropdown under Locked KPI by default. Its options are limited to the measures defined in the Measures tab.

Figure 20:  KPI Lock

You can enable users to manually select the locked measure in the extension during usage by setting the User-Lock option. You can select the Always allowed, Never allowed, and Conditional Lock. With Conditional Lock, you can determine access using a Qlik expression. Users who are allowed to switch between locked KPIs can do so through an additional navigation bar entry displaying a gear icon that is visible to them.

Figure 21:  Gear icon in the navigation bar

Figure 22:  Locked KPI

Locked Column as Calculation Target

The Locked Column as Calculation Target feature modifies how locked columns behave in Dual KPI calculations. By default, a locked column remains static and does not change when other KPIs are edited. Enabling this option transforms the locked column into a calculation target, meaning that changes to other KPIs will adjust that locked column rather than keeping it fixed.

This feature is available in the Property Panel > Write! Plan Settings (Dual KPI) as a toggle button labeled Locked column as calculation target, located at the bottom of the Dual KPI configuration options.

Default Behavior (Disabled)

When disabled, the locked column acts as a constraint. Using the example formula Price × Amount = Cost:

• If Cost is locked and you edit Amount, the system recalculates Price to maintain the locked Cost value.
• The locked column never changes regardless of other edits.

New Behavior (Enabled)

When enabled, the locked column becomes the target of all calculations:

• If Cost is locked and you edit either Price or Amount, the system updates only the Cost column.
• Changes to non-locked KPIs propagate to the locked column, allowing users to experiment with multiple factors to achieve a desired target value.
• The system uses the configured Calculation Strategy to propagate changes appropriately.

User-Accessible Toggle

If User-Lock is enabled (see KPI Lock), you can toggle the Locked Column as Calculation Target directly in the extension via the Settings toolbar button (gear icon in the navigation bar). This allows you to switch between locking behavior and target-based calculation on demand, without requiring extension reconfiguration.

Operation

Under Operation, you can find helpful information and tips and tricks for your daily work with Plan.

Create New Budgets

The New Budget popup window (see the New Budget window image) allows for the creation of new plan budgets based on the current selection of budgets and years.

Figure 23:  New Budget window

You can create several new budgets simultaneously using the Add Year button in the popup. By default, the popup automatically adds a new budget year for each selected year. You can give a name to new budgets using the Budget Name option where you can choose between a free text or a dropdown, depending on the extension's settings. By default, the current budget name is selected.

In addition, each new budget requires three inputs: A Base Year, a Budget Year and a planning Factor in %:

• The Base Year can be set to any year of the current selection. Its associated budget serves as the base for this new budget.

• The Budget Year can be set to any year. Therefore, overriding existing budget-year combinations is explicitly allowed, too.

• The Factor in % can be set to any non-negative number.

During the calculation of new budgets, the Base Year's values are multiplied by Factor in %'s value to calculate the new budget's values.

For example, setting Factor in % to 100 results in the new budget to be exactly the same as the base budget, while setting it to 200 doubles the base budget's values in the new budget.

The Prefill missing periods option can be enabled to prefill any undefined periods in the base budgets with 0 values in the new budgets. The option is enabled by default.

Press Ok in order to create the configured budgets. The new budgets are rendered in the Plan extension and can subsequently be saved.

After successfully saving the budgets and a reload of Qlik's data, the created budgets and years are available in the app.

Create Rolling Forecasts

You can create rolling forecasts using the icon on the navigation bar. For more information, see Navigation Bar.

Rolling forecasts combine two existing budgets into one by taking the first n periods of a year from one budget and the remaining periods from another. A common use case is combining actuals of a year up to a point with a planned budget for the remainder of the year to update financial planning to a year's realities.

To create a new forecast, do the following:

1. Go to Plan navigation bar and click the Create new Forecast icon. The software displays the Create Rolling Forecast modal.

   Figure 24:  Data inside the Create Rolling Forecast modal

   

2. Configure periods for the Base and Period columns by filling in the respective Start Period and End Period.

3. Configure the KPIs. For each row, that is, Price and Amount, configure KPI source by providing the values for Scenario and Year. You can choose different sources for each KPI.

4. Provide associated multiplication Factors for the Base and Budget KPIs. The KPI chosen in step 3 is multiplied by the multiplication factor and inserted in their periods of the forecast. In the Data inside the Create Rolling Forecast modal figure this would be, for example, the Actuals in 2024 with a factor of 100% for the Base periods.

5. (Optional) Enable the Fixed checkbox. The checkbox forces the forecast budget values to change such that the Base Period combined with the forecast periods add up to the original total value of the Budget Period source.

6. Fill in the Forecast Name and Forecast Year fields.

7. Press the Save button to confirm the configuration.

You have created a Rolling Forecast. For more information on the forecasts, see the this YouTube video.

Budgets And Forecasts - Use Cases And Planning Examples

The following section contains example use cases covered by the Budget and Forecast features as well as an example integration in an Integrated Business Planning App.

Budgeting

Budgeting is typically done prior to the start of a fiscal year. This use case is fully covered by Plan.

In a scenario where a controller wants to create a new budget based on last year actual revenues and plans to increase revenue by, for example, 10%, they need to do the following:

1. Select the actuals data in Plan.
2. Open the New Budget modal dialog.
3. Choose the Budget Name and Planning Year.
4. Change the planning Factor to 110%.
5. Confirm by clicking Ok.

A new budget is immediatly created with all cells adjusted to increase the total revenue by 10%.

Forecasting

The standard forecasting scenario is to adjust budgets to changes within a fiscal year. In this scenario a budget with the KPI revenue was created at the start of the fiscal year. Within the fiscal year, Actual revenues become reality. The controller may now want to forecast how the remaining fiscal year's periods must be changed to still meet the initial budget's goals. To do so, they do the following:

1. Open the Create Rolling Forecast modal dialog.

2. Choose the moths that have passed already as Base Periods.

3. Choose the Plan from which the Base Periods are taken. In this example, it's the Actuals scenario of 2023.

   Figure 25:  Base section in the Create Rolling Forecast modal dialog

   

4. Repeat steps 2 and 3 for Budget Periods.

5. Activate the Fixed checkbox.

   Figure 26:  Budget section in the Create Rolling Forecast dialog box

   

When the controller presses the Save button, a new Plan is calculated. It contains:

• the Actuals data for the months from January to June.
• the budgeted data for the months from July to December adjusted such that the budget totals of it are still reached given the Actuals data.

Suppose a budgeted row had a total value of 120$, distributed equally across 12 months for 10$ per month. After half of the year, a controller has Actuals data for January-June. Contrary to the budgeted values, these months' values add up to 50$, falling short of the expected 60$ after half a year.

The Fixed forecast now combines the actuals months with the budgeted months and distributes the remainder of the expected values, that is, total - actuals = 120$-50$ = 70$, onto the remaining 6 budgeted months, that is July - December. Therefore, the entries for July-December now read 70$ / 6 = 11.66$ each. The controller now knows that in order to comply with the initial budget's total value, the values in the remaining months have to be increased by 1.66$ to still reach the year's original target value.

NULL Branch Handling For Plan Dimensions

The Plan extension shows a table in which its dimensions can be expanded and collapsed according to the user's chosen hierarchy. Datasets may be incomplete and show gaps such that there are null-values present for some dimensions.

Figure 27:  Null values

The Plan extension deals with this as follows: If the innermost dimension in the hierarchy has a null-value, its associated row is hidden. All other dimensions are expandable in the extension. Therefore, the null-values are replaced by the - symbol in the affected table row instead of hiding the full row. A typical use case for this logic is cost type/cost center planning.

Figure 28:  Plan table showing dimension hierarchy with null-values on some dimensions