Tag Archives: IMS data

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.

Database

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