Organizing, Charting and Graphing Data: Creating Information for Better DecisionMaking

1
Organizing, Charting and Graphing DataCreating
Information for Better Decision-Making

• Using QI Macros in MS Excel

2
Importance of Data Information

• If you can not measure it, you can not improve
  it
• What gets measured, gets done
  Lord Kelvin
• (British physicist and engineer William
  Thomson)
• "When you can measure what you are speaking
  about, and express it in numbers, you know
  something about it, when you cannot express it in
  numbers, your knowledge is of a meager and
  unsatisfactory kind
• it may be the beginning of knowledge, but you
  have scarcely, in your thoughts, advanced to the
  stage of science, whatever the matter may be."

3
Importance of Data Information

• Managements job is to know Deming
• which systems are stable and which are not.
• Management is prediction. Deming
• The goal is to transform data into information,
  and information into insight. - Carly Fiorina
• Without data, you are just another person with
  an opinion . . .
• Terence T. Burton,
• Managing
  Director
• The Center for Excellence in
  Operations, Inc

4
Some Basics

• Any set of data (dataset) can be analyzed and
  charted.
• Chart Types
• Static Snapshots in Time/fixed
• Pie charts
• Pareto (bar chart)
• Histogram
• Dynamic Over some defined time periods/variable
• Time-Series data is most common type used for
  presentations to management
• The Standalone Test
• What, when, where, good/bad direction, data
  source, creator

5
Tips

• Purpose
• Whats the Point clear, effective
• Whats the Conclusion obvious, correct
• Misleading?
• accidently or on purpose
• Anticipate
• Audience Response (Your Expectation)
• Information (i.e. status) OR Action?
• Quality of data
• Reliable? (timely, accurate, sufficient)
• May need to clean or scrub the data (GIGO)
• Scaling
• for better or worse

6
Data Types

• Qualitative/Categorical/Discrete
• Counts, groupings, Yes/No
• Quantitative/Numerical/Continuous
• Measurements, fractional values

7
(Data) Tables

• Used to present summary info. in row/column
  format
• Provides several data points for a particular
  variable
• Typically used in research reports
• Data can be used to create other types of charts
  Pareto, Scatter diagrams

8
The Big 3 You Need to Know (KIS)

• Pie chart snapshot in time
• Break down by percentage
• Pareto chart (Bar chart) snapshot in time
• Vital few trivial many
• Breakdown by quantity
• Run chart time series line graphs
• Control chart variation
• Opportunities Normalize the dat
• - Premise behind Six Sigma dpmo

9
Pie Charts

• One of the more commonly used
• Easy to read Quick to the point
• Parts of the whole
• Information
• Usually for Qualitative (category) data
• E.g. survey results
• Snapshot of some defined Timeframe
• ALWAYS include both percent AND raw numbers (or
  Total amount)
• Check to make sure the percentages add to 100
  (rounded)
• Beware of the Other categories/slices S/B
  smaller than the other slices.

10
Bar Charts Pareto Analysis

• Displays individual values for comparison WITHIN
  a fixed timeframe
• Similar to Pie breaks down categorical data
  usually as numbers (quantity) vs. percentages
• Can also be used for comparisons (side-by-side
  bars)
• Can be used to show parts of the whole (stacked
  bars)
• A widely used bar chart for for Q. purposes is
  the
• Pareto Analysis, named after Italian economist
  Vilfredo Pareto, who, in the early 1900s found
  that 80 of countrys land (wealth) was owned by
  top 20 of the population.
• Made popular by Dr. Joseph Juran in the 1940s by
  demonstrating the 80-20 rule applies in many
  business/industrial situations (80 of a problem
  is caused by 20 of the causes).
• illustrates top contributors to an issue/problem
  Vital few, trivial many for taking ACTION
• Includes a Cum. line (i.e. combo bar-line
  chart) to indicate contribution of each and all

11
Bar Charts Pareto Analysis
12
Run Charts

• Time-series charts Trend chartsControl charts
• Illustrates performance (TREND) over time (some
  defined timeframe) indicates rate of change over
  time
• Used as the starting point to study/ understand
  trends and VARIATION.
• If direction is obvious (e.g. steep slope), use
  trend line function if NOT use Control Charts
  (Excel)

13
Visualize Your Process (Behavior)

• The process behavior chart allows you to
  separate the probable noise from the potential
  signals, so you can concentrate on the signals,
  where interesting things are happening, and can
  ignore the noise, where nothing much is going
  on.
• - Dr. Donald Wheeler, Building Continual
  Improvement
• Most effective process behavior chart is the
  Individuals or X chart.

14
Control Charts

• Control charts (process behavior charts) are run
  charts with control (variation) limits ( 3s)
  where s is a measure of variability called the
  standard deviation (the square root of the
  variance for a dataset).
• A stable process (in control) produces
  predictable results consistently. The results are
  due to chance or common causes inherent in the
  system/process.
• Example time it takes to drive to work each day
• An unstable process (out of control) indicates
  special causes are present. They are represented
  on the chart as either a point, set of points or
  a trend in your data. Stability Rules are used
  to determine these unstable conditions (and to
  help search for causes).
• Applying Rules allows for understanding of
  whether the process is
• Stable (predictable) where the variation is
  random (natural, inherent)
• OR
• Unstable, special (assignable) i.e. whether a
  shift has occurred and/or the variability has
  increased!

15
In Control

• Is performance stable i.e. in a state of
  statistical control, predictable within the
  limits, affected only by random variation
  (inherent in the process) not affected by
  unusual, outside influences with no assignable
  causes of variation.

16
Signals Most Common Rules for Detecting Out
of Control Conditions for X charts

• Point(s) Outside the Control Limits (variation
  increase?)
• A single point outside the limits is taken as an
  indication of the presence of an assignable cause
  which has a dominant effect.
• Runs Near the Limits (shift?)
• 3 out of 3 OR 3 out of 4 consecutive values in
  the upper or lower 25 (1/4) region between the
  limits is taken as an indication of an assignable
  cause which has a moderate but sustainable
  effect.
• Runs About the Center Line (shift?)
• 8 consecutive values on the same side of the
  central line (mean) is taken as an indication of
  an assignable cause which has a weak but
  sustainable effect.
• Trend Rules (changes in trend)
• 6 in a row (consecutive) trending up or down.
• 14 in a row alternating up and down

17
The Key to Improvement

• The path to improvement depends upon what type of
  variation is
• present. Investigation and reduction of variation
  is key to process
• improvement.
• Statistical Thinking Tenet 3 All improvement
  comes from understanding and reducing variation.
• basis for Six Sigma
• If the process is stable (displays predictable
  variation) improvement will only come by changing
  (a major portion of) the process.
• If the process is unstable (unpredictable) then
  improvement will come by finding and removing the
  assignable (special) causes first.
• Variations due to special causes are
• localized in nature
• exceptions to the system
• considered abnormalities
• often specific to a
• certain worker/group
• certain machine/equipment
• certain batch of material, etc.

18
Other Q. Charts

• Histogram frequency distribution
• Illustrates the shape of the distribution of
  quantitative (numerical) data helps to indicate
  normality
• Scatter diagram/Correlation chart
• Illustrates the strength of the relationship
  between 2 variables (not necessarily
  cause-effect)
• Concentration Diagram / Measles chart
• Shows the distribution of a problem/condition
  across some space (item, geographic area)
• Radar chart
• Shows changes or frequencies of data relative to
  a center point (target) and to each other

19
Using QI Macros in Excel

• Start with raw dataset (e.g. import data into
  worksheet)
• May need to format date field if HTML
• May need to scrub the data
• Make copy of worksheet ltCtrlgt worksheet (to keep
  raw data intact) delete any columns or rows not
  needed in new worksheet
• Sort data into chronological order oldest to
  most recent
• Highlight data set then
• Data Sort (sort by) date field - Ascending

20
Using QI Macros in Excel

• Categorize Event Data
• O Operations
• S Science
• N Neither (others)
• Pie Chart Use COUNTIF function to summarize

21
Using QI Macros in Excel

• Determine your purpose/message to identify want
  to chart/graph
• Trend of events over time
• Establish timeframe weekly, MONTHLY, quarterly
• Breakdown/Distribution of Events
• e.g. in Operations depts. vs. Science depts.
• label each event as either O for Operations or S
  for Science by adding a field (column) to the
  data set
• CONSIDER NORMALIZING THE DATA
• based on six sigma concept of opportunities.
• Calculate then graph rates whenever population
  varies widely (i.e. opportunities change over
  time), mix is high / opportunities do not remain
  constant

22
Summarizing Data for Pareto Analysis Using
Subtotals

• Copy visible cells only
• If some cells, rows, or columns on your worksheet
  are not displayed, you have the option of copying
  all cells or only the visible cells. For example,
  you can choose to copy only the displayed summary
  data on an outlined worksheet.
• Select the cells that you want to copy.
• On the Edit menu, click Go To.
• In the Go To dialog box, click Special.
• In the Go To Special dialog box, click Visible
  cells only, and then click OK.
• Click Copy on the Standard toolbar, or press
  CTRLC.
• Select the upper-left cell of the paste area.
• Tip   To move or copy a selection to a different
  worksheet or workbook, click another worksheet
  tab or switch to another workbook, and then
  select the upper-left cell of the paste area.
• Click Paste on the Standard toolbar, or press
  CTRLV.

23
Descriptive Statistics

• Starting point for analyzing a set of data
• Basic statistics
• Mean
• Median
• Mode
• Central Tendency
• Variation
• Range
• Variance
• Standard Deviation (square root of variance)
• In Excel Tools-Data Analysis-Descriptive
  Statistics

24
Importance of Data Information

• There are those who look at things the way they
  are and ask whyI dream of things that never were
  and ask why not? - George Bernard Shaw
  (Robert Kennedy)
• It is not too late to seek a better world.
  - Tennyson (Robert Kennedy)
• Its not to late to seek a better lab but we need
  to better understand WHAT to improve
• then HOW