Enhancing Operational Efficiency - Hex
As a Data and BI Analyst at Shiftkey, LLC, I developed Hex dashboards for operations and other teams. I do not have access to those dashboards, but here I describe the process I followed to iterably develop a Hex solution.
The first iteration solved the stakeholder’s short-term problem, but each following iteration empowered him more.
Situation
A stakeholder from the Finance, Provider team requested a data pull. He planned on running an email VLOOK to reconciliate vendor’s Y report records in our warehouse to the report records provided by the vendor. For all the records that did not match via VLOOK, he would manually match based on first and last name assumptions; he planned on disregarding any of the records that he could not match.
The stakeholder also mentioned that vendor Y’s invoice was always a surprise because he had zero visibility into the number of reports they run for us - for forecasting, he estimated based on new users and other excel workbooks in his local machine.
Our requirements gathering meeting ended with a finalized user story:
“As a finance manager, I want a real-time visibility into report usage and spending, so I can proactively identify discrepancies between our records and the vendor’s invoices to ensure accurate financial reconciliation. I also want a historical report usage view so that I can compare to current report usage and forecast.”
Action
The first iteration solve his immediate problem: the reconciliation of the current months report usage. With Hex powerful .csv import feature, I was able to import the vendors records and directly compare them to our warehouse records via a unique record id - a field our stakeholder did not have access to.
At a glance, the stakeholder was able to see the total number of discrepant records. He was able to download a file with the details of each mismatched record.
The second iteration included the total MtD requested report by type and the total estimated cost.
The third iterations provided visibility into historical report usage and spending.
Result
With this dashboard we achieved the following:
Increased Efficiency - The dashboard eliminated 6 hours of monthly manual work load.
Decreased Operation Costs - The accuracy of his reconciliation significantly increased and the finance manager was able to accurately dispute any and all discrepancies, the company recouped $520K.
Accurate Budgeting -The monthly invoices were no longer a surprise, at any given moment the finance manager could see how much we were projected to spend for the month.
