Our Client, the Chief Actuarial Officer of a large healthcare Payer, had an urgent need to improve reporting insights into their predicted and actual membership. Challenging Trexin to help him develop an interim solution in less than 90 days, our Client asked us to create reporting tools based on currently available technology that could be maintained by his own staff. He also asked Trexin to provide guidance for future, long-term solutions to manage this membership data more efficiently.
Trexin began by gathering information through a series of interviews with our Client’s staff and an inventory of currently available software and tools. As a result of the investigation, we discovered that the problem was more daunting than we anticipated.
The staff had access to the raw data, but it came from three disparate sources within the company and was represented at varying levels of granularity. Complicating matters, the sheer volume of the information collected was too much for Microsoft Excel alone to process. Because of this, reports on this data were run manually and only on isolated sets of data, and those reports took weeks to complete. Faced with these challenges, our team was forced to consider some unconventional means to overcome them.
Because our Client wanted to continue to use Excel to run reports, our solution included PowerPivot, a free add-in for Excel that adds the ability to process larger amounts of data and pull it from multiple sources. We also incorporated R, an open-source analytics and statistical programming language published under the GNU General Public License that integrates well with Excel. R allowed us to clean and consolidate the data into a unified set. Once we had these technologies in place, we then worked closely and quickly with our Client’s staff to develop the solution and deploy it within the targeted 90-day timeframe.
Trexin delivered a suite of reporting tools that gave our Client the insight they needed into their membership data, eliminating the need to manually run hundreds of separate reports. Our Client’s staff can now accomplish in a matter of hours what previously took weeks to produce.
We also provided training and documentation for the processes we built around PowerPivot and R, so our Client can now not only maintain the systems, but they’re able to build on the tools we helped them create and continue to develop more efficient reporting methods without any additional spending on new technology.