CPO Consulting

Challenge

A Research and Development group in a global biotechnology company needed to replace their old portfolio planning and analysis tool, which was built using Microsoft Excel. They wanted to keep the user-friendly aspects of Excel, but needed a group software solution with more control over the database and ability to use unique key performance indicators. They also wanted more advanced analytic capabilities to look at various aspects of the portfolio, and wanted a flexible tool that could be adapted as their portfolio management skills matured.

Solution

CPO Consulting studied the business need and recommended using a combination approach of Microsoft Access and Microsoft Excel. Access was used on the back end to consolidate and hold the data in a database, which users would not use. Excel, which was a familiar tool to the R&D managers, was used on the front end. Managers could enter and manipulate their proposed portfolio of projects, and score them using various proprietary criteria. The administrator consolidated the data using an automated process in Excel and Access, then produced multiple portfolios for different managers, business units, and divisions. Pivot tables and charts were used to analyze the portfolio to recommend funding priorities.

Benefits

This replaced an old system that required considerable manual manipulation, and was significantly less expensive than commercial off-the-shelf solutions. The pivot tables provided automated but flexible financial modeling and other business analytics, enabling better R&D investment decisions.