Imagine starting a shaved ice business, beginning with only the resources you have now. Your first reaction would likely be to start looking for data. Consider the following sequence of questions:

• How much shaved ice can a person generate by scraping a robust soup spoon across the surface of a frozen chunk of ice?
• How long will it take, using the inexpensive spoon method, to get enough for a single serving of shaved ice?
• How much will it cost to purchase an actual shaved ice machine?
• How much money do you have?
• How much will it cost to rent a shaved ice machine?
• Where can you get flavors?
• Which flavors are customers most interested in?
• How much should you charge for each cup of shaved ice?
• Where would be a good place to set up your shaved ice stand?
• How many customers should you expect to serve each week?
• Can you justify the work required to make the business successful?

A potential shaved ice business owner would probably ask ALL of these questions, and more, because the information is critical to developing their business plan. Imagine the huge number of questions asked across a complex global supply chain, every day!

Because of these kinds of important questions and more, decision makers can’t ignore the need for data. With higher quality data, coupled with the appropriate analyses, managers are more likely to make great decisions. This is especially true across supply chains, where the complexity and changing nature of different industries make data all the more valuable.

So, we need to understand some basic characteristics of data, how to analyze data, and then how to interpret and take action based on what we learn.

Assuming we have good data, the most prolific tool for analyzing data today is spreadsheets, and the most common and capable spreadsheet software is Microsoft Excel (we can rock-paper-scissors any disagreements about this at a later time). This text will rely on Excel for data analysis and other calculations, though much of what is covered in this introductory chapter can be performed in other spreadsheet software, or by using generative AI tools such as Perplexity, Gemini, Grok, ChatGPT, Copilot, Claude, Deep Seek, etc. (assuming you know what to ask in your prompts!!).

Tools for Processing Data and Analysis

For this introductory text, we are going to limit our introduction of data analysis to the following key tools, techniques, and skills:

• Understanding basic data types
• Summarizing and describing data
• Data filtering and sorting
• Pivot tables
• Categorical data visualization
• Numerical data visualization
• Storytelling with data and the C.A.R. method

This text assumes a basic understanding of Excel navigation. For interested readers, Appendix 1 at the end of this chapter includes an overview of what students should already be familiar with, as well as some basic shortcuts and techniques that are useful for laptop users.

The rest of this chapter will go through the tools, techniques, and skills required for the remainder of this book, beginning with an introduction to data types.

Understanding Basic Data Types

Data comes in many forms: numbers, words, pictures, sounds, videos, and really anything that can be observed and recorded. Supply chain managers use all kinds of data to understand how everything is operating, but we are going to primarily focus on two basic types of data: numerical and categorical.

Numerical data is quantitative data that was generated by some kind of measurement. Weight, dollars, temperature, speed, and volume are just a few examples. Technically, the numerical data type can be broken down further, into interval data or ratio data. For our purposes, we want to focus on the fact that numerical data is measured quantitative data.

Categorical data, on the other hand, is generally qualitative in nature. Gender, location names, categories, and even scaled rankings can be considered categorical. Similar to Numerical data, Categorical data is often broken down into subcategories. Ordinal categorical data has a specific order like restaurant ratings or classifications of business sizes, and Nominal categorical data has no implied order, such as marital status or gender.

A Food Manufacturer’s Need for Data

Remember the cultural significance of peanut butter and honey sandwiches from the first chapter? In this chapter, we are going to look at a company named “Shef B. Foods”, to help us learn and apply the basics of working with data.

Shef B. Foods, as EVERYONE knows, is famous for their Peanut Butter and Honey sandwiches.

Recently, Shef B. Foods gathered some data to try and improve their sandwiches even further.

Below, we list what they specifically wanted to know, and why:

• Which is the best brand of bread? (Dunford Bakery or Grandma Sycamore’s)
  • … for highest customer ratings? (quality)
  • … for productivity/speed of sandwich construction? (labor cost)
• What’s the preferred peanut butter brand? (Adams, Peter Pan, Jif, Skippy, or Smucker’s)
  • … for highest customer ratings? (quality)
  • … for productivity/speed of sandwich construction? (labor cost)
• What is the preferred type of peanut butter? (Creamy or Chunky)
  • … for highest customer ratings? (quality)
  • … for productivity/speed of sandwich construction? (labor cost)
• Which Chef has the best technique? (Ben, Francois, Shane, Hugo, or Evan)
  • … for highest customer ratings? (quality)
  • … for productivity/speed of sandwich construction? (labor cost)
  • … for least amount of ingredients used? (raw material cost)
• What sandwich temperatures are more preferred? (measured in degrees Fahrenheit)
  • … for highest customer ratings? (quality)

To get the data, Shef B. Foods conducted a randomized study, where they watched their 5 best Chefs make about 50 PB&H sandwiches using different kinds of ingredients and methods, as mentioned above. They recorded the ingredients (brands, volumes, weights), the sandwich temperatures, and the assembly times, as well as which Chef made each sandwich.

Then, a group of loyal, experienced customers tried each of the experimental sandwiches and rated the flavor on a scale from 1 to 5. A score of 1 meant that the sandwiches were much worse than normal, and a 5 meant the sandwiches were much better than normal. A score of 3 meant that the customers rated the sandwich as being the same flavor/quality as a normal Shef B. Foods PB&H sandwich.

That data is located here: Clean Sandwich Data

In the table below, we have defined some of the Shef B. Foods by the data type, how the data was collected, and why it was collected.

TABLE 4-1

Measurement	Example Data	Data Type	How collected	Why collected
Volume of peanut butter	16.7 grams 15.9 grams 20.1 grams	Numerical (Ratio)	Weigh on kitchen scale	Monitor consistency of ingredients in each sandwich
Brand of peanut butter	Jif Peter Pan Skippy	Categorical (Nominal)	Read each jar label during use	Test customer preferences for different brands
Sandwich assembly time	106.78 seconds 48.67 seconds 59.01 seconds	Numerical (Ratio)	Stopwatch used to time each Chef’s sandwich assembly time	See which Chef uses the fastest sandwich assembly method
Customer flavor ratings	Much worse (1) A little worse (2) The same (3) A little better (4) Much better (5)	Categorical (Ordinal)	Customers record flavor ratings on feedback cards	Monitor the quality of the sandwich in comparison to the normally produced sandwich
Sandwich Temperature	77.3° F 91.5° F 71.0° F	Numerical (Interval)	Food thermometer reading in center of sandwich	Find the ideal sandwich temperature for optimal taste

The type of data you have determines the kind of charts you can make, the kind of analysis you can do, and the kind of statistical techniques you can apply. The data type also affects how data is collected.

The most important aspect of data is its purpose. Collecting data just to have data is potentially wasted effort, which is why in the table we included the column “Why Collected”. There may be several reasons to collect certain information, but there should be intentional reasoning behind spending time and money to gather data.

Raw Data

Raw data, like the data provided in the Shef B. Foods dataset, is generally organized with each row representing the entity being measured. For Shef B. Foods, each sandwich that was made gets its own row of data, because sandwiches are the “entities” being measured.

The columns, on the other hand, represent the characteristics that we want to know about each “entity”. The columns in the Shef B. Foods file each provide different measured characteristics for each sandwich. For example, the “chef_name” column records the name of the Chef who made the sandwich on each row, while the “temperature_F” column shows the measured temperature of each sandwich, in Fahrenheit, just before being served to customers. More scientific terms for the “characteristics” being measured include variables, or factors – we may use these terms interchangeably.

While mostly for organizational purposes, the columns can also include index variables, so that each entity can have a unique “label”. In the example dataset, the first column includes a “sandwich_index_#”, that gives a unique assigned identification number to each sandwich entity.

Raw data comes in various sizes, from a single entity/variable, to millions and millions of entities and variables. Our example data set for Shef B. Foods is only 49 rows and 13 columns if you include the row of column/variable names, but some raw datasets are thousands, millions, or even billions of rows and/or columns. Excel, as a software, “only” has room for 1,048,576 rows and 16,6384 columns, but it is not recommended to use Excel for larger datasets.

Finally, there is no rule that says we can’t swap the columns and rows (so that entities are in columns, rows represent variables). The problem-solver, analyst, manager, or whoever is analyzing the data set, needs to know how the data is organized before beginning their work.

Data Storytelling

When data is used for decision-making, it is rarely used in its raw, row-by-column-by-tedious-row format.

Instead, we summarize the data with statistical calculations, or we create charts and graphs, to more efficiently and effectively describe it. We suggest that any data can be summarized and described in five key areas, regardless of whether it is numerical or categorical, or whether it is shown in charts or in calculated statistics.

Meta Data
The most basic way to describe data is to answer ‘how much’ and ‘where it came from’. Other similar terms used are ‘totals’, ‘sum’, ‘amount’, ‘source’ or ‘size’. We refer to this most basic result summary as the quantity of data or the meta data. We can explain the source of the data (meta), the size or quantity of data (meta), the names of important categories or variables (categorical), the type of data we have (meta), and we can sum important numbers like sales figures or shipping costs (numerical). These basic information areas and calculations give us an idea of the quantity of the data we have for each category or condition and where the data came from.

Typical Data
We use terms like ‘average’, ‘most likely’, ‘median’, or ‘expected value’ to describe typical data for a specific data set, or for group within a data set. For example, to portray a typical salary for fresh college graduates from your major, university brochures will promise an average salary or median salary.

Rare Data
On the other end of the spectrum, terms like ‘outlier’, ‘extreme point’, ‘special case’, or ‘one-off’ are used to describe rare data within a data set. Often, these rare results are due to unexpected outcomes that are not typical, and possibly too extreme to be included when considering the full data set. Imagine an NFL recruit graduating with a degree in Supply Chain, whose starting annual salary is $12 Million per year. The NFL salary would be considered an outlier when compared with the other Supply Chain graduates’ salaries the same year, and should probably not be included in the calculations for average salaries.

Variability of Data
When describing the data as a whole, we may use terms like ‘dispersion’, ‘range’, or ‘spread’, or we may describe the data as ‘dense vs. sparse’, or ‘consistent vs. inconsistent’ to describe the variability of data. Going back to the salary example for college graduates, marketing campaigns may include an average salary $70k for Supply Chain Majors, but also provide a typical range of $60k – $90k. This added information provides a bit more understanding of what to expect as a college graduate going into the field of Supply Chain Management.

Shape of Data
Lastly, when terms like ‘cluster’, ‘skew’, ‘spike’, or ‘uniform’ are used, we are trying to describe the shape of data. The best way to understand the shape of results is by using a graph or diagram. However, as the graph or diagram is described in words, the ‘shape’ terms are used to describe what is shown. We could add to the salary brochure by describing the starting Supply Chain salary data as being clustered around $80k, with a few rare salaries skewed towards the lower end of the $60k – $90k range.

Applying Data Storytelling to Different Data Types

Different data types need to be summarized using different methods. For example, numerical data can be used to calculate averages, whereas an average cannot be computed for most categorical data. Instead, categorical data can be summarized using proportions or percentages. The table below summarizes the different measurements that can be used for each data type.

Data Summary Area	Numerical Data Type	Categorical Data Type
Meta Data	Summation of the data (where warranted), Count of the number of total data points, and a brief description of the data source	Count of the results in each category, count of the number of total data points, count/names of the categories, and a brief description of the data source
Typical Data	Average (the data added together, then divided by the number of data points) Median (the middle of the data after it’s sorted smallest to largest – 50% above and 50% below) (Look at the graph)	Proportions are computed for each category (the category count is divided by the total count across categories) The larger proportion categories represent more ‘typical’ data (Look at the graph)
Rare Data (if applicable)	Outliers – points that seem extreme on either end of the data (officially from statistics: a data point that is more than 1.5 * IQR beyond the first or third quartiles, OR a data point with a z-score less than -3 or more than +3) (Look at the graph)	Categories with very small proportions in relation to other category proportions (compare the proportions) (Look at the graph)
Variability of Data	Standard Deviation (basic measure of variability for numerical data) Variance (the square of the standard deviation) Range (maximum value – minimum value) (Look at the graph)	Compare the category proportions, and if they are roughly similar then there is low variability. If they are extremely different, then variability is high. (Look at the graph)
Shape of Data (if applicable)	Quartiles and Interquartile Range (i.e. from a box-and-whisker plot) Skewness (balance vs. imbalance) and Kurtosis (“pointy” vs. “rounded”) (Look at the graph)	This is more for ‘ordinal’ categorical data. If the data is clustered on one end or the other, and/or if the data is very light towards one end or the other. (Look at the graph)

Data Storytelling Basics

A simple way to ‘tell the story’ about the data, is to compute or graph the data summaries as described in the able above, and then describe each summary area briefly. This allows us to quickly understand what the data looks like for a particular variable (or characteristic) of the entities that are being measured. In the table below, we summarize two of the variables from the Shef B. Foods dataset mentioned earlier – one is numerical and the other is categorical.

 

TABLE 4-3

Data Summary Area	Sandwich Assembly Times (numerical)	Peanut Butter Brand (categorical)
Meta Data	Shef B. Foods was looking for ways to improve their peanut butter and honey sandwiches, and so they gathered data on 48 experimental sandwiches. To understand the speed at which the sandwiches were made, assembly times were recorded in seconds.	Shef B. Foods was looking for ways to improve their peanut butter and honey sandwiches, and so they gathered data on 48 experimental sandwiches. To see whether the brand of peanut butter used affects customer ratings, they had their Chefs use 5 different brands of peanut butter: Adams, Jif, Peter Pan, Skippy, and Smucker’s.
Typical Data	Average Sandwich Assembly Time = 78.1 seconds Median Sandwich Assembly Time = 78.4 seconds	The top two brands used in the study were Jif and Skippy, representing almost 46% of the sandwiches made, evenly splitting the percentage with 11 sandwiches each.
Rare Data (if applicable)	Looking just at the assembly times overall, there were no outliers, or “rare” assembly times in this data set.	The two brands least used, also equally, were Smucker’s and Adams, at roughly 17% each. Despite them being the smallest categories, they should not be considered rare as both brands were still well-represented.
Variability of Data	Standard Deviation of sandwich assembly time was 22.7 seconds. Total Range of home prices (max – min) was 74.33 seconds. The maximum assembly time was almost two minutes (116.89 seconds), and the minimum assembly time was 42.56 seconds).	The Peanut Butter Brands did not vary much, with the top two categories at 23% each, and the bottom to brands at 17% each – a span of only 6%.
Shape of Data (if applicable)	Data was more clustered around the lower end, between 42.56 and 65.43 seconds for sandwich assembly time, with the data spread more evenly on the upper end.	Because there was not much variation, the shape of this data is fairly even. However, if Shef B. Foods wanted a more complete study of the impact of peanut butter brands, it would be better to have the brands equally represented in their experimental data.

 

 

Organizing, Summarizing, and Visualizing Data

In order to have the information necessary to fill in the table above (or really, to tell the story within the data), we’ll need to look beyond the raw data and do at least some basic analysis. Performing even a basic analysis will let us do more than just tell the story – it will enable our organization to make better decisions and solve problems.

So, to get to the story being told by the data, we have three crucial steps:

1. Organizing the data (which sometimes involves “cleaning”)
2. Summarizing the data (which involves basic statistics calculations and tabulating results)
3. Visualizing the data (which involves charts and graphs)

Organizing Data

After collecting data, even expensive data, many organizations find that their efforts stall or fail because the data is messy or flawed. Working with messy and unorganized data can be tedious and lead to errors during analysis. Furthermore, there may be need for quick, simple calculations or insights that do not require a full analysis. Why go to the trouble of a full analysis if you just need a couple sales figures?

Sorting Data

Sorting data provides an easy way to see the maximums, minimums, and extreme data points that might be of interest. When sorting by date, the data can be scanned for trends, cycles, or other time-based phenomena. We can also established rankings or priorities by sorting the data based on a key metric or performance target.

Filtering Data

Excel provides the user with a versatile filtering mechanism, for both text and numerical data. With a set of data that includes dates, suppliers, production times, quality ratings, or costs, filtering allows users to drill down into the when, where, and in which group specific issues are occurring. Of course, in a spreadsheet format the users still have to scroll through the rows and columns of data and try to pick out the important information.

Excel offers several tools for quickly cleaning and organizing data so that it can be quickly scanned and also used more effectively for full analysis. See Example in Appendix 2 to see how filtering and sorting can be used to quickly discover data issues, resolve the issues, scan for interesting findings, and prepare for a complete analysis.

The reader is encouraged to follow along with the Example in Appendix #2 at the end of the chapter to see Excel’s sorting and filtering capabilities at work on a messy data set from Shef B. Foods.

Summarizing Data

Summarizing data is bulk what occurs in an introductory Statistics class. In this text, we will rely solely on Excel’s capabilities, with a focus on simple interpretations of the calculations. Students are directed to their university’s or college’s statistics classes for a full treatment of these measures and their explanations.

We want to focus on two areas of summarizing data: Descriptive Statistics and Tabular Summaries (Sometimes called “crosstabs”).

Descriptive Statistics

Cleaned Excel Data File: Clean Sandwich Data

Data Opened in Excel:

The data above shows raw data, already cleaned, and ready for analysis. The pivot table tool creates a “sandbox” environment for exploring cross sections and calculations from our data set. Anything we do in our pivot table will leave the original data untouched.

Pivot Table Sandbox Environment:

We can use Pivot Tables to glean valuable information from the Shef B. Foods data set regarding sandwich assembly, customer ratings, and sandwich maker (chef) performance.

Numerical Summaries with Pivot Tables

Suppose in our Shef B. Foods data set, we wanted to get numerical statistics summaries for the Kitchen Assembly Time column (measures how fast the Chefs assemble each sandwich). Using pivot tables, and only a few clicks of the mouse, we can get the Minimum, Average, Maximum, and Standard Deviation. The table below shows what this summary looks like:

Where pivot tables especially shine is when specific results are needed for each category or grouping of data. The practice data file “Clean Sandwich Data” has categories for chef name, peanut butter type, customer ratings (which could be considered categorical or numerical), peanut butter brand, and bread brand. We include a few pivot table results below to show results from our preliminary analysis of the practice data.

Pivot Table #1 (below) shows the calculated statistics for sandwich assembly times, by Chef. This table took only 13 clicks of the mouse. It was done so quickly, that it took longer to change the names of the columns! (disclaimer: We do NOT receive royalties from the Pivot Table “company” for our endorsement of its capabilities).

Pivot Table #2 (below) shows the same chart, but broken down by type of peanut butter used. To create this table, we made a quick copy (“Ctrl+C”) of the original table above, pasted it below it on the same worksheet (“Ctrl+V”). Then, we used the Pivot Table sandbox (aka “Pivot Table Field List”) to swap the Chef column for the Peanut Butter Type Column, and changed the column name. SUPER FAST. We can see that pivot tables are like creamy peanut butter: fast (it clearly took less time to assemble creamy peanut butter and honey sandwiches than chunky).

Peanut Butter Type	Min Assembly Time	Average Assembly Time	Max Assembly Time
Chunky	79.23	101.8684211	116.89
Creamy	42.56	62.56551724	87.65
Grand Total	42.6	78.1	116.9

Pivot Table #3 (below) shows the average temperature of the sandwich for each level of customer rating. It seems like the warmer the sandwich, the higher the customer rating.

Customer Rating	Average Sandwich Temperature °F
1	65.9
2	71.0
3	79.5
4	85.2
5	85.5
Grand Total	79.6

You should see if you can discover anything else interesting in this data after following the example at the end of this chapter. Do the different Chefs earn higher customer ratings? Does the brand of bread make any difference to the customers? Even though the data is largely fictitious, you can get some good practice using pivot tables in Excel.

 

Visualizing Data

Categorical data and numerical data can be visualized in charts and graphs, but, despite a similar appearance, the two data types require completely different graphs. Recall from the earlier section on Summarizing and Describing Data.

We have five primary areas to describe: quantity or size of the data, what’s typical, what’s rare, the variation, and shape of the data. The charts and graphs introduced below are meant to help portray each of those five areas for each data type.

Categorical Data Visualizations:

For categorical data, we generally count data in each category, compute proportions, create tables and graphs, and then interpret the graphs based on each of those five areas to describe what the data shows. There is more we could do with categorical data, such as word clouds, cluster analysis, and more, but more advanced categorical visualization is beyond the scope of this textbook.

Here, we focus our attention on bar charts, column charts, line charts, and pie charts, as these are most commonly used to portray categorical data. Using the practice data, we generated a number of categorical visualization below:

From top left, and moving clockwise, the first chart is called a “Column Chart” in Excel, and shows the counts in each category. The column chart seems very basic, yet it is completely effective. We could easily replace the bars with a line (call it a “Line Chart”) and get the same idea of what is happening with the data.

The top right chart is called a “Bar Chart” in Excel, and shows the same information as the column chart, but turned on its side to display the counts horizontally.

The bottom right chart is another column chart, but the vertical (y) axis is showing the proportion of each category. It is literally the exact same chart as the top left, but the axis is showing percentages instead of counts. Which one do you like better?

Finally, the bottom left chart shows a pie chart, which shows the same information again, but you might have to look closer to figure out the details. It is a hopeful possibility that, someday, a famous scientist will finally decide that pie charts are worthless and they will be removed from the insert chart options in Excel – but for now, pie charts still exist.

Overall, these categorical charts should be used to describe the data, not just take up space on a page.

Looking at these charts, we can describe the data as follows:

• • Meta Data: There are about fifty sandwiches represented in the data, the data was collected to understand how customers feel about different ingredients and specific Chefs.
  • Typical Values: Jif and Skippy Brands are most represented (11 each), with Peter Pan close behind (10).
  • Rare Values: Smucker’s and Adams are the least represented brands, but they are not rare.
  • Variation of Data: The five brands are fairly evenly represented, with 8-11 sandwiches made with each brand of peanut butter.
  • Shape of Data: The graphs show a cluster (only a slight imbalance in this case) of data around Jif and Skippy, but not drastically different than the other categories.

To create these graphs yourself, you can follow along with Example ## at the end of the chapter.

Numerical Data Visualizations:

Numerical data allows for a much wider variety of visualization options, and when coupled with categorical data provide a plethora of opportunities to improve our understanding of the data.

We will explore visualizations of three types:

• • Single-variable visualizations (where only one column is being visualized)
  • Multi-variable visualizations (where more than one column is being visualized)
  • Category-specific numerical visualizations (where visualizations of type (1) or (2) are created, but within specific categories).

Single-variable numerical visualizations

For a single-variable visualization, we want to understand all the numbers in a single column of data. In the Shef B. Foods practice data, we will look at the data from the “kitchen_assembly_time_sec” column, which shows how much time it took to assemble each sandwich in the data set.

Common single-variable charts include histograms (which look like column charts), and box-and-whisker plots (which look like a rectangular box split in two with whiskers protruding from both ends).

Below we have included both types of single-variable numerical data charts, but first the focus is on histograms:

Because there are multiple ways to create histograms in Excel, we are showing the resulting charts from two separate methods. This illustrates the challenge of interpreting histograms for numerical data, which as you can see here, can appear to show two different sets of data despite being generated by the same data column.

To interpret these histograms, start by looking at the number ranges along the x-axis (bottom of the chart). On the left-hand chart each number range (referred to as the ‘bin’) includes about 22 seconds (i.e. 64.56 to 86.56 seconds in the second bar), whereas the right-hand chart ranges are approximately12.4 seconds wide (i.e. 42.6 to 54.9 seconds in the second bar).

The histogram y-axis shows the number of sandwiches with assembly times within the time ranges shown at the bottom of each bar. There are roughly 7 sandwiches included in the second bar on the left-hand chart, and about 8 in the second bar of the right-hand histogram chart.

Since the standard time range used is different for each chart, the count of sandwich times within those ranges are also different, leading to widely different histogram shapes for the same set of data.

For this reason, the box-and-whisker plot might be a better tool to visualize the data for a single variable. It quickly shows us the minimum, maximum, and median values, and can even display individual points. This makes it much easier to assess typical values, rare values, as well as both the variability and the shape of the data.

The two plots below represent the same data set, with the left chart showing the built-in Excel output. The right chart shows the same box-and-whisker plot with edits, displaying each of the individual assembly time data points as small dots/circles, numbered labels for each quartile, including the minimum, maximum, and median values from the data.

The above box-and-whisker plots allow for identification and assessment of most of the key characteristics of a data set. Below is an attempt to portray the basic ‘story’ of the sandwich assembly time data.

• Quantity: There are about 50 sandwiches represented (based on previous findings)
• Typical Data: The median sandwich assembly time is 78.395 seconds, so 50% of the sandwiches took longer to assemble, and 50% took less than 78.395 seconds to assemble.
• Rare Data: There doesn’t appear to be any rare data, but it is interesting that between about 65 seconds and 80 seconds there are only two data points. So, we can say it is rare to see a sandwich assembled in that time window.
• Variability of Data: Sandwiches in this data set took between about 42 seconds and 117 seconds to assemble. There is an interesting change in variation as the data points seem to cluster at the endpoints but are more spread out in the middle.
• Shape of Data: The data is sparse in the middle (between 65 and 80 especially) and much more densely arranged at the upper and lower ends. This is an example of a bimodal shape – where data seems to be clustered in two separate areas. There may be a good explanation for this phenomenon.

Multi-variable numerical data visualizations

When two or more numerical variables (such as “kitchen assembly time” and “sandwich temperature” or “peanut butter volume”) are found within a data set, we often want to see if there is a relationship between them.

To visualize a relationship between two variables, we use a scatter plot – also called an “X,Y Scatter Plot”. It is critically important to carefully decide which variable is “X” and which variable is “Y”.

From the Shef B. Foods practice data, we might wonder if the assembly time in the kitchen affected the temperature of the sandwiches. It seems like a plausible hypothesis.

In defining which variable should be used for “X”, we can think of the “X” variable as the “influence”, “input”, “predictor”, or “independent” variable. In this example, we are wondering if the assembly time affects, or predicts, or influences, the sandwich temperature. So, assembly time will be our assigned “X” variable, and the numbers along the X-axis will represent assembly time.

In the case of the “Y” variable, the sandwich temperature, it will be represented on the Y-axis. The “Y” variable is often referred to as the “dependent”, “response, or “output” variable.

Finally, to set up the data for Excel to build a graph, the “X” variable column of data must be on the left side of the “Y” variable column, even if the columns are not side-by-side. Review the tutorial on building this graph for more guidance, if needed.

The graph is shown below:

Telling the story of this data is a bit different than previous data, as there are two variables represented. It is helpful to ‘walk’ along the X-axis and see how the dots change in relation to the Y-axis. Remember, each dot represents a single sandwich, where the associated X-axis positioning represents that sandwich’s assembly time, and the associated Y-axis position of the same dot represents the single-sandwich’s temperature.

In this case, there isn’t much to say. For kitchen assembly times on the lower end (40-65 seconds, outlined in orange below), the temperature shows both high and low figures in relation to the Y-axis.

The same could be said for the other values for kitchen assembly time, though there are some minor differences. In the assembly times between 70-90 seconds (in blue), the temperatures aren’t as high, or as low, over all. Still, there is no clear pattern of increasing or decreasing temperatures based on assembly time.

Finally, in the green highlight, where the temperature data seems to be missing along the 80 °F range on in the Y-axis, there are still both low and high temperatures, regardless of how much time the kitchen assembly took.

Overall, we would say that there doesn’t seem to be a relationship between assembly time and sandwich temperature.

If you’ve ever made a peanut butter sandwich, you know that the sticky, thick consistency of peanut butter makes it harder to assemble the sandwich. More peanut butter seems like it should affect assembly time, based on our own experience. In this case, we are wondering if peanut butter volume influences assembly time, so peanut butter volume should be used as the “X” variable, and assembly time should be used for the “Y”.

The next graph shows a clear relationship between the amount of peanut butter in grams (X) and the assembly time (Y).

It is pretty simple to make out a rough pattern emerging in the data, as highlighted in the next image in a diagonal red oval. Clearly, as the points move along the X-axis values (increased peanut butter volume), the points move higher in the Y-axis direction (longer assembly time).

Two potential outliers are also highlighted in purple, as these two sandwiches had values that suggest a different relationship between the two variables.

The first potential outlier sandwich contained 20 grams of peanut butter but took almost twice the assembly time compared to other sandwiches with only 20 grams of peanut butter.

The second potential sandwich outlier had more peanut butter (at 50 grams) than any other sandwich in the data set, but had an assembly time of about 50 seconds – on par with sandwiches containing only about 1/4^th the peanut butter.

Overall, we would say that peanut butter volume is highly correlated with assembly time, barring the two outliers highlighted above. As peanut butter volume increases, sandwich assembly times also tend to increase.

Working with three or more numerical variables

While scatter plots are great for looking at two numerical variables in graphical form, it gets a lot harder when you have more than two. Sometimes it is beneficial to look at three dimensions, or even more as the number of important variables increase.

With three variables, there is a bubble chart available in Excel where the dots of the scatterplot are replaced with circles sized according to the third variable of interest. However, these charts are difficult to interpret unless generated by a small data set.

Beyond this, it is more common to do pairwise, two-variable scatterplots, or take a purely statistical route with the technique of multiple regression – which is beyond the scope of this text.

Category-specific numerical visualizations

Looking at numerical visualizations from the perspective of categorical variables can be very insightful. For instance, we can view the previous scatter plot by peanut butter type (categorical variable).

The color-coded data shows that not only were slower assembly times associated with more peanut butter, they were also associated with the chunky peanut butter type. Looking closer, you can see that none of the chunky peanut butter sandwiches were assembled in less than about 80 seconds, while the creamy peanut butter sandwich were normally assembled in less than 80 seconds, barring a few exceptions.

Excel also makes using box-and-whisker plots with categorical variables very simple. For example, we have plotted sandwich assembly time in a set of box-and-whisker plots, separated by peanut butter type.

Similarly, this chart shows the huge difference in assembly times, based on the type of peanut butter used. Chunky peanut butter sandwiches generally took longer to complete.

Good Data Visualizations Require Creativity and Practice

While these visualizations and other approaches to telling the story of data might make sense for the example used in the text, data encountered in industry settings may not provide the same insights using the same techniques. Instead, analysts and other professionals will need to ensure that they are using the right analysis techniques for the right purposes. And it isn’t always clear which data is important, or how the data should be analyzed or presented.

To help with this process, we are going to deploy a foundational structure, called the C.A.R. Structure, or the C.A.R. Method, to guide you as you learn to address complex situations in the real world – regardless of your major or your career path.

The C.A.R. Method

Because analyzing data, calculating statistics, and creating graphs are such exciting activities, we need to be certain not to lose sight of the purpose of our supply chain: to generate value.

Looking beyond the adventures surrounding Excel and other analytical software, we want to remember to focus on the Context. The context includes our organization’s situation, its background, the questions we need answers to, and what we hope to gain from collecting and analyzing the relevant data.

Once we have the data, our Analysis should be purposeful, focused, and ensure that we have verifiable answers to the all-important questions. Just because we can perform a certain analysis, or create a certain kind of chart, doesn’t mean that it results in valuable insights.

The answers generated from our analyses should provide guidance as we develop Recommendations for a solution, a plan, or a pathway forward. All recommendations can be improved by considering the entire system or supply chain that might be impacted by our actions or reactions. We certainly don’t want to create more, or even larger, problems than we started with.

The C.A.R. structure (Context, Analysis, Recommendations) provides a simplified approach to addressing issues (simple or complex) within any organization or across its supply chain.

Now that we have an understanding of basic data analysis, visualization, and storytelling, we can see and apply the C.A.R. structure more effectively. The example below shows a fictitious example of the C.A.R. method at work in the real world.

Final word on the C.A.R. Method

Similar to the Example, students will have the opportunity to apply the C.A.R. method to supply chain situations throughout this textbook. To learn the C.A.R. method, students will first need to practice seeing the technique (or some variation) applied by someone else to a real-world situation. After recognizing the process, students will have opportunities to apply the technique to more open-ended situations, as in a business case study. In this text, our case studies have been targeted to first-time case analysts. More advanced case analysis techniques common to advanced business or supply chain courses will build on the foundation of the C.A.R. method in one form or another.

DISCUSSION AND EXCERCISES

Discussion Questions

1. What kind of data do you use on a daily basis at home, school, or work? What type of data is it? How do you analyze the data? What decisions do you make based on the analysis?
2. How does the desktop version of Microsoft Excel compare with the online version of Google Sheets.
3. Have you tried data analysis with a Generate AI tool? If so, which tool was it and how did it go? If not, see if you can try one now, and we can discuss how it went.
4. Which undergraduate university majors benefit from data analysis skills?

Essay/Reflection Questions

1. Describe the role of storytelling in presenting data to stakeholders. What are the consequences of good storytelling vs. bad storytelling?
2. After learning about and using the C.A.R. method for addressing complex issues, what practice(s), if anything, could be added to the C.A.R. method to improve it?
3. Compare and contrast calculated data summaries (i.e. averages, proportions, standard deviations, etc.) with data visualizations. Which method for summarizing data do you prefer, and why?

Practice Exercises

A. Diamond Data – Complete the following exercises based on the “Diamond Data” Excel file, found here: Diamond Data.

A.1. Calculate basic summary data for the “Price($)” column, but only for diamonds certified by GIA.

• • • Include: Average, Median, Standard Deviation, Min, Max, Range
    • Include brief statements for meta data, typical data, rare data, variability of data, and shape of data.

A.2. Calculate basic summary data for the “Clarity” column, but only for diamonds with the color “G”.

• • • Include: Names for each category, counts of each category, proportions (%) for each category
      • You can use a PivotTable to efficiently generate your summary.
    • Include brief statements for meta data, typical data, rare data, variability of data, and shape of data.

A.3. Create two different visualizations for the “Carat” column, but only for diamonds certified by “HRD”.

• • • Include: Histogram, Box-and-Whisker plot
    • Include brief statements for meta data, typical data, rare data, variability of data, and shape of data.

A.4. Create a visualization that looks at the relationship between “Carat” to “Price($)”, but only for diamonds with “VVS1” clarity.

• • • Include: Scatter plot, with “Price($)” used as the Y-variable and represented on the Y-Axis.
    • Include brief statements on what you think the visualization/chart shows.

A.5. Create a visualization showing the relationship between “Clarity” and “Certification Body”.

1. 1. • Include the “Crosstab” table from the PivotTable tool in Excel.
      • Use the PivotTable to create a color-coded column chart or bar chart.
      • Include brief statements on what you think the visualization/chart shows.

B. Real Estate Data – Complete the following exercises based on the “Real Estate Data” Excel file, found here: Real Estate Data.

B.1. Summarize and Graph the “Fair Market Value($000)” data, based on which “School District” the homes are in.

• • • Include: Average, Median, Standard Deviation, Min, Max, and Range calculations for Fair Market Value summaries in each school district.
    • Create Box-and-Whisker plot visualizations for Fair Market Values in each school district
    • Include brief statements for meta data, typical data, rare data, variability of data, and shape of data.
    • Compare and contrast the data for home prices across the school districts.

C. Shef B. Foods Data – Complete the following exercises based on the “Clean Sandwich Data” Excel file, found here: Clean Sandwich Data

C.1. Continue on from the example presented in these slides (CAR for ShefBFoods Flavot Ratings) , to complete the analysis and recommendations for improving the Speed of Assembly AND Raw Material Costs.

• • • Include Charts, Graphs, and Calculations in your analysis that support your recommendations.
    • Structure your approach using the C.A.R. Method, and include your written discussions with your file submission on Canvas.