Tag Archives: News Viewer

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:

  1. Formulas : CHAR, OFFSET, IFERROR, MID, FIND, LEN, SUBSTITUTE,
  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.