Problem Set: Advanced Analysis of Mean,

Mode, and Median for Postgraduate Students

Overview
This problem set is designed to challenge postgraduate students at Ivy League business schools
to apply advanced statistical concepts—mean, mode, and median—to analyze complex datasets
in a business context. The problems require the use of Microsoft Excel for calculations, data
visualization, and interpretation, focusing on a multinational retail chain, GlobalMart, facing
strategic decisions about inventory management and customer satisfaction. The questions are
deliberately complex, incorporating skewed distributions, large datasets, and strategic trade-offs
to test analytical rigor and decision-making skills.

Students must solve each problem using Excel, provide detailed interpretations, and justify their
recommendations. The dataset and instructions are provided below, followed by a series of high-
difficulty questions requiring both quantitative analysis and qualitative insights.

Dataset Description
GlobalMart operates 200 stores across North America, Europe, and Asia, selling consumer
electronics. The company has collected data on two key metrics to optimize operations:

1. Daily Inventory Turnover Time (days): Time taken to sell and restock inventory for a
specific product category (smartphones) across 200 stores over one year (365 days).
2. Customer Satisfaction Scores (1–10): Daily scores based on customer feedback for the
same period.

Sample Data (Simplified for Illustration)

 Inventory Turnover Time (days): Dataset of 73,000 records (200 stores × 365 days),
with values ranging from 2 to 30 days.
o Example subset for one store (365 days): [3, 4, 4, 5, 5, 5, 6, 6, 7, 8, 10, 12, 15,
20, ...]
o Characteristics: Right-skewed distribution (occasional high turnover times due to
supply chain disruptions).
o Aggregate statistics (approximate): Mean = 7.8 days, Mode = 5 days, Median = 6
days.
 Customer Satisfaction Scores: Dataset of 73,000 records, ranging from 1 to 10.
o Example subset for one store: [7, 8, 8, 8, 9, 9, 9, 10, 10, 6, 5, ...]
o Characteristics: Slightly left-skewed distribution (most scores are high, with rare
low scores due to service issues).
o Aggregate statistics (approximate): Mean = 8.2, Mode = 9, Median = 8.5.
Excel Data File

Students are provided with an Excel file (GlobalMart_Data.xlsx) containing two sheets:

 Sheet 1: Inventory Turnover: Columns include Store ID, Date, Turnover Time (days).
 Sheet 2: Customer Satisfaction: Columns include Store ID, Date, Satisfaction Score.

Note: For this problem set, assume students have access to the full dataset. If unavailable, they
can simulate a representative sample using the provided characteristics (e.g., right-skewed
turnover times, left-skewed satisfaction scores).

Problem Set Questions

Students must use Excel to perform calculations, create visualizations, and provide detailed
interpretations. Each question requires a combination of statistical analysis, Excel proficiency,
and strategic reasoning.

Question 1: Descriptive Statistics and Distribution Analysis

 Task:
o Using Excel, calculate the mean, mode, and median for inventory turnover times
and customer satisfaction scores across all stores.
o Compute these metrics for three regions (North America, Europe, Asia)
separately, assuming 80 stores in North America, 70 in Europe, and 50 in Asia.
o Create histograms for both datasets (turnover times and satisfaction scores) to
visualize their distributions.
o Use Excel’s SKEW function to quantify the skewness of each dataset.
 Excel Instructions:
o Use AVERAGE, MODE.SNGL (or MODE.MULT if multiple modes exist), and MEDIAN
functions for calculations.
o Use FILTER or Pivot Tables to segment data by region.
o Create histograms using the Data Analysis Toolpak or chart tools.
 Interpretation:
o Explain how the mean, mode, and median provide different insights into
inventory turnover and customer satisfaction. For example, why might the median
be a better measure than the mean for skewed data?
o Discuss how regional differences in these metrics reflect operational challenges
(e.g., supply chain issues in Asia, service quality in Europe).
o Analyze the impact of skewness on decision-making. For instance, how do
extreme turnover times (e.g., 30 days) affect the reliability of the mean?

Question 2: Correlation and Strategic Implications

 Task:
 o In Excel, calculate the correlation coefficient between inventory turnover times
and customer satisfaction scores using the CORREL function.
o Create a scatter plot to visualize the relationship between turnover times and
satisfaction scores.
o Segment the data by region and recalculate correlations for each.
 Excel Instructions:
o Use CORREL to compute the correlation coefficient for the entire dataset and by
region.
o Use a scatter plot with trendline to visualize the relationship.
o Apply IF statements or FILTER to segment data by region.
 Interpretation:
o Interpret the correlation coefficient. For example, does a longer turnover time
correlate with lower satisfaction? How strong is the relationship?
o Discuss regional variations in correlation. For instance, if Asia shows a stronger
negative correlation, what might this imply about supply chain issues affecting
customer experience?
o Propose a strategy to improve customer satisfaction based on turnover time
analysis. Should GlobalMart prioritize reducing mean turnover time or addressing
extreme delays (outliers)?

Question 3: Outlier Analysis and Mitigation

 Task:
o Identify outliers in the inventory turnover dataset using Excel’s interquartile range
(IQR) method: Q1 – 1.5×IQR and Q3 + 1.5×IQR.
o Recalculate the mean, mode, and median for turnover times after removing
outliers.
o Create a box plot to visualize the distribution and outliers by region.
 Excel Instructions:
o Use QUARTILE.INC to compute Q1, Q3, and IQR.
o Use conditional formulas (e.g., IF) to filter out outliers.
o Create box plots using Excel’s chart tools or Data Analysis Toolpak.
 Interpretation:
o Compare the mean, mode, and median before and after outlier removal. How do
outliers distort these metrics, and which is most affected?
o Discuss the business implications of outliers (e.g., supply chain disruptions
causing 30-day turnover times). Should GlobalMart focus on eliminating outliers
or improving overall consistency?
o Propose a mitigation strategy (e.g., supplier diversification, safety stock) and
estimate its impact on the median turnover time and customer satisfaction.

Question 4: Scenario Analysis with Monte Carlo Simulation

 Task:
o GlobalMart is considering two strategies to reduce inventory turnover times:
  Strategy A: Invest $20 million in automated inventory systems, with a
70% chance of reducing mean turnover time by 2 days and a 20% chance
of no change.
 Strategy B: Renegotiate supplier contracts for $10 million, with a 50%
chance of reducing mean turnover time by 3 days and a 30% chance of
increasing turnover by 1 day due to supplier resistance.
o Use Excel to run a Monte Carlo simulation (1,000 iterations) to estimate the
distribution of mean turnover times under each strategy.
o Calculate the expected mean, mode, and median turnover times for each strategy.
 Excel Instructions:
o Use RAND or RANDBETWEEN to simulate probabilities for each strategy.
o Create a data table with 1,000 rows to model turnover time outcomes based on
probability distributions (e.g., normal distribution with current mean and standard
deviation).
o Use AVERAGE, MODE.SNGL, and MEDIAN to summarize simulation results.
 Interpretation:
o Compare the expected mean, mode, and median turnover times for Strategies A
and B. Which strategy is more likely to achieve GlobalMart’s goal of a mean
turnover time below 5 days?
o Discuss the role of probability in decision-making. How does the uncertainty in
Strategy B’s outcomes affect its viability compared to Strategy A?
o Recommend a strategy, justifying your choice with simulation results and
potential impacts on customer satisfaction and costs.

Question 5: Executive Summary and Visualization

 Task:
o Prepare an executive summary (500–600 words) for GlobalMart’s board,
synthesizing your findings from Questions 1–4.
o Create a dashboard in Excel summarizing key metrics (mean, mode, median,
correlation, outliers) and visualizations (histogram, scatter plot, box plot).
 Excel Instructions:
o Use Pivot Tables and Charts to create a dashboard with histograms, scatter plots,
and box plots.
o Include calculated metrics (e.g., mean, mode, median) in a summary table.
 Interpretation:
o Summarize how mean, mode, and median inform GlobalMart’s inventory and
customer satisfaction strategies. For example, why might the median be a better
target for operational goals?
o Discuss how your analyses (correlation, outliers, simulation) support your
recommendations. For instance, how do regional differences or outlier mitigation
strategies align with strategic goals?
o Address potential board objections (e.g., cost of Strategy A, risks of Strategy B)
and propose a balanced approach to optimize performance.
Teaching Objectives
This problem set is designed to:

 Challenge students to apply mean, mode, and median in analyzing complex, skewed
datasets in a business context.
 Develop proficiency in Excel for statistical calculations, visualization, and simulation.
 Foster strategic thinking by linking statistical insights to operational and customer-
focused decisions.
 Prepare students to present data-driven recommendations to senior executives.

Expected Deliverables
 Excel Workbook: Containing calculations (mean, mode, median, correlation, outliers),
visualizations (histograms, scatter plots, box plots, dashboard), and Monte Carlo
simulation results.
 Detailed Report (1,500–2,000 words): Addressing all questions with quantitative results
and qualitative interpretations, including strategic recommendations.
 Executive Summary (500–600 words): Summarizing findings and recommendations for
the board, supported by Excel outputs.

Discussion Points for Class

 Statistical Nuances: How do mean, mode, and median differ in their utility for skewed
datasets? When should GlobalMart prioritize one over the others?
 Excel Applications: How can Excel’s tools (e.g., Pivot Tables, Data Analysis Toolpak)
enhance decision-making in large datasets?
 Strategic Trade-offs: Should GlobalMart focus on reducing average turnover times or
addressing extreme delays? How do statistical metrics guide this choice?
 Uncertainty and Risk: How does the Monte Carlo simulation account for uncertainty in
strategic decisions, and what are its limitations?

Notes for Students

 Ensure all Excel calculations are clearly labeled and replicable (e.g., use cell references,
avoid hardcoding).
 Interpret results in the context of GlobalMart’s goals (e.g., cost reduction, customer
satisfaction).
 Use visualizations to communicate insights effectively to non-technical stakeholders.
 Address skewness and outliers explicitly, as they significantly impact mean-based
decisions.

This problem set challenges students to integrate advanced statistical analysis with Excel
proficiency and strategic decision-making, preparing them for data-driven leadership roles.