3. MDE Product

We will now start creating our "Product" Submodel to determine Gross Profit per product.

We will start from the top and move them through the tree. First step, Gross Profit.

Gross Profit

The Gross Profit is the total of our product revenue after subtracting the associated costs of goods sold (COGS). Remember that the best practice of Valsight is to have all data concerning costs store as negative numbers. Therefore we can create the Gross Profit Product Node by adding Revenue Product and COGS Product Node. Create these three nodes and join them, for example with drag and drop.

Gross Profit Product

'Revenue Product' + 'COGS Product'


'Revenue Product' + 'COGS Product'

To determine our product revenue, we multiply the average price of our products with their volume. Create the node nodes first and enter the multiplication into the Revenue Product formula:

Revenue Product

'Average Price Products'*'Volume Products'



COGS Product

We model our products cost of goods sold(COGS) as the sum of the non-personnel and personnel COGS. Create these 2 parts and sum them.

COGS Product

'COGS Non Personnel'+'COGS Personnel'


Prices, Volume, COGS (Non) Personnel

To calculate the 4 following nodes, we use the base data and drive them. Following table has the required explanations and formulas. 

Nr. Node Explenation fx(Color Links the explanation and formula)
1. Average Price Products To calculate the average price of products, we divide the past revenue by the past volume. We are only interested in the products. We use the most recent data point as projection for the future.

ROLLFORWARD(FILTER(
'Base Data'.'Base Data Revenue'/'Base Data'.'Base Data Volume',
"ProductGroup","Products"))

2. Volume Products We filter the volume projection from the Drivers model. FILTER('Drivers'.'Volume',"ProductGroup","Products")
3. COGS Non Personnel To calculate the non-personnel part, we first calculate the non-personnel ratio of the cost by multiplying the personnel ratio by minus one and adding plus one to it. We multiply that by the costs per product and the volume to get the total non-personnel cost. We the year 2018 only (see *). We use the most recent data point as projection for the future increasing it with inflation and volume.

ROLLFORWARD(FILTER(
ADDEACH('Drivers'.'COGS Personnel Split' * -1,1) *
'Drivers'.'COGS per Product' *'Volume Products' ,"Year","2018"),
'Volume Products','Drivers'.'Inflation')

4. COGS Personnel The same as 3. but with personnel split and merit increase as the cost increase driver.

ROLLFORWARD(FILTER(
'Drivers'.'COGS Personnel Split' * 'Drivers'.'COGS per Product' *
'Volume Products' ,"Year","2018"),
'Volume Products','Drivers'.'Merit Increase')


If you entered all the commands correctly your driver tree should look like this. 

Since we created a separate "Driver" and "Base Data" submodel, we are able to see which driver and data points are accessed through which node by simply following the dotted lines. 


Next Steps

Now you learned how to join complex formula together in order to model even the toughest relationships. You will find that the next 2 sub-models will be a breeze for you now. Start with the Service sub-model. 

Next Page

Was this helpful?

Yes | Somewhat | No