Category Archives: Pharma Dashboards

Create an awesome responsive chart for your dashboard in 10 easy steps

Hi friends,

Today I will show how you can create a cool excel chart for your dashboards that responds to mouse over – yes, no clicking required. The final chart is going to look like the one below.

Animated Graphs

This can be a very useful technique to show some important data points related to your main series. For example in the chart above I am showing the profit made for each year when the user points his/her mouse over the relevant series.

So, let’s see how this is done Continue reading

Pegasus‒A VBA based Drag and Drop tool in MS Excel [1/2]

Hello friends..Happy New Year to all of you!

First year in a B-school takes its toll on you. But now, as things have settled down, I’m back.

This time around I’m back with something new—something that will take your MS Excel skills to the next level. We are going to create a drag-and-drop tool which would allow your deliverable to be more interactive. As usual, I’m going to give you a glimpse of what we’re going to develop and follow it with a post explaining how we develop it. And yes, I call it the Pegasus :) .

Our drag-and-drop tool would be a solution for pharma analysts and would help them to analyze the drug pipeline for Pharma companies in a very novel and effective way. We would do a simple two dimensional analysis which would allow the user to view the number of molecules by their mechanism of action that various pharma companies have in their pipeline.

You would ask why to go for such a complicated solution when we can simply use dropdowns to create this solution. Well, my aim here is to demonstrate how to build drag-and drop functionality in MS Excel which will open a whole new range of possibilities for you in the long run. Moreover, it looks and feels amazing!

Just to give you a peek of what we will be learning, I’ve uploaded a video (shown below).

The list below is mentioned just to tell you what all we will be using in to develop this tool: Continue reading

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

Hello readers!

I’m back with the final version of SphInX. In this post, we will discuss the process of exporting our results on the dashboard, to a MS PowerPoint presentation.  Thought this has already been covered by Keshu in the final post of CompTraX, the major difference in this post would be that in CompTraX, we exported images from Excel to PowerPoint whereas in SphInX, we will create editable charts and editable tables in the PowerPoint presentation to represent the analysis (click here to download the dashboard with the PowerPoint template; .zip, .xlsm)

The video below shows the tool in action:

This approach of making editable slides would solve two purposes: Continue reading

LaunchTraX – A Post-Launch Brand Performance Tracking Tool for the Pharma Industry [2/5]

Hi Guys!!

With this post, I am uploading the first version of Post-Launch Brand Performance Tracking Tool ─ LaunchTraX (Download LaunchTraX_v1.0). Before jumping to the tool, I would like to share a small concept used in the pharmaceutical industry for the launch performance tracking.

Launch Date Standardization

Sales performance tracking generally involves comparing value/volume sales of brands for same time periods (e.g. Year 2011 sales of Drug A vs. Year 2011 sales of other drugs). However, in case of newly launched brands, it would make more sense to compare the sales of brands for equivalent months/quarters of launch, so as to relate the post-launch growth curve of different brands irrespective of their launch dates.

For instance, if I have recently launched a new drug (say in Jan 2011) in the Hypertension market, I can compare my drug’s first-12 month (Jan-11 to Dec-11) performance with one of its competitors (launched in Jul 2007) for the same period i.e. Scenario1 or with the competitor’s first 12-month (Jul-07 to Jun-08) i.e. Scenario2. These equivalent months of launch are represented as M1, M2…., Mn.

Launch Date Standardization - Scenarios

The Scenario2 is usually preferred for new brands to benchmark their post-launch performance with the competitor brands in the same market, thereby helping companies in fine-tuning their strategies. Continue reading

LaunchTraX – A Post-Launch Brand Performance Tracking Tool for the Pharma Industry [1/5]

Hi Guys!!

In this project, I am going to showcase the importance of MS Excel automation in the post-launch tracking of drugs in the Pharmaceutical industry. Although this launch tracking dashboard/tool is going to be Pharma specific, it can be used for other industries as well, with minor tweaks.

Post-marketing and performance optimization of the newly launched drugs are the most important aspects of the drug development cycle in the Pharmaceutical/Biotech industry. It is quite crucial to determine the planned/anticipated effects of all such implemented strategies on a regular basis, so that the companies can make a change in their post-launch strategies (if required).

Despite having well-designed KPIs at the centre of each drug launch, only 2-3% of these newly launched brands excel in today’s pharmaceutical environment. One of the few reasons for this is that companies fail to define and track post-launch success of their brands. Hence, development of launch tracking performance metrics/ dashboards is very important to:

  • provide timely reports to senior management on Sales performance KPIs such as gain/loss in brand share vs. market
  • resolve deviations from already implemented plans in a timely and effective manner
  • fine-tune brand strategies due to potential risks from other competitor brands Continue reading

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

Hello friends!

Lets start with a Recap: till now we have talked about what SphInX is and how it is structured. Let us come to the part where we get our hands dirty  ‒ the construction and working of this Pharma KPI tracking dashboard.I’m sure that by now, you must have explored and played around with the dashboard‒ just in case you haven’t, here’s the Dashboard link again.

Before starting the development of any dashboard, it is very important to have an idea of how the final output would look like‒ it’s a different story that it may not turn out to be anything as you imagine it in the beginning, but overall it gives you a starting point. For SphInX, my idea was to create something that would enable the user to view the country-wise sales trend of different molecules in different currencies—Euro and USD for now as it covers most of the geographies of interest from a pharma company point of view. So, I decided to take the country and currency as inputs from the user, based upon which the results would be presented.

You would know that we always follow the three sheet structure when it comes to MS Excel dashboards:

  1. The Data sheet
  2. The Processing sheet
  3. The Display sheet (User Interface)

Lets take them one by one.

The Datasheet

To keep things simple, I chose a pretty small and a simple dataset for SphInX ‒ as we progress, we’ll add complexity to the data. It is a sample IMS sales dataset with value sales for some products in Euros and USD over a period of 10 months for three countries i.e. US, Japan and UK. The dataset is shown in the figure below:

(click image to see a larger image)

Different column headers have been marked in the dataset above and the each row represents a unique entry in the dataset. The first column (labeled 1) has been used to create a key by concatenating the Country and Currency values for every row‒ the value of this key alone helps me in differentiating entries which are different in either one of the parameters of country or currency. To delve deeper into the concept of unique key and data structure, you can visit this section in Dashboard 101 by clicking here.

The data here is first arranged by currency and then at a second level by country (for example, all values for US in Euros are together). This arrangement of data is of prime importance ‒ based on this, we will be extracting the data from this dataset. This arrangement allows extraction of data for a specific country and a specific currency (both selected by the user) in one go, using a single formula (we’ll see how).


The Processing Sheet

This is the brain of SphInX dashboard and controls how the data will be extracted‒ it links user inputs to the dataset. For the user inputs: I have chosen to take the country input from the user via a combo-box and the currency input in the form of option buttons. In order to process these inputs, I need them in cells on the processing sheet. I have described the process of retrieving these user inputs in the figure below (For more details, you can visit the Combo-box section of Dashboard 101 by clicking here).

(click image to see a larger image)

The Display Sheet

The user interface or the display sheet of the SphInX is pretty simply structured:

  1. The selectors for country and currency have been provided at the top
  2. Two charts‒ a line chart showing sales and a clustered column chart showing the market share have been added to see the market trend and the data for these charts has been shown in a table
  3. A table to view the mini-profile of molecules selected by the user has also been provided

The illustration below describes the features of the various components of this sheet.

(click image to see a larger image)

The mini-profile section of this sheet has been created using data validation and conditional formatting‒ the user can select the molecule using in-cell drop-downs and its details will automatically appear under the corresponding headers. Also, the legend for the charts has been created using conditional formatting since the number of molecules may change for every country and we would need to only view molecules for a selected country.

At this stage I would encourage you to download the dashboard and explore its formulas and features. If you have any confusion over the formulas that have been used in SphInX, you can visit our  Dashboard 101 section that has detailed explanations for all MS Excel features and formulas that have been employed for creating this dashboard.

I’ll use a different post to explain some of the formulas used  in the dashboard, such as the one used for creating a unique list that we have used in the mini-profile section of the dashboard. In my next post, I’m also going to add some new features to SphInX such as a third user input that would give the dashboard a new dimension; some conditional formatting to highlight  trends in the data and we’ll also touch upon some basic VBA to create simple outputs (at first) in a MS PowerPoint presentation simply, on the click of a button.

Till then, keep Excelling  !

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!  :)