Situation

​

A manufacturer had recently abandoned an implementation of a package-based GTN solution. The failure was the result of:

​

• GTN-specific knowledge and experience of the software partner was limited to a small sample of prior implementations for much smaller manufacturers

• The manufacturer’s data structures and volumes revealed multiple unresolved functional, technical and performance issues with the software solution

• The software provider did not possess the requisite knowledge of the manufacturer's source system data to properly transform and adapt it into their solution

• The software lacked flexibility to adapt to the manufacturer’s GTN methodology

​

In order to move forward with an alternate solution, the manufacturer established a set of primary objectives:

​

• Eliminate or greatly reduce the risk associated with new and/or unproven technology

• Address immediate, high priority GTN forecasting needs for the Commercial Managed Care and Part D channels

• Align functionality closely with the manufacturer’s existing forecasting methodologies and processes

​​

Solution Implementation

​

To ensure the revised approach would meet the primary objectives, the project team:

​

• Assessed the available options and technologies meeting the manufacturer’s desire for familiarity and reduced risk

• Reviewed the current situation, the issues that led to the failure, and design mitigation and remediation strategies into the solution

• Confirmed the desired objectives for the future state in light of the current situation

• Designed an efficient Excel-based model, backed by SQL Server and Power BI, with formulas and macros focusing on ease of use, flexibility, data integrity checks, appropriate data aggregation, and performance management

​

The effort achieved the following outcomes over a 12-week implementation:

​

• Leveraged existing analytics interfaces from Model N Flex to source and verify actuals, reducing interface development and testing time

• Enabled book-of-business level forecasting for account and brand combinations

• Implemented support for 4 utilization projection algorithms and nearly all price protection methodologies (including ESI’s Inflation Protection calculation)

• Provided clear visibility to the impacts of pricing actions and adjustments to contract units and discount rates on the GTN forecast

• Enabled dynamic reporting outputs to improve downstream partner interactions

​​

The Pharosity Consulting team designed, built and tested all elements of the solution:

​

• Defined the overall solution architecture based on the manufacturer’s forecasting model requirements and technology perspective

• Designed and implemented a SQL Server database for appropriate aggregation and storage of forecasts allowing the use of Microsoft Excel in a higher volume use case

• Built all solution components

  • Data interfaces from upstream sources

  • SQL Server back-end data model

  • Excel components (formulas, macros / modules / classes, PowerQueries)

  • Interactive Power BI dashboards

• Developed all testing materials and facilitated UAT execution

• Documented training materials and provided step-by-step walkthrough of model features

​​

If you'd like to learn more about how Pharosity Consulting can help you address challenges with an existing implementation, or build an Excel-based model to support your GTN processes, please get in touch. We're always willing to share and look for ways to collaborate.