procurement supply chain masterclass

Solving Supply Chain and Procurement Problems using Data Analytics in Excel

Module 1 – Introduction to Data Analytics in Excel (2-day)

Module 2 – Deep Dive into Power Tools for Procurement Experts (1 day)

Module 3 – Deep Dive into Excel Power Tools for Supply Chain Experts (2-day)

Target Audience

Key objectives

1. Automate Data Management

Master Excel Power Query to automatically import, clean, and transform procurement data, reducing manual effort and ensuring data accuracy.

2. Enhance Analytical Capabilities

Utilize Excel Power Pivot and DAX for advanced data modeling and analysis, enabling deeper insights into supplier performance, inventory management, and cost optimization.

3. Empower Strategic Decisions

Develop interactive reports and dashboards to visualize key metrics, empowering procurement specialists to make data-driven decisions and achieve operational excellence.

Module 1 – Introduction to Data Analytics in Excel (2-day)

Day 1                    Introduction and Fundamentals

Topic 1     What is Data Analytics?

  • Data Analytics is not statistics, not SQL.
  • The relationship between Data Analytics and Excel

Topic 2     Introduction to Excel Power Query

  • Overview of Power Query
    • Basic interface and navigation
    • Importing data from various sources (Excel files, CSV files, databases, web pages)

Topic 3     Data Transformation with Power Query

  • Data cleaning and transformation techniques
  • Filtering and sorting data
  • Removing duplicates and handling missing values
  • Merging and appending queries

Day 2                    Advanced Power Query Techniques

Topic 1     Advanced Data Transformation

  • Pivoting and unpivoting columns
  • Grouping and aggregating data
  • Custom columns and conditional columns

Topic 2     Connecting and Combining Data

  • Merging queries (inner join, outer join, left join, right join)
  • Appending queries
  • Creating relationships between different data sources

Topic 3     Automating ETL Processes

  • Setting up automatic data refresh
  • Creating reusable query functions
  • Using parameters in Power Query
  • Hands-on Practice
    • Practical exercises on advanced data transformation and automation techniques

Module 2 – Deep Dive into Power Tools for Procurement Experts (1 day)

Day 3                    Introduction to Power Pivot

Topic 1     Overview of Power Pivot

  • Introduction to Power Pivot and its interface
  • Loading data into Power Pivot
  • Creating data models

Topic 2     Data Modeling with Power Pivot

  • Understanding relationships and data modeling concepts
  • Creating relationships between tables
  • Creating calculated columns

Topic 3     Introduction to DAX (Data Analysis Expressions)

  • Basic DAX syntax and functions
  • Calculated columns vs. measures
  • Commonly used DAX functions (SUM, AVERAGE, COUNT, etc.)
  • Hands-on Practice
    • Practical exercises on creating data models and using DAX in Power Pivot

Module 3 – Deep Dive into Excel Power Tools for Supply Chain Experts (2-day)

Day 4                    Advanced Power Pivot Techniques

Topic 1     Advanced DAX Functions

  • Time intelligence functions (DATEADD, DATESYTD, SAMEPERIODLASTYEAR, etc.)
  • Logical functions (IF, SWITCH)
  • Statistical functions (MEDIAN, RANKX)

Topic 2     Creating Advanced Data Models

  • Handling many-to-many relationships
  • Using hierarchies in data models
  • Optimizing data models for performance

Topic 3     Interactive Reporting with Power Pivot

  • Creating PivotTables and PivotCharts
  • Using slicers and timelines for interactivity
  • Building dashboards
  • Hands-on Practice
    • Practical exercises on advanced DAX functions and creating interactive reports

Day 5                    Practical Applications in Procurement and Supply Chain Management

Topic 1     Case Studies and Real-World Applications

  • Demand forecasting with Power Query and Power Pivot
  • Supplier performance analysis
  • Inventory management optimization

Topic 2     Building Comprehensive Dashboards

  • Combining multiple data sources
  • Creating key performance indicators (KPIs)
  • Visualizing supply chain metrics

Topic 3     Final Project

  • Participants work on a comprehensive project integrating all learned skills
  • Project examples: Supplier performance dashboard, inventory optimization report, procurement cost analysis

Topic 4     Review and Q&A

  • Review key concepts and techniques
  • Address participant questions and challenges
  • Discuss best practices and tips for ongoing use of Power Query and Power Pivot

Course Duration:

  • Module 1: 2 days
  • Module 2: 1 day
  • Module 3: 2 days

Delivery Mode:

  • Classroom

Course Delivery Language:

  • English

What participants said about Excel Power Query:

"Get to know a very useful ms office tools to manage large data."

"It is very helpful for people with zero knowledge for power query and power pivot."

"Saved time by automating data from files through Power Query. Many real live examples from other participants made the training more meaningful amd and relatable. "

"Get to know a very useful ms office tools to manage large data."

"It is very helpful for people with zero knowledge for power query and power pivot."

"Saved time by automating data from files through Power Query. Many real live examples from other participants made the training more meaningful amd and relatable."

"Taking long time to compile data and present to management, if master the power query and power pivot, a lot of time can be saved."

"For data  I hv to do it every month  now I can apply power query to cut short my time on the data analysis "

Price per pax:

  • Module 1: $900
  • Module 2: $450
  • Module 3: $900
  • All Modules: $1800

Not sure if this is the right course for you? Contact us below to find out more about this course or other courses that may be more relevant to you.

Problem Solving Data Analytics Supply Chain Procurement