4. MDE Service

Similar to the product side we now build our Service side sub-model regarding our gross profit.

Gross Profit Services

We start off once again by creating the nodes Gross Profit, Revenue and Services and summing the later two. If you have any doubts how to do it, just have a look at 3. MDE Products first step once more.

Revenue Services

We continue defining our Revenue Services, by multiplying our "Volume" holding the number of projects with our "Daily Rates" and our "Billed Days per Project type".

Revenue Servies

'Volume Services' * 'Daily Rates' * 'Billed Days per Project Type'

COGS Services

Similar to our Revenue we also receive our COGS by multiplying three nodes. This time we need the "Internal Cost Rate" describing the internal cost per consulting day and multiply it with the "Billed Days per Project Type" and our "Volume".

COGS Services

'Volume Services' * 'Internal Cost Rate' * 'Billed Days per Project Type'

Similar to 3. MDE Product, we once again connect the lowest nodes to our Drivers and Base Data. Once again, you need to use the basic  4 formulas we learned before. You can use this as a training opportunity to figure the solution yourself. Once you ready, you can compare it with our solution.

Nr. Node Task
1. Daily Rates Use the nodes 'Base Data'.'Base Data Total Revenue' & 'Base Data'.'Base Data Billed Days' to calculate the daily rates and project them into the future.
2. Volume Services Load in the 'Drivers'.'Volume' node, but keep only "Services" from "ProductGroup" . (Volume node does not need to get projected.)
3. Billed Days Per Project Type Load in 'Base Data'.'Base Data Billed Days per Project Type' and project them into the future
4. Internal Cost Rate

Take the 'Base Data'.'Base Data Internal Cost Rate', switch the sign of data inside and project it with 'Drivers'.'Merit Increase' as a driver

Nr. Node f(x)
1. Daily Rates ROLLFORWARD('Base Data'.'Base Data Total Revenue' / 'Base Data'.'Base Data Billed Days per Project Type')
2. Volume Services FILTER('Drivers'.'Volume',"ProductGroup","Services")
3. Billed Days Per Project Type ROLLFORWARD('Base Data'.'Base Data Billed Days per Project Type')
4. Internal Cost Rate

ROLLFORWARD('Base Data'.'Base Data Internal Cost Rate' * -1,'Drivers'.'Merit Increase')

After following these steps your sub-model should look like this:

Next Steps

Congratulations, no one is stopping you now from creating meaningful models with your now solidified knowledge. You are only missing the 5. MDE OPEX to be able to create the final P&L.

Next Page

Was this helpful?

Yes | Somewhat | No