Internal P&L reporting solution for a multi-country IT company
Data, in theory, is like Mathematics. It’s pure, unbiased, unquestionable, non-negotiable, and simple. It’s neutral. But in practice, data is what you use it for and how you use it. The complexity, the diversity, the structure, and usability of it depends on its sources, its users, and the output formats that you choose. Working together with our client, we saw how different needs, different professional experiences and expectations, different views and different datasets from various sources can increase the complexity and make reporting challenging. Our client’s various business units, in different countries, with managers coming from completely different backgrounds used their data in distinctive ways – serving their specific needs. In business, just as in life, the challenge is usually to simplify things as much as possible, to synchronize processes, to avoid clutter, and reduce superfluous activities to reach maximum efficiency. Our client has offices in various countries, including Austria, Germany, Switzerland, and Hungary, and works across three different business units – at different stages of their lifecycle. Their reporting was already well-advanced, with clear expectations and reports that serve business planning and forecasting: to find the harmonized view of the various data sources. Having a sales-oriented mindset, the focus was on revenue and pipeline data, with a lower priority on costs.
What we achieved
With our project the aim was to create a consolidated profit and loss report that delivers a near-real-time solution for all business units, all countries with different legal and taxation needs – serving the reporting needs of the CEO, the management, and the mid-management equally.
Challenges
Our biggest challenge was to find the right way of consolidating the data that can serve all the different professional needs (different countries, different legal entities, different business units with different business goals) and cater to the familiarity of previous experiences. The expected reporting solution had to serve multiple purposes. First, it had to serve all the potential different levels, from the CEO to management, going various levels down to the business units. This meant to provide the option to show the bigger picture view to display trends, while also offering the possibility to drill down to specific granularities. Beyond the business planning, the reporting structure had to support the work of controlling, the CFO, and the COO – and to serve as the base for the quarterly performance review of the management and the teams. Secondly, we had to create a structure that can show the changes in past, and that can help forecast the future (typically until the end of actual fiscal year); that also allows to compare achieved and forecasted performance to business plan (created before every fiscal year). For comparability and more precise planning, we needed to facilitate highlighting changes and deviations between different reporting periods. And lastly, our client being an already established company, the report structure, and the report categories within had to be aligned to the company’s established standard of P&L report, with given categories via a complex rule set. We have worked with various data sources, 3 instances of ERP, CRM and HRMS systems, the data from accounting and payroll systems of 5 legal entities in different countries around Europe, and the offline dataset of detailed yearly business planning.
Implementation
In the project we used the following technologies:
- Microsoft Power BI
- Microsoft Power Automate
- Microsoft SQL Server
- Data connections: SQL Server, Firebird, REST API, Excel, SharePoint lists
What we implemented, as the result of the project, was a customized, filterable aggregated report-system with deep drill down functions for measuring, forecasting, and comparing with the business plan for the following categories:
- direct business units’ revenue (software/service),
- revenue related cost (software/service),
- staff costs (delivery, sales management and for corporate positions),
- indirect cost
- cost of administration and finance,
- cost of operating HR,
- cost of infrastructure and IT,
- cost of management,
- cost of marketing,
- depreciation and amortization,
- taxes and
- profitability on different levels: direct margin on business units, EBIDTA, EBIT, profit after tax.
All items contain (1) actual data, from invoices and other accounting items, (2) forecast from orders (agreed delivery in contracts), and (3) forecast from sales pipeline weighted with probability. The trends are displayed over periods. All report pages can be filtered by period (month/quarter/year), business unit and lower units and by different categories. Beyond this, all specific pages can be filtered by projects, customers, vendors, products, and services type. As a unique feature, reports can be compared in detail with previous versions. The changes are highlighted week-by-week, so the group management and the business management can see the items responsible for the change in revenue and/or cost. Data updates are made in an identical structure staging environment where the controlling team and the CFO review and approve changes before publishing them to the management.
Result
As a result of the implementation, our client now has a continuously updated report for overall financial performance, as well as for detailed metrics. The current reporting system is accepted as single source of truth within the company’s leadership and used for the regular management review to set and measure individual and overall business goals. This project proved how data can serve as the go-to-solution in multiple areas of a given company, even if the diversity creates a complexity that seems impossible to handle. Promeritum PandL Report CaseStudy (PDF)
