Custom-Built Excel Model

Enabling Gross-to-Net through advanced Excel functionality

Excel Gross-to-Net spreadsheet. GTN solution built on Excel with PowerQuery, VBA, SQL Server, Oracle, Power BI.

In Brief

A manufacturer had recently abandoned an implementation of a package-based GTN solution. Seeking to address the resulting functional gap while significantly minimizing risk, Pharosity developed and implemented an Excel-based solution.

How We Helped

  • 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 designed mitigation and remediation strategies into the solution

  • Designed an efficient Excel-based model backed by SQL Server and Power BI, with appropriate guardrails to manage data integrity and volume / scalability challenges

  • Built all 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

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.

  • LinkedIn

© 2020 by Pharosity Consulting Inc

8 Atkinson Dr. #118

Doylestown, PA 18901

 

Mail: info@pharosity.com