Epidemiological Forecasting System

Situation: Top 10 pharmaceutical company had an epidemiological forecast model that was run biannually across multiple local markets for 2 key oncology brands. This needed to be expanded to incorporate additional brands, more involved reporting and analysis, as well as including a broader scope of local markets (up to 82 per brand) – whilst also being feasible for the global team to produce aggregated area, regional and global analysis. The solution needed to be agile and incorporate new requests without affecting the existing functionality, for example; it became necessary for local teams to make multiple submissions during forecasting cycles, and for them to interact with the live reporting and comparison of scenarios.

Solution: Initially we were asked to build the reporting component of the model outputs, which involved retro-engineering all aspects of the original excel model so that we could recreate all calculation steps and produce interactive reports and analysis in Tableau. The reporting dashboards provided comparisons between forecasting cycles and against targets, representing both the final model outputs across the short-term and full range of the models (up to 15 years into the future) as well as in-depth analysis of the assumptions input into the model (up to 23 assumptions, depending on the brand) – utilising tools such as interactive maps, cluster analysis, dynamic cross-tabs, waterfall charts, scatter plots and heatmaps.

Having successfully done this, we were then asked to also take over ownership of the underlying database build as well as the user authentication and submission system.  This was done within 40 days of the entire system going live. User authentication required enterprise level security protocols integrating the client’s multiple Active Directory systems & network, as well as being able to apply user permissions to determine which products users could access as well as their regional or country scope. Similarly, the reporting within Tableau required hierarchical permissions to control which views could be seen as well as row-level permissions with the views. Having taken ownership over the system we then also created automatic quality checks on all submissions and additional live reporting in Tableau to monitor system performance.

Outcome: The integrated forecasting system has been live since 2017, and now incorporates 5 oncology brands and 30 therapeutic indications across 96 local markets and over 350 users, with over 18,000 submissions made by users. It is used for internal brand planning & budgeting, other analytical projects with company and is integrated with other analytical databases and secondary reporting systems. In addition to the live & interactive Tableau dashboards, the database generates static PowerPoint & Excel reports for offline usage, and we provide differentiated support to global and regional teams.

More Info: If you would like to learn more about our forecasting solutions, or how we could help you with a similar project, please contact us.