V2 - Basic Data Upload & Model Definition

This page is part of a tutorial. If you have navigated to this page directly you may start the tutorial at V2 - Building a First Model - Step by Step.

For this model, we need:

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

(info)The Demo Data is downloadable as an Excel file in the following URL: DemoContentStep3.xlsx

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 "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 which 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 this operations as well as others here: Operations)

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: 

Market

DATA("Tutorial Data","Forecasted","Market")

(warning) 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 the 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.


2018 2019 2020
Base Price 10 EUR

Inflation
2% 3%
Price
10.2 EUR 10.506 EUR

The ROLLFORWARD operation does exactly that. You can find the exact syntax at the Operations 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:

Price

ROLLFORWARD(DATA("TutorialData","Actuals","Price"),'Inflation')

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:

Volume

'Market' * 'Market Share'

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 future years.

Market Share

(lightbulb) In this tutorial we will calculate the market share based on "Volume" and "Market data", you can of course also just upload the according data. However, it gives us the opportunity teach you the most important formulas. When building your own model you should start by determining what kind of data can be obtained easily and in high quality from your systems.

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 a 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: 

Market Share

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


Step Summary

In this step you have learned how to use the:

DATAFILTERROLLFORWARDEXPAND

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

Further readings


Inflation

DATA("TutorialData","Forecasted","Inflation")

Was this helpful?

Yes | Somewhat | No