# Introduction to ValQ ValQ is a lightweight, multi-purpose application running on Power BI that can support planning, budgeting, time series forecasting, value driver planning, reporting, and presentation. It helps enterprises run simulations, plans & analyses in a very visual and intuitive manner. It is designed keeping in mind the analysis & decision-making needs of business users & executives without any dependencies on IT. ### Use cases of ValQ * Business Modeling & Calculations * Planning * Budgeting * Forecasting * Financial Planning & Analysis * Time Series Forecasting * What-if Simulations * Scenario Comparison * Scenario Analysis * Variance Analysis * Value Driver Analysis * Executive Reporting ### How ValQ matches your role? ValQ is for everyone ranging from C-suite executives to analysts across departments and functions. How you use ValQ depends on your role in the team or project. Explore use cases that pertain to your role:


C-Suite			/files/LMOcpB0vWOlcgRmBsUl1
Decision Makers			/files/9QhoT0B8s9G5d7hOtc8C
Analysts			/files/Ncojtp24o5MLRxcUk1ZE

### Solutions by Functions ValQ supports use cases across various departments and functions such as * [Finance](https://valq.com/finance/) * [Sales & Marketing](https://valq.com/sales-marketing/) * [Supply Chain](https://valq.com/supply-chain-planning/) * [Procurement](https://valq.com/procurement/) * [Human Resources](https://valq.com/human-resources/) Click on the relevant functions above to know more. {% hint style="info" %} ValQ extends its functionality beyond financial & operations planning. {% endhint %} For example, 1. A customer service representative can leverage ValQ to simulate headcount needs based on estimated growth in call volume, upcoming product launches, marketing campaigns, and more.
2. A marketing manager can leverage ValQ to analyze web traffic, online assets, conversion rates, etc. to calculate marketing ROI. **ValQ Enterprise edition** has exclusive data persistence and security features ideal for Enterprise collaboration and Advanced analysis. To learn more, contact [support](https://valq.com/contact-us/). ### What do I need to run ValQ? {% hint style="info" %} All you need is Microsoft Power BI to run ValQ. {% endhint %} ValQ is an independent solution that does not require any additional infrastructure, database, service, or IT support (except for the ValQ On-Premises Plan).

You can download Microsoft Power BI desktop [here](https://powerbi.microsoft.com/en-us/downloads/). ### Sources ValQ can connect to ValQ connects to all databases, platforms, and other data sources supported by Power BI.

### Where can I learn more about ValQ? Go to the [Resources](/resources) section for more information on ValQ. # Get ValQ A step-by-step guide to get started on ValQ You can try ValQ for free or purchase licenses through the two ways listed below: 1. **Microsoft AppSource** - for seamless license procurement and administration using Microsoft's framework and for use in Power BI Desktop and Service. 2. **ValQ website** - for free trials without credit card, license purchase for use in Power BI Report Server, or for Embedded or OEM applications. ## 1. Microsoft AppSource Free trials are available through Microsoft AppSource *only for certain pricing tiers*. * To download a free version (only for Power BI Desktop), follow the steps [here](https://learn.microsoft.com/en-us/power-bi/developer/visuals/import-visual). * To purchase and manage licenses through Microsoft [AppSource](https://appsource.microsoft.com/marketplace/apps?page=1\&product=power-bi-visuals), read this [blog](https://powerbi.microsoft.com/en-au/blog/introducing-a-new-way-to-purchase-licensed-power-bi-visuals-and-manage-licenses-through-microsoft-platforms/). * For FAQs on Microsoft AppSource purchase, visit this [page](https://learn.microsoft.com/en-us/power-bi/developer/visuals/licensing-faq). ## 2. ValQ Website You can also register for a [trial](https://valq.com/trial/) through the ValQ website. ## a) Download ValQ If you have registered for a trial or [paid](https://valq.com/pricing/) subscription to ValQ via [Microsoft AppSource](https://appsource.microsoft.com/en-us/product/power-bi-visuals/valq.valqplan?tab=Overview), the visual is already embedded with a unique dynamically generated license key and you can start using ValQ right away. In case you have registered for a [trial](https://valq.com/trial/) via ValQ website, you will receive an email with a license key and a link containing the steps to download the visual from AppSource. ## b) Install ValQ There are 2 different methods to deploy ValQ in Power BI: #### 2.1. Install for yourself * You can use this option to install the visual on your own in Power BI desktop or service, without help from your IT or Power BI administrator. For instructions, visit this [page](https://valq.com/get-started/). #### 2.2. Install for your Organization (Organizational Visual) * Under this option, your Power BI Administrator uploads ValQ to the **Organizational Custom Visual (OCV).** * Once uploaded, the custom visual becomes available to all users within your organization who log on to Power BI. They need not have to individually install or update visuals. * Any future updates done to the visual – when uploaded to the repository by the administrator – will automatically update all the reports in your organization. For instructions, visit this [page](https://valq.com/get-started/). ## **Troubleshooting FAQ** #### 1. 'I haven't received the welcome email'. Make sure there are no spelling mistakes/typos in the mail ID used for the registration. Check your spam folder or whitelist . If that doesn’t work, reach out to your IT team and ask them to whitelist the domain at the firewall level. You will start receiving subsequent emails from us in your inbox. #### 2. 'When I click elsewhere, the license key entered disappears/license key does not get accepted' * Before adding the license key, you need to assign at least one data field to the visual in the Power BI Visualisations pane. This preserves the key entered in the License Key field. * Please ensure the license key entered is still valid and has not expired. #### If you still have difficulties, please contact us [here](https://valq.com/contact-us/) or through [mail](mailto:support@valq.com). If you have any other queries, reach out to us through [ValQ Community](https://community.valq.com/). Paid customers can also send an email to the ValQ support team (**mentioned in your purchase email**). In the next sections, we'll be looking at a step-by-step approach to [working with ValQ.](/introduction-to-valq/the-valq-workflow) # The ValQ Workflow The ValQ workflow consists of four steps - Model, Plan, Simulate and Report – all of which are available as individual tabs at the top.

Let us see an overview of these tabs:

	TAB	PURPOSE
1	Model	Create models based on your business logic by leveraging ValQ's model building capabilities, functions and templates.
2	Plan	Adopt intelligent planning grid to perform budgeting, allocations, rolling forecast and time-series forecasting.
3	Simulate	Perform on-the-fly simulations, conduct scenario analysis and comparisons in tree/ table interface and also present them.
4	Report	Perform in-depth variance analysis including Top N and generate templated reports.

{% hint style="info" %} In Power BI service, Model and Plan tabs can be accessed by changing the mode from Read-Only to **Edit** (Click Edit at the top of Power BI service). This helps in data integrity by preventing multiple users from altering the model and plan grid. Business users who require strict access permissions can use ValQ preferably in Power BI service than in a desktop version, so that they can make use of the default Power BI roles and access permission settings. Users can also use the **Enterprise** edition for better user and data security. {% endhint %} What follows is a preview of these workflow items and we'll explore them in detail later. {% hint style="info" %} *ValQ can be used with other native and custom Power BI visuals, but throughout this guide, we will be using ValQ images in full-screen mode for the best experience.* {% endhint %} ### Model The Model tab helps the analyst or the planner bring a data model to life and build complex visuals without writing a single line of code.

While modeling, the user can leverage the Fields, Visualizations & Filters pane in Power BI to connect to data and configure the visual. By choosing appropriate [formulas and functions](/model/formula-functions), a fully functional model can be built. Further, [templates](/model/templates) help in scaling the model with ease.

**Leveraging Model with Fields & Visualizations pane**

### Plan The Plan tab is used to create new plans and budgets for business cycles, perform top-down and bottom-up analyses using goal seek, apply different allocation methods, and perform time series forecasting.

### Simulate In the Simulate tab, you would be able to visualize the KPIs and identify their key drivers arranged in a tree-like fashion. From this tab, you can create new initiatives and scenarios, run simulations, and perform scenario comparisons. You can also present the results to your team using the **Presentation** option available.

### Report The Report tab allows the user to run advanced analysis such as variance analysis as well as download the results of the analysis as reports in a spreadsheet and PDF file.

# Model Build your business models with ease using ValQ's Model Tab Model tab is the first tab on ValQ where you can create the structure of your model and configure the node properties for your business-specific use case.

To understand the Model tab, its features have been explained in these major sections: 1. [Create Model](/model/create-model) 2. [Model Editor](/model/model-editor) 3. [Node Settings](/model/node-settings) 4. [Templates](/model/templates) 5. [Formula Functions](/model/formula-functions) 6. [Global Settings](/model/global-settings) Let us have an overview of each section. {% hint style="info" %} In the Power BI service, the model tab is available only in the **Editing view**. To view the model in the reading view, you can enable this option - '**Enable Model tab in the Reading view'** in the [**Settings**](https://docs.valq.com/pages/9dRQlKNML0z3zNWEfNgv#1.1.-about-model) menu. {% endhint %} ## 1. Create Model The first step is to choose how you would like to begin building the ValQ model. We'll look into the three ways to create a new model.

## 2. Model Editor Editing a model involves structuring the entire layout of the model and setting up each node using features such as formulas, templates, bulk edit, node settings, display properties, and more.

## 3. Node Settings Editing the model requires editing and configuring the node properties such as node name, type, configuration, simulation method, etc. - all of which are available in the [Node Settings](/model/node-settings) window.

## 4. Templates While building a model, you can make use of the Template feature whenever you need to create a repetitive node structure. Templates are discussed in detail in [this section](/model/templates). ## 5. Formula Functions In this section, you can find information about [Functions and Formula](/model/formula-functions) keywords. ## 6. Global Settings You might need to use the Settings menu on the toolbar for configuring the global level settings of the visual anytime during the ValQ workflow. This is explained separately in the section [Global Settings. ](/model/global-settings)

While we explore each of the above sections in the upcoming pages, let us first learn to get data into ValQ. # Get Data This guide is designed to provide you with the steps to get data inside ValQ with the help of an example - the Sales Performance Dataset (you can download the file below). If you have not installed ValQ already, see the installation guide [here](/introduction-to-valq/get-valq). You can also use your own datasets and follow the steps outlined. {% file src="/files/i1IEp7mDjmsgapflTtY5" %} {% hint style="info" %} Excel dataset is used here for the purpose of illustration only. ValQ supports all formats of databases, platforms and data sources supported by Power BI. {% endhint %} ## Step-by-Step Guide Let us learn how to get data inside ValQ to build models. 1. Add the visual 2. Import data from Excel 3. Assign your data fields to ValQ ### 1. Add the visual The first step to using ValQ is to add it to the Power BI canvas. Click on the ValQ icon in the visualization pane.

Resize it to fill the entire screen.

### 2. Import data from Excel We have a simple columnar dataset as shown below that measures sales performance across regions, and product categories & with respect to sales representatives.

This is a columnar dataset as the data is stored in columns instead of rows. Download the dataset using the above link and try it yourself. 1. To import this data into Power BI, go to **Get Data** in the toolbar. Now choose **Excel Workbook** to import the sales data. The process is the same as importing any data to Power BI.

{% hint style="info" %} ValQ consumes data the best when it is structured in **columnar** format. If the data is of any other structure such as a Crosstab, convert the data into columns at the source as a best practice. You can also use Power BI Query Editor to transform the data before loading it to ValQ. {% endhint %} 2. Go to the file location and select the file to be imported. Click **Open.**

**Select Excel File through System Dialog Box**

3. Select the *Sales Data(Columnar)* checkbox to preview the dataset and click **Load.** No transformation is required for this data as it is already prepared.

4. You will now see the dataset under the **Data** pane on the right.

### 3. Assign your data fields to ValQ Now that you have the data imported, the next step is to assign them to the appropriate fields. The available fields include **Category**, **Time Period**, **Values** and **Others.**

1. Assign *Category* and *Region* in **Category** field. 2. Then add *Month* in the **Time Period** field. {% hint style="info" %} The example showcased here has a hierarchical time format and hence time interval mapping is not required. ValQ accepts non-hierarchical time periods as well. Refer to [this section](https://docs.valq.com/model/pages/9dRQlKNML0z3zNWEfNgv#1.3.-time-interval-mapping) to know more. {% endhint %} 3. *Sales Budget* and *Sales Forecast* are added to the **Values** field. 4. You can also assign one of these values to the **Others** field. They would not become a part of the data series but can be used in formulae in the Model tab and for weight distributions in the Plan tab. For example, in the below image, the *Sum of Sales Budget* is assigned to the **Others** field instead of the **Values** field. This makes the series unavailable in the visual - in Plan, Simulate and Report tabs. However, in Model tab, you can refer to this series in formula calculations.

**Note:** 1. In the **Category** field, you can assign the category names, sub-categories and other heads under which you want the data to be categorized. 2. In the **Time Period** field, you can assign the time frame in which your data has been aggregated like Year, Quarter, Month, Week, Days, etc., Both hierarchical and non-hierarchical time formats are accepted. {% hint style="info" %} ValQ accepts non-hierarchical custom time formats like FY2023, 23, Qtr1, Jan, etc. and for the visual to consume data in such time formats use the **Time Interval Mapping** feature as explained in [this section](https://docs.valq.com/model/pages/9dRQlKNML0z3zNWEfNgv#1.3.-time-interval-mapping). You can also split the data series by year using the [Split Series by year](https://docs.valq.com/model/pages/9dRQlKNML0z3zNWEfNgv#1.2.1.-series-manager) option. {% endhint %} 3. All the numerical value columns that you want to aggregate can be assigned to the **Values** field. 4. Assign data in the **Others** field which you do not want to be added as an exclusive data series but want it to be still available for formula calculations and weight distributions. You have now imported your data to ValQ. Take some time to explore further. In the next section, let us learn the steps to create a model. **Resources** [**How to use Cross-Tab Data for ValQ Model in Microsoft Power BI**](https://valq.com/webinars/how-to-use-cross-tab-data-for-valq-model-in-microsoft-power-bi/) [**Create ValQ Model in Microsoft Power BI with 2 FACT Tables**](https://valq.com/webinars/create-valq-model-in-microsoft-power-bi-with-2-fact-tables/) **Note:** While the resources above are from previous versions of ValQ, the information available also applies to the current version of ValQ - Plan. ValQ packages a few inbuilt examples as samples. To explore more such models and use cases, visit [Demos](https://valq.com/demos). # Create Model In the launch screen of the visual, there are two options for you to get started with ValQ. -> If you are new and want to explore ValQ, Click **Select Sample** to explore the Sample models. -> If you are ready to build models using your own data set, click **Create Model.**

### Create New Model Upon clicking **Create Model**, you have three types of models to choose from and start modeling.

* [Dynamic Model](/model/create-model/dynamic-model) - ValQ automatically creates a model from the data assigned to it. * [Custom Model](/model/create-model/custom-model) - It is used when you want to build the model manually from scratch. * [Import a Model](/model/create-model/import-a-model) - This option can be used to import an existing model that is already built and exported. To learn about exporting a model, click [here.](https://docs.valq.com/model/pages/9dRQlKNML0z3zNWEfNgv#3.-export) Let us learn in detail about each of the above model types in the upcoming sections. # Dynamic Model A Dynamic Model gets built from the data source, instantly at the click of a button. The structure of the model is taken from the categories assigned to the visual and each level adds up one over the other. After importing the data using the steps explained in the [Get Data](/model/get-data) section, choose **Dynamic Model** and click **Create**.

{% hint style="info" %} **Dynamic Model** option gets enabled only after you have assigned Category and Values fields to the visual. {% endhint %} Dynamic model gets built instantly as shown below.

This type of model is termed dynamic as ValQ automatically updates your model whenever the categories are added, removed, or re-arranged. Since Dynamic models are flexible in nature they can be immensely useful for **scaling** your model. {% hint style="info" %} Refer to [this section](https://docs.valq.com/model/create-model/pages/ILpN3IAHcg3C6utwcg9N#3.-assign-your-data-fields-to-valq) for more help on assigning fields to the visual. {% endhint %} The nodes on a dynamic model, referred to as dynamic nodes, are locked/disabled by default **except for the root node.** Ideally, you can not configure, move, or remove them. ## Working with dynamic models A Dynamic model can be edited while keeping its dynamic nature in the following ways. ## **1. Add Nodes to Root Node** New nodes can be added to the root node in two ways. 1.1. Add nodes using Template 1.2. Add nodes using the 'Add New Node' option ### **1.1. Add Nodes Using Template** In a dynamic model, new nodes can be added to the root node using templates. Refer to [this section](/model/templates/apply-template#add-template-as-new-nodes) to read more about it. Find below the image of a dynamic model with a template added using **Add New Node-> Add Node using Template.**

**Dynamic Model with Added Template Nodes**

### **1.2.** Add Nodes using the 'Add New Node' option New child nodes can also be added to the root node using the other options available in the **Add New Node** menu. Check out the steps [here](https://docs.valq.com/model/create-model/pages/FS2Kz9WTA6yhUl2VCcA9#1.-add-node) to learn how to add new nodes to the model.

The below image shows a dynamic model with a new node added through **Add New Node-> Text.**

## 2. Replace/Insert Nodes with Template You can edit the dynamic model using templates by way of replacing the existing nodes or just inserting the template nodes within the model. Let us see both the ways below. ### **2.1. Replace nodes using templates** Replacing the existing dynamic node structure with template nodes is one of the ways to edit a dynamic model, without converting it. The template nodes are introduced in relation to the existing nodes while still keeping the entire model dynamic and locked for node-level edits. To learn about replacing dynamic nodes using templates, refer to [this section.](https://docs.valq.com/model/create-model/pages/PzgatPtXNNPVsb5orib0#1.-replace-dynamic-nodes-with-template) Click on **Templates->Apply Template->Replace Nodes with** **Template** to replace dynamic nodes with templates.

**Apply Template->Replace Nodes with template**

The below image shows a dynamic model where some nodes are replaced by template nodes as highlighted below.

**Dynamic Model replaced with template**

### 2.2. Insert nodes using templates Inserting template nodes at multiple levels within a dynamic model is another way to edit it. In this method, the existing dynamic nodes are not replaced but new nodes are inserted by way of templates wherever required. Refer to [this section](https://docs.valq.com/model/create-model/pages/PzgatPtXNNPVsb5orib0#2.-insert-custom-nodes-to-the-model) to know more. Click on **Templates->Apply Template->Insert New Nodes with** **Template** to insert nodes within the dynamic model.

**Apply Template->Insert New Nodes with Template**

In the following example, the template ‘*Pastries*’ is added to the dynamic model at each *Region* level.

**Dynamic model inserted with template**

**Note:** You can also click on the below icons to replace the nodes or insert them with template nodes.

## **Convert as Editable Nodes** In this case, the model ceases to be dynamic when you convert it to an editable one. Following are the steps to convert: In the Model Tab menu bar, choose **Convert Dynamic to Editable.**

Click **Yes** to confirm.

The model is now converted, enabling us to edit, configure, and customize the nodes as required.

It is recommended that the model be converted only when required and as the last option. **Note:** 1. The template structure within the model remains unalterable. To edit the template, you can use the **Templates** menu. 2. You can use the [Undo ](https://docs.valq.com/model/create-model/pages/GgVj5dX0kEHxZGXcbEbR#5.-undo-and-redo)

option to convert an editable model back to a dynamic model. {% hint style="info" %} While converting a dynamic model makes it static, it provides various options to customize the model according to your needs. {% endhint %} To learn how to configure the nodes, you can refer to the [Model Editor](/model/model-editor) section. # Custom Model Custom Model lets you create a model from scratch manually. Select **Custom Model** and click **Create.**

Once you click Create, a blank model is created where you can structure the model as per your requirements.

{% hint style="info" %} For guidance on building models, please refer to the [Model Editor](/model/model-editor) section. This section is further divided into two sections - [Node Operations](/model/model-editor/node-operations) and [Editor Features](/model/model-editor/editor-features) which can assist you in creating a custom model of your choice. {% endhint %} {% hint style="info" %} [Templates](/model/templates/apply-template#add-template-as-new-nodes) can also be used in a custom model where repetitive node structure is required. {% endhint %} # Import a Model Import Model is used to import a previously built ValQ model into the new visual. {% hint style="info" %} To learn how to export a model, refer [here.](https://docs.valq.com/model/create-model/pages/9dRQlKNML0z3zNWEfNgv#3.-export) {% endhint %} Select **Import Model** and click **Create.**

Select **Upload configuration file** in the pop-up screen.

Select the **.txt** file of the model you wish to Import and click **Open.**

Map the data fields as displayed in the visual and click **Done.**

The model is now imported to the ValQ visual.

Now that we have explored the different types of models that we can build using ValQ, let us have a closer look at how we can go about building them using the features available. # Model Editor This section details the concepts of building the node structure, editing the nodes, and making changes to the model. To go to the Model Tab, click **Model** on the top left corner of the visual.

This opens the model in the form of a grid where the nodes are listed in the model hierarchy along with their node properties. Find below the image of a custom model where the node structure can be customized and edited manually.

**A sample editable model with template**

Within this view, there are various editing options provided at the node level and the model level. They are explained in the sections [Node Operations](/model/model-editor/node-operations) and [Editor Features](/model/model-editor/editor-features). Let us start with the modeling features that are available with respect to nodes. # Node Operations In this section, we'll learn about the features that are specifically used for building the node structure. ## 1. Add Node New nodes can be added to the model, one node at a time or as multiple nodes at one instance. We'll first learn how to add a single node and then move on to adding multiple nodes. ### 1.1. Adding a Single Node Next to every node, you can find a few icons and menu options. **Add Child Node** and **Add Sibling Node** are the options that help in adding new child nodes or sibling nodes respectively. **Add Child Node**

is the first option next to the node name and **Add Sibling Node** ![](/files/L9CU6Uhr6sWWXbYWl4y6) is available on the three dots menu (click on the three dots to access the menu). Adding a sibling node adds a separate new node at the same level as the original node and not as a dependent.

{% hint style="info" %} **Note:** By default, new child nodes are added to the top node if the user does not select any node to add them to. {% endhint %} ### 1.2. Adding Multiple Nodes To add multiple new nodes to the model, click **Add New Node** on the toolbar. There are 4 different ways of adding multiple nodes - **Text, Data,** **Add Node using Template,** and **Constant Node.**

#### **Text** It is used to manually enter the node names that need to be added to the model. 1. Select the node to which you want to add new nodes. 2. Navigate to **Add New Node** **-> Text** to open the **Add Nodes by Name** pop-up. 3. In the text box, enter the name of the nodes you want to add.

4. To create a hierarchy, use the Tab key to insert child nodes. 5. After entering the node names choose whether you want to add them as a child or as a sibling. 6. Click **Add** to add the nodes to the model. #### **Data** This option is used when you want to create nodes that have data source values. These nodes are also referred to as dynamic children as the child nodes are created to the top node dynamically. 1. Select the node to which you want to add new nodes. 2. Navigate to **Add New Node->Data** to open the **Add Nodes from Data** pop-up. 3. A list of source keys is displayed in a hierarchy as per the categories assigned to the visual.

4. Select the source key which you want to add to the model. 5. The selected source key's data source values are displayed as **Preview** at the bottom for all the data series (see the dropdown list in the image). {% hint style="info" %} In case the selected source key has children, all the descendant source keys will also be added as nodes in the same hierarchy. {% endhint %} 6. After selecting the source key, you have the option to add the new nodes as a child or as a sibling. 7. Click **Apply** to add the nodes to the model. #### **Add Node using Template** Another way of adding new nodes is by using the template feature. After creating a template structure in the desired manner (Click [here](/model/templates#create-new-template) to learn about creating templates) you can add the template anywhere on the model. 1. Select the node to which you want to add new nodes. 2. Navigate to **Add New Node->Add Node using Template** to open the **Add Node from Template** pop-up. 3. In case there is no available template, you will be prompted to create a new template.

4. When there is an available template, the **Add Node from Template** pop-up is displayed. Select the template you want to add and the source key for which you want the template to fetch the values.

5. After selecting the source key, choose whether you want to add them as children or as siblings. 6. Click **Preview** to view the changes and then add the nodes to the model. {% hint style="info" %} Parent nodes of leaf nodes are only available for selection in the **Add Node from Template** dialog box while the leaf nodes become the template keys. {% endhint %} ### 1.3. Adding a Constant Node A constant node is a node that has a single value for its periods as well as the total. To add a constant node to your model, 1. Select the node to which you want to add a new constant node. 2. Navigate to **Add New Node->Constant Node** to open the **Add Constant Node** pop-up. 3. Enter the name of the constant node and choose whether you want it to be added as a child or a sibling node.

4. Click **Add**. 5. **Time aggregation** and **adding child nodes** are **not applicable** for constant nodes.

**Time aggregation and dependents - not applicable for Constant node**

{% hint style="info" %} The new node created through the above ways will copy all the properties except the Type and Configuration of the node to which it was added. {% endhint %} ## 2. Group Node A group node is a node that is used as a title or header inside the model and it doesn't hold any value. They are used to provide context and improve the design of the model. Group nodes can be used to group multiple nodes under a common label/ title. To add a Group node, click the 3 dots beside the node name and select **Add Group**.

A Group node is created and named as *New Group(1)* by default*.* You can rename the group node name accordingly. You can now add new child nodes under this group node as explained [here](#1.-add-node) or [move ](#5.-move-node)the existing nodes within this group. In the below image, you can see that the group node is renamed to *Income Statement* and the 'Net Income' node is moved within that group.

In the Simulate tab, it is shown with the group node icon as below.

## 3. Duplicate Node Hover over the node that you want to duplicate and select **Duplicate** from the options under the 3 dots. This will duplicate all the properties of the selected node including the Type and configuration of the node.

## 4. Rename Node To rename, double-click on the node you want to rename and enter the new name.

## 5. Move Node To relocate a node to a different place in the model, hover over the node you want to move and choose **Move** **To** from the 3 dots menu.

From the pop-up, choose the node under which you want to move the node and click **OK**.

The node is now moved to the selected node as its first child. ## 6. Edit Node Editing a node consists of updating any of the node's settings. To edit a node, click the pencil icon next to the node name or select the node and click **Edit Node** on the toolbar. Each of the node properties is explained in detail in the section [Node Settings.](/model/node-settings)

## 7. Re-order Node The position of the nodes can be re-ordered through the drag-and-drop feature. The re-order icon

before the node name can be used to re-order the child nodes within a parent or move them to another parent. Hover over the node and click on the icon. Now drag it to the desired position and drop it.

{% hint style="info" %} When you drag & drop a parent node, all the descendants of it will also be moved to the new location. {% endhint %} ## 8. Bulk Editor Bulk Select is a model editing tool that enables us to perform an action on multiple nodes in one go. The actions that can be performed under Bulk Select are editing node settings, and moving and deleting nodes. To select the nodes that need to be edited, click **Bulk Select** on the toolbar and then select all the nodes that need to be edited.

Another way to select nodes would be from the dropdown on the checkbox in the header. The dropdown has options such as All Nodes, Leaf Nodes and Filtered Nodes.

**Edit node settings:** Select the **Bulk Editor** button from the black color menu at the bottom of the screen.

This will open the list of properties that can be updated. Make the necessary changes in the popup and click **Apply** to update all the selected nodes.

**Move nodes:** Select the **Move To** button from the black color menu at the bottom of the screen.

This will open the node selection popup where you can select the node under which you want to move the selected nodes and click **OK**.

**Delete nodes:** Select the **Delete** button from the black color menu at the bottom of the screen to delete all the selected nodes.

## 9. Delete Node Click the Bin icon

next to the node name to delete a node and its dependents.

# Editor Features The model tab has some essential features on the toolbar that help in the model-building activity. They are detailed in this section. ### 1. Expand or Collapse The arrow icons

available at the left of each node name can be used to expand/collapse the parent nodes. To expand/collapse all levels, click on the burger icon

to the left of the **Node Name** column header. This can be used to show/hide the node hierarchy of the model. **Expand All** option expands the entire node structure till the last level of the model while the **Collapse All** option displays only the nodes at the first level.

### 2. Manage Columns **Manage Columns** option allows you to decide which columns to be displayed on the grid. There are 9 columns displayed by default, which are Type, Configuration, Time Aggregation, Desired Trend, Scale, Decimals, Prefix, Suffix and Simulation Method. To modify the grid view, select the **Manage Columns** button on the toolbar. In the pop-up window, you can select and deselect the columns as required.

### 3. Search Search for any node on the model by clicking the **Search** icon

. After clicking, enter the text using which you want to search the nodes. The search result nodes are highlighted and the results dropdown displays the number of results along with the arrows with which you can navigate to the highlighted nodes.

### 4. Filter The **Filter** icon

**Disable All** can be used to toggle between the states of filter and no filter. **Clear All** will delete all the applied filters. ### 5. Undo and Redo Undo or redo the recent changes you have made to the model by clicking

and

respectively. Undo is enabled after you start making changes to the model. Redo is enabled after the first undo. ### 6. Notes Click **Notes** to add any notes or view saved notes about the model.

In the notes window, select the **Edit** button to type your notes and click **Save**.

### 7. Settings The settings menu contains the global-level options that are used to configure the visual as required. These options are explained in detail in the [Settings](/model/global-settings) section.

### 8. Keyboard Shortcuts Grid interactions can also be performed using keyboard shortcuts which are listed under the help icon. Click

and the keyboard shortcuts window opens as shown below.

### 9. Bottom Panel The bottom panel of the model tab provides necessary information about the model such as node count, preview of the node value, errors on the model, analysis of the model, formula editor and the model name. Click this icon

to expand or collapse the bottom panel.

The first one is the **Preview** section which displays the total and period values of the node selected by the user.

The second one is the **Errors** section which shows the count of errors in the model if any. Selecting this icon will list all the error nodes along with the error description. The errors are highlighted on the grid and you can navigate to the error node by clicking on the node name from the error section. In the below image, you can see the errors and their description at the bottom of the screen in the **Errors** section. The total number of errors is also displayed at the top.

The **Analysis** window assesses the model's complexity by analyzing its configuration - type, configuration, dependencies, etc. and shows the complexity index for all the nodes.

**Formula Editor** window allows the user to view, enter, or edit the formula contained in the formula type nodes. When you make any changes to the formula, the **Apply** button gets enabled which can be clicked to apply the new formula.

At the bottom left of the panel, you can find the **Total** node count with hidden node count (if any) under the info icon. In case a filter is applied on the grid, the filtered node count is displayed along with the total node count and hidden node count under the info icon.

**Filtered, total and hidden node count**

The last item on the right side of the status bar is the **Model name with the logo**. Both the model name and the logo can be updated in the [Settings](https://docs.valq.com/model/model-editor/pages/9dRQlKNML0z3zNWEfNgv#1.1.-about-model). Alternatively, the model name can be renamed by double-clicking on it.

# Node Settings In this section, we will learn about each of the node properties in detail. {% hint style="info" %} The node properties can be configured in the grid view as well as in the node settings panel. {% endhint %} In the grid view, click on the pencil icon next to the node name or click the **Edit Node** option from the toolbar to open the **Node Settings** window.

This window has 4 tabs as listed below. 1. [General](#1.-general) 2. [Format](#2.-format) 3. [Simulate](#3.-simulate) 4. [Display](#4.-display) ## 1. General The General tab consists of some essential node properties such as Node Name, Type, Configuration, Time Aggregation, Desired Trend and Description.

### **1.1. Node name** Here, you can edit the name of the node. ### **1.2. Type** The Type property is used to assign the nature of the node which helps in setting up the configuration. There are four node types to choose from.

Node type	Description
Formula	ValQ computes and executes the user-defined formula on the node.
Data Source	The node gets the values directly from the source data.
Linked to Node	The node value, its properties and simulation are linked to another node.
Manual Input	The user can enter the values manually for this type of node.

### **1.3. Configuration** Based on the Type selection, the configuration can be filled in as required by the user. #### Formula If the node is of formula type, the configuration can be filled in as a formula. You can manually type the formula or choose the required formula from the suggestions pop-up that appears as you start typing.

**Note:** Use Ctrl+Space to open all suggestions. For more help on the usage of different formulas and functions, you can refer to [this section](/model/formula-functions). #### Data Source In this case, the node fetches the values from the source data based on the source key selected by the user.

#### **Dynamic Children From Data Source** This setting is used to instantly add all the descendant nodes from data, for Data Source nodes based on the selected source key. The nodes are identified from the Category Fields that are assigned to the visual.

**Add dynamic children from data source**

#### Linked Node If the node type is ‘linked node’, then a node selection pop-up appears where you can choose a specific node to connect to.

{% hint style="info" %} Nodes that are linked share the same node value, properties, configuration, and simulation so that they mirror each other. Nodes are linked when the same node is required in many instances in the model. {% endhint %} #### **Manual Input** For manual input-type nodes, users are allowed to fill in the node values manually.

#### **Hybrid Configuration** In cases where you want to specify different node configurations for the open and the closed periods, you can enable hybrid configuration of nodes. Open period values refer to the plan/forecast series and the closed period values refer to the actuals. Using this option, you can configure different formulas, data sources, or manual values for the actuals and the plan/forecast. {% hint style="info" %} Hybrid configuration can be done only in editable models. For dynamic models, convert it to an editable one before configuring it for the open period. For a model that contains templates, use the **Templates** window to include open-period configuration for the **template nodes**. Templates are covered in detail in the [next section](/model/templates). {% endhint %} To enable open period node configuration, toggle the button below in the [Model Settings](/model/global-settings#model-settings) window.

The model looks as shown with an option to configure the nodes for the open period.

Convert the model to an editable model to start open period configuration.

The image below shows an example where the *Expense* node has two different configurations for the closed and open periods - formula type and manual input type respectively.

The node settings window also appears as shown below where you can configure the node for both open and closed periods.

The following example illustrates how the hybrid node configurations defined in the model are applied to the plan for the *Expense* node.

### **1.4. Time Aggregation** This property determines how the total node value needs to be calculated from its period values. The following are the different time aggregations available to the user.

Time Aggregation	Description
Sum	Adds all the period values
Average	Calculates the average of the period values
Average Excluding Zeros	Calculates the average of the non-zero period values
Formula	Calculates and applies the custom formula entered by the user
Last	Only displays the last period value of the node
Running Total	Cumulative total of the period values of the node
Weighted Average	Calculates the weighted average of the period values based on the nodes from which the weights are taken.

### **1.5. Desired Trend** The trend of the node can be set to *Increase* or *Decrease* depending on the context of the node. For instance, the Desired Trend for the Revenue node is Increase whereas for the Expense node, it is Decrease. ### **1.6. Description** Any text describing the node or technical notes can be entered in the Description box.

The node description is also displayed in the respective node details screen on the Simulate tab.

**Node Description in Node Scenario Analysis screen**

## 2. Format The Format tab contains properties to edit the number formatting and conditional formatting of a node.

### 2.1. Number Formatting Under Number Formatting you can configure the scale, number of decimal places to display, prefix and suffix. {% hint style="info" %} The *User Selected* option under **Scale** means that the scaling of the node is dynamic and gets changed when the user changes it on the Scaling Setting in the toolbar from any of the tabs. {% endhint %} ### **2.2. Conditional Formatting** Conditional Formatting can be used to configure the rules for the color formatting applied to the nodes on the Simulate tab. It can be applied based on the variance or the node value.

**Conditional Formatting on nodes in Tree view**

**Conditional Formatting on nodes in Table view**

## 3. Simulate In the Simulate tab, you can configure simulation settings such as the Simulation Method and setting the slider's direction.

### **3.1. Simulation Method** There are five methods by which simulation can be performed. 1. Change % - Percentage change applied to the baseline value. 2. Growth % - Percentage growth applied to the baseline value. 3. Constant - Constant value applied for all the simulation periods. 4. None - Disable simulation for the node. 5. Linked Simulation - Links the node's simulation to the selected node simulation (mirrors the simulation on it). {% hint style="info" %} The simulation is always applied to the period values and the total node value is calculated depending on the node's **Time Aggregation** set by the user. {% endhint %} Here are the examples showing the node values before and after simulation under each method:

**Slide Right to:** To increase the node value on sliding right, set the Slide Right to property as *Positive Simulation* and to decrease the value set it as *Negative Simulation*. **Note:** Simulation for dynamic children can be enabled or disabled at the top node in a dynamic model.

When the simulation is disabled, all data source nodes beneath the top node can not be simulated, as indicated by the simulation-disabled icon on the nodes.

**Simulation disabled for dynamic children**

You can toggle the same option to enable simulation - for all dynamic nodes or only the leaf nodes. The desired simulation method can also be selected for these nodes.

## 4. Display The Display tab is used to configure node settings such as node display, show as KPI or Key Input, setting up constraints, adding composite nodes and customizing secondary KPI.

{% hint style="info" %} The display settings configured here (except the Node Display option) apply to the Simulate tab, i.e. any changes made here would reflect on the Simulate tab. {% endhint %} ### **4.1. Node Display** This property determines whether the node should appear on the model and how it should appear. It changes the node's visibility across the other tabs, while the nodes are always available in the Model tab for configuration. The Node Display dropdown consists of the following three options: 1. Show - Displays the node as a normal node (Default). 2. Derived - Displays the node as being derived from its parent. It is denoted by a dotted line and transparent node tile in the Simulate tab - tree view. 3. Hidden - The node is hidden from the visual based on the tabs chosen, but it is available for calculation and other references in the Model tab. If a parent node is hidden all the descendants under it also get hidden. ### 4.1.1. Hide in Tabs This dropdown menu appears when you choose the **Hidden** option in the Node Display to hide a node. You can select the tabs where you want the node to be hidden.

{% hint style="info" %} Node Display of the root node can never be set as *Derived* or *Hidden*. {% endhint %} ### **4.2. Show as Key Input and Show as KPI** To add/show the node as a key input in the Key Inputs section or as a KPI in the KPI ribbon on the Simulate tab, enable any of these toggle buttons.

### **4.3. Constraint** Constraint can be added to a node by way of assigning minimum and maximum threshold values a node can have, against which you can evaluate the model's performance. To set a constraint for a node, go to the Constraint section in the Display tab. Then enter the minimum and maximum values permissible for that node. In the below example, the constraint for *Cost of Sales* is set with the minimum and maximum values as 2700 and 4000 respectively.

The values entered are for each period and hence the minimum and maximum limits for that node are computed as the product of the value entered and the number of periods. As there are 12 periods available here, 12 \* 2700 and 12 \* 4000 are the limits. Therefore, the minimum and maximum threshold values would be 32400 and 48000 respectively. If the cost of sales exceeds $48000 or falls below $32400 in any scenario during simulation, a warning is displayed to the user about the constraint along with the difference in value by which the simulated result exceeds or falls below the threshold levels.

Click on the warning symbol to view all the constraints violated in the model.

### **4.4. Composite nodes** When there is a large number of nodes in the model, and you want to take a quick look at another node that is important along with the current node you can use the Composite Nodes option. This option helps you to include up to two nodes within the node's tile that are important or relevant to it. To add a composite node, click **Select a node**, choose the node you want to add and click **OK.**

In the Simulate tab, it appears as shown below. *Revenue* node is added to *US* node so that you can have a quick look at Revenue and also simulate it.

{% hint style="info" %} The composite nodes option is for the visual arrangement of nodes and does not impact their relationship or the dependency of nodes. {% endhint %} ### **4.5. Secondary KPI** Secondary KPI helps you to insert additional values to the node tile. Under this property, you can input up to 2 customized secondary KPI values to the node tile. In the below example, Net Profit Margin is to be added as a secondary KPI to all *Net Profit* nodes. Since these are template nodes, go to **Templates** Sections and click on the **Edit Node** option for the *Net Profit* node in the template available. Go to **Secondary KPI** in the **Display** section and click on the pencil icon. Enter the formula/function you want as the Secondary KPI, enter a label for it and click **OK**.

# Templates Template refers to a common layout of nodes that can be used to create a model that has a repetitive pattern of node structure. The template is created once as per the user's needs and can be reproduced any number of times within the model to build it. The below example shows how a template named **Net Profit** is created once and has been reused for different product categories.

Click the **Templates** menu in the toolbar to enter the template section. There are two options available to you: 1\. [Create your own template](#create-new-template) 2\. [Import an existing template](/model/templates/import-template)

Let us first see how to create a new template. ## Create New Template After clicking the **Create New Template** option from the pop-up, the editor screen is displayed to start building your template. An editor screen just like the model tab editor is available to you to create your template structure. Creating a template is similar to creating a new model, more precisely, a subset of the model where all the modeling features can be used such as [adding new nodes](https://docs.valq.com/model/pages/FS2Kz9WTA6yhUl2VCcA9#id-1.-add-node), [editing the nodes](https://docs.valq.com/model/pages/FS2Kz9WTA6yhUl2VCcA9#id-6.-edit-node), [moving](https://docs.valq.com/model/pages/FS2Kz9WTA6yhUl2VCcA9#id-5.-move-node) and [re-ordering](https://docs.valq.com/model/pages/FS2Kz9WTA6yhUl2VCcA9#id-7.-re-order-node), [hybrid formula configuration](/model/node-settings#hybrid-configuration), etc. Find below the image of a template being created just like building a model. You can refer to [this ](/model/model-editor/node-operations)section for more help on using the modeling features.

**Template being created in Template Editor**

Click on the vertical ellipsis next to the template name at the bottom to duplicate or delete it.

Click on the + icon as shown below to create and/or import further templates.

{% hint style="info" %} Nodes in the template can be of any type - manual input/data source/formula type. {% endhint %} ## Template Keys A unique feature in the template editor is a special type of node called a **Template key node.** Template keys are a list of keywords taken from the leaf nodes that are used in creating Data Source nodes in the model.

While creating the template structure, you can choose the required template keys for the driver nodes that need to get values from the data source. For example, if you want the 'selling price' and the 'units sold' as the driver nodes to be picked from the data source, you can choose them as template keys from the leaf level (*GL Account* Category).

**Choosing template keys from leaf nodes**

Upon creating and applying the template to the model, the template key is paired with the source key to identify its lineage, and the template key nodes are displayed as Data Source type nodes in the model.

**template keys pairs with the source key at each 'Product' level**

**Template keys as Data Source Nodes in the model**

## How do Template Keys work? **Step 1:** Add template keys in the template. You can choose any one of the three ways listed below to add template key(s).

1. Click on the

icon beside the node name under which you want to add the template keys as child nodes. 2. Select the node and click **Add Template Key Node** on the toolbar. 3. Create a node and choose its type as *Template* *Key* in the **Type** dropdown. **Step 2:** Configure Template keys. ValQ allows you to configure the template keys by letting you choose the last level nodes (leaf nodes) of the model from the data source. These leaf nodes are automatically presented in the pop-up as shown. {% hint style="info" %} If you have not assigned any data to the visual, you will be prompted to do so in the **Add Template Key Nodes** pop-up as '*Add Category and Values fields to the visual'*. {% endhint %} Select the relevant leaf nodes from the list.

**Step 3:** Select whether you want it to be a child node or a sibling node and then click **Add** to apply. Template keys are now configured and the template is ready. Find below the *Net Profit* template that has been created using the above steps.

{% hint style="info" %} The leaf nodes belong to the last category assigned to the visual for models having uniform structure. In the case of a ragged hierarchy, the template keys are leaf nodes belonging to different levels. {% endhint %} **Step 4:** Apply template. Now, while applying the template, these template keys fetch the relevant leaf node data for some or all of the categories based on the source key(lineage of the node it replaces). In the below image, template keys such as *Selling Price, Units Sold, Other Income*, etc. fetch the relevant leaf nodes from the product categories - Men, Women, and Orange. On applying the template, the *Selling Price, Units Sold,* and *Other Income* nodes become the respective Data Source nodes of the model.

## FAQs on templates #### **1. Data source nodes also fetch data from the data source. What is the difference between template keys and data source nodes?** The template key only contains the leaf part of the source key (the last level in the lineage) while Data Source node is created with the complete source key (the entire lineage).

Hence DataSource nodes refer to static nodes (E.g. *US/Clothing/Men/Units Sold*), while template keys (E.g. *Units Sold*) can refer to and fetch any leaf node depending on the lineage **dynamically**. As the template key is only a part of the source key it can be used to create many nodes across the model that fetches its respective values based on the lineage. This dynamic nature of the template key helps in scaling the model whenever new data is added. #### **2. Are template keys mandatory to create templates?** Templates can be created without template keys too. For example, in use cases where you would want to insert or replace nodes with a repetitive set of numerical/calculation nodes at different levels, you can create nodes that are of manual input type/data source type/formula type. In the below example, a template has been created with only the formula nodes to calculate variance.

#### 3. How does template help in scaling the model? By creating a dynamic model, whenever the data set is updated with new data such as a new product or division, the new data is automatically added as nodes to the model, and template structure is applied to these nodes. #### **4.** Can the template structure be modified after applying it to the model? Yes, to modify a template structure go to the **Templates** section, and select the respective template tab to edit the nodes. -> To know how to apply the created templates, go to the [next section](/model/templates/apply-template). -> To understand other template actions like - modifying the template application rules or copying the rule to other dynamic nodes, or removing the template from the model, refer to [this section](/model/templates/manage-template-nodes). -> Import and Exporting templates are explained in the further sections. # Apply Template Let's understand how to apply the created template in dynamic and custom models with the help of examples. ## Dynamic model When a [dynamic model](/glossary) or [dynamic children](/glossary) are created, all the nodes (except the top node) are disabled from editing and they simply just add up over each level. Now, with the help of a template, you can **modify** the existing node structure while retaining its dynamic nature. There are two ways by which you can modify the model with templates: * By replacing the existing node structure (or) * By inserting new nodes within the structure To do this, click the **Apply Template** button from the **Templates** window.

It has two options - [**Replace Nodes with Template**](#1.-replace-dynamic-nodes-with-template) and [**Insert New Nodes with Template**](#2.-insert-custom-nodes-into-the-model). ### **1. Replace Dynamic Nodes with Template** In this case, the template is applied to multiple nodes wherever required to replace their original structure with the template structure. Let us see the steps to replace the dynamic nodes using template. 1. Create the template structure as needed. 2. Click **Apply Template-> Replace Nodes with Template** button.

**Choose 'Replace Nodes with Template'**

3. Datasource Node Editor opens in which you can configure the template rules.

**a) Select Datasource Node:** Select the datasource node under which you want to apply the template. **b) Select Level:** Select the level at which you want to replace the existing nodes with the template. In the below image, parent of the leaf nodes are chosen to be replaced with template.

{% hint style="info" %} ValQ automatically detects which level is to be replaced by the template. Only the last two levels in the model (leaf nodes and parent of leaf nodes) are available for replacing nodes with template. If leaf nodes are chosen as template keys, then select the level that is the parent of leaf nodes. {% endhint %} **Template Rules** **c) Select Template:** This displays the current template. You can also choose any other template you had created, from the dropdown (if any) to apply that. **d) Apply to:** In this section, choose **All Nodes** to apply the template to all the nodes at the selected level (or) you can choose **Filter** to apply the template to only a few specific nodes at the level based on certain filter conditions.

**Replace all nodes at the 'Product' level**

**e)** **Append Template Root Node Name:** Checking the **Append Template Root Node Name** option appends the template name with the name of the root node. E.g. '*Men'* node is renamed to *'Men - Net Profit'* if the 'Net Profit' template **replaces** the *Men* node structure. 4. Click **Preview** to view the changes and then click **Save** to apply the template.

**Preview of the template to be applied**

### Example 1: Replace with a template structure In the below example which has a dynamic model, we'll see how net profit is calculated for the regions - Asia and US. **Step 1:** Find below the example of a dynamic model. Click **Templates**.

**Step 2:** The template is created first with the desired structure and configuration along with the template key nodes as shown below.

**Step 3:** Click **Apply Template->Replace Nodes with Template.**

**Step 4:** The level and the template are selected. Then the template is applied to replace **all the nodes** at the ***Region*** level. Click **Preview**.

**Step 5:** After previewing, click **Save** to apply the template.

Template is applied to the dynamic model as shown below. Nodes that belong to a particular template share the same color scheme.

**After applying 'Net Profit' template**

This way each region's profit is calculated and since the model is still dynamic any new region that gets introduced will automatically take the shape of the template structure as we have applied the template to all nodes at the *Region* level. ### Example 2: Replace with more than one template structure In this example, two templates have been applied to the dynamic model at the *Company* level. The nodes *Company A* and *Company B* have been replaced with *Template (1)* and *Company C* and *Company D* with *Template (2)*. This is achieved by using the **Add Rule** and then the **Filter** option in the **Apply to** section where two rules are added, one for each template.

**Template(1) for Company A and Company B**

**Template(2) for Company C and Company D**

Click **Preview** to verify and then click **Save**.

The templated nodes in the model are color-coded to identify the different templates that have been applied.

### **2. Insert new nodes into the model** The other way of applying a template is to **insert the template nodes as new nodes** at the selected levels in a dynamic structure. The new nodes can be added as children of **all** the parent nodes in a level or **only to a few** parent nodes in a level using the **Filter** option. Let us see the steps to insert the template in the model as custom new nodes. 1. Create the template structure as needed. 2. Click **Apply Template->Insert New Nodes with Template** button.

**Choose 'Insert New Nodes with Template'**

3. Datasource Node Editor opens in which you can configure the template rules. **a) Select Datasource Node:** Select the Data Source node under which you want to apply the template.

**Insert template after the 'Region' level**

**Template Rules** **b) Select Template:** This displays the current template. You can also choose any other template you had created, from the dropdown (if any) to apply that. **c) Parent Level:** Select the parent level under which you want to insert the template nodes as children. {% hint style="info" %} Unlike the Replace option, which only allows you to replace leaf nodes and parent of leaf nodes, inserting can happen at any category level. {% endhint %} **d) Apply to:** In this section, choose **All Nodes** to insert the template under all the parent nodes at the selected level or use **Filter** to insert under only specific parent nodes at the selected level based on filter conditions. ### Example 1: Insert new nodes for all parents under any level: In the below example, we have created a template structure for *Net Profit with Discount%* and applied it using the **Insert with template** option for **All Nodes** under **Product** level.

This will add the template as additional nodes under every ***Product*** parent (every *Men* and *Women*).

### **Example 2: Insert nodes under a 'selective' parent based on filter conditions** In this example, *Template (1)* has been created with few formula nodes to calculate the variance.

This will be applied only for *Supplier A* in *India* region for *Apple* product. These specific categories are filtered using the **Filter** option in Template Rules as shown in the image below.

**Apply to Selective parent using Filter**

{% hint style="info" %} **And** is the operator type used in the example to combine the filtering rules which means **all** the categories matching the entered text should be filtered. {% endhint %} On applying this template to the model, the variance template structure gets added as a descendant of the filtered parent node (*Apple* node belonging to *Supplier A* of *India*).

**template structure gets added as a descendant of the filtered parent node**

{% hint style="info" %} To apply the template, the Data source node editor can also be opened by clicking on the below icons. {% endhint %}

**Click to open Data source Node Editor**

## Custom Model ### Add Template as New Nodes In a custom model (editable model) we can add new nodes in the form of template nodes just like adding any other node in the model. Here **the template is added for a single node at a time**. Let us see the steps now. 1. Create the template structure as needed. 2. In the model, select the node where you want to add the template. 3. Click **Add New Node -> Add Node using Template.**

4. Choose the template you want to add and select the source key to fetch the values for template key nodes.

5. Clicking on **Select source key** opens a pop-up where you can select the source key for template.

6. Choose the source key from the list and the position of the template to be added as a child or sibling of the selected node.

7. Click **Preview** to view the changes and then click **Apply** to apply the template.

The nodes are added as template as shown in the below image.

{% hint style="info" %} Note: **Add node using template** is also available for dynamic model and dynamic children. However, using this option will add the template outside of dynamic node structure as a new branch to the model. {% endhint %} # Manage Template Nodes After applying the template, the template nodes can be edited, copied, or deleted by clicking on the **Manage Template Nodes** icon

as shown below.

The 'Manage Template Nodes' pop-up shows all the templated nodes used in the model, including their name, whether they have replaced the nodes or were added as new nodes, and a list of actions to select from. The number on the icon indicates the total number of templates applied in the model. In the example below, there are two templates applied to the model - one to replace the nodes and one to add new nodes.

#### Note: Another way to open the datasource node editor and edit the templates is to use the shortcut icon near the node name.

**Click To Open the DataSource Node Editor**

The following actions are possible through the DataSource Node Editor window. ### Add/Edit/Delete Template Rules 1. Add a new template to the model by clicking **Add Rule** to configure that template into the model.

2. Edit the applied templates. * You can change the template using the 'Select Template' dropdown. * To edit the level at which the template is applied, click on the 'Select Level' dropdown. * Templates can be applied to all the nodes or specific nodes using the filter.

3. Re-apply to different nodes using filter conditions. * Edit the Category and Member fields to change the filter conditions.

* Add more than one filter condition and combine them using AND/OR operators.

4. You can delete a specific template by clicking on **Delete** beside that template rule.

{% hint style="info" %} In case of multiple template rules applied to the model, if they overlap, the latest added rule overrides the previous rules. {% endhint %} ### Remove Template from the model To remove the template completely and revert the model to its initial state, click on the bin icon that appears next to the applied template.

{% hint style="info" %} This option removes **only the template references from the model** and does not delete the actual template that had been created. The template would still be available in the **Templates** window. Alternatively, to delete the actual template, it is mandatory to remove its references from the model first, by using [this option](#remove-template-from-the-model). {% endhint %} ### Copy Template Rules to Dynamic Children In case of [dynamic children](/model/model-editor/node-operations#data) being added to the model (adding new nodes from data), you can apply the existing template rules to them. Click on the **Copy rule** icon beside the template that you want to copy.

In the pop-up that opens, choose the dynamic nodes to which the existing template rules are to be applied. Click **Apply.**

This applies the templates to the newly added dynamic child nodes. You can also click on the 'data source node editor' icon beside the node to configure a template for it manually.

# Import Template If you want to apply an already created template into your model, choose **Templates->Import Template** in the pop-up menu.

Selecting this option will open the System Dialog box where you can choose the template you want to import. # Export Template Previously created templates can be exported to your local system so that they can be used for the future by importing them back to the model. To export your template, click **Templates** and then select **Export Template.**

Click **Yes** in the confirmation pop-up to download it locally. # Formula Functions ValQ supports many traditional user-friendly functions that allow you to create complex models. You can use these functions under **Configuration** in the Model Tab while writing formulae for the **Formula** type nodes to build/edit the models. A formula can contain functions, expressions, references and constants. 1. Function takes in one or more values and performs a defined operation to return a result. Every function is defined with a keyword and a specific syntax that is to be followed. 2. Expressions are node names with logical or mathematical operators that specify the operation to be performed. 3. References are the node names that refer to the nodes from the model and contain those values. 4. Constant refers to a value or a number that does not change. Let us first see the logical, mathematical and other operators. ## Logical Operators

OPERATORS	DESCRIPTION
&&	Logical AND
\|\|	Logical OR

## Mathematical and Other Operators

OPERATORS	DESCRIPTION
()	Parenthesis
+, -	plus, minus(-1) sign
*, /	Multiplication, Division
+, -	Addition, Subtraction
==, !=	Equal to, Not equal to
>, >=	Greater than, Greater than or equal to
<, <=	Less than, Less than or equal to

In the upcoming sections, we'll explore the different functions along with relevant examples for a better understanding. {% hint style="info" %} **Note:** 1. The arguments with a ? in the function syntax are optional arguments based on the use-case. When there is no optional argument, a function performs according to its default settings. 2. In function syntax, **- > number** refers to the numerical result of the performed function. 3. **valueN?** refers to the unlimited 'N' number of arguments that a function can accept. 4. Node references have been enclosed within square brackets, and each node is shown in a different color scheme for easy comprehension. e.g. *Sales* node is represented as \[Sales] and so on. In ValQ, along with colour scheme, the complete lineage of nodes are displayed when you choose them from the pop-up. 5. Text in blue represents the keywords in formula functions of ValQ. {% endhint %} # Logical Functions

NAME	SYNTAX	DESCRIPTION
IF	IF(logical_test, value_if_true, value_if_false)	Checks whether a condition is met and returns the first value if TRUE and second value if FALSE.
SWITCH	SWITCH(expression, value1, result1, value2, result2, ...,value_n, result_n, default?)	Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value is returned.
AND	AND(logical_test1,...logical_test2?)	Checks whether all the arguments are TRUE, and returns TRUE only if all the arguments are TRUE
OR	OR(logical_test1,...logical_test2?)	Checks whether any of the arguments are TRUE, and returns TRUE or FALSE. Returns FALSE only if all the arguments are FALSE
NOT	NOT(logical_test)	Reverses FALSE to TRUE, or TRUE to FALSE
XOR	XOR(logical_test1,...logical_test2?)	Returns a logical 'Exclusive Or' of all the arguments
IFNAN	IFNAN(value1,value2?:number) - > number	Returns a default value if the value in the node is an error

# IF An IF statement returns a value depending on whether the condition that is being evaluated is TRUE or FALSE. ### Syntax IF(logical\_test, value\_if\_true, value\_if\_false) ### Arguments

Arguments	Data Type	Condition
logical_test	Text / Number	The condition to be evaluated.
value_if_true	Node reference / Number	The value to be returned if the condition is met.
value_if_false	Node reference / Number	The value to be returned if the condition is not met.

### Return Value Either *value\_if\_true* or *value\_if\_false.* ### Remarks 1. Returns Blank when the formula is misspelled. 2. The specified node should contain Numerical data. 3. The IF function returns error in any of the following cases: * *value\_if\_false* argument is missing. * The argument *value\_if\_true* or *value\_if\_false* are not of numeric data type. * Not using appropriate operator in *logical\_test*. ### Example Consider the **Revenue** and **Cost of Goods Sold** nodes to follow the trend as shown below.

Parameter	Jan	Feb	Mar	Apr	May	Jun
Revenue	100	124	137	0	23	45
Cost of Goods Sold	25	25	25	25	25	25

Now, a new node **Gross Profit Margin** can be computed using the IF function. Gross Profit Margin is calculated as (Revenue-Cost of Goods Sold)/Revenue. In case Revenue has period value of 0, Gross Profit Margin should return 0. To achieve this, the following formula should be written in the \[Gross Profit Margin] node: **`IF`****`(`****`[Revenue]`****`==0,0%,(`****`[Revenue]`****`-`****`[Cost of Goods Sold]`****`)/`****`[Revenue]`****`)`** ### Result

Parameter	Jan	Feb	Mar	Apr	May	Jun
Gross Profit Margin	75%	80%	82%	0%	-9%	44%

As you can see from the above table, if the logical test condition (Revenue==0) is True, it returns 0, i.e. the gross profit margin is 0 and if the logical test condition is False (Revenue is a non-zero number), it calculates the Gross Profit Margin from the formula and returns it. {% hint style="info" %} Use **SWITCH** function instead of nesting multiple IF functions. {% endhint %} # SWITCH A SWITCH statement evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value is returned. ### Syntax SWITCH(expression, value1, result1, value2, result2, ..., value\_n, result\_n, default?) ### Arguments

Arguments	Datatype	Description
expression	Text, Number, Node reference	The statement to be evaluated.
value1, value2, … value_n	Number	The list of values to be compared against the expression.
result1, result2, … result_n	Number	The result to be returned if the expression matches with the corresponding value in the argument list.
default?	Number	The default value to be returned if the expression does not match with any of the values in the argument list. This is an optional argument.

### Return Value Result when the expression matches with value, default in case there was no match. ### Remarks All result expressions and the default expression must be of the same data type. ### Examples 1. In the below given table, discount rate is 20% for every month. Consider a scenario in which the node Discount Rate is to be calculated based on the current month. Months Jan, Aug, Nov and Dec should have 15% as discount rate whereas the remaining months should have 20%.

To achieve this, the following formula should be written in the \[Discount Rate] node: **`SWITCH`****`(`****`CURRENT_PERIOD_INDEX`****`,1,0.15,8,0.15,11,0.15,12,0.15,0.20)`** The result of the above formula is shown below:

In the above example, if the current period's index value is 1,8,11 and 12 (which is the case for Jan, Aug, Nov and Dec periods), their corresponding matching result of 15% is returned. For other periods, the default discount rate of 20% is returned. 2. Consider a scenario where Year 1's estimated unit sales value and Year 5's target operating income are given. You need to calculate the unit sales for further periods. Since we have different calculations for each period we can use the SWITCH function to specify the calculation for each period. | | | | ------------------------------------------------------------------- | --------- | | Target Operating income (year 5) | $1,00,000 | | Year 1 estimated unit sales | 100 |

Period	1	2	3	4	5
Growth%	N/A	10.0%	50.0%	75.0%	100.0%
Unit price	400.00	420.00	441.00	463.05	486.20
Unit cost	250.00	262.50	275.63	289.41	303.88
Fixed cost	250000	257500	265225	273181.8	281377.2

This can be achieved by using the SWITCH function in the following manner: **`SWITCH`****`(`****`CPI`****`1,100,2,`****`THIS`****`.`****`PERIOD_LOOKUP`****`(3,100)`****`[Growth%]`****`,3,`****`THIS`****`.`****`PERIOD_LOOKUP`****`(2,100)`****`[Growth%]`****`,4,`****`THIS`****`.`****`PERIOD_LOOKUP`****`(1,100)*`****`[Growth%]`****`,5,(`****`[Fixed cost]`****`+`` `****`[Target Operating income]`****` ``)/(`****`[Unit price]`****`-`` `****`[Unit cost]`****`))`** The result of the above formula is shown below: | Period | 1 | 2 | 3 | 4 | 5 | | :--------: | :-: | :-: | :--: | :--: | :--: | | Unit Sales | 100 | 209 | 1045 | 1568 | 2091 | {% hint style="info" %} Refer [THIS](/model/formula-functions/static-identifiers/this-me), [CPI ](/model/formula-functions/static-identifiers/current_period_index)and [PERIOD\_LOOKUP](/model/formula-functions/range-functions/period_lookup) functions to understand why and how they are used in the above formula. {% endhint %} ### Explanation For period 1 the value is 100, for period 5 the unit sales is calculated as **(fixed cost + targeted operating income)/(Unit price - Unit Cost)**. Periods 2, 3 & 4 are calculated as the product of 5th period value and growth percentage. For this [period lookup function](/model/formula-functions/range-functions/period_lookup) is used. # AND An AND function returns TRUE only if all the conditions are TRUE and returns FALSE if one or more conditions are FALSE. ### Syntax AND(logical\_test1, ...logical\_test2?) ### Arguments

Arguments	Data type	Description
logical_test1	Number,text	The first condition to be evaluated. This is a required argument.
logical_test2?	Number,text	The additional conditions to be evaluated. These are optional arguments.

### Return Value Either True or False values that are given in the arguments. ### Remarks AND function accepts multiple conditions. ### Example Consider the below scenario in which, if the number of licenses are greater than 500 **and** usage period is lesser than 24 months, the price should be $20 else $25.

Particulars	Jan	Feb	Mar	Apr	May	Jun
No of Licenses	498	497	502	505	502	498
Duration	25	21	22	26	23	25

To achieve this, the following formula should be written in the \[Price] node: **`IF`****`(`****`AND`****`(`****`[No of Licenses]`****`>500,`****`[Duration]`****`<24),20,25)`** ### Result

Particulars	Jan	Feb	Mar	Apr	May	Jun
Price	25	25	20	25	20	25

As you can see, the price is 20 when both the conditions (No of licenses>500 and Duration<24) are true (in Mar and May). Even if one of the conditions is false, as seen for the other periods, the price is 25. # OR An OR statement returns TRUE if at least one of the conditions is TRUE and returns FALSE when all the conditions evaluate to FALSE. ### Syntax OR(logical\_test1, ...logical\_test2?) ### Arguments

Arguments	Datatype	Descriptions
logical_test1	Node reference,Number	The first condition to be evaluated. This is a required argument.
logical_test2?	Node reference,Number	The additional conditions to be evaluated. These are optional arguments.

### Return Value Either True or False values that are given in the arguments. ### Remarks OR function accepts multiple conditions. ### Example Consider the below scenario in which if the Sales Quantity is greater than 50 **or** the usage Customer Ranking is 1, the price should be $20 else $25.

Periods	Jan	Feb	Mar	Apr	May	Jun
Sales Quantity	50	55	45	65	25	31
Customer Ranking	1	1	2	2	1	2

To achieve this, the following formula should be written in the \[Price] node: **`IF`****`(`****`OR`****`(`****`[Sales Quantity]`****`>50,`****`[Customer Ranking]`****`==1),20,25)`** ### Result

Parameter	Jan	Feb	Mar	Apr	May	Jun
Price	20	20	25	20	20	25

As you can see from the above table, the result will be 20 even if one of the conditions is true and it will be 25 only when both of the conditions are false. In Feb, both of the conditions are True & in Jan, Apr and May one of the conditions is true. So the result is 20. But in Mar both the conditions are false, so the result is 25. # NOT A NOT statement reverses the value of the condition. For example, it returns TRUE if the condition is FALSE and FALSE if the condition is TRUE. ### Syntax NOT(logical\_test) ### Arguments

Argument	Data type	Description
logical_test	Node reference, Formula	The condition to be evaluated

### Return Value Either True or False. ### Example Consider the below scenario in which an employee is eligible for Sales Bonus if Total Sales is greater than the Sales Goal. Sales Bonus is calculated as the product of Bonus% and Total Sales.

To achieve this, the following formula should be written on the \[Sales Bonus] node: **`IF`****`(`****`NOT`****`(`****`[Total Sales]`****`>`****`[Sales Goal]`****`),0,`****`[Bonus %]`****`*`****`[Total Sales]`****`)`** ### Result Here, the condition Total Sales > Sales Goal is TRUE for Jan, but NOT function negates it and returns FALSE. Hence the argument value for FALSE condition which is "Bonus% \* Total Sales" is calculated. For all TRUE condition, its matching argument 0 is the result.

# XOR A XOR statement returns a logical 'Exclusive Or' of all arguments. XOR gives TRUE only when the conditions return different truth values.

Arguments	Result
1 or more test conditions return different results (TRUE and FALSE)	TRUE
All the conditions are TRUE	FALSE
All the conditions are FALSE	FALSE

### Syntax XOR(logical\_test1, ...logical\_test2?) ### Arguments

Arguments	Data type	Description
logical_test1	Node reference,number	The first condition to be evaluated. This is a required argument.
logical_test2?	Node reference,number	The additional conditions to be evaluated. These are optional arguments.

### Return Value Either True or False. ### Remarks **OR** : If at least 1 of the conditions is TRUE, the result is TRUE. **XOR** : If only 1 of the conditions is TRUE, the result is TRUE but if both the conditions are TRUE the result is FALSE. XOR returns TRUE only if the conditions give different results, if both give the same results, XOR returns a FALSE. ### Example Consider the below scenario, where the price should be 20, if either Sales Quantity is greater than 50 or Customer Ranking = 1, but not both. It should be 25 otherwise.

To achieve this, the following formula should be written on the \[Price] node: **`IF`****`(`****`XOR`****`(`****`[Sales Quantity]`****`>50,`****`[Customer Ranking]`****`==1),20,25)`** ### **Result**

As you can see from the above table, the result will be 20 when either of the conditions is true and it will be 25 when both the conditions are true/false. In Feb, both the conditions are TRUE so the result is False and the corresponding value for FALSE is returned which is 25. In Mar, both the conditions are FALSE so again the result in 25. Whereas in Jan, only 1 of the arguments is TRUE so the value will be 20. # IFNAN The IFNAN function checks for error and returns the default value if the value in the node is an error. ### Syntax IFNAN(value1, value2?: number) - > number ### Arguments

Arguments	Data type	Description
value1	Number,Node reference	The node that is to be checked for error values.
value2?: number	Number,Node reference	The value that should replace the errors. The value should be of numerical datatype only.

### Return Value value2 in the place of Error. ### Example Consider the nodes **Total Cost** and **No. of units** to have the periods values as shown below:

Now, Cost per unit is calculated by dividing the Total Cost by No. of units. If IFNAN is not applied, the result would be as follows due to 'divide by zero' error:

The error is to be replaced with 0. To achieve this, the following formula should be written in the \[Cost Per Unit] node: **`IFNAN`****`(`****`[Total Cost]`****`/`****`[No. of units]`****`,0)`** ### **Result**

# Math Functions

NAME	SYNTAX	DESCRIPTION
SUM	SUM(value1,… valueN?) - > number	Adds all the numbers in the arguments, which can be numbers or node references
SUBTRACT	SUBTRACT(value1,… valueN?) - > number	Subtracts all the values in the arguments, which can be numbers or node references
PRODUCT	PRODUCT(value1,… valueN?) - > number	Multiplies all the values in the arguments
DIVISION	DIVISION(value1,… valueN?) - > number	Divides all the values in the arguments
AVERAGE	AVERAGE(value1,...valueN?) - > number	Returns the average (arithmetic mean) of its arguments, which can be numbers or node references
AVERAGEEXZERO	AVERAGEEXZERO(value1,...valueN?) - > number	Returns the average (arithmetic mean) of its arguments excluding zeros, which can be numbers or node references
AVERAGEEXNEG	AVERAGEEXNEG(value1,...valueN?) - > number	Returns the average (arithmetic mean) of its arguments excluding negatives, which can be numbers or node references
AVERAGEEXZERONEG	AVERAGEEXZERONEG (value1,...valueN?) - > number	Returns the average (arithmetic mean) of its arguments excluding zeros and negatives, which can be numbers or node references
COUNT	COUNT(value1,...valueN?) - > number	Counts the number of items in a range
ABS	ABS(value) - > number	Returns the absolute value of a number, a number without its sign
MIN	MIN(value1,...valueN?) - > number	Returns the smallest number in a set of values. Node references are also accepted as arguments
MAX	MAX(value1,...valueN?) - > number	Returns the largest number in a set of values. Node references are also accepted as arguments
POWER	POW(value,power) - > number	Returns the result of a number raised to a power
SQRT	SQRT(value) - > number	Returns the square root of a number
EXP	EXP(value) - > number	Returns e raised to the power of a given number
LOG	LOG(value,base) - > number	Returns the logarithm of a number to the base you specify
ROUND	ROUND(value) - > number	Returns the rounded value of the specified node, number
FLOOR	FLOOR(value) - > number	Returns the rounded down value of the specified node, number
CEILING	CEIL(value) - > number	Returns the rounded up value of the specified node, number

# SUM The SUM function returns the sum of all numbers in the arguments, which can be numbers or node references. ### Syntax SUM(value1, ...valueN?) - > number ### Arguments

Arguments	Datatype	Description
value1	Number/Node reference	The first node or number to be added. This is a required argument.
...valueN?	Number/Node reference	The additional nodes or numbers to be added. These are optional arguments.

### Example Consider sales of a particular product across different regions East, South, Central and West to be as follows. The total sales is to be calculated.

To achieve this, the following formula should be written in the \[Total Sales] node: **`SUM`****`(`****`[East],`****`[South]`****`,`****`[Central]`****`,`****`[West]`****`)`** ### **Result**

# SUBTRACT The SUBTRACT function returns the difference of all values in the arguments. ### Syntax SUBTRACT(value1, ...valueN?) - > number ### Arguments

Arguments	Datatype	Description
value1	Number/Node reference	The first node or number to be subtracted. This is a required argument.
...valueN?	Number/Node reference	The additional nodes or numbers to be subtracted. These are optional arguments.

### Example Consider the below example where we have Revenue and expenses data. The objective is to calculate the profit.

Periods	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Revenue	300	450	367	408	832	671	700	260	572	903	534	671
Sales	180	200	238	174	286	319	265	136	220	260	300	315
Other revenue	120	250	129	234	546	352	435	124	352	643	234	356
Expenses	170	173	360	264	174	184	582	273	503	296	295	484
Rent	50	50	50	50	50	50	50	50	50	50	50	50
Other expenses	120	123	310	214	124	134	532	223	453	246	245	434

To achieve this, the following formula should be written in the \[Profit] node. **`SUBTRACT`****`(`****`[Revenue]`****`,`****`[Expenses]`****`)`** ### **Result**

Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13
Profit	130	277	7	144	658	487	118	-13	69	607	239	187

# PRODUCT The PRODUCT function returns the product of all values in the arguments. ### Syntax PRODUCT(value1, ...valueN?) - > number ### Arguments

Arguments	Datatype	Description
value1	Number/Node reference	The first node or number to be multiplied. This is a required argument.
...valueN?	Number/Node reference	The additional nodes or numbers to be multiplied. These are optional arguments.

### Example Consider the below example where we have data for Units sold and price. The objective is to calculate Sales value by multiplying Units sold with Unit price.

Periods	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Units sold	2000	2351	978	1520	2415	3154	1548	3144	2458	3654	1452	2548
Unit price	6	11	6	7	8	11	7	10	6	5	9	12

To achieve this, the following formula should be written in the \[Sales] node. **`PRODUCT`****`(`****`[Units sold]`****`,`****`[Unit price]`****`)`** ### **Result**

Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13
Sales	12000	25861	5868	10640	19320	34694	10836	31440	14748	18270	13068	30576

# DIVISION The DIVISION function returns the division of all numbers in the arguments, which can be numbers or node references. ### Syntax DIVISION(value1, ...valueN?) - > number ### Arguments

Arguments	Datatype	Description
value1	Number/Node reference	The first node or number to be divided. This is a required argument.
...valueN?	Number/Node reference	The additional nodes or numbers to be divided. These are optional arguments.

### Example Consider the below example where Revenue and Cost of Sales Is given. Calculate Gross Profit%.

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Revenue	31400	32600	35100	32600	31900	33700	35000	31700	33600	34100	32500	33000
Cost of Sales	24300	25300	25800	23300	27300	28400	21200	26900	25700	24200	28900	27300

To achieve this, the following formula should be written in the \[Gross Profit %] node. **`DIVISION`****`((`****`[Revenue]`****`-`****`[Cost of Sales]`****`),`****`[Revenue]`****`)`** {% hint style="info" %} To show the values in percentage, scale should be set in Percentages. {% endhint %} ### **Result**

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Gross Profit %	22.61%	22.39%	26.50%	28.53%	14.42%	15.73%	39.43%	15.14%	23.51%	29.03%	11.08%	17.27%

# AVERAGE The AVERAGE function returns the average (arithmetic mean) of its arguments, which can be numbers or node references. ### Syntax AVERAGE(value1, ...valueN?) - > number ### Arguments

Arguments	Data type	Description
value1	Node reference, number	The first node or number to be averaged. This is a required argument.
...valueN?	Node reference, number	The additional nodes or numbers to be averaged. These are optional arguments.

### Example Consider the below example in which the sales of a particular product across different regions East, South, Central and West are as follows. The average sales is to be calculated.

To achieve this, the following formula should be written in the \[Average Sales] node: **`AVERAGE`****`(`****`[East]`****`,`****`[South]`****`,`****`[Central]`****`,`****`[West]`****`)`** ### **Result**

# AVERAGEEXZERO The AVERAGEEXZERO function returns the average (arithmetic mean) of its arguments excluding zeros, which can be numbers or node references. ### Syntax AVERAGEEXZERO(value1, ...valueN?) - > number ### Arguments

Arguments	Datatype	Description
value1	Number/Node reference	The first node or number to be averaged. This is a required argument.
...valueN?	Number/Node reference	The additional nodes or numbers to be averaged. These are optional arguments.

### Example Consider the sales of a particular product across different regions East, South, Central and West to be as follows. Assume if sales for a particular period is zero, the product was withdrawn from the market and hence that region need not be considered when calculating the average sales.

To achieve this, the following formula should be written in the \[Average Sales] node: **`AVERAGEEXZERO`****`(`****`[East]`****`,`****`[South]`****`,`****`[Central]`****`,`****`[West]`****`)`** ### Result

# AVERAGEEXNEG The AVERAGEEXNEG function returns the average (arithmetic mean) of its arguments excluding negatives, which can be numbers or node references. ### Syntax AVERAGEEXNEG(value1, ...valueN?) - > number ### Arguments

Arguments	Datatype	Description
value1	Number/Node reference	The first node or number to be averaged. This is a required argument.
...valueN?	Number/Node reference	The additional nodes or numbers to be averaged. These are optional arguments.

### Example Consider Profit of a particular product across different regions East, South, Central and West to be as follows. Assume if Profit for a particular period is negative, the product was withdrawn from the market and hence that region need not be considered for calculating average Profit.

To achieve this, the following formula should be written in the \[Average Sales] node: **`AVERAGEEXNEG`****`(`****`[East]`****`,`****`[South]`****`,`****`[Central]`****`,`****`[West]`****`)`** ### Result

# AVERAGEEXZERONEG The AVERAGEEXZERONEG function returns the average (arithmetic mean) of its arguments excluding both zeros and negatives, which can be numbers or node references. ### Syntax AVERAGEEXZERONEG(value1, ...valueN?) - > number ### Arguments

Arguments	Datatype	Description
value1	Number/Node reference	The first node or number to be averaged. This is a required argument.
...valueN?	Number/Node reference	The additional nodes or numbers to be averaged. These are optional arguments.

### Example Consider the Profit of a particular product across different regions East, South, Central and West to be as follows. Assume that if Profit for a particular period is negative or zero, the product was withdrawn from the market and hence that region need not be considered when calculating average Profit.

To achieve this, the following formula should be written in the \[Average Sales] node: **`AVERAGEEXZERONEG`****`(`****`[East]`****`,`****`[South]`****`,`****`[Central]`****`,`****`[West]`****`)`** ### Result

# COUNT The COUNT function counts the number of items in a range. ### Syntax COUNT(value1, ...valueN?) - > number ### Arguments

Arguments	Datatype	Description
value1	Node reference	The first node or number to be counted. This is a required argument.
...valueN?	Node reference	The additional nodes or numbers to be counted. These are optional arguments.

### Example Consider a scenario where you want to calculate the number of periods for which the simulations are performed for a \[Sales] node. That is if simulation period is between July and September, the result should be 3. | Sim.Months | July | August | September | | ---------- | ---- | ------ | --------- | In order to calculate the number of periods, the following formula should be written in the \[COUNT] node: **`COUNT`****` ``(`****`[Sales]`****`.SIM_PERIODS`****`)`** {% hint style="info" %} Refer[ \[NODE\]**.SIM\_PERIODS**](https://docs.valq.com/model/formula-functions/math-functions/pages/bd9R41FrVkmuCJPGQLmc#node-.sim_periods) to know more. {% endhint %} ### Result | COUNT | 3 | | :---: | :-: | # ABS The ABS function returns the absolute value of a number (i.e) a number without its sign. ### Syntax ABS(value) - > number ### Arguments

Arguments	Datatype	Description
value	Number/Node reference	The node or the number for which you want the absolute of.

### Example In the below table, data of demand and inventory are given. Consider a scenario where you want to calculate the absolute difference between demand and inventory.

Parameter	Week 1	Week 2	Week 3	Week 4	Week 5
Demand	110	95	75	78	0
Inventory	85	70	100	80	25

To achieve this, the following formula should be written in the \[Absolute Difference] node: **`ABS`****`(`****`[Demand]`****`-`****`[Inventory]`****`)`** ### Result


Absolute Difference	25	25	25	2	25

#### Note If ABS is not used the difference between values will be as follows:


Absolute Difference	25	25	-25	-2	-25

# MIN The MIN function returns the smallest number in a set of values. Node references are also accepted as arguments. ### Syntax MIN(value1, ...valueN?) - > number ### Arguments

Arguments	Datatype	Description
value1	Number, Node reference	The nodes or numbers for which you want to get the smallest value.
valueN?	Number, Node reference	Additional nodes or numbers for which you want to get smallest value. This is an optional argument.

### Example Consider a node which has the demand for a particular product for 12 months.

To calculate the minimum demand, the formula should be written in the \[Minimum Demand] node **`MIN`****`(`****`[Demand]`****`.`****`ALL_PERIODS`****`)`** We are including [**.ALL\_PERIODS**](https://docs.valq.com/model/formula-functions/math-functions/pages/bd9R41FrVkmuCJPGQLmc#node-.all_periods) so that it will consider all the period values rather than one single period. ### **Result** | **Minimum Demand** | 136 | | ------------------ | --- | # MAX The MAX function returns the largest number in a set of values. Node references are also accepted as arguments. ### Syntax MAX(value1, ...valueN?) - > number ### Arguments

Arguments	Data type	Description
value1	Number, Node reference	The nodes or numbers for which you want to evaluate the largest value.
...valueN?	Number, Node Reference	Additional nodes or numbers for which you want to evaluate the largest value. This is an optional argument.

### Example Consider the below example with a node that has the demand for a particular product for 12 months.

To calculate the maximum demand, the following formula should be written in the \[Maximum demand] node: **`MAX`****`(`****`[Demand]`****`.`****`ALL_PERIODS`****`)`** We are including [**.ALL\_PERIODS**](https://docs.valq.com/model/formula-functions/math-functions/pages/bd9R41FrVkmuCJPGQLmc#node-.all_periods) so that it will consider all the period values rather than one single period. ### Result | **Maximum Demand** | 319 | | ------------------ | --- | # POWER The POWER function returns the result of a number raised to a power. ### Syntax POW(value, power) - > number ### Arguments

Arguments	Datatype	Description
value	Number, Node reference	The base number, it can be any real number or node.
power	Number	The exponent to which the value is raised.

### Example Consider a below example with $16,000 loan at an annual rate of 5% that is to be paid off in 6 years. Calculate Compound Interest.

Items	Values
Principal	16000
Rate	0.05
Time	6
Monthly Compound	12

To calculate the Compound Interest, the formula should be written in the \[Compound Interest] node. **`[Principal]`****`(`****`POWER`****`(1+(`****`[Rate]`****`/`****`[Monthly Compound]`****`),(`****`[Time]`****`*`****`[Monthly Compound]`****`)))`** ### Result | **Compound Interest** | 21584.28391 | | --------------------- | ----------- | # SQRT The SQRT function returns the square root of a number. ### Syntax SQRT(value) - > number ### Arguments

Arguments	Datatype	Description
value	Number, Node reference	The number or node for which you want the square root of.

### Example Consider an example where a node has the following values. Calculate the square root of these Values.

Column1	Column2	Column3	Column4	Column5	Column6
Number	25	81	169	289	49

To calculate the Square Root, the following formula should be written in the \[Square root] node: **`SQRT`****`(`****`[Number]`****`)`** ### Result | Square root | 5 | 9 | 13 | 17 | 7 | | ----------- | :-: | :-: | :-: | :-: | :-: | # EXP The EXP function returns e raised to the power of a given number. This is the reverse of ln, the natural logarithm of number. ### Syntax EXP(value) - > number ### Arguments

Arguments	Datatype	Description
value	Number, Node reference	The exponent applied to the base e. This is a required argument.

### Example Consider a scenario where we have the 2020 population figures of 5 major cities. Our goal is to estimate the population in the year 2030 assuming an average growth rate of 2%.


City	New York	London	Hongkong	Toronto	Los Angeles
2020 population	8804190	9002488	7960000	3998747	6197000
Growth rate	0.02	0.02	0.02	0.02	0.02
Duration	10	10	10	10	10

To achieve this, the following formula should be written in the \[2030 population] node: **`[2020 population]*`****`EXP`*****`([`*****`Growth rate]*[Duration])`** ### Result | 2030 population | | --------------- | | 10753461.95 | | 10995663.67 | | 9722365.955 | | 4884080.615 | | 7569032.892 | # LOG The LOG function returns the logarithm of a number to the base you specify. ### Syntax LOG(value, base) - > number ### Arguments

Arguments	Datatype	Description
value	Number, Node reference	The positive real number for which you want to derive the logarithm.
base	Number, Node reference	The base of the logarithm.

### Example Consider the below scenario where we have values and base for four periods. The objective is to derive the LOG of those values.

Period	1	2	3	4
value	30	40	35	50
base	5	4	3	6

To achieve this, the following formula should be written in the \[LOG] node: **`LOG`****`(`****`[value]`****` ``,`` `****`[base]`****`)`** ### Result | LOG | 2.113282753 | 2.660964 | 3.2362173 | 2.1833416 | | --- | ----------- | -------- | --------- | --------- | # ROUND The ROUND function calculates rounded value of the specified number or node. ### Syntax ROUND(value) - > number ### Arguments

Arguments	Datatype	Description
value	Number/Node reference	This is the number or node values which you want to round off. This is a required argument.

### Remarks If the decimal value is less than 5, it will round down and if it is 5 or more it will round up. ### Example Consider this example.

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Sales	69700.64	72700	76500.93	70200.69	71500.72	83900.54	70000.721	69500.43	74200.94	67700.38	78450.82	68950.43
Cost of Sales	43400.36	44300.68	46700.93	45300.25	43600.54	46900.39	44200.452	42800.81	45900.18	43800.06	47200.67	41000.32

To achieve this, the following formula should be written in the \[Gross Profit] node **`ROUND`****`(`****`SUBTRACT`****`(`****`[Sales]`****`,`****`[Cost of Sales]`****`))`** ### **Result**

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Gross Profit	26300	28399	29800	24900	27900	37000	25800	26700	28301	23900	31250	27950

# FLOOR The FLOOR function returns the rounded down value of the specified number or node. ### Syntax FLOOR(value) - > number ### Arguments

Arguments	Datatype	Description
value	Number/Node reference	The value or the node name that has the values to be rounded down.

### Example In the below example Sales target to achieve is 2200 and employee sales are given. Employees will receive 2% incentive of sale price for sales made more than the target. The sales price of a product is 1254. We need to calculate the incentive received by employee.

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Sales Target	2200	2200	2200	2200	2200	2200	2200	2200	2200	2200	2200	2200
Employee Sales	1850	2134	2489	1783	1965	2498	1934	2356	2793	3120	1897	2185

To achieve this, the following formula should be written in the \[Incentive] node **`FLOOR`****`(`****`IF`****`((`****`[Employee Sales]`****`>`****`[Sales Target]`****`),(`****`[Employee Sales]`****`-`****`[Sales Target]`****`),0) * 1254 * 0.02)`** ### **Result**

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Incentive	0	0	7248	0	0	7473	0	3912	14872	23073	0	0

### Remarks Without using FLOOR, the result would be:

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Incentive	0	0	7248.12	0	0	7473.84	0	3912.48	14872.44	23073.6	0	0

# CEILING The CEILING function returns the rounded up value of the specified number or node. ### Syntax CEIL(value) - > number ### Arguments

Arguments	Datatype	Description
value	Number/Node reference	The value or the node name that has the values to be rounded up.

### Example Consider the below example where we have raw material consumption data. The objective is to find out the number of boxes of raw material that we have consumed.

Periods	Jan	Feb	Mar
Raw material available per box	2	2	2
Raw material used per box	1.25	1.25	1.25
Demand	20	25	30

To achieve this, the following formula should be written in the \[No. of raw material boxes used] node. **`CEIL`****`((`****`PRODUCT`****`(`****`[Demand]`****`,`****`[Raw material used per box]`****`))/`****`[Raw material available per box]`****`)`** ### **Result** | Periods | Jan | Feb | Mar | | ------------------------------ | ----- | ----- | ----- | | No. of raw material boxes used | 13.00 | 16.00 | 19.00 | ### Remarks Without using CEIL, the results would be: | Periods | Jan | Feb | Mar | | ------------------------------ | ----- | ------ | ----- | | No. of raw material boxes used | 12.50 | 15.625 | 18.75 | # Finance Functions

NAME	SYNTAX	DESCRIPTION
IRR	IRR (Values, Guess?) - > number	Returns the internal rate of return for a series of cash flows. Node references are also accepted as arguments
NPV	NPV (Rate, value1, valueN?) - > number	Returns the net present value of an investment based on a discount rate and a series of future payments (negative values) and income (positive values)
PMT	PMT(Rate, Nper, PV, FV?, Type?) - > number	Calculates the payment for a loan based on constant payments and a constant interest rate
IPMT	IPMT (Rate, Per, Nper, PV, FV?,Type?) - > number	Returns the interest payment for a given period for an investment, based on periodic, constant payments and a constant interest rate
FV	FV(Rate, Nper,Pmt?, PV,Type?) - > number	Returns the future value of an investment based on periodic, constant payments and a constant interest rate
PV	PV(Rate,Nper,Pmt?,FV,Type?) - > number	Returns the present value of an investment
CUMIPMT	CUMIPMT (Rate, Nper, PV, start_period, end_period, Type) - > number	Returns the cumulative interest paid between two periods
CUMPRINC	CUMPRINC (Rate, Nper, PV, start_period, end_period, Type) - > number	Returns the cumulative principal paid on a loan between two periods
RATE	RATE(Nper, PMT, PV?,FV?,Type?,Guess?) - > number	Returns the interest rate per period of a loan or an investment.

# IRR The IRR function returns the internal rate of return for a series of positive and negative cash flows. Node references are also accepted as arguments. ### Syntax IRR (values,Guess?) - > Number ### Arguments

Arguments	Datatype	Description
values	Number, node reference	The node that contains the cash outflow and inflows for which you want to calculate the IRR. This is a required argument.
Guess?	Number	A value that you guess is closest to the actual IRR value. This is an optional argument.

### Remarks The argument should contain at least one positive and one negative value. ### Example Consider the below example of a project with an initial investment of $10,000 and cash inflows of $4,000 for the subsequent four years. The objective is to evaluate the project by calculating IRR.

Periods	0	1	2	3	4
Initial Investment	-10000	-	-	-	-
Cash Inflows	-	4000	4000	4000	4000

To achieve this, the following formula should be written in the \[IRR] node: **`IRR`****`(`****`[Initial Investment]`****`,`****`[Cash Inflows]`****`.`****`ALL_PERIODS`****`)`** We are including[ .ALL\_PERIODS](https://docs.valq.com/model/formula-functions/finance-functions/pages/bd9R41FrVkmuCJPGQLmc#node-.all_periods) in the formula so that it takes the value of the entire year's cash inflow rather a single period. ### Result

Column1	Column2
IRR	21.86%

# NPV The NPV function returns the net present value of an investment based on a discount rate and a series of future payments (negative values) and income (positive values). ### Syntax NPV (Rate, value1,...valueN?) - > Number ### Arguments

Arguments	Datatype	Description
Rate	Number	The rate of discount
value1	Number, Node reference	The node that contains the order of cash flows for which you want to calculate the NPV. This is a required argument.
valueN?	Number, Node reference	The additional nodes that contains the order of cash flows for which you want to calculate the NPV. This is an optional argument

### Example Consider the below example of a project with an initial investment of $10,000 and cash inflows of $4,000 for the subsequent four years. Discount rate is 12%. The objective is to evaluate the project by calculating NPV.

Periods	0	1	2	3	4
Initial Investment	-10000	-	-	-	-
Cash Inflows	-	4000	4000	4000	4000

To achieve this, the following formula should be written in the \[NPV] node: **`NPV`****`(12% ,`****`[Cash Inflows]`****`.`****`ALL_PERIODS`****`)+`****`[Initial Investment]`** We are including .ALL\_PERIODS in the formula so that it takes the value of the entire year's cash inflow rather a single period. ### Result | NPV | 2149.40 | | --- | ------- | # PMT The PMT function calculates the payments for a loan based on constant payments and a constant interest rate. ### Syntax PMT(Rate,Nper,PV,FV?,Type?) - > Number ### Arguments

Arguments	Datatype	Description
Rate	Number, Node reference	The interest rate per period.
Nper	Number, Node reference	The total number of periods.
PV	Node reference	The present value or initial investment. Cash outflows are considered as negative and cash inflows as positive.
FV?	Node reference	The future or residual value. This is an optional argument and if omitted, it is considered to be zero.
Type?	Number	Indicates when the payments are made. Type is zero if payments are made at the end of the period and non-zero if payments are made at the start of the period. This is an optional argument and when omitted, it is considered to be zero.

### Example Consider a $10,000 loan at an annual rate of 10% that is to be paid off in 1.5 years. All payments are made at the beginning of the month.

Periods	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
Periodic Rate	0.83%	0.83%	0.83%	0.83%	0.83%	0.83%	0.83%	0.83%	0.83%	0.83%	0.83%	0.83%	0.83%	0.83%	0.83%	0.83%	0.83%	0.83%
Number of periods	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18
Loan Amount	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000
Type	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1

To calculate the monthly payments that are to be made, the formula should be written in the \[PMT] node: **`PMT`****`( 0.83% ,`****`[Number of periods]`****`,`****`[Loan Amount]`****`,0,1)`** We are dividing the annual rate by 12 to get the period rate. ### Result **Monthly Payments**

Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13	Column14	Column15	Column16	Column17	Column18	Column19
PMT	₹ -595.61	₹ -595.61	₹ -595.61	₹ -595.61	₹ -595.61	₹ -595.61	₹ -595.61	₹ -595.61	₹ -595.61	₹ -595.61	₹ -595.61	₹ -595.61	₹ -595.61	₹ -595.61	₹ -595.61	₹ -595.61	₹ -595.61	₹ -595.61

# IPMT The IPMT function calculates the amount of interest to be paid on a loan where the interest payments and timing of payments are consistent. ### Syntax IPMT(Rate, Per, Nper, PV,FV?,Type?) - > Number ### Arguments

Arguments	Datatype	Description
Rate	Number	The interest rate per period
Per	Number	The period for which you want to find the interest. It must be in the range of 1 and the total number of periods.
Nper	Number	The total number of periods
PV	Number, Node name	The present value or initial investment. Cash outflows are considered as negative and cash inflows are positive.
FV?	Number, Node name	The future or residual value. This is an optional argument and if omitted, it is considered to be zero
Type?	Number	Indicates when the payments are made. Type is zero if payments are made at the end of the period and 1 if the payments are made at the start of the period.

### Example Consider a $10,000 loan at an annual rate of 10% that is to be paid off in 1.5 years. All payments are made at the beginning of the month.

Periods	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
Number of periods	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18
Loan Amount	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000
Type	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
Period	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
Rate	10%	10%	10%	10%

To calculate the interest payments the following formula should be written in the IPMT node: **`IPMT`****`(`****`[Rate]`****`/12,`` `****`[Period]`****`,`` `****`[Number of periods]`****`,`` `****`[Loan Amount]`****`,0,1)`** ### Result

Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13	Column14	Column15	Column16	Column17	Column18	Column19
IPMT	₹ 0.00	₹ -78.37	₹ -74.06	₹ -69.71	₹ -65.33	₹ -60.91	₹ -56.46	₹ -51.96	₹ -47.43	₹ -42.86	₹ -38.26	₹ -33.61	₹ -28.93	₹ -24.21	₹ -19.45	₹ -14.65	₹ -9.80	₹ -4.92

The interest payments for Period 1 is 0 as the payment is made at beginning of the month. Note the decrease in interest amount as the principal amount will be repaid gradually. # FV The FV function returns the future value of an investment based on periodic constant payments and a constant interest rate. The future value is the closing amount that you will receive/pay at the end of the period. ### Syntax FV(Rate, Nper, Pmt?, PV, Type?) - > Number ### Arguments

Arguments	Datatype	Description
Rate	Number, Node reference	The interest rate per period
Nper	Number	The total number of periods
Pmt?	Number, Node reference	Equal payments paid out each period during the course of the investment term. This is an optional argument.
PV	Number, Node reference	The present value or initial investment. Cash outflows should be negative and cash inflows should be positive.
Type?	Number	Indicates when the payments are made. Type is 0 if the payments are made at the end of the period and 1 if payments are made at the start of the period.

### Example Consider a series of $2000 payments made at the end of each year for 6 years for an investment. The objective is to calculate the future value at the end of 6 years where the interest rate is 5%.

Period	1	2	3	4	5	6
Payment	2000	2000	2000	2000	2000	2000
Interest Rate	5%	5%	5%	5%	5%	5%
Number of periods	1	2	3	4	5	6

To calculate the future value, the following formula should be entered in the \[FV] node: **`FV`****`(`****`[Interest Rate]`****`,`****`[Number of Periods]`****`,`****`[Payment]`****`,0,0)`** ### Result

Column1	Column2	Column3	Column4	Column5	Column6	Column7
FV	$2,000.00	$4,100.00	$6,305.00	$8,620.25	$11,051.26	$13,603.83

The value of $2000 keeps increasing at the end of each year by the interest rate of 5% giving a value of 13603.83 by the end of 6 years. # PV The PV function returns the present value of an investment based on periodic, constant payments and a constant interest rate. ### Syntax PV (Rate, Nper, Pmt?, FV, Type?) - > Number ### Arguments

Arguments	Datatype	Description
Rate	Number, Node reference	The interest rate per period.
Nper	Number	The total number of periods.
Pmt?	Number, Node reference	Equal payments paid out each period during the course of the investment term. This is an optional argument.
FV	Number	The future value of investment. Cash outflows are considered as negative and cash inflows as positive.
Type?	Number	Indicates when the payments are made. Type is zero if payments are made at the end of the period and non-zero if payments are made at the start of the period. This is an optional argument and when omitted, it is considered to be zero.

### Example Consider a Loan of $20000 for which interest payments are made at the end of each year for 6 years. The objective is to calculate the present value where the interest rate is 7%.

Year	1	2	3	4	5	6
Interest Rate	7%	7%	7%	7%	7%	7%
Number of periods	1	2	3	4	5	6
Payments Made	1400	1400	1400	1400	1400	1400
Type	0	0	0	0	0	0

To achieve this, the following formula should be written in the \[Present Value] node: **`PV`****` ``(`****`[Interest Rate]`****`,`` `****`[Number of Periods]`****`,`` `****`[Payments Made]`****`,0,0)`** ### Result

Column1	Column2	Column3	Column4	Column5	Column6	Column7
Present Value	₹ -1,308.41	₹ -2,531.23	₹ -3,674.04	₹ -4,742.10	₹ -5,740.28	₹ -6,673.16

# CUMIPMT The CUMIPMT function returns the cumulative interest paid between two periods. ### Syntax CUMIPMT (Rate, Nper, PV, start\_period, end\_period, Type) - > Number ### Arguments

Arguments	Datatype	Description
Rate	Number, Node reference	The interest rate per period.
Nper	Number	The total number of periods.
PV	Number	The present value of the loan.
start_period	Number	The first payment period.
end_period	Number	The last payment period.
Type	Number	Indicates when the payments are made. Type is zero if payments are made at the end of the period and non-zero if payments are made at the start of the period.

### Example Consider a $10,000 loan at an annual rate of 10% that is to be paid off in 1.5 years. All payments are made at the beginning of the month. Calculate Cumulative Interest Payments for all periods.

Periods	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
Rate	10%	10%	10%	10%	10%	10%	10%	10%	10%	10%	10%	10%	10%	10%	10%	10%	10%	10%
Number of Years	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5
Loan Amount	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000
Start Period	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
End Period	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
Payments Type	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1

To achieve this, the following formula should be written in the \[Cumulative Interest Payments] node: **`CUMIPMT`****` ``(`****`[Rate]`****`/12,`****`[Number of Years]`****`*12,`****`[Loan Amount]`****`,1,`****`[End Period]`****`,1)`** ### Result

Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13	Column14	Column15	Column16	Column17	Column18	Column19
Cumulative Interest Payments	0	-78.369938	-152.42956	-222.14296	-287.4739	-348.38588	-404.84206	-456.80531	-504.238192	-547.102955	-585.361529	-618.97553	-647.906252	-672.114668	-691.561427	-706.206846	-716.010916	-720.933291

# CUMPRINC The CUMPRINC function returns cumulative principal paid on a loan between two periods. ### Syntax CUMPRINC (Rate, Nper, PV, start\_period, end\_period, Type) - > Number ### Arguments

Arguments	Datatype	Description
Rate	Number, Rate	The interest rate per period.
Nper	Number	The total number of periods.
PV	Number	The present value of the loan.
start_period	Number	The first payment period.
end_period	Number	The last payment period.
Type	Number	Indicates when the payments are made. Type is zero if payments are made at the end of the period and non-zero if payments are made at the start of the period.

### Example Consider a $10,000 loan at an annual rate of 10% that is to be paid off in 1.5 years. All payments are made at the beginning of the month. Calculate cumulative principal payments for all the periods.

Periods	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
Nper	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18
Loan Amount	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000
Start Period	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
End Period	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
Payments Type	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1

To calculate the principal payment for all periods, the formula should be written in the \[Cumulative Principal Payments] node: **`CUMPRINC`****`(10%/12,`****`[Nper]`****`,`****`[Loan Amount]`****`,1,`****`[End Period]`****`,1)`** ### Result

Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13	Column14	Column15	Column16	Column17	Column18	Column19
Cumulative Principal Payments	-595.607405	-1112.8449	-1634.3927	-2160.2867	-2690.5631	-3225.2586	-3764.4098	-4308.0539	-4856.22845	-5408.9711	-5966.31993	-6528.31333	-7094.99001	-7666.389	-8242.54965	-8823.51163	-9409.31497	-10000

# RATE The RATE function returns the interest rate per period of a loan or an investment. ### Syntax RATE (Nper, PMT, PV?, FV?, Type?, Guess?) - > Number ### Arguments

Arguments	Datatype	Description
Nper	Number	The total number of periods.
PMT	Number/ Node reference	The payment made each period.
PV?	Number/ Node reference	The total amount that a series of future payments is worth now.
FV?	Number/ Node reference	The future value or a cash balance you want to attain after the last payment is made. This is an optional argument.
Type?	Number	Indicates when the payments are made. Type is zero if payments are made at the end of the period and non-zero if payments are made at the start of the period.

Guess?	Number	Estimate of what the rate will be. This is an optional argument.

### Example Consider a series of $2000 payments made at the end of each month for 1 year. The objective is to calculate the interest rate per month where the present value is $20,000.

Periods	0	1	2	3	4	5	6	7	8	9	10	11	12
Present Value	-20000	-	-	-	-	-	-	-	-	-	-	-	-
Payments made	-	2000	2000	2000	2000	2000	2000	2000	2000	2000	2000	2000	2000

To calculate the Interest Rate, the formula should be: **`RATE`****`(12,`****`[Payments made]`****`,`****`[Present Value]`****`,0)`** ### Result The interest rate is calculated to be 2.92% # Range Functions

NAME	SYNTAX	DESCRIPTION
Range	.Range(start_index,end_index)- > number	Returns the values between the specified start and end periods.
LastNPeriods	.LastNPeriods(n)- > number	Returns the values of the last 'n' periods
Get	.Get(period_index)- > number	Returns the values of the specified period index
ForEach	ForEach(Array,Iterator)	To apply the same operation for multiple nodes.
findByTitle	findByTitle(searchString) - > [?]	Tagging/Grouping function. Returns the value of the nodes which has the specified title
GETCURRENTRANGE	DS.(SERIES).GETCURRENTRANGE()	Returns the current range values based on the period selection.
GETROWVALUE	DS.(SERIES).GETROWVALUE(sourceKey: string) - > [number]	Value of source key row
THIS_SOURCE_KEY	DS.(SERIES).THIS_SOURCE_KEY	Returns the value of the mentioned source key.
PRIOR_SERIES_PERIOD_LOOKUP	PRIOR_SERIES_PERIOD_LOOKUP (seriesOffset, periodIndex, initialValue) - > [number]	Returns the specified period's value.
PERIOD_LOOKUP	PERIOD_LOOKUP(offset, initialValue) - > [number]	Returns the specified series's value.

# RANGE The RANGE function refers to the values of a node between the specified start and end periods. We can use this function to find the values of certain periods. ### Syntax .RANGE(start\_index, end\_index) - > \[number] ### Arguments

Arguments	Datatype	Description
start_index	Number	The starting range/period value
end_index	Number	The ending range/period value

### Remarks This function can only be used after refering a node for which this calculation needs to be done as shown below in the example. ### Example Consider a scenario where we have interest payments for months Jan to Dec. Our objective is to calculate the interest paid in the months April to June. | Periods | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | | ---------------------------------------------------- | --- | ----- | ----- | ----- | ----- | ----- | ----- | ----- | ----- | ----- | ----- | ----- | | Interest payments | 0 | 78.37 | 74.06 | 69.71 | 65.33 | 60.91 | 56.46 | 51.96 | 47.43 | 42.86 | 38.26 | 33.61 | To achieve this, the following formula should be written in the \[Range Interest payments] node: **`SUM`****`(`****`[Interest payments]`****`.`****`Range`****`(4,6))`** ### Result

Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13
Range Interest payments	195.95	195.95	195.95	195.95	195.95	195.95	195.95	195.95	195.95	195.95	195.95	195.95

# LastNPeriods The LASTNPERIODS function refers to the values of the node’s last ‘n’ periods. It will give the result as the last 'n' periods' value. ### Syntax .LASTNPERIODS(n) - > \[number] ### Arguments

Arguments	Datatype	Description
n	Number	The integer that is less than the number of periods defined in the model.

### Remarks This function can only be used after referring a node for which this calculation needs to be done as shown below in the example. ### Example Consider a scenario where we have interest payments for Jan to Dec. Our objective is to calculate the interest paid in the last 6 months for each month.

2022 Periods	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Interest payments	0	78.37	74.06	69.71	65.33	60.91	56.46	51.96	47.43	42.86	38.26	33.61

To achieve this, the following formula should be written in the \[Last 6 period's value] node: **`SUM`****`(`****`[Interest payments]`****`.`****`LastNPeriods`****`(6))`** We give n as 6 since we want to calculate the last 6 months value. This will calculate the last 6 months value for each period. So for March it will add Jan & Feb, for April it will add Jan to Mar and so on. ### Result

Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13
Last 6 period's value	0	0	78.37	152.43	222.14	287.47	348.38	404.84	378.43	351.8	324.95	297.88

# Get The GET function returns the value of the node corresponding to the period index that is specified. ### Syntax .GET(period\_index) - > number ### Arguments

Arguments	Datatype	Description
period_index	Number	An integer that is less than the number of periods defined in the model.

### Remarks This function can only be used after referring a node for which this calculation needs to be done as shown below in the example. ### Example Consider a scenario where you have the Discount rates for each period of 2023. The objective is to take the Dec 2023 rate as the discount rate for all periods of 2024.

Periods	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sept	Oct	Nov	Dec
Discount (2023)	10%	12%	12%	11%	12%	12%	13%	12%	10%	11%	13%	12%

To achieve this, the following formula should be written in the \[Discount(2024)] node, **`[Discount (2023) ]`****`.`****`Get`****`(12)`** ### Result

Periods	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sept	Oct	Nov	Dec
Discount (2024)	12%	12%	12%	12%	12%	12%	12%	12%	12%	12%	12%	12%

# ForEach When we have multiple nodes and want to execute the same operation for each of the node, we will use the ForEach function. It is not applicable for performing operation to a single node. ### Syntax ForEach (Array,Iterator) ### Arguments

Arguments	Datatype	Description
Array	Node Name	The node for which the iterator statement is executed for all the periods.
Iterator	This, Children, Parent	The operation that is to be executed.

### Example Consider the below scenario where we have Revenue and cost items for different regions. Our objective is to calculate the total costs incurred for marketing.

Year	2020
US Marketing (FOREACH)	14000
New York (Sum children)	2510
Men (Subtract Children)	1000
Revenue (Sum Children)	4500
Sales	4000
Receivables	500
Costs (Sum Children)	3500
Marketing	3000
Salary	500

Women (Subtract Children)	1510
Revenue (Sum Children)	5510
Sales	5000
Receivables	510
Costs (Sum Children)	4000
Marketing	2000
Salary	2000

LA (Sum children)	-4120
Men (Subtract Children)	-1200
Revenue (Sum Children)	4000
Sales	3500
Receivables	500
Costs (Sum Children)	5200
Marketing	4000
Salary	1200

Women (Subtract Children)	-2920
Revenue (Sum Children)	4080
Sales	3540
Receivables	540
Costs (Sum Children)	7000
Marketing	5000
Salary	2000

To achieve this, the following formula should be written in the \[US Marketing] node, **`SUM`****`(`****`FOREACH`****`(`****`THIS`****`.`****`CHILDREN`****`,`****`FOREACH`****`(`****`THIS`****`.`****`CHILDREN`****`.`****`THIS`****`,`****`THIS`****`.`****`CHILDREN`****`.`****`GET`****`(2).`****`CHILDREN`****`.`****`GET`****`(1))))`** ### **Explanation**


(FOREACH (THIS.CHILDREN,	As we are entering the formula in the parent node of both regions - New York and LA , THIS.CHILDREN will refer to each of the children of [US Marketing] node which is [New York] and [LA]
FOREACH (THIS.CHILDREN.	This will refer to each of the child nodes of [New York] and [LA] which are [Men] and [Women]
THIS,	This refers to that same node so it will be [Men] and [Women]
THIS.CHILDREN.GET(2).	This will consider the second child [Costs] of the Men and Women nodes as we have mentioned Get(2)
CHILDREN.GET(1) )))	This will get the value of the first child of [Costs] that is [Marketing]. Sum of these [Marketing]nodes are returned which is 14000.

# findByTitle The findByTitle function can be used when you want to find the values of the nodes with same name/title. ### Syntax findByTitle(searchString) - > \[?] ### Arguments

Arguments	Datatype	Description
searchString	Text / Number	Title to match the node name.

### Remarks This function can only be used after refering a node for which this calculation needs to be done as shown below in the example. ### Example Consider a Scenario where you want add all the nodes with title "Apple Sales" & "Orange Sales". Below is the data of apple and orange sales made by different Suppliers.

Period	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Suppliers (Parent node)	185800	192400	194000	182400	188000	206900	184900	186100	192500	188600	202650	190050
Supplier A	55700	57900	60900	55900	59200	62100	56200	58600	59300	58300	61400	60300
Apple Sales	24300	25300	25800	23300	27300	28400	21200	26900	25700	24200	28900	27300
Orange Sales	31400	32600	35100	32600	31900	33700	35000	31700	33600	34100	32500	33000
Supplier B	60400	61800	56600	56300	57300	60900	58700	58000	59000	62600	62800	60800
Apple Sales	22500	22100	21800	23600	21900	22300	24200	24800	21700	25600	23400	22000
Orange Sales	37900	39700	34800	32700	35400	38600	34500	33200	37300	37000	39400	38800
Supplier C	69700	72700	76500	70200	71500	83900	70000	69500	74200	67700	78450	68950
Apple Sales	43400	44300	46700	45300	43600	46900	44200	42800	45900	43800	47200	41000
Orange Sales	26300	28400	29800	24900	27900	37000	25800	26700	28300	23900	31250	27950

To achieve this, the following formula should be written in the \[Total Apple Sales] node: **`SUM`****`(`****`[Suppliers]`****`.`****`DESCENDANTS`****`.`****`findbytitle`****`("`****`Apple Sales`****`")`** Here we have mentioned the \[Suppliers] node name before writing Findbytitle which means we are asking the function to go to the descendants of the Suppliers node and get the value under the title "Apple Sales". ### Result The function will add up all the Supplier's Apple Sales value and give it in the Total Apple Sales node.

Period	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Suppliers	185800	192400	194000	182400	188000	206900	184900	186100	192500	188600	202650	190050
Total Apple Sales	90200	91700	94300	92200	92800	97600	89600	94500	93300	93600	99500	90300

# GETCURRENTRANGE The GETCURRENTRANGE funtion returns a range of value based on active period and period selection. ### Syntax DS.(SERIES).GETCURRENTRANGE() ### Example Consider the below table that has Sales values for all months in a year. Our objective is to find the Quarter-1 Sales value.

Periods	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Sales	845	122	561	681	594	842	1153	674	951	154	214	854

To get the value of First Quarter sales, we should first set the Value display as Sim.Months and keep Jan to Mar as the Simulation Period. Then give the formula as: **`SUM`****`(`****`DS`****`._Sum_of_2023.`****`[Sales]`****`.`****`GETCURRENTRANGE`****`())`** {% hint style="info" %} Change the Sim.Months according to the values you want to achieve. If you want to get Q4 Sales you should set the Sim.Months as Oct to Dec. {% endhint %} ### Result


Q1 Sales	1528

# GETROWVALUE The GETROWVALUE function returns the value of the source key row. ### Syntax DS.(SERIES).GETROWVALUE(sourceKey: string) - > \[number] ### Arguments sourceKey: string - string that corresponds to the source key. ### Example Consider the below scenario where we have the Sales values for a Parent node. The objective is to allocate the Sales value of 2020 to all periods of the child node. #### Parent

Year	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2019	8744503	8599938	8642337	8430046	8537787.6	8635150.9	8751803.7	8530118	8546371.7	8397872.3	8596191.1	8379559.9
2020	8188477	7959189	7470250	7688532	8221765	7188152	7341150	8265013	7718777	8299877	8550181	8544909
2021	1890876	1959373	2204492	1809924	1889838	1676175	1844642	2209590	1702085	1650029	2048406	1879161

To achieve this, the following formula should be written in the Child node: **`DS`****`._Sum_of_2020.`****`GETROWVALUE`****`(`****`THIS.PARENT.SOURCE_KEY`****`).`****`GET`****`(`****`CPI`****`)`** {% hint style="info" %} **THIS\_SOURCE\_KEY** function is explained in detail in [this section](/model/formula-functions/range-functions/this_source_key). {% endhint %} ### Result #### Child

Year	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2019	8188477	7959189	7470250	7688532	8221765	7188152	7341150	8265013	7718777	8299877	8550181	8544909
2020	8188477	7959189	7470250	7688532	8221765	7188152	7341150	8265013	7718777	8299877	8550181	8544909
2021	8188477	7959189	7470250	7688532	8221765	7188152	7341150	8265013	7718777	8299877	8550181	8544909

### Explanation GETROWVALUE allows the node to look into the row of the parent as mentioned in the formula and get the sum of 2020 value for all periods. # THIS\_SOURCE\_KEY The THIS.SOURCE\_KEY function returns the value of the mentioned source key row from the data series. ### Syntax DS.(SERIES).THIS.SOURCE\_KEY ### Example Consider the below scenario where we have the Sales values for a Parent node. The objective is to allocate the Sales value of 2020 to all periods of the child node. #### Parent

Year	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2019	8744503	8599938	8642337	8430046	8537787.6	8635150.9	8751803.7	8530118	8546371.7	8397872.3	8596191.1	8379559.9
2020	8188477	7959189	7470250	7688532	8221765	7188152	7341150	8265013	7718777	8299877	8550181	8544909
2021	1890876	1959373	2204492	1809924	1889838	1676175	1844642	2209590	1702085	1650029	2048406	1879161

To achieve this, the following formula should be written in the Child node: **`DS`****`._Sum_of_2020.`****`GETROWVALUE`****`(`****`THIS`****`.`****`PARENT`****`.`****`SOURCE_KEY`****`).`****`GET`****`(`****`CPI`****`)`** {% hint style="info" %} To know about **GETROWVALUE** function, click [here](/model/formula-functions/range-functions/getrowvalue). {% endhint %} ### Result #### Child

Year	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2019	8188477	7959189	7470250	7688532	8221765	7188152	7341150	8265013	7718777	8299877	8550181	8544909
2020	8188477	7959189	7470250	7688532	8221765	7188152	7341150	8265013	7718777	8299877	8550181	8544909
2021	8188477	7959189	7470250	7688532	8221765	7188152	7341150	8265013	7718777	8299877	8550181	8544909

### Explanation PARENT.SOURCE\_KEY allows the node to look into the source key value of the parent as mentioned in the formula and get the sum of 2020 value for all periods. # PERIOD\_LOOKUP A PERIOD\_LOOKUP function returns any periods values. ### Syntax PERIOD\_LOOKUP(offset, initialValue) - > \[number] ### Arguments

Arguments	Datatype	Description
offset	Number	The number of periods it has to go back or front.
initial Value	Number	The value of first period.

### Remarks * This function can only be used after referring a node for which this calculation needs to be done as shown below in the example. * If we give negative number for the offset it will go back to the past periods and vice versa. * Period lookup will only work if there is 1 series (Year). To use it for multiple series PRIOR\_SERIES\_PERIOD\_LOOKUP function should be used along with PERIOD\_LOOKUP. ### Example 1 Consider a scenario where you want to calculate the closing balance of Non-Current assets by including the monthly movements. Below is the information.


Non-Current Asset Balance	$30 million
Depreciation (Straight line)	12% pa

To achieve this, the following formula should be written in the \[Opening Balance] node: **`[Closing Balance]`****`.`****`PERIOD_LOOKUP`****`(-1,30000000)`** Closing Balance node name is referred in the formula before using PERIOD\_LOOKUP so that the calculation is performed for this specific node values. Since we have mentioned the first period value as $30 million it will take this as January month value. After that it will take the Closing Balance node as the previous period value as we have mentioned -1. ### Result

Period	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sept	Oct	Nov	Dec
Opening Balance	30000000	29700000	29400000	29100000	28800000	28500000	28200000	27900000	27600000	27300000	27000000	26700000
Monthly movements	300000	300000	300000	300000	300000	300000	300000	300000	300000	300000	300000	300000
Closing Balance	29700000	29400000	29100000	28800000	28500000	28200000	27900000	27600000	27300000	27000000	26700000	26400000

### Example 2 Consider a scenario where you have Year 1's Unit price value and Growth rate of Unit price for Years 2 to 5. We need to calculate the current year's price by taking the previous year's price and multiplying it with the growth rate.

Year	1
Unit price	400
Growth rate (Years 2-5)	5%

To achieve this, the following formula should be written in the \[Unit Price] node: **`(`****`THIS`****`.`****`PERIOD_LOOKUP`****`(-1,400)*(1+`****`[Growth rate (Years 2-5)]`****`)`** ### Result | Year | 1 | 2 | 3 | 4 | 5 | | ---------- | --: | --: | --: | -----: | -----: | | Unit price | 400 | 420 | 441 | 463.05 | 486.20 | # PRIOR\_SERIES\_PERIOD\_LOOKUP PRIOR\_SERIES\_PERIOD\_LOOKUP function returns prior series period values. It can also go to forward series period values. ### Syntax PRIOR\_SERIES\_PERIOD\_LOOKUP(seriesOffset, periodIndex, initialValue) -> \[number] ### Arguments

Arguments	Datatype	Description
seriesOffset	Number	The number of series it has to go back.
periodindex	Number	The number of periods it has to go back.
initial Value	Number	The value of first period.

### Remarks This function can only be used after referring a node for which this calculation needs to be done as shown below in the example. Seriesoffset and periodindex can also go back to many prior series/periods. ### Example Consider a scenario where you want to create cashflow statement for 3 years and refer the closing balance of prior period as opening balance of current period. In the below table net cash in / out for three periods are given. Calculate the opening cash balance and closing cash balance for all periods in 2020,2021, 2022.

Period	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sept	Oct	Nov	Dec
Opening Cash Balance 2020	183439
Net Cash in/out 2020	10279	10413	14912	9905	15466	7953	14312	12984	12691	12581	10357	31164
Net Cash in/out 2021	12335	2056	2056	2056	2056	2056	2056	2056	2056	2056	2056	2056
Net Cash in/out 2022	12828	2138	2138	2138	2138	2138	2138	2138	2138	2138	2138	2138

To achieve this, the following formula should be written in the \[Opening cash Balance] node: **`[Closing Cash Balance]`****`.`****`PERIOD_LOOKUP`****`(-1,`****`[Closing Cash Balance]`****`.`****`PRIOR_SERIES_PERIOD_LOOKUP`****`(1,12,183439))`** Closing Cash Balance node name is referred in the formula before using PERIOD\_LOOKUP & PRIOR\_SERIES\_PERIOD\_LOOKUP so that the calculation is performed for this specific node values. In the PRIOR\_SERIES\_PERIOD\_LOOKUP initial value is given as 183439 which will be considered as first period value for the first series. In the SeriesOffset, the value is given as 1 which will go back to previous series and get the 12th period value from the node Closing Cash Balance. PERIOD\_LOOKUP will get the previous period value. ### Result

The initial value mentioned in the formula (**183439)** is taken as the January 2020 opening balance. Closing balance of every month is carried forward as the opening balance for the next month. Closing cash balance of 2020 December will be carried forward as opening balance for January 2021. This happens for all the series. # Static Identifiers

CONSTANTS	EXPLANATION
THIS/ME	Refers to the Current Node
CURRENT_SERIES*	Refers to the Current series label
CURRENT_SERIES_ID*	Refers to the ID of the current series
CURRENT_PERIOD_INDEX (or) CPI	Returns the value of the period selected.
CURRENT_NODE_VALUE	Returns the value of the current node.
DS.SERIES	Returns the value from the data series for the specified node value

# THIS/ME THIS identifier refers to the current node. It cannot be used separately but should be used along with other functions to refer the Parent/Child node. Refer [FOREACH ](/model/formula-functions/range-functions/foreach)to understand it more. ### Arguments

Arguments	Explanation
THIS.PARENT	When you want to access the Parent node of a current node.
THIS.CHILDREN	When you want to access the child node of a current node

# CURRENT\_SERIES\* ### Syntax CURRENT\_SERIES == 'series'? value1:value2 ### Arguments series - Refers to the series, i.e., act, base or tgt. value1 – The value that should be assigned to the series mentioned in the argument list. If series is mentioned as base, value1 is assigned as baseline series. value2 – The value that should be assigned to the series other than the one mentioned in the argument list. If series is mentioned as base, value1 is assigned as comparison series. ### Example Consider a scenario where you want to assign 100 as the monthly baseline value and 85 as the monthly comparison value. This can be achieved by using the formula: **CURRENT\_SERIES** **==** **'base'?**** 100:85** {% hint style="info" %} \*This function's development is in progress. {% endhint %} # CURRENT\_SERIES\_ID\* ### Syntax CURRENT\_SERIES\_ID == 'series'? value1:value2 ### Arguments series - Refers to the series label i.e., Forecast, Budget, etc. value1 – The value that should be assigned to the series mentioned in the argument list. If series is mentioned as Forecast, value1 is assigned as the series Forecast. value2 – The value that should be assigned to the series other than the one mentioned in the argument list. If series is mentioned as Forecast, value1 is assigned as the series Comparison. ### Example Consider a scenario where you want to assign 100 as the monthly Forecast value and 85 as the monthly Budget value. This can be achieved by using the formula: **CURRENT\_SERIES\_ID == 'Forecast'?**** 100:85** {% hint style="info" %} \*This function's development is in progress. {% endhint %} # CURRENT\_PERIOD\_INDEX Current period index returns the value of the period that is selected. ### Example Consider the scenario where you want to calculate the Balance due for a loan taken for $40000.

Periods	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Loan amount	40000	35000	30000	25000	21000	17000	14000	11000	8000	5000	3000	1000
Amount paid	5000	5000	5000	4000	4000	3000	3000	3000	3000	2000	2000	1000

To achieve this the following formula should be written in the \[Balance due] node: **`[Loan amount]`****`-`****`[Amount paid]`****`.`****`GET`****`(`****`CURRENT_PERIOD_INDEX`****`)`**

Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13
Balance due	35000	30000	25000	21000	17000	14000	11000	8000	5000	3000	1000	0

### Explanation As we used the current period index for Amount paid it will take Feb's value to subtract with Feb's loan amount, Mar's value to subtract with Mar's loan amount and so on. # CURRENT\_NODE\_VALUES The CURRENT\_NODE\_VALUES function returns the node's current period value. ### Example Consider a scenario where you want to assign 1000 as the first month's Sales value and want the subsequent periods to have a month-on-month growth of 10%. To achieve this, the following formula should be written in the \[Sales] node: **`IF`****`(`****`CURRENT_PERIOD_INDEX`****`==1,1000,`****`CURRENT_NODE_VALUES`****`.`****`GET`****`(`****`CURRENT_PERIOD_INDEX`****`-1)*1.1)`** ### Result | Period | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | | ------ | ---: | ---: | ---: | ---: | ---: | ---: | ---: | ---: | ---: | ---: | ---: | ---: | | Sales | 1000 | 1100 | 1210 | 1331 | 1464 | 1611 | 1772 | 1949 | 2144 | 2358 | 2594 | 2853 | # DS.SERIES Ths function will return the value of the specified measure from the dataseries. ### Syntax

Arguments	Datatype	Description
DS.SERIES_Title	Series reference	Returns the data source value for the mentioned data series

### Example Find the Variance between sales values of 2020 & 2021. #### Sales (Data Source) | Year | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | | -------- | ------ | ------ | ------ | ------ | ------ | ------ | ------ | ------ | ------ | ------ | ------ | ------ | | **2021** | 121100 | 124200 | 120900 | 118300 | 116600 | 114900 | 123000 | 116500 | 112200 | 117500 | 119700 | 116100 | | **2020** | 194971 | 199962 | 194649 | 190463 | 187726 | 184989 | 198030 | 187565 | 180642 | 189175 | 192717 | 186921 | To achieve this, the following formula should be written in the \[Sales variance] node: **`SUBTRACT`****`(`****`DS`****`._Sum_of_Sales_2021.Sales.`****`GET`****`(`****`CPI`****`),`` `****`DS`****`._Sum_of_Sales_2020.Sales.`****`GET`****`(`****`CPI`****`))`** ### **Result** **Sales Variance** | 73871 | 75762 | 73749 | 72163 | 71126 | 70089 | 75030 | 71065 | 68442 | 71675 | 73017 | 70821 | | ----- | ----- | ----- | ----- | ----- | ----- | ----- | ----- | ----- | ----- | ----- | ----- | The function will take the respective values from the Datasource and give the result in the visual. By using the DS function we can avoid adding too many Datasource nodes to the ValQ Models. # Node Properties | Constants | Explanation | | ------------------------------------------- | ------------------------------------------------------- | | \[NODE].PARENT | Refers to the Parent Node | | \[NODE].CHILDREN | Refers to the visible children node | | \[NODE].VISIBLE\_CHILDREN | Returns all values of the visible children of a node | | \[NODE].HIDDEN\_CHILDREN | Returns all values of the hidden children of a node | | \[NODE].act | Returns the value of the Simulated Series of the node | | \[NODE].tgt | Returns the value of the Comparison Series of the node | | \[NODE].base | Returns the value of the Baseline Series of the node | | \[NODE].ALL\_PERIODS | Returns all Period values | | \[NODE].SIM\_PERIODS | Returns simulation period values | | \[NODE].act.ALL\_PERIODS | Returns all period values of Simulated Series, | | \[NODE].tgt.ALL\_PERIODS | Returns all period values of Comparison Series | | \[NODE].base.ALL\_PERIODS | Returns all period values of Baseline Series | | \[NODE].act.SIM\_PERIODS | Returns simulation period values of Simulated Series | | \[NODE].tgt.SIM\_PERIODS | Returns simulation period values of Comparison Series | | \[NODE].base.SIM\_PERIODS | Returns simulation period values of Baseline Series | | \[NODE].Range(start\_index,end\_index) | Returns selected range values | | \[NODE].act.Range(start\_index,end\_index) | Returns selected range values of Simulated Series | | \[NODE].base.Range(start\_index,end\_index) | Returns selected range values of Baseline Series | | \[NODE].tgt.Range(start\_index,end\_index) | Returns selected range values of Comparison Series | | \[NODE].Get(period\_index) | Returns selected period value | | \[NODE].act.Get(period\_index) | Returns selected period value of Simulated Series | | \[NODE].base.Get(period\_index) | Returns selected period value of Baseline Series | | \[NODE].tgt.Get(period\_index) | Returns selected period value of Comparison Series | | \[NODE].LastNPeriods(n) | Returns selected range values | | \[NODE].act.LastNPeriods(n) | Returns selected range values of Simulated Series | | \[NODE].base.LastNPeriods(n) | Returns selected range values of Baseline Series | | \[NODE].tgt.LastNPeriods(n) | Returns selected range values of Comparison Series | | \[NODE].TOTAL | Returns the all period aggregated value of node | | \[NODE].PTD | Returns the period to date aggregated value of node | | \[NODE].SEL | Returns the simulation periods aggregated value of node | | \[NODE].TITLE | Returns node TITLE | # Node Properties Explanation ## **\[NODE].PARENT** \[NODE].PARENT refers to the parent node of the NODE. This cannot be used seperately and should be used along with other functions while referring to the Parent node. #### **Example** If the node Sales has child nodes, East, West, South and Central, the node Sales can be referred to as \[East].PARENT, \[West].PARENT, \[South].PARENT or \[Central].PARENT. ## **\[NODE].CHILDREN** \[NODE].CHILDREN refers to the children nodes of the NODE. This cannot be used seperately and should be used along with other functions while referring to the Child nodes. #### **Example** If the node Sales has child nodes, East, West, South and Central, the child nodes of Sales can be referred to using \[Sales].CHILDREN. ## **Sample Dataset 1** We will use the following dataset for explaining the below functions.

## **\[NODE].VISIBLE\_CHILDREN** \[NODE].VISIBLE\_CHILDREN refers to the children nodes of the NODE, that are visible and not hidden. This cannot be used separately and should be used along with any aggregation functions like SUM, Product, Subtract etc. For example, if we want to calculate the sum of visible children of the Profit node, we will use the formula **SUM(\[Profit].VISIBLE\_CHILDREN)**

**Sum of Profit node's visible children**

## **\[NODE].HIDDEN\_CHILDREN** \[NODE].HIDDEN\_CHILDREN refers to the children nodes of the NODE, that are hidden. This cannot be used separately and should be used along with any aggregation functions like SUM, Product, Subtract etc. If we want to calculate the sum of hidden children of the Expenses node, we will give the formula as, **SUM(\[Expenses].HIDDEN\_CHILDREN)**

**Sum of Expense node's hidden children**

## **\[NODE].TITLE\*** \[NODE].TITLE returns the node's TITLE. **Note**: \*This function's development is in progress. ## **Sample Dataset 2** This is a dataset of \[Sales] node. This is used for the below examples.

## **\[NODE].ACT** \[NODE].ACT refers to the Simulated Series of the node. This can only be used along with other functions like SUM, GET(CPI). SUM(\[Sales].act) will return the simulated values of the sales node.

## **\[NODE].TGT** \[NODE].TGT refers to the Comparison Series of the node. This can only be used along with other functions like SUM, GET(CPI). SUM(\[Sales].tgt) will return the comparison values of the sales node.

## **\[NODE].BASE** \[NODE].BASE refers to the Baseline Series of the node. This can only be used along with other functions like SUM, GET(CPI). SUM(\[Sales].base) will return the baseline values of the sales node.

## **Sample Dataset 3:** This is a dataset of \[Cost of Sales] node. This is used for the below examples.

## **\[NODE].ALL\_PERIODS** \[NODE].ALL\_PERIODS refers to all period values. This can only be used along with other functions like SUM, GET(CPI). \[Cost of Sales].ALL\_PERIODS.GET(CPI) will return baseline values of all periods.

## **\[NODE].SIM\_PERIODS** \[NODE].SIM\_PERIODS refers to simulated period values. This can only be used along with other functions like SUM, GET(CPI). The periods in a model are defined from Jan to Dec and the active simulation period is from Jun to Dec. \[Cost of Sales].SIM\_PERIODS.GET(CPI) will return Simulated values of all Sim periods.

## **Sample Dataset 4:** This is a dataset of \[Sales] node. This is used for the below examples.

## **\[NODE].act.ALL\_PERIODS** \[NODE].act.ALL\_PERIODS returns all period values of the simulated series. This can only be used along with other functions like SUM, GET(CPI). \[Sales].act.ALL\_PERIODS.GET(CPI) will return Simulated values of all periods.

## **\[NODE].tgt.ALL\_PERIODS** \[NODE].tgt.ALL\_PERIODS returns all period values of the comparison series. This can only be used along with other functions like SUM, GET(CPI). \[Sales].tgt.ALL\_PERIODS.GET(CPI) will return Comparison Series value of all periods.

## **\[NODE].base.ALL\_PERIODS** \[NODE].base.ALL\_PERIODS returns all period values of the baseline series.This can only be used along with other functions like SUM, GET(CPI). \[Sales].base.ALL\_PERIODS.GET(CPI) will return Baseline Series value of all periods.

## **\[NODE].act.SIM\_PERIODS** \[NODE].act.SIM\_PERIODS returns simulated period values of the simulated series.This can only be used along with other functions like SUM, GET(CPI). The periods in a model are defined from Jan to Dec and the active simulation period is from Jun to Dec. \[Sales].act.SIM\_PERIODS.GET(CPI) will return simulated period values of the simulated series.

## **\[NODE].tgt.SIM\_PERIODS** \[NODE].tgt.SIM\_PERIODS returns simulated period values of the comparison series.This can only be used along with other functions like SUM, GET(CPI). The periods in a model are defined from Jan to Dec and the active simulation period is from Jun to Dec. \[Sales].tgt.SIM\_PERIODS.GET(CPI) will return simulated period values of the comparison series.

## **\[NODE].base.SIM\_PERIODS** \[NODE].base.SIM\_PERIODS returns simulated period values of the baseline series.This can only be used along with other functions like SUM, GET(CPI). The periods in a model are defined from Jan to Dec and the active simulation period is from Jun to Dec. \[Sales].base.SIM\_PERIODS.GET(CPI) will return simulated period values of the baseline series.

## **Sample Dataset 5:** This is a dataset of \[Sales] node. This is used for the below examples.

## **\[NODE].TOTAL** \[NODE].TOTAL returns the all period aggregated value of the node. \[Sales].TOTAL

## **\[NODE].PTD** \[NODE].PTD returns the period till date aggregated value of the node. The seleted months in Simulte tab will be considered for the PTD calculation and value display should be Sim Months. If Full Year is selected Full Year Values will be Shown instead of PTD. Here Sim Months is selected and the period is set From Jan to Mar. \[Sales].PTD

## **\[NODE].SEL** \[NODE].SEL returns the simulation period aggregated value of the node. \[Sales].SEL

## **Sample Dataset 6:** This is a dataset of \[Sales] node. This is used for the below examples.

## **\[NODE].RANGE(START\_INDEX,END\_INDEX)** \[NODE].RANGE(start\_index,end\_index) returns selected range values of the node. The Value display should be selected as Sim.months and the number of periods should be selected in Sim periods. If we keep it as Jan to Apr it will give us 4 month's value. If we keep it as Jan to may it will give us 5 month's value. SUM(\[Sales].RANGE(9,12)) will return the values of Sept to Dec as the full period value.

**Sum of Baseline Series from Sept to Dec**

## **\[NODE].act.RANGE(START\_INDEX,END\_INDEX)** \[NODE].act.RANGE(start\_index,end\_index) returns selected range values of Simulated Series. SUM(\[Sales].act.RANGE(9,12)) will return the simulated values of Sept to Dec as the full period value.

**Sum of Simulated Series from Sept to Dec**

## **\[NODE].base.RANGE(START\_INDEX,END\_INDEX)** \[NODE].base.RANGE(start\_index,end\_index) returns selected range values of Baseline Series. SUM(\[Sales].base.RANGE(9,12)) will return the baseline values of Sept to Dec as the full period value.

## **\[NODE].tgt.RANGE(START\_INDEX,END\_INDEX)** \[NODE].tgt.RANGE(start\_index,end\_index) returns selected range values of Comparison Series. SUM(\[Sales].tgt.RANGE(9,12)) will return the comparison values of Sept to Dec as the full period value.

**Sum of Comparison Series from Sept to Dec**

## **\[NODE].GET(PERIOD\_INDEX)** \[NODE].GET(period\_index) returns the selected period value of the node. \[Sales].GET(6) will return the value of 6th month which is June

## **\[NODE].act.GET(PERIOD\_INDEX)** \[NODE].act.GET(period\_index) returns the selected period value of the Simulated Series. \[Sales].act.GET(6) will return the simulated value of the month June

## **\[NODE].base.GET(PERIOD\_INDEX)** \[NODE].base.GET(period\_index) returns the selected period value of the Baseline Series. \[Sales].base.GET(6) will return the baseline value of the month June

## **\[NODE].tgt.GET(PERIOD\_INDEX)** \[NODE].tgt.GET(period\_index) returns the selected period value of the Comparison Series. \[Sales].tgt.GET(6) will return the comparison value of the month June

## **\[NODE].LastNPeriods(n)** \[NODE].LastNPeriods(n) returns selected range values of the node. This cannot be used seperately and should be used along with other aggregate functions like SUM,PRODUCT,SUBTRACT. If we want to calculate the sum of previous two periods, the formula should be SUM(\[Sales].LastNPeriods(2))

**Sum of Baseline Values of Previous 2 Periods**

## **\[NODE].act.LASTNPERIODS(N)** \[NODE].act.LastNPeriods(n) returns selected range values of the Simulated Series.This cannot be used seperately and should be used along with other aggregate functions like SUM,PRODUCT,SUBTRACT. SUM(\[Sales].act.LastNPeriods(2)) will return the Sum of the simulated values of the previous two periods.

**Sum of Simulated Values of Previous 2 Periods**

## **\[NODE].base.LASTNPERIODS(N)** \[NODE].base.LastNPeriods(n) returns selected range values of the Baseline Series.This cannot be used seperately and should be used along with other aggregate functions like SUM,PRODUCT,SUBTRACT. SUM(\[Sales].base.LastNPeriods(2)) will return the Sum of the baseline values of the previous two periods.

## **\[NODE].tgt.LASTNPERIODS(N)** \[NODE].tgt.LastNPeriods(n) returns selected range values of the Comparison Series.This cannot be used seperately and should be used along with other aggregate functions like SUM,PRODUCT,SUBTRACT. SUM(\[Sales].tgt.LastNPeriods(2)) will return the Sum of the comparison values of the previous two periods

**Sum of Comparison Values of Previous 2 Periods**

# Global Settings The Settings tab allows you to configure global-level settings for the visual. To access the Settings menu, go to the **Model** Tab and then click **Settings** on the toolbar at the top.

Settings are categorized as General and Display, within which many sections are available. You can also use the search box to jump to the required sub-menu directly.

## 1. General ### 1.1. About Model #### **Model Name** The name of the model can be changed here. This will appear at the bottom right corner of the screen in all the tabs.

#### **Enable Model tab in Reading view** By default, the model tab is available only in the editing view in the Power BI service. Toggle this button to use the model tab in the reading view.

A sample ValQ model looks as shown below before enabling this option.

**Before enabling model tab in the reading view**

After toggling on this button, the report now has the model tab in read mode.

**After enabling model tab in the reading view**

#### **Simulation Settings** To create multiple initiatives within your simulations, you can enable this toggle button.

By default, this feature is disabled. To know more about initiatives refer to [this section.](/simulate/create-initiatives-in-a-scenario) #### **Model Settings** Enabling this button allows different node configurations for open and closed periods. To know more about hybrid period configuration, you can refer [here](/model/node-settings#hybrid-configuration).

### 1.2. Data Settings The Data Settings menu helps in transforming the data that is added to the visual in the desired manner before you can start building the model. #### Split Series by Fiscal Year You can break down the data series added to the visual, into multiple series using the **Split Series** option. {% hint style="info" %} **Note:** 1. The **Split Series** option gets enabled only when the visual is mapped with the data series containing data from **multiple years.** 2. Suppose the time period data you have assigned is not in a hierarchy and has custom time formats like 2023Q1, 23, Jan, 07, etc., you'll need to verify whether the **Time Interval Mapping** is automatically done by ValQ. Otherwise, you'll need to [map it manually](#1.3.-time-interval-mapping) for the visual to identify and map the time periods accordingly before using the **Split Series** option. {% endhint %} A single data series can be split into multiple series where you can define the starting and ending periods for the fiscal year like April to March, Q1 to Q4, and so on. Based on the starting period you assign, ValQ automatically configures the ending period. In the below example, the visual has a data series assigned, that contain 6 years of data from 2017 to 2022.

By clicking the **Split Series** option, a pop-up appears on the screen where you can define the start period of the fiscal year. Since April is chosen as the starting period, ValQ automatically fills the end period as March. As shown in the below example, the source series is split into six separate data series for each fiscal year from April 2016 to March 2022.

Let us now explore the different columns available here. **Source:** This refers to the initial data series assigned in the **Values** field before splitting the series. **Name:** This refers to the multiple series names. They can be renamed by the user in the Split Series pop-up window. By default, the series name starts with the end year followed by the name of the source data series. **Type:** This column contains details about the source of the data series. 'Native' type refers to the native Power BI series assigned to the visual, while 'Plan' refers to the plan series created by the user. **\*Forecast Start:** This feature used in the hybrid formula is still under development. **Previous Series:** The available data series are mapped to their respective previous series (series that comes before logically) by ValQ automatically. Users are allowed to change the previous series if required. **Excluded Series:** In this column, you can choose to exclude any of the data series that are not required. The last two recent data series cannot be excluded as they become the default baseline and comparison series. {% hint style="info" %} The excluded series, though excluded in the Plan, Simulate and Report tabs, would still be available for calculations and formula reference. {% endhint %} After splitting the series, you can compare and simulate any two series as baseline and comparison.

### 1.3. Time Interval Mapping If the time periods are assigned as individual fields instead of using a time hierarchy, ValQ automatically maps them to the model using the custom formats as shown in the below image.

In some cases, when mapping is not done automatically you can map the time periods manually as per the time format. In the below example, 'Year' is not mapped.

Let us see how to map the time periods manually in such cases. Three columns are listed - Time Period, Type and Format. **Time Period:** ValQ pre-fills all three time periods in this field (Year, Quarter and Month) based on the data you assigned in the **Time Period** field. **Type:** Depending on the nature of the data select the appropriate time period from the dropdown list like Year, Quarter, Month or Day. **Format:** Finally, under the **Format** column, key in the pattern, the way the values are available in your data. Type **\[** or hover over the help icon

to open the list of suggested formats. E.g. If the time period is of the form 2022 Q2, enter \[YYYY] in the Year field and Q\[Q] in the Quarter field to map it.

When the green tick is shown verifying that the entered format matches with the available time label, click **Apply Changes**. This ensures that the visual accepts the data in the appropriate time format.

### 1.4. Data Window Size Using the **Data Window** section, you can load additional columns or periods from the data source into the model. The **Max Columns** is set as 15 by default and can extend up to 60 based on the data you assign, beyond which the visual would display a warning, 'Too many 'Month' values'.

At this point, go to **Settings->Data Window Size** where you can increase the Max Columns to the required number up to 1000.

## 2. Display All the display-related settings are available here. ### 2.1. Nodes The Nodes menu has a bunch of checkbox options with regard to how the nodes should work. They are explained below. **Conditional Formatting:** Check this option to apply conditional formatting to the nodes on the Simulate tab based on the node's **simulation impact** instead of variance.

**Conditional Formatting & Advanced Options**

**Advanced:** Here, using the first option you can specify the model to use manual values for the nodes whose data source value is empty. The other option is used to disable the editing of nodes in the Plan tab whose **Simulation Method** is set to *None*. **Node Customization** Two node presets are available to customize the layout style of the nodes. Preset 1 is the default style.

### 2.2. Number Formatting The Number Formatting menu has options to change the suffix of scaling and formatting the display settings of numbers.

### 2.3. Period Measure With the Period Measure menu, the labels of time period measures such as Till Period, All Periods and Simulation Period can be renamed.

{% hint style="info" %} Till Period refers to all the periods leading up to the starting simulation period. The **Exclude Active Period** checkbox can be used to exclude the starting simulation period in the Till Period. {% endhint %} ### 2.4. Level Name Configuration The levels are named by the visual based on the categories assigned in the Visualizations pane. Double-click on the text to rename any level except Level 1 which would always be the name of the root node.

### 2.5. Theme The Theme menu has options to change the font style, chart theme and logo to be displayed along with the model's name. The ValQ logo is available in the visual as the default logo which can be replaced by any 500\*500px jpeg or png file.

## 3. Export Export is provided to get the encoded text of the entire visual, which can then be used to import the model into a new visual. To export the model, click the **Export** button at the bottom left corner of the Settings window. A pop-up window with encoded text is displayed which can be downloaded by clicking the **Download** button.

The text file gets the model saved in your local system. This exported file can be shared and used to create a new model. The steps to create a new model with the exported text can be found [here](/model/create-model/import-a-model). ## 4. Delete The current model can be deleted using the **Delete** button in the Settings tab. To permanently delete the model, click **Yes** in the **Delete Model** confirmation pop-up.

# Plan Intuitively perform planning, budgeting and forecasting through an editable table interface Using Plan Tab, you can: * Create a new plan/budget based on an existing data series * Create a new plan from scratch (Zero-based Budgeting) * Plan continuously by creating a rolling forecast * Perform Time Series Forecasting * Update an existing plan/forecast by leveraging various allocation methods Take a look at the Plan tab below

{% hint style="info" %} In the Power BI service, the plan tab is available only in the **Editing view**. {% endhint %} # Create New Plan In this section, we will see how to create a plan series and the different plan configurations that are available in ValQ. ## **Steps to Create a Plan Series** ### **Step 1: Click Plan** Click **Plan** on the main navigation bar to open the Plan tab. When you open Plan tab for the first time, a pop-up window **Create a Plan** appears on the screen.

### **Step 2: Configuration Options** In the pop-up window, you have the following options to configure your new plan series. **Series Name:** Name your new series here. By default, the series would be named as *Plan Series 1, Plan Series 2* and so on as and when you create new ones.

**Starting Forecast Period:** You can choose the forecasting period to start from any period in the series. Period can be of any time dimension, but throughout this document, for the purpose of illustration we'll consider the example of months in a calendar year.

With that, let us see the options available based on the month you choose. ### **Case 1:** When you choose the first period as the starting forecast period In this case, the forecasting is performed for the full year and there is no closed period or actuals. **Forecast Period:** This automatically sets as *Jan-Dec,* since all periods (January to December) are taken as forecast periods.

'**Jan' as the starting forecast period**

**Type:** In this dropdown menu, you will be provided with three options to create a new series. 1. Copy Series 2. Forecast 3. Empty Series Each of the above types is explained in detail here: [Types of Series](#types-of-series). **Configuration:** Choose the underlying series or time periods from this menu, with which the new series has to be created. ### Case 2: When you choose any other period as the starting forecast period In this case, we will take *April* period as the starting forecast period and as a result *Jan to Mar* are considered as Closed Periods. **Forecast Period:** This automatically sets as *Apr-Dec.* **Closed Period:** All the periods before the forecast period i.e. from Jan to Mar are taken as Closed Periods. Closed period values can be chosen from **Copy Series** dropdown list consisting of all the existing series. {% hint style="info" %} The Closed Period values are displayed in locked/disabled state which cannot be edited. {% endhint %}

**April as the starting forecast period**

**Type:** In this dropdown menu, you will be provided with five options for configuring the forecast period values to create a plan series. 1. Copy Series 2. Forecast 3. Empty Series 4. Moving Average 5. Last Value Each of the above types is explained in detail here: [Types of Series](#types-of-series). **Configuration:** Choose the underlying series or time periods from this menu, with which the new series has to be created. ### Case 3: Creating Multiple Forecast Periods in a Single Plan Series You can create a plan series with multiple time frames, with each time frame forecasted using different type and configuration. **Starting Forecast Period:** It can be *Jan* or any period. There may or may not be closed periods, depending on the selected period (Case 1 or Case 2 as discussed above). **Forecast Period:** Here you can create multiple forecast periods by selecting different end periods for each of the series. New series can be added by clicking on the **+Add new** option available beside Configuration.

{% hint style="info" %} The **+ Add new** option gets enabled only if the end period selected in the **Forecast period** is not the last period. (in the above example, end period is selected as *Jun* and not *Dec).* {% endhint %} **Type and Configuration:** Each forecast series can have its own type and configuration based on your plan requirements.

Use the Bin icon next to the period configuration to delete the required time period and reconfigure. ### **Step 3: Enable Driver based Planning** Enabling this option allows the users to configure driver inputs in the series and perform driver-based planning.

Note: This feature can also be enabled later using the option available in the toolbar. For more details on enabling and configuring the driver inputs, you can refer to [this section](/plan/driver-based-planning/enabling-driver-based-plan) and [this section.](/plan/driver-based-planning/configuring-driver-inputs) ### **Step 4: Keep Source Data in Report** By default, whenever a plan series is created, its initial source data (from which it was created) is not stored anywhere in the report. This implies that any changes made to the underlying source would impact all the series that are derived from it in the run time. To retain the underlying source data and store it in the report you can check this option:

Upon checking this, the new plan series retains its initial source data values, even if any changes are made to the underlying source. #### Example Let's say you configured *Plan Series 2* from *Plan Series 1.* On checking this option, any changes made in *Plan Series 1* do not impact *Plan Series* 2 and it retains the original source data as shown below:

**Plan Series 2 retains the original source data**

**Note:** Consider the data and report size while choosing to store the source data in the report as it can affect the loading time for larger reports. {% hint style="info" %} After retaining the source data, if at any point the report data needs to be updated as the source, the **Refresh Data** option can be used. {% endhint %} If this option is unchecked, *Plan Series 2* reflects all the changes that you make in *Plan Series 1*.

### **Step 5: Add Series** After configuring all the details, click **Add Series** to create it. ## Types of Series Let us now understand the different options for creating a new series. ### **1. Copy Series** This option creates a plan series that exactly copies all the data from the underlying series chosen from **Configuration** menu. You can now perform planning on this new series that is a replica of the chosen existing series. There are two examples below. #### Example 1 The new series is created as an exact copy/duplicate of *Sum of 2021* series.

#### Example 2 The new series is created as a copy of *Sum of 2022* series only from April-Dec while the closed period data is copied from *Sum of 2021* series.

Clicking on **Add Series** generates the below series with closed periods from January to March. The closed periods are locked and visually indicated by greyed-out cells.

**New Series with Open and Closed Periods**

### **2. Forecast** A new plan series can be created by performing time series forecasting on the historical data. To perform it, select **Forecast** from the Type dropdown menu. Then, click **Configure** under Configuration to configure the settings such as the source and target periods, forecasting method, past data series, etc. that are needed to perform the forecasting.

This opens a pop-up where you can configure the weights to be applied to the previous series, the statistical method to be used, etc. as shown below.

For a detailed understanding of the forecasting methods, refer to the next section: [Time Series Forecasting.](/plan/time-series-forecasting) ### **3. Empty Series** Choose this if you do not have any past data series and/or you want to plan from scratch. This creates a blank series in which you can key in your values.

On clicking **Add Series**, a blank series is created as shown below.

{% hint style="info" %} Empty Series can be used to perform Zero Based Budgeting. {% endhint %} ### **4. Moving Average** This creates series values based on the moving average of the period you choose in configuration.

Clicking on **Add Series** generates the below series with a closed period from January to April which is greyed out to indicate that it is closed. The remaining values from May to December are the moving average of the closed period.

### **5. Last Value** This copies the values of the selected node to all the open periods. Last values can be useful when you want to retrieve and use a specific period's value for other periods.

In the below example, April to December values are taken from March month's values as Configuration is chosen as Mar.

{% hint style="info" %} You can perform a **rolling forecast** by changing the starting forecast period at the end of every month manually, while keying in the actuals for the closed period. {% endhint %} Subsequent new plans can be created by clicking **+ (Add New Series)** at the bottom of the visual. # Time Series Forecasting ValQ has built-in time series forecasting capabilities that allow you to forecast based on the past data collected, without writing a single line of code. There are two ways in which you can access this powerful feature: 1. **While creating a new plan series:** From the **Create a Plan** pop-up, select **Forecast** in the **Type** drop-down menu and click **Configure.**

2. **After creating a plan series:** Select any cell on the series and click **Forecast** from the Actions Menu to forecast for that node.

**Forecast after creating a plan series**

## Forecast Configuration The Forecast window gives you the configuration options for time series forecasting with which you can make predictions designed to suit your needs.

{% hint style="info" %} If you start forecasting while creating a new series, the forecast window does not contain the **Target Node** option to choose from. However, after selecting the forecast type, you will be prompted to choose the same when you want to run the forecast. {% endhint %}

	Options	Description
1	Target Node	Node for which forecast is done
2	Target Periods	Period for which forecast is done
3	Source Periods	The historical data period to be considered for the forecast
4	Grow By	Period-over-period growth component can be added here if required, either as an absolute value or in percentage. Use the dropdown to change between None, Value and Percentage.
5	Past Data Series & Weights	Choose the historical data series for forecasting along with its weightage.
6	Method	Select the desired forecasting method to be applied from the list: Linear Trend, Moving Averages, Weighted Moving Average, ARIMA, Single Exponential Smoothing, Holt's Method & Holt-Winters Method.
7	Distribution Method	Choose the distribution method as Match or M2M. Match is to distribute the total according to the weights of periods belonging to a single data series. M2M(+1), M2M(+2),..is used to distribute the total according to the average weights of the periods from multiple data series.

{% hint style="info" %} M2M(+1) and M2M(+2) distribution options get enabled when you choose two or more data series for weight distribution in the Forecast pop-up. {% endhint %} ## Forecasting Methods Following are the *Forecasting Methods* available: 1. Moving Averages 2. Weighted Moving Average 3. Linear Trend 4. Auto Regression-Integrated Moving Averages (ARIMA) 5. Single Exponential Smoothing 6. Holt's Method (Double Exponential Smoothing) 7. Holt-Winter's Method(Triple Exponential Smoothing) ## 1. Moving Averages The moving average method forecasts the results of Target Node for the Target Period, by calculating the Moving Average of Past Data Series period values. A Simple Moving Average is computed by dividing the sum of all the included period values by the total number of periods. **Simple Moving Average= (a1+a2+a3+..+an)/n** where, **a1,a2,a3,.....an** are the period values and **n** is the total number of periods (2 years = 24 periods) The forecasted series is predicted from the past series by applying the above computation method. The growth component can also be added as an absolute value or as a percentage when needed and the Distribution Method can be selected as Match or M2M(+1). ### Example: Moving Average Forecast with Growth Component and Distribution Method as M2M In this example, forecasting is done for the node 'Total Net Profit' for the year 2023 based on the *Sum of 2021* and the *Sum of 2022* series with equal weights as 1 and 1 respectively and 5% growth. The Forecast Method applied is the Moving Average with 'M2M(+1)' as the Distribution Method which distributes the total to its periods based on the average distribution of all Past Series Data chosen. The below figure shows the results of forecasting done based on the above configuration and the net growth is indicated in the **Results** section which is 4.9%.

## 2. Weighted Moving Average The Weighted Moving Average method computes the forecast results of Target Node for the Target Period based on the Weighted Moving Average of Past Data Series, their Weights and Source Periods. Unlike a simple moving average model that assigns equal weights to all the period values, a weighted moving average allows us to assign different weights to different periods. As a result, the accuracy of the model is improved by assigning more weights to the recent periods than to the older periods which could be irrelevant. It is computed by dividing the sum of weighted period values (product of weight and the period value) by the sum of weights. Weighted moving average WMA= (w1\*a1 + w2\*a2 +...+wn\*an) / (w1+w2+...+wn), where w1,w2,w3,....wn = weights assigned to the period values a1,a2,a3,........an = period values The growth component can be added as an absolute value or percentage growth when needed and the Distribution Method can be selected as Match or M2M(+1). ### Example: Weighted Moving Average Forecast with Growth Component and Distribution Method as M2M In this example, forecasting is done for the node 'Total Net Profit' for the year 2023 based on the *Sum of 2021* and the *Sum of 2022* series. Both the series have been given equal weights as 1 and 1 respectively along with 15% growth. Weights are also assigned for the period from January to June as 2 and the rest of the periods as 3. The Forecast Method applied in this example is the Weighted Moving Average with 'M2M' as the Distribution Method. It distributes the total to its periods based on the weighted moving average of all the Past Series Data. The image below shows the forecasting results based on the above configuration and the net growth is indicated as 15.1% in the **Results** section.

## 3. Linear Trend The linear Trend method applies Linear Regression to predict the Target Node for the Target Period based on Past Data Series, their Weights and Source Periods. The linear regression method models a linear relationship between the forecast variable(dependent variable) and one or more predictor variables(independent variables). A linear regression model in its simplest form follows this equation: **y=ax+b**, where, **y**=forecasted variable (forecasted series) **x**=predictor/input variable (past series) **a**= regression co-efficient (measure of the effect of the predictor variable on the forecasted variable) **b**= error term/noise In our case, ValQ forecasts the new series based on the linear relationship model to the selected past series. The growth component can also be included as an absolute value or a percentage increase or decrease if required and the Distribution Method can be selected as Match or M2M(+1) or M2M(+2). ### Example: Linear Trend Forecast with Growth Component and Distribution Method as Match In this example, forecasting is done for the node 'Total Net Profit' for the year 2023 based on the *Sum of 2021* and the *Sum of 2022* series with weights as 1 and 2 respectively and 10% growth. The Forecast Method applied is the Linear Trend with 'Match' as the Distribution Method. The image below shows the forecasting results based on the above configuration and the Net Growth is indicated as 8.4% in the **Results** section.

Clicking on the **table icon** at the top right of the **Results** section displays the same results in a tabular format.

Click **Apply** to apply the forecasted results to the series. Since the 'Total Net Profit' node is a complex formula, ValQ opens the Goal Seek window to select the input node you want to change.

Choose any one of the key driver nodes that you want to change, from the list displayed and click **Apply**. The forecasted series appears as shown below. Here 'Total Net Profit' is forecasted for the selected period with the selected past data series and weights, by changing the 'Units Sold' node. All the changed cells are highlighted in yellow.

{% hint style="info" %} **Learn more about Goal Seek in the** [**next section**](https://docs.valq.com/plan/pages/GAOnGGE0P17WMmvvqLXH#1.-goal-seek)**.** {% endhint %} {% hint style="info" %} 1. The following methods of forecast**(ARIMA, Single/Double/Triple Exponential Smoothing)** can be used if there is a minimum of 3 years or 36 periods of data series. 2. To apply these forecast methods, the data in the **Value** Field should be arranged in **descending order** before loading into the model, i.e. from the most recent year to the oldest year. 3. Customized Weights do not apply to these exponential smoothing methods as they already assign exponentially decreasing weights in their models. {% endhint %} ## 4. Auto Regression-Integrated Moving Averages (ARIMA) Auto Regression-Integrated Moving Average method (ARIMA) is best for a short-term planning horizon. It consists of three parts. **Auto-regression:** Auto Regressive (AR) regression model is built on the autocorrelation concept, where the dependent variable depends on the past values of itself. Beta(ß) constant is used for Auto Regression. **Integrated:** The integrated part of ARIMA attempts to convert the non-stationarity nature of the time-series data to a stationary one by performing prediction on the **difference** between any two pairs of observations rather than directly on the data itself. {% hint style="info" %} A stationary series is one whose statistical properties do not vary with time or not a function of time. Stationarity in Time Series also means series without a Trend or Seasonal components. {% endhint %} **Moving Averages:** It attempts to reduce the noise in the time series data by performing some aggregation operation to the past observations in terms of residual error. The primary Alpha(α) constant is used for Moving Average prediction. Thus it creates a 'model' for a given time series based on its past values, its lags and the lagged forecast errors and that equation can be used to forecast future values. The distribution method is M2M by default. After selecting the 3-year data series, select ARIMA method. Alpha(α) value should be provided in the box which is between 0.01 and 0.99. You can forecast without a growth factor by using *none*, or you can give the growth factor as an absolute *value* or in *percentage*. ### Example: Auto Regression-Integrated Moving Average Forecast with growth component In this example, forecasting is done for the node 'Overall Result' for the year 2023 based on 2021, 2020 & 2019 actuals with the weight as 1 for all and 15% Growth. The forecast method used in this example is ARIMA and the Alpha value is given as 0.15. You can check or un-check the required data series in the Results section for a better view.

## 5. Single Exponential Smoothing(SES) Single Exponential Smoothing method is the simplest of exponential smoothing techniques. It predicts short term baseline forecast and volatility accurately. This method uses only the level component Alpha(α) constant for forecasting. It is a ratio (or a percentage) of how much importance the model will allocate to the most recent observation compared to the importance of demand history. Hence, alpha is the smoothing factor. It is used as a Short-Term and Mid-Term planning horizon. The distribution method used in this calculation is M2M by default. After selecting the 3 years data series, select Single Exponential Smoothing method. Alpha(α) value should be provided in the box and it must be between 0.01 and 0.99. You can forecast with no growth factor by using none, or you can give the value in percentage or as an absolute value. You can also assign weights to the data series. There is an option here to optimize the model so that you get the highest possible outcome in this forecast. ## 6. Holt's Method (Double Exponential Smoothing) Double Exponential Smoothing method estimates both Level and Trend components of the series to forecast. Hence it uses two smoothing parameters Alpha(α) and Beta(ß) for the level and trend respectively. They have to be provided in the box given in Forecast Pop-Up. It is used for short, medium and long time planning horizon. The distribution method used in this method is by default M2M. After the selection of 3 years data series, select Double Exponential Smoothing method and enter Alpha & Beta values. Both Alpha & Beta values must be between 0.01 and 0.99. You can enter the growth in percentage or as an absolute value if required. Also, there is an option to optimize the model which gives the highest possible outcome in this forecast that can be used to compare with other forecast values. ## 7. Holt-Winter's Method (Triple Exponential Smoothing) Triple Exponential Smoothing method introduces seasonality components also along with level and trend components for a better and more robust forecast. A Holt-Winter's model is defined by its three order parameters, alpha, beta and gamma. Alpha specifies the coefficient for level smoothing. Beta specifies the coefficient for trend smoothing. Gamma specifies the coefficient for seasonal smoothing. So, the values for Alpha, Beta & Gamma should be provided in the box given in Forecast Pop-Up. It is used as a Long-Term planning horizon. The distribution method used in this calculation is by default M2M. After the selection of 3 years' data series, select Triple Exponential Smoothing method and enter Alpha, Beta & Gamma values in the box. Alpha, Beta & Gamma values must be between 0.01 to 0.99. You can enter the growth in percentage or as an absolute value if required. There is an option to optimize the model which gives the highest possible outcome in this forecast that can be used to compare with other forecast values. ### Example: Single, Double & Triple Exponential smoothing Forecasts with Growth component in percentage In this example, all the above exponential forecasting methods (Single, Double and Triple) have been applied for the node 'Overall Result' for the year 2023 based on 2021, 2020 and 2019 actuals. Find below the **sample** parameters used for these methods. **Single Exponential Smoothing:** 8% Growth; Alpha = 0.99 (optimized form), **Holt's Method:** 10% growth; Alpha = 0.35, Beta = 0.25, **Holt-Winter's Method:** 10% growth; Alpha = 0.33, Beta = 0.33 & Gamma = 0.33. You can simultaneously compare the results of different methods in this way and click **Apply** on the desired method to create the required series.

**Single, Double and Triple Exponential Smoothing methods**

# Allocation Features Let's explore the different features available for planning and allocations. ValQ provides multiple options to operate on the node values for accurate and effective planning. Primarily, planners are provided with 3 major options. ## 1. [Goal Seek ](/plan/allocation-features/goal-seek) It is applicable when you are trying to update a node value which is derived using a complex formula. Such nodes can be updated only by changing one of its key drivers/input nodes. Whenever you click and try to change such cells, **Goal Seek** option is automatically enabled in the Action menu and you can select it.

## 2. [Copy to Periods](/plan/allocation-features/copy-to-periods) It is used to copy the value of a node to the remaining periods of the year- either as absolute copies or in an increasing/decreasing trend fashion.

## 3.[ Distribute](/plan/allocation-features/distribute) Using Distribute, the total value of a node can be distributed horizontally to the periods of the year, as well as vertically to the descendant nodes (its dependencies).

The above Plan options are explained in detail in the upcoming sections. # Goal Seek Goal Seek feature is applicable when you want to update a node value that is derived using a complex formula, or has dependents/node references that use a complex formula. Such nodes can be updated only by changing one of its key drivers/input nodes. The **Goal Seek** button in the Action menu bar gets enabled automatically when you are trying to update a node that has a reference to a complex formula.

{% hint style="info" %} In the above image, clicking on the *Clothing* node has enabled **Goal Seek,** despite its formula being ***Sum of Visible Children*** because one or more of its child nodes contain complex formulas(e.g. division, sum of products, etc.). {% endhint %} {% hint style="info" %} This option is presented to the user automatically, in cases when you are forecasting for the nodes that involve complex formulas. In the below image, forecasting is done for the *Total Net Profit* node. Since it involves a complex formula, the user is prompted with the below pop-up window to choose the input node/changing node through goal seek. {% endhint %}

Forecasting is explained in the section: [Time Series Forecasting.](/plan/time-series-forecasting) ## Steps for Goal Seek **Step 1:** Select the node you want to change and click **Goal Seek** from the Action Menu Bar. A pop-up window opens below the screen. *Revenue* node of January is selected in the example below and **Goal Seek** is chosen.

**Step 2:** By default, **Goal Value, Target Node,** and **Period** fields are pre-filled based on the node selected. The user can change these values if required.

**Step 3:** Enter the target value to be achieved in **Goal Value.** Here the Goal Value is set as 50000.

**Note:** By clicking on the **Target Node** text box, **'Select a node'** pop-up opens in which you can select a different input node other than what you had chosen previously (for example, in this case *Revenue* node can be changed to a different node) **Step 4:** From the **Period** dropdown list, choose *Total* if you want to change and distribute the total value. [**Distribute**](/plan/allocation-features/distribute) option is displayed if you select *Total*.

**Distribute the Total after changing the Input Node**

(or) choose *any month (Jan)* if you want to copy to the remaining periods. [**Copy to Periods**](/plan/allocation-features/copy-to-periods) option gets displayed if you choose a specific period.

**Copy to Periods after changing the Input Node**

**Step 5:** Select the **Input Variable** from the list that appears on clicking it, whose change will result in achieving the goal value. The input variable selected in the example below is *Units Sold* node. The changed value of January is copied in an increasing trend of 10% to the remaining periods.

**Copy to Periods after changing the Units Sold Node**

**Note:** To preview your change, click on **View Goal Values.**

**Step 6:** Click **Run** to see the results and then Click **Apply** or continue performing goal seek until you get the desired result.

Finally, the new series is created as shown below for the desired target node, by changing the input variable. All the changed cells are highlighted in yellow in the table grid.

# Distribute The total value of a node can be allocated horizontally (to its periods) or vertically (to its dependents) by way of Distribute. ## **1. Distribute Total to Periods (Horizontal Distribution)** The value of a total node is distributed horizontally to its periods in the below examples. They can be done in two ways: 1.1. Distribute by Weights 1.2. Distribute Equally ## 1.1. Distribute by weights The total value of the cell is distributed to its periods by its **initial** weights or by **customized weights** taken from a different node that you choose. The values can be replaced or appended. ### **Example 1: Distribute by Customized Weights by Replace** Consider the data below.

The total of the *East* node is changed from 30 to 120 and the Distribution option chosen here is '**Replace by Weights'** of the *Midwest* node.

**Weights taken from** **Midwest** **node by Replace**

Since we chose Replace, the new total 120 itself is distributed based on the spread of the *Midwest* node (ratio of 1:2:3) by overwriting existing period values. The parent node's *Overall Result* also gets changed as a result of this action.

### Example 2: Distribute by current weights by Append For the same data, let us see what happens if we choose Append by current weights.

**Weights taken from** **East** **node (current weight) by Append**

As you can see, the delta value of 90 (120-30) only is distributed using their current weights yielding 40,40, and 40 (10+30, 10+30, 10+30). {% hint style="info" %} **Note:** When you distribute a total value using the **current weights,** both **Replace** and **Append** options would yield the same results as both are logically the same when using the current weights. So in the above example, using the Replace option instead of 'Append' would also give the same result. {% endhint %} ## 1.2. Distribute Equally The total value of the node can be equally distributed to periods. ### Example 1: Distribute Equally to Periods by Replace In the below example, the new value of 120 in *Midwest* node is distributed to the three periods equally as 40,40 and 40 by replacing their old values.

### Example 2: Distribute Equally to Periods by Append For the same example explained above if we had chosen **Append Values**, you would get the below results. Only the delta value of 60 (120-60) is distributed equally to the three periods yielding 30,40 and 50.

## 2. Nodes with Dependencies In the case of nodes with dependencies, along with the weights being distributed horizontally, they are also distributed vertically to their dependents. ### Example 1: Distribute to Periods and Dependents by Current Weights and by Replace Consider the data below.

*Overall Result* node is changed from 90 to 270 and the new value 270 is distributed to **dependents** - *East* and *Midwest* nodes **as well as to the periods** based on their current weights.

**Distribute periods and dependencies by weights- by Replace**

As seen from the image below, the new total of 270 is distributed between *East* and *Midwest* with their current weights of 1:2 respectively. Also, the period-wise distribution is based on their initial weights.

### Example 2: Distribute to dependencies only - equally You can distribute the total of a period node **only to its dependencies** without impacting other periods. The example below shows such a case where the value of 6k in *Jan* node is distributed only to its dependencies equally. Its impact is carried over to the top nodes as well.

**Distribute equally to** **Jan's** **dependents**

### Example 3: Distribute to dependencies only - with customized weights In the below image, the desired series and period have been chosen through the **Select Series** and **Period** dropdown list from which the new weights would be derived. The value of 6k would be distributed accordingly only to its dependencies and up to the top.

{% hint style="info" %} The value can be appended or replaced. **Append** option adds the delta value to the present value while **Replace** overwrites the present value with a new value. {% endhint %} ## 3. Distribute using the Distribute Dialog Box **Distribute** is enabled in the Action Menu Bar whenever you click on a cell that has a total. **Step 1:** Click on the required cell and click **Distribute** on the Action Menu Bar.

**Step 2:** In the dialog box, enter the total value you want. Choose to distribute it to the periods by weights or equally. In the example below, we are choosing to distribute equally.

**Step 3:** If the node has dependencies, it provides another option where you can choose to distribute the new total to its dependents equally or by weights. You can also choose the series name from which the weights should be taken. In this example, we are distributing by weights that are taken from *Plan Series 1*. # Copy to Periods **Copy to Periods** copies a node's period value (or new value if it is edited) to the future periods of that node. This action gets applied to the changing node's dependent nodes, if any. The Copy feature is available in the following ways: 1. [Copy to all Periods in right](#1.-copy-to-all-periods-in-right) 2. [Copy with Trend Percentage](#2.-copy-with-trend-percentage) 3. [Copy with Trend Value](#3.-copy-with-trend-value) {% hint style="info" %} Copy to Periods option can be applied on the period nodes only (not on the total nodes as the total value can not be copied to individual months). It is also not applicable for the last period -e.g. December. {% endhint %} ## 1. Copy to all Periods in right The original value or the edited value of the selected node is copied to the subsequent periods till the end. * In the Plan tab, click on the cell that is required to be copied. Use the right arrow that pops up. Select **Copy to right-> Copy to all periods in right.** * To edit a period's value and copy the edited value across the remaining periods, double-click on the cell and change the value you need. * Without pressing Enter, select **Copy to right->Copy to all periods in right.** The new value takes the place of the existing value and then ValQ re-calculates the total based on the time aggregation set for the node. In the below example, January month's *Cost of Sales* value of $2740 is changed to $1k. Without pressing Enter, click on the pop-up at the right and choose **Copy to all Periods in right.**

The edited value is copied horizontally to the other periods along with a bottom-up distribution to the above nodes as highlighted in yellow in the below image.

{% hint style="info" %} For nodes with dependents, check [here ](#nodes-with-dependents)to know how the copy option works with both top-down and bottom-up distribution. {% endhint %} ## 2. Copy with Trend Percentage The value of the selected period is copied to the remaining periods with a month-over-month increase or decrease in percentage. Click on any cell that is not a total. Choose **Copy to right-> Copy with Trend Percentage->**Enter the desired percentage trend you want.

## **3. Copy with Trend Value** The value of the selected period is copied to the remaining periods with a month-over-month increase or decrease of a constant value. Click on any cell that is not a total. Select **Copy to right-> Copy with Trend Value->**Enter the desired positive or negative value(month-over-month increase or decrease).

## Nodes with dependents When there are dependent child nodes for a node, you will have additional options - **Allocate by current weight, Allocate equally** or **Customize Distribution** in the Copy to Periods dropdown. Here you can specify how the change should be distributed to its dependencies(top-down) **along with** copying to periods. In the below image, *Expense* node has two child nodes and hence copying the new value not only copies it to the periods but also distributes the new value to its two child nodes-*Cost of Sales* and *Other Costs.*

## Example 1: Copy to periods and dependencies with current weights The option shown below copies the new value of 6k (*Expense* node) to its remaining periods in the right as well as distributes it to its dependencies with current weights.

**Copy and** **Distribute with current weights**

## Example 2: Copy to periods and dependencies with customized weights The option shown in the below example copies the new value of 6k (*Expense* node) to its periods and also distributes it to its dependencies, with customized weights derived from the series - *Plan Series 1 (*chosen in **Select Series).**

**Copy and** **Distribute with Customized Weights**

## Using the Copy to Periods dialog box For all the above options, alternatively, you can use the **Copy To Periods** dialog box. **Step 1:** Click on any cell whose value you want to copy to other periods. **Step 2:** Click the **Copy To Periods** option on the Action Menu Bar.

**Step 3:** A dialog box appears. Here, you can enter the desired value.

**Step 4:** Choose if you want to just **Copy** or Copy with **Trend**. Copy copies the same values to all the remaining periods till the end, while Trend gives you further options. **Step 5:** If you have selected **Trend**, choose whether the mode of increasing or decreasing trend should be in percentage or value.

**Step 6:** Use the slider or manually enter the values. Enter the percentage change in case of percentage mode or the incremental value in case of value mode and finally click **Apply.** In the example, the *Expense* node value $3k is copied to the remaining periods with an increasing trend of 20%. Since the *Expense* node has dependents, its total is distributed according to the weights of periods in *Plan Series 1* as shown below.

**Copied with 20% Increasing Trend and Distributed with Weights**

# Driver Based Planning Driver Based Planning is an add-on approach to planning and budgeting apart from the conventional allocation features explained in the previous sections. With this feature, planners explicitly identify the key business and value drivers, understand how they affect the KPIs, and then perform planning/budgeting by configuring these drivers. ## Advantages of Driver-based Planning 1. Planners can create a transparent and defined planning/budgeting methodology that clearly explains how certain key operational drivers impact the KPIs. This simplifies your forecast model and facilitates clear communication to all the stakeholders. 2. Forecast demand, risk and opportunities over the long term. Efficient and precise forecasting is achieved by concentrating on the drivers that matter the most and getting rid of superfluous information. 3. Execute fast and flexible planning by changing key metrics in drivers and understanding the effect. You can also create multiple drivers for different periods. Let us see the steps to configure the drivers and understand how this works in the upcoming sections. # Enabling Driver Based Plan You can choose to switch between the driver-based planning approach and the standard planning approach. To create a driver-based plan while adding a new plan series, check the option '**Enable Driver based Planning'** in the **Create a Plan** pop-up window.

Create a Plan with Driver based Planning — **Enable Driver Based Planning**

You can also change to a driver-based plan mid-way through your standard planning cycle by clicking the menu shown below and selecting **Driver Based Plan**.

{% hint style="warning" %} On the contrary, switching back to the **Standard Plan** from the driver-based plan deletes all the applied configurations in the series. {% endhint %} After enabling driver-based planning, the plan window looks as shown below where you can configure the driver inputs.

You can click on the arrows to collapse/expand the driver inputs configuration columns for a better view.

**Collpase/Expand the driver configuration columns**

{% hint style="info" %} Driver configuration options are available only for the leaf nodes to avoid conflicts between the top-down and bottom-up distributions. {% endhint %} Now you can [configure the drivers](/plan/driver-based-planning/configuring-driver-inputs) as well as perform the [standard allocations](/plan/allocation-features) for your plan. #### **Note:** Nodes with driver-based inputs and nodes with one or more dependencies using driver-based inputs are disabled from the standard allocations or configurations. In the below example, the *Transport Cost* node is disabled for any allocations as it already has a driver-based input (*HC* node) impacting it.

'**Transport Cost' node has a driver based input**

*Company A* and *Overall Result* nodes are also disabled for configurations as their dependency node *Transport Cost* has a driver-based input. All other nodes can be configured or allocated as required.

Subsequent driver based plans can be created newly or can be copied from existing plans. To copy the driver configurations (and the values, if required) you can select the options as shown below while creating a plan.

# Configuring Driver Inputs Let us now see the steps to configure the drivers. The following options are available for configuration. ### **Driver** This column lets you choose the drivers for the required nodes. The drivers can be chosen either from the current series or from any other data/plan series.

### **Driver Method** This column lists the different ways in which you can link the node and the driver according to the input value.

Depending on the driver method selected, the node values are expressed in terms of the **driver** and the **input value** for all periods.

Driver Method	Explanation
Multiplication	This multiplies the driver value and the input value.
Division	This divides the driver value by the input value.
Addition	This adds the driver value and the input value.
Subtraction	This subtracts the input value from the driver value.
Percentage of	This represents the node values as a percentage of the driver, specified in the input value column.
Growth by	This represents the node values as the growth rate of the driver. The growth rate is entered in the input value column.

### Input Value In this column, you can enter the required numerical value. Driver methods like multiplication, division, addition and subtraction accept absolute numbers as input values.

The 'percentage of' and 'growth by' methods automatically accept the numbers in a percentage format. For the 'Growth by' method, the growth rate can either be an absolute number or in percentage. The percentage increase or decrease is specified by entering positive or negative numbers in the input value column respectively.

### Example 1 Suppose you want to configure the transport cost based on the headcount in the organization for Company A. Let us assume the transport cost is $1000 per head. 1. Within ***Company A***, click on the 'Select' dropdown in the **Driver** column beside the node *Transport Cost.* 2. It has two dropdown lists. Click on the first dropdown list and pick the series from which you want to choose the driver values. For instance, let us choose 'Current Series'.

3. Click on the second dropdown list to choose the 'HC' node as the driver for the 'Transport Cost' node. Ensure that you choose the HC node of the respective company/lineage.

**Note:** You can click 'Reset to Default' to clear the choice and start configuring again. 4. Since the headcount is a factor for the transport cost, the **driver method** is selected as 'Multiplication'. 5. Per head transport cost is given as the **input value**. Click on the input value cell, key in the number '1000' and then press enter to input the value.

Now you have configured a driver for the transport cost. In the same way, you can configure as many drivers as required in your plan. ### Distribution Method The new total of the driver nodes (leaf nodes) can be distributed by their current weight or equally or can be custom distributed. These three options are available in the distribution method as shown below.

The value to be distributed is entered in the **Input Value** column and then the distribution option can be chosen. Distribution can be done for any of the leaf nodes with or without the driver input configuration. Find below the examples for the same. ### Example 2 The below example shows the new value of sales ($140m) being distributed to the periods equally. The *Sales* node and the corresponding result nodes at the top are disabled for further allocations.

### Example 3 In this example, the *Other Revenue* node is driven by a percentage of the driver node which is the *Sales* node. By default, the total is distributed by current weights as shown below.

**Other Revenue is driven by Sales node**

It can be custom-distributed if required.

{% hint style="info" %} Distribution of the total is applicable only for the 'percentage of' and 'growth by' driver methods. This is because other arithmetic methods (Addition, Subtraction, Multiplication and Division) perform period-wise calculations first and then aggregate the total. {% endhint %} To configure a bunch of drivers simultaneously, you can use the Bulk configure option which is explained in the next section. We'll also explore the configuration of template key drivers and other features in the upcoming sections. # Bulk Configuration of Drivers ### Configuring drivers for multiple nodes 1. To configure drivers for multiple nodes at once, click **Configure Drivers->Bulk Configure.**

2. Check on the required nodes to select them all together as shown below.

3. Click on the **Editor Driver Inputs** option in the menu bar at the bottom of the screen which opens the Bulk Edit window.

4. In the window, you can configure the driver, the driver method, the input value type and the distribution method (if applicable) for the drivers selected. Click **Apply.**

This way, multiple nodes can be configured simultaneously with the same driver and the driver method. The input values can then be entered for each of the nodes separately as required. ### Configuring Template Nodes For models consisting of templates, driver configuration for the template nodes can be done using this option. The user is given a pop-up window to configure the template nodes once, after which ValQ automatically assigns the respective driver nodes for each level based on the lineage. i.e., suppose the *Sales(*node) is the driver of the *Other Income* template node. By configuring it only once as shown in the below example, all the regions' respective *Sales* node is configured as the driver for the corresponding region's *Other Income* node. 1. Click **Configure Drivers->Configure Driver in Template.**

2. A pop-up window that contains all the templates and template nodes in the model opens.

3. Configure the driver inputs for the required template nodes by keying in the driver node name, the driver method, the input value, and the distribution (if applicable).

4. Click **Apply.** All the template nodes would be configured at once, based on their respective source keys as shown below.

**Driver configuration for template nodes**

{% hint style="info" %} Can I use the Bulk Configure option for the template nodes? What is the difference between bulk configure and configure driver in template? **-Bulk Configure** configures multiple nodes (similar or different named nodes), with **the same driver from the same series and lineage.** -Use the **Configure Driver in Template** option to specifically configure template nodes. This ensures that **the driver nodes are chosen from their respective lineage.** {% endhint %} ### Reset Drivers and Values 1. To reset all the driver configurations, choose **Configure Drivers->Reset->Reset Driver.** 2. To reset only the input values configured in the driver while retaining other information, choose **Configure Drivers->Reset->Reset Values.**

3. To reset the driver inputs in the template nodes, choose **Configure Drivers->Reset->Reset Template.**

* Choose the required nodes and click **Reset** which resets the driver configurations in the selected template nodes. Check **Reset Only Values** to reset only the input values.

# Plan Interface The Plan tab consists of 3 sections - Action Menu bar at the top, data table grid and the bottom pane. Allocation features available on the data grid and driver-based planning have been explained in the previous sections. Let's now explore the Action menu bar and the bottom pane which has additional options to fine-tune your plan series. ## Action Menu Bar

### 1. Standard Plan/Driver Based Plan Switch between standard planning and driver-based planning using this menu option.

Allocation features used for a standard plan as well as the driver based planning have been explained in the previous sections. ### 2. Forecast It is used to perform [Time Series Forecasting](/plan/time-series-forecasting) as explained in the previous sections.

### 3. Copy to Periods This is explained under [Allocation Features](https://docs.valq.com/plan/pages/GAOnGGE0P17WMmvvqLXH#2.-copy-to-periods).

### 4. Distribute You can distribute total value to periods and/or dependencies equally/ by weights. Check [this section](/plan/allocation-features) to know more.

### 5. Goal Seek Goal Seek is already explained in the section: [Goal Seek](/plan/allocation-features/goal-seek).

### 6. Lock/Unlock Cell Click on any cell, row, or column that you want to lock. Then click the **Lock** option in the menu bar. Locked cells do not get affected by any of the allocation methods.

In this example, *US* node is locked which is indicated by its graying out and lock symbol. *US* node and all its children would be unaffected by any allocations that would be done in the future.

To unlock, simply click on the locked cells and select **Unlock.** You can also lock or unlock by clicking the lock icon in the action column as highlighted in the below image.

### 7. Number Formatting It is used for changing the scale and to increase or decrease decimals for nodes whose **Scale** property is set as *User selected*.

### 8. Reset Reset is used to undo the last change made. Click **Reset** and a confirmation to revoke the last made change appears which you can confirm or cancel.

Use the drop-down arrow next to it to click **Reset All** which clears all the performed changes and resets to the initial state of the plan series.

### 9. Logs It preserves a log of all the changes made to the table along with the date and time of change. Click **Logs** to open the logs. You can search, view, select and highlight, or delete the specific changes in the log. Click on **Clear Logs** to clear all the logs.

### 10. Notes Select any cell and click **Notes** to add new notes. After adding a note, click **Save** to save it.

Cells with added notes are distinguished from other cells by a triangular mark at the top right corner as shown below. On hovering your mouse over the cell, you can view, edit or delete the notes.

To view all the added notes at once, click the **Show All Notes** option from the Notes menu.

Here, you can search, edit, or delete the added notes.

### 11. Export to Excel Export the plan series to Excel using this option

### 12. Refresh Data In case of any changes to the input data, **Refresh Data** can be used to update the plan report as that of the source data. On refresh, the plan gets updated and the allocation performed on the plan gets re-applied to the recent data.

On clicking **Refresh Data**, a confirmation pop-up appears.

Click **Confirm** to re-run the calculations on the recent data. {% hint style="info" %} **Refresh Data** option is available only when the user chooses to keep the source data in the report (Checked '**Keep Source Data in Report'** option). {% endhint %} ### 13. Settings Click on this option to update the closed period. This option lets the user update the actuals, close the current period and update the active/open periods.

### 14. Search Search for any node on the model by clicking the **Search** bar. After clicking, enter the text using which you want to search the nodes. The search result nodes are highlighted and the results dropdown displays the results which navigates to the highlighted nodes.

### 15. Filter The **Filter** icon

can be used to perform a condition-based search on the model. Multiple filter criteria can be provided by the user to perform advanced searches across different node properties. To add multiple criteria, click the **Add Filter** option and choose between AND or OR conditions. **Disable All** can be used to toggle between the states of filter and no filter. **Clear All** will delete all the applied filters.

### 16. Autofit You can auto-size the tabular columns to fit the visual window size for a better view by clicking on the Auto Fit icon.

### 17. Display Settings It has options to hide/unhide the Actions column as well as hidden nodes.

### 18. Formula Bar It shows the formula or the number contained in the selected cell.

## Actions Column Some of the frequently used actions like viewing node properties, log, node preview, lock/unlock, edit node, add and delete node options are available in this column beside each node that pops up when you hover your mouse over it.

## Bottom Pane It contains the information about the total number of nodes, last applied change, tab container with all the plan series tabs and **+ (Add New Series)** icon to add new plan series.

# Simulate Visualize the model in hierarchical tree or table, Run Simulations on-the-fly and Present them as interactive charts Simulations help businesses make informed decisions by testing out different scenarios and seeing the results. ValQ helps users create and compare multiple scenarios, perform in-depth scenario analysis, and visualize and present them to their team.

**Simulate Tab showing a sample tree model**

### **Highlights of the Simulate tab:** * Simulate on the fly and perform a what-if analysis * Create and compare multiple scenarios * Assign important nodes as KPIs and key inputs to track progress * Identifying the key drivers of performance * Evaluate performance against a benchmark * Present the simulation results for multiple scenarios * Add notes for your reference. * Comment, Share and Collaborate with your team * Export the scenarios to other BI sources for analysis * Write back data and comments to the source(files, SharePoint, databases, etc.) {% hint style="info" %} Comments, Collaboration, Export, and Writeback features are available in ValQ Enterprise Edition {% endhint %} Let us see the steps to create and manage scenarios for effective value driver planning and the insights we can gain from it. # Create a Scenario Scenarios are particularly useful when you want to perform what-if analysis on the model, and you can save each instance as an individual scenario. This way, you have multiple scenarios to analyze and conduct scenario analysis. {% hint style="info" %} A scenario can have a single driver node or multiple driver nodes simulated at once. {% endhint %} {% hint style="info" %} To simulate multiple combinations of drivers and evaluate their possible outcomes by adding, removing, or combining the required combinations, you can use initiatives within a scenario. To learn more, click [here.](/simulate/create-initiatives-in-a-scenario) {% endhint %} ## 1. Create a Scenario On opening the Simulate tab, the default scenario named **Scenario 1** is available without any simulations performed on it. To create a new scenario, click on the **+ Add New Scenario** option.

This opens the **Create New Scenario** pop-up window where you can enter a title for your scenario and select the baseline series on which the simulations will be performed. Click **Create** which opens the new scenario.

You can now perform your desired simulations here. To learn more about managing the scenarios that you have created, click [here](/simulate/manage-scenarios). {% hint style="info" %} Scenarios are not stored as data series that can be used for further planning and analysis. Use the [Plan](/plan) Tab for such cases. A plan series can however be simulated on the Simulate tab as a baseline or as a comparison. {% endhint %} ## 2. Views With ValQ, you can build a model of your business process and view it in the form of a tree as well as a table.

**Click the required icon to switch between the views**

Toggle between the below icons present on the top right corner of the canvas to change the view.

### 1.1. Tree View The tree is presented in a horizontal format as a visual. Its structure begins with a single node called **Top node/ Root node** and this node then branches out to other nodes as **children/ descendants**. In the below image, the model is segmented into levels starting from level 1 with the Top node named 'Total Net Profit'. The nodes share a parent-child relationship at each level. For example, the 'Total Net Profit' node is the Parent node while 'US' node, 'Asia' node and 'Europe' node are its child nodes. Similarly, the 'US' node is the parent of 'Clothing' and 'Beverages' nodes and so on.

Click on **>** to expand and **<** collapse levels. Following are the options available to search and drill through the model. * **Go to level** option to navigate between different levels. * **Search** option to search and get to a particular node. * **Zoom** icon to zoom in/out of the canvas. * Full or minimal view option for the nodes.

**Display Options & Navigating to the Nodes**

### 1.2. Table View The same information can be viewed in tabular form as seen in the image below. The total value of a node can be simulated on the Table view to view the results. This view is preferred if you want to see more details about the nodes especially the simulation impact and the variances **at the period level.** Click on the table icon

to go to the Table View. Observe the simulation slider available on Total cell of 'Other Income' node.

Table View also has **Search** and **Filter** options to search for and filter the required nodes.

## 3. Manage View In both tree and table views, you can display or hide a few details like variance, variance%, simulation impact%, trendlines, conditional formatting, secondary KPIs, etc. using the **Manage View** option at the top of the toolbar.

You can also view the simulation results for the full year or the simulation period from this menu. Click [here ](https://docs.valq.com/simulate/pages/mvRvAD7RkS13NSLAhFXT#id-5.-manage-view)to learn more about it. In the next section, we'll see how to simulate the scenarios. {% hint style="info" %} If you want to understand the components present in the node, you can refer to this section: [Understanding the Node Components.](/simulate/understanding-the-node-components) {% endhint %} # Perform Simulations ValQ provides two ways to model the simulation outcomes for your ‘what-if’ questions. 1. [Using the Simulation Slider](#1.-using-the-simulation-slider) 2. [Manual Input](#2.-manual-input) ## 1. Using the Simulation Slider Using the simulation slider is an intuitive way to simulate the nodes. The slider is available for all the simulation methods such as Change%, Growth% and Constant. ### 1.1. Tree View In tree view, hover over the desired node to reveal a slider. Clicking and dragging the slider changes the value in the cell. It also shows a percentage change with an up or down arrow as you keep moving the slider to the right or the left. The values and the total get updated once you release the slider.

Another way of interacting with the slider is using the text box next to it. Just hover over the node and click on the text box beside the slider. Enter the required simulation percentage in the box. E.g. For a percentage increase in total value, enter positive values such as 10 or 10% and for the percentage decrease you can use negative values like -5 and so on. In the below image, the 'Cost of Sales' node is decreased by 5%.

**Cost of Sales simulated to decrease by 5%**

#### Simulation results are represented in the following ways: * The impact of simulation rolls up all the way to the top node and all these impacted nodes are highlighted in blue for a while. * Display of change percentage above the nodes with an up or down arrow indicating the %increase or %decrease respectively. * Conditional formatting (color indication) on the nodes based on their variances/values. * Trendlines in the nodes are updated dynamically based on the new variances. * The KPI ribbon at the top shows the new variances and the simulation impact on the KPIs. * Variance, Variance% and Simulation Impact% on the nodes get changed. * Display of simulation summary at the top that stacks all the simulated nodes with simulation percentage. {% hint style="info" %} Conditional formatting of the nodes, Trendlines and Secondary KPIs can be shown or hidden as required using the **Settings** menu at the top right corner of the canvas. {% endhint %} ### 1.2. Table View The slider is also available in Table View for all the total value cells when you click on them. As you click, a slider pops up. Click and drag the slider to change its value.

{% hint style="info" %} Table view has an Actions column which contains frequently used options like view node details, add note, simulation disabled icon, edit node and add or remove KPIs/ key inputs. {% endhint %}

You can also click on the pencil icon beside the slider to open the text box in which you can enter the simulation value in percentage. #### Simulation results are represented in the following ways: * Impacted cell values are highlighted in blue. * Change the percentage beside the highlighted cells indicating the %increase or %decrease. * The slider symbol is displayed in the Actions column of the table for the nodes on which the simulation has been performed. * Conditional formatting (color indication) beside the cells based on their variances/values. * Updated Trendlines showing the original and the simulated variances. * The KPI ribbon at the top shows the new variances and the impact of simulation on the KPIs. * Variance, Variance%, Simulation Impact and Simulation impact% are displayed on the table when you enable them. * Display of simulation summary at the top that stacks all the simulated nodes with simulation percentage. {% hint style="info" %} Choose to display or hide the Trendlines, variance and its percentage, Simulation impact and its percentage, conditional formatting and cell highlights by using the **Settings** menu at the top right corner of the canvas. {% endhint %} ## 2. Manual Input Manual Input can be used to input a specific value as a simulation to the node. This action is allowed at both total as well as at period level for nodes that have simulation enabled on it. ### 2.1. Tree View To edit or manually input the simulation value on a node, click on the **Edit Simulation** icon that is available next to the slider.

This opens a grid consisting of the total node value and the period values where you can double-click on any of the cells (total node/ variation % /individual period) to enter a new value or use expressions like +20% or +10k to edit them, similar to the in-cell editor of the [Table View](#2.2.-table-view). The Bin icon is used to reset the simulation of the particular node.

**You can manually input a period's value to simulate**

You can also click on the **Edit** icon at the top to open this table. Clicking this icon displays a table of all the simulated nodes **at once** where you can view/edit them at the period level.

The Simulation results shown are discussed [here ](#simulation-results-are-represented-by-the-following-ways)already. {% hint style="info" %} The Simulation method is change% by default. It can be changed to growth%, constant values, etc. on the [Node Settings window](https://docs.valq.com/simulate/pages/XZI8iAj4AfUR6AwSnFil#3.1.-simulation-method). {% endhint %} ### 2.2. Table View In the table view, double-click on the total value or on the individual period values to edit the old value to a new one. ValQ also allows to enter expressions with scaled values (e.g., 11250 + 0.5k) and percentages (e.g., 11250 - 10%).

You can also click on the required cell, then go to the **formula bar** and edit to a new value.

You can also use keyboard shortcut keys to copy-paste the cell values within the table view to simulate.

The Simulation results that will be displayed are discussed [here ](#simulation-results-are-represented-by-way-of)already. {% hint style="info" %} **Note:** For an in-depth analysis of scenarios and simulation results, check this section: [Node Scenario Analysis.](/simulate/scenario-analysis-by-node) {% endhint %} {% hint style="info" %} The effect of simulation performed on a node can be seen only on the levels above the simulated node. It does not impact the descendant nodes. To achieve both bottom-up and top-down implementations, you can use the [Plan Tab.](/plan) {% endhint %} # Manage Scenarios To manage scenarios after you have created them, click on the 3 dots

beside the scenario name at the bottom, to access further options. ## Edit a Scenario To change the scenario's title and add an optional description click on the three dots and choose **Edit.**

This opens the **Edit Scenario** window where you can enter or change the title and description. Click **Apply**.

{% hint style="info" %} The baseline series once set for a scenario can not be edited. To simulate a new baseline series, either create a new scenario or use the [Duplicate](#duplicate-scenario) option. {% endhint %} ## Duplicate Scenario To duplicate an existing scenario into a new one, choose **Duplicate** on the three dots menu.

Enter a title and select a new baseline series if required and then click **Create.**

{% hint style="info" %} While duplicating a scenario, you can select a new baseline series from the **Select Baseline** dropdown list so that all the simulation steps are recreated, but essentially on a new baseline series. {% endhint %} ## Export Scenario You can export and download a scenario to your local system so that it can be imported again for future use whenever required. Click on the icons

on the toolbar to export to Excel or PDF.

This opens a pop-up where you can choose to have an expanded hierarchical format or along with an Expand/Collapse option in the hierarchical format. After choosing, click **Export** and **Download.**

## Delete Scenario To delete a scenario, choose **Delete** on the menu.

Click **Confirm** on the pop-up to delete it.

## Reset Simulation To remove the simulation on a specific node click on the **bin** icon on the node/cell or on the '**x**' icon on the simulation summary.

To remove all the applied simulations on a scenario and reset the model to its initial state click the **Clear** option on the navigation panel.

# Simulation Interface Now that we have created scenarios and explored the different ways to simulate, let us see few features that can help you with customizing and fine tuning your scenario analysis. ## 1. Key Inputs Any important node that drives the KPIs can be added as a Key Input so that it can be viewed and simulated quickly instead of searching for it in the model. You can add multiple key inputs, so that they can be simulated at once and the overall impact can be understood. ### 1.1. Add nodes as key inputs 1. Click the menu icon on the top right corner of the node and choose **Add to Key Inputs** to add that node as a key driver.

2. You can also add key inputs by clicking on **+Add** in the **Key Inputs** section.

* A pop-up opens in which you can select the desired nodes to be added as key inputs.

* Key inputs added would be displayed in the **Key Inputs** section where you can instantly edit their values to simulate them and view the results.

3. Another way to add key inputs is through the **Node Settings** window. To open that window, click on the three dots menu and choose **Edit Node**.

* This opens the Node Settings window, where you can go to the **Display** section and enable the toggle button **Show as Key Input.**

### 1.2. Remove nodes from key inputs 1. To remove a key input, choose **Remove from Key Inputs** from that specific node's menu.

2. In table view, click on the three dots in the actions column beside the required key input and choose **Remove from Key Inputs**.

3. The third way to remove a node from Key Inputs is to disable the toggle button **Show as Key Input** in the **Display** section of the **Node Settings** window as explained [here](https://docs.valq.com/simulate/pages/XZI8iAj4AfUR6AwSnFil#4.2.-show-as-key-input-and-show-as-kpi). ## 2. KPIs Any important node that you consider as a Key Performance Indicator can be added as a KPI. All the added KPIs are displayed on the KPI ribbon so that after a simulation is done you can see them being dynamically updated with their new value, variance percentage and the simulation impact.

Find below the minimal view of KPI ribbon.

### 2.1. Add nodes as KPIs 1. Click on the menu at the top right corner of the node. Then choose **Add to KPIs**. The node is now a KPI.

2. Alternatively, click the pencil icon beside **KPIs** on the KPI ribbon.

**Click on the pencil icon next to KPIs**

* Click **Add** from the pop-up.

* Choose the desired node from the list to be added as a KPI.

3. Another way to add KPIs is through the **Node Settings** window. To open that window, click on the three dots menu and choose **Edit Node**.

* This opens the Node Settings window, where you can go to the **Display** section and enable the toggle button **Show as KPI.**

### 2.2. Remove nodes from KPIs 1. To remove a node from the KPIs list, click on the pencil icon beside **KPIs** on the KPI ribbon. Then select the bin icon beside the desired node to remove it.

2. You can also click on the three-dots menu on the required node and choose **Remove from KPIs.**

3. The third way to remove a node from KPIs is to disable the toggle button **Show as KPI** in the **Display** section of the **Node Settings** window as explained [here](https://docs.valq.com/simulate/pages/XZI8iAj4AfUR6AwSnFil#4.2.-show-as-key-input-and-show-as-kpi). ## 3. Constraints and Violations Constraint is added to a node to evaluate its performance after simulation, against a threshold. After the simulation, if the node values fall below or exceed these threshold limits, a warning pops up on the screen saying that the constraint has been violated along with the difference by which it has exceeded or fallen below.

Clicking on the violation warning symbol displays all the constraints set by the user in the model.

**All Constraints violated in the model gets displayed**

Constraints can be added to the nodes through the Node Settings window. To know more, click [here](https://docs.valq.com/simulate/pages/XZI8iAj4AfUR6AwSnFil#4.3.-constraint). ## 4. Simulation Period Simulation period helps set active periods to which you can perform simulation. By default, all the periods are set as the simulation period, and they can be changed to any range of periods (including a single period) for the scenarios. This behavior ensures that when you perform a simulation to the total value it only applies the simulation to the simulation period range. To select the simulation period range, use the drop-down list at the top.

In the below example, simulation periods have been selected as 'Apr' through 'Dec', hence the simulation is applied only to these periods as shown below.

**Simulation applied from April to December**

As you can see from the table view below, the periods outside the range of the simulation period are greyed out/locked indicating that the simulation has not been performed on these periods and they are the actuals.

**Simulation Period undergoes changes while other periods are not simulated**

{% hint style="info" %} All the scenarios created in the model would share the same simulation period. {% endhint %} ## 5. Manage View You can select which node details to display in the tree or table view using the **Manage View** option. ### 5.1. Node Value Display With this option, you can view the node value, variances, and simulation impact for either only the simulation periods or for all the periods. In the below example, the model has the time period in months and the simulation period is chosen as Jan to June. A simulation of 10% change is applied to the *Cost of Sales* node. On choosing the **Sim.Months** option**,** the simulation impact is displayed as 10% and the simulated variances are shown for these 6 periods only.

On Choosing the **Full Year** option, the simulation impact and the variances are re-calculated for all 12 months (Full Year range here, consists of Jan to June as simulation periods and Jul to Dec as actuals). Hence, the simulation impact is shown as 5% for the full year.

### 5.2. Secondary KPIs Display To view the secondary KPIs on nodes, click on the option available here as shown in the image.

To learn how to set secondary KPIs, check out [this section](https://docs.valq.com/simulate/pages/XZI8iAj4AfUR6AwSnFil#4.5.-secondary-kpi). ### 5.3. Node Details Display Settings You can display or hide a few more details like variance, variance%, simulation impact%, trendlines, conditional formatting, composite nodes, etc. by toggling the respective buttons.

In Table View, you can check on the fields that you wish to display.

## 6. Lock nodes from simulation To lock a specific node from being simulated, you can set the **Simulation Method** of that node to *None.* To know more, click [here.](https://docs.valq.com/simulate/pages/XZI8iAj4AfUR6AwSnFil#3.1.-simulation-method) In the below image, the node that is disabled for simulation has a lock symbol to indicate the same.

**Simulation locked-nodes in table view**

## 7. Focus Node When you are working on large models, you might need to focus and zoom in on a specific node and its children on the canvas at the click of a button. Focus Node feature comes in handy in this case. Hover over the node and then click on

icon on the top right corner of the node.

This creates a new tab with only the selected node and its children showcased on the canvas.

You can create multiple focus nodes and click on the required tabs/focus nodes to switch between the views and quickly navigate through the model.

## 8. Settings Within the settings menu, you can find custom display options like show or hide empty nodes, node count, conditional formatting, and impacted nodes by simulation. You can change canvas and display-related settings like expansion level, theme, main color, font color, background color, the color of derived nodes, connectors, etc. from here.

Accessing this menu from the table view provides table design-related settings as shown below.

## 9. Number Scaling Change the number scaling on nodes and increase or decrease decimals by clicking on this option at the top.

## 10. Export To export your scenario to Excel or in PDF, click on the icons as shown in the below image. This is already discussed in [Export Scenario.](/simulate/manage-scenarios#export-scenario)

# Add Notes Save and organize key ideas, technical or business information and even informal notes that you can refer to later, by adding notes to specific nodes in the model. ### Tree View To add notes to a specific node, hover over the required node and click on the Add Note icon on the top right corner. Enter your note in the pop-up window and click **Save** to save it.

Nodes with added notes are distinguished from other nodes by a triangular mark at the top right corner as shown below. On hovering your mouse over the node, you can view, edit or delete the notes.

### Table View In table view, hover your mouse over the Actions column beside the desired node and click on **Add note** icon to add a note.

Enter a note and click **Save**.

In table view, nodes with added notes are indicated as shown below.

### View and Edit Notes To get an overall view of all the notes added to a model as well as to edit or delete them, click on **Notes->Show All Notes** on the navigation panel at the top.

# Compare Scenarios Multiple scenarios can be created, simulated and compared to perform scenario analysis. Click on **Compare** on the navigation panel to open the **Scenario Comparison** window.

**Click Compare for scenario comparison**

In **Scenario Comparison** pop-up, all the scenarios are listed under the baseline using which it was created, and all the series including plan series are also available for comparison. Select the required scenarios and data series that you want to compare by checking them and then click **Compare.**

**Select scenarios & series to compare**

This opens the **Compare Scenarios** window where the top node value, KPIs, key inputs and simulation summary for all chosen scenarios and data series can be compared alongside each other.

{% hint style="info" %} The current or the active scenario is highlighted with a blue outline in the **Compare Scenarios** window. {% endhint %} ## Manage Scenarios Click on **Manage Scenarios** to add or remove any existing scenarios and series. Alternatively, you can click on the **+ icon** to add an existing scenario or data series for comparison. Click on the **bin icon** to remove them from the comparison window.

Other features available in this window are highlighted in the below image.

**Edit Simulation:** This icon

opens the Edit Simulation grid where you can manually edit the values for simulation at the period level for the desired nodes. This is already discussed [here](https://docs.valq.com/simulate/pages/lJGnVOM9Zbnpb3aF7Fde#1.-manual-input). **Simulation Impact:** Clicking this icon

opens a waterfall chart showing the breakdown of the Simulation Impact on the top node for that specific scenario.

**Waterfall Chart for Total Net Profit - Scenario 2**

**Go to Scenario:** Navigate to the respective Scenario's tree model on the simulation canvas by clicking this icon

. **Remove:** Finally, **bin**

icon is used to remove the scenario from comparison. **Add KPI:** Add any node as a KPI by clicking on **+Add** to compare. **Add Note:** Click this icon

to add a note and this icon

to view already added notes. ## Download Scenario Comparison To export the scenario comparison data to Excel, click on **Download as Excel** option.

This not only exports the KPIs, Key Inputs and simulation summary as seen in the window but also all the node values in the model for the respective scenarios and series under comparison. # Scenario Analysis by Node Scenario analysis by node helps in further investigation of scenarios along with the node details and its breakdown. It helps you to visualize the trend, variance breakdown and the simulation impact for the desired nodes and its calculation components with the help of charts. To open the Analysis window, click on any node in the tree view or any cell on the Table View that needs to be analyzed. For example, clicking on the *US* node from Scenario 2 of the Simulate window opens the below pop-up where you can see the values, charts, details and notes specific to the US node in Scenario 2.

{% hint style="info" %} At the bottom of the window, you have an option (**+**) to open other nodes and switch between the opened nodes. {% endhint %} The information available on this window is explained below. We will take up an example with the following simulation summary: | | | | ---------------------------- | ------------------------------------------------------ | | Scenario Name | Scenario 2 | | Baseline Series | Sum of 2022 | | Comparison Series | Sum of 2021 | | Simulated Nodes with change% | ***Revenue (8%)*** and ***Expense (-6%)*** nodes of US | ## **1. Trend** A line chart along with a clustered column chart is used to quickly compare the simulated value, baseline value and comparison value in a period-wise distribution.

Chart Element	Representation
	Blue-green trendline represents the scenario (simulated values)
	Column chart in blue represents Baseline Values
	Column chart in orange represents Comparison Values

Hover the mouse over the chart to get the simulation details for the individual periods on the tooltip.

**Hover over to get details of simulation for a specific period**

**Click on the table icon**

**at the top right corner to switch to the table view.** Find below the table view of the trend of *Total Net Profit*.

**Trend of Total Net Profit - Table view**

### **1.1. Variance Charts** In the **Trend** chart, you can choose to include variance charts for Simulation Impact, Variance and the original variance to be displayed. Each shows their respective variance. To choose, click on the dropdown menu at the top right corner of the window in the **Trend** tab.

**Variance chart drop-down list when 'None' is selected**

The below image shows the trend chart along with the simulation impact variance chart as **Sim.Impact** option has been chosen from the dropdown list.

**Variance chart - 'Simulation Impact' is selected**

Hover over the chart to get period-wise details on the tooltip. In table view, the same chart appears as shown below.

**Table View of Sim.Impact and Sim Impact%**

Similarly, the Variable and Original variance charts can be presented in graph and table views. {% hint style="info" %} Refer to [this section](#id-5.-node-values-and-variances) to learn the difference between simulation impact, variance and original variance. {% endhint %} ## **2. Variance Breakdown** A Waterfall chart shows the variance breakdown for any chosen node. For a node with no simulation, the variance between the baseline and the comparison series is shown with a breakdown of all its immediate child nodes/calculation components. The image below shows the variance breakdown of the baseline series (Sum of 2022) versus the comparison series (Sum of 2021) for the *Asia* node.

**Variance Breakdown of Asia's total net profit - Baseline Series vs Comparison Series**

For a simulated node, the variance between the Scenario (simulated values) and the comparison values are shown with a breakdown of all its immediate child nodes/calculation components. For example, in the below image, the variance breakdown of the *Total Net Profit* node is shown for Scenario 2 versus the comparison series (Sum of 2021).

**Variance Breakdown of Total Net Profit - Scenario 2 vs Comparison Series**

## **3. Simulation Impact** A waterfall chart that visually breaks down the simulation impact is shown for the selected node i.e. it shows all the simulated inputs that add up to create the new scenario value. In the below image, the simulation impact on *Total Net Profit* is shown when *Revenue* and *Expense* values were simulated for Scenario 2.

**Simulation Impact on Total Net Profit**

In table view, the chart appears as shown below.

**Table View of Simulation Impact on Total Net Profit**

## 4. Calculation Components This shows the trend and comparison (line and clustered column charts) for the selected node with its calculation component nodes that make up the selected node. The example below shows the calculation components of Total Net Profit through trellis charts.

**Options in the Scenario Analysis window**

All the charts of calculation components can be individually downloaded or pinned using the icons available beside each of them. For larger models, the **Search** option can be used to search for any calculation components. Clicking on the pop-up icon next to each component name opens the node scenario analysis of the selected component node. ## 5. Node Values and Variances At the top of the window under the node title, you can view the Scenario value (simulated value), baseline value, comparison value and their simulation impact, variance and original variance.

**Original Variance** shows the difference between the baseline value and the comparison before simulation. **Simulation Impact** shows the difference between the simulated value and the baseline value. **Variance** shows the difference between the simulated value and the comparison value. ## 6. Open Other Nodes To include other nodes for similar analysis, click on **+ Add node details** icon on the bottom panel.

From the pop-up window, select the desired node for which you want to view the trend and analysis charts.

It will be added as a new tab to view and compare the charts.

## 7. Table View Toggle between table view and graph view by clicking on the table/chart icon at the top right corner of the window. Use the [variance dropdown menu](#id-1.1.-variance-charts) beside it to view the required data.

## 8. View Node Details On the right, under the **Details** section, you can view the node details such as its lineage, type, configuration, time aggregation, simulation method, desired trend, and the node description if added.

## 9. Add Note On the right, under the **Notes** section, you can add new notes or view added notes. You can also add notes in the tree or table view which is explained in detail [here](/simulate/add-notes). ## 10. Download Chart The **Download** option on the chart lets you download the charts in PDF format.

## 11. Split Screen View Use the **Split screen** option in the top right corner to view the tree/table model and simulation analysis window simultaneously.

The split screen also offers all the above options available in full-screen mode.

You can switch between full-screen mode and split-screen mode. To close the analysis window click **close.** # Create Initiatives in a Scenario Initiatives are particularly useful when multiple assumptions need to be simulated, either individually or in a desired combination, and evaluated based on their potential outcomes. They are quite effective for a quick and seamless scenario analysis as opposed to creating multiple scenarios for each of the combinations. This aids in strategic value driver planning. ### How do Initiatives work? 1. You can create many initiatives within one scenario. 2. By default, the simulation impact of all the initiatives is included in a scenario. 3. Choose to include or exclude required initiatives in a scenario to assess their individual/combined impact.

### Use-cases and advantages of initiatives 1. Create what-if scenarios for smaller organizational units as initiatives and merge them as required to understand the total impact. Alternatively, you can drill down a scenario by excluding a few initiatives to understand the unit-level impact. 2. Simulate possible risks that can occur in the course of your project/business by adding them as initiatives within your base case/best case/worst case scenarios. This can help you in contingency planning. 3. Multiple driver combinations can be simulated on the fly without having to start from scratch. 4. Both micro-level and macro-level scenarios can be analyzed quickly. Let us now see how to create and add them to the scenarios in the next section. # Steps to create initiatives ## Enable Initiatives Initiatives can be enabled by turning on the toggle button in the [Settings ](/model/global-settings)menu as shown below.

## Initiative and Scenario Views When initiatives are enabled, the simulation slider appears only on the 'Initiatives' view and not on the 'Scenario' View. In other words, users can only simulate the initiatives rather than the whole scenario. To toggle between both views use the Initiatives/Scenario dropdown menu.

**Simulating is possible only in initiative view**

### Initiative View Choose **Initiatives** from the Initiatives/Scenario dropdown menu. In this view, you can perform all the necessary simulations on the drivers in both the tree and the table view. #### Add/Edit/Delete Initiatives The menu at the top allows you to add, edit, delete and navigate between different initiatives. By default, every scenario has an initiative, *Initiative 1.* This means that all the simulations you make on Initiative 1 are also the simulations for Scenario 1 if the scenario has only one initiative. 1. **Add Initiative:** Click on the **+Add** option beside the Initiatives dropdown menu. A new initiative is created where you can perform the required simulations. 2. **View/Navigate:** Use the dropdown menu to **navigate** between the different initiatives. After navigating to the required initiative, you can see the simulation results corresponding to that initiative. 3. **Rename:** Initiatives are named as Initiative 1, Initiative 2, and so on by default. You can use the pencil icon beside it to **rename** it. 4. **Delete:** Click on the bin icon to **delete** the desired initiative. You cannot delete Initiative 1 which is the default initiative. You can find below the image that illustrates the above actions.

### Scenario View Choose **Scenario** from the Initiatives/Scenario dropdown menu. This gives the overall scenario results where you can view all the initiatives' aggregated simulation impact. The numbers on the simulated nodes indicate the number of initiatives in which they have been simulated. Hovering over the numbers, you can view the simulation impact of all the initiatives on that specific node.

**Aggregated and Individual Sim. Impact**

To analyze the overall impact of specific initiatives individually or collectively, click on the 'Initiatives' dropdown which opens a table containing the details of included and excluded initiatives.

Select an initiative and then click the arrows to move it from included to excluded, or vice versa. This allows you to view their simulation results accordingly. --- [Next Page](/llms-full.txt/1)