Tag Archives: MS Excel

Substituting the list box with formula!

I was discussing with my colleagues last week, the ability of excel formula to solve most of the problems that we have in our pharma analytic lives. To an extent he agreed but was more of the opinion that “formula are good but most of the time I find myself writing macros as they are easy to implement”, while I was more of the opinion that formula are very creative, fast, better than macros many a times, and we should use macros only when necessary i.e. for doing things that formulas are not supposed to do—to be very honest I find more and more blogs about people trying to exploit formulas and make them do things that were never thought of; but lets not go into all this.

While we were discussing all this, he came up with a challenge for me. 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 company profiling tool – small update

Hello friends,

The last couple of weeks have been hectic and we have been busy like bees, uploading the Dashboard 101 tutorials. This has kept me away from the blog—thankfully not for too long—and from the CompTraX updates.

I am back on track now and today, I would like to explain a set of formulas that I used while building the first version of CompTraX

You would have noticed that there is a sub-section on the Display Sheet of CompTraX that displays recent news.

spin button and news section

This section fetches the data from the Database Sheet with the help of a spin button (circled in red above). The cells which hold the relevant data in the database sheet look somewhat like:

snippet of database sheet containg news on pharma company

These cells have different news items in the same cell; separated by a line break (I have added color to different items to differentiate them in the pic). However, the Display Sheet shows the news items one by one on clicking the spin button. So, how is this accomplished?

The key lies in a group of four cells on the Process sing Sheet which looks something like this:

processing sheet

Cell 1: takes input from the data base sheet i.e. the group of news items

Cell 2: takes input from the spin button and helps decide which news item to show. E.g. in the image above the value ‘1’ means—show the first news.

Cell 3: has a formula that adds an identifier to demarcate the start and end of a news item. The formula is:

= SUBSTITUTE (SUBSTITUTE (A44, CHAR (10),”start1start”, B44), CHAR (10),”stop1stop”, B44)

This formula replaces the first instance of a ‘line break’ with the keyword ‘start1start’. Then it again acts on this modified string and replaces the first instance (which would earlier have been the second instance) of ‘line break’ with ‘stop1stop’. So we have a demarcated news item. Something like this:

‘start1start news item stop1stop’

Cell 4: has a formula which extracts this demarcated news item. The formula is explained below:

explanation of formula

It might seem a bit complicated at first look, so, I will try to explain it using another example—see the picture and the maths below.


Let’s say cell ‘A44’ has some text value. Formula in Step 3 adds a start and stop position to this string. Say this is stored in cell ‘C44’. Now, Step 4 extracts the demarcated news item from this string. As mentioned earlier, the entire set of formula is dependent on the spin button and with every click of the button the next news item is extracted.

That’s all for this post; hope you find this useful. In the next post I will upload a new version of CompTraX which would have a search interface. So, stay tuned and 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 !

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:

  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.