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.
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.
Let us now discuss the tool structure along with all the MS Excel formulas, objects and tricks used for building it. As you know we, at Crab Sheet, follow a dashboard three sheet structure ─ D3S structure. The section below briefly describes the LaunchTraX D3S structure:
This sheet acts as an interface between the user and the datasheet. In this version, I have provided the drop-down to select geography, which will act as our report filter, and the resulting analyses – Sales since Launch Month – will follow accordingly. In addition, a fully dynamic 2-d matrix summarizing the list of brands along with their launch dates in the available geographies – I call it the LaunchTraX Matrix.
I will keep on adding new features and analyses to LaunchTraX in my subsequent posts − the idea is to build it along with all of you to make it easy to understand.
This sheet acts as a linkage between the Display Sheet and the Data Sheet by fetching the requisite data according to the selections made by the user. This control sheet has been made completely dynamic with the help of some awesome MS Excel formulas and hence should not be altered manually (should ideally be hidden to avoid tool malfunctioning).
Crab Tip: Geography & Brand unique lists have been calculated using a single-step formula (refer to Column ‘A’ & ‘B’) – I would explain this awesome formula along with other ways of filtering unique entries in MS Excel separately. Till then, try and explore this formula on your own.
This sheet comprises sales data for different products of a specific therapeutic area available across geographies. Moreover, the monthly launch dates for each of these brands/entries have also been calculated using a single-step formula – these launch dates act as the base for all our Launch Tracking analyses. Automating this step using only MS Excel formulas (without VBA) actually narrows down the manual effort to one thing – Pasting the updated data in the Data Sheet.
The formulas, objects and tricks used so far in making the LaunchTraX tool have been listed below:
- Formulas: INDEX, OFFSET, MATCH, COUNTIF, IF, IFERROR, ISNA, ROW, TRANSPOSE (The array formulas are used in combination with above formulas to automate tasks that are usually done using VBA)
- Objects: Combo Box (Drop down)
- Tricks: Named range, Dynamic chart, Conditional formatting, Array formulas (Unique List generation & Launch Month calculation)
Refer to Dashboard 101 section for the detailed explanation of MS Excel tips/tricks required for dashboarding and feel free to contact us at firstname.lastname@example.org in case you have any queries related to LaunchTraX or MS Excel in general.