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