Power BI can be beautifully utilized as a central tool within a company’s culture — a tool that should be used across the entire organization in a leading role.
At one of my clients, I encountered a decentralized structure where each sales representative calculated their staffing services orders in whichever way they thought was best, using tools they individually preferred or had even created themselves.
Of course, this had the major disadvantage that there was no standardized approach or consistent perspective on preliminary costing, and no central framework through which company leadership could issue binding guidelines for calculations.
An operational drawback was that when changes to collective wage agreements (tariff changes) arose, dozens of different calculation tools needed to be manually updated. Sometimes this happened promptly — and sometimes not — and sometimes the updates were implemented correctly — and sometimes not.
It was a highly diffuse and uncontrollable process!
Calculations were not standardized, not comparable, highly individualized, and ongoing (parallel) costing or final costing after an assignment was completed often led to undesirable deviations from the original preliminary costing.
A centralized costing approach directly from the ERP system was not possible either, because it could not accommodate future tariff changes.
This is where Power BI was able to help.
I integrated the calculation tool shown below into the existing reporting structure. It (almost) meets all of my requirements — it would be perfect if it also included an approval escalation process and the handling of shift and time bonuses.
Nevertheless, even as it stands, it is very complete and offers a reliable and centralized view of relevant calculations:
Consideration of industry-specific collective wage agreements (branch-specific tariffs)
Consideration of orders subject to minimum wage regulations (legal minimum wage, minimum wage according to the German ‘Entsendegesetz’)
Known tariff changes in the future
Implementation of an internal company wage agreement (“Haustarifvertrag”) and comparison with the general sectoral agreement (in this case, iGZ)
Feel free to try it out below. I recommend you test the following:’Entsendegesetz’
Menu navigation at the bottom right corner (indicated by the white arrow pointing to the right on an orange background) which opens a navigation menu.
Switch between calculation based on “Haustarif” and “iGZ“.
Comparison of calculations based on “Haustarif” and “iGZ” (tooltip “orange exclamation mark” next to the SVS column).
The calculation is factor-based, meaning that the hourly billing rate is determined, following industry standards, by applying a factor to the base wage.
Of course, comparison wages and equal pay wages are also taken into account, as well as the tariff-compliant regulations regarding a “minimum industry surcharge (minimum ‘Branchenzuschlag’).”
BZ surcharges can be calculated using a separate factor, and it is also possible to account for a declining supplement (overtariff or off-tariff supplement) in relation to the BZ surcharges, as well as additional allowances for specific assignments — and much more.
In the minimum wage calculation / calculation according to the ‘Entsendegesetz’, future changes, both tariff-related and minimum wage-related, are also incorporated.
In the example above, a fictional “Haustarifvertrag” (internal company wage agreement) is applied.
This agreement always provides employees with 3% better conditions compared to the sector-wide collective agreement, and at least €0.50 more in each wage level.
One disadvantage is the performance of the calculation.
Due to the “dual” calculation (“Haustarif” and “iGZ“), Power BI must handle massive computational loads in the background.
Additionally, the platform hosting this report is not the fastest.
The computational demand is enormous because of the complex parameterization.
Nevertheless, in my opinion, this is a highly successful approach to gaining control over decentralized calculations and to centralize the management of critical parameters within the company, ensuring that work is carried out uniformly and following the same consistent methodology.
Excel tools for calculation purposes have become obsolete within this company.
Do you also have a wide variety of tools in use?
Are you also concerned about different versions and redundant tools?
Do you need support or assistance with your tooling?
Get in touch with me!
