5. MDE OPEX
We now proceed to create our final submodel, which contains our operating expenses.
OPEX
Operating Expenses (OPEX) are in our case modeled as the sum of the three nodes: “M&S”, “R&D” and “G&A”: (Marketing & Sales, Research and Development, General & Administrative)
Splitting costs into Personnel and Non-Personnel and their Projection
Once again, you have the chance to train ROLLFORWARD projections, this time also with transforming the personnel split into non-personnel split with ADDEACH formula as described in 3. MDE Product.
Try to use the description to create your functions and when ready, have a look at the solution
| Node | Description |
|---|---|
| M&S Personnel Cost | Switch the sign of 'Base Data'.'Base Data M&S', use 'Drivers'.'M&S Personnel Split'to calculate the personnel part of costs. Roll it forward afterwards with 'Drivers'.'Merit Increase'as a driver. |
| M&S Non-Personnel Cost | Transform the 'Drivers'.'M&S Personnel Split'into the non-personnel split. Apply the result to 'Base Data'.'Base Data M&S' to calculate the non-personnel costs. Project the result into the future with 'Drivers'.'Inflation'as driver. |
| R&D Personnel Cost | Same logic as 1 with nodes: 'Base Data'.'Base Data R&D' 'Drivers'.'R&D Personnel Split' 'Drivers'.'Merit Increase' |
| R&D Non-Personnel Cost | Same logic as 2. with nodes: 'Drivers'.'R&D Personnel Split' 'Base Data'.'Base Data R&D' 'Drivers'.'Inflation' |
| G&A Personnel Cost | Same as 1. with nodes: 'Base Data'.'Base Data G&A' 'Drivers'.'G&A Personnel Split' 'Drivers'.'Merit Increase' |
| G&A Non-Personnel Cost | Same as 2. with nodes: 'Drivers'.'G&A Personnel Split' 'Base Data'.'Base Data G&A' 'Drivers'.'Inflation' |
Solution
| Node | fx | |
|---|---|---|
| 1 | M&S Personnel Cost | ROLLFORWARD(-1 * 'Base Data'.'Base Data M&S'*'Drivers'.'M&S Personnel Split','Drivers'.'Merit Increase') |
| 2 | M&S Non-Personnel Cost | ROLLFORWARD(-1 * 'Base Data'.'Base Data M&S' * ADDEACH('Drivers'.'M&S Personnel Split'*-1,1),'Drivers'.'Inflation') |
| 3 | R&D Personnel Cost | ROLLFORWARD(-1 * 'Base Data'.'Base Data R&D'*'Drivers'.'R&D Personnel Split','Drivers'.'Merit Increase') |
| 4 | R&D Non-Personnel Cost | ROLLFORWARD(-1 * 'Base Data'.'Base Data R&D' * ADDEACH('Drivers'.'R&D Personnel Split'*-1,1),'Drivers'.'Inflation') |
| 5 | G&A Personnel Cost | ROLLFORWARD(-1 * 'Base Data'.'Base Data G&A'*'Drivers'.'G&A Personnel Split','Drivers'.'Merit Increase') |
| 6 | G&A Non-Personnel Cost | ROLLFORWARD(-1 * 'Base Data'.'Base Data G&A' * ADDEACH('Drivers'.'G&A Personnel Split'*-1,1),'Drivers'.'Inflation') |
If entered correctly, your model should look somewhat like this:
Next Step
Now that you have created all five sub-models you should have a fully functional Profit model for a multinational diversified enterprise covering all three important areas (Product, Services, Operating Expenses). Let’s combine it quickly into a final profit and loss model in P&L which won’t take you more than 3 minutes.