Payroll control
On this page I will show a real project - respecting the General Data Protection Law (LGPD) - used daily at Bayeux City Hall.
Control of payroll information is carried out on a constant basis. From this it is possible check indicators,deliver information It is insights expected.
1 - ADMISSION
As a Strategic Planning Analyst at Bayeux City Hall, I was the responsible per normatize the admission process of commission employees and service providers.
From that, established the process flowchart (for dismissals as well) and the preparation of protocol documents, referring to admission and payroll information - the sector responsible for recording the information.
Within the scope of the process, using knowledge in MS Office and data, I structured- in a direct mail system - the data feed to the creation of contracts of service providers. In this way, a data base in MS Excel, which is synchronized to the document in MS Word, generating the final versions.
Furthermore, I structured, in VBA, a code that allows contracts to be exported in Portable Document Format (PDF), titled by the name of the server to be hired.
Given these improvements,The preparation of each contract was optimized by 70%. A single contract what took, at least,10 minutes to be done, after the automations, takes - maximum - 3 minutes, already considering the conferences in the data.
2 - THE CALL
In the wake of the improvements implemented, there was still a bottleneck to be overcome: summoning employees to sign the contract.
Many do not answer unknown numbers and, as a result, the contract ends up expiring its legal validity for signature.
In view of this problem, new digital channels were created with the aim of more assertive communication, including: the call for the signing of contracts. After this implementation, the non-subscription rate within 15 days fell from 15% to 7%.
3 - DATA ANALYSIS
At this point we have the “icing on the cake”. After all the improvements implemented, one was missing: the creating a Dashboard, fed by month, in which it was possible to check the main indicators. The main purpose of this dash is to serve the client, who is the municipal manager, and support decision-making.
To create the Dashboard, I used concepts from Business Intelligence, VBA, Pivot Table, data modeling and Advanced Excel.
Currently this dash is powered with data from the municipal payroll. Paying attention to LGPD, in the processing, I remove sensitive and very personal data from servers, keeping only the data relevant for decision making.
Additionally, there are buttons to generate PDF report,print out on physical paper or check other indicators such as Ranking and the static base.
Some improvements will be implemented. They are: (1) automate monthly data processing from a Python prompt and (2) create workspaces in Power Bi with login and password, where data is fed monthly, after updating key points.
