ARCANE Logistics: Statistics Paper

ARCANE Logistics: Statistics PaperARCANE Logistics
Introduction
In this exercise, you will use various Excel functions to perform statistical analysis on data, which
will simulate the exploratory data analysis that decision modelers perform before moving on to
more sophisticated analysis. As part of this analysis, you will work with pivot tables to analyze
the behavior of subsets of the data. These fundamental techniques are essential when trying to
make sense of large data sets. Many managers confine their analysis to simple calculations of
average. But knowledge of how variables are distributed and whether or not they are strongly
associated with other variables is as much or more important.

Scenario
ARCANE Logistics is a shipping contractor located in Waco, Texas. ARCANE has a shipping
contract with Bear Industries, which operates three plants in Austin, Fort Worth, and Houston. In
the past nine months, managers at ARCANE have found that there have been an unusual
number of losses on shipments for this customer. Losses from shortage (possibly theft), spoilage
from improper handling, and damage have been reported. Although some loss is expected and
very difficult to prevent, the high number of losses this year does not look random. As a result,
ARCANE has decided to hire your loss prevention team to investigate the causes of these
losses.
Data
For this exercise, use the Shipping Data Excel file, which is located in the same folder as this
assignment.
Part 1: Spreadsheet Analysis
1. Compute correlation between each numerical variable. For this exercise, you should use the
correlation function in the data analysis package. Make sure to include date in your correlations
to identify if losses are trending over time.
2. Highlight any correlation that you think is interesting and then discuss this correlation in Part 2
below.
3. Use the descriptive statistics package in Excel to compute descriptive statistics for each of the
numerical variables in your data set.
4. Study loss behavior. How do individual drivers compare on sum of losses and frequency
(count) of losses? Does this behavior vary by loss code? How about shipping origins or destinations. Do the sum of losses and frequency of losses vary by origin and/or destination?
Use pivot tables to isolate truck drivers, origins, destinations and types of losses. Compute
relevant statistics for each. Look for patterns. Hint: Look at the screenshot below.
The following selection of pivot fields yields losses by driver as shown below.
5. Create any charts that you think would be relevant to illustrate your findings. Possible charts
include histograms, line charts, bar charts, etc. Note: if the chart you would like to create will not
work with a pivot table, copy and paste as values the pivot table to another worksheet. You will
then be able to create your chart from this data.