Tag Archives: Analytics

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

Hello readers!

Ready to impress your boss with the new MS Excel dashboard‒ just download the SphInX Dashboard and you’re set to go.

In this post, I’m going to give a detailed description of the structure of the dashboard and an overview of the components involved. I’ll describe the working and creation part in detail in the posts that will follow.

As always, we have divided the have followed a three sheet structure:

  1. Display sheet (User Interface)
  2. Processing sheet
  3. Database

The section below describes the three sheets individually.

Display sheet

This sheet forms the most important part of the dashboard‒ it is the sole point of interaction between the user and the underlying dataset. It doesn’t matter if you have analyzed your data to derive an extraordinary insight‒ it won’t sell if you’re unable to present it well.


Since SphInX is a KPI reporting dashboard across countries, I’ve created the selectors for only Country and currency selection to keep it simple but as we go on, we will keep on increasing the number of  visualization options available to the user. The analyses have  been restricted to 1) brand-wise sales 2) market share 3) molecule profiles.

Processing Sheet

This sheet forms the link between the display sheet and the data sheet. This is where all the calculations for the dashboard are performed. While making this sheet, we ensured that all the calculations are dynamic i.e. they change on changing the selections on the display sheet.


The database can be one sheet or multiple sheets depending upon the nature of the data being represented.  We have used two sheets with different types of data:

1)      Sheet 1 – IMS sales data sheet: This sheet has the sales data for different products across three countries (to know more about IMS data, visit their website)

2)      Sheet 2- Molecule profile sheet: This sheet has a mini-profile(Molecule name, Indication, Price, etc.)  for the different  molecules covered in the sales sheet

Working of the tool:

We’ll be using a number of formulas, objects and tricks in making the dashboard. They have been listed below and their detailed explanation can be found in Dashboard 101 section:

  1.  Formulas: OFFSET, MATCH, COUNTIF, INDEX, ISNA, ISERROR (We’ll be using these formulas in combination with each other and will also be develop array formulas to perform tasks that are usually done programmatically using VBA.)
  2. Objects: Combo Box (Drop down), Option buttons
  3. Tricks: Named range, Dynamic chart, Conditional formatting, Array formulas

We’ll detail on all of these in our next post. Till then you can play with the dashboard and explore its various components.

Keep Excelling!  :)

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 !