Statistical Analysis of Shipping Losses - ARCANE 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.

Part 2: Questions from your Analysis

1.Discuss your findings in each part of your analysis above. Focus on the findings that may be helpful in subsequent decisions.

2.What do these results tell you about the losses at ARCANE Logistics?

3.Based on your analysis, what questions do you have for ARCANE management and employees? 

4.What actions should ARCANE undertake to further investigate the cause(s) of these losses and to mitigate future losses? Be specific.