Archive for the ‘Analytics’ Category

Whenever we design any metric in Excel which requires input from different people then there are always chances that people enter wrong or inconsistent information which eventually result in lot of data massaging to get the data into correct format.

To prevent users entering incorrect or inconsistent information, data validation is a great tool in excel. As the name suggests, it provide users a conditional mechanism by applying the rule on the cells where the data need to be entered.

Let’s explore this with couple of examples.

1. Allowing cells to only accept the text values –

a) Select the range on which you want to apply the validation

b) Goto Data -> Validation -> Allow -> Custom

c) Under formula enter =istext(A1:A10)  … given that your range is A1 to A10 where you need to apply the validation.

d) Lastly press OK


2. Allowing cells to accept only limited no. of characters –

a) Select the cell range on which you want to apply the validation

b) Goto Data->Validation->Allow->Custom

c) Under formula enter =len(A1:A10)<6  …this will allow less than 6 characters in range A1 to A10.

d) Press Ok


3) Entering only unique values in a cell range

a) Select the cell where validation is required

b) Goto Data->Validation->Allow->Custom

c) Under formula enter =countif($A$1:$A$10,A1)=1

d) Press Ok

e) Copy the validated cell and paste it into the cells below where you need the unique below.

Note: In this formula we used $ sign to freeze the range where we want to apply the validation because when we copy the validated cell and paste it to the cell below and if the $ is not applied on range then the range will change and the validation will not produce the accurate results. Try do it by both freezing and not freezing the range.


Read Full Post »


Dashboarding is a tool that is used at several level of management for making informed decision. In its early days it used to be a static one where mangement need to go through many pages or links to view different information at different level of granularity. But now a days the technology has reached such a level that now you can create such cutting edge Dashboarding solution which can accomodate both the views i.e. at a very high level and the same dashboard can be drilled down to present the low level or reason behind a particular high level chart or Key Performance Indicator.

Below is my experience and thought process (with example) which helped me while creating a daynamic dashboard for Senior and Middle management.

In just one sentence dashboard can be said as “The overall summary of the Business or Program in one page with all required data points for decision-making”. Dashboards are basically combination of various metrics and analytical charts that are related to overall business and program which a particular company is running. One of the important feature of dashboard is the high level filter or filters that are related to almost every datapoint in the dashboard so that everyone from the senior management can see their required information in just one page. That’s why it has become very important to decide at the planning stage that what are the critical data points that we need to select for the dashboard and are related to the high level filters.

Example: There is an IT Vendor “True Soft” who is providing the Software Services to A, B, C & D companies. Now senior management of True Soft wants to have a metric or dashboard in place which can show the entire summary of their program with their clients. So for the designing of the dashboard we really need to see that what is the high level filter we should use and what are the critical data points that can be used in the dashboard. Now because senior management of True Soft wants to see the summary of their Program with clients A, B, C, & D that’s why it is very simple that high level filter would be on A, B, C & D but there would be another filter which would be showing the information for all the companies and can be named as “total summary” or “overall summary” for true soft. the filter can be combo box, or list box or 5 buttons etc. so that you can quickly select any one single company or all the companies and see their program. Now the next step is that what are all the critical data points. So for an IT vendor below are couple of data points which are critical:

1. Revenue: One of the hot favourite data point for senior management as they really want to see that from where the maximum revenue is coming so that they can better positioned themselves to ensure consistent revenue from that company and even more. And can work with the client which is generating least revenue.

2. Service Satisfaction Score: This is the score that come from a survey which they roll out to companies where they are providing the services. By this they can very quickly work on the pain points that they have with particular company and ensure the quality service delivery for that company. It can also help the True Soft to assess or analyze why the satisfaction scores are different for the different companies and in this way it will help them to understand the problems more clearly that are inside the True Soft and implement the best practices from where the scores are good.

3. Defect Delivery Rate: It is also uniform for all the companies and help then assess where the defect delivery rate is high so that they can improve on that area.

4. Attrition: This can help them assess the attrition that they will have with different companies.

5. Staffing: It will help them assess how quickly they are completing the staffing request for the companies where they are providing the services.

And there can be some other data points which one can create or use according to their needs and requirements. But what I’ve seen is that in a dashboard normally there should be 5 or less than 5 data points which are very critical in most of the business or programs that one can see clearly for the decision-making. In my view using the facts and figures for more than 5 data points will actually confuse the end-user and make him unable to take the quality decision. But situations or requirements can be differ so at the end, it really depends on what your business requirement is.

So it is not a rule of thumb that you really need to use 5 or less than 5 critical data points. And there can be 6 or 7 critical data points that are important for one’s decision-making but the best practice from my point of view is to show maximum up to 5 critical data points in your dashboard.

One other important point is that we should always leave some space for the user to make a comment in your dashboard about a particular metric or about any other figure that you have putted there that is effecting the business so that other users can also se his/her comments and can make good discussion around it, which will help them to truly utilize the dashboard at the max.



Read Full Post »

What is Analytics?

 There are so many definitions about analytics on the web but not sure why I found all those definitions like jumble of complex words to make this complex subject more complex. Or sometimes the definition is so simple like “Analytics is analysis” which means a lot but only for the one who has spent a good time in this field and can really understand the depth of this statement.

 That’s why I thought that why don’t I simplify this definition and give you a general understanding of what are all these definitions are trying to say but again in simple words. So I’ll start with the background of analytics word. It came from the word analysis which means that there are some basic rules or things on which you have done some experiment to relate them and then trying to come up with a conclusion that is of a specific nature to achieve certain results.

For Ex: You want to purchase a bike and that’s a basic rule or purpose. Now you collect the information about specific segment in that bike falls and figure out what is the best deal. And to figure out this you check what are all the companies available, what are their ranges in that segment, which bike is giving maximum mileage in that segment and having minimum price and then you trade off little bit between mileage and looks . So what you have really done is analysis to purchase a bike. And if your decision is worth and making you happy in the long run then you did a good analytics for that segment by doing the proper analysis.

 I believe this is very basic and general example to understand the analytics but my dear friend the subject is not so basic and general in nature but having it’s own breadth of segments and some segments have further sub-segments. Some of the segments mentioned below.

Marketing Analytics

Financial Analytics

Operations Analytics

Retention Analytics

HR Analytics

IT Analytics etc. etc.

So these are some high level segments which are again having some sub segments in it to provide the specific information about a part of that segment.

Hope you will like and as always comments and suggestions are always welcome.


Read Full Post »

What the hack it is ……why everyone is so keen in going this field ……..why most of the jobs coming only from this field………how it has become the so popular among every company???????????

These were the kind of questions that I had when I first heard about analytics around 3 or 4 years before. I was very new (or you can say new-born baby 🙂 ) in the field of analytics and at that time I didn’t know the “A” of Analytics. But after passing 4 years or so in this field now I think I’m in a situation where at least I can brainstorm around these questions and try to answer.

In simple Analytics is a way by which you identify new and innovative way of solving business problems and then presenting them in a way that the person can easily take the decision based on that presentation. When you are designing a such a solution then you just don’t come up with a single solution but at least a two or three solutions by which the business problem can be solved. In my view if you have more than three or four ways to solve your business problem then you haven’t understand your business problem at all and based on initial understanding you have just formed the N no. of ways to solve the problem. In such case one need to start from the scratch then figure out the solutions of the problem.

A true Analytics not just tell about the best solution of the problem but also about the implication of using the solution so that the decision maker can take all that corrective action which really need to be taken to implement the solution.

Earlier the company only used to have the reporting department where in most cases accountant were responsible for the reporting and they only presented the account related reports but when companies started operating across different geographical locations and with multiple line of products and businesses then it was really a necessity to have a dedicated reporting department in the company who could not only report about the finance but also about its overall health of operations, marketing, collections etc. and then companies started hiring those people who are from commerce and maths background and could do not only the finance reporting but also the management reporting. With this MIS (management Information System) advent. Companies spending heavily on databases and other tools which could help doing the MIS work and with this it was made necessary that the person should also have the good command over the computers apart from good education.

So for many next years companies collected data related to their operations and doing their reporting work but with the ever-changing market conditions and cut throat competition it was really a necessity to have something in place which can help the decision maker to make smart and inform decision to take advantage over its competitior. With such a necessity the field of analytics advent.

Today every company is having a dedicated research and analytics department and these companies are spending heavily on these departments so that they can create breakthrough strategies and take advantage over its competitors. And that’s why it has now become a such a hot field and generating many job opportunities.

As I said earlier that the term Analytics is very broad and so its field and today every banking, marketing, government, medical etc. companies are having their dedicated analytics department which are creating new and innovative solutions of the current problems.

My struggle is still going on related to my answers in the field of analytics where I’ve got the answers of some basic questions but there are still some next level questions which are in my mind and I’m trying to find their answers which I’ll keep posted here.


Read Full Post »