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:
- 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
- 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:
- A dashboard presents the analyses in a single view and eliminates all navigation issues i.e. moving across sheets
- A dashboards allows filtering of data i.e. the user can slice and dice the data in numerous ways to suit his/her needs
- Comparison between different scenarios is easier by selecting different parameters on the dashboard interface
- 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 !