About Me

My photo
R.K.Laxman's Common man!
Showing posts with label Stats. Show all posts
Showing posts with label Stats. Show all posts

Sales Forecasting

Making SFE Work for Field Force

Presentation during #FFE2016 organized by Medicin Man Mr.Anup Soans - 16th Feb 2016 on "Making SFE Work for Field Force" FFE 2016 

Mean,Median and Mode - When and Where?

If you know only 'Average' and do not know much about the words  'Mean', 'Median' and 'Mode’, you are lucky!!  Those who know, always get confused about  When to use it? Which measure to use?
However, clarity helps understand things better.
Let us take the classical example of average income in two localities in Mumbai and try to understand these....
     Locality 1, say Mulund where multi-storied flats co-exist with slums around it.
     Locality 2, say South Mumbai where only Multi-storied flats exist and no slums.
Mean a.k.a Average
When to use Mean?  Mean is also called Average. 
You average out when things are similar or symmetrical. Isn’t it? 
So, in the above-mentioned localities where do you use mean? 
Obviously, in a locality where you find similarity in the population.
i.e. Locality 2 – only multi-storied flats.
Mean is the best measure when the distribution is not skewed.
This means, when symmetry exists use Mean.
Median
Median is the best choice in locality 1 because the distribution is skewed.
(High Income of the people residing in flats and presumably low income in people residing in slums). 
Few People who have disproportionately high income and few with very low income (these are called Outliers) can completely skew and distort the picture.
So , the moral of the story is next time when you are offered a job ask for the median salary of the post rather than the average salary for the post offered!
Mode 
Mode is most recurring or most often seen.
Mode is the least used measure of central tendency. 
Often you find it is not very meaningful.
Lets take the example: The salaries in an office with 6 people are as follows.
Rs.10,000, Rs.10,000, Rs.7,590, Rs.7,550, Rs.2,200 and Rs.7,565.
So, the Mode is most recurring i.e. Rs. 10,000 ( it appeared Twice).
This is not reflecting central tendency. It’s meaningless.
But where do you find Mode useful?
Let’s take this example which may be more meaningful for Gentlemen!
You are standing in a typical Mumbai Bus stop and watching women's dressing!
A Sari , Jeans, Chudidaar or Skirts.
At At the end of the day, if you are not arrested by Police, you come to a conclusion, that most women in Mumbai wear Jeans! (Quite an insight).
So, Mode is the best measure for Nominal data (such as Sex, Race, Ethnicity, Country etc.)

To summarize,
Mean is the Average, Median is Middle and Mode is Most often.
Mean tells the basic character of a population. It does not represent an individual from the population. In reality, you may not find anyone matching the mean in the population.
Median represents a typical Individual from the population.
Mode is most often observed.
Learn with this simple song for a better understanding..

Concentration Curves : Tool for Resource Optimization

Every organization in these times is working against odds. One of the major actions of organizations during tough times is Ephemeralization or  "Do more with less"
During these tough times, organizations need to constantly evaluate their resource utilization and undertake  Optimization of the process so that you can really "Do more with less".
To me, resource Optimization is not cutting resources. It’s putting resources to good use.

 This principle holds good not only for organizations but also individuals who are attempting to optimize resources available to them. All you need to do is identify is the resources!

There are many tools to undertake work towards Resource optimization. One of the easiest and fastest is creating "Concentration curves".
Once you understand the basic concept, you can use it with Resources and Outcomes like Sales Vs. People deployed,  Sales Vs. Expenses Sales Vs. Time invested etc.
Concentration curves are simply a Graphical representation of Cumulated Outcomes Vs Cumulated Resources. In the graph,
     ~ The Vertical axis(Y-Axis)holds Cumulated Outcomes and
     ~ Horizontal Axis (X-Axis) holds Cumulated Resources.
So at a given point on the graph, you know exactly how much is the Outcome Vs. Resources were used to generate the Outcome. Take a look at this example on concentration curves of GDP of  181 countries and their contribution to World GDP. You can observe that 35 Countries Contribute to 90% of the World GDP and the Rest of the 146 countries contribute to a mere 10% of the World GDP.


So the information you get will be something like this.
~ 20% of the field force give us about 80% of the sales
~ 40% of the money spent  on the brand gave us 95% of the sales
~ 25% of Territories are responsible for 70% of the Sales generated
~ 20 Members out of 125 salesforces produce 75% of the Sales.

Does that ring a bell?  Yes.. 
It's akin to Pareto Chart,  Lorenz Curves  for Gini Calculations.
You may refer to my earlier post: What is GINI ? for more information

Coming back to concentration curves, Here is an example. 
This demonstrates World GDP with GDP of countries on Y-Axis  Vs  No. of Countries on X-Axis.
I have created a Template for you. You can create concentration curves using your own data.
Here are 3 simple steps
  1.Paste data in coloured cells. The rest of the computations are automatic.(In fact computations are simple)
  2.Tweak the slider to match your data (Shown in downloadable excel file)
  3.Read values at each data point by using the slider. 

Download the Concentration Curves Tool  Here
This is a DOCOMO stuff! (Download,Copy and Modify at your will...You can find them @Downloads

Five Point Summary : Box and Whisker Plots

One evening, when you were reading a newspaper and your ten-year-old son came to you and announced that he scored 90 marks in Mathematics and scored 80 in English and rushed to play cricket. 
You were Happy and Sad at the same time. If 90 makes you Happy and 80 makes you sad, Think again!
Well, the next day when visited his class, you received new information.
In Mathematics, everyone in the class got more than 90 marks and the class average is 95. In English, everyone is below your son. He is the topper! You are Happy and Sad again!
Well, that means more information, more clarity.
More the information does not mean all marks of all the children in all the subjects. 
So, you need a summary.  The best summary that can describe data is the 5 Point Summary.
What are those five points? ( The simple definitions are  from wiki.paranormalcop.org)
   1.Minimum - the value above which there is 100% data
   2.First Quartile - the value above which there is 75% data
   3.Median (second quartile) - the value which divides the data set into two equal halves, 50% above and       50% below
  4.Third Quartile - the value above which there is 25% data
  5.Maximum - the value above which there is no data.
These five points can be represented in a graph that is called Box and Whiskers plot.

Creating Box plots in Excel are quite tricky as excel does not offer a straightforward chart type for the same.
Here is a Box and Whiskers Plot template created for you.
You can create up to 10 plots. 
Download the excel from @Downloads .
This is a Do ..Co..Mo stuff: Download, Copy and Modify at your will.
Few Ideas to get started.
1. Plot employee performance scores before and after training. You may be able to find out the training impact!
2. Plot employees Incentive earnings of last 2 years… You may understand the truth of employee earning!

Here is a wonderful article named  Visual Presentation of Data by Means of Box Plots 
by D.L. Massart,a J. Smeyers-Verbeke,a X. Caprona and Karin Schlesierb

Here is another great resource for special excel charts.



Text Visualizations with manyeyes

Text and Data visualization ? Try many eyes.
You can explore and create spectacular and compelling data visualizations. The name of the site is apt as you can view data through "manyeyes".
You need to register to log in to use your own data. 
well in addition to normal visualizations, the best part of the site is text analytics. This is quite important in the context that everyone is viewing to get insights of from huge piles of data generated on social media! Bringing out essence from text is made possible through various text analytical tools available.
Here, I present you the way religions describe The God. I have taken from Internet how various religions describe The God.
Sabka Malik Ek......means - One God Governs All
This is how It appears if I create a Word Cloud without editing data!

This is an example.
You can load your own data like text of your own speech, text from an article etc. and analyze the essence of the text!
Just to ignite your thoughts... 
~ Use the brand message text...
~ Use your CEO's Speech...
~ Use your brand detailing story...
~ Use text from your own presentation....
~ Use Ted Transcripts
I recommend Word Cloud  and Word Tree 
Word Cloud to know what the audience will hear from you (Top most repeated words. Obviously! )
Word Tree to know how a word is contexted.
Visit the site Watch the Video to learn more! Click the link. 

Numbers numbers all the way!

You often hear statements like "Our company has 2 billion dollar turnover"
Do you know what a billion is ? Can you write down the number ?
I guarantee that most can not write this most often used number .I have an easy way to remember!

1,000 is 10 to the power of 3 - so to say one followed by 3 zeros 
                 i.e 10 X 10 X 10 written as 10^3

1,000,000 is 10 to the power of 6 is  Million....
1,000,000,000 is 10 to the power of 9 is a Billion 
1,000,000,000,000 is 10 to the power of 12 - Trillion

The trick is remembering  the 3 Table
  • 10^3 is Thousand,
  • 10^6 is Million,
  • 10^9 is Billion,
  • 10^12 is Trillion and
  • 10^15 is Quadrillion and so on....

Here are some fun facts:
Zillion : Means a very large number. This has no defined Mathematical meaning!

Googol : This is the number from where Our "Google" name originated!
googol means 10^100 ie 10 to the power of 100!, one followed by 100 zeros!

Googolplex :  Means  10^Googol ie 10 raised to the power of Googol
in Mathematical terms it is 10^10^10^2
I can not write this number @#$!%^&  
Sure, I am not going to get a job in a company that has a Googol or Googolplex dollar turnover!

Tree Maps

Have you ever heard of Tree Maps? In fact, a Tree map is a Big Brother of both Bar Charts and Pie Charts.
Tree-maps are a complex but powerful information visualization technique.
They were introduced in Shneiderman, 1992. Tree Maps are used to visualize hierarchical data as a set of nested rectangles.
As mentioned, the concept of tree-maps is basically from Ben Shneiderman an American computer scientist from University of Maryland as a solution to his problem of understanding and visualizing who is using how much of disk space ( It was 80 MB  of disk space shared by 14 people in those days ! )
You can read a wonderful article by Ben Sheneiderman titled " Discovering Business Intelligence Using Tree-map Visualizations.
Tree maps can show relatives size of each component in the hierarchy and also use color coding to show another attribute. Here is an example of a simple tree-map .
This picture is taken from Wikipedia
The size of Rectangle denotes the waiting time for patients in NHS Primary care Trust in UK.
There is  another variant of Tree maps that uses circles instead of rectangles. Someone called it as pebbles. Here is an example of a pebbles Chart.
This shows GDP Per capita of countries in PPP Terms in $ International
This is the same data in a Tree chart
Creating Tree-maps is a Complex work and need considerable skills . Here I provide you two easiest ways to create Tree maps.
      1. Use Microsoft Excel
      2. Use ManyEyes, an IBM Initiative to create and share data visualizations on Web
Visit my earlier Post on Many Eyes : Text Visualization-Many Eyes
Microsoft  Research Provides a Free excel Add-in, that makes the job of creating Interactive Tree Maps in Excel easy.
Here is the link to download Excel Add-in
1.Tree Maps Free Excel add in - Link 1

2.Tree Maps Free Excel Add in - Alternative Link from Microsoft Store.
for more advanced users who can handle xmls , Here is a site to download a Java based Tree visualization Programme. Here is the Link
http://www.randelshofer.ch/treeviz/
According to the web site,
The project currently consists of a file browser demo, which visualizes the file system with the following tree diagrams:
  • Hyperbolic Tree
  • Circular Tree-map
  • Rectangular Tree-map
  • Sunburst Tree
  • Icicle Tree
  • Sunray Tree
  • Iceray Tree
Try few Tree maps now....

The ' Z ' Graph for Sales - Short, Medium and Long-term Sales analysis - All at once at a glance!

Short, Medium and Long-term Sales analysis - All at once at a glance!
Sales progress is best shown in graphs.
Here is a consultant's style of showing Long-term, Medium-term and Short-term sales progress - all in one graph. All you need is, sales data for this year till this month, and the past 12 months of sales data.
The magic of the graph is it not only throws light on the long-term, medium-term and short-term, it also can show how good is your near future going to be!
Caution! 
Show it only if you have healthy & right data...if you are facing projector lens :-)
If you are the one on the other side of the lens , never forget to ask for this graph ;-)
This is called "Z" Graph.
Now let us see what are these long term, medium-term and short term sales progress
Long term sales progress is best represented by Rolling MAT
      MAT - Moving Annual Total (sum of last 12 months data - till this month)
e.g: MAT Jan-11 = Feb-10 + Mar-10 +……+ Dec-10 + Jan-11
      MAT Feb-11 = Mar-10 + Apr-10 +……+ Dec-10 + Feb-11
Medium term sales progress is best represented by YTD
YTD - Year To Date (Cumulative sales starting from the beginning of the year ..say JAN)
e.g. YTD Apr-11 = Jan-11 + Feb-11 + Mar-11 + Apr-11
       YTD Jul-11 = Jan-11 + Feb-11 + Mar-11 + Apr-11 + May-11 + Jun-11 + Jul-11
Short term sales progress is best represented by monthly sales
e.g. Jan-11 , Feb-11 , Mar-11 , Apr-11.....
Bring all the data on to a single graph, it becomes a Z-Graph.
Do not undermine the Graph, the shape of “Z”, the angle inclination, slope of arms of “Z” can through new insights.
“Z” Graph is best for sales dashboards.
Once you plot MAT,YTD and Month, You should get a graph that more or less looks like this…

The 
Red line represents Rolling MAT
Blue represents YTD and 
Green represents Monthly sales
Just to stir your thoughts, here I present you 3 scenarios!
The shape of the “Z” should give you the complete picture.

Here is one more idea to explore...
Plot your Months-To-Go and corresponding expected YTD and expected MAT figures as per the Targets/Quotas of Months-To-Go. You may get a perfect "Z" Ora a distorted "Z" like this.....


If the shape of "Z" is... as shown in the picture, it is obvious that risk is ahead unless you have a strategy to defy the trends!
All the best!
Download the excel workbook to understand better! Click Here
alternatively @downloads on this website
It's a DO.. CO..MO.. stuff (Download, Copy ,Modify)

Acknowledgements and Reference article by David Straker
One visit to David Straker’s sites will make you a regular visitor.
....In his words….
Syque (pronounced 'sike') is my knowledge-sharing site.
My purpose is to share knowledge and understanding on an unprecedented scale, adding real value for individuals and companies. Consider it as 'original books on the internet', with already over 7,000 web pages of industrial-strength knowledge freely available.

Data Calisthenics : Gapminder Motion Chart - 5 ways to create these Motion Chart with your own data

Sometime back I wrote blog post on Gapminder.
It’s one of the most viewed posts. Here is the link for the same:
Having used and worked with the motion chart, I found that many are fascinated by the insights a Motion Chart (Gapminder) throws out. 
Boring stats Can be transformed into brilliant data dancing!
As I explore the Web world, I find many have tried various methods of presenting data the Gapminder way
Here I list out the possibilities I have come across.
It's a compilation of various methods of creating Motion Charts.
Each one is a masterpiece.. and efforts are worth a zillion claps....
The First: Use Google Docs
            The easiest way to make data dance.....
            Google Docs: MotionChart  : a Chart type in Google Docs
            Another link to explore is Quick Guide to Motion Chart
Here below is an example created by me with the Population and GDP of BRIC TM over a period of time.
The Second: Use Excel
            Making it possible in excel
Jon Peltier, author of the famous site http://peltiertech.com writes a post on how you can create motion charts in excel. Here is the link to his post
Jorge Camoes in his famous blog excelcharts. Here is his post
Anand from his brilliant blog http://www.s-anand.net/blog/
Here is the link to his post
The Third: Use Tableau
Andy Cotgreave in his Awesome Blog  Thedatastudio created a Motion chart with all possible paraphernalia on Tableau
here below are the links to his posts

 The Fourth: Use Crystal Xcelsius
             http://www.ideas2evidence.com/showcase.html
             http://www.ideas2evidence.com/demos/CO2-demo.html

 The Fifth : Use TrendCompass from Epic systems
          Trend compass is commercial software from Epic systems.
          Trend compass uses the basic idea of showing five variables in a single graph
          (X,Y,Bubble size, Bubble Color and Time) as a motion chart.
The user interphase is quite intuitive and simple to create Visualizations. However, for first time user, arranging data may be a challenge. The simple solution is to follow Trend Compass's instructions and their model of Excel to arrange data.
Trend Compass helps you present your business data from your own desktop without the hassle of connecting to the internet and hopping between presentation and web.
Here is a demo of Trendcompass from the website http://www.epicsyst.com/test/v2/mastercard_vs_visa/
You can download a demo version that works for 30 days....

 For those who want to start from the basics, you can watch the video below from Steve

Last but not least...

 You can download Gapminder for desktop
Yet another great site for data is OECD- Factbook
Though You can not use these with your own data, You have enough and more to explore..
Now...Play..... 

Sales Forecasting : Easy and Simple



Sales Forecasting is a Paradox. 
Theoretically, Faaaarther the Period, Poorer the Reliability.
Whereas, in the real world its the opposite...This month, not very sure; year end, guarantee. ;-)
I may not solve the Paradox but, here is a easy and simple tool to forecast sales.
Two simple rules and a bit of Stats were used to create this tool in Excel.
       ~ The near future most likely will be an extension of our known past....
           Like we know that tomorrow should more or less will be like today...The Armageddon is far away
       ~  Seasonality exists in sales and the reason may not always be the Mother nature
           Like we know soft drinks sell during summer and on a sales closing day, we do more sales
Download the Excel and take a look at the Powerpoint to understand and use the tool.
This is a DOCOMO stuff! (Download,Copy and Modify at your will...You can find them @Downloads

Computing trendline values in Excel

Forecasting is an Art,Science,Social studies,Maths and more ! 
From www.Forexmillion.com
Use of trends can make forecasting easy ! And God's gift to corporate mankind can make it easier !( Well, I 'am referring to Excel)
However, the trend line in excel does not display the values it holds. Computing the values on excel is an extremely confusing stuff .
You need to use innumerable formulae in excel to arrive at the values.
Just to make the job easy, here I present a way to do it. I have also attached an excel file link in the presentation. You can download the same and use it.
Alternatively you can download it from my BOX: Trendlines.XLS
Computing trendline values in excel

Knoema : The Knowledge Highway

Knoema is all about up-to-date World data , Visualization and Download.
It's Brilliant. Lay hands on the most updated Public data with visualization tools and Download the Stuff !! :-)

 This is what Knoema  Says about itself...
Knoema is a knowledge platform. The basic idea is to connect data with analytical and presentation tools. As a result, we end with one uniform platform for users to access, present and share data-driven content.
This is what Wikipedia Says..
Knoema provides access to a large number of databases and provides visualization tools for use with those databases. The visualizations can be either publicly-accessible or in the case of corporate customers, may remain private. Knoema also works with individual groups to provide visualizations for that group's projects.[2] The company has development teams in Perm, Russia and Bangalore, India.

 Watch the Video to Explore....

Hans Rosling's 200 Countries, 200 Years, 4 Minutes - The Joy of Stats - BBC Four

20 Vital Sales and Marketing Metrics

Today, we have abundant data, better information, fair knowledge and poor insight. Encountering wisdom is a rare occurrence! 
The data is out there on our spreadsheets, databases and hard disks.
Here are 20 simple effective metrics for a Sales Manager and a Brand Manager.
It is not all these metrics, probably an ideal combination of one or two of these metrics for a given business problem can show a direction - The right direction. 
The wisdom is finding out the combination. I leave the choice to you.
Lastly, as someone aptly puts it,
  • A collection of data is not information.
  • A collection of information is not knowledge.
  • A collection of knowledge is not wisdom.
  • A collection of wisdom is not truth.
Do not hesitate to click the link and download the PowerPoint.

Google Insights

When people have internet access, What do they do? They Google!
Google captures the words that are used to search...
so with the power of the words that are used to search, Google can predict and plot trends.
Simple isn't it?
With this power, Google predicted the flu trends  even before Centers for Disease Control and prevention could present the flu trends.
Google showcases the power of word searches in a simple and amazing tool called 
Google Insights for Search
Go and get the insights.
Here are the videos.. get a hang of it...and Explore....
Watch this video for an overview of Google Insights for Search.

Watch this video to learn how to create effective advertising messages using Google Insights for Search.

Watch this video to learn how to evaluate brands using Google Insights for Search

Watch this video to learn how to measure the impact of your campaign using Google Insights for Search.