All courses

Data and business intelligence

Advanced Data Analysis Tools

From accounting, sales and operating data to analysis models, reports and dashboards, with Excel, Power Query, SQL, Power Pivot and Power BI.

Reference duration
32 hours
Structure
8 sections · 32 one-hour chapters
Approach
Hands-on, data-driven, applied
Bring this course to your team

What people learn

  1. 01

    Understand the management data supply chain.

  2. 02

    Import, clean and transform company data.

  3. 03

    Model data for management analysis.

  4. 04

    Use Excel, Power Query, SQL, Power Pivot and Power BI for management work.

  5. 05

    Build management dashboards.

  6. 06

    Run quality and reconciliation checks.

How it runs

  1. 01 · 4 hThe management data supply chain
  2. 02 · 4 hAdvanced Excel for management control
  3. 03 · 4 hPower Query to import and transform
  4. 04 · 4 hSQL for management analysis
  5. 05 · 4 hData modelling for control
  6. 06 · 4 hPower Pivot and basic DAX
  7. 07 · 4 hPower BI for management dashboards
  8. 08 · 4 hProject work and model quality

Programme

8 sections, 32 hours.

Durations and programmes are the reference structure: content, level, examples and format are agreed with each company.

  1. 014 h

    The management data supply chain

    1. From business process to analytical dataSales, purchasing, production, stock, accounting and HR as data sources.
    2. Master data, transactions and codesCustomers, suppliers, items, accounts, cost centres, jobs and codes.
    3. The dimensional modelFinancial, sales and industrial facts; descriptive dimensions.
    4. Data quality and recurring problemsDuplicates, inconsistent codes, gaps, outliers, grain and history.

  2. 024 h

    Advanced Excel for management control

    1. Structured tables and dynamic modelsTables, names and structured references for recurring analysis.
    2. Advanced formulas for management analysisLookups, filters, conditional sums, dynamic logic and cross-checks.
    3. Pivot tables and multidimensional analysisAggregations by customer, product, period, area, cost centre and job.
    4. Tie-out checks in ExcelAccounts and management models compared; completeness and reconciliation.

  3. 034 h

    Power Query to import and transform

    1. Importing data from different sourcesExcel files, CSV, folders and exports from company systems.
    2. Cleaning and normalisationTypes, headers, nulls, dates, codes and standards.
    3. Merge, append and recurring transformationsJoining and appending tables, master-data lookups, automation.
    4. Reusable data flowsRobust, documented queries that can be refreshed over time.

  4. 044 h

    SQL for management analysis

    1. SQL for business usersQuery logic, SELECT, FROM, WHERE, ORDER BY.
    2. Joins between related entitiesTransactions, master data, accounts, customers, items, jobs and cost centres.
    3. Aggregations and indicatorsGROUP BY; sales, costs, quantities, margins and ratios.
    4. Views and queries for reportingManagement datasets ready for Excel, Power BI or other tools.

  5. 054 h

    Data modelling for control

    1. Grain and relationships between tablesThe right detail for sales, costs, budget, jobs and production.
    2. A model for budget and actualActual, budget, forecast and history compared.
    3. A model for customer and product marginsSales, direct costs, master data, price lists, discounts and terms.
    4. A model for industrial analysisProduction, times, scrap, departments, BOMs, routings, machine and labour hours.

  6. 064 h

    Power Pivot and basic DAX

    1. Relationships and the tabular modelTabular model, relationships, filters and filter direction.
    2. DAX measures for controlSales, cost, margin, percentages and ratios.
    3. Time analysis and comparisonsYear, month, quarter, year-to-date, prior year and trend.
    4. Budget versus actual in DAXActual, budget, absolute and percentage variance, progress.

  7. 074 h

    Power BI for management dashboards

    1. Designing the dashboardUsers, information goals, layout, navigation, summary and detail.
    2. Visuals for controlKPIs, matrices, time series, waterfall, decomposition tree, drill-down.
    3. A financial management dashboardRevenue, costs, margins, variances, customers, products and periods.
    4. Management commentaryFrom chart to decision: evidence, anomalies, causes and actions.

  8. 084 h

    Project work and model quality

    1. Data model quality checksCompleteness, uniqueness, consistency, reconciliation and refresh.
    2. Documenting the modelData dictionary, sources, transformations, measures and ownership.
    3. Project work: from raw data to dashboardImport, transformation, modelling and visualisation on simulated data.
    4. Presenting the project workManagement reading, issues and possible next steps.

Let’s fit the course to your team’s work.

Durations and programmes are the reference structure: content, level, examples and format are agreed with each company.

Plan the course

How training runs

  1. 01Company coursesCommon ground for people who work with data and models.
  2. 02Solution trainingBy role, on the tools and models that were adopted.
  3. 03Everyday useRepeatable processes and independent users.