Challenge. U.S. Government Agency Program had incurred excessive overtime expenditures in recent fiscal years and, as part of a Headquarters-directed, enterprise-wide initiative, Region leadership was charged to reduce overtime expenditures in the current fiscal year to 25% of base year levels. To help evaluate its effort, Region leadership sought a simple, user-friendly labor execution reporting tool it could use to track and analyze unscheduled overtime and other labor expenditures during the course of the fiscal year. On assignment for a government leadership development program, I was engaged on this project to design and develop a reporting tool that Region leadership could use to track labor execution performance and make informed, data-driven decisions to realize its objectives.
Approach. Because the project involved leading Region leadership in a business function – data analytics – in which the organization didn’t have established capabilities, I had to be very flexible in my planning and execution of the project. During the engagement, I met with the project sponsor and other members of the leadership team regularly and incorporated multiple dashboard feature, functionality, and data element requests as the leadership team learned how the newly added element worked on the dashboard. In doing so, I employed an iterative approach to product design and development that required nimbleness and flexibility.
Specific tasks on this project included:
- Analyzed historical program labor execution data;
- Reviewed Standard Operating Guides (SOGs);
- Interviewed program stakeholders;
- Designed, developed, and implemented a Microsoft Power Query-powered labor execution reporting tool (incl. executive dashboard with pivot charts and slicers, performance gauge clusters, and user manual) (a key benefit of incorporating the use of Microsoft Power Query in the reporting tool design is that the user could update the dashboard after each pay period-end by formatting the most recent labor execution data file, placing the file into a designated folder, and simply pushing data refresh on the tool);
- Designed and produced a management report template used to distribute periodic labor execution performance reports to program stakeholders;
- Produced a video tutorial and user guide of labor execution data transformation, preparation, and loading procedures;
- Provided user training on the reporting tool and data update procedures; and
- Re-designed the personnel roster and provided stakeholder training (incl. video tutorial) on the application of MS Excel VLOOKUP and INDEX-MATCH lookup functions and production of the personnel roster.
After implementing the tool and delivering user training, I transferred the reporting tool to the project sponsor to independently operate during the fiscal year, and provided post-project support, as necessary.
Results. The Region leadership team received a powerful yet user-friendly Microsoft Power Query-powered reporting tool it could use to track program labor execution performance in support of its initiative and, after each pay period, successfully tracked and analyzed overtime and labor execution during the fiscal year. In feedback provided for the government leadership development program, the project sponsor offered that my experiences and skills in business planning, consulting, and Microsoft Excel were exceptionally valuable to the project, the end product more than met his expectations, and that he thought the impact of the project would have a lasting effect for years to come.