Monthly Archives: June 2012

Use dynamic charts to display special points/events on line graph

One of my colleagues came up with a visualization problem today. He was making an MS Excel dashboard for his client. The client wanted to track the sales of a drug across various countries (using third-party monthly sales data). The data itself was really simple—it had sales value in USD million for 10 months since launch of the drug in various countries.[To know more about the ‘months since launch’ way of representing data, follow D’s post on LaunchTraX].

To give you a better idea of the data set, I have created a mock data table for this post (lets call it the sales_table; M1 = one month since launch; M10= 10 months since launch).

His idea was to use a dynamic ‘line with marker chart’ to represent this. 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

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

Hello friends,

This is the final post in the CompTraX series.

As discussed in my last post, this update focuses on exporting all the companies of interest from the Excel tool to the PowerPoint template in one go. The tool now also creates a table of content in the beginning of the presentation. You can download the final version of the tool here (.zip ; .xlsm)

The video below demonstrates the tool in action:

This update is mostly dependent on VBA. One of the important code snippets for this tool is the part that copies the charts and relevant text fields (company overview, news item and pipeline information) from Excel and exports them on the slide: Continue reading

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

Hello friends,

Its time for a new version of CompTraX. But, let’s start with a recap first: In the last couple of posts we built a company profiling tool that allowed the used to feed data in one sheet (Database Sheet) of an excel workbook and then visualize it on another sheet (Display Sheet). This set-up insured a neat representation of data on MS Excel files. While this solved half of the problem (dealing with multiple column profiles) it did not solve the other half i.e. how to take all of this to a PowerPoint slide (after all, company profiles look best on PowerPoint slides).

So, for this post I have built a new feature in the tool—called Xport—that allows you to export profiles of your choice to a PowerPoint slide. Things are better experienced than read, so,  download Version 3 of the tool (click here to download) and extract the content of the zip file in a directory of your choice. Now open the CompTraX tool, select a company that you want to export (from the drop down) and click on Xport. Wait till you get a message confirming the completion of the export process.


CompTraX V3.0 in action

How does this feature work? Continue reading

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

Hello readers!

As promised, I’m back with some new features for the SphInX‒ click here to download the new version. I’ve added the following functionalities to the dashboard:

  1. An option to select a specific time period‒ the user can specify the start and end month using combo-boxes and everything in the dashboard would change according to this. To see how this has been done, you can visit the dynamic chart section of Dashboard 101 by clicking here
  2. Conditional formatting to help the user view the product with the highest sales in the last month of the selected daterange by highlighting the corresponding row. Click here to visit the conditional formatting section of Dashboard 101 page

The snapshot below demonstrates the new functionalities of the dashboard in action.

All the newer functionalities have been added around the time period input that is received by the user. Continue reading