DPBS1110/BMGT1310 Evidence-Based Problem Solving
Unit 2: Breaking Down Problems and Gaining Insights
Tutorial Questions
Case Part 1: Decoding the Furniture Store's Sales Puzzle: Visualising Multiple Perspectives with Logic Trees
Background: Modern Living, a leading national furniture store franchise, has recently faced an unexpected sales decline. The management team, keen on understanding the underlying causes, has called upon different analysts to examine the problem. A logic tree, a visual analytical tool that breaks down problems into their core components, has been suggested as a potential means to shed light on this situation. With their unique background and perspectives, each analyst is asked to approach the problem using a logic tree.
Your Task: As emerging problem solvers, you are tasked with developing a logic tree to analyse the sales decline at Modern Living. Remember, a logic tree is not a fixed solution but a reflection of a problem solver's perspective and understanding of the issue.
a) Your tutor will allocate the following initial branches to your group.
Begin your logic tree with the assigned branch. From this starting point, expand your logic tree by adding two more layers, ensuring that:
i) Each layer dives deeper into potential factors or reasons explaining the sales trends within the branch.
ii) The branches at each layer are mutually exclusive and collectively exhaustive (MECE).
b) How does the choice of initial branches (which simulate different analysts' perspectives) influence your logic tree's subsequent layers and insights?
c) How does the logic tree help visualise and convey your thought process and perspective to others? In what ways can logic trees serve as a collaborative tool, encouraging diverse viewpoints and fostering understanding among team members?
d) Preliminary analysis indicates that poor delivery services might be a potential driver for the sales decline. Your team wants to understand the various factors contributing to the customer delivery experience. Create a logic tree to break down and analyse the components of the customer delivery experience. A two-layer logic tree is sufficient as the team needs a quick analysis.
Case Part 2: Data-Driven Insights into Freight Carrier Delivery Performance
In the first part of the case, you investigated the sales decline of Modern Living by visually breaking down the problem using a logic tree. This exercise revealed multiple facets potentially contributing to the sales decline. One of the emerging concerns from the analysis was the role of delivery services.
Having identified delivery service as a potential area of concern, Modern Living's management has now shared a dataset detailing recent delivery records. This dataset contains information about various orders, their respective delivery times, the weight of items, the number of items, and if any damages were reported. All this data is segmented by the three main freight carriers: Modern Living uses: AllStar Freight, Bolt Logistics, and CleverCart Deliveries.
Dataset Description:
The dataset includes the following fields for a sample of 450 deliveries:
• Order Number: A unique identifier for each order, e.g., 0ZCVA3C9.
• Delivery Month: The month in which the delivery was made, e.g., January.
• Freight Carrier: The name of the freight carrier responsible for the delivery, e.g., AllStar Freight.
• Delivery Time (days): The number of days taken for delivery, e.g. 5 (i.e. 5 days).
• Number of Items: The number of items in the order, e.g. 7 (i.e. 7 items).
• Weight of Items (kg): The total weight of items in the order, e.g. 49.9 kg.
• Damages Reported: Indicates whether damages were reported for the
delivery, e.g., YES/NO.
Your Task:
a) Understanding the data: Imagine you are part of the Modern Living data analysis team. Before diving into deeper investigations, it is crucial for us to understand the nature of our data clearly.
i) Identify the data type for each of the variables in the dataset.
b) Starting with the Right Set of Questions: Utilising the 5Ws for Initial Data Analysis: Modern Living's management is keen on understanding its delivery performance, especially concerning delivery time and reported damages. As you embark on this analytical journey, it's pivotal first to ask the right questions that will lead you to the insights hidden within the data. Remember, each piece of data tells a story. Your role is to piece together this story in a meaningful way.
The 5Ws (What, Where, When, Who and Why) we learnt last week is a great tool to identify questions that will guide your data analysis and potentially uncover insights to help Modern Living address its challenges. Using the 5Ws framework:
i) Formulate Key Questions: Outline the questions you believe are essential to uncover insights into Modern Living's delivery performance.
ii) Identify Relevant Variables: For each of your 5W questions, identify which variables from the dataset will help you answer them.
iii) Select Analytical Approaches: Decide on the statistical measures or visual representations suitable for each question. Consider what would best highlight the insights or patterns in the data.
c) In-depth Analysis of Delivery Performance: The management at Modern Living has established delivery benchmarks. They categorise a delivery time of over 8 days as "Late", anything above 14 days as "Very Late", and aim for an average delivery time of 7 days. Additionally, they have set a benchmark where a damage rate of 10% or less is acceptable. This will be important in understanding and evaluating the performance of the freight carriers
i) Descriptive Statistics and Visualisation: Delivery Time Analysis:
1. Using Excel, calculate the following descriptive statistics for the entire dataset's delivery time:
• Mean
• Median
• Standard Deviation
• Minimum
• Maximum
• Range (Max - Min)
2. Create a histogram in Excel to visually represent the distribution of delivery times. What can you infer from the histogram about the general delivery performance?
Damage Analysis
3. Using a PivotTable in Excel, calculate the number of deliveries with reported damages, and the percentage of reported damages out of the total number of deliveries.
Hint: Create a PivotTable with the following fields and calculate the percentages.
4. Create a pie chart in Excel to visually represent the percentage of deliveries with and without reported damages. What insights can you gather from this pie chart?
5. Compare the descriptive statistics and analysis results for delivery time and reported damages against the management's benchmarks. Based on your analysis, which area (delivery time or reported damages) is comparably more important and requires first attention?
ii) In-depth Analysis by Freight Carrier – Focusing on Delivery Time
1. Using a PivotTable in Excel, calculate the mean delivery time and standard deviation for each freight carrier.
Hint: Create a PivotTable with the following fields
2. Using a PivotTable in Excel, calculate the mean delivery time for each freight carrier by delivery month.
Hint: Create PivotTable with the following fields