Overtime Reporting Automation
This project aimed to ingest data from Excel spreadsheets and write it to an SQL database that is connected to a Power BI Report that shows monthly overtime data by department / vessel.
Challenge
Each vessel has crew that are part of an external company that employs members of a union and due to this there are certain requirements of what must be paid based on their role and department. Each voyage has expected costs to the corporation that they use to negotiate contracts with these external companies. The company needs to provide an accurate and clear accounting of the overtime being consumed by their staff. Currently this data is being ingested and processed by a small staff that is consuming a large amount of their workday keeping them from focusing on their day-to-day activities.
Technology Utilized
Microsoft Excel
Each vessel department fills out a spreadsheet with overtime data. These spreadsheets are highly formatted.
Power Flow
Power Automate was utilized to ingest and process all data.
M365
SharePoint lists and libraries are used to store and house content for this process.
​
Power Application
A Power Application was created to administer and correct data if needed.
Azure SQL
Add data was saved to an Azure SQL Database.
​
​
​
Power BI
A Power BI report was utilized to show the data by vessel / department / month.
Strategy
Monthly each vessel captain sends out a series of overtime spreadsheets that detail the overtime for each department of the vessel. These spreadsheets are sent to a shared email. Changing this process would be detrimental to the vessel staff as the training would be problematic and the current process of data capture is very accurate. Based on this we decided to listen to the incoming email of this shared email inbox, parse each spreadsheet, extract the overtime data for each department, and write this to an Azure SQL database. If an error was found during data extraction the file was sent to staff for manual manipulation to ensure it was captured correctly in the database. A BI report was then built that allowed overtime data to be seen by the vessel, department to establish trends to help with time estimating in upcoming contract negotiations.
Result
In the last year that this automation has been running, we have had 0 data entry errors, the staff that managed this process has been allowed to participate in other activities saving the organization close to 250k per year in staffing reallocation savings. Also, the contract for vessel crewing was able to be renegotiated preserving a multi-million-dollar contract and saving the organization untold millions.