MBA Data Analysis Project
Budget: $10 – $30 USD
Assignment 2 (MBA 693)
Problem 1: (20 points)
The income levels vary by race and educational attainment. To examine this inequality in the
income, data have been collected for seven different years on the median income earned by an
individual based on his or her race and education. This data is given in the data file accompanying
the assignment.
a) Sort the PivotTable data to display the years with the smallest sum of median income on
top and the largest on the bottom. Which year had the smallest sum of median income?
What is the total income in the year with the smallest sum of median income?
b) Add the Racial Demographic to the Row Labels in the PivotTable. Sort the Racial
Demographic by Sum of Median Income with the lowest values on top and the highest
values on bottom. Filter the Row Labels so that only the year 2003 is displayed. Which
Racial demography had the smallest sum of median income in the year 2003? Which
Racial demography had the largest sum of median income in the year 2003?
Problem 2: (20 points)
The regional manager of a company wishes to determine the time spent at each division in the
car production process. A study was undertaken over a month that resulted in the following data
related to the percentage of time spent at three divisions (car body construction, paint shop, and
assembly) at four locations of production plants.
Production Plants Car Body
Construction (%) Paint Shop (%) Assembly (%)
Michigan 35 45 20
Kentucky
37 41 22
Illinois
33 39 28
Ohio
36 40 24
a) Create a stacked-bar chart with production plants along the vertical axis. Reformat the
bar chart to best display these data by adding required labels and chart title.
b) Create a clustered-bar chart with production plants along the vertical axis and clusters of
divisions. Reformat the bar chart to best display these data by adding required labels and chart title.
c) Create multiple bar charts where each production plant becomes a single bar chart
showing the percentage of time spent at the divisions. Reformat the bar charts to best
display these data by adding required labels and chart title.
d) Which form of bar chart (stacked, clustered, or multiple) is preferable for these data?
Why?
Problem 3: (15 points)
A consumer electronics company, after three months of the launch of five new products in the
market, arrived at the following results.
Products Profit (%) Market share (%) Cost ($)
A 19 18 4500
B 28 12 3000
C 15 25 8750
D 22 35 6250
E 16 10 2500
a) Create a bubble chart where the market share is along the horizontal axis, the profit is on
the vertical axis, and the size of the bubblesrepresentsthe cost. Format this chart for best
presentation by adding axes labels and labeling each bubble with the product name.
b) The manager of the company is interested in producing the product that increases the
profit for a given level of market share and cost. From the bubble chart in part a, identify
the product which needs to be produced in larger quantity.
c) From the bubble chart in part (a), now identify the product which needs to be produced
in larger quantity taking into account its market share, cost, increase in profit.
Problem 4: (5 points)
Data are shown below on the quality rating, volume, average wait time from pull-up to
completion, average unit purchase, and revenue tier for franchises of a certain fast food
restaurant in Area 6.
Area 6 Franchise Data
Franchise
Number Quality Rating Volume
Category
Wait Time
(sec.)
Average Unit
Purchase ($)
Revenue
Tier
124 Above Average High 175 13.25 4
152 Acceptable Low 181 10.02 2
452 Above Average Low 179 13.56 4
462 Above Average Medium 175 12.12 3
485 Superior High 171 15.11 4
567 Acceptable High 178 9.78 2
568 Above Average Medium 177 12.54 4
584 Above Average Medium 176 11.54 3
625 Acceptable Medium 175 11.14 3
875 Acceptable Low 180 8.78 1
Is it appropriate to make a scatter chart to display the relationship between the franchise
number and the average unit purchase? Explain.
Problem 1: (20 points)
The income levels vary by race and educational attainment. To examine this inequality in the
income, data have been collected for seven different years on the median income earned by an
individual based on his or her race and education. This data is given in the data file accompanying
the assignment.
a) Sort the PivotTable data to display the years with the smallest sum of median income on
top and the largest on the bottom. Which year had the smallest sum of median income?
What is the total income in the year with the smallest sum of median income?
b) Add the Racial Demographic to the Row Labels in the PivotTable. Sort the Racial
Demographic by Sum of Median Income with the lowest values on top and the highest
values on bottom. Filter the Row Labels so that only the year 2003 is displayed. Which
Racial demography had the smallest sum of median income in the year 2003? Which
Racial demography had the largest sum of median income in the year 2003?
Problem 2: (20 points)
The regional manager of a company wishes to determine the time spent at each division in the
car production process. A study was undertaken over a month that resulted in the following data
related to the percentage of time spent at three divisions (car body construction, paint shop, and
assembly) at four locations of production plants.
Production Plants Car Body
Construction (%) Paint Shop (%) Assembly (%)
Michigan 35 45 20
Kentucky
37 41 22
Illinois
33 39 28
Ohio
36 40 24
a) Create a stacked-bar chart with production plants along the vertical axis. Reformat the
bar chart to best display these data by adding required labels and chart title.
b) Create a clustered-bar chart with production plants along the vertical axis and clusters of
divisions. Reformat the bar chart to best display these data by adding required labels and chart title.
c) Create multiple bar charts where each production plant becomes a single bar chart
showing the percentage of time spent at the divisions. Reformat the bar charts to best
display these data by adding required labels and chart title.
d) Which form of bar chart (stacked, clustered, or multiple) is preferable for these data?
Why?
Problem 3: (15 points)
A consumer electronics company, after three months of the launch of five new products in the
market, arrived at the following results.
Products Profit (%) Market share (%) Cost ($)
A 19 18 4500
B 28 12 3000
C 15 25 8750
D 22 35 6250
E 16 10 2500
a) Create a bubble chart where the market share is along the horizontal axis, the profit is on
the vertical axis, and the size of the bubblesrepresentsthe cost. Format this chart for best
presentation by adding axes labels and labeling each bubble with the product name.
b) The manager of the company is interested in producing the product that increases the
profit for a given level of market share and cost. From the bubble chart in part a, identify
the product which needs to be produced in larger quantity.
c) From the bubble chart in part (a), now identify the product which needs to be produced
in larger quantity taking into account its market share, cost, increase in profit.
Problem 4: (5 points)
Data are shown below on the quality rating, volume, average wait time from pull-up to
completion, average unit purchase, and revenue tier for franchises of a certain fast food
restaurant in Area 6.
Area 6 Franchise Data
Franchise
Number Quality Rating Volume
Category
Wait Time
(sec.)
Average Unit
Purchase ($)
Revenue
Tier
124 Above Average High 175 13.25 4
152 Acceptable Low 181 10.02 2
452 Above Average Low 179 13.56 4
462 Above Average Medium 175 12.12 3
485 Superior High 171 15.11 4
567 Acceptable High 178 9.78 2
568 Above Average Medium 177 12.54 4
584 Above Average Medium 176 11.54 3
625 Acceptable Medium 175 11.14 3
875 Acceptable Low 180 8.78 1
Is it appropriate to make a scatter chart to display the relationship between the franchise
number and the average unit purchase? Explain.