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.

1.Daily RatesUse 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 ServicesLoad in the 'Drivers'.'Volume' node, but keep only "Services" from "ProductGroup" . (Volume node does not need to get projected.)
3.Billed Days Per Project TypeLoad 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

1.Daily RatesROLLFORWARD('Base Data'.'Base Data Total Revenue' / 'Base Data'.'Base Data Billed Days per Project Type')
2.Volume ServicesFILTER('Drivers'.'Volume',"ProductGroup","Services")
3.Billed Days Per Project TypeROLLFORWARD('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