Unit 2 Tutorials: Descriptive Statistics

INSIDE UNIT 2

Descriptive Measures

Using Measures of Center

Calculating Measures of Spread
Correlation and Covariance
Regression Analysis

Data Visualization

Telling Your Story

Table Design Principles
Crosstabulation
PivotTables
Creating Charts
Histograms and Frequency Polygons
Effective Data Dashboards
Case Study: Schmickel’s Pickles

Using Measures of Center

by Sophia

 WHAT'S COVERED

In this lesson, you will learn about four measures of central tendency. Specifically, this lesson will cover:
1. Measures of Central Tendency
1a. Mean
1b. Geometric Means for Growth Rates

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 1
 1c. Median
1d. Mode
2. Comparing Mean, Median, and Mode

 BEFORE YOU START

Before beginning this lesson, we suggest you review Sophia’s Introduction to Statistics course for a
refresher on several key ideas. Specifically, in this lesson, we will cover the following topics from the
Introduction to Statistics course:
Data Analysis
Shapes of Distribution
Mean, Median, and Mode
Measures of Center

1. Measures of Central Tendency

The mean, median, and mode are often referred to as measures of central tendency because they provide a
central or typical value of the data. In business analytics, few concepts are as fundamental as mean, median,
and mode. These statistical measures of center serve as the backbone of data analysis, providing insights into
various aspects of business operations, customer behavior, and market trends. In this tutorial, we will
investigate the mechanics of mean, median, and mode in the context of analyzing data, exploring their practical
applications, their common misinterpretations, and real-world examples of their use and misuse.

 TERM TO KNOW

Measures of Central Tendency

The mean, median, or mode of a data set.

1a. Mean
Let us begin with the mean, perhaps the most familiar of the three measures. The mean, also known as the
average, is calculated by summing all values in a dataset, and then dividing by the total number of values. The
mean provides a snapshot of the central tendency of the data and is widely used in business analytics in
multiple ways, from calculating revenue per customer to assessing employee productivity.

It is important to remember that the mean can be from a population (denoted by μ) or a sample (denoted by x̅
).
A population mean considers all data in a complete set, while a sample mean is the average of a subset of a
population. The population mean is a parameter, and the sample mean is a statistic. In other words, the
population mean is a fixed value that describes the entire population, while the sample mean is a variable
estimate based on a sample from that population. As a business analyst, it is paramount that you understand
whether you are working with population or sample data, as many analytical methods will vary based on
whether the data is population or sample.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 2
 The table below displays how to calculate a population and a sample mean. For a mean, the two formulas are
the same, but the notation used to denote population mean and sample mean is different.

Calculation Formula Notes

μ = population average
Population Mean X = individual values of population
N = count of individual values

x̅= sample average

Sample Mean x = individual values of population
n = count of individual values in sample

 HINT

The Greek sigma, written as Σ, is a symbol in mathematics and statistics to indicate to “add up” or “sum
together” a series of numbers. Sometimes, Σ has indices that tell you where to start and stop adding. For

example, in the lower index is , meaning that you start at 1. The upper index is n,

which usually denotes the total number of observations in a data set for a particular variable, meaning that
you end at your last data point in the data set. The lowercase values of x denote the data values.

If you wanted to use the ∑ notation to sum up the number of patients that a veterinary sees each day who
are delinquent in paying their bills, you would perform the following.

Day Number of Patients

Monday 8

Tuesday 9

Wednesday 10

Thursday 2

Friday 5

Means can be heavily influenced (skewed) when outliers are present. Skew happens when data is not
distributed symmetrically, it is the measure of asymmetry in the distribution of data. If data is skewed to the
right, most of the outliers will be present on the right side of the distribution. In a right-skewed distribution, the
mean will be larger than the median. If data is skewed to the left, most of the outliers will be present on the left
side of the distribution. In a left-skewed distribution, the mean will be smaller than the median. If data is not

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 3
 skewed, it is considered symmetric. Symmetric data is data that is evenly distributed around the mean. The
figure below illustrates these concepts.

Situation Skew

Mean > Median Right

Mean > Mode Right

Mean = Median Normal

Mean = Mode Normal

Mean < Median Left

Mean < Mode Left

IN CONTEXT
The Case of Misleading Averages

Retail Row, a low-price retail chain, is analyzing regional average sales to evaluate performance.
Assume there are four regions, and there are five stores per region. Region A boasts impressive
average sales figures, leading management to shower it with praise and incentives. However, upon
closer inspection, it is revealed that this average is skewed by a few exceptionally high-performing
stores, while the majority struggle to meet targets. By relying solely on the mean, the company fails to
identify underperforming stores in need of support and optimization.

While the mean provides valuable insights, it can be easily influenced by outliers, leading to skewed
interpretations and misguided decisions. Always examine the distribution of data alongside the mean
to gain a comprehensive understanding.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 4
  TRY IT

Let’s calculate some averages for the Retail Row example.

1. Open the retail_row_rents.xlsx data in Excel.

The data provided are the rent expenses as reported by four regional managers for Retail Row. Each
regional manager is responsible for five stores. The averages reported are in the $1,000s.

You are going to calculate the average rent for the five stores in each region using the AVERAGE() Excel
function. The argument required for the AVERAGE() function is the range of cells that contain the data you
wish to take the average of.

2. In cell B7, enter the following formula and select Enter.

=AVERAGE(B$2:B$6)

You will obtain an average value of 39.44 ($39,440) for the five stores in the Northern region.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 5
 Regional managers report the average rent expense for their regions as follows (in $1,000’s):

Northern Region: 39.44

Southern Region: 39.58

Eastern Region: 39.90

Western Region: 39.34

Based on these regional averages, management proposes that the Western regional manager should
receive a bonus for her careful attention to rent expenses. The Northern regional manager disagrees. To
resolve the case, management presents you with a spreadsheet of yearly rent expenses, by region and
store.
 TRY IT

Use what you have learned about means to resolve this issue for Retail Row. Hint: recall the effect of
outliers on means.

Do the data support a decision to give the Western Manager a bonus? +

Even though the Western region has the lowest rent expense average, its other stores (except for store
four) have higher rent expenses than the comparable regions. Store four has an unusually low rent
expense, which skews the data when using averages. Averages can be misleading, due to (in this case)
one store with an exceptionally low rent expense.

What other data might you need to present your case? +

It would be helpful to have to following data:

Sales data for each store—this could help justify the expense of each store.
The city in which the store is located—rent is more expensive in some areas than others.
Net profit/loss for each store—determining rent expense as a percentage of profit can help in the
analysis of the expense.
What other data might you choose?

This data could help to add clarity to why rent expenses are high or low in some areas.

 WATCH

Follow along with this video on calculating average rent.

 TERMS TO KNOW

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 6
 Mean
The sum of values divided by count.

Population Mean
A mean that is calculated from a population.

Sample Mean
A mean that is calculated from a sample.

Skew
The measure of asymmetry in the distribution of data.

Skewed to the Right

Data in which most of the outliers are present on the right side of the distribution.

Skewed to the Left

Data in which most of the outliers will be present on the left side of the distribution.

Symmetric Data
Data that is evenly distributed around a mean.

1b. Geometric Means for Growth Rates

Let’s consider an example of when you would need a geometric mean as a motivation to understand why we
need an alternative to the standard mean calculation (arithmetic).

The arithmetic mean can be misleading when calculating average growth rates due to its additive nature. The
arithmetic mean treats each data value equally, sums them up, and divides them by the total number of
observations. However, growth rates are inherently multiplicative, not additive. When we say that a growth rate
is multiplicative, we are referring to how changes in a quantity are expressed relative to its previous value. A
multiplicative change occurs when a value increases or decreases by a certain percentage (or factor) relative to
its previous value. Instead of adding or subtracting a fixed amount, we multiply or divide by a factor. When
calculating an average growth rate, you are examining how multiplicative changes occur over time.
Multiplicative changes, such as growth rates, compound over periods, while additive changes, like the number
of units sold per quarter, do not.

The geometric mean is a measure of central tendency that is calculated by taking the nth root of the product of
n values. The formula for the geometric mean is given by:

 FORMULA TO KNOW

Geometric Mean

The subscript g on the sample mean symbol (x̅

) denotes the mean is a geometric sample mean.

The geometric mean is appropriate for analyzing growth rates in financial data.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 7
 EXAMPLE Suppose you are analyzing the annual growth rates of a company’s revenue over three
years. The growth rates are given below:

Year 1: Revenue increased by 10%

Year 2: Revenue increased by 15%
Year 3: Revenue increased by 30%

To calculate the geometric mean, add 1 to each percentage before multiplying them together to avoid
negative percentages. For example, if a company had a -5% decrease in revenue, if we add 1 to this
percentage, the percentage used in the calculation will remain positive

The geometric mean for this data would be:

The geometric mean for this data is 1.18. This means that, on average, the company’s revenue grew by ~18%
annually over the three-year period.
The example below illustrates how the arithmetic mean is a poor measure when calculating an average with
data that has a compounding effect.

EXAMPLE You are interested in calculating the mean growth rate over a four-year period, based on the
percentage changes from the previous year (PY). You can follow along with the formulas used in this
example by opening GvA_Growth.xlsx.

Model A: The geometric mean accounts for compounding effects by multiplying all percentage changes
together and taking the nth root (where n is the number of years).

1. Open GvA_Growth.xlsx.

2. Convert the percentages to decimal form by selecting Column C and changing the number format to
General.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 8
 3. Add 1 to each decimal to help avoid having negative percentages. In the cell next to C3, enter the
following formula and select ENTER.

=C3+1

4. Use the fill handle (a small square at the bottom-right corner of the cell) to drag the formula down from
D3 to D6. This will apply the same calculation to the other cells in Column D.

5. Now calculate the geometric mean growth rate. If you want, you can label a cell A8 with Geometric
Average and then in cell D8, enter the following formula and press ENTER.

=GEOMEAN(D3:D6)

6. To obtain the mean growth rate, we will subtract 1 from this average and then view the calculation as a
percentage.

In cell D7, place the following formula and press ENTER.

=D8-1

7. To see the answer as a percentage, ensure Number Format is set to Percentage.

If calculated by hand, the formula for this calculation would be:

Model B: The arithmetic mean simply adds up all the percentage changes and divides by the number of
years.

1. Calculate the arithmetic mean growth rate. If you want, you can label cell A9 with Arithmetic Mean and
then in cell D9, enter the following formula and press ENTER.

=AVERAGE(C3:C6)

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 9
 2. Ensure Number Format is Percentage for this cell.

The formula for this calculation, if calculated by hand, would be:

Check your work by comparing it to the table shown at the beginning of the example.

The spreadsheet below compares these two models of revenue growth from 2021-2025. Model A is based
on the geometric mean, and Model B is based on the arithmetic mean.

Which average accurately captures the rate of growth over this period? Let’s start with the actual year 1
revenue and apply the geometric and arithmetical calculated average growth rates.

Which final revenue value best matches the actual 2025 revenue? +

The geometrically calculated mean is identical (Model A), but the arithmetically calculated mean is
overestimated by over $300,000!

The geometric mean is the most suitable measure of central tendency in this scenario because it accounts
for the compounding effects inherent in growth rates, thereby accurately reflecting consistent proportional
growth over time.

 KEY CONCEPT

Use geometric means for data involving growth rates or returns over multiple periods, such as investment
returns, sales growth, or population growth.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 10
  TERM TO KNOW

Geometric Mean
A central tendency measure that is appropriate for data with a multiplicative nature.

1c. Median
The median is the middle value of a data set, when arranged in ascending (smallest to largest) order. Unlike the
mean, the median is not affected by extreme values, making it a robust measure of central tendency,
particularly in skewed distributions. In business analytics, the median finds application scenarios such as
determining the typical salary in a company or assessing housing prices in a real estate market.

 HINT

Recall, if the dataset has an odd number of entries, there is a true median. If the dataset has an even
number of entries, the median is the average of the two middle numbers, when arranged in ascending
order.

IN CONTEXT
The Tale of Two Salaries

Consider a Human Resources (HR) department analyzing employee salaries. The mean salary appears
inflated due to a few high-paid executives, giving the impression of generous compensation across
the board. However, when the median salary is calculated, it paints a different picture, revealing that
most employees earn significantly less than the mean suggests. This discrepancy prompts HR to
reassess its compensation strategy and ensure equitable pay practices.

The median serves as a reliable indicator of central tendency, especially in datasets prone to
skewness or outliers. Incorporate the median alongside the mean to gain a more nuanced
understanding of the data distribution.

 TRY IT

Returning to the Retail Row scenario, you have now been provided with the net sales for the company for
the five stores in all four regions.

1. Open the retail_row_sales.xlsx file in Excel. The sales are presented in $1,000s.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 11
 Calculate the median net sales for all stores using the MEDIAN() Excel function. The argument required for
the MEDIAN() function is the range of cells that contain the data you wish to take the median of.

2. In any empty cell, enter the following formula and select Enter.

=MEDIAN(B2:E6)

The cells B2 through E6 contain all the values of the net sales for all stores.

The formula should have returned 78.2. The median sales for all stores across all regions is $78,200.

3. Repeat these steps, but now find the average of the net sales. In another empty cell, enter the following
formula and press Enter.

=AVERAGE(B2:E6)

The formula should have returned 76.54. The average net sales for all stores is $76,540.

Since the mean is a little less than the median, you can conclude that the data is slightly left-skewed. When
data is skewed, the median is a better measure of central tendency than the mean.

 WATCH

Follow along with this video on calculating average and median net sales.

 TERM TO KNOW

Median
The middle value of a data set, when the data is arranged in ascending order (smallest to largest).

1d. Mode

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 12
 The mode is the value that appears most frequently in a dataset. While less commonly used than the mean and
median, the mode offers unique insights, particularly when working with categorical data. Assume you want to
know which department customers visited in a department store, so you ask a random sample why they came
to the department store that day. If you constructed the frequency distribution for departments shown in the
table below, the Toy category would be the mode because it is the category that appears the most (has the
largest frequency).

Product Category Frequency

Electronics 10

Clothing 8

Home Appliances 5

Toys 12

Beauty 6
In business analytics, the mode finds relevance in identifying the most popular product or service,
understanding customer preferences, and optimizing inventory management.

IN CONTEXT
The Inventory Conundrum

A bookstore is concerned with inventory management. Because inventory is disproportionately

allocated, often popular items are not in stock, while they carry excess inventory of slower-moving
products. The management team relies on historical sales data to predict demand and ensure
adequate stock levels, but overlooks the importance of identifying the best-selling products. This
oversight not only impacts sales but also incurs additional storage and handling costs. In this case,
identifying the mode (the most popular items) could help assure the bookstore of having the correct
inventory levels of popular items.

Do not underestimate the power of the mode in identifying patterns and trends within categorical
data. Incorporate it into your analysis to optimize decision-making and resource allocation.

 TERM TO KNOW

Mode
The value that appears most frequently in a data set.

2. Comparing Mean, Median, and Mode

Now that we've explored the significance of mean, median, and mode in business analytics, let's review some
real-world examples of companies that have struggled with the analysis of these measures. See if you can

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 13
 identify the problem.

 TRY IT

Real-World Missteps: Business Paralysis Due to Bad Analysis

Misstep 1: The "Average" Coffee Price

A trendy coffee shop chain decides to set its prices based on the average cost of coffee in the city. They
calculate the mean price across various competitors and proudly announce their pricing strategy.

What’s the problem? +

The coffee shop failed to consider that the mean is heavily influenced by outliers—including luxury
cafes serving premium blends. As a result, their pricing falls significantly above what most customers
are willing to pay, leading to dwindling sales and disgruntled caffeine aficionados.

Misstep 2: The "Median" Marketing Campaign

A multinational corporation launches a marketing campaign targeting millennials, citing the median age of
the demographic as justification.

What’s the problem? +

This company overlooked the fact that the median represents the midpoint of the age distribution, not
necessarily the peak of the target audience. As a result, their campaign fails to resonate with the
intended demographic, leaving them reevaluating their approach.

Misstep 3: The "Mode" Mishap

A fast-food chain has decided to revamp its menu based on customer feedback, focusing on the most
commonly requested items. They conduct surveys and interviews, compiling a list of popular menu choices.

What’s the problem? +

They failed to account for regional preferences and seasonal variations, resulting in a menu dominated
by burgers and fries while neglecting regional delicacies and healthier options. Customers are left
disappointed, and the chain faces backlash for its lack of product diversity and innovation.

The mean, median, and mode are indispensable tools of business analytics, offering valuable insights into data
distributions, central tendencies, and patterns. However, their misuse or misinterpretation can lead to erroneous
conclusions and misguided decisions, as evidenced by the previous examples. By understanding the strengths
and limitations of each measure and incorporating them into analysis, businesses can unlock deeper insights,
optimize performance, and avoid statistical blunders along the way.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 14
 So, the next time you find yourself crunching numbers or analyzing data, remember the importance of the
mean, median, and mode—and the lessons they can teach us about business analytics.

 SUMMARY

In this lesson, you learned about four measures of central tendency: mean, geometric mean, median,
and mode. Guidelines were provided for choosing between each of the measures of central tendency.
Examples were given of misguided consequences that can occur if the incorrect central measure of
tendency is selected in a business analytics scenario.

Source: THIS TUTORIAL WAS AUTHORED BY SOPHIA LEARNING. PLEASE SEE OUR TERMS OF USE.

 TERMS TO KNOW

Geometric Mean
A central tendency measure that is appropriate for data with a multiplicative nature.

Mean
The sum of values divided by count.

Measures of Central Tendency

The mean, median, or mode of a data set.

Median
The middle value of a data set, when the data is arranged in ascending order (smallest to largest).

Mode
The value that appears most frequently in a data set.

Population Mean
A mean that is calculated from a population.

Sample Mean
A mean that is calculated from a sample.

Skew
The measure of asymmetry in the distribution of data.

Skewed to the Left

Data in which most of the outliers will be present on the left side of the distribution.

Skewed to the Right

Data in which most of the outliers are present on the right side of the distribution.

Symmetric Data

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 15
 Data that is evenly distributed around a mean.

 FORMULAS TO KNOW

Geometric Mean

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 16
 Calculating Measures of Spread
by Sophia

 WHAT'S COVERED

In this lesson, you will learn about four measures of spread to quantify the variability in a data set.
Specifically, this lesson will cover:
1. Measures of Spread (Dispersion)
1a. Range
1b. Variance
1c. Standard Deviation
1d. Coefficient of Variation
2. How Businesses Use Measures of Spread

 BEFORE YOU START

This lesson builds on key concepts from an Introduction to Statistics course. Specifically, we will cover the
following topics, assuming familiarity with these foundational ideas:
Data Analysis
Measures of Variation
Range and Interquartile Range (IQR)
Calculating Standard Deviation

1. Measures of Spread (Dispersion)

Measures of spread (also known as measures of dispersion) provide insights into how much variability is in a
data set. Understanding variability is critical in making informed decisions and assessing risks. Measures such
as range, variance, standard deviation, and the coefficient of variation are all measures of spread. These
measures play a vital role in analyzing data variability and providing insights into the stability and predictability
of business processes. In this section, we will explore each of these measures, understand their significance in
business contexts, provide examples of their calculations, and highlight how they are utilized (and sometimes
misused) by companies.

 TERMS TO KNOW

Measure of Spread

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 17
 A value that describes the variability in a data set.

Measure of Dispersion
A synonym for measure of spread.

1a. Range
The range is the simplest measure of dispersion, representing the difference between the largest and smallest
values in a data set. While it offers a quick glimpse into the spread of data, it can be misleading when outliers
are present.

 FORMULA TO KNOW

Range

 TERM TO KNOW

Range
A measure of spread equal to the difference between the largest and smallest value in a data set.

1b. Variance
Variance is another measure of the spread or dispersion of a set of data. Specifically, it quantifies how much the
values in a dataset differ from the mean of the dataset. A high variance indicates that the data points are spread
out over a wide range of values, while a low variance indicates that the data points are clustered closely around
the mean. In business analytics, variance quantifies the spread of data points in a data set from the mean,
providing insights into the stability and reliability of processes.

It is important to note that variance is measured in squared units of the original data. To revert to the original
scale of the data, you can take the square root of the variance, which produces the standard deviation (which
we will discuss in the next section). Converting the variance is often done because standard deviation is easier
to interpret since it is in the same units as the original data.

Mathematically, variance (denoted as 𝜎2 for a population and for a sample) is calculated as the average of the
squared differences from the mean. It is represented by the formulas:

 FORMULA TO KNOW

Population Variance

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 18
  FORMULA TO KNOW

Sample Variance

The key difference between the population and sample variance formulas is in the denominators. When
calculating the population variance, you divide by the total number of observations in your population, denoted
by N. Whereas with the sample variance, you divide by one less than the number of observations in the sample,
denoted by (n-1). This difference in the denominators is known as Bessel’s correction (Alexander, 2023). Sample
statistics tend to underestimate the actual parameters found in the population. Bessel’s correction remedies this
bias by adjusting for the fact that you are working with only a subset of the population data. The correction
ensures that the sample variance, which estimates the population variance, is more accurate.

EXAMPLE Customer Response Times

Consider a telecommunications company measuring the response times of its customer service
representatives (in minutes). The response times for a week are 5, 6, 7, 4, and 8 minutes. Calculate the
mean and variance for these data.

The response times are a sample, so we are using the sample variance formula, which uses (n-1) in the
denominator. The difference between each data value (response time) and the mean is squared. This
computation also squares the units of the data, minutes in this example.

The sample variance is 2.5 minutes2. The units are minutes-squared.

 KEY CONCEPT

Below, as we explore calculating variance in Excel, there are two variance formulas, VAR.P() and VAR.S().
The argument required for both VAR.P() and VAR.S() is the range of cells that contain the data you wish to

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 19
 take the variance of. The suffix .P and .S stands for population and sample, respectively. If you select
VAR.P(), the denominator will be the total number of observations (cells) you select. If you select VAR.S(), the
denominator will be one less than the total number of observations (cells) you select.
Now, let's see how to calculate variance using Excel.

 TRY IT

1. Open the customer_response_times.xlsx file in Excel.

The data provided are the response times of a telecommunications company explained in the previous
example.

Since sample data is provided, you will use the VAR.S() function.

2. In any empty cell, enter the following formula and select Enter.

=VAR.S(A2:A6)

The cells A2 through A6 contain all the values of customer response times.

The formula should have returned 2.5. The variance for the response times is 2.5 minutes2.

Now, interpret the sample mean and variance from the customer response time data provided in the
previous examples.

What is the mean? +

On average, it takes this company 6 minutes to respond.

What is the variance? +

The variance of 2.5 minutes2 provides a range that the company can use to determine if these
response times are acceptable. The units themselves are not easy to interpret, but we could compare
this variance to another time period.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 20
 What could go wrong? +

Variance is used, in this case of response time, to assess the consistency of the process. However, it
can be sensitive to outliers and may not always accurately represent the dispersion of data, especially
in skewed distributions. Also, this is a small sample size and may not be indicative of the company. For
a more accurate measure of variance, use a larger sample size if possible. As the sample size
increases, the sample statistics (like variance) become closer to the population parameters. This means
that with a larger sample, the sample variance will be a more precise estimate of the population
variance.

Another complication with variance is that it is measured in units of the data squared. In this example,
the variance is 2.5 minutes2. The problem is that the squared units make the interpretation less
intuitive. Who understands what minutes2 represents? For a more interpretable measure of variance,
you should use the standard deviation, which will be explained in the next section.

 TERM TO KNOW

Variance
Quantifies the spread of data points in a dataset from the mean.

1c. Standard Deviation

Standard deviation is a measure of the amount of variation or dispersion in a set of values. In statistics, it is
commonly used to quantify the spread or dispersion of a dataset and is found by taking the square root of the
variance.

The standard deviation tells you how spread out the values in a data set are around the mean. A high standard
deviation indicates that the data points are spread out over a broad range of values, while a low standard
deviation indicates that the data points are close to the mean.

 KEY CONCEPT

If this seems familiar, it’s not just your imagination. Variance and standard deviation are closely related
concepts. So why do we use both? Variance and standard deviation are both measures of spread, but the
standard deviation is more commonly used because it is in the same units as the original data and provides
a more interpretable measure of dispersion. Variance is often used in statistical calculations, while standard
deviation is more commonly used for the interpretation and communication of results across different
scales. As we progress through this course, you will examine how each is used in analytical calculations.

EXAMPLE In the previous example, you found the variance of the customer response times was 2.5
minutes2. The standard deviation is the square root of the variance. If you wanted to find the standard
deviation of the customer response time, you would take the square root of 2.5 as shown below.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 21
 The standard deviation for the sample data is 1.58 minutes.

The square root and the square of a number are inverses of each other. The result is original units of the
data, minutes in this case, since you are taking the square root of a square (minutes2).

 KEY CONCEPT

In Excel, in a similar fashion as the variance functions, there are two standard deviation formulas, STDEV.P()
and STDEV.S(). The argument required for both STEDEV.P() and STDEV.S() is the range of cells that contain
the data you wish to take the standard deviation of. The suffix .P and .S stands for population and sample,
respectively. If you select STDEV.P(), the denominator will be the total number of observations (cells) you
select. If you select STDEV.S(), the denominator will be one less than the total number of observations (cells)
you select.
Now, let's see how to calculate standard deviation using Excel.

 TRY IT

1. Open the customer_response_times.xlsx file in Excel.

The data provided are the response times of a telecommunications company explained in the previous
example.

Since sample data is provided, you will use the STDEV.S() function.

2. In any empty cell, enter the following formula and select Enter.

=STDEV.S(A2:A6)

The cells A2 through A6 contain all the values of customer response times.

The formula should have returned 1.58. The standard deviation for the response times is 1.58 minutes.

 THINK ABOUT IT

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 22
 Interpret the sample standard deviation from the customer response time data provided in the previous
examples.

A standard deviation of 1.58 minutes for customer response times indicates the typical variability or spread
in the response times. On average, customer response times deviate by 1.58 minutes from the mean
response time of 6 minutes.

 TERM TO KNOW

Standard Deviation
The square root of the variance is used to interpret variability with the same units as the original data,
making the measure interpretable.

1d. Coefficient of Variation

The coefficient of variation (cv) provides a standardized (relative) measure of dispersion, allowing for the
comparison of variability across datasets with different scales or units by expressing standard deviation as a
percentage of the mean. If the cv equals 1, this means the standard deviation equals the mean. Values less than
one indicate that the standard deviation is smaller than the mean. A higher cv (value greater than 1) indicates
greater relative variability, while a lower cv (value less than 1) suggests less variability relative to the mean.

The table below summarizes the interpretation of the cv.

cv Value Interpretation

1 standard deviation = mean

standard deviation < mean

<1
(less variability relative to the mean)

standard deviation > mean

>1
(greater variability relative to the mean)

 FORMULA TO KNOW

Coefficient of Variation (cv)

The cv is multiplied by 100%. The reason we multiply the coefficient of variation (cv) by 100% is to express it as a
percentage. The cv is originally calculated as the ratio of the standard deviation to the mean, but multiplying by
100% allows us to present it in a more intuitive and interpretable format.

The example provided will illustrate how you can use cv in a business data analytics application.

IN CONTEXT
Scenario: Employee Productivity Metrics

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 23
 Suppose you are working as a human resource (HR) analyst at a tech company, and you want to
compare the productivity levels of two teams: Software Development (SD) and Quality Assurance (QA).
You collect the following sample data from 75 employees:

1. Software Development Team:

mean of 800 lines of code written per day
standard deviation of 100 lines of code per day

2. Quality Assurance Team:

mean of 50 test cases executed per day
standard deviation of 10 test cases executed per day

Now, let’s calculate the cv for both teams.

1. CV for SD Team:

2. CV for QA Team:

Interpretation:
Remember, a higher percentage indicates greater relative variability.

The SD Team has a lower cv (12.5%), indicating less variability in writing lines of code productivity.
The QA Team has a higher cv (20%), suggesting more variability in test case execution.

The cv allows you to compare different units across two different sets of data. In this example, you are
comparing lines of code written to test cases executed.

The tech company can use this information to identify areas of improvement for the process of
performing QA test cases. The cv comparison among the two teams also allows for allocating
resources effectively based on variability and monitoring consistency over time.

 TERM TO KNOW

Coefficient of Variation (cv)

A statistical measure that indicates the size of a standard deviation in relation to its mean.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 24
 2. How Businesses Use Measures of Spread
In business analytics, spread, variance, standard deviation, and the coefficient of variation play crucial roles in
understanding and analyzing data. Here is how they are interpreted and applied:

1. Risk Assessment: Knowing measures of spread helps in assessing risk. A higher variance/standard
deviation in sales, for instance, might indicate greater uncertainty in future revenues, which can inform
decisions related to inventory management, staffing levels, or investment strategies.
2. Market Analysis: Dispersion analysis can also be used to analyze market trends and consumer behavior.
For instance, analyzing the standard deviation of sales of different products across regions or time periods
can provide insights into changing customer preferences or the effectiveness of marketing strategies.
3. Resource Allocation: Understanding the standard deviation and the variance in resource utilization can
help optimize resource allocation. For example, if there's a significant spread or variance in the utilization of
machinery or workforce across different shifts, adjustments can be made to improve efficiency and reduce
costs.
4. Process Optimization: Standard deviation is used in process optimization to identify areas for improvement.
By analyzing the standard deviation of key performance indicators (KPIs), such as production efficiency or
customer satisfaction scores, businesses can pinpoint processes that exhibit high variability and implement
measures to enhance consistency and efficiency.
5. Supply Chain Management: The coefficient of variation is used to assess the reliability and consistency of
supply chain performance metrics such as lead times, order fulfillment rates, or inventory turnover ratios. A
higher cv in these metrics may signal potential bottlenecks or inefficiencies in the supply chain, prompting
businesses to optimize inventory management, streamline logistics processes, or diversify supplier
networks.

 SUMMARY

In this lesson, you were introduced to four measures of spread: range, variance, standard deviation,
and coefficient of variation (cv) and their role in analyzing data variability. You learned how each
measure helps assess data consistency, with variance and standard deviation providing insights into
stability, and cv enabling comparisons across datasets. Practical applications included risk assessment,
market analysis, resource allocation, process optimization, and supply chain management. Examples
and calculations demonstrated how businesses use these measures to make data-driven decisions. By
understanding these concepts, you can better assess variability and improve decision-making in
business analytics.

Source: THIS TUTORIAL WAS AUTHORED BY SOPHIA LEARNING. PLEASE SEE OUR TERMS OF USE.

REFERENCES

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 25
 Alexander. (2023, March 14). Bessel’s correction: Why use N-1 for Variance/Standard Deviation? - Statistics How
to. Statistics How To. [Link]/bessels-correction/

 TERMS TO KNOW

Coefficient of Variation (cv)

A statistical measure that indicates the size of a standard deviation in relation to its mean.

Measure of Dispersion
A synonym for measure of spread.

Measure of Spread
A value that describes the variability in a data set.

Range
A measure of spread equal to the difference between the largest and smallest value in a data set.

Standard Deviation
The square root of the variance is used to interpret variability with the same units as the original data,
making the measure interpretable.

Variance
Quantifies the spread of data points in a dataset from the mean.

 FORMULAS TO KNOW

Coefficient of Variation (cv)

Population Variance

Range

Sample Variance

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 26
© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 27
 Correlation and Covariance
by Sophia

 WHAT'S COVERED

In this lesson, you will learn about two measures, covariance and correlation. These measures provide
information on the relationship between two variables. Specifically, this lesson will cover:
1. Covariance
1a. Computing and Interpreting Covariance
2. Correlation
2a. Computing and Interpreting Correlation
2b. Correlation in Practice
3. Correlation Does Not Imply Causation

1. Covariance
Covariance quantifies the degree to which two variables change together. Covariance measures the linear
direction of the relationship between two variables. When covariance is positive, the variables tend to move
together; when negative, they move in opposite directions. However, interpreting the magnitude of covariance
can be tricky.

 TERM TO KNOW

Covariance
Statistical measure that indicates how one variable varies in response to another variable.

1a. Computing and Interpreting Covariance

Let’s understand why the magnitude of covariance can be difficult to interpret by computing and interpreting a
covariance measure.

The sample covariance is calculated using the following equation:

 FORMULA TO KNOW

Sample Covariance

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 28
 In this equation, x and y are the two variables of interest. In general, x is known as the explanatory variable. You
can think of an explanatory variable as a characteristic or factor that may influence something else. The y is
known as the response variable. It is like the outcome that you want to understand or predict. The covariance
sums the product of the deviations of each variable’s values from its mean and divides the sum by the number
of observations minus 1

If you have a population of N items, the population covariance is calculated as follows:

 FORMULA TO KNOW

Population Covariance

This equation is like the sample covariance formula but uses parameters instead of sample estimates and
divides the sum of the products of deviations by N and not Recall that μ is the population mean.

 KEY CONCEPT

Guidelines for Covariance:

A positive covariance indicates that when x increases, y increases as well.
A negative covariance indicates an inverse relationship, that is, when x increases, y typically decreases.
A covariance close to zero indicates that there is no linear relationship among the variables.

Covariance only provides direction. Correlation does not quantify the strength of the linear relationship.
This concept will be illustrated with the next example.

EXAMPLE Employee Productivity and Training Hours

You want to analyze the relationship between employee productivity (measured in units produced per hour)
and the number of training hours attended by employees. We have data for a manufacturing company over
the past five months.

Month Productivity (units per hour) Training Hours

January 50 20

February 55 22

March 52 24

April 54 21

May 51 19

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 29
 Calculate the sample covariance between employee productivity and training hours. Let x = productivity
and y = training hours.

1. Calculate the mean for each of the variables.

The means have been rounded for ease of computational purposes.

2. Calculate the deviations from the mean for productivity and training hours.

Productivity

Training Hours

3. Calculate covariance.

For this example, the covariance is positive, indicating that higher productivity is associated with more

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 30
 training hours. It’s important to understand how covariance is calculated, but in practice, you perform these
calculations with software.

 KEY CONCEPT

In Excel, there are two covariance formulas, COVARIANCE.P() and COVARIANCE.S(). The argument required
for both COVARIANCE.P() and COVARIANCE.S() is the range of cells that contain the data you wish to take
the covariance of. The suffix .P and .S stands for population and sample, respectively. If you select
COVARIANCE.P(), the denominator will be the total number of observations (cells) you select. If you select
COVARIANCE.S(), the denominator will be one less than the total number of observations (cells) you select.
Let’s calculate a sample covariance measure for sample data using Excel, using employee_productivity.xslx.

 TRY IT

Open the employee_productivity.xlsx file in Excel.

The data provided are data from a manufacturing company related to employee productivity and the
number of training hours over the past five months.

Since sample data is provided, you will use the COVARIANCE.S() function. The argument is the range of the
data for the two variables, separated by a comma.

In any empty cell, enter the following formula and select ENTER.

=COVARIANCE.S(B2:B6,C2:C6)

Cells B2 through B6 contain all the values of employee productivity (units produced per hour), and C2
through C6 contain the values of the number of training hours.

The formula returns 1.65, which rounds to 1.7.

 WATCH

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 31
 Follow along with this video on calculating sample covariance.
Interpreting covariance can be challenging because the magnitude and scale of covariance are affected by the
units of the variables. A larger covariance does not necessarily imply a stronger linear relationship. For example,
the relationship between productivity and the amount of training should be the same whether we measure the
training time in hours or minutes. However, if we measure training time in minutes, the values of will be
much larger. These larger deviations will produce a larger covariance value when the relationship between the
variables has not changed.

The magnitude and units of measurement of the variables strongly affect the covariance value, making it
difficult to determine the strength of the linear relationship. It is important to remember that context matters
when talking about the “bigness” of a number. When comparing numbers, consider their relative scale. For
instance, $1,000 is substantial in the context of personal finances, but it might be insignificant for a large
corporation. Similarly, 1,000 grains of sand might be a tiny fraction of a beach, but if you are examining a handful
of sand, it is quite significant.

 TERMS TO KNOW

Explanatory Variable
A factor or input that you believe influences or explains variation in another variable.

Response Variable
Represents the outcome variable that you want to explain, predict, or understand.

2. Correlation
While covariance assesses the directional relationship between variables, it does not provide information about
the strength of the relationship. Correlation standardizes the covariance value to always fall between -1 and 1.
This standardization makes it easier to interpret because correlation does not depend on the units the variables
are measured. In contrast, covariance can range from negative to positive infinity, and its interpretation is
challenging due to sensitivity to the variable’s units of measurement.

Correlation provides the direction and strength of the linear relationship. A correlation coefficient of 1 indicates
a perfect positive linear relationship (as one variable is increasing, so is the other one), -1 indicates a perfect
negative linear relationship (as one variable is increasing, the other variable is decreasing), and 0 indicates no
linear relationship.

The figure below provides visuals for the relationship between an X and Y variable for various correlation
coefficient values. The first row illustrates a positive relationship between the variables. The graphs in the first
row are displayed in order of increasing correlation value. The closer the correlation is to 1, the more of a
positive linear relationship exists between the variables. With a correlation of 0.30, there is a slight positive
relationship between the variables. As the correlation rises to 0.70, the variables have a more moderate positive
relationship. Finally, when the correlation is equal to 1, there is a perfect positive linear relationship between the
variables.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 32
 The second row illustrates a negative relationship between the variables. In a similar fashion as illustrated in the
first row, as the correlation coefficient is approaching -1, a more negative linear relationship exists between the
variables.

Correlation is also sometimes referred to as the correlation coefficient and is represented by a lowercase “r.”

 TERMS TO KNOW

Correlation
Statistical measure (r) that indicates the strength of the linear relationship between two variables.

Correlation Coefficient
A synonym for correlation.

2a. Computing and Interpreting Correlation

There are two types of correlation: one computed from population data and one computed from sample data.

The sample correlation is given by:

where:
is the sample correlation coefficient
is the sample covariance between x and y
is the sample standard deviation for x

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 33
 is the sample standard deviation for y

If you have a population of N items, the population correlation is given by:

This formula is like the sample correlation coefficient but uses parameters instead of sample estimates.
Correlation is a dimensionless measure because it standardizes the relationship between two variables. In the
formula, when the covariance is divided by the product of the two variables’ standard deviations, this process
normalizes the values, making them dimensionless and unit independent. This normalization process ensures
that the correlation value will be constrained between -1 and 1.

The correlation coefficient, denoted by “r,” measures the strength and direction of the linear association between
two variables. The value of r is always between -1 and 1.

Let’s now calculate the sample correlation coefficient for employee productivity and training hours, where x =
productivity and y = training hours from the previous example.

EXAMPLE Recall that you have collected the following data from a manufacturing company related to
employee productivity and the number of training hours over the past five months.

Month Productivity (units per hour) Training Hours

January 50 20

February 55 22

March 52 24

April 54 21

May 51 19

Calculate the sample correlation coefficient, for employee productivity and training hours.

From the previous example, you found You need to find the standard deviations for the

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 34
 productivity and training hours variables, as follows.

Productivity:

You computed the sample mean for the productivity units as from the previous example. With this
value, you are now ready to calculate the standard deviation for the number of units produced per hour.

A standard deviation of 2.1 units per hour indicates the typical variability or spread for how many units the
employees produce per hour.

You computed the sample mean for the training hours as from the previous example. With this
value, you are now ready to calculate the standard deviation for the number of training hours.

A standard deviation of 1.9 hours indicates the typical variability or spread for the number of hours of
training.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 35
 The sample correlation is 0.43.

Since the correlation coefficient is greater than 0 and closer to 0.5 than it is to 0, this indicates a moderate
positive relationship between productivity (measured in units per hour) and training hours. There is a
discernible tendency for productivity and training hours to vary together. When training hours increase,
productivity tends to increase as well. In other words, more training hours are associated with higher
productivity.

Keep in mind that correlation does not imply causation. While the correlation coefficient provides insight
into the relationship, other factors may also influence productivity.

 HINT

Excel is a powerful tool for performing complex calculations, including correlation. While it’s essential to
understand the underlying concepts, let’s use Excel to handle the heavy lifting.

 TRY IT

Let’s calculate a correlation coefficient for a sample using Excel.

1. Open the employee_productivity.xlsx file in Excel.

The data provided are data from a manufacturing company related to employee productivity and the
number of training hours over the past five months.

In Excel, the CORREL() function computes the correlation between two variables. The argument is the range
of the data for the two variables, separated by a comma.

2. In any empty cell, enter the following formula and select ENTER.

=CORREL(B2:B6,C2:C6)

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 36
 Cells B2 through B6 contain all the values of employee productivity (units produced per hour), and C2
through C6 contain the values of the number of training hours.

The formula should return 0.41. The hand computations shown in the previous example produced a
correlation value of 0.43. The difference in the values is not meaningful and is due to rounding error in the
example.

 WATCH

Follow along with this video on calculating correlation.

 TERM TO KNOW

Normalize
Process that transforms data to a standard scale.

2b. Correlation in Practice

While there are some general guidelines for what are considered strong and weak correlation values, these
definitions vary across disciplines and contextual scenarios. There are medical applications that consider a
weak correlation to have practical meaning. For example, for rare diseases, correlations may be weak due to
limited data. However, any correlation could be significant because identifying a rare condition early can impact
patient outcomes.

Context matters in business data analytics situations. Consider the following example that illustrates how
context matters in a business data analytics scenario.

EXAMPLE Suppose you analyze the correlation between the frequency of customer interactions (e.g.,
website visits, emails opened) and their actual purchases. You find a weak positive correlation of 0.2.

While this correlation is not strong, it has practical business implications. This means that there’s a slight
tendency suggesting that when engagement increases, purchases also tend to increase, but not strongly.
While weak, this correlation still matters. You could use this weak correlation to take these practical actions.

1. Segmentation: You can find more engaged customer segments.

2. Retention efforts: Focus on retaining these engaged customers.
3. Retention strategies: Nurture engaged customers to boost loyalty.

In some industries (for example, e-commerce), even a small increase in customer purchases can
significantly impact revenue. So, understanding this weak correlation helps guide business decisions.
A hallmark of a good business analyst is to look at the big picture, consider the domain-specific application, and
provide the business with an actionable insight.

3. Correlation Does Not Imply Causation

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 37
 Correlation is a valuable tool to provide information related to the relationship between two variables. However,
correlation does not indicate a cause-and-effect link. Let’s explore an example to illustrate this point.

EXAMPLE Suppose you collect data for monthly ice cream sales and monthly shark attacks around the
United States each year. You find that these two variables are highly correlated—when ice cream sales
increase, shark attacks tend to increase as well.

However, does this mean that consuming ice cream causes shark attacks? Not quite!

The more likely explanation is that during warmer months, people both consume more ice cream and
spend more time in the ocean (where shark attacks occur).

So, while ice cream sales and shark attacks are correlated, one does not directly cause the other (Bobbitt,
2021).

 SUMMARY

In this lesson, you learned about two measures that quantify the relationship between two variables;
covariance and correlation. An example pointed out that covariance provides you with the direction of
how two variables vary together, but if you want to know the strength of the direction, correlation is
required.

Source: THIS TUTORIAL WAS AUTHORED BY SOPHIA LEARNING. PLEASE SEE OUR TERMS OF USE.

REFERENCES

1. Bobbitt, Z. (2021, November 5). Correlation does not imply causation: 5 Real-World examples. Statology.
[Link]/correlation-does-not-imply-causation-examples/

 TERMS TO KNOW

Correlation
Statistical measure (r) that indicates the strength of the linear relationship between two variables.

Correlation Coefficient
A synonym for correlation.

Covariance
Statistical measure that indicates how one variable varies in response to another variable.

Explanatory Variable
A factor or input that you believe influences or explains variation in another variable.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 38
 Normalize
Process that transforms data to a standard scale.

Response Variable
Represents the outcome variable that you want to explain, predict, or understand.

 FORMULAS TO KNOW

Population Covariance

Sample Covariance

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 39
 Regression Analysis
by Sophia

 WHAT'S COVERED

In this lesson, you will develop a simple linear regression model and evaluate the model. Specifically,
this lesson will cover:
1. Simple Linear Regression
1a. The Straight Line and Simple Linear Regression
1b. Fit the Simple Linear Regression Model to the Data
2. Sum of Squares

 BEFORE YOU START

This tutorial provides a gentle introduction to simple linear regression. In an upcoming unit, there will be
more details and examples related to regression. For now, concentrate on how simple linear regression is
applied in business, including grasping what simple linear regression entails, its business applications, and
how analyzing different components of the model aids in the interpretation of the results in the context of
the business problem.

1. Simple Linear Regression

Simple linear regression is a statistical model that allows you to summarize and study the relationship between
two variables. The term ‘simple’ in simple linear regression means there are only two variables involved. The
variables are:

x: the predictor, explanatory, or independent variable. This tutorial refers to x as a predictor. x may be a
quantitative or categorical variable.
y: the response, outcome, or dependent variable. This tutorial will refer to y as the response.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 40
 Simple linear regression is named from the fact that the model is a linear one, meaning it assumes the
relationship between x and y is linear.

Not all relationships will be linear. However, the regression ideas in this tutorial can be applied to whatever
function best describes the data (exponential, logarithmic, etc.)

 TERM TO KNOW

Simple Linear Regression

A statistical model used to estimate or predict the relationship between two variables.

1a. The Straight Line and Simple Linear Regression

The equation of a straight line is generally written as:

where:
y is the response variable
x is the explanatory variable
m is the slope
b is the y-intercept

In the context of simple linear regression, this equation is written as:

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 41
 where:
y is the response variable
x is the explanatory variable
is the slope
is the y-intercept

Simple linear regression for population data would also include an error, but the errors in a regression model
working with sample data are expected to be zero, so it is standard practice to drop the error term. Handling
errors in a simple linear regression model will be discussed in the next section.

 DID YOU KNOW

Greek letters are used to denote population parameters, and lowercase letters represent sample statistics.

1b. Fit the Simple Linear Regression Model to the Data

The first step in constructing a simple linear regression model is to fit the data to the model. This is done using a
mathematical procedure called ordinary least squares (OLS).

Working for a retail company, you may want to predict future sales based on how much is spent on advertising.
Using collected historical data on sales and advertising expenses, the ordinary least squares (OLS) method is
used to fit a simple linear regression model. By understanding and applying OLS, this analysis can provide
valuable insights into how advertising spend impacts sales, helping the company allocate its marketing budget
more effectively.

OLS finds estimates of the intercept and slope for the model so that it accurately represents the
observed data as shown in the figure below. OLS determines the slope and y-intercept of the regression line,
which minimizes the difference between predicted and actual values. The difference between the predicted
and the actual values is known as residuals Each residual is the distance between a data point and the
point predicted for that x value by the linear regression model (ŷ). OLS will find the linear line that is as close to
the data points as possible.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 42
 Source: 13.4 The Regression Equation - Introductory Business Statistics | OpenStax

Suppose you want to predict the tip amount from a lunch bill from a restaurant. You have collected the total
lunch bills and the amount of the tip from a local restaurant in your city. The table below shows the data.

 TRY IT

1. Open the [Link] file.

In this scenario, the response (y) is tip and the predictor variable (x) is total_bill.

2. To build the simple linear regression model in Excel, select Linear Regression in the XL Miner Analysis

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 43
 Toolpak. The Linear Regression dialog box opens.

3. Put your cursor in the Input Y Range box. Then use your mouse to select the tips data that are in rows B1
to B6 (including the tip column label value in row B1). Once you have selected all the data, the Input Y
Range value should now show rows B1 to B6 selected.

Input Y Range: $B$1:$B$6

4. Repeat this same process for the Input X Range box and select the data for the total_bill column. Once
you have selected all the data, the Input X Range should not show rows A1 to A6 selected.

Input X Range: $A$1:$A$6

5. The Output Range box specifies where you want the regression output to be displayed. Let’s output the
regression results in the same worksheet as our data. Click into the Output Range box and select any cell in
the worksheet.

6. Select the Residuals checkbox so the residuals will be in the regression results. Select OK.

7. You will see that you obtain three components of the output: SUMMARY OUTPUT, ANOVA, and

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 44
 RESIDUAL OUTPUT. Let’s parse through what we need to evaluate the regression model.

First, the estimated parameters of the model can be found under the ANOVA output in the Coefficients
column as shown below.

The table below shows the estimated values of the intercept and the slope

Coefficients

Intercept ( ) -0.526450834

Total_bill ( ) 0.162739408

8. Using these values, you can now specify the model as:

9. You can use the model to predict the amount of tip for a particular bill value. For example, if you wanted
to know what the predicted amount of the tip would be for a $20 bill, perform the following:

Great work! Now you have a formula for a line that best represents your tip and bill data. In the next section,
we will return to this example to assess the model fit. Specifically, we will be returning to the section of your
output called ANOVA.

 WATCH

Follow along with this video to predict the tip amount from a lunch bill from a restaurant using linear
regression. The predicted value of the tip that you observe in step 9 above and in the video will be different
in the decimal places due to the rounding of the hand computation shown in step 9 above.

 TERMS TO KNOW

Ordinary Least Squares (OLS)

A mathematical procedure that estimates the parameters in a linear model.

Residual
The difference between the predicted value of the response and the actual value of the response
variable. Also a synonym for error.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 45
 2. Sum of Squares
A business may want to predict the tip amount based on the total bill. The linear regression analysis could help
in understanding the relationship between the two variables more quantitatively, which could be useful for
restaurant management to predict future tips and plan accordingly. However, the sum of squares determines
the model fit, which helps to assess how well the regression line represents the data by quantifying the
variability the model does and does not account for.

To determine how well this line fits the data, you calculate three values known as the sum of squares:

Value What It Is Example

Sum of Squares Sum of differences between Measures the total variability in the tip amounts. It
Total (SST) each response and the mean shows how much the tip amounts vary from their
response average value.

Sum of Squares Sum of differences between Measures the variability in the tip amounts that the
Regression (SSR) each predicted response and model (the line) can explain. It shows how much of the
the mean response total variability is captured by the model.

Sum of Squares Sum of differences between Measures the variability in the tip amounts that the
Error (SSE) each response and the model cannot explain. It shows how much of the total
predicted response variability is left unexplained by the model.
Let’s look in more detail at each of these parts:

Sum of Squares Total (SST): In more general terms, SST measures the total amount of variability in the y
variable (the response) around the mean (ȳ
).

ȳ is the mean of the response variable, y. If you take all the values of y, add them up, and divide by the total,
this will produce ȳ. SST represents how much the data points deviate from the mean of the response variable,
ȳ, when the mean is a model to predict the response variable.

 HINT

Remember, the Σ sign means summation, that is to “add up” or “sum together” a series of numbers. The
SST formula above is using Σ to indicate the sum of the squared differences between each observed
response value ( ) and the mean of the observed response values (ȳ
). The index variable is i. would be
the first value of the response variable in the data, would be the second value of the response variable in
the data, and would be the last value (the nth value) of the response variable in the data.
When you compare data points to the mean (average) of the response value, you’re using the mean as a basic
model to predict the response. If you didn’t have any other model, the mean would be a decent guess because

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 46
 it represents the average outcome.

Steps:

Using the Mean as a Model: Think of the mean as a simple way to predict the response. For example, if you
want to predict tips, you could just use the average tip amount.
Calculating SST: The Total Sum of Squares (SST) measures how much the actual data points differ from this
mean. It does this by:
Finding the difference between each data point and the mean.
Squaring these differences (to avoid negative values canceling out positive ones).
Adding up all these squared differences.

Interpretation:

Large SST: If SST is large, it means the data points are far from the mean, indicating that the mean is not a
good predictor.
Small SST: If SST is small, it means the data points are close to the mean, indicating that the mean is a good
predictor.

In essence, SST helps you understand how well the mean (as a simple model) predicts the response. If the data
points are close to the mean, the mean is a good predictor. If they’re far away, the mean isn’t a good predictor.

Sums of Squares Regression (SSR): SSR represents the deviation of predicted values from the mean of the
response variable.

What It Does: SSR measures how well the model’s predictions compare to the mean (average) of the
response variable. For example, SSR measures how well the model’s predictions (predicted tips) compare
to the mean (average tip).
Large SSR: If SSR is large, it means the model’s predictions are far from the mean and closer to the actual
data points. This indicates the model is doing a good job of predicting the response variable.
Small SSR: If SSR is small, it means the model’s predictions are close to the mean and not much better than
just using the mean itself. This indicates the model is not doing a good job of predicting the response
variable.

Why It Matters:

Good Model: A large SSR means the model explains a lot of the variability in the data, making it a good
predictor.
Poor Model: A small SSR means the model doesn’t explain much of the variability, so you might as well just
use the mean to predict the response.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 47
 In essence, SSR helps you understand how much better your model is at predicting the response compared to
just using the average. If SSR is large, your model is good. If SSR is small, your model isn’t much better than
using the average.

Sums of Squares Error (Residuals) SSE: This measures how much the predicted values (tips) differ from the
actual observed values (tips). It represents the error in the model’s predictions.

Source: 13.4 The Regression Equation - Introductory Business Statistics | OpenStax

The relationship between the three sums of squares is The total amount of variability in the
response variable around the mean is the variability that the model accounts for (SSR) and the amount of
variability the model does not account for (SSE). So, SST shows the overall variability, SSR shows how much of
that variability your model explains, and SSE shows how much your model misses.

The coefficient of determination, often denoted as , is a measure that tells you how well your regression
model explains the variability of the response variable. Here’s a simple explanation:

Coefficient of Determination ( ):

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 48
 What It Is: is a number between 0 and 1 that indicates how well your model’s predictions match the
actual data.
How It’s Calculated: is calculated using the sums of squares we discussed earlier:

This means is the proportion of the total variability (SST) that is explained by the model (SSR).

Range of R 2 Guidelines

0 - 0.25 Model is a weak fit to the data

0.26 - 0.69 Model is a moderate fit to the data

0.70+ Model is a good fit to the data

Keep in mind that these are general rules, and you need to keep the context of the analysis in mind when
determining a threshold for what constitutes a good model fit to the data.

Now let’s look at how you could apply the sum of squares to help you assess a simple linear regression model
in Excel.

 TRY IT

Continuing with the [Link] file from the previous example or using [Link] which contains the linear
regression model, assess the model fit using the sum of squares.

Using the calculations from above, we specified the model as:

The sum of squares values are in the ANOVA output as well. The table below shows the sum of squares
values. The column labeled SS is the sum of squares.

ANOVA

df SS MS F Significance F

Regression 1 3.594103 3.594103 5.014615 0.111030188

Residual 2 2.150177 0.716726

Total 3 5.74428

The SST value is 5.74, SSR is 3.59, and SSE is 2.15. Notice that SSR and SSE add up to SST.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 49
 To interpret the sum of square values, it will be helpful to calculate the average of y (tip amount). The mean
of y can be found with the AVERAGE() function in Excel.

=AVERAGE(B2:B6)
=2.618 or 2.62

The SST value of $5.74 quantifies the amount of variability there is in the tip amounts without considering
any predictors. $5.74 is how much the tips deviate from the average tip amount of $2.62.

For both SSR and SSE, you must have the predicted values of the tips In the regression output under
the RESIDUAL OUTPUT, the predicted values are provided. For each data point, you obtain the predicted
value (Predicted tip column) and the residuals (Residuals column).

You can verify the first predicted value by substituting the corresponding x value into the model for each
observation.

For the first data point, the bill_amount (x) value is $16.99. So, to obtain the predicted value of the tip for this
observation, substitute the $16.99 value for x in the model.

In Excel, the predicted value of the tip is $2.24. The hand computations are different due to rounding the
Excel results to 2 decimal places. The predicted tip for the first lunch bill is $2.19 (rounded).

The SSR value of $3.58 quantifies the amount of variability that the regression model accounts for when
you compare the predicted values to the mean amount of tips.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 50
 And lastly, SSE provides you the sum of the squared residuals. In the RESIDUAL OUTPUT, the column
named Residuals contains the residual for each data point. You can verify the first residual by confirming:

The difference between the first residual you observe in Excel (-1.23) and the hand-computed residual is a
rounding error from using two decimal places. The first residual value of $-1.18 means that the model
underestimated the tip amount by $1.18.

SSE is the sum of the squared residuals, and its value is $2.15 from the ANOVA portion of the Excel output.
$2.15 represents the amount of variability in the tip amounts that the regression model cannot quantify.

To determine what proportion of improvement beyond using the mean the regression model provides, you
can calculate

63% of the variability in the amount of tips can be accounted for (explained) by the simple linear regression
model. The value is in the SUMMARY OUTPUT portion of the Excel output.

 WATCH

Follow along with this video to assess the model fit using the Sum of Squares for our linear regression
model predicting the tip amount from a lunch bill from a restaurant.

 DID YOU KNOW

The coefficient of determination, is the square of the correlation coefficient, That is,

 TERMS TO KNOW

Sums of Squares
An umbrella term to denote the three key sum of squares measures used to evaluate how well the
regression model fits the data.

Sums of Squares Total (SST)

The total variability of the response variable around the mean.

Sums of Squares Regression (SSR)

Quantifies the variability explained by the regression model.

Sums of Squares Error (Residual) (SSE)

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 51
 Quantifies the unexplained variability between the predicted values and the actual value of the
response variable.

Coefficient of Determination
Measures the proportion of variability in the response variable that can be explained using the predictor
variable.

 SUMMARY

In this lesson, you learned how to build a simple linear regression model. The relationship between a
straight line and simple linear regression was explored. You fit a simple linear regression model to a
data set and evaluated the model using the sum of squares measures.

Source: THIS TUTORIAL WAS AUTHORED BY SOPHIA LEARNING. PLEASE SEE OUR TERMS OF USE.

 TERMS TO KNOW

Coefficient of Determination
Measures the proportion of variability in the response variable that can be explained using the predictor
variable.

Ordinary Least Squares (OLS)

A mathematical procedure that estimates the parameters in a linear model.

Residual
The difference between the predicted value of the response and the actual value of the response
variable. Also a synonym for error.

Simple Linear Regression

A statistical model used to estimate or predict the relationship between two variables.

Sums of Squares
An umbrella term to denote the three key sum of squares measures used to evaluate how well the
regression model fits the data.

Sums of Squares Error (Residual) (SSE)

Quantifies the unexplained variability between the predicted values and the actual value of the response
variable.

Sums of Squares Regression (SSR)

Quantifies the variability explained by the regression model.

Sums of Squares Total (SST)

The total variability of the response variable around the mean.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 52
 Telling Your Story
by Sophia

 WHAT'S COVERED

In this lesson, you will learn about the importance of data visualization in business data analytics.
Specifically, this lesson will cover:
1. Introduction to Data Visualization
1a. Data Visualization versus Data Exploration
2. Importance of Data Visualization in Business Data Analytics
2a. Applications of Data Visualization
2b. Data Visualizations
3. Basic Data Visualization Design Principles
3a. Preattentive Attributes
3b. Data-to-Ink Ratio

1. Introduction to Data Visualization

Data visualization allows numbers and data to come to life through the power of representing data in a visual
format such as tables, graphs, and maps. Imagine being able to tell a compelling story about your business
insights, not just with reports but with vivid, engaging visuals. Data visualizations have the unique ability to
condense large volumes of data into a single, easily interpretable image. They allow you to quickly identify
patterns, trends, and anomalies that might be hidden in raw data. Data visualizations condense vast amounts of
information into a single image, allowing you to immediately grasp complex concepts related to the data.

Below are some reasons that data visualization is crucial in business data analytics.

Simplifies Complex Data: Data visualization transforms large and complex datasets into visual formats like
charts, graphs, and maps, making it easier to understand and interpret.
Identifies Trends and Patterns: Visualizations help in quickly spotting trends, patterns, and correlations that
might not be apparent in raw data.
Enhances Decision-Making: By presenting data visually, decision-makers can grasp insights swiftly, leading
to faster and more informed decisions.
Improves Communication: Visual data is easier to share and explain to stakeholders, ensuring everyone is
on the same page.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 53
 Saves Time: Visualizations allow for quick data analysis, reducing the time needed to interpret and act on
data.
Engages and Persuades: Well-designed visualizations can be more engaging and persuasive, helping to
convey the significance of data-driven insights effectively.

In essence, visualizations are not just tools for analysis; they are powerful storytelling devices that transform
data into insights, enabling you to make informed decisions and drive business success.

 TERM TO KNOW

Data Visualization
The graphical representation of data using visual elements such as graphs and maps.

1a. Data Visualization versus Data Exploration

It is important to distinguish between data visualization and data exploration because they serve different
purposes. Understanding the distinction will help ensure that the right approach and tools are used for each.
Data visualization and data exploration complement each other in the data analysis process, but they have
different objectives.

Data visualization aims to present a clear and concise overview of data patterns, relationships, and insights
using a small number of well-designed graphs meant for communicating the insights of the data to a non-
technical audience. Data exploration involves digging deeper into specific aspects of the data. During the data
exploration phase, there are many graphs made for the analyst's use. Little to no thought in design
considerations are given to the graphs constructed during the data exploration phase because these graphs
are for the analyst to deeply understand the data.

Data visualizations are prepared for sharing with a wide audience. The individuals you are sharing data
visualizations with are usually non-analytical but are on the frontlines of the business when it comes to making
decisions using the data. For example, you are going to present some findings at a monthly manager’s meeting,
and you will be using one or two visualizations to make your point. These one or two visualizations will require a
lot of care and time in their preparation. You will want to incorporate data visualization design concepts (which
you will learn about later) into the visualizations that will allow the managers to more easily comprehend the
data shown in the visualization. The one or two visualizations that you use for presentation purposes should
effectively convey the insight you have derived from the data.

Data exploration is the initial step of an analytical project. Data exploration includes sifting through, cleaning,
and organizing the data in preparation for analysis, and gaining insights without any preconceived ideas or
notions. The analyst takes a deep dive into the data and searches for insights that will be meaningful to the
business. There are many visuals constructed during the data exploration process. These graphs are messy,
and little to no care goes into their construction in terms of design principles.

The table below summarizes the difference between data visualization and data exploration.

Concept Analogy Purpose

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 54
 Data visualization Single picture that speaks volumes. One visual A visual “aha!” moment.
representation that captures complex data
succinctly.

Data exploration Detective with a magnifying glass, combing over Assists the analyst in understanding
every clue. Multiple visuals are created that allow the nuances, patterns, and hidden
the analyst to delve deep into the data. gems in the data.
For this tutorial and the others in this Challenge, the focus is going to be on data visualization and not data
exploration. The focus is creating visuals that will be communicated to a wide and mostly non-analytical
audience. The emphasis will be on incorporating design principles into both tables and graphs so that you can
construct a well-crafted graph or table that will tell a compelling story.

 TERM TO KNOW

Data Exploration
The initial step in data analysis is to gain a better understanding of the data and prepare the data for
further examination.

2. Importance of Data Visualization in Business

Data Analytics
A data visualization is one of the few tools that an analyst has. If constructed properly, almost any audience can
comprehend the message and meaning of the data in the visualization. In a previous lesson, you learned how
to apply common formatting to numbers, text, and dates. In this lesson, we will focus on formatting from a data
visualization perspective. Both lessons are essential for ensuring that the data is presented in an
understandable manner.

Data visualization is crucial in business for three main reasons.

1. Simplification: Data visualization simplifies complex data sets, making them easier to understand and
interpret.
2. Insight Generation: It helps in identifying patterns, trends, and correlations that might go unnoticed in table-
based data or require a much more sophisticated analysis to detect.
3. Decision Making: Visuals support decision-making processes by clearly presenting a picture of analytical
results.

Let’s look at an example where data visualization reveals insights that might require more advanced analytical
skills to detect.

EXAMPLE Imagine a data set related to customer behavior in an e-commerce platform. An e-commerce
platform processes millions of transactions daily. The company wants to detect fraudulent transactions early
to prevent financial losses.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 55
 Fraudulent transactions are often subtle and hard to detect using traditional analytical and statistical
methods. You need to identify patterns that distinguish fraudulent transactions from legitimate ones.

You construct a scatter plot of the transaction amount and the time of day for the transaction as shown in
the figure below. Legitimate transactions follow a consistent pattern throughout the day. Fraudulent
transactions might deviate from this pattern. For example, fraudulent transactions might consist of high
amounts during odd hours. Using the scatter plot below, you observe that there are four transactions that
are higher than usual, and they occur in the late evening hours.

2a. Applications of Data Visualization

Data visualization plays a vital role in various aspects of business data analytics. Below are several applications
of how data visualizations are used in business data analytics.

Sales Trend Analysis: Visualization tools can help businesses track their sales trends over time. This can
aid in identifying peak sales periods and understanding sales patterns.
Customer Segmentation: Data visualization can help in segmenting customers based on various variables
like age, location, buying behavior, and so on. This can assist in targeted marketing and personalized
customer engagement.
Risk Analysis: Businesses can use data visualization to identify potential risks and anomalies in
transactional data in real-time, enabling them to take proactive measures.
Performance Metrics: Visualization aids in tracking key performance indicators (KPIs) in an easily digestible
format, helping businesses monitor their performance and achieve their goals.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 56
 2b. Data Visualizations
There are various data visualizations used in business data analytics. The table below provides some of the
most widely used graphs and a description of when to use them. You will learn more about these specific
graphs and their appropriate use in an upcoming tutorial.

Graph When to Use

Bar chart Comparing quantities for different categories

Pie chart Expressing a part-to-whole relationship and having a small number of distinct
categories (2–5) to show

Line graph Showing trends over time

Scatter plot Displaying relationships between two quantitative variables

Heatmap Appropriate for using a color gradient to display the values of a quantitative
variable

3. Basic Data Visualization Design Principles

Remember, data visualization is one graph for many. So when you are preparing a data visualization for
presentation purposes for an audience, there are design considerations that should be taken into account. Data
visualization design is a whole discipline. There is much more to data visualization design than what you will
learn about in this Tutorial. However, as you move through this Challenge, when appropriate, data visualization
design concepts will be applied and discussed.

Two fundamental concepts in data visualization design are preattentive attributes and the data-to-ink ratio. Let’s
explore both.

3a. Preattentive Attributes

Preattentive attributes are visual properties that our brains process automatically and rapidly, even before
conscious attention. To demonstrate how powerful preattentive attributes are, consider the following task:

You are given a block of numerical digits and asked to count the number of times the digit 8 appears.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 57
 The answer is the number 8 appears nine times. To complete this simple task without a lot of digits was
somewhat time consuming. You had to look through each digit. Now, suppose we apply a bold format to the 8
in the block of digits.

Now, the task is easier. Your eye goes right to the 8 digit, and you can easily count the 8s without having to look
through each digit.

We want to use this same type of cue in the graphs we present to audiences, particularly non-analytical
audiences. You do not want to make your audience consciously analyze every detail in the graph. Rather, you
want to reduce the cognitive effort of your audience and have them focus on the main message the graph is
conveying. Preattentive attributes make visualizations more intuitive, helping your audience quickly grasp
essential information.

There are several preattentive attributes that can be used in graphs, such as:

Color: Notice differences in color intensity instantly.

Size: Larger or smaller elements stand out.
Position: Spatial arrangement conveys order or hierarchy.
Length: Longer bars represent larger categories.
Orientation: Angles or slopes catch our eye.
Shape: Distinctive shapes attract attention. Bigger shapes represent a greater quantity.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 58
 The example below illustrates how you can use several of these preattentive attributes to allow an audience to
focus on what is important in a graph.

EXAMPLE Color as a Preattentive Attribute

A retail company tracks monthly sales for various product categories (electronics, clothing, home goods).
The goal is to identify trends and focus on areas that need improvement. The heatmap below displays the
sales for three product categories for the first quarter of 2024. A blue color gradient represents the sales
for the month, where the darker shades of blue indicate higher sales.

Your eye is naturally drawn to the square with more intense color, making it easy to detect that in February,
the electronics category was the best performer. Whereas home goods are the product that needs some
improvement.
Let’s look at another example in the healthcare domain where you can use position as a preattentive attribute.

EXAMPLE Imagine you work for a hospital, and you want to analyze patient wait times in the
emergency room. You have a dataset containing patient arrival times and corresponding wait times (in
minutes) for the emergency department. Each row represents a patient visit, with columns for “Arrival Time”
and “Wait Time.” You calculate the mean wait times for all patients that arrive in each hour and construct the
scatter plot in the figure below. Each point in the scatter plot represents the average wait time for all
patients who arrived in the corresponding hour.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 59
 By leveraging the position of the points in the scatter plot, your audience can quickly compare average wait
times across different hours. It is easy for your audience to identify that patients who arrive at 11 p.m. must
wait the longest, on average. Your eye is immediately drawn to the point in the top right because the point
is positioned far away from the others.

 TERMS TO KNOW

Preattentive Attribute
A visual property of an element in a design or data visualization that your eyes automatically perceive
before you consciously focus on the details.

Heatmap
A table that represents the magnitude of individual values within a data set using color.

3b. Data-to-Ink Ratio

The data-to-ink ratio is a term attributed by Edward Tufte, a prominent expert in data visualization and
information design. Tufte emphasizes the importance of maximizing the representation of relevant data while
minimizing unnecessary ink or visual clutter. One of Tufte’s mantras is “above all else show the data.” This
mantra means the data is what should be the focus of the graph, not anything else that is aesthetically pleasing
or unrelated to the data.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 60
 The data-to-ink ratio measures how much ink (or pixels) is used to represent the actual data compared to the
total ink used for the entire visualization.

Here’s the formula for calculating the data-to-ink ratio:

where:
Data-ink: The ink (or pixels) used to represent the data points, labels, and other essential information.
Total-ink: The overall ink (or pixels) used in the entire visualization, including gridlines, borders, legends,
and other non-data elements.

A higher data-to-ink ratio indicates a more efficient visualization because it minimizes unnecessary ink while
maximizing the representation of relevant data. Data visualization designers strive to create visualizations with a
high data-to-ink ratio to enhance clarity and reduce visual clutter.
The two graphs below illustrate two bar charts, one with a low data-to-ink ratio and the other with a high data-
to-ink ratio.

 THINK ABOUT IT

Before you continue, take a few minutes and consider if you were presenting this bar chart, which one
would you select? Below are a few questions you can ask yourself.

Which chart is easier to read and understand at a glance?

Are there any elements in either chart that seem unnecessary or distracting?
Which chart more effectively communicates the data?

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 61
 Which chart uses fewer visual elements to convey the same information?
Which chart is more visually appealing to you? Why?
Which chart would be more suitable for a professional presentation? Why?

In the bar chart with a low data-to-ink ratio, too much ink (or pixels) is used for non-essential elements. For
example, the bars have thick outlines or borders, consuming unnecessary ink. Extraneous embellishments like
gridlines and the border for the axes are present. The impact of a low data-to-ink ratio graph is a cluttered
appearance, reduced focus on the actual data, and less efficient communication of the information that is being
displayed in the graph.

In the bar chart with a high data-to-ink ratio, ink is primarily used for conveying essential data. Desirable
features in this bar chart are bars that have no outlines, emphasizing the height of the bars (data points), and
minimal tick marks on the axes. Minimalistic styling without distracting elements allows a visual to be crisp and
effectively communicate the data, which leads to enhanced readability and understanding for your audience.

 TERM TO KNOW

Data-to-Ink Ratio
Ratio of ink (pixels) in a visual representation that shows the data to the graph's total elements.

 SUMMARY

In this lesson, you explored the differences between data visualization and data exploration. An
example was provided that illustrated the importance of data visualization in a business data analytics
context. Finally, you were introduced to two data visualization design concepts that you can apply in
real-world settings that will make your data visualizations more understandable to a non-analytical
audience.

Source: THIS TUTORIAL WAS AUTHORED BY SOPHIA LEARNING. PLEASE SEE OUR TERMS OF USE.

 TERMS TO KNOW

Data Exploration
The initial step in data analysis is to gain a better understanding of the data and prepare the data for
further examination.

Data Visualization
The graphical representation of data using visual elements such as graphs and maps.

Data-to-Ink Ratio
Ratio of ink (pixels) in a visual representation that shows the data to the graph's total elements.

Heatmap

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 62
 A table that represents the magnitude of individual values within a data set using color.

Preattentive Attribute
A visual property of an element in a design or data visualization that your eyes automatically perceive
before you consciously focus on the details.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 63
 Table Design Principles
by Sophia

 WHAT'S COVERED

In this lesson, you will learn about design concepts for when you need to display and present data
using tables. Specifically, this lesson will cover:
1. Data Visualizations versus Tables
2. Table Design Properties
2a. Heatmap Tables
2b. Use of Color in Table Design

1. Data Visualizations versus Tables

There are several scenarios where using a table is more appropriate than a visual representation. A table works
best when:

Individual values must be looked up

Values must be expressed precisely (Few, 2012; Knaflic, 2015).

Let’s look at an example of when you need to use a table to look up individual values.

EXAMPLE Imagine a sales manager at a retail company. The manager wants to analyze the monthly
sales performance of team members. You collect data on sales for each team member, including their
names, sales amounts, and dates. This data is stored in a spreadsheet. You need to update this data
regularly as new sales occur. A table is ideal for this purpose because it allows you to input precise
numerical values (sales amounts) and other relevant information (such as dates and team member names).

Suppose the manager needs to know how much Jasmine sold in February. The table below can be
provided to the manager, and the manager can quickly locate Jasmine’s row, look up the value in the
February column, and find her exact sales amount.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 64
 Tables are perfect for presenting precise numerical values. The manager can see the exact sales figures
without any approximation.

A bar graph could be used to visualize these data too, but that is if you were interested in focusing on a
comparison or trend of monthly sales over the months for the team members, instead of precise
comparisons.
Tables are perfect for data requiring meticulous attention and precise values. If you want to visualize overall
trends, compare sales across team members, or identify patterns (for example, which months had the highest
sales), charts (such as line graphs or bar charts) would be more suitable.

 TERM TO KNOW

Table
A structured arrangement of data organized into rows and columns.

2. Table Design Properties

Let’s dive into the principles of effective table design for presenting raw data in a clear and usable format.
Listed below are some best practices for table design1,2.

Column Headers Stand Out: Make column headers distinct from the data. Use bold text or a different font
size to emphasize them. Clear headers help users quickly understand what each column represents.
Light Shading for Separation: Apply light shading to separate rows or columns. This subtle visual cue helps
readers distinguish between different data points.
Minimal Borders: Borders should be used to improve the legibility of the table. Think about pushing the
borders to the background or eliminating them altogether. The data should be what stands out, not the
borders.
Text Alignment: Left-align text (labels, descriptions) and right-align numbers. This alignment enhances
readability by creating a consistent visual flow.
Currency Symbols: Use currency symbols sparingly. Too many symbols can clutter the data.
Precision: Do not exceed the required level of precision. Too many decimal places can clutter the table and
prevent comparisons.
Avoid Repetition: Place labels only in the first row to prevent redundancy. Repeating labels for each row
can clutter the table.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 65
 Group and Sort Data: Organize data logically. Group related rows together and sort them based on
meaningful patterns (e.g., chronological order, alphabetical order).
Use Color Sparingly: Use color judiciously. Highlight key items or outliers with subtle color accents.

 TRY IT

The table below shows the breakdown of customers based on their credit rating categories at a financial
institution.

You have been told the information must be communicated using a table. Are there any changes that you
would make to the way the data is presented or the overall way the table is designed?

Below are the answers to the question above:

1. Remove unnecessary elements that detract from the data. The heavy borders and the black shading
behind the column names need to be removed. This shading does not assist the user in understanding the
data in the table. This table design property has been implemented in the figure below.

2. Make the column labels bold so that they stand out from the rest of the data. This table design property
has been implemented in the figure below.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 66
 3. The text in the Credit Category column needs to be left-aligned, and all the numerical values need to be
right-aligned. Center-aligned text can lead to hanging text and jagged edges, making the data look messy.
When text spans multiple lines, this issue becomes more pronounced. To maintain a cleaner appearance,
left-align your text. This table design property has been implemented in the figure below.

4. There are too many formatting symbols used in the table. In the columns that use the % and $ signs, the
column labels indicate the symbol. There is no need to repeat the symbols on every row in the table. This
overuse of symbols makes the data in the table cluttered. This table design property has been implemented
in the figure below.

5. Examine the digits of significance for the % Accounts column. The two decimal places seem like a lot
given the context of the data. There is no single right answer to how many decimal places should be used.
A good rule of thumb is to ask yourself, “Do the decimal places provide a meaningful difference when
interpreting the values?” For example, in this scenario, is there a meaningful difference between 8.7% and
8.72%? If not, you can drop a digit by rounding. In this context, given the differences between the numbers,

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 67
 you can round to whole numbers.

For the Revenue ($ mil) column, it might be important to know that the revenue for the Very Good category
of customers is $5,680,000 versus $5,600,000. By rounding to one fewer decimal place, $80,000 is not
being represented. This might be significant to the financial institution. So, in this context, it might be
practical to round to two decimal places for this column. This table design property has been implemented
in the figure below.

6. The Credit Category represents the customers as classified by their credit rating categories. The
Excellent category should be listed before the Very Good category to maintain a logical ordering or
grouping of the categories. The data in these rows should be switched. The table below implements this
design change and provides the final table with all table design elements incorporated.

2a. Heatmap Tables

A heatmap is a special type of table. A heatmap enhances a table by visually representing patterns and
relationships within the data. Unlike a plain table, a heatmap uses color intensity to highlight variations in values
across rows and columns. It allows you to quickly identify trends, clusters, and outliers.

Color is a preattentive attribute and needs to be used judiciously in a table. When color is used intentionally and
sparingly, it can be used as a technique to draw the audience’s attention to where you want them to focus. Let’s
look at an example where a heatmap can be used to direct attention to a certain aspect of a table.

EXAMPLE The heatmap below shows the previous table for information related to the credit ratings of
customers at a financial institution converted to a heatmap.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 68
 Suppose you want the audience to make a comparison between the two columns % Accounts and %
Revenue. You can apply heatmapping using a green color gradient for just these two columns. The lighter
shade of green indicates relatively lower values, and the darker green indicates higher values. The
heatmap enables the audience to compare two columns by visually highlighting the relative intensity of
values across both columns.

Color allows you to direct your audience to where you want them to pay attention. When you look at the
table, ask yourself the question, “Where are my eyes drawn?” For example, when you look at the heatmap,
your eye should be drawn to the number 47% in the % Accounts column, and it should be quite easy to
detect that the largest percentage of accounts are made up of customers with Fair credit scores.

 TRY IT

Create the heatmap using the customer credit ratings.

1. Open credit_ratings_table.xlsx.

2. Select the two columns % Accounts and % Revenue. You can use the CTRL key to select multiple
columns.

3. From the Home menu, select the drop-down arrow to the right of the Conditional Formatting tool -->
Color Scales --> Blue-White-Red Color Scale.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 69
 4. The table will update with the color scale, showing that the darker blue indicates a higher value and the
darker the red indicates a lower value.

In the next section, you will learn that there are some colors, like red, that you should avoid due to design
considerations.

5. Check your work by comparing your heatmap to the one in this Try It exercise.

 DID YOU KNOW

A heatmap can indeed function as both a table and a graph.

Graphical Representation: A heatmap visually represents data where individual values are represented
by colors.
Tabular Structure: The underlying structure of a heatmap can be a table, where rows and columns
correspond to different categories or variables. Each cell in this table is color-coded based on the value
it represents.

2b. Use of Color in Table Design

The choice of color in a heatmap plays a crucial role in shaping the audience’s perception of the data. Several
color design principles must be considered when selecting a color for a heatmap. The table below provides
some best practices for using color.

Color Design Concept Best Practice

Contrast and Intensity Dark colors (dark blue) highlight significant/high values, and lighter colors (light
blue) indicate lower values.

Emotional Associations Color invokes emotions. Red signals urgency/problem. Whereas blue conveys
calmness/coolness.

Cultural Context Color meanings can vary across cultures. What’s perceived as positive or negative
might differ based on cultural backgrounds.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 70
 Accessibility Consider using color-blind-friendly colors like orange and blue. Stay away from
colors that color-blind individuals have trouble with, like red, green, and yellow.

 SUMMARY

In this lesson, you learned key principles for designing clear and effective tables, emphasizing the
importance of formatting choices such as bold headers, minimal borders, aligned text, and appropriate
precision. It also introduced heatmaps as an enhanced table format that uses color to highlight
patterns, trends, and key comparisons, making it easier for the audience to interpret data. Heatmaps
serve as both tabular and graphical representations, offering a balance between numerical precision
and visual insight. By applying these table design and heatmap principles, you can present data in a
way that improves clarity, usability, and audience focus.

Source: THIS TUTORIAL WAS AUTHORED BY SOPHIA LEARNING. PLEASE SEE OUR TERMS OF USE.

REFERENCES

Few, S. (2012). Show Me the Numbers: Designing Tables and Graphs to Enlighten (2nd ed.). Burlingame, CA:
Analytics Press.

Nussbaumer Knaflic, C. (2015). Storytelling with data (C. N. Knaflic, Ed.). John Wiley & Sons.

 TERMS TO KNOW

Table
A structured arrangement of data organized into rows and columns.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 71
 Crosstabulation
by Sophia

 WHAT'S COVERED

In this lesson, you will learn how to analyze data in a crosstabulation and perform a chi-square test of
association for variables that can be represented in a crosstabulation. Specifically, this lesson will cover:
1. Crosstabulation
1a. Interpreting a Crosstabulation
2. Chi-Square Test of Association

 BEFORE YOU START

This lesson assumes familiarity with several key introductory statistics concepts. Reviewing the following
content from Sophia’s Introduction to Statistics is advised:
Hypothesis Testing
ANOVA and Chi-Square Tests focusing on Chi-Square Test of Association and Independence

1. Crosstabulation
Crosstabulation, also known as a contingency table, is a tabular form used to analyze the relationship between
two or more categorical variables. The steps below outline how the procedure works.

1. Tabulate Frequencies

Create a two-dimensional table that records the number (frequency) of respondents with specific
characteristics.

Each cell in the table represents a combination of categories from the variables being analyzed.

2. Interpreting Relationships

By examining the intersections of categories, you can understand how the variables relate to each other.

For example, you might compare the city of residence with the type of government services used to determine
if these two variables are independent or related.

The relationship is determined using a statistical test known as the chi-square test of association.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 72
 3. Insights and Patterns

Crosstabulation helps reveal patterns, dependencies, and associations that might not be apparent when
looking at the total frequencies of categorical variables. Examining the frequencies of categorical variable
categories together allows for deeper insights.

 TERMS TO KNOW

Crosstabulation
A table that displays the frequencies or counts of observations that fall into different categories of
categorical variables.

Contingency Table
A synonym for crosstabulation.

1a. Interpreting a Crosstabulation

Before any formal analysis is performed on a crosstabulation, it is important to understand the components that
make up the crosstabulation and interpret the values in the crosstabulation.

Let’s look at an example to demonstrate the interpretation and understanding of a crosstabulation.

EXAMPLE Suppose you are assisting an e-commerce manager who is analyzing customer purchasing
behaviors. You want to segment your customers based on their preferences and payment methods. You
construct the contingency table below.

The rows are the categories of products the company sells. The columns are the payment methods used by
the customers. The intersection of the cells contains a frequency count representing a unique combination
of the two categorical variables being analyzed. Using the contingency table for this scenario, the company
has 250 customers who purchased electronics and paid with a credit card, 50 customers who purchased
clothing and paid with PayPal, and so on.

The contingency table also presents row and column totals. The row totals are listed in the Totals column.
The row total represents the sum of frequencies (counts) for each category within a specific row. For
example, 460 electronic products have been ordered. A column total represents the sum of frequencies
(counts) for each category within a specific column. For example, 170 customers have paid using PayPal.
 TRY IT

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 73
 Using the contingency table provided in the previous example, answer the following questions:

How many customers purchased home goods and paid with PayPal? +

40

How many customers purchased electronics and paid with Bitcoin? +

130

How many customers purchased home goods products? +

275

How many customers paid using Bitcoin? +

365

Just knowing how to interpret the values in the contingency table and what the frequencies represent can
provide you with insight into the data. For example, by examining the intersections in the table, you can uncover
insights such as:

Which product categories are most popular for each payment method? Payment by credit card is the most
popular for electronics.
Are there any unexpected patterns (for example, high credit card usage for specific products)?
Do certain payment methods dominate specific product categories? Credit card payment dominates all the
categories.

Armed with your insights from the analysis, the e-commerce manager can tailor marketing strategies such as:

Offer discounts when the customer uses credit card payments.

Optimize checkout experiences based on preferred payment methods.
Customize product recommendations for different segments.

For further analysis, you can also calculate percentages from a contingency table. Let’s return to the previous
example to determine how you can calculate certain percentages.

EXAMPLE Returning to the previous example where you are assisting an e-commerce manager who is
analyzing customer purchasing behaviors, you have the following contingency table.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 74
 There are three types of percentages you can calculate from a contingency table, and they are listed below.

Overall percentages: An overall percentage in a contingency table represents the proportion of the value in
a specific cell relative to the grand total of all values in the table. This percentage helps understand the
cell's contribution to the overall total number of observations.

To calculate an overall percentage, divide the individual cell frequency by the grand total. The grand total is
the number of observations in the bottom right of the table. For example, if you wanted to know what
percentage of customers purchased electronics and paid by credit card, then you would divide the number
of transactions in that specific cell by the grand total and multiply by 100%.

Percentage of customers who purchased electronics and paid with a credit card:

This means that 23% of all transactions were credit card transactions for electronics.

Row percentages: A row percentage in a contingency table represents the proportion of a specific cell’s
value relative to the total of its row. Row percentages help in comparing the distribution of different
categories within the same row.

To calculate a row percentage, divide the individual cell frequency by the row total and multiply by 100%.

Row percentage of credit card transactions for electronics:

This means that 54% of the electronics transactions were made using credit cards.

Column percentages: A column percentage in a contingency table represents the proportion of a specific
cell’s value relative to the total of its column. Column percentages help in comparing the distribution of
different categories within the same column.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 75
 To calculate a column percentage, divide the individual cell frequency by the column total and multiply by
100%.

Column percentage of credit card transactions for electronics:

This means that 45% of the credit card transactions were for electronics.

 TERMS TO KNOW

Overall Percentage
Percentage of observations in a specific cell relative to the total number of observations in a
contingency table.

Row Percentage
Percentage of each cell’s value relative to the total of its row in a contingency table.

Column Percentage
Percentage of each cell’s value relative to the total of its column in a contingency table.

2. Chi-Square Test of Association

A contingency table is a useful tool for summarizing the relationship between two categorical variables. A
contingency table provides a descriptive summary of the data via counts and percentages in the contingency
table, but does not provide any information related to how the variables are related to each other. A chi-square
test of association is needed to validate whether a relationship exists between categorical variables in a
contingency table.

The method used to determine if there is an association between two categorical variables in a contingency
table is a chi-square test of association. This test determines if two categorical variables are related, that is, if
there is an association between the two variables.

When a chi-square test assesses the association between two variables, it examines whether there is a
statistically significant relationship between them. Specifically:

Null Hypothesis ( ): The two variables are independent (that is, no association).
Alternative Hypothesis ( ): The two variables are dependent (that is, there is an association).

The chi-square test calculates an expected frequency distribution based on the assumption of independence. It
then compares this expected distribution with the observed frequencies in the data. If the observed frequencies
significantly deviate from what would be expected under independence, you reject the null hypothesis and
conclude that an association exists.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 76
 In practical terms, this test helps you understand whether changes in one variable are related to changes in
another. For example, you might investigate whether customer satisfaction scores (one variable) are associated
with the type of customer support channel used (another variable). If there’s a significant association,
businesses can tailor their support strategies accordingly.

Another application of using the chi-square test is to determine whether product preferences are related to
customer demographics (age groups, regions, or income levels). If yes, businesses can use this information to
tailor marketing strategies, optimize inventory management, or personalize recommendations.

Let’s illustrate the chi-square test of association using an example.

EXAMPLE You are still assisting an e-commerce manager with understanding the purchasing behaviors
of customers. You now want to determine whether there is a relationship between the payment method and
the product categories using the contingency table from the previous example, which is shown below.

You perform a chi-square test of association between the two variables and find that the hypothesis test is
significant. You can report to the manager that the payment method and product category are associated.
Further, you can provide the following practical implications to the manager that might assist in business
decisions.

Inventory Management: Adjust inventory levels based on payment trends. If Bitcoin is popular for
specific products, ensure sufficient stock for those items. Avoid overstocking products associated with
less common payment methods.
Partnerships and Agreements: Collaborate with payment providers based on usage patterns.
Negotiate favorable terms with providers preferred by customers. Explore partnerships with popular
payment gateways.
Pricing and Discounts: Consider differential pricing or discounts based on payment methods.
Encourage customers to choose methods that benefit the business (lower transaction fees). Offer
exclusive deals for specific payment channels.

 TRY IT

Perform the chi-square test of association using the payment method and product category data discussed
in the previous example.

1. Open the payment_method_product_categories.xlsx file.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 77
 2. The first task that needs to be completed is to calculate the expected frequencies if the null hypothesis is
true. That is, if the payment method and product categories were independent, not related to each other,
what would the expected frequencies in the contingency table be?

To calculate the expected frequencies for each of the cells, you use the following formula:

where is the expected frequency if the null hypothesis is true.

Create a blank table right beside the contingency table that contains the observed counts in the Excel
workbook.

3. Calculate the first expected frequency for the Electronics/Credit Card cell. In cell H2, enter the following
formula:

=(E2*B5)/E5

4. Repeat this process to calculate the expected frequencies for all the cells of the contingency table. The
formulas are shown below.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 78
 The expected frequencies are shown below.

5. Use the [Link]() function to find the p-value from the Chi-square test. The [Link]() has two
arguments. The first argument is the range of data for the observed frequencies. The second argument is
the range of data for the expected frequencies.

Select an empty cell, type the following formula, and select ENTER.

=[Link](B2:D4,H2:J4)

The p-value should populate the cell as 0.004831106.

6. Make a decision. Since the p-value is less than the standard level of significance value of 0.05, you can
reject the null hypothesis and accept the alternative hypothesis. You conclude that there is a relationship
between payment method and product category.

7. Check your work by comparing the results of your chi-square test to the one in the example.

 WATCH

Follow along with this video on performing a chi-square test with Excel.

 HINT

It is important to remember from the Introduction to Statistics class that there are just two possible
conclusions when it comes to hypothesis testing:

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 79
 1. Reject the null hypothesis: If the p-value is less than the chosen significance level (commonly 0.05), you
reject the null hypothesis. This suggests that there is enough evidence to support the alternative
hypothesis.
2. Fail to reject the null hypothesis: If the p-value is greater than the significance level, you fail to reject the
null hypothesis. This means there is not enough evidence to support the alternative hypothesis, but it
does not prove that the null hypothesis is true. In fact, you should never conclude that the null
hypothesis is true.

Recall that from the previous example, the chi-square test of association is testing the following:

no association between payment method and product categories

association exists between payment method and product categories

Suppose that the p-value had been greater than 0.05, then you would have failed to reject the null
hypothesis.

When you fail to reject the null hypothesis, interpreting the results of the test can be tricky because of the
ambiguity involved. Failing to reject the null hypothesis does not confirm that the null hypothesis is true. It
simply means there is not enough evidence to support the alternative hypothesis.

The way you could interpret the results of this test, if you failed to reject the null hypothesis, would be:

There is not enough evidence to conclude that a significant association exists between payment method
and product type.

Interpreting what it means to reject or fail to reject the null hypothesis in a way that is relevant to the
business context helps ensure that the findings are actionable. For instance, if you fail to reject the null
hypothesis, you might explain that the choice of payment method does not appear to influence the type of
product purchased. This means that marketing strategies or payment options do not need to be adjusted
based on product type, allowing the business to focus resources on other areas that might have a more
significant impact.

 TERM TO KNOW

Chi-Square Test of Association

A statistical method used to determine if an association exists between two categorical variables.

 SUMMARY

In this lesson, you were introduced to how categorical data can be represented in crosstabulations. An
example was provided illustrating how to read data from a crosstabulation and what business

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 80
 implications can be gleaned from it. A chi-square test of association was presented that provides a
statistical method for determining if a relationship exists between two categorical variables.

Source: THIS TUTORIAL WAS AUTHORED BY SOPHIA LEARNING. PLEASE SEE OUR TERMS OF USE.

 TERMS TO KNOW

Chi-Square Test of Association

A statistical method used to determine if an association exists between two categorical variables.

Column Percentage
Percentage of each cell’s value relative to the total of its column in a contingency table.

Contingency Table
A synonym for crosstabulation.

Crosstabulation
A table that displays the frequencies or counts of observations that fall into different categories of
categorical variables.

Overall Percentage
Percentage of observations in a specific cell relative to the total number of observations in contingency
table.

Row Percentage
Percentage of each cell’s value relative to the total of its row in a contingency table.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 81
 PivotTables
by Sophia

 WHAT'S COVERED

In this lesson, you will learn more about PivotTables and be introduced to PivotCharts. Specifically, this
lesson will cover:
1. PivotTables
2. Description of Project Management Data Set
2a. Creating and Sorting a PivotTable
2b. Filtering a PivotTable
3. PivotCharts
3a. Creating a PivotChart

1. PivotTables
PivotTables are a powerful feature in Excel that allow you to summarize, analyze, explore, and present your
data, making it easier to extract meaningful insights. Think of PivotTables as tools that transform rows and
columns of data into more meaningful and concise formats. Once you learn the basics, PivotTables are
straightforward to understand. They instantly analyze data, replace manual calculations, and reduce thousands
of cells into a compact table for easier analysis. PivotTables are engaging and interactive. They help identify
trends and patterns and are more accurate than performing manual calculations. In general, the rows and
columns of a PivotTable are categorical variables, and the frequencies or counts being displayed in the body of
the PivotTable are quantitative variables. The next several sections of this tutorial will guide you through how
you can build and use PivotTables in Excel.

2. Description of Project Management Data Set

The concepts for constructing and using PivotTables in Excel will be illustrated with a modified version of a
project management data set, project_magangement.xlsx.

The data contains information about employees who have been assigned several work tasks at a company. The
data set includes variables such as Project Name, Task Name, Assigned To, Start Date, Days Required, End
Date, and Cost. The data consists of 43 rows and eight columns.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 82
 In the next several sections, you are going to use a project management data set to determine the overall costs
of the projects in the company, determine the overall progress of projects in the company, identify top
performers, explore lagging projects, and determine where additional support is needed.

2a. Creating and Sorting a PivotTable

Suppose you want to analyze the cost of the different projects to identify which projects are the most costly for
a company. You can use a PivotTable and then sort the PivotTable values in descending order. Let’s look at an
example of how to accomplish this analytical task.

EXAMPLE Imagine you work for a large multinational company that manages multiple projects across
different departments. Each project has associated costs, including labor, materials, and overhead
expenses. The CFO (Chief Financial Officer) wants to identify the most expensive project to allocate
resources more effectively.

The PivotTable below displays that the projects related to Operations are the costliest for the company.

 TRY IT

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 83
 Create a PivotTable that displays the cost for each project and sort the costs in descending order.

1. Open the project_management_data.xlsx file.

2. Select all the data in the worksheet (cells A1 to H44).
3. From the Insert menu select PivotTable --> From Table/Range.

4. The Insert PivotTable dialog box opens. Select + New Worksheet to place the PivotTable in a separate
sheet from the data.

5. Drag Project Name to the Rows box and Cost to the ∑ Values box. The PivotTable will look as follows:

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 84
 6. You can sort a column in a PivotTable the same way you sort any other column in Excel. Select a row
cell in the Sum of Cost column and on the Home menu, select Sort and Filter --> Sort Descending. You
can then format the Sum of Cost column to have dollar signs and no decimal places using the
formatting techniques from a previous tutorial.

The final PivotTable provides you with the costliest project, which is Operations, followed by Production
projects.

7. Check your work by comparing your PivotTable to the one in the example.

2b. Filtering a PivotTable

There are several reasons why you would need to filter the results of a PivotTable. Thinking about the project
management data you have been working on, being able to filter the PivotTable could be useful for project

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 85
 managers. Filtering would allow project managers to assess how much work each team member is handling,
identify team members who are overworked or underutilized, and allocate resources more effectively based on
progress and workload.

Let’s see if we can provide the project manager with some useful insights by filtering a PivotTable.

EXAMPLE Let's continue with the example of you working for a large multinational company that
manages multiple projects across different departments. The table below provides a filtered data table for
team members who have completed more than 50% of their assigned projects. This PivotTable would be
useful for a project manager because the project manager could use this information to start planning
upcoming tasks for team members that are nearing completion or are completed.

From the filtered PivotTable above, you can see that Fiona is working on two projects, and she is almost
80% completed with her Product Development project.

 TRY IT

Create a PivotTable that displays the team members who have completed more than 50% of their assigned
projects.

1. Open the project_management_data.xlsx file.

2. Select all the data in the worksheet (cells A1 to H44).
3. From the Insert menu, select PivotTable --> From Table/Range.
4. The Insert PivotTable dialog box opens. Select + New Worksheet to place the PivotTable in a separate
sheet from the data.
5. Drag the column Assign To to the Rows box, drag Project Name to the Columns box, drag Progress to
the ∑ Values box, and drag Progress to the Filter box.
6. The filter for Progress is placed in the top left of the worksheet. Select the drop-down arrow for the
filter. Click the Select All to deselect all the values of Progress and then select all the values greater
than 50%. Select Apply.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 86
 The PivotTable will be updated to show all the team members who have more than 50% completed
their projects.

7. Check your work by comparing your PivotTable to the one in the example.

 TRY IT

Looking at your PivotTable, which tasks are 100% complete? +

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 87
 Bug Fixes, Software Development, and Technical Support are 100% complete.

 MAKE THE CONNECTION

You might be looking at PivotTables and thinking they are like crosstabulations. While they do have some
similarities, there are some differences. The table below compares a crosstabulation with a PivotTable.

Differences Crosstabulations PivotTables

Data type Analyze relationships between two Can analyze relationships among multiple
categorical variables variables, both categorical and quantitative

Aggregation Display frequencies/counts Can display frequencies, averages, or sums

3. PivotCharts
PivotTables are a great way to summarize raw data into a summary table so that it is easier to see the big
picture. Sometimes, you cannot look at numbers in a table to see what is going on or if there is anything
interesting you want to highlight or explore further. PivotCharts are a great way to add visualizations to your
PivotTable. PivotCharts complement PivotTables by providing a graphical representation of the data in the
PivotTable. PivotCharts assist in exploring and presenting summarized data in an interactive and visual format.

 DID YOU KNOW

PivotCharts are directly linked to a PivotTable. When you update your PivotTable (change filters, add new
data), the PivotChart automatically reflects those changes. Regular charts require manual adjustments to the
graphs when the data changes.

 TERM TO KNOW

PivotChart
A visual display of the summarized data contained in a PivotTable.

3a. Creating a PivotChart

Imagine you want to visualize the duration of different projects. The visualization via a PivotChart will allow you
to easily see which projects are shorter or longer, helping prioritize resources and deadlines. Let’s look at an
example of how to accomplish this analytical task.

EXAMPLE Let's continue with the example of you working for a large multinational company that
manages multiple projects across different departments. You are working with the project manager for the
company, and the manager knows from past historical projects that projects related to the Sales and
Development Departments typically take the longest time. It is close to the end of the year, and the project
manager wants to look ahead to next year, starting in January, and see what the days required are for the

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 88
 projects related to the Sales and Development Departments. You construct the following horizontal bar
chart via a PivotChart to share with the project manager.

This bar chart will help the project manager allocate resources to an upcoming Sales project expected to
take close to two months to complete. This chart can be constructed using the PivotChart functionality in
Excel.

 TRY IT

Construct a PivotChart in a horizontal bar chart that shows the start dates on the vertical axis and the days
required for the Development and Sales projects.

1. Open the project_management_data.xlsx file.

2. Select all the data in the worksheet (cells A1 to H44).

3. From the Insert menu, select PivotTable --> From Table/Range.

4. The Insert PivotTable dialog box opens. Select + New WorkSheet to place the PivotTable in a separate
sheet from the data.

5. Drag the column Start Date to the Rows box, drag Project Name to the Columns box, and drag Days
Required to the ∑ Values box.

6. Sort the Grand Total column in the PivotChart in ascending order (this will ensure that the bars in the final
bar chart are sorted in descending order). Select a cell in the Grand Total column and on the Home menu,
select Sort and Filter --> Sort Ascending.

7. From the Insert menu, select the drop-down arrow by the pictures of charts as shown in the image below.
Select the Stacked Bar option.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 89
 You will see a stacked bar chart overlaid on the PivotTable. A stacked bar chart displays two categorical
variables (in this case Project Name and Start Date). Each bar is divided into sub-bars, where the sub-bar
represents the proportion of the quantity being displayed for one of the categories. For example, below is a
partial snapshot of the stacked bar chart that you just created for 1/1/2024.

The bar for 1/1/2024 shows you that for the projects that start on January 1, 2024, the number of days
required for the Support projects is larger than the number of days required for the Marketing projects.

The stacked bar chart that you created is way too cluttered for you to make any sense of. Let’s filter the
PivotTable to only examine projects related to Sales and Development.

When you create the PivotChart, the right-hand side of Excel 365 updates to the PivotChart Fields dialog
box. As you modify the PivotTable using these options, the PivotChart updates automatically.

8. Drag Project Name to the Filters box. The filter for Project Name is placed in the top left of the
worksheet. Select the drop-down arrow for the filter. Click the Select All to deselect all the values of Project
Name and then select the Sales and Development departments.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 90
 9. Drag Project Name from the Filters box to the Legend (Series) box in the PivotChart Fields dialog box.

10. The PivotChart should update to look like the following:

11. Label the axes and provide a title for the PivotChart using Excel techniques from a previous tutorial so
that the PivotChart looks like the following:

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 91
 12. Check your work by comparing your PivotChart to the one in the example.

 WATCH

Follow along with this video to create a PivotChart.

 TERM TO KNOW

Stacked Bar Chart

An extension of the standard bar chart by displaying numeric values across two categorical variables.

 SUMMARY

In this lesson, you learned how to construct PivotTables and PivotCharts. You extended what you
learned from a previous lesson that used PivotTables by performing more advanced functionality with
PivotTables like sorting and filtering. A PivotChart is an extension of a PivotTable that allows you to
visualize the data in a PivotChart. The concepts and applications of these two topics were illustrated
using a project management data set.

Source: THIS TUTORIAL WAS AUTHORED BY SOPHIA LEARNING. PLEASE SEE OUR TERMS OF USE.

 TERMS TO KNOW

PivotChart
A visual display of the summarized data contained in a PivotTable.

Stacked Bar Chart

An extension of the standard bar chart by displaying numeric values across two categorical variables.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 92
 Creating Charts
by Sophia

 WHAT'S COVERED

In this lesson, you will learn how to use several of the most widely used charts to analyze summarized
data. Specifically, this lesson will cover:
1. Basics of Excel Charts
2. Scenario: Verde Candy
2a. Line Chart
2b. Scatter Plots
2c. Bar Charts
2d. Bar Charts in Excel

1. Basics of Excel Charts

Before diving into analyzing summarized data from Excel charts, it is important to understand the visuals
provided in Excel and their purposes. The table below summarizes these concepts.

Visual in Excel Purpose

Column chart (also known Compare categories/levels of a categorical variable

as a vertical bar chart)

Bar chart (horizontal bar Like column chart but with horizontal bars
chart)

Line chart Ideal for showing trends over time. Generally, a time variable (year, month, day)
is on the horizontal axis, and a quantitative variable is on the vertical axis

Scatter plot Shows the relationship between two quantitative variables

Histogram Represents the distribution of a quantitative variable

2. Scenario: Verde Candy

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 93
 Verde Candy has created a version of tooth-friendly candy that includes lollipops, chocolate bars, and gummy
bears. These vegan, sugar-free treats are currently only sold in California. For the past year, the company has
been trying to expand into Oregon. Verde Candy has conducted market research specific to Salem, Oregon,
trying to understand local preferences, demographics, and existing competitors. They have attempted to target
health-conscious consumers, eco-friendly shoppers, and those seeking unique treats. Over the last year, the
company has entered the Salem, Oregon market. They have specifically spent money on marketing their
product using social media influencers and local bloggers to create buzz.

The table below shows the data collected over the last year. The data has been summarized by month. That is,
for each month, the company has recorded their sales in the Salem, OR market, how much they spent on
marketing, and the number of new customers they have acquired. The company wants to expand to other parts
of Oregon beyond Salem, so they have asked you to analyze the data in summarized charts in Excel and report
any interesting insights to determine if their efforts in expanding into Salem are paying off.

2a. Line Chart

A line chart (also known as a line plot or line graph) is a powerful tool for visualizing trends and changes in data
over time. Here is how a line chart helps business data analysts analyze summary data in Excel:

Visualizing Trends: Line charts display data points connected by line segments, emphasizing changes in a
single quantitative variable (usually on the vertical axis) over values of another variable (typically time on the
horizontal axis). Analysts can easily identify upward or downward trends, seasonality, or fluctuations.
Comparing Series: Multiple line graphs can be plotted on a single chart to compare trends across different
subgroups or categories. For instance, you can compare sales performance across different product lines

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 94
 or regions.
Highlighting Differences: Line charts help highlight differences between data points. By observing the
slopes of the lines, analysts can identify variations within the data.
Predictive Insights: The linear perspective of line charts allows analysts to make predictions about future
trends. For example, if sales have been consistently increasing, analysts might predict continued growth.

Let’s look at an example of how you can construct a line chart for Verde Candy.

EXAMPLE You provide Verde Candy with the following line chart that you created in Excel to show
them their sales over the last year in the Salem, OR market.

Overall, the company’s sales have increased throughout the year. There were a couple of months in which
sales were down slightly, but considering this was the company’s first year in a new market, this line chart
provides good news for the company.

 TRY IT

Create a line chart that shows Verde Candy’s sales over the first year they were in the Salem, OR market.

1. Open the Verde_Candy.xlsx data set.

2. Select the two columns Month and Sales ($).
3. From the Insert menu, select the drop-down arrow by the pictures of charts as shown in the image
below. Select Line with Markers. This places a marker (small circle) for each data point in the line chart.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 95
 4. To remove the gridlines in the background, in the Chart menu, select Gridlines --> Primary Horizontal
Gridlines --> None.
5. Label the axes and provide a title for the line chart using Excel techniques from a previous tutorial so
that the final line chart looks like the following:

6. Check your work by comparing your line chart to the one in the example.

 TERM TO KNOW

Line Chart
A graph in which a line connects a series of data points that are plotted over a period of time.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 96
 2b. Scatter Plots
A scatter plot is a chart type used to observe and visually display the relationship between two quantitative
variables. Here is how scatter plots help business data analysts analyze summary data in Excel:

Demonstrating Relationships: Scatter plots show how two variables relate to each other. By plotting
individual data points, analysts can identify correlations and trends even from seemingly disparate data.
Detecting Data Patterns: Grouping data points based on proximity helps identify patterns. Outliers or gaps
become noticeable, aiding decision-making.
Detecting Correlation: A scatter plot makes it quite easy to detect how two variables are changing in
relation to each other. A scatter plot helps you detect whether one variable is increasing or decreasing, and
what the behavior of the other variable is—is it increasing or decreasing.

Let’s look at an example of how you can construct a scatter plot for Verde Candy.

EXAMPLE You created a scatter plot for Verde Candy to help them determine if the marketing money
spent is helping create new customers. You create the scatter plot below.

It looks like Verde Candy has found the ‘sweet spot’ on how and who to market to in Salem, OR. As the
amount the company has spent on marketing is increasing, so is the number of new customers that Verde
Candy is acquiring.

 TRY IT

Create a scatter plot for Verde Candy that shows the relationship between the amount the company has
spent on marketing and the number of new customers the company has gained in the Salem, OR market.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 97
 1. Open the Verde_Candy.xlsx data set.
2. Select the two columns Marketing Spend ($) and New Customers.
3. From the Insert menu, select the drop-down arrow for the Recommended Charts and select Scatter
with only Markers. This will place a marker (small circle) for each data point in the scatter plot.
4. To remove the gridlines in the background, in the Chart menu, select Gridlines --> Primary Horizontal
Gridlines --> None. Repeat the same process to remove the vertical gridlines.
5. Label the axes and provide a title for the line chart using Excel techniques from a previous tutorial so
that the final scatter plot looks like the following:

6. Check your work by comparing your scatter plot to the one in the example.

 TERM TO KNOW

Scatter Plot
A chart type used to observe and visually display the relationship between two quantitative variables.

2c. Bar Charts

A bar chart is a chart type used to compare the categories of a categorical variable. Each bar represents a
summary value (such as counts, sum, or mean) for a specific category. By visually comparing bar lengths, you
can quickly identify differences between categories. Here’s why they are useful and how they can aid in
analyzing summarized data.

Understanding Relationships: If your data set includes multiple categorical variables, bar charts help you
understand the relationships between them. For example, you can compare sales performance across

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 98
 different product categories or analyze survey responses by demographic groups.
Highlighting Trends and Variations: Bar charts are excellent for illustrating trends over time or comparing
quantities across various groups. They can highlight significant variations or outliers, making it easier to
spot patterns or anomalies.

 HINT

Excel makes a unique distinction between column and bar charts; column charts being vertical bar charts
and bar charts being horizontal bar charts. Most analysts would call both of these bar charts and then
specify horizontal/vertical. In this course, we will use “column chart” in instructions where Excel is involved,
but you should keep in mind that these can all be referred to as “bar charts.”

2d. Bar Charts in Excel

When constructing a bar chart in Excel, your data must be aggregated before you construct it. Aggregating
data means summarizing data at an individual row level to a summarized quantity such as a total or average.
This summarization should be performed using the group or category level. For example, the data shown below
shows sales data over a few days for two products.

The data in the table has not been aggregated. The data in the table would be described as granular data. Data
at the granular level means each row is stored separately at the individual level. For example, daily sales
transactions for each product are listed individually rather than being summed up by month or year.

 DID YOU KNOW

Suppose you wanted to know which product was selling more, Product A or B? An Excel novice might be
tempted to create a bar chart using the granular data and will receive unexpected results. If your data is not
aggregated in Excel and you try to construct a bar chart on the granular data, you will get a bar chart like
the figure shown below.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 99
 Each product has its own bar, and its height represents the sales value for that product. Each row in the
data will be plotted in the bar chart. This bar chart does not provide you with any meaningful information
about the data. If you wanted to figure out something simple like which product has more sales, that would
be a time-consuming and arduous task even with these very few observations.
When data is not aggregated, a bar chart can become cluttered and difficult to interpret, especially if you are
trying to identify overall trends or compare totals across categories. Aggregating the data simplifies the bar
chart and makes it much easier to extract meaningful insights.

The easiest way to aggregate data in Excel is by using a pivot chart and then constructing the bar chart from the
pivot table. Let’s explore how Verde Candy can use bar charts to compare sales performance across different
products.

EXAMPLE Verde Candy, which is new to the Salem, OR market, decided to obtain a vendor booth at the
Salem Sweet & Green Festival. The festival features a variety of plant-based and eco-friendly vendors, live
music, cooking demonstrations, and family-friendly activities, all centered around sustainable and healthy
living. This is the perfect venue for Verde Candy to showcase its new products. Verde Candy records the
quantity and sales of each transaction over the festival for each of their products and the customers' age
group. A sample of the data is shown below. This data is at the granular level because each row is an
individual transaction.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 100
 Verde Candy is interested in knowing how their sales compare across products and age groups. You
construct the following side-by-side bar chart.

A side-by-side bar chart is used to compare categories of multiple categorical variables side by side. Each
category has multiple bars that correspond to another category of a different variable. For example, the
Chocolate product has three bars that represent the sales of the categories of the Age Group. Side-by-side
bar charts allow you to make comparisons between multiple categorical variables. Below are some
interesting insights you can share with Verde Candy using the side-by-side bar chart.

Lollipops Popularity with Families: Lollipops are the top-selling product among the Family age group.
This suggests that families prefer Lollipops over other sweets.
Young Adults Love Chocolate: For the Young Adult age group, Chocolate is the most popular, with
sales right at $250.
Wide Range for Chocolate: Chocolate has the widest range of sales among different age groups. It is
very popular with Young Adults but less so with Teens and Families.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 101
  TRY IT

Create a side-by-side bar chart for sales for the products and age groups for Verde Candy.

1. Open the Verde_Candy.xlsx data set.

2. From the Insert menu, select Pivot Table --> From Table/Range.
3. The Insert Pivot Table dialog box opens. Select + New Sheet to place the pivot table in a separate sheet
from the data.
4. Drag the column Product Type to the Rows box, drag Age Group to the Columns box, and drag Sales
to the ∑ Values box.
5. From the Insert menu, select the drop-down arrow for Recommended Charts --> Clustered Column.
6. Label the axes, provide a title, and remove the gridlines for the side-by-side bar chart using Excel
techniques from a previous tutorial so that the final line chart looks like the following:

7. Check your work by comparing your side-by-side bar chart to the one in the example.

You have learned about a lot of different types of graphs and their uses in an applied business analytics
scenario in this tutorial. The table below summarizes the key uses for each of the graphs.

Chart Key Uses Data Type

Bar charts Comparing Categories: Ideal for comparing different Categorical variables
categories or groups. For example, comparing sales figures
across different regions or product lines.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 102
 Line charts Tracking Changes Over Time: Excellent for showing trends Continuous variables
and changes over time, such as stock prices, sales growth,
or website traffic.
Comparing Multiple Data Sets: Compare multiple data sets
on the same graph, such as comparing the performance of
different products over time.

Scatter plots Identifying Relationships: Used to identify relationships Continuous variables

between two variables, such as advertising spend and sales
revenue.

Histograms Visualize Distribution of a Single Variable: Perfect for Continuous variables

analyzing the distribution of customers’ ages, income
levels, or purchase histories.

 TERMS TO KNOW

Aggregated Data
Summarizing a set of individual data points into a single representative value, such as a total or
average.

Granular Data
Individual information about a data point that is broken down into the smallest possible unit.

Side-by-Side Bar Chart

Bar chart that displays multiple bars next to each other for each category of the categorical variable
being displayed.

 SUMMARY

In this lesson, you were introduced to some of the most popular graphs used in business data analytics
for analyzing summarized data such as line charts, scatter plots, and bar charts. You were provided
with guidance on selecting the best chart to showcase your data based on the data types of the
variables.

Source: THIS TUTORIAL WAS AUTHORED BY SOPHIA LEARNING. PLEASE SEE OUR TERMS OF USE.

 TERMS TO KNOW

Aggregated Data
Summarizing a set of individual data points into a single representative value such as a total or average.

Granular Data
Individual information about a data point that is broken down into the smallest possible unit.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 103
 Line Chart
A graph in which a line connects a series of data points that are plotted over a period of time.

Scatter Plot
A chart type used to observe and visually display the relationship between two quantitative variables.

Side-by-Side Bar Chart

Bar chart that displays multiple bars next to each other for each category of the categorical variable being
displayed.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 104
 Histograms and Frequency Polygons
by Sophia

 WHAT'S COVERED

In this lesson, you will explore how to apply histograms, frequency polygons, and cumulative
distributions to aid in finding insights from data. Specifically, this lesson will cover:
1. Interpreting Histograms, Frequency Polygons, and Cumulative Distributions
1a. Histograms
1b. Frequency Polygons
1c. Cumulative Frequency Distribution

1. Interpreting Histograms, Frequency Polygons,

and Cumulative Distributions
Interpreting histograms, frequency polygons, and cumulative distributions is crucial for analyzing data in
business analytics. These graphical representations help us visualize data distributions, identify patterns, and
make informed decisions. Let’s dive into each of these concepts in detail.

1a. Histograms
A histogram is a graphical representation of the distribution of a quantitative variable. It is like a bar chart but
represents continuous data. Histograms are used to show the frequency of data points within specified
intervals, called bins.

In histograms, the bars touch each other, whereas in bar charts, the bars do not touch each other. The bars in a
histogram touch each other because the data is continuous, meaning there are no gaps between the intervals
(bins). Each bar represents a range of values, and the touching bars indicate that the ranges are adjacent and
continuous. The bars in a bar chart do not touch because the data is categorical, meaning each bar represents
a distinct category. The gaps between the bars emphasize that the categories are separate and not part of a
continuous range.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 105
 To remind you of the basic ideas of a histogram, below is a summary of what you learned about in a previous
tutorial:

Key Components of a Histogram:

Bins: These are the ranges into which the data is divided. Each bin has a specific width.
Frequency: The height of each bar represents the number of data points within that bin.

Steps to Create a Histogram:

1. Collect Data: Gather the quantitative data you want to analyze. If in Excel, have one column that contains
the quantitative variable.
2. Determine the Number of Bins: Decide how many bins to use. Too few bins can oversimplify the data, while
too many bins can overcomplicate it. In a previous tutorial, specific guidelines were provided for how to
determine the number of bins.
3. Calculate Bin Width: Divide the range of the data by the number of bins.
4. Count Frequencies: Count the number of data points that fall into each bin.
5. Construct the Histogram: Plot the bins on the x-axis and the frequencies on the y-axis.

Interpreting Histograms:

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 106
 Shape: Look at the overall shape of the histogram. Common shapes include:
Symmetrical: The left and right sides are mirror images.
Skewed Right (Positive Skew): The tail is on the right side.
Skewed Left (Negative Skew): The tail is on the left side.

Now that we have reviewed the key elements of a histogram, let's see how we can use a histogram in a real-
world scenario to assist in business decision-making.

EXAMPLE A local bank has been receiving complaints about long wait times. The bank manager, Lucia,
has decided to analyze the wait times to improve customer satisfaction. Lucia collects data on the wait
times of 200 customers over a month. The wait times are recorded in minutes. Lucia organizes the wait
times into intervals (bins), such as 0–2 minutes, 2–4 minutes, 4–6 minutes, etc. She creates a histogram to
visualize the frequency of wait times within each interval, as shown below.

The histogram shows that most customers wait between 4–6 minutes, with a significant number waiting
even longer. The bank has an internal goal set that it does not want to have customers waiting more than
five minutes. So, using the information from the histogram, Lucia decides to implement a new queue
management system to streamline the process and reduce wait times.
 TRY IT

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 107
 A digital marketing agency wants to analyze the engagement levels of their latest social media campaign
across different platforms. They collect data on the number of likes, comments, and shares for 200 posts
across Instagram, TikTok, and Twitter. Below is a histogram that the marketing department created. The data
includes the total engagement (likes + comments + shares) for each post. The engagement data is
organized into intervals (bins), such as 0–100, 100–200, 200–300, etc. The histogram is shown below.

The marketing department needs your assistance in interpreting the histogram. They ask you the following
questions.

What is the most common range of total engagement for the posts? +

Look for the tallest bar in the histogram, which is the bar that contains the number of posts between
700 and 800.

How many posts received between 200 and 300 total engagements? +

20

Based on the histogram, what recommendations would you make to improve future social media
+
campaigns?

Examine the posts that receive more than 800 engagements. Is there something different/unique
about those types of posts compared to posts that receive, say 100 or 200 engagements? That is,

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 108
 could you build a regression model where the number of engagements was your response variable
and try to identify predictor variables for the response?

1b. Frequency Polygons

A frequency polygon is a line graph that represents the frequencies of data points within different intervals. It is
like a histogram but uses points connected by straight lines instead of bars.

The components of a frequency polygon are described below.

Key Components of a Frequency Polygon:

Midpoints: The midpoints of the bins used in the histogram.

Frequencies: The number of data points within each bin.

Steps to Create a Frequency Polygon:

1. Create a Histogram: Start by creating a histogram of the data.

2. Calculate Midpoints: Find the midpoint of each bin.
3. Plot Points: Plot the midpoints on the x-axis and the corresponding frequencies on the y-axis.
4. Connect Points: Connect the points with straight lines.

Interpreting Frequency Polygons:

Shape: Like histograms, look at the overall shape of the frequency polygon.
Trends: Frequency polygons make it easier to see trends and patterns over intervals.

Below is an example of a frequency polygon for the number of products sold for a particular product. The
frequency polygon has been overlaid on the outline of the histogram so you can compare the two different
visuals and understand how they visually represent the data in two different ways.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 109
 You do not include the outline of the histogram in a frequency polygon. The final frequency polygon for the
product data would look like the following:

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 110
  THINK ABOUT IT

Why would you use a frequency polygon instead of a histogram?

Choosing between a frequency polygon and a histogram depends on the specific needs of your data
analysis and visualization goals. Frequency polygons are particularly useful when you need to compare
multiple data distributions on the same graph. The lines can overlap without cluttering the visualization,
making it easier to see differences and similarities between distributions.

The continuous line of a frequency polygon provides a smoother representation of the data, which can help
in identifying trends and patterns more clearly than the discrete bars of a histogram. Frequency polygons
can make it easier to highlight trends over time or across different categories, as the lines naturally guide
the eye along the data points.
The table below provides guidance on when to use a histogram versus a frequency polygon.

Visualization Type When to Use

Histogram Clear and straightforward visualization of a single data distribution

Large data set with a frequency of data points within specific intervals

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 111
 Interest in exact frequency counts within each interval

Frequency polygon Comparing multiple distributions on the same graph

Smoother, less cluttered representation of the data
Highlighting trends and patterns more effectively

EXAMPLE Suppose you are analyzing delivery times for two different services over a year. Using a
frequency polygon allows you to overlay the distributions of delivery times for both services on the same
graph, making it easier to compare their performance and identify any significant differences. The
frequency polygon for this scenario is shown below.

 TERM TO KNOW

Frequency Polygon
Type of line graph that is like a histogram but uses points connected by straight lines instead of bars to
represent the distribution of data.

1c. Cumulative Frequency Distribution

A cumulative distribution (or cumulative frequency distribution) shows the cumulative frequency of data points
up to a certain value. It helps us understand the proportion of data points that fall below a specific value.

One of the most popular types of cumulative distributions to visualize is a cumulative histogram. A cumulative
histogram uses a cumulative frequency distribution for its construction.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 112
 Key Components of a Cumulative Distribution:

Cumulative Frequency: The cumulative total of frequencies up to each bin.

Cumulative Percentage: The cumulative frequency expressed as a percentage of the total number of data
points.

Steps to Create a Cumulative Distribution:

1. Create a Histogram: Start with a histogram of the data.

2. Calculate Cumulative Frequencies: Add the frequencies of each bin to the sum of the previous bins.
3. Plot the Cumulative Frequencies: Plot the cumulative frequencies on the y-axis against the bin values on
the x-axis.

Interpreting Cumulative Distributions:

Growth: Observe how the cumulative frequency increases.

EXAMPLE Imagine you are a data analyst for a popular online clothing store that targets young adults.
The store has been tracking its monthly sales data for a new line of eco-friendly clothing. The goal is to
understand sales trends and make data-driven decisions to improve marketing strategies and inventory
management.

To analyze the sales data, you create a cumulative frequency distribution for each month’s sales. This graph
will help visualize how sales have accumulated over the year and identify key trends.

The cumulative frequency distribution is below.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 113
 This cumulative frequency histogram can provide the retailer with several business insights, such as:

Overall Growth: The upward trend in the cumulative histogram indicates that sales are consistently
increasing month over month. The graph suggests a positive growth trend for the eco-friendly clothing
line.
Sales Milestones: By looking at the cumulative frequency, you can identify key milestones. For
example, you can see that in April (month 4), the store reached 50% of its total annual sales, which
helps in understanding the pace of growth.
Inventory Planning: Understanding the cumulative sales helps in planning inventory. If certain months
show higher cumulative sales, the store can prepare by stocking more inventory in anticipation of
increased demand.
Marketing Effectiveness: The cumulative frequency distribution can also help assess the effectiveness
of marketing strategies. If a particular campaign led to a noticeable increase in cumulative sales in one
month, this might indicate that the campaign was successful.
 TRY IT

VizAcademy is a company that provides educational resources to individuals who want to learn more about
data visualization methods and techniques. The company wants to analyze the time spent on its website.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 114
 The cumulative distribution of the number of minutes visitors have spent on a website is represented in
both frequency and percentage form using the cumulative frequency polygon below.

Using the cumulative frequency polygon, answer the following questions:

What is the total number of website visits recorded in the data set? +

To determine the total number of website visits recorded in the data set, you can look at the highest
value on the cumulative frequency line. From the cumulative frequency polygon provided, it appears
that the cumulative frequency reaches its maximum at around 20 visits.

At what duration do 50% of the website visits fall below? +

To determine the duration at which 50% of the website visits fall below, you need to look at where the
cumulative percentage line crosses the 50% mark on the y-axis.

From the cumulative frequency polygon provided, it appears that the 50% mark is crossed at around 10
minutes. This means that half of the website visits lasted less than or equal to 10 minutes.

Are there any noticeable trends and patterns in the cumulative frequency polygon? +

Yes. The cumulative frequency line shows an increase between approximately 5 and 15 minutes. This
indicates that many website visits fall within this duration range.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 115
 After around 15 minutes, the cumulative frequency line begins to plateau. This suggests that fewer
visits extend beyond this duration.

How would you modify the visualization to make it more informative or easier to understand for
+
individuals using the polygon to make decisions?

Display the exact values of cumulative frequency and cumulative percentage at each data point
(adding data labels). Highlight key points, such as where 50% of visits fall below, with annotations. The
revised graph would look like:

 SUMMARY

In this lesson, you explored how to interpret histograms, frequency polygons, and cumulative
distributions within real-world business analytics applications. You learned how these visualizations
provide valuable insights into data distributions, trends, and patterns, enabling informed decision-
making. By analyzing histograms, you can assess data distribution and identify key frequencies.
Frequency polygons offer a clearer way to compare multiple data sets, while cumulative distributions

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 116
 help track cumulative trends over time. Understanding these visualizations equips you with essential
tools for analyzing business data and deriving actionable insights.

Source: THIS TUTORIAL WAS AUTHORED BY SOPHIA LEARNING. PLEASE SEE OUR TERMS OF USE.

 TERMS TO KNOW

Frequency Polygon
Type of line graph that is like a histogram but uses points connected by straight lines instead of bars to
represent the distribution of data.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 117
 Effective Data Dashboards
by Sophia

 WHAT'S COVERED

In this lesson, you will be introduced to dashboards and learn about practical design techniques that
can be implemented into dashboards to make them more effective. Specifically, this lesson will cover:
1. Introduction to Dashboards
2. Poorly Designed Dashboard
3. Principles of Effective Dashboard Design
4. Redesigned Dashboard

1. Introduction to Dashboards
A dashboard is a visual display of the most important information needed to achieve one or more objectives,
consolidated and arranged on a single screen so the information can be monitored. Dashboards are used in
various fields, including business, healthcare, and education, to track performance, identify trends, and make
data-driven decisions.

There are several key components of a dashboard, and they include:

Dashboard Component Description

Charts and graphs Visual representations of data, such as bar charts, line graphs, and pie charts, help
to quickly understand trends and patterns

Tables Organized data in rows and columns, useful for detailed analysis

KPIs (Key Performance Metrics that reflect the performance of an organization or some aspect of it
Indicators)

Filters Tools that allow users to customize the data displayed in the dashboard

Interactive elements Features like drill-down and hover-over (tooltips) details that provide more in-
depth information
Drill-down allows users to click on a data point to view more detailed information. For example, clicking on a bar
in a sales chart might reveal sales data broken down by product or region. It helps users explore data
hierarchies and gain deeper insights without cluttering the main dashboard with too much information. Hover-
over (or tooltips) displays additional information when the user hovers their mouse over a data point. This

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 118
 information can include data values, descriptions, or other relevant details. Hover-over elements provide
context and additional insights without requiring clicks, keeping the dashboard clean and user-friendly. For
example, in a profit margin chart, hovering over a data point might show the exact profit margin percentage and
additional notes about that period.

Below is an example of a dashboard titled “Banking Loan Report” for a fictitious bank named MAD Banking,
which provides a comprehensive overview of loan data. The key performance indicators (KPIs) are:

Offered Amount: $15,002,000

Accepted Amount: $15,882,489
Number of Loans: 7,937
Average Accepted Rate: 5.9%

The visualizations are:

Map: Displays loan amounts by state, with varying shades of orange indicating loan offered amounts. The
darker the orange, the higher the loan amount for that state.
Bar Chart (Top 10 States): Shows the offered amount by state, with California having the highest amount.
Heatmap (Loan Term and Risk Rate): Categorizes loans into four risk levels (low (1) to high (4)) and the loan
term. The color gradient represents the loan amount, with darker shades of orange indicating higher loan
amount. The heatmap shows that the largest amount of loans are in a risk category of 1 with an 11–20-year
term.

 Enlarge image
Source: Banking Loan Report | Tableau Public

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 119
 The type of dashboard you construct should be tailored to the specific needs and preferences of your
audience. Here are some considerations based on different types of audiences:

Dashboard
Audience Focus Features Example
Type

Executive Senior High-level metrics and Summarized data, A dashboard showing

management and KPIs that provide a quick trend analysis, and overall sales
executives overview of the KPIs with minimal performance, profit
organization’s detail margins, and market
performance share

Operational Middle Day-to-day operations Detailed data, real- A dashboard tracking

management and and performance metrics time updates, and daily sales, inventory
operational alerts for immediate levels, and production
teams action efficiency

Analytical Data analysts and In-depth analysis and Interactive elements, A dashboard analyzing
business analysts exploration of data drill-down capabilities, customer behavior,
and complex sales trends, and
visualizations market segmentation

Informational General staff and Informing and educating Clear and simple A dashboard presenting
stakeholders about specific topics or visualizations, the progress of a
projects explanatory text, and marketing campaign or
context project milestones

 TERMS TO KNOW

Dashboard
A visual display of key information and data points, typically presented in a way that is easy to read and
interpret in a single view.

Key Performance Indicators (KPIs)

Numerical values that demonstrate how effectively an organization is achieving its key business
objectives.

Drill-Down
The ability to explore more detailed data by clicking on a summary or high-level data point.

Hover-Over
Refers to the interactive feature that displays additional information when a user moves their cursor
over a specific element in a dashboard, such as a data point, bar, or line.

2. Poorly Designed Dashboard

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 120
 Sometimes the best way to determine how or why a dashboard has a poor design is to look at an example of a
poorly designed dashboard. The dashboard below is designed to visualize different aspects of business data,
such as sales, expenses, and profit.

 THINK ABOUT IT

Before you continue, take a few moments and examine the dashboard that is presented above. Ask
yourself these questions.
What is the primary purpose of this dashboard? Does it clearly convey this purpose?
Are the data visualizations clear and easy to understand? Are there any confusing elements?
Is the design aesthetically pleasing? Does it help or hinder the understanding of the data?

The list below provides you with several design flaws in this dashboard, such as:

Inconsistent colors: Different colors are used for the same type of data (e.g., sales data is brown in one
chart and blue in another), which can be confusing.
Poor data-to-visualization pairing: A pie chart isn’t ideal when you have many categories with similar
percentages because it becomes difficult to distinguish between the slices (categories). When slices are
too similar in size, it becomes hard to see the differences between them. This can make the chart look
cluttered and confusing.
No clear hierarchy: There is no clear visual hierarchy, making it difficult to determine which information is
most important.
Busy design: The overall design is cluttered, with too many elements crammed into a small space.
Annotation: There is very little labeling or guidance for the audience. For example, the side-by-side bar
chart has no legend, so the audience does not know what the colors mean. The pie chart does not have a
legend for its colors.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 121
 This example highlights the importance of thoughtful design in creating effective dashboards.

3. Principles of Effective Dashboard Design

Hopefully seeing a poorly designed dashboard has motivated you to learn about techniques that can be used
to improve the design of the dashboard you saw in the last section. You are going to be introduced to simple
yet effective dashboard design concepts in this section. In the next section, you will see the revised dashboard
with several of these techniques applied for a better designed dashboard.

Below are several techniques to consider when designing a dashboard.

1. Understand Your Audience

Identify stakeholders: Know who will use the dashboard and their data needs.
User proficiency: Consider the analytical/technical skills of your audience.
Decision-making needs: Understand what decisions will be made using the dashboard.

2. Define Clear Objectives

Purpose: Determine the primary goal of the dashboard (e.g., monitoring performance, tracking KPIs, etc...).
Key metrics: Identify the most important metrics to display.
Actionable insights: Ensure the dashboard provides insights that lead to action.

3. Choose the Right Visualizations

Charts and graphs: Use appropriate visualizations (for example, bar charts, line graphs, scatter plots, etc...)
based on the data type.
Avoid clutter: Keep the design simple and avoid unnecessary elements.
Consistency: Use consistent colors, fonts, and styles.

4. Layout and Design

Logical flow: Arrange elements in a logical order, usually from top-left to bottom-right.
White space: Use white space effectively to avoid a crowded look.
Responsive design: Ensure the dashboard is accessible on different devices (laptops, cell phones, and
tablets).
Clarity and context: Utilize annotations such as titles, appropriately labeled axes, legends, and any short
descriptions that help provide context to the data being presented. They can explain trends, highlight key
points, or provide additional information that might not be immediately obvious from the data alone.

5. Interactivity

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 122
 Filters and drill-downs: Allow users to filter data and drill down into details.
Tooltips: Provide additional information through tooltips.
Interactive elements: Use interactive elements like filters, tooltips, sliders, and buttons for better user
engagement.

4. Redesigned Dashboard
Let’s return to our poorly designed dashboard and implement some of the design methods discussed in the
previous section. Below is the redesigned dashboard.

The following improvements have been made.

Consistent Colors: The same colors are used for the same type of data across all charts (e.g., blue for sales,
orange for expenses, green for profit).

Appropriate Data-to-Visualization Pairing: Bar charts are used for categorical data (sales and expenses by
month), and a line chart is used for time series data (profit by month).

Proper Annotation: Legends and titles are added to provide context and guidance for the audience.

Clear Hierarchy: If you want the user to focus on sales, then put sales in the top-left corner of the dashboard.
Your audience will read and consume a dashboard in the same way they read a book. The audience will start in
the upper left corner and scan to the right. In the poorly designed dashboard, the sales, expenses, and profit
were all arranged in the same locations as in the redesigned dashboard. Now the layout is more strategic,
meaning with all the design changes described above, the layout is organized to highlight key metrics and
trends, making it easier to understand the most important information at one glance.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 123
 In addition, you could add tooltips and a filter for some interactive functionality. The dashboard below shows
the same dashboard as above, but with some interaction added. You notice the drop-down functionality so that
the user can select a certain month or months to focus on. The dashboard will update to only show the months
selected. When the user toggles over a data element in the dashboard, a tooltip displays the exact value of that
data element (bar, point, line, etc).

 Enlarge image

 SUMMARY

In this lesson, you learned about dashboards and some design techniques that should be implemented
into dashboards to make them more effective. You were shown a poorly designed dashboard and
then, using some design best practices, a redesigned dashboard that was more user-friendly.

Source: THIS TUTORIAL WAS AUTHORED BY SOPHIA LEARNING. PLEASE SEE OUR TERMS OF USE.

 TERMS TO KNOW

Dashboard
A visual display of key information and data points, typically presented in a way that is easy to read and
interpret in a single view.

Drill-Down
The ability to explore more detailed data by clicking on a summary or high-level data point.

Hover-Over
Refers to the interactive feature that displays additional information when a user moves their cursor over a
specific element in a dashboard, such as a data point, bar, or line.

Key Performance Indicators (KPIs)

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 124
 Numerical values that demonstrate how effectively an organization is achieving its key business
objectives.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 125
 Case Study: Schmickel’s Pickles
by Sophia

1. Schmickel’s Pickles
Scenario: Known for its innovative flavors and high-quality ingredients,
Schmickel’s Pickles is a local producer of boutique pickles. Victoria, the
owner of Schmickel’s Pickles, is interested in knowing more about the
sample data she collected over the past 85 weeks. Her goal in collecting
this data is to secure funding from an investor. To receive funding, she
needs to create visualizations that will be presented to the investor.

Data: Use the Schmickel’s Pickles Excel workbook to help you with this
case. Schmickel's [Link]

A breakdown of each column is as follows:

Year The year in which the data was collected.

Month The month in which data was collected.

Quarter The financial quarter in which the data was collected.

Week The week that the data was collected in chronological order.

Sales (Units) How many jars of pickles were sold per week.

Revenue ($) The overall revenue earned from sales that week.

Production ($) The cost of ingredients, materials, and overhead for the week.

Production Volume How many jars of pickles were produced each week.

Marketing Spend ($) The amount of money spent on marketing for the week.

Employee Hours The number of hours employees worked during the week.

Payroll ($) The amount of total payroll expense paid to employees.

Profit ($) The amount earned by Schmickel’s Pickles, after paying all expenses.

Increase/Decrease The percentage of increase or decrease in profit, based on the prior week’s profit.
Profit

Customer Satisfaction The weekly customer satisfaction score for Schmickel’s Pickles, on a scale of 1–10, 10
being extremely satisfied and 1 being not satisfied at all.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 126
 Net Promoter Score Defines a customer as a promoter of your business, neutral, or a detractor, based on
satisfaction score.

Competitor’s Sales The jars of pickles sold each week by Schmickel’s rival brand, Pierson’s Pickles.
(Units)

How to Complete the Case Study

You will be presented with guiding questions from Victoria, the owner of Schmickel’s Pickles. These questions
are the types of questions you will field as a data analyst. For each guiding question complete both parts:

1. Answer/Justification:

Write the correct answer.

Justify your answer by explaining the calculations you used to determine it. Include the results of these
calculations.

2. Analysis:

Analyze the issue by addressing the guiding question.

Provide data-informed recommendations based on your analysis.

EXAMPLE
Example Guiding Question: What is the trend in my weekly production volume over time?

Answer/Justification: A line chart should be made to track data over a period of time. Highlight the
production volume column, then apply a line chart. Add the titles to both the x-axis and the y-axis, and give
the chart the name “Production Volume.” Your chart should look similar to this:

Analysis: The units of production repeat every few weeks, ranging from the low 340s to the high 360s.
Since the Production Volume does not vary much from week to week, it’s easier to plan for future

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 127
 production due to consistency. This also shows investors that there is not a lot of variation in production
volume.

Guiding Questions from Victoria

Question 1: Victoria has tasked you with identifying overall trends in weekly sales volume over the past 85
weeks. She wants to understand the growth and stability of Schmickel’s Pickles to showcase the business’s
consistency to potential investors.

Objective: Analyze the sales data to identify patterns, trends, and any anomalies in weekly sales over the 85-
week period. Provide insights based on your analysis.

Think About:

1. How have weekly sales volumes changed over the 85 weeks? Are there identifiable growth trends?
2. What seasonal or cyclical patterns can be observed in weekly sales?
3. Are there any outliers in the data that need explanation?

Considerations:

Highlight periods of significant growth or decline and hypothesize potential causes (e.g., marketing efforts
or market conditions).
Emphasize the implications of stable sales trends for investor confidence.

Visualization: Consider using a visualization that effectively demonstrates trends over time. Ensure the choice
of visualization highlights overall patterns while allowing you to annotate notable peaks or dips to aid in
interpretation.

Step-by-Step Guidance for Question 1 +

Step 1: Create the Line Chart

As stated in the example, a line chart is useful for showing changes in data over time, making it easier to
identify trends and patterns. To create a line chart in Excel to visualize how production volume changes
over time, follow these instructions:

1. Open the Schmickel’s Pickles dataset in Excel.

2. Highlight the column Production Volume.
3. Go to the “Insert” tab and choose “Line Chart.”
4. Add titles to the chart:
a. Chart Title: Weekly Production Volume
b. X-axis: Week
c. Y-axis: Production Volume

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 128
 Step 2: Analyze the Line Chart

Victoria’s investors are particularly interested in understanding revenue tendencies. They have requested a
visual representation that highlights tendencies in revenue per quarter over the 85-week period.

Objective: Examine weekly revenue tendencies to provide insights into revenue trends.

Think About:
1. What are the quarterly tendencies in revenue?
2. How do these tendencies align or differ across the observed quarters?
3. What implications do these trends have for Schmickel’s operational profitability?

Considerations:
Summarize the Revenue ($) per year and quarter.
Is the data set complete?
Identify whether there are patterns that suggest inconsistent revenue patterns.
Highlight weeks with significant deviations and provide potential explanations for these discrepancies.

Visualization: Consider this data is time series data and visualize as appropriate. This will allow investors to
see the trends in revenues while ensuring clarity and readability.

Step 3: Summarize Findings and Make Recommendations

Summary:
The weekly production volume of Schmickel’s Pickles has remained consistent over the observed
period, with minor fluctuations.
A repeating 4-5 week cycle suggests operational stability, which is a positive indicator for investors.
Two anomalies (Weeks 35 and 68) show temporary production declines, potentially due to operational
or external factors. Addressing these disruptions could further improve consistency.

Recommendation:
Victoria can present the line chart to investors, emphasizing the stability and predictability of
production volume.
Additionally, she can investigate the causes of anomalies and outline strategies to prevent similar
occurrences in the future.

Question 2: Victoria’s investors are particularly interested in understanding revenue tendencies. They have
requested a visual representation that highlights tendencies in revenue per quarter over the 85-week period.

Objective: Examine weekly revenue tendencies to provide insights into revenue trends.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 129
 Think About:

1. What are the quarterly tendencies in revenue?

2. How do these tendencies align or differ across the observed quarters?
3. What implications do these trends have for Schmickel’s operational profitability?

Considerations:

Summarize the Revenue ($) per year and quarter.

Is the data set complete?
Identify whether there are patterns that suggest inconsistent revenue patterns.
Highlight weeks with significant deviations and provide potential explanations for these discrepancies.

Visualization: Consider this data is time series data and visualize as appropriate. This will allow investors to see
the trends in revenues while ensuring clarity and readability.

Step-by-Step Guidance for Question 2 +

Step 1: Create the Line Chart

A line chart is useful for showing changes in data over time, making it easier to identify trends and patterns.
To create a line chart in Excel to visualize how revenue changes over time, follow these instructions:

1. Open the Schmickel’s Pickles dataset in Excel.

2. Copy and paste the columns Year, Quarter, and Revenue ($) into a new worksheet.
3. Create the PivotTable.
a. Highlight the three columns.
b. Go to the “Insert” tab and choose “PivotTable.”
c. Choose “From Table/Range.”
d. In the area that asks you to “choose where you want the PivotTable report to be placed,” choose
“Existing Worksheet.”
e. Place your cursor in the “Location” box, then click in an open cell in your spreadsheet. Choose OK.
f. In the “PivotTable Fields” window, click and drag Year and Quarter to “Rows” and move Revenue ($)
to Values.
4. Create the PivotChart.
a. Highlight the PivotTable you just created.
b. Go to the “Insert” tab and choose “Line Chart.”
c. Add titles to the chart:
i. Chart Title: Quarterly Revenue
ii. X-axis: Year and Quarter

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 130
 iii. Y-axis: Revenue ($)

Step 2: Analyze the Line Chart

Once the chart is created, observe the overall trends, patterns, and anomalies.

Answers to the Guiding Questions:

What are the quarterly tendencies in revenue?
Other quarters exhibit consistent revenue without significant variability or outliers, making revenue
trends predictable.
Q3 of 2022 and Q2 of 2024 show a lag in revenue due to missing weekly data.

How do these tendencies align or differ across the observed quarters?

The overall trend is one of stability and predictability in revenue, which aligns across most quarters.
The missing data in Q3 of 2022 and Q2 of 2024 creates inconsistencies that break the otherwise
steady revenue pattern. These discrepancies are not indicative of operational changes but rather data
gaps.
Importantly, there is no evidence of revenue growth over time, which could be a concern for investors
looking for upward momentum in financial performance.

What implications do these trends have for Schmickel’s Pickles's operational profitability?
The consistency of revenue suggests operational stability and a reliable customer base, which are
attractive to investors seeking steady returns.
Predictable revenue streams make it easier for Victoria to plan and allocate resources efficiently,
reducing risks associated with unexpected fluctuations.
The lack of revenue growth may signal stagnation in Schmickel’s Pickles market or operational
strategies. Investors might interpret this as a sign that the company is not scaling or capturing new
market opportunities.
The missing data in two quarters could raise questions about data integrity or operational oversight,
which should be addressed transparently to maintain investor confidence.

Step 3: Summarize Findings

Victoria’s operational profitability benefits from its consistent revenue streams, which indicate stable
performance and low volatility. However, the lack of growth is a potential red flag for investors who
prioritize expansion.

Question 3: Victoria needs to justify Schmickel’s marketing expenditures to investors by demonstrating their
alignment with business performance.

Objective: Analyze monthly marketing spend in 2023 to identify spending trends and their alignment with
strategic goals.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 131
 Think About:

1. What trends can be observed in monthly marketing expenditures for 2023?

2. Are there months with unusually high or low marketing spend? What caused these deviations?
3. How might the observed trends reflect Schmickel’s strategic focus or adjustments?

Considerations:

Discuss whether higher spending periods align with increased sales or revenue.
Highlight opportunities for reallocating marketing resources for better impact.

Visualization: Think about a visualization that summarizes categorical data by magnitude. The goal is to present
monthly spending in a way that makes deviations clear and allows for comparisons between months.

Step-by-Step Guidance for Question 3 +

Step 1: Create the Bar Chart

As stated in the problem, you should create a visualization that summarizes categorical data by magnitude.
A bar chart is an ideal way to show the relative size of categories. To create a bar chart in Excel to visualize
the spending in different marketing categories, follow these instructions:

1. Open the Schmickel’s Pickles dataset in Excel.

2. Apply a filter to the Year column and filter for the year 2023.
3. Copy and paste the Month and Marketing Spend ($) columns into a new worksheet. Be sure to paste
values.
4. Create the PivotTable.
a. Highlight the two columns.
b. Go to the “Insert” tab and choose “PivotTable.”
c. Choose “From Table/Range”
d. In the area that asks you to “choose where you want the PivotTable report to be placed,” choose
“Existing Worksheet.”
e. Place your cursor in the “Location” box, then click in an open cell in your spreadsheet. Choose OK.
f. In the “PivotTable Fields” window, click and drag Month to “Rows” and move Marketing Spend ($) to
“Values.”
5. Create the PivotChart.
a. Highlight the PivotTable you just created.
b. Go to the “Insert” tab and choose “Clustered Column.”
c. Add titles to the chart:
i. Chart Title: 2023 Monthly Marketing Spend

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 132
 ii. X-axis: Month
iii. Y-axis: Marketing Spend ($)

Step 2: Analyze the Bar Chart

Once the chart is created, observe the overall trends, patterns, and anomalies.

Answers to Guiding Questions:

What trends can be observed in monthly marketing expenditures for 2023?
Marketing expenditures in 2023 were remarkably consistent, with spending ranging from $2,050 in
August to $2,200 in February.
This narrow range suggests that Schmickel’s maintained a steady investment in marketing throughout
the year, without significant fluctuations in budget allocation.

Are there months with unusually high or low marketing spend? What caused these deviations?
February had the highest marketing spend at $2,200, which may reflect a strategic push for campaigns
tied to seasonal sales opportunities, events, or promotions early in the year.
August had the lowest spend at $2,050, which could indicate a seasonal dip in marketing activities or a
deliberate reduction in spending during a slower sales period.
Without additional data on the types of marketing activities and their timing, the specific causes for
these deviations remain speculative.

How might the observed trends reflect Victoria’s strategic focus or adjustments?
The overall consistency in marketing spend suggests Victoria’s focus is on maintaining a steady
presence in its market rather than experimenting with large fluctuations or aggressive growth
strategies.
The slight variations in February and August may indicate targeted adjustments based on seasonality
or campaign-specific goals, reflecting a moderate degree of strategic planning.
There is no evidence from the data to suggest Victoria is diversifying its marketing approaches or
optimizing spending across different channels.
Conducting regression analyses on how sales are influenced by different types of marketing will help
Victoria determine the efficacy of the different marketing channels.

Step 3: Summarize Findings and Make Recommendations

Summary: Marketing expenditures in 2023 were consistent, reflecting a stable but conservative approach.
February and August represent minor deviations in spending, likely tied to seasonal or campaign-specific
activities. There is no current indication that the marketing budget is actively driving sales growth or being
evaluated for efficiency across different channels.

Recommendations:
Victoria should conduct further analysis to maximize the return on marketing investment:

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 133
 Run a Linear Regression Analysis: Evaluate the relationship between overall marketing spend and
sales to determine if increased spending correlates with higher revenue.
Collect Detailed Marketing Data: Break down marketing spend by channel (e.g., digital ads, social
media, print) and analyze the effectiveness of each channel using linear regression models.
Adjust Budget Allocation: Use insights from these analyses to focus spending on high-performing
channels and reduce investment in less effective ones.
Seasonal Planning: Identify whether sales trends align with the higher and lower spending months and
refine the marketing strategy accordingly.

Question 4: Investors will evaluate Schmickel’s ability to control production costs by examining quarterly gross
margin in 2023.

Objective: Analyze the gross margin (Revenue ($) – Production Cost ($)) contributed each quarter in 2023 to
highlight cost controls.

Think About:

1. What dollar amount of gross margin was generated in each quarter of 2023?
2. Are there significant variations in gross margin across quarters?
3. How do these variations inform predictions about gross margin in future years?

Considerations:

Emphasize the implications of high-performing quarters for investor confidence.

Address any challenges revealed by low-performing quarters.

Visualization: Consider how to best visualize proportional data. Choose a format that clearly communicates the
share of gross margin for each quarter and facilitates comparison.

Step-by-Step Guidance for Question 4 +

Step 1: Create the Bar Chart

As stated in the problem, you should consider how to best visualize proportional data to clearly
communicate the share of gross margin for each quarter and facilitate comparison. The best way to
visualize this comparison is to create a bar chart. To create a bar chart in Excel to visualize the quarterly
gross margin for 2023, follow these instructions:

1. Open the Schmickel’s Pickles dataset in Excel.

2. Apply a filter to the Year column and filter for the year 2023.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 134
 3. Copy and paste the Month, Revenue ($), Production Cost ($), and Quarter columns into a new
worksheet. Be sure to paste values.
4. In the new worksheet, create a column called Gross Margin and calculate the gross margin by
subtracting production cost from revenue for each row of data.
5. Create the PivotTable.
a. Highlight Quarter and Gross Margin columns.
b. Go to the “Insert” tab and choose “PivotTable.”
c. Choose “From Table/Range.”
d. In the area that asks you to “choose where you want the PivotTable report to be placed,” choose
“Existing Worksheet.”
e. Place your cursor in the “Location” box, then click in an open cell in your spreadsheet. Choose OK.
f. In the “PivotTable Fields” window, click and drag Quarter to “Rows” and move Gross Margin to
“Values.”
6. Create the PivotChart.
a. Highlight the PivotTable you just created.
b. Go to the “Insert” tab and choose “Clustered Column.”
c. Add titles to the chart:
i. Chart Title: 2023 Gross Margin per Quarter
ii. X-axis: 2023 Quarter
iii. Y-axis: Gross Margin ($)

Step 2: Analyze the Bar Chart

Once the chart is created, observe the overall trends, patterns, and anomalies.

Answers to Guiding Questions:

What dollar amount of gross margin was generated in each quarter of 2023?
The analysis indicates that gross margins for each quarter in 2023 were consistent, with only a $125
variation between the highest and lowest quarters.
The narrow range confirms strong cost control and stable operations throughout the year.

Are there significant variations in gross margin across quarters?

There are no significant variations in gross margin across quarters.
The minimal $125 difference between the highest and lowest gross margins reflects tight control over
costs and consistent revenue streams.
This stability minimizes financial surprises and aids in precise budgeting for supplies and operations.

How do these variations inform predictions about gross margin in future years?
The consistency observed in 2023 suggests that Victoria is likely to maintain a stable gross margin in
future years, assuming current operational practices and market conditions remain unchanged.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 135
 This pattern may also indicate limited opportunities for growth unless additional strategies are
implemented to increase revenue while keeping costs steady.

Step 3: Summarize Findings and Make Recommendations

Summary:
Gross margins are consistent, with only a $125 difference across quarters in 2023.
No significant quarterly variations suggest reliable cost control and stable revenue.
Future predictions point to continued stability in gross margins, but the lack of growth could concern
investors seeking upward trends.

Recommendations:
Focus on Revenue Growth: While cost control is commendable, Victoria should prioritize strategies to
increase revenue, such as expanding product lines, entering new markets, or enhancing marketing
efforts.
Improve Gross Margin: Aim for a growth trajectory where revenue increases while maintaining current
cost levels. This would create a more appealing financial outlook for investors.
Communicate Consistency as a Strength: Highlight stable gross margins as a sign of operational
efficiency and reliability to reassure current stakeholders.
Track Key Metrics: Implement more granular tracking of gross margin contributors to identify areas for
improvement or investment.

Question 5: Victoria wants to evaluate how marketing investments drive sales to optimize her budget
allocation.

Objective: Assess the relationship between marketing expenditures and sales volumes.

Think About:

1. Is there a positive correlation between marketing spend and sales units?

2. Based on the analysis, is there a possibility to optimize marketing budget allocations for better results?

Considerations:

Discuss how marketing spend impacts sales growth and customer engagement.
Recommend strategies for reallocating marketing funds based on performance.

Visualization: Select a method that illustrates relationships between two continuous variables.

Step-by-Step Guidance for Question 5 +

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 136
 Step 1: Create the Scatter Plot

As stated in the problem, you should select a method that illustrates relationships between two continuous
variables. The best way to visualize this comparison is to create a scatter plot. To create a scatter plot that
assesses the relationship between marketing expenditures and sales volumes, follow these instructions:

1. Open the Schmickel’s Pickles dataset in Excel.

2. Apply a filter to the Year column and filter for the year 2023.
3. Copy and paste the Months, Profit ($) columns into a new worksheet in columns A and B, respectively.
Be sure to paste values.
4. Create the scatter plot.
a. Highlight columns A, Sales Units, and B, Marketing Spend ($).
b. Go to the “Insert” tab and choose “Scatter with only Markers.”
c. Add titles to the chart:
i. Chart Title: Units Sold in Relation to Marketing Spend
ii. X-axis: Marketing Spend
iii. Y-axis: Units Sold

Step 2: Analyze the Scatter Plot

Once the scatter plot is created, observe the overall trends, patterns, and anomalies.

Answering the Guiding Questions:

Is there a positive correlation between marketing spend and sales units?
The scatter plot indicates a positive linear relationship between marketing spend and units sold.
The minimal spread of data points suggests a medium to strong correlation. This means that as
marketing spend increases, units sold tend to increase as well.

Based on the analysis, is there a possibility to optimize marketing budget allocations for better results?
The observed relationship suggests potential for optimizing the marketing budget.
By calculating the correlation coefficient and running a regression analysis, Victoria can determine the
strength of the relationship and evaluate whether increases in marketing spend yield proportional
returns.
If the regression shows a high R-squared value, targeted budget adjustments can focus on high-impact
areas to maximize sales growth.

Step 3: Summarize Findings and Make Recommendations

Summary:
A positive linear relationship exists between marketing spend and sales units, with a medium to strong
correlation suggested by the scatter plot.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 137
 The strength of this relationship can be confirmed by further statistical analysis, such as regression, to
assess the efficacy of current spending.
Optimizing marketing budget allocations is likely feasible and may lead to better results by focusing on
areas with the greatest return on investment (ROI).

Recommendations:
Calculate the Correlation Coefficient: Confirm the strength of the relationship between marketing
spend and units sold using statistical methods.
Run Regression Analysis: Conduct a regression analysis to estimate the impact of marketing spend on
sales and evaluate the R-squared value to determine how well marketing spend predicts sales units.
Segment and Optimize Budget: Break down marketing spend by type (e.g., digital ads, social media,
print) and analyze the ROI of each segment. Shift the budget toward channels that yield the highest
sales growth.
Test Incremental Increases: Experiment with incremental increases in marketing spend in controlled
campaigns to observe sales responses in real-time.
Monitor Results: Continuously track sales and marketing performance to refine strategies and ensure
optimal budget utilization.

Question 6: Victoria needs to highlight Schmickel’s Pickles profitability trends to demonstrate financial health to
investors.

Objective: Analyze monthly profit distribution for 2023 to identify trends and outliers that impact overall
financial performance.

Think About:

1. What is the shape of this distribution and what does that say about monthly profits?
2. Are there months with significant deviations from the average?
3. How do these profitability trends reflect Schmickel’s financial management?

Considerations:

Emphasize the stability of monthly profits as a positive indicator for investors.

Discuss opportunities for addressing months with lower-than-average profits.

Visualization: Use a visualization that represents distributions. These methods are effective for showing the
spread and variability of monthly profits, as well as highlighting any outliers.

Step-by-Step Guidance for Question 6 +

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 138
 Step 1: Create Histogram

As stated in the problem, you should use a visualization that represents distributions. Since histograms are
effective for showing the spread and variability of monthly profits, as well as highlighting any outliers, this is
the best way to visualize this data. To create a histogram that shows the spread and variability of monthly
profits earned in 2023, follow these instructions:

1. Open the Schmickel’s Pickles dataset in Excel.

2. Apply a filter to the Year column and filter for the year 2023.
3. Copy and paste the Months and Profit ($) columns into a new worksheet. Be sure to paste values.
4. Create the PivotTable.
a. Highlight Month and Profit ($) columns.
b. Go to the Insert tab and choose PivotTable.
c. Choose “From Table/Range.”
d. In the area that asks you to “choose where you want the PivotTable report to be placed,” choose
“Existing Worksheet.”
e. Place your cursor in the “Location” box, then click in an open cell in your spreadsheet. Choose OK.
f. In the “PivotTable Fields” window, click and drag Month to “Rows” and move Profit ($) to “Values.”
5. Create the Histogram.
a. Determine the minimum and maximum monthly profit values from the PivotTable.
i. Use the MIN() function to determine the minimum profit value. It is $3,265.
ii. Use the MAX() function to determine the maximum profit value. It is $3,334.
b. Calculate the number of bins by finding the square root of n.
i. In this case, n is the number of months or 12.
ii. Use the SQRT(12) function to calculate 3.464.
iii. Round this up to the nearest whole number to determine that you will have 4 bins in your
histogram.
c. Calculate Bin Width.
i. Bin width is calculated using this equation: (MAX-MIN)/Number of Bins.
ii.
iii. Round 17.25 up to 18. By rounding up, we are ensuring that 4 bins with a width of 18 will cover the
range of values in the data set.
d. Construct a frequency distribution.
i. Create a column named Bin Ranges that lists the lower and upper values of the bin intervals.
Since you know the minimum value is 3265, you can use 3265 as the lower limit of the first bin.
ii. The first bin range is 3265 to 3283 (3265 + 18).
iii. For the second bin range, add one to the upper limit of the first bin, which equals 3284 (this is the
lower limit of the second bin). Then add 18 to get the upper limit of 3302.
iv. Repeat this until you have four bins.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 139
 v. Create another column in the cell right beside Bin Ranges named Frequency.
vi. Leave this column blank for now.
vii. Excel uses the upper limits of each bin to count the number of observations in a particular bin, so
you need a column that contains the upper limits of each bin. Name this column Bin Upper Limit.
viii. Populate this column with the upper limit from each bin.
6. Select the Histogram option in the XL Miner Analysis Toolpak.
a. Put your cursor in the Input Range box and select the Profit data from the PivotTable.
b. Repeat the same process for Bin Range by first putting your cursor in the Bin Range box and then
selecting the cells that contain the upper bin limits.
c. Select the checkbox for Chart Output.
d. Select the Labels checkbox so that a label will be added to the x-axis.
e. Copy the values in the Frequency column and the blank Frequency column you created earlier so
that you will have a complete frequency distribution.
7. Format the histogram.
a. If the histogram bins do not touch:
i. Select the histogram.
ii. Go to the Chart tab and select Format.
iii. From the Chart menu, click on “Horizontal Axis” and move the slider for “Gap Width” to 0.
8. Add titles to the chart:
a. Chart Title: 2023 Monthly Profit Distribution
b. X-axis: Monthly Profit Earned
c. Y-axis: Frequency of Profit Earned

Step 2: Analyze the Histogram

Once the histogram is created, observe the overall trends, patterns, and anomalies.

Answering the Guiding Questions:

What is the shape of this distribution and what does that say about monthly profits?
The distribution of monthly profits is skewed to the right.
Most months have profits clustered toward the lower end of the range ($3,265 to $3,300), with fewer
months showing higher profits (closer to $3,334).
This indicates that Victoria’s profits are consistent but slightly more likely to be on the lower end of the
narrow profit range.

Are there months with significant deviations from the average?

The data indicates no significant deviations, as the range of $69 is minimal.
This small variation suggests consistent profit levels across the year.
Although the histogram shows a right-skewed distribution, the minimal range and only 12 data points
make it unlikely that any observed skewness represents meaningful deviations.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 140
 How do these profitability trends reflect Schmickel’s financial management?
The narrow range of monthly profits demonstrates Victoria’s ability to maintain consistent financial
performance throughout the year, a sign of strong cost control and reliable revenue streams.
The skewed distribution and lack of variation may reflect limited growth potential or the absence of
significant profit increases. This could signal a need for strategies to expand profit margins while
maintaining stability.

Step 3: Summarize Findings and Make Recommendations

Summary:
The monthly profit range is $69, with an average of approximately $3,300.
The distribution of monthly profits is right-skewed, reflecting a concentration of profits on the lower
end of the range.
There are no significant deviations from the average, indicating strong financial stability but limited
growth potential.

Recommendations:
Investigate High-Profit Months: Analyze the higher-profit months to identify the factors contributing to
increased performance and explore replicating those conditions in other months.
Focus on Growth: Develop strategies to push monthly profits higher, reducing the concentration of
lower-profit months while expanding the range upward.
Evaluate Skewness Over Time: Continuously track and assess profit distributions to identify patterns or
opportunities for growth and improvement.
Leverage Stability as Strength: Communicate consistent profits as a foundation of operational strength
to investors while working on growth initiatives.

Question 7: Victoria’s investors are curious about the distribution of customer sentiment as reflected in Net
Promoter Score (NPS) designations. To strengthen her pitch, Victoria wants to visually display the frequencies of
customer designations—promoters, passives, and detractors.

Objective: Identify the best way to visualize the frequencies of NPS designations to demonstrate customer
loyalty and areas for improvement. Analyze this data.

Think About:

1. What are the frequencies of promoters, passives, and detractors in the Schmickel’s Pickles customer base?
2. How does the distribution of these categories reflect overall customer satisfaction?
3. What actionable insights can be derived from the distribution of NPS designations?

Considerations:

Emphasize the proportion of promoters as a sign of strong brand loyalty.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 141
 Identify areas where improvements are needed, particularly among passives and detractors.

Visualization: Think about a visualization that clearly displays comparisons between categorical variables. The
goal is to make the relative proportions of promoters, passives, and detractors easy to interpret and compare.

Step-by-Step Guidance for Question 7 +

Step 1: Create the Bar Chart

As stated in the problem, you should use a visualization that clearly displays comparisons between
categorical variables. The goal is to make the relative proportions of promoters, passives, and detractors
easy to interpret and compare. The best way to visualize this comparison is to create a bar chart. To create
a bar chart in Excel to visualize Net Promoter Score categories, follow these instructions:

1. Open the Schmickel’s Pickles dataset in Excel.

2. Copy and paste the Net Promoter Designation column into a new worksheet.
3. Create the PivotTable.
a. Highlight the Net Promoter Designation column.
b. Go to the “Insert” tab and choose “PivotTable.”
c. Choose “From Table/Range.”
d. In the area that asks you to “choose where you want the PivotTable report to be placed,” choose
“Existing Worksheet.”
e. Place your cursor in the “Location” box, then click in an open cell in your spreadsheet. Choose OK.
f. In the “PivotTable Fields” window, click and drag Net Promoter Designation to “Rows” and move Net
Promoter Designation to “Values.” This will create a frequency table of Net Promoter Designations.
4. Create the PivotChart.
a. Highlight the PivotTable you just created.
b. Go to the “Insert” tab and choose “Clustered Column.”
c. Add titles to the chart:
i. Chart Title: Net Promoter Scores by Designation
ii. X-axis: Net Promoter Designation
iii. Y-axis: Frequency of Customer Response

Step 2: Analyze the Bar Chart

Once the chart is created, observe the overall trends, patterns, and anomalies.

Answering Guiding Questions:

What are the frequencies of promoters, passives, and detractors in Schmickel’s Pickles' customer base?
Promoters: 9 customers

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 142
 Neutral/Passives: 69 customers
Detractors: 7 customers

How does the distribution of these categories reflect overall customer satisfaction?
With 69 out of 85 total customers (81%) feeling neutral about their experience, the data suggests most
customers are indifferent rather than enthusiastic about Schmickel’s Pickles.
Only 9 customers (11%) are promoters, indicating a small proportion of customers are actively
recommending the brand.
Although the number of detractors is relatively low at 7 (8%), it still represents a potential risk if not
addressed.
The distribution shows a lack of strong positive sentiment, highlighting a missed opportunity to convert
neutral customers into promoters and build brand loyalty.

What actionable insights can be derived from the distribution of NPS designations?
Neutral customers represent a significant opportunity for improvement. Understanding their
expectations and enhancing their experience could shift them to promoters.
The low number of promoters indicates a need for targeted engagement strategies, such as loyalty
programs or personalized experiences, to encourage advocacy.
Understanding the pain points of detractors can prevent churn and improve overall satisfaction.

Step 3: Summarize Findings and Make Recommendations

Summary:
The customer base consists of 9 promoters (11%), 69 neutral customers (81%), and 7 detractors (8%).
The predominance of neutral customers and limited promoters indicates a lack of strong customer
satisfaction or loyalty.
The distribution suggests an urgent need for strategies to engage neutral customers and nurture
promoters, while addressing detractor concerns.

Recommendations:
Survey Neutral Customers: Conduct surveys or focus groups to understand why customers feel neutral
and identify areas for improvement in product quality, customer service, or marketing.
Engage Promoters: Create targeted initiatives, such as referral programs, exclusive deals, or
personalized thank-you messages, to strengthen relationships and amplify advocacy.
Address Detractor Concerns: Analyze feedback from detractors to identify pain points and implement
solutions to resolve issues.
Enhance Customer Experience: Develop strategies to improve overall customer satisfaction, such as
faster response times, improved packaging, or additional product offerings.
Monitor NPS Trends: Track NPS designations over time to measure the effectiveness of implemented
changes and adapt strategies accordingly.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 143
 Question 8: Victoria wants statistical evidence to support claims about the relationship between customer
sentiment and quarterly performance for all 85 weeks’ worth of data.

Objective: Conduct a test to evaluate if there is a significant association between Net Promoter Scores and
quarters.

Think About:

1. Is there a statistically significant relationship between NPS and quarters?

2. What do these findings indicate about quarterly trends in customer satisfaction?
3. How can Schmickel’s Pickles leverage this insight to improve customer loyalty?

Considerations:

Discuss how statistically significant findings can guide strategic decisions.

Highlight potential opportunities for targeting specific periods to enhance customer experience.

Visualization: Use a visualization that shows actual data and expected data pertaining to customer sentiment
and quarter. Visual aids such as segment-specific breakdowns can help clarify associations.

Step-by-Step Guidance for Question 8 +

Step 1: Create the Frequency Table

As stated in the problem, you should use a visualization that shows actual data and expected data
pertaining to customer sentiment and quarter. The best way to visualize this comparison is to create two
tables, a frequency table of observed data and a table of expected values. To create these tables, follow
these instructions:

1. Open the Schmickel’s Pickles dataset in Excel.

2. Copy and paste the Quarter and Net Promoter Designation columns into a new worksheet.
3. Create a PivotTable.
a. Highlight the Quarter and Net Promoter Designation columns.
b. Go to the “Insert” tab and choose “PivotTable.”
c. Choose “From Table/Range.”
d. In the area that asks you to “choose where you want the PivotTable report to be placed,” choose
“Existing Worksheet.”
e. Place your cursor in the “Location” box, then click in an open cell in your spreadsheet. Choose OK.
f. In the “PivotTable Fields” window, click and drag Quarter to “Rows” Net Promoter Designation to
“Columns” and move Net Promoter Designation to “Values.” This will create a frequency table of Net
Promoter Designations.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 144
 g. This is your frequency table of observed values.
4. Create a second table of expected values.
a. Create a table of 5 rows and 4 columns.
i. Label the columns Quarter, Detractor, Neutral, and Promoter, respectively.
ii. Label rows 2-5, as 1, 2, 3, and 4, respectively.
b. Calculate expected values.
i. Expected values are calculated by multiplying row totals by column totals and then dividing by
the grand total. =(Row Total x Column Total)/Grand Total.
ii. For example, in the cell at the intersection of Detractor and 1, enter in the formula for the
expected calculation. =(24 x 7)/85. Numbers are shown for illustrative purposes. You will use the
cell references, rather than numbers in your calculations.
iii. Repeat this step 11 times until all the expected values have been calculated.

Step 2: Run the Chi-Square Test for Association

1. In an empty cell, calculate the p-value for chi-square using this formula: = [Link](first argument,
second argument).
2. The first argument will be the frequencies of the observed values from the PivotTable you created.
a. Be sure to only include the values, and not the totals in this table.
3. The second argument will be the calculated expected values from the expected value table you
created.

Step 3: Analyze Your Results

Once the p-value for the Chi-Square Test for Association is calculated, analyze the outcome.

Answering the Guiding Questions:

Is there a statistically significant relationship between NPS and quarters?
The chi-square test yielded a p-value of 0.94, which is greater than the standard significance level of
0.05.
There is no statistically significant relationship between Net Promoter Score (NPS) and the quarter in
which it was recorded. This means that customer satisfaction, as measured by NPS, does not vary
significantly across different quarters.

What do these findings indicate about quarterly trends in customer satisfaction?

Customer satisfaction remains consistent regardless of the quarter, suggesting that seasonal factors or
timing do not strongly influence how customers perceive their experience with Schmickel’s Pickles.
The absence of significant fluctuations in NPS implies that external factors like holidays, promotions, or
changes in operations likely have minimal impact on overall customer sentiment.
This consistency could reflect stability in customer experience delivery, but it also suggests a missed
opportunity to leverage quarterly strategies to improve satisfaction.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 145
 How can Schmickel’s Pickles leverage this insight to improve customer loyalty?
Since customer satisfaction is not influenced by seasonal trends, Victoria should focus on continuous
improvement initiatives that enhance the overall customer experience regardless of timing.
With consistent satisfaction levels, efforts should concentrate on converting neutral customers to
promoters by addressing common feedback themes and delivering exceptional value.
While quarterly trends are consistent, Victoria should evaluate NPS trends over longer periods to
identify potential shifts in customer sentiment and address them proactively.

Step 4: Summarize Findings and Make Recommendations

Summary:
There is no statistically significant relationship between NPS and the quarter in which it was recorded
(p-value = 0.94).
Customer satisfaction does not show meaningful variation across quarters, indicating consistent
performance year-round.
Quarterly factors do not appear to influence customer sentiment, highlighting the importance of
addressing underlying issues rather than focusing on seasonal adjustments.

Recommendations:
Enhance Overall Customer Experience: Implement initiatives such as loyalty programs, personalized
customer interactions, or improved product quality to elevate satisfaction consistently.
Segment and Analyze Feedback: Identify common themes in customer feedback to prioritize changes
that can have the most significant impact on satisfaction and loyalty.
Experiment with Seasonal Promotions: Although the current data does not show significant quarterly
variation, introducing targeted promotions or campaigns during key periods could drive engagement
and test for new opportunities.
Monitor Long-Term NPS Trends: Conduct periodic reviews to track customer satisfaction over
extended timeframes and respond promptly to emerging patterns.
Develop a Proactive Strategy for Neutral Customers: Focus on converting neutral customers into
promoters by understanding and addressing their specific needs and preferences.

Answers to Victoria's Questions

Question 1

Answer/Justification: Apply a line chart using the Sales (Units) data. A line chart should be made to track data
over a period of time. Title the X and Y axes appropriately and give the chart a name. Your line chart should
look similar to the following:

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 146
 Analysis: Sales (Units) seems to have a repeating pattern over a 4-to-5 week period, which ranges from 295
units to 320 units. There is not a great deal of variability or any outliers in units sold from week-to-week, so
predicting future sales is formulaic. This also shows investors that there is not a lot of variation in units sold,
meaning consistent sales.

Question 2

Answer/Justification: Use the variables Year, Quarter, and Revenue ($) to create a PivotTable to separate each
quarter by year, then apply a line PivotChart to show the trend in revenue over the 8 quarters. Your PivotTable
should look like this:

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 147
 A line chart was created because this data is being tracked over a period of time. Title the X and Y axes
appropriately and give the chart a name.

Your line chart should look similar to the following:

Analysis: Quarterly revenue over time seems to have a lag in revenue in quarter 3 of 2022 and in quarter 2 of
2024. Upon further investigation, these two quarters have missing data (weeks are not present in the data). This
information should be disclosed to the investors. Otherwise, revenue is very consistent without a great deal of
variability or outliers, so it is rather easy to predict revenue trends. This analysis also shows investors that there
is not a lot of variation in revenue, leading to consistent revenue streams. The data shows no growth, which
may be concerning to the investors.

Question 3

Answer/Justification: Create a bar chart based on a PivotTable of marketing spend per month in 2023. First,
create a PivotTable using Month and Marketing Spend ($) to summarize the amount of marketing that is spent
per month. Your PivotTable should look like this:

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 148
 Then, create a PivotChart by inserting a Clustered Column Chart. This bar chart should look similar to this:

Analysis: Your marketing spend was consistent in 2023, ranging from a low of $2,050 in August to a high of
$2,200 in February. You could run a simple linear regression analysis on how sales move, as more money is
spent on marketing. There may also be value in collecting data on what kinds of marketing you are spending
money on and potentially running simple linear regressions on each type and sales to determine if the money
you spend on marketing is increasing sales and to what extent.

Question 4

Answer/Justification: The data used for this question were Quarter, Year, Production Cost ($), and Revenue ($).
Calculate the Gross Margin by subtracting the production cost from the revenue. Then, create the PivotTable
you see below that summarizes gross margin for each quarter of 2023.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 149
 Using the PivotChart functionality in Excel, based on the PivotTable, create a Clustered Column PivotChart. The
PivotChart should look like this:

Analysis: Your gross margin is very consistent throughout the year, with only a $125 difference between the
quarter with the highest gross margin to the quarter with the lowest gross margin. So, there are no major
fluctuations up or down, given this data. This consistency shows you are able to control your costs throughout
the year and maintain a steady gross margin. This pattern also helps with budgeting for supplies. Even though
consistency in controlling costs is a good thing, the data also indicates that revenue is consistent. An investor
may want to see a gross margin pattern that has a growth trajectory (meaning revenue is increasing while costs
remain unchanged), rather than one that is flat.

Question 5

Answer/Justification: Create a scatter plot using Marketing Spend ($) and Sales (Units) as the variables. The
scatter plot shows the relative movement of sales due to marketing dollars spent. Your scatter plot should look
like this:

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 150
 Analysis: This scatter plot shows that Marketing Spend ($) and Units Sold have a positive linear relationship.
The linear relationship of the plotted points indicates very little spread, suggesting there is a medium to strong
correlation. The next steps would be calculating the correlation coefficient and/or running a regression analysis
to help show the strength of this relationship. If the relationship is strong, then using Marketing Spend ($) to
predict Units Sold is likely.

Question 6

Answer/Justification: Use the data points Year, Month, and Profit ($) to create a histogram to show the
distribution of profits for each month for 2023. First, filter the data for the year 2023, then create a PivotTable
that gives the sum of each month’s profit as seen below:

Using the data from the PivotTable, find the minimum value and maximum value of this data. Calculate the
number of bins needed by taking the square root of 12, which rounds up is 4. Then, determine the bin width by
subtracting the minimum value from the maximum value and then dividing the difference by the number of bins.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 151
 Finally, create a table in Excel to calculate the bin range, then use the histogram tool in the XLMiner Analysis
ToolPak to generate the following histogram.

Analysis: Based on this histogram, the distribution of profit per month is skewed to the right, meaning more
months have lower profit than higher profit. It is important to note that even though the data is skewed, there
are only 12 data points, and the range of values falls between a low of $3,265 to a high of $3,334. With such a
narrow range ($69), there are no significant deviations. Therefore, even though the distribution is skewed, one
should be cautious about drawing misleading conclusions from this data.

Question 7

Answer/Justification: The best way to show these frequencies is to create a clustered column PivotChart based
on a PivotTable frequency distribution of Net Promoter Designations. Your PivotTable and clustered column
PivotChart should look similar to these:

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 152
 Analysis: Based on this chart, the majority of your customers feel neutral about their customer experience. This
is alarming and should lead to further investigation as to what can be done to help make neutral customers
promoters. The chart identifies a problem, but without further information, it would be premature to make any
changes.

Question 8

Answer/Justification: You will be working with the variables Quarter and Net Promoter Designation for this
problem. Since there are two categorical variables, you should run a chi-square test of association to test for a
relationship between the variables. First, create a PivotTable as a contingency table using the Quarter and Net
Promoter Designation variables. This will serve as your observed values. Next, calculate the expected
frequencies for each cell containing a joint total in the contingency table by multiplying the corresponding row
total by the column total, then dividing by the overall total. These are your expected values. Your observed
values and expected values should look like this, respectively:

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 153
 Finally, use the =[Link](observed, expected) function to determine the p-value of .94.

Analysis: The chi-square test of association tells you whether there is a statistically significant relationship or
association between two categorical variables (Quarter and Net Promoter Designation). The null hypothesis of
this test assumes there is no association. The alternative hypothesis says that there is a significant association
between the variables. The standard to reject the null hypothesis is a p-value of less than .05. The p-value of
this test was .94. Since p is greater than the alpha of .05, you would fail to reject the null hypothesis. There is
not enough evidence to conclude that a significant association exists between Quarter and Net Promoter
Designation. The quarter in which the Net Promoter Score was recorded does not appear to be influential.

 SUMMARY

In this lesson, you were given a scenario involving Schmickel’s Pickles, a local producer known for its
innovative flavors and high-quality ingredients. Victoria, the owner, has collected data over the past 85
weeks to secure funding from an investor. You were tasked with using Excel to analyze this data and
answer eight specific questions, such as identifying trends in weekly production volume, calculating
monthly marketing expenses for 2023, and determining the best chart to display the distribution of
monthly profits for the 2023 fiscal year. The goal was to create visualizations that Victoria could present
to potential investors to support her funding request and better understand her business financials.

Source: THIS TUTORIAL WAS AUTHORED BY SOPHIA LEARNING. PLEASE SEE OUR TERMS OF USE.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 154
 Terms to Know
Aggregated Data
Summarizing a set of individual data points into a single representative value such as a total
or average.

Chi-Square Test of Association

A statistical method used to determine if an association exists between two categorical
variables.

Coefficient of Determination
Measures the proportion of variability in the response variable that can be explained using
the predictor variable.

Coefficient of Variation (cv)

A statistical measure that indicates the size of a standard deviation in relation to its mean.

Column Percentage
Percentage of each cell’s value relative to the total of its column in a contingency table.

Contingency Table
A synonym for crosstabulation.

Correlation
Statistical measure (r) that indicates the strength of the linear relationship between two
variables.

Correlation Coefficient
A synonym for correlation.

Covariance
Statistical measure that indicates how one variable varies in response to another variable.

Crosstabulation
A table that displays the frequencies or counts of observations that fall into different
categories of categorical variables.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 155
 Dashboard
A visual display of key information and data points, typically presented in a way that is easy
to read and interpret in a single view.

Data Exploration
The initial step in data analysis is to gain a better understanding of the data and prepare the
data for further examination.

Data Visualization
The graphical representation of data using visual elements such as graphs and maps.

Data-to-Ink Ratio
Ratio of ink (pixels) in a visual representation that shows the data to the graph's total
elements.

Drill-Down
The ability to explore more detailed data by clicking on a summary or high-level data point.

Explanatory Variable
A factor or input that you believe influences or explains variation in another variable.

Frequency Polygon
Type of line graph that is like a histogram but uses points connected by straight lines instead
of bars to represent the distribution of data.

Geometric Mean
A central tendency measure that is appropriate for data with a multiplicative nature.

Granular Data
Individual information about a data point that is broken down into the smallest possible unit.

Heatmap
A table that represents the magnitude of individual values within a data set using color.

Hover-Over
Refers to the interactive feature that displays additional information when a user moves their
cursor over a specific element in a dashboard, such as a data point, bar, or line.

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 156
 Key Performance Indicators (KPIs)
Numerical values that demonstrate how effectively an organization is achieving its key
business objectives.

Line Chart
A graph in which a line connects a series of data points that are plotted over a period of time.

Mean
The sum of values divided by count.

Measure of Dispersion
A synonym for measure of spread.

Measure of Spread
A value that describes the variability in a data set.

Measures of Central Tendency

The mean, median, or mode of a data set.

Median
The middle value of a data set, when the data is arranged in ascending order (smallest to
largest).

Mode
The value that appears most frequently in a data set.

Normalize
Process that transforms data to a standard scale.

Ordinary Least Squares (OLS)

A mathematical procedure that estimates the parameters in a linear model.

Overall Percentage
Percentage of observations in a specific cell relative to the total number of observations in
contingency table.

PivotChart

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 157
 A visual display of the summarized data contained in a PivotTable.

Population Mean
A mean that is calculated from a population.

Preattentive Attribute
A visual property of an element in a design or data visualization that your eyes automatically
perceive before you consciously focus on the details.

Range
A measure of spread equal to the difference between the largest and smallest value in a data
set.

Residual
The difference between the predicted value of the response and the actual value of the
response variable. Also a synonym for error.

Response Variable
Represents the outcome variable that you want to explain, predict, or understand.

Row Percentage
Percentage of each cell’s value relative to the total of its row in a contingency table.

Sample Mean
A mean that is calculated from a sample.

Scatter Plot
A chart type used to observe and visually display the relationship between two quantitative
variables.

Side-by-Side Bar Chart

Bar chart that displays multiple bars next to each other for each category of the categorical
variable being displayed.

Simple Linear Regression

A statistical model used to estimate or predict the relationship between two variables.

Skew

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 158
 The measure of asymmetry in the distribution of data.

Skewed to the Left

Data in which most of the outliers will be present on the left side of the distribution.

Skewed to the Right

Data in which most of the outliers are present on the right side of the distribution.

Stacked Bar Chart

An extension of the standard bar chart by displaying numeric values across two categorical
variables.

Standard Deviation
The square root of the variance is used to interpret variability with the same units as the
original data, making the measure interpretable.

Sums of Squares
An umbrella term to denote the three key sum of squares measures used to evaluate how
well the regression model fits the data.

Sums of Squares Error (Residual) (SSE)

Quantifies the unexplained variability between the predicted values and the actual value of
the response variable.

Sums of Squares Regression (SSR)

Quantifies the variability explained by the regression model.

Sums of Squares Total (SST)

The total variability of the response variable around the mean.

Symmetric Data
Data that is evenly distributed around a mean.

Table
A structured arrangement of data organized into rows and columns.

Variance

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 159
 Quantifies the spread of data points in a dataset from the mean.

Formulas to Know
Coefficient of Variation (cv)

Geometric Mean

Population Covariance

Population Variance

Range

Sample Covariance

Sample Variance

© 2025 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC. Page 160