Situation

​

Our client's GTN forecasting processes were managed completely in Excel. Every quarter, users would

​

• Gather demand forecasts by brand

• Gather WAC price schedule by brand

• Collect actual transactions (millions of lines) across Chargebacks, Medicaid and Rebates

• Load data to Excel per brand, per contract

• Review actuals, locate & adjust previous assumptions, update projections by brand, by contract

• Consider book of business scenarios and contracting events requiring assumptions and adjustments

• Consolidate results; Manually prepare summary comparisons and reports

• Provide data to downstream processes (e.g., Finance, Brand Teams)

​​

This process involved 150 separate workbooks, thousands of worksheets and more than 10,000 manual interventions performed across all workbooks, leaving them exposed to multiple risks for the future:

​

• Limited visibility to the effects of pricing actions (price protection penalties, Medicaid liability implications, PHS price changes, ASP-based reimbursement impacts, etc.)

• Long turnaround times for what-if analyses

• Excel scalability constraints and brittleness

• High risk of manual error

• Lack of visibility to previous data overrides and assumptions

​​

Solution Implementation

​

Implementing a more comprehensive GTN forecasting solution required analysis and redesign across data, methodology, process and people. These activities focused on a well defined set of objectives:

​

• Improve accuracy by enabling top-down and bottom-up forecasting methodologies down to book-of-business level (i.e., off-invoice, base rebate, performance rebate, price protection, admin fee)

• Provide scalable data architecture

• Increase efficiency, reduce turnaround times

• Enable seamless generation of summary comparisons and reports 

​​

The project was a partnership between:

​

• Manufacturer

• Pharosity Consulting

• Package-Based Software Vendor

​​

The effort achieved the following outcomes:

​

• Data architecture supporting desired forecasting granularity, reporting and analytics, including sub-price group extraction of load of actuals from Model N Flex

• Establishment of several different methodologies for forecasting utilization

• Clear visibility to data for pricing actions, adjusted contract terms, new strategies (rebates, price protection, fees, URA, PHS, etc.)

• Automated return rate derivation based on weighted average shelf-life of lot level data by product and calculation of return rates using time-adjusted sales

​​

The Pharosity Consulting team provided all business analysis, methodology design, data architecture and integration:

​

• Designed and implemented interfaces for actuals from Model N Flex

• Defined and documented all GTN policies, processes and methodologies

• Served as an extension of manufacturer team, acting as an advocate, process expert, data expert, and methodology designer

• Facilitated relationship between manufacturer and package-based software vendor

• Designed, developed and implemented return rate derivation solution

• Served as test lead, developed test strategy and plans, facilitated execution of system test and UAT phases while providing issue resolution support

​​

If you'd like to learn more about how Pharosity Consulting can help you decide which package is right for your GTN processes; or if you are looking to implement a specific package-based solution, please get in touch. We're always willing to share and look for ways to collaborate.