Basic Data Upload & Model Definition

For this model, we need:

• Actual Values (2018) for Price and Volume

• Actual and Forecasted Values (2018 -2023) for Inflation and the Market

Here, the first data sets are already prepared for you. 

We now go to the "Upload Data" button, which can be found in the top right corner.

After clicking the "Create new datasource" sign a new data source can be added (in this case an excel XLSX data source). 

We will now assign a name to our new datasource, for example, "TutorialData" and then simply upload it. 

Now, to use the data in the model, we navigate back to the model that was set up before. 

Similar to the way that calculations can be entered in the operations field, we can also enter a formula that defines that the system should get the data for a specific node from a specific data source.

Market

Let's start with the Market node. We want to get the data from the data source "TutorialData", on the worksheet "Forecasted", from the column "Market".  Valsight offers the DATA function for this. (You can look up these operations as well as others here: Operations / Node Formulas)

The function takes exactly our three aforementioned arguments: the data source, the worksheet (called a table in Valsight), and the column (which will be called a measure).

We will now enter this formula into the operations field: 

 Some browsers copy the above double quotes (") wrong. If the formula does not work, try to re-type the quotes directly.

Inflation

To connect the inflation, we simply change the last part of the formula, to reference the "Inflation" column from the uploaded worksheet.

We can always verify that the data is correctly connected by using the "Preview Data" functionality in the node's inspector (located a little further below the operations field in the segment "Data Sources Configuration").

Price

Now we can go on and build the first forecasted value in the system: the Price. In this demo model, we want Inflation to "drive" the price. E.g. if our price was 10 EUR in the base year, the system should automatically calculate future values by considering the Inflation.

The ROLLFORWARD operation does exactly that. You can find the exact syntax on the ‘Operations / Node Formulas’ page, but in short: The ROLLFORWARD operation tells the system to take the last value it can find to create future values of it based on one or more drivers.

To get the base data for the price, we once again use the DATA function to reference the uploaded data, which leads us to the following formula:

The "inner" operation (DATA) here again references the required data from the data source, whereas the "outer" operation, the ROLLFORWARD operation tells the system to take the last value it can find from the price and create future values of it based on inflation.

Volume

While we haven't defined the Market Share just yet, we already know that our "Volume" will simply be the product of the nodes "Market" and  "Market share". Hence, for the "Volume" node, we can simply enter the following operation:

To get a complete model we still have to define the "Market Share", however, we already know, that it has to be available for each simulation year as well because otherwise, we would not be able to calculate the "Volume" in the future years.

Market Share

The values for the "Market Share" node should now be calculated with the help of the actual volume of the market as defined in the "Market" node and the "Market Share" node.

Let's get there step by step to calculate the "Market Share" from the base data:

For the "Market Share" node, we enter the function: DATA("TutorialData","Actuals","Volume") / DATA("TutorialData","Forecasted","Market")

As we only have actual Volume data available for the year 2018, this operation returns 0% "Market Share" for all following years. To solve this issue, the years 2019 - 2023 can be filtered from the result. 

We, therefore, extend the operation with the FILTER function like this: 

FILTER(DATA("TutorialData","Actuals","Volume") / DATA("TutorialData","Forecasted","Market"),"Year","2018")

The result can be forecasted with the ROLLFORWARD function. The ROLLFORWARD function always uses the last available value of a node that is then "rolled forward" either in a straight line or with certain effects like the influence of inflation. Therefore it was important to use the FILTER function so that now the last value from 2018 will be "rolled forward", instead of the 0% from the following years.

Without any further arguments, the ROLLFORWARD does flat forecasting, e.g. no growth is applied and the value is simply written in the future years.

Extend the operation with the ROLLFORWARD function like this: 

Step Summary

In this step you have learned how to use the:

DATA, FILTER, ROLLFORWARD, EXPAND

functions, which are the most important functions to build models in Valsight. 

Further readings

• Learn more about the data upload and hierarchies: Dimension & Hierarchy Management

• All available Operations / Node Formulas for formulas

• How to correct the basis of your data using formulas and the Previous function: Model One-Time Effects to Correct the Simulation Basis

• Or continue with the next step of the tutorial: V2 - Adding a Custom Dimension