Monthly Archives: April 2012

SphInX ‒ A pharma KPI tracking dashboard [1/5]

Hi everyone!

You probably are already aware of what MS Excel means to us CRABS‒ if not, I can tell you (and I’m not exaggerating here) that it made all of us outstanding performers at our workplace in a highly competitive environment  (& got us the highest appraisals :D). All of this became possible after we learned the true power of Excel and then used it to develop some revolutionary solutions for our Clients. Our aim here is to demonstrate the power of MS Excel and consequently, empowering our readers with the skill set to use it at their workplace.

I still remember the day I started working on my first project that involved IMS sales data. For those of you who do not know, IMS is a company that tracks sales and prescription data for practically every marketed drug on the planet among many other things.  I was handed over a simple dataset (sales data for a few products across different geographies) by my manager and asked to report KPIs for that market. When I saw the data, I asked myself—what shoud I do:

  1. Make many graphs and tables one by one from the dataset and place them on different sheets to represent the market scenario (which was expected from me) or
  2. Should I could make an interactive dashboard that would present the user with certain selection options to see the entire market scenario/analysis/graphs in a one page via a User Interface(UI).

Do I really need to tell you which road I took?

But I think it might be good if I discussed the  reasons why I chose the dashboard approach, and they were:

  1. A dashboard presents the analyses in a single view and eliminates all navigation issues i.e. moving across sheets
  2. A dashboards allows filtering of data i.e. the user can slice and dice the data in numerous ways  to suit his/her needs
  3. Comparison between different scenarios is easier by selecting different parameters on the dashboard interface
  4. It is way cooler, and the appraisal cycle was closer :)

I faced a lot of difficulty in assembling the required knowledge for creating that first IMS data based dashboard. So, in this post I’m going to talk about the process of creating a KPI reporting dashboard and everything associated with it‒ we’ll cover the process exhaustively from the beginning to the end in the following phases:

Phase I:    Data cleaning and processing—will make the raw data ready for calculations and then apply formulas to get the desired field values

Phase II:   Finalizing and creating some KPI reporting graphs that capture the entire market scenario

PhaseIII: Designing the dashboard and adding some really cool features (which I’ll reveal as we progress)

And we will call this the SphInX project.

Though this dashboard is going to be specific to IMS data, it can be used to represent/analyze the sales data of any industry with minor tweaks.

Oh yes! There’s one minor detail I missed— we’re going to create this dashboard using only formulas and no VBA. The reason for this is that through this project  I want to unveil the power of Excel formulas (from basic to advanced) and demonstrate how they can be used by the pharma and other industries. Moreover, formulas are inbuilt into Excel and run much faster for a medium sized dataset than a VBA code.

Stay tuned for the next update on SphInX.

Till then, keep Excelling !

CompTraX – A Microsoft Excel-based company profiling tool [2/5]

As promised, I am posting the first version of the Microsoft Excel-based company profiling tool—CompTrax. You can download it by clicking here.

Now let’s discuss the details. I have made the tool in-line with our earlier discussion (about the key fields in any company profiling request). So let’s start with the interface.

layout of the tool

I have kept it really simple—all one needs to do is select a company from the drop-down box on the right-hand-side top corner and everything changes accordingly. There are four main sections to this dashboard 1) company overview 2) recent news 3) pipeline molecules details and 4) financial details

Overall the dashboard follows a three sheet structure. The display sheet is where the user makes the request (the drop-down), the processing sheet is where all of the relevant data is fetched by simply using formulas, and the database sheet is where I have placed all the company specific data. I have put the data for only three companies here, but you can increase it to any number you want—the tool is completely flexible on that front.

layout of the CompTraX

While the display and the database sheet would need inputs from the user, I would ideally hide the processing sheet, if I were to send it to someone—unwanted changes here would result in tool malfunction.

So, this is how the tool works:

CompTrax in action

Ok, let’s list down the different formulas, objects and tricks that I have used for this tool:

  1. Formulas : CHAR, OFFSET, IFERROR, MID, FIND, LEN, SUBSTITUTE,
  2. Objects: Combo Box (Drop down)
  3. Tricks: Named range,Dynamic chart

The basics for all these MS Excel tips and tricks can be found on the Dashboard-101 page. I will discuss the more specific ones in my next post. But if you want to explore some of the slightly complex formulas I have used in this tool, you may want to explore the section in the ‘Processing sheet’ that deals with ‘recent news’.

CompTraX – A Microsoft Excel-based company profiling tool [1/5]

During the early phase of my career as an analyst, I used to make a lot of company profiles. These profiles served a multitude of purpose. They were used by:

  1. Marketing and sales department in Pharma companies to keep a track of their competitors
  2. Business development managers to evaluate licensing opportunities
  3. Investment banks to evaluate investment option
  4. M&A consultants to boil down on companies of interest
  5. Procurement department to evaluate suppliers
  6. And many more…

Though the profiles made for each of the above mentioned purpose varied by the need of the project, almost all of them had the following sections in common:

  1. An overview—gives a brief description of the company
  2. Financial details—revenue, profit, R&D expenditure, geographical break-up of revenue, revenue from key molecules, etc
  3. Key pipeline molecules—details on key molecules and phase of development
  4. Recent development—mergers, acquisition, in-licensing deals, drug approvals, legal issues, etc

Most of these profiles are sent out either as an MS PowerPoint or MS Excel deliverable. The MS PowerPoint deliverable would have a deck of 10-50 slides and MS Excel deliverable would usually have a single sheet with many rows and columns of data. While the PowerPoint slides get really bulky at times, the Excel deliverables usually become too boring—especially if one is tracking 30-40 companies regularly.

Aim of the CompTraX project

The aim of this project is to show our viewers how to build a robust Microsoft Excel-based dashboard/tool that will make company profiling easier and interesting. I intend to do this in 4 phases:

Phase I:    Make a simple dashboard—will keep it really simple here; just formulas and some nice visualization

Phase II:   Jazz it a bit and add utilities by using some VBA (simple one; not too complex)

Phase III:  Use some Excel to PowerPoint automation (this is going to be real fun; trust me)

Phase IV: Take automation to the next level –when we reach there we expect to deliver a true utility

Since this may not be a turn-key solution for all the profiling needs, I will try to explain all the steps involved in tool creation in as much detail as possible.  So,  it will not only be easier for everybody to follow but will also make sure that everyone understands the technology and technique behind it—then one can easily make minor changes to the tool to fit his/her need.

So, stay tuned for the next update on CompTraX

Phase III:  Use some Excel to PowerPoint automation (this is going to be real fun; trust me)

Phase IV: Take automation to the next level –when we reach there we expect to deliver a true utility

Since this may not be a turn-key solution for all the profiling needs, I will try to explain all the steps involved in tool creation in as much detail as possible.  So,  it will not only be easier for everybody to follow but will also make sure that everyone understands the technology and technique behind it—then one can easily make minor changes to the tool to fit their need.