Retail Data Analytics for Merchandising and Demand Planning Training Course

Course overview

Merchandising and demand planning teams make daily decisions on range, price, allocation, replenishment and promotional volume, yet the data needed to support those decisions is often dispersed across point-of-sale, inventory, product, customer and supplier systems. This course helps retail professionals turn those operational data sources into reliable measures of sales, availability, sell-through, margin, stock cover and forecast accuracy. Participants learn to distinguish a genuine change in demand from a stock-out, calendar effect, data-quality issue or promotional distortion before acting on the numbers.

The course covers the retail data model, KPI design, SQL querying, Excel analysis, Power BI dashboards and practical demand forecasting methods. Participants work with retail sales and inventory data at SKU, store, channel and category level. They calculate core merchandising measures, profile seasonal demand, identify slow-moving and at-risk stock, assess promotion uplift, create replenishment inputs, and compare forecast performance using bias, MAD, MAPE and WAPE. Attention is given to the business rules behind each metric, so learners can explain findings to buyers, planners, store operations and senior management.

Delivery combines instructor-led demonstrations with guided analysis of a realistic multi-store retail dataset. Each day includes worked examples, individual data tasks and team discussions that mirror trading and planning meetings. By the end of the week, each participant produces a retail performance pack: a documented KPI set, SQL extracts, an Excel demand-planning workbook, a Power BI dashboard and a 90-day action plan for a selected category or product group.

The programme is suited to professionals moving from spreadsheet reporting into structured retail analytics, as well as experienced commercial users who need a consistent method for converting data into merchandising and demand-planning decisions.

Course objectives

By the end of this course, participants will be able to:

• Define a retail analytics data model linking SKU, store, calendar, sales, inventory, promotion and supplier attributes
• Calculate sales, sell-through, stock turn, weeks of supply, gross margin return and on-shelf availability KPIs
• Write SQL queries that join retail sales and inventory tables and aggregate results by store, SKU, category and week
• Clean and validate retail datasets by identifying duplicate records, missing product attributes, returns and stock-out distortions
• Build an Excel demand-planning workbook using pivot tables, XLOOKUP, Power Query and seasonal trend analysis
• Create a Power BI dashboard with measures, drill-through views and exception flags for merchandising decisions
• Develop baseline demand forecasts using moving averages, seasonal indices and promotional uplift assumptions
• Present a category action plan supported by forecast-error metrics, inventory exceptions and recommended interventions

Benefits of attending

Target competencies

Who should attend

• Merchandise Planners — who need defensible SKU and category decisions on range, allocation and replenishment
• Demand Planners — who must translate sales history, promotions and stock positions into practical forecasts
• Category Managers — who need to diagnose category performance beyond headline sales figures
• Retail Buyers — who need evidence for supplier discussions, assortment reviews and order commitments
• Retail Analysts — who need structured SQL, Excel and Power BI methods for commercial reporting
• Inventory and Replenishment Analysts — who need to identify availability risks, excess stock and slow-moving lines

Requirements and prerequisites

This is a foundation-to-intermediate course. Participants should be comfortable using spreadsheets for basic calculations, sorting, filtering and charts, and should understand common retail terms such as SKU, sales, stock, margin and promotion. Experience with retail, wholesale, e-commerce or consumer goods data is helpful but not essential. No prior SQL, Power BI, Python, forecasting or statistics qualification is required; these are introduced through retail examples. Participants should bring a laptop capable of running Microsoft Excel and Power BI Desktop, or have access to the live-online training environment.

Training methodology

The instructor uses a realistic retail dataset containing product hierarchies, store sales, inventory snapshots, promotions and calendar events. Short teaching segments establish the commercial question and analytical method, followed by guided work in Excel, SQL and Power BI. Participants calculate measures, investigate exceptions and compare decisions in merchandising and planning scenarios. Group case discussions test how findings should change range, replenishment or promotional actions. On day five, participants assemble their own performance pack and receive structured feedback on the assumptions, measures and recommended actions.

Course outline

Day 1: Retail data foundations and commercial KPIs

• Retail data sources: POS, inventory, product master, promotion and supplier files
• SKU, store, channel, category and retail calendar data grains
• Retail data-quality checks for duplicates, returns, missing attributes and late feeds
• Sales value, unit sales, average selling price and markdown calculations
• Sell-through, stock turn, weeks of supply and stock-cover definitions
• Gross margin, GMROI and contribution measures for assortment decisions
• Stock-out effects and the distinction between observed sales and unconstrained demand

Workshop: Participants audit a category dataset and produce a KPI definition sheet with data-quality exceptions and agreed calculation rules.

Day 2: Querying and preparing retail datasets

• SQL SELECT, WHERE, GROUP BY and ORDER BY for retail transactions
• INNER JOIN and LEFT JOIN across sales, inventory and product tables
• Date filtering using retail weeks, fiscal periods and comparable-store periods
• SKU-store aggregation and category roll-ups
• CASE expressions for sales bands, availability flags and product-status rules
• Window functions for ranking stores and calculating period-over-period change
• Power Query transformations for product master and inventory data preparation

Workshop: Participants write SQL extracts for a weekly category review and create a cleaned SKU-store analysis table in Power Query.

Day 3: Merchandising performance and inventory analysis

• Assortment productivity analysis by SKU, brand, subcategory and store cluster
• ABC and XYZ segmentation for value and demand variability
• Store-cluster analysis using sales mix, local demand and ranging differences
• Markdown and price-change impact analysis
• On-shelf availability, lost-sales signals and phantom inventory investigation
• Slow-moving stock, aged inventory and exit-risk identification
• Promotion performance analysis using baseline sales, uplift and cannibalisation

Workshop: Teams conduct a category trading review and produce a ranked list of range, price, markdown and inventory actions.

Day 4: Demand forecasting and replenishment decisions

• Demand-history preparation and treatment of outliers, returns and stock-outs
• Moving-average and weighted-moving-average forecast methods
• Seasonal indices and retail calendar event adjustments
• Promotional uplift assumptions and post-promotion demand effects
• Forecast bias, MAD, MAPE and WAPE calculations
• Reorder point, safety stock and lead-time demand concepts
• Forecast collaboration inputs from buying, marketing, stores and suppliers

Workshop: Participants build a SKU-level forecast workbook, compare two forecast methods and document inventory implications for a promotion period.

Day 5: Retail dashboards and decision-ready action plans

• Power BI data model relationships for sales, inventory, product and calendar tables
• DAX measures for sales variance, sell-through, stock cover and forecast accuracy
• Retail dashboard design for executive, category and store-level users
• Drill-through, slicers and exception-based navigation in Power BI
• Visualising seasonal trends, stock risks and promotional performance
• Communicating assumptions, limitations and recommended actions
• Ninety-day retail analytics implementation planning

Workshop: Participants complete and present a retail performance pack containing a Power BI dashboard, forecast summary, exception list and 90-day category action plan.

Tools & standards covered

Microsoft Excel, Microsoft Power BI Desktop, Microsoft SQL Server, Power Query

A typical training day

Live online deliveries follow the same structure in the East Africa Time zone, with shorter screen blocks and longer breaks.

What the fee includes

How you can take this course

Certification

Participants who complete the full five days receive the Skillset Development Certificate of Completion, stating the course title, course code, dates and delivery format — suitable for professional-development records and employer reimbursement.

Frequently asked questions