Hi, this is Mike Negami, Lean Sigma Black Belt. Today, I'll talk about two items from
the 7 QC Tools: The Histogram and Stratification with an Excel template. A Histogram graphically
shows occurrence frequency of one numerical data group. You can understand which data
range is the most frequent at a glance. I made a Histogram Excel template that you
can download from my blog site, econoshift.com. This is the template screen. Enable 'Macro'
first. After opening your source data file in Excel, click here “Click this to generate
a Histogram”. Then, follow Excel's messages,…. and it will ask you “Select your data range
(Only numeric data)”, so select your data range and click ‘OK’. Then, Excel generates
a Histogram for you. If your data is prepared beforehand, it takes less than 1 minute.
This example shows returned products' GP%'s ranges on the horizontal axis and occurrence
frequency on the vertical axis. This also shows that 328 pieces of data were
used and that their average GP% was 22.83%. However, there are two bumps in the charts.
This is a typical example. It may include two kinds of data groups. This result “as
is” doesn't show any meaningful insight. So, I divided the source data into two groups
by product category and made separate Histograms. Here they are. They both now show meaningful
results. As you can see, dividing data and making separate data analyses quite often
discloses the truth. This is Stratification. You can apply Stratification to any data analyses,
in fact you have to. Otherwise, you might miss a critical data point. Therefore, always
keep Stratification in mind when you see data. Let’s return to the Histogram. The good
thing about this chart is that you can visually and objectively see that products were returned
with which GP% most. I compared the high frequency product return GP% with regular GP % average.
Return GP% was much higher than the regular average!
In other words, the price was too high so that it was returned. You may say “Duh,
that’s no surprise.” It’s important to verify the situation with data. Now you
may have another question: “Why were those products with high prices delivered without
customers' consensus?” Since you’ve verified the situation with data, you can move to the
next phase. There is one issue regarding data analysis.
Although data analyses are very important, there are people who spend too much time on
making charts. For example, their analyses may have many
nice graphs, but lack of actionable insights for the readers. Those analyses are meaningless.
Therefore, delegate graph composition to template programs, then spend your time on adding actionable
insights in your analyses for your readers or yourself.
I talked about Histogram and Stratification from the 7 QC Tools today. Thank you very
much for viewing. Please click the ‘Subscribe’ button for “Learn world-class Kaizen and
improve your work and yourself. Also click and watch other recent videos.