Let us consider an example involving Espléndido Jugo y Batido, Inc. (EJB), a company that manufactures bottled juices and smoothies. EJB produces its products in five fruit flavors (apple, grape, orange, pear, and tomato) and four vegetable flavors (beet, carrot, celery, and cucumber), and it ships these products from distribution centers (DCs) in Idaho, Mississippi, Nebraska, New Mexico, North Dakota, Rhode Island, and West Virginia.
EJB management has retrieved data on each order it has received over the past three years and stored it in a data file. Each record in the data corresponds to the amount of one product (a combination of category and flavor) included in a single order, so an order can consist of multiple records in the data. Each record in the data provides the following.
Order ID
flavor
category (juice or smoothie)
dollar amount sold
date ordered
date delivered
DC that filled the order
an indication on whether the order was received from a new customer
service satisfaction rating reported by the customer
product satisfaction rating reported by the customer
Within the chapter, univariate analysis was demonstrated on some of the variables. We continue this analysis in this problem.
(a)
Using a PivotChart, construct the relative frequency distribution of records over the values of the Category variable. Describe your findings. (Enter your answers in percent. Round your answers to two decimal places.)
Juices make up
% of total orders, and smoothies make up
% of total orders.
(b)
Using a PivotChart, construct the relative frequency distribution of records over the values of the New Customer variable. In the PivotTable, relabel a "No" value for New Customer as "Existing" and a "Yes" value as "New." Describe your findings. (Enter your answers in percent. Round your answers to two decimal places.)
Existing customers make up
% of all orders, and new customers represent
% of all orders.
Answer (1)
(a) To create the relative frequency distribution for the Category variable (Juice or Smoothie):Use Excel PivotChartInsert PivotTable > Drag Category to RowsDrag Order ID to Values > set to "Count"Create % by dividing each count by totalCreate a bar chart (PivotChart)Example result (example values only, depends on data):Juices make up 62.75%Smoothies make up 37.25%(b) For the New Customer variable:Change "Yes" to "New", "No" to "Existing" in the dataRepeat PivotTable stepsAdd a pie chart to show the splitExample result:Existing customers make up 71.40%New customers represent 28.60%
