6 Data Analysis and Activity Profiling

In this assignment, you will work with operational data provided by a company to conduct a thorough data analysis. You will profile activities, measure relevant Key Performance Indicators (KPIs), and use data analysis techniques to uncover insights on how the company can optimize its operations within the scope of your assigned challenge.

What is Activity Profiling?

Activity profiling involves analysing a company’s key operational activities to understand their characteristics, performance metrics, and impact on overall efficiency and effectiveness. By profiling activities, you can identify areas for improvement, optimize resource allocation, and enhance decision-making processes.

Data Sources

The data files provided by the company are detailed in Chapter 4. These files contain unstructured operational data that you will need to clean, format, and analyze to complete the assignment effectively.

6.1 Learning Objectives

Identify and measure Key Performance Indicators (KPIs) to assess efficiency and effectiveness across different areas of the business.
Format and analyse operational data to develop insights into performance metrics and key activities.
Profile key operational activities to support data-driven decision-making.
Convey insights through clear figures and tables.

6.2 Overview

To fully understand the company’s operational performance, you will analyse all relevant data. Your team will specialize in profiling specific activities, but it is important to maintain an understanding of the broader data to gain insights into overall performance.

The data is provided as a series of files in an unstructured format. Your task is to format the data, filter out irrelevant information, and analyse the remaining data to showcase the company’s operational activities and performance, identifying areas for improvement.

Real-World Data Analysis

In a real-world setting, company data is often complex, unstructured, noisy, and dispersed across multiple sources (e.g., formats, departments, and systems).

When profiling activities to extract meaningful insights and support decision-making processes, it is your responsibility to:

Find (i.e., talk to relevant stakeholders, employees, or managers).
Filter (i.e., select relevant data sources).
Clean (i.e., remove duplicates, errors, or inconsistencies).
Combine (i.e., merge data from different sources).
Format (i.e., structure data for analysis).
Analyse (i.e., extract insights, identify patterns, and measure performance).
Augment (i.e., fill in gaps or simulate data if necessary).

For this assignment, the finding step has already been completed, and you will focus on the remaining steps to effectively analyse the data.

6.3 Assignments

1) Data Preparation

The company has provided you with data related to its operational activities. This data is in an unstructured format and needs to be formatted and organized for analysis. Your first task is to prepare the data for further analysis by converting it into a more usable format.

Your preparation should be targeted towards helping you profile key activities, measure KPIs, and conduct scenario analysis to provide actionable recommendations for improving operational outcomes within your team’s specialization.

Importance of Data Preparation

Data preparation is often the most time-consuming and critical step in the data analysis process. It involves cleaning, transforming, and structuring data to make it suitable for analysis. The quality of your data preparation will significantly impact the accuracy and reliability of your analysis and insights.

Deliverables

A flowchart or diagram illustrating the data preparation process, including the steps taken to format the data for analysis. (Search online for examples of data preparation, data cleaning, or data preprocessing flowcharts to guide you.)
A summary of your preparation process linked to the flowchart, detailing the steps you took to structure the data for analysis. This summary should explain the rationale behind each decision made regarding filtering, cleaning, and structuring the data. It should answer the following questions:
- What data sources did you use, and why?
- How did you filter out irrelevant information?
- What cleaning steps did you perform, and why?
- How did you structure the data for analysis, and why?
Excel spreadsheet(s) containing the formatted operational data, ready for analysis. If necessary, include separate sheets for different contexts.

Assessment Criteria

Data Formatting: Accuracy and effectiveness in formatting operational data to facilitate analysis.
Data Cleaning: Thoroughness and attention to detail in cleaning the data to remove errors and inconsistencies, fill in missing values, etc.
Data Structuring: Clarity and organization in structuring the data for analysis, making it easy to interpret and analyse.
Flowchart: Clarity and completeness of the flowchart illustrating the data preparation process.
Summary: Coherence, clarity, and depth of the data preparation process, explaining the rationale behind each step.
Relevance: Relevance of the data preparation process to the company’s operational goals and challenges.
Organization: Organization and clarity of the spreadsheets containing the formatted data for analysis.

2) Activity Profiling

Once the data is formatted, you will profile key operational activities to understand their characteristics, performance metrics, and impact on the company’s operations.

Since the end goal is to produce a dashboard that provides actionable insights, you need to identify the key activities that drive the company’s performance and measure the relevant metrics associated with each activity. Your analysis should focus on identifying trends, patterns, and performance indicators that can guide decision-making and operational improvements within your team’s specialization.

Using the pre-processed data, create profiles of key operational activities, including the metrics you will use to assess performance in your area of specialization. These profiles should provide a detailed overview of each activity, highlighting its importance, performance metrics, and potential areas for improvement.

Your goal is to create informative and visually appealing profiles that comprehensively overview the relevant key operational activities and their performance metrics. These profiles will serve as the foundation for your subsequent analysis and decision-making processes.

Creating Profiles

A profile can include information such as:

Financial metrics (e.g., costs, revenues, profits over time or per activity)
Time metrics (e.g., lead times, cycle times, processing times)
Resource utilization (e.g., labour, materials, equipment)
Volume or frequency of activities (e.g., production runs, deliveries, maintenance schedules)
Performance against targets or benchmarks (e.g., efficiency, productivity, quality)
Trends or patterns in activity data (e.g., seasonality, fluctuations, correlations)
Demands or requirements associated with each activity (e.g., customer orders, service requests)
Risks or challenges related to each activity (e.g., bottlenecks, delays, errors)

A profile can be presented in various formats, such as:

Tables (e.g., summary of key metrics for selected activities, comparison of performance metrics).
Charts (e.g., a bar chart showing costs per activity, a line chart showing production volumes over time, a pie chart showing resource utilization).
KPI summary statistics (e.g., average lead time, total cost, average utilization rate, maximum delay, earliest start time).

Deliverables

Excel File containing:
- Formatted operational data ready for analysis. If necessary, include separate sheets for different contexts.
- At least four activity profiles, each focusing on a specific operational activity within your team’s specialization. The profiles must be created using the pre-processed data from the previous step and presented separately in Excel spreadsheets.
Summary Report: For each profile, a summary of the activity profiling analysis, including:
- A description of the activity and its importance to the company.
- Key performance metrics associated with the activity.
- Visual representations of the key metrics (e.g., table, chart, summary statistic).
- Insights or observations based on the data analysis.
- Recommendations for improving performance or addressing challenges.
This summary should refer to the information, tables, or charts exported from the Excel file.

Assessment Criteria

Metric Selection: Appropriateness and significance of the metrics selected to assess performance and identify areas for improvement.
Visual Representations: Clarity, quality, and effectiveness of the visual representations used to present key metrics and insights.
Creativity: Creativity and originality in identifying performance metrics and presenting activity profiles.
Insights: Depth and relevance of the data analysis and activity profiling insights.

6.4 Delivery Instructions

Submit the following deliverables on Canvas:

Excel file entitled groupXX_p2_data_analysis.xlsx, where XX is your group number (e.g., group01_p2_data_analysis.xlsx). This file should contain:
- Spreadsheet(s) containing formatted operational data ready for analysis. Include separate sheets for different contexts, and label them clearly, using the prefix data_ (e.g., data_production, data_forecasting).
- At least four activity profiles generated using the data, including metrics and visual representations. Use separate sheets for each profile, and label them clearly, using the prefix profile_ (e.g., profile_yearly_production, profile_delivery_performance).
A PDF report entitled groupXX_p2_activity_profiling.pdf (e.g., group01_p2_activity_profiling.pdf). This file should include two sections:
1. Data Preparation [max. one page]:
  - A summary of the data preparation process detailing the steps taken to structure the data for analysis.
  - A flowchart or diagram illustrating the data preparation process.
2. Activity Profiling:
  - Per profile [around half a page]:
    - Descriptive title of the activity profile.
    - A summary of the activity and its importance to the company.
    - Key performance metrics and visual representations.
    - Insights, observations, and recommendations based on the analysis.
    - Figures or tables exported from the Excel file to support the analysis.