Contributing technical expertise to a P/L project with Sander Lenaerts
I received an MSc in Computer Science and was contacted by Addestino during my 5th year. The company provided a similar atmosphere and “sales pitch” as competitors but somehow, they seemed more sincere.
In five years at Addestino, my primary focus has been on software development gradually moving towards architecture and analysis. Addestino works on the boundary between technology and business, and I prefer to stick towards the technology side.
THE P&L PROJECT
Recently, Dominique asked me to provided assistance with an ongoing profit and loss project. Until now the calculation was done in Excel, however we had reached the limits of what Excel can do. The constant (small and big) reorganisations of our client made maintaining the Excel labour intensive. As the distribution of costs, revenue and team composition was slightly different every run, the resulting P&L lacked transparency. The challenge in this assignment comes down to: “how can we efficiently and transparently calculate profitability knowing the team compositions, cost drivers, cost allocation, profit allocation, customer segments can change at any point in time.”
Note that the actual calculations are not the problem, the excel runs in less than 5 minutes. All previously mentioned configurations were spread over multiple Excel documents each containing multiple tabs with corner case and exceptions. We needed a way to separate and centralize all configuration (all the business rules) and create a tool that can handle any combination of these configurations.
Luckily for me (who lacks experience in financial consulting), Matteo has prior experience in this domain and particularly on this P&L Excel. He had already created a first abstraction containing the high-level overview of every data transformation outlining all capabilities the new program needed. It was my job to create a software architecture to facilitate these data transformations while maximizing clarity and easy of use of the final product.
To maximise clarity, we not only used the single responsibility principle but as much as possible separated business rules from technical details.
At the highest level, this project has several easily distinguished tasks:
- Read input files.
- Calculate the different P&L elements.
- Write combined matrix in the expect PowerBI format.
1 and 3 easily translated to a single purely technical class. The calculation required for the different P&L elements is different per type of P&L element so further refinement was required.
We process three types of P&L elements:
- Revenue: per customer and product.
- Cost: format varies per cost
- Volume drivers. These drivers are used to distribute costs and revenue over customers and products.
Each of these P&L elements resulted in a 2 or more classes. First every P&L element has a factory class. The factory is responsible for calculating the element. It knows what methods to call and in what order to properly calculate the P&L element. For example: first we drop all non-EU inputs, then we aggregate the input on the Lvl 2 business unit, finally we distribute several non-businesses unite specific cost to each unit. The factory does not need to care to do any of these transformations, i.e. it does not care about which matrix transforms to execute.
Below every factory is a worker. The worker is responsible only for executing the factories commands. It stores incoming data in a certain format and transforms it as requested by the factory. The worker classes contain all technical details while the business rules are clearly written down in the factory elements.
The final missing piece is the configuration files. Something/someone should specify which volume driver to use for each group of costs. These “configurations” will change over time, therefor we do not want to enforce these configurations in code. Instead, we put all these configurations in several Excel files, after all the business will be responsible for maintaining these configurations.
The resulting architecture can be summarized as:
- A configuration manager responsible for reading all configuration files. Ensure the necessary files are present.
- A revenue factory responsible for executing the business rules related to revenue and validating the correctness of revenue input files. A revenue object to turn these business rules into matrix operations.
- A cost factory responsible for executing the business rules related to cost and validating the correctness of cost files. A cost object to turn these business rules into matrix operations.
- A volume driver factory responsible for calculating volume drivers.
As each volume driver is unique, they each receive a class solely responsible for calculating that specific volume driver and validating the correctness of the required input files.
- A Profit and Loss class responsible for combining both views and finally writing it to disk.
For the sake of this post, we ignore logging and testing.
This project is an exercise in complex problem-solving, challenging but also genuinely fun. I enjoy working closely with someone at Addestino because “I can learn as much from Matteo as he can from me.” We bring expertise from different domains and working together allows us to create value that scales.