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
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.
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:
Some browsers copy the above double-quotes (") wrong. If the formula does not work, try to re-type the quotes directly.
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").
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.
|Base Price||10 EUR|
|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:
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.
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:
'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.
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:
ROLLFORWARD(FILTER(DATA("TutorialData","Actuals","Volume") / DATA("TutorialData","Forecasted","Market"),"Year","2018"))
In this step you have learned how to use the:
functions, which are the most important functions to build models in Valsight.
- Learn more about the data upload and hierarchies: Hierarchies & Dimensions
- All availble Operations 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