Embedded Files
Tags: Dashboards, Data Visualization, VBA/Macros, Pivot Tables
Excel
● GOAL
● GOAL
Create an automated and interactive dashboard to display general metrics related to the HR budget, along with the specifics for each division within the HR department, based on monthly data retrieved from the system.
● OVERVIEW
● OVERVIEW
The Finance and Administration Specialist in the HR Department identified the need to automate the budget execution reporting for each of their divisions. At that moment, these updates were performed inconsistently and lacked standardization, requiring extended work periods each time they were conducted.
The main idea was to visualize budget execution compared to the approved budget to assess the current situation, calculate projected execution and reallocate resources if needed. This project involved the discussion of the requirements and priorities to select the most suitable visualizations.
The dashboard was built in Excel, leveraging the use of VBA to facilitate the uploading of raw data, automate the data cleaning process, generate visualizations, and produce PDF reports.
● CHALLENGES
● CHALLENGES
➜ Data Inconsistency. The data downloaded from the system came in various formats, which interfered with task automation.
➜ Lack of Standardization. There was no consistent format for presenting the budget information to the divisions, resulting in considerable variations between reports.
➜ Time constraints. The project was conducted remotely under a short-term contract, during a period with multiple large projects in progress. This made communication with the team less immediate and more challenging.
● SOLUTIONS AND RESULTS
● SOLUTIONS AND RESULTS
Eliminated manual work.
The dashboard significantly reduced the time and effort spent by HR Specialists on data cleaning and visualization creation, enabling a quicker and more efficient way to present updates to division heads.
Easy report generation.
This tool facilitated rapid generation of selected visualizations for the dashboard, with flexible customization options. It also provided the option for importing the individual reports to PDF.
An interactive tool.
The final dashboard was visually appealing and aligned with institutional branding. It provided an interactive summary of budget execution updates, giving a clear and relevant overview of the financial situation for each division.
Page updated
Google Sites
Report abuse