Descriptive Business Analysis
Name ___________________________________________
QSO 260 Descriptive Analytics
Descriptive Statistics Part 1
Week 2 Homework Assignment
Instructions
1. This assignment is to be done individually. This is not a group assignment.
2. Word-process your answers within this document. Expand the space as necessary.
3. Copy and paste your Excel work as required into the spaces provided for the purpose. Do not attach at the end of the assignment.
4. The homework assignment is due before the next class. Please submit your assignment through the Week 2 Assignment Submission folder.
5. You can submit up to one late/redone homework assignments before the midterm exam. Late/redone homework assignments will not be accepted at any other time.
Camm2 Problem 2-3
Ohio Logistics manages the logistical activities for firms by matching companies that need products shipped with carriers that can provide the best rates and best service for the companies. Ohio Logistics is very concerned that its carriers deliver their customers’ material on time, so it carefully monitors the percentage of on-time deliveries. The following table contains a list of the carriers used by Ohio Logistics and the corresponding on-time percentages for the current and previous years.
Carrier
Previous Year On-Time Deliveries (%)
Current Year On-Time Deliveries (%)
Blue Box Shipping
88.4
94.8
Cheetah LLC
89.3
91.8
Granite State Carriers
81.8
87.6
Honsin Limited
74.2
80.1
Jones Brothers
68.9
82.8
Minuteman Company
91.0
84.2
Rapid Response
78.8
70.9
Smith Logistics
84.3
88.7
Super Freight
92.1
86.8
These data are contained the data file Carriers Data.
(a) Starting with the file Carriers Data, sort the carriers in descending order by their current year’s percentage of on-time deliveries. Copy and paste the table after sorting in the space below.
(b) Which carrier is providing the best service in the current year? Which carrier is providing the worst service in the current year? Note: Answer using the table from part (a).
(c) Starting with the table from part (a) and calculate the change in percentage of on-time deliveries from the previous to the current year for each carrier. Copy and paste the table after calculation of change in percentage of on-time deliveries in the space below. Note: Change in percentage of on-time deliveries = on-time percentage of current year on-time percentage for last year. The data is already in percentages. So a only a simple difference between the two percentage values is needed.
(d) Starting with table from part (c) and use Excel’s conditional formatting to highlight the carriers whose on-time percentage decreased from the previous year to the current year. Copy and paste the table after conditional formatting in the space below.
(e) Starting with table from part (c), use Excel’s conditional formatting tool to create data bars for the change in percentage of on-time deliveries from the previous year to the current year for each carrier. Copy and paste the table after conditional formatting in the space below.
(f) In your opinion, which carriers should Ohio Logistics try to use in the future? Give reason for your answer.
Camm2 Problem 2-7
Consumer complaints are frequently reported to the Better Business Bureau. Industries with the most complaints to the Better Business Bureau are often banks (BANK), cable and satellite television companies (CABLE), collection agencies (COLLECTION), cellular phone and data providers (CELL), and new car dealerships (CAR). The results for a sample of 200 complaints are in the file BBB.xls.
(a) Starting with the file BBB Data, calculate the frequencies and percent frequencies. Copy and paste the table with frequencies and percent frequencies in the space below. Note: Use the countif function to calculate the frequencies.
(b) Which industry had the highest number of complaints?
(c) Comment on the percentage frequency distribution for complaints. Data
Industry Industry Frequency Relative Frequency
Cable Bank
Cable Cable
Car Car
Collection Cell
Cell Collection
Collection
Cell
Car
Bank
Cell
Bank
Cell
Bank
Cell
Cable
Bank
Cell
Cable
Cell
Cable
Car
Cell
Collection
Cell
Cable
Cell
Cell
Cable
Cell
Collection
Collection
Collection
Car
Car
Cell
Cell
Car
Cable
Car
Car
Car
Cable
Car
Collection
Cable
Cable
Bank
Bank
Cable
Collection
Cable
Cable
Bank
Bank
Cable
Car
Bank
Car
Collection
Bank
Bank
Cable
Cell
Cable
Cell
Cell
Cell
Bank
Collection
Cell
Cell
Collection
Car
Car
Collection
Car
Car
Cell
Cell
Cable
Cell
Cell
Cable
Cell
Car
Cell
Cell
Collection
Cable
Bank
Cable
Cable
Car
Cell
Collection
Car
Car
Car
Cell
Cell
Car
Bank
Cell
Cable
Bank
Cell
Cable
Cell
Cable
Cable
Cable
Car
Collection
Cell
Collection
Cell
Car
Bank
Cell
Bank
Cell
Cell
Cable
Collection
Car
Cell
Collection
Cell
Cable
Cell
Car
Car
Car
Cable
Car
Collection
Cable
Cable
Bank
Bank
Cable
Collection
Cable
Cable
Bank
Bank
Collection
Bank
Cell
Collection
Collection
Car
Car
Cell
Cell
Car
Cable
Cable
Cable
Car
Bank
Car
Bank
Car
Car
Cell
Cell
Cable
Cell
Cell
Cell
Bank
Collection
Cell
Cell
Collection
Car
Car
Collection
Cell
Cable
Cell
Cell
Cell
Collection
Car
Cell
Cell
Collection
Cable
Bank
Cable
Cable
Car
Cell
Cable
Cell
Car
Car
Cell
