Charts and Graphs assignment for research class
ALHAISANI
ExcelDataAnalysisLecture.pptxBA 301
Research & Analysis of Business Problems
1
S
1
Problem Solving Framework
2
Context & Background
Problem Analysis & Description
Solution Development
Evaluation & Decision-Making
Communication & Implementation
2
This gets us back to the problem solving framework I presented last week.
Data Supports Decision Making
3
Data
Information
Knowledge
Research & Data Gathering
Where do I start? (The Research Lecture)
Sources and methods
Hints and tips
What do I do with it once I get it? (Today)
Analysis, manipulation, and presentation
Excel and other useful tools
4
4
Primary Research Results
5
Is this data or information?
| Age | Gender | Income | Movies/Week | Education | Distance | Movie Type | Visits/Week | Transport | New/Old |
| 25 | M | $21,000 | 2 | College | 2 | Action | 1 | Walk | New |
| 38 | F | $60,000 | 2 | College | 1 | Action | 1 | Walk | Old |
| 27 | F | $23,000 | 1 | College | 2 | Comedy | 1 | Walk | Old |
| 23 | M | $175,000 | 2 | College | 2 | Drama | 2 | Bus | Old |
| 33 | F | $60,000 | 2 | College | 2 | Drama | 1 | Bike | Old |
| 29 | F | $30,000 | 5 | College | 5 | Romance | 2 | Car | Old |
| 40 | F | $45,000 | 3 | College | 1 | Romance | 2 | Car | Old |
| 42 | F | $50,000 | 1 | College | 2 | Romance | 1 | Bike | Old |
| 40 | F | $52,000 | 2 | College | 1 | Romance | 1 | Walk | New |
| 45 | F | $65,000 | 4 | College | 3 | Romance | 2 | Car | Old |
| 39 | F | $67,000 | 3 | College | 1 | SciFi | 1 | Walk | Old |
| 28 | F | $175,000 | 2 | Grad | 2 | Comedy | 1 | Walk | Old |
| 77 | F | $70,000 | 5 | Grad | 2 | Romance | 2 | Bike | Old |
| 56 | F | $125,000 | 4 | Grad | 1 | Romance | 2 | Walk | New |
| 22 | M | $18,000 | 1 | HS | 1 | Action | 1 | Walk | New |
| 24 | M | $25,000 | 2 | HS | 4 | Action | 2 | Car | New |
| 26 | M | $25,000 | 2 | HS | 1 | Action | 1 | Walk | New |
| 32 | F | $35,000 | 2 | HS | 1 | Comedy | 1 | Walk | Old |
| 82 | F | $35,000 | 6 | HS | 4 | Comedy | 3 | Car | Old |
| 34 | M | $52,000 | 2 | HS | 1 | Comedy | 2 | Walk | New |
| 34 | M | $40,000 | 1 | HS | 1 | Drama | 1 | Walk | Old |
| 34 | F | $22,000 | 2 | HS | 1 | Drama | 1 | Walk | Old |
| 24 | F | $22,000 | 4 | HS | 2 | Romance | 2 | Car | Old |
| 28 | F | $24,000 | 1 | HS | 1 | Romance | 1 | Walk | Old |
| 29 | F | $29,000 | 1 | HS | 2 | Romance | 1 | Walk | Old |
| 25 | F | $32,000 | 2 | HS | 3 | Romance | 2 | Car | Old |
| 34 | F | $40,000 | 3 | HS | 1 | Romance | 2 | Bike | Old |
| 32 | M | $40,000 | 2 | HS | 6 | Romance | 2 | Car | New |
| 26 | M | $30,000 | 1 | HS | 1 | SciFi | 1 | Walk | New |
5
Data Versus Information
Raw numbers, facts and figures are data. Alone, a collection of data can mean little.
Information is data that has been interpreted. Someone has taken the data and made it meaningful.
What might you do to the video store research data to turn it into information?
6
6
Information
7
| Age | Gender | Income | Movies/Week | Education | Distance | Movie Type | Visits/Week | Transport | New/Old |
| 25 | M | $21,000 | 2 | College | 2 | Action | 1 | Walk | New |
| 38 | F | $60,000 | 2 | College | 1 | Action | 1 | Walk | Old |
| 27 | F | $23,000 | 1 | College | 2 | Comedy | 1 | Walk | Old |
| 23 | M | $175,000 | 2 | College | 2 | Drama | 2 | Bus | Old |
| 33 | F | $60,000 | 2 | College | 2 | Drama | 1 | Bike | Old |
| 29 | F | $30,000 | 5 | College | 5 | Romance | 2 | Car | Old |
| 40 | F | $45,000 | 3 | College | 1 | Romance | 2 | Car | Old |
| 42 | F | $50,000 | 1 | College | 2 | Romance | 1 | Bike | Old |
| 40 | F | $52,000 | 2 | College | 1 | Romance | 1 | Walk | New |
| 45 | F | $65,000 | 4 | College | 3 | Romance | 2 | Car | Old |
| 39 | F | $67,000 | 3 | College | 1 | SciFi | 1 | Walk | Old |
| 28 | F | $175,000 | 2 | Grad | 2 | Comedy | 1 | Walk | Old |
| 77 | F | $70,000 | 5 | Grad | 2 | Romance | 2 | Bike | Old |
| 56 | F | $125,000 | 4 | Grad | 1 | Romance | 2 | Walk | New |
| 22 | M | $18,000 | 1 | HS | 1 | Action | 1 | Walk | New |
| 24 | M | $25,000 | 2 | HS | 4 | Action | 2 | Car | New |
| 26 | M | $25,000 | 2 | HS | 1 | Action | 1 | Walk | New |
| 32 | F | $35,000 | 2 | HS | 1 | Comedy | 1 | Walk | Old |
| 82 | F | $35,000 | 6 | HS | 4 | Comedy | 3 | Car | Old |
| 34 | M | $52,000 | 2 | HS | 1 | Comedy | 2 | Walk | New |
| 34 | M | $40,000 | 1 | HS | 1 | Drama | 1 | Walk | Old |
| 34 | F | $22,000 | 2 | HS | 1 | Drama | 1 | Walk | Old |
| 24 | F | $22,000 | 4 | HS | 2 | Romance | 2 | Car | Old |
| 28 | F | $24,000 | 1 | HS | 1 | Romance | 1 | Walk | Old |
| 29 | F | $29,000 | 1 | HS | 2 | Romance | 1 | Walk | Old |
| 25 | F | $32,000 | 2 | HS | 3 | Romance | 2 | Car | Old |
| 34 | F | $40,000 | 3 | HS | 1 | Romance | 2 | Bike | Old |
| 32 | M | $40,000 | 2 | HS | 6 | Romance | 2 | Car | New |
| 26 | M | $30,000 | 1 | HS | 1 | SciFi | 1 | Walk | New |
Average Income = $51,276
7
Information
8
| Age | Gender | Income | Movies/Week | Education | Distance | Movie Type | Visits/Week | Transport | New/Old |
| 25 | M | $21,000 | 2 | College | 2 | Action | 1 | Walk | New |
| 38 | F | $60,000 | 2 | College | 1 | Action | 1 | Walk | Old |
| 27 | F | $23,000 | 1 | College | 2 | Comedy | 1 | Walk | Old |
| 23 | M | $175,000 | 2 | College | 2 | Drama | 2 | Bus | Old |
| 33 | F | $60,000 | 2 | College | 2 | Drama | 1 | Bike | Old |
| 29 | F | $30,000 | 5 | College | 5 | Romance | 2 | Car | Old |
| 40 | F | $45,000 | 3 | College | 1 | Romance | 2 | Car | Old |
| 42 | F | $50,000 | 1 | College | 2 | Romance | 1 | Bike | Old |
| 40 | F | $52,000 | 2 | College | 1 | Romance | 1 | Walk | New |
| 45 | F | $65,000 | 4 | College | 3 | Romance | 2 | Car | Old |
| 39 | F | $67,000 | 3 | College | 1 | SciFi | 1 | Walk | Old |
| 28 | F | $175,000 | 2 | Grad | 2 | Comedy | 1 | Walk | Old |
| 77 | F | $70,000 | 5 | Grad | 2 | Romance | 2 | Bike | Old |
| 56 | F | $125,000 | 4 | Grad | 1 | Romance | 2 | Walk | New |
| 22 | M | $18,000 | 1 | HS | 1 | Action | 1 | Walk | New |
| 24 | M | $25,000 | 2 | HS | 4 | Action | 2 | Car | New |
| 26 | M | $25,000 | 2 | HS | 1 | Action | 1 | Walk | New |
| 32 | F | $35,000 | 2 | HS | 1 | Comedy | 1 | Walk | Old |
| 82 | F | $35,000 | 6 | HS | 4 | Comedy | 3 | Car | Old |
| 34 | M | $52,000 | 2 | HS | 1 | Comedy | 2 | Walk | New |
| 34 | M | $40,000 | 1 | HS | 1 | Drama | 1 | Walk | Old |
| 34 | F | $22,000 | 2 | HS | 1 | Drama | 1 | Walk | Old |
| 24 | F | $22,000 | 4 | HS | 2 | Romance | 2 | Car | Old |
| 28 | F | $24,000 | 1 | HS | 1 | Romance | 1 | Walk | Old |
| 29 | F | $29,000 | 1 | HS | 2 | Romance | 1 | Walk | Old |
| 25 | F | $32,000 | 2 | HS | 3 | Romance | 2 | Car | Old |
| 34 | F | $40,000 | 3 | HS | 1 | Romance | 2 | Bike | Old |
| 32 | M | $40,000 | 2 | HS | 6 | Romance | 2 | Car | New |
| 26 | M | $30,000 | 1 | HS | 1 | SciFi | 1 | Walk | New |
Average Age = 35.4
8
Information
9
9
Descriptive Statistics
Definition?
A collection of tools and techniques for describing, organizing and interpreting data.
Extract useful information from data.
There are three kinds of lies: lies, damned lies, and statistics. – Benjamin Disraeli
USA Today has come out with a new survey – apparently, three out of every four people make up 75% of the population. – David Letterman
10
10
Refresher – Data Types
11
Qualitative Data
Robust aroma.
Frothy appearance.
Strong taste.
White cup.
Quantitative Data
12 ounces.
150 degrees.
Cup is 7 inches high.
Cost is $3.95.
11
Descriptive Tools (Quantitative)
12
Mean (Average) versus Median?
More about averages…
$40,000
$51,276
12
Descriptive Tools
13
Histograms
Median
Mean
13
Income Distribution
Frequency 0 10000 20000 30000 40000 50000 60000 70000 80000 90000 100000 111000 120000 130000 140000 150000 170000 180000 More 0.0 0.0 1.0 10.0 6.0 2.0 4.0 3.0 0.0 0.0 0.0 0.0 0.0 1.0 0.0 0.0 0.0 2.0 0.0Income Bins
Frequency
Descriptive Tools
14
Other Useful Stats…
Median
Mean
What is the standard deviation?
The spread of the data around the mean
$40,698 in this example
14
Income Distribution
Frequency 0 10000 20000 30000 40000 50000 60000 70000 80000 90000 100000 111000 120000 130000 140000 150000 170000 180000 More 0.0 0.0 1.0 10.0 6.0 2.0 4.0 3.0 0.0 0.0 0.0 0.0 0.0 1.0 0.0 0.0 0.0 2.0 0.0Income Bins
Frequency
Descriptive Tools
15
Charts and graphs?
15
Descriptive Tools
16
Charts and graphs?
What is the correlation?
The strength and direction of the relationship between two variables – between income and age.
-1.0 to +1.0, and closer to one is better.
Correl = 0.132
16
Descriptive Tools
17
More about charts…
17
Descriptive Tools
18
The beauty of pivot charts…
Pivot
Table
Pivot Chart
18
Building A Great Chart Or Graph
What are the variables?
Characteristics of a sample or population (age, car brand, etc.)
What is the data?
Values or counts of the variable from observations
What type of data & what type of graph?
Trend graphs (generally quantitative)
Relative size graphs (categories counts/qualitative)
Composition graphs (counts/percentage breakdown)
What do you want to communicate?
19
19
Principles of Good Design
High data to chart ratio.
The right graph for the right data.
Most Excel choices are poor choices.
What’s a donut chart for, anyway?
Make sure it’s complete.
Labels.
Titles.
Think about the overall presentation.
20
20
Trend Graphs
Scatter plots and line graphs.
Often used when you want to view a trend or relationship between two variables.
Put the information where the reader expects it to be!
Where time is a variable – put it on the x axis.
Use bars or columns when data is categorical.
21
21
Relative Size Graphs
Used to compare relative sizes of different variable categories.
Columns (vertical) are better than bars (horizontal).
Can be used with one or more variables (for example: gender, education)
Columns should be equal width.
Don’t overuse colors and take care with legends.
22
22
Composition Graphs
This is where pie charts are often misused.
Readers often have trouble with angles…
Try a segmented column with important segments at the top or bottom.
23
23
College Grad HS 11.0 3.0 15.0 College 11.0 Grad 3.0 HS 15.0
Composition Graphs
This is where pie charts are often misused.
Readers often have trouble with angles…
Try a segmented column with important segments at the top or bottom.
Consider whether a relative size graph is better!
24
24
College Grad HS 11.0 3.0 15.0
25
Simple, well-planned graphs can communicate complex information.
25
Six Ways To A Great Chart
A graph should communicate only one idea
Minimize chart or graph junk
Plan out your chart before you create the final copy
Label everything so nothing is left to the misunderstanding of the audience
Keep things balanced
A chart alone should convey what you say
26
26
Bad Charts
27
27
Bad Charts
28
28
Bad Charts
29
29
Bad Charts
30
30
Other Tools
t-test
Determining the significance of differences between two independent groups
ANOVA
Analysis of Variance – a whole bunch of different tools for analyzing the differences between means of different groups
Chi-squared (Goodness of Fit)
A test for comparing what you observe against what you expect
31
31
Let’s play ‘What’s wrong with this statistic!’
Fake out
32
Comparing Data
33
Huh?
34
Oh. Wow.
35
From Fox News (no kidding)
Size Matters
36
What’s wrong with this picture?
37
Um, pretty?
38
Header: a big drop in the number of school aged children is upon us.
39
http://www.illicitsnowboarding.com/2012/11/how-dangerous-are- avalanches.html
40
Um.
41
Conclusions
Getting the data is generally not the problem – analyzing it and using it to make good decisions is the problem.
Use the tools available to you, but don’t overanalyze.
Think about the questions you want to answer, and the important stakeholders.
Can you make data say what you want it to say?
42
42
Critical Thinking
51% of women are now living without spouse. – New York Times
Conclusion – marriage is threatened in the U.S.
The Times got to 51% by including 2.4 million American females over 15 (of 117 million) who are married but not living with their husbands. It also counts widows not living with their husbands (geez – they’re dead!)
We spend $50 billion per year on weddings.
43
