Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for merchandise planners

For merchandise planners and allocators whose week runs on sales, stock and intake files in Excel. The lessons of The Complete Excel Course with AI Integration use sales orders and a company budget; this page picks out the ones a planning workbook draws on: ratio formulas, SUMIFS by category and store, PivotTables for the trading review, lookups that join stock to sales, the FinanceGuard model for open-to-buy, and Power Query with Power Pivot for the weekly files.

See the plans Watch free lessons

Taught by Sagnik Bhattacharya and Virginia Thorn

The Complete Excel Course with AI Integration: Sagnik Bhattacharya and Virginia Thorn

What you will be able to do

At work, you will be able to:

  • Work out sell-through, weeks of cover and stock turn for each line and category with ratio formulas, absolute references and IFERROR for lines with no sales.
  • Total units sold, stock and intake by category, store and week with SUMIFS, and compare the weeks before and after a markdown with date conditions.
  • Keep an open-to-buy view: planned intake against orders placed by category and month, with the gap shown as variance and percent variance in colour-coded number formats.
  • Work out each store's share of sales with an absolute reference to the total or a DAX percent of total, as the starting point for an allocation.
  • Combine the weekly sales and stock files in one folder with Power Query, and turn a plan with a column per week into a long table.
  • Read the week's trading by category and store in PivotTables with slicers and PivotCharts.

Projects

What you will build.

The course has 14 projects. These three matter most here.

  • Excel PivotTables and a PivotChart of sales data (SalesPlusPerformance): net sales by product category as a column chart, with State, Region and City filters.

    Project 05

    SalesPlusPerformance

    A sales question answered in seconds, straight from the order data.

    Category and store totals for the weekly trading review: dates grouped into periods, a slicer per store and Refresh All once the week's sales are in.

  • Budget vs actual dashboard in Excel (FinanceGuard): total budget, actuals and variance cards above a monthly line chart and a column chart by department.

    Project 09

    FinanceGuard

    The budget vs actual report finance teams send every month.

    An open-to-buy view in budget vs actual form: planned intake against orders placed, the gap left to spend shown as the variance, coloured when it turns negative.

  • Power Query sales report in Excel (SalesFusion): sales by category, sales by region and a monthly trend, with slicers for channel, year, category and region.

    Project 12

    SalesFusion

    A sales report that refreshes itself from a folder of files.

    Each week's sales and stock files dropped in one folder and joined into a single table, week columns turned into rows, and store shares from a DAX percent of total.

Lessons

The lessons that matter, in course order.

The Complete Excel Course with AI Integration has 186 lessons. This page picks out 56 of them, 4 h 45 min of video. Each lesson keeps its number in the course.

Where to start

Lesson 10 Relative vs absolute references in Excel

Begin at Lesson 10 if you already write formulas: absolute references and IFERROR keep the planning ratios right when they are copied down a range. New to Excel? Begin at Lesson 1. If SUMIFS, PivotTables and XLOOKUP are daily tools, go straight to Lesson 115 for the open-to-buy model, or to Lesson 153, where Power Query begins.

56 of 186 lessons · 4 h 45 min

01 Microsoft Excel fundamentals: building a foundation from scratch 3 of 13 lessons · 10 min The arithmetic of the planning ratios: absolute references that hold a total or a target in one cell, percentages for sell-through and store shares, and IFERROR for a line with no stock or no sales. The lessons use a personal budget; the ratios take the same formulas.
  1. 10 Start here Relative vs absolute references in Excel Dollar signs and mixed references 3 min
  2. 11 Calculating percentages in Excel Each expense as a percentage of income 2 min
  3. 12 Common Excel errors and how to fix them DIV0, NAME, VALUE errors and IFERROR 4 min
04 Essential formulas and functions (IF, SUMIF and more) 2 of 13 lessons · 15 min SUMIF and SUMIFS for units sold, stock and intake by category, store and week, with date conditions to total the weeks before and after a markdown, and COUNTIFS for the lines in each category.
  1. 36 SUMIF and COUNTIF in Excel Conditional sum and count with one criterion 8 min
  2. 37 SUMIFS and COUNTIFS in Excel Sum and count with multiple criteria 7 min
07 PivotTables and PivotCharts: data analysis made easy 10 lessons · 33 min PivotTables for the weekly trading review: sales by category and store, dates grouped, slicers, PivotCharts and a refresh when the new week's data is added. The lessons use sales orders, the same kind of data a planner reads.
  1. 57 PivotTables 101 in Excel What a PivotTable does and the sales dataset 5 min
  2. 58 Preparing data for PivotTables Clean data with TRIM, PROPER, find and replace 5 min
  3. 59 Create your first PivotTable in Excel Insert a PivotTable from a sales table 1 min
  4. 60 PivotTable fields in Excel Rows, values, columns and Value Field Settings 2 min
  5. 61 PivotTable summaries in Excel Sales by product category and grouping by date 4 min
  6. 62 Slicers in Excel Filter PivotTables with clickable buttons 3 min
  7. 63 PivotCharts in Excel Create interactive charts from a PivotTable 5 min
  8. 64 PivotTable design Tab Report layouts, PivotTable styles and number formats 5 min
  9. 65 Refresh a PivotTable in Excel Update pivots when source data changes 2 min
  10. 66 Excel Ninja Challenge PivotTable sales by region, category and monthly trend 1 min
08 Advanced Excel functions: lookups, text and arrays 10 of 12 lessons · 49 min Lookups that bring stock and intake onto each sales line (VLOOKUP, XLOOKUP, INDEX and MATCH, with IFERROR), LEFT and MID to read the category or store part of a code, then UNIQUE, FILTER, SORT and SORTBY for lists such as the lines with the most weeks of cover.
  1. 68 VLOOKUP in Excel Find employee data across sheets with exact match 4 min
  2. 69 XLOOKUP in Excel Exact match, if not found and two-way lookup 6 min
  3. 70 IFERROR in Excel Handle lookup errors in VLOOKUP and XLOOKUP formulas 3 min
  4. 71 INDEX and MATCH in Excel A flexible alternative to VLOOKUP 6 min
  5. 72 CONCAT and TEXTJOIN in Excel Combine employee names and text with a delimiter 3 min
  6. 73 LEFT, RIGHT and MID in Excel Extract parts of a text code like DAL-SAL-0001 7 min
  7. 74 TODAY, EOMONTH and NETWORKDAYS in Excel Date calculations for HR data 5 min
  8. 75 UNIQUE function in Excel Extract a distinct list with dynamic arrays 6 min
  9. 76 FILTER function in Excel Dynamic array filtering with AND and OR criteria 5 min
  10. 77 SORT and SORTBY in Excel Sort data dynamically with formulas 4 min
12 Project: FinanceGuard budget variance analyser (budget vs actual Excel dashboard) 10 lessons · 52 min FinanceGuard as an open-to-buy check: planned intake against orders placed, the gap as variance and percent variance, custom number formats that colour an overspend, PivotTables, KPI cards, a combo chart and slicers. The project uses a company budget; an intake budget by category has the same shape.
  1. 115 FinanceGuard budget vs actual dashboard Excel project overview 3 min
  2. 116 Excel tables for budget and actual data Organise the FinanceGuard datasets 4 min
  3. 117 Budget vs actual model in Excel SORT, UNIQUE, VSTACK, LET, TEXTSPLIT, SUMIFS 8 min
  4. 118 Budget variance in Excel Calculate variance and percent variance 4 min
  5. 119 Custom number formats in Excel Show budget variances with colours and signs 2 min
  6. 120 PivotTables for budget vs actual The FinanceGuard pivot workspace 8 min
  7. 121 KPI cards in Excel Summary cards for a budget variance dashboard 4 min
  8. 122 Budget vs actual charts in Excel Combo chart with secondary axis for variance 5 min
  9. 123 Slicers and PivotCharts in Excel Interactive FinanceGuard budget dashboard 4 min
  10. 124 Finish the FinanceGuard dashboard in Excel Final polish and variance insights 10 min
15 SalesFusion: advanced data tools and a Power Query modelling system 21 lessons · 2 h 7 min SalesFusion: Power Query combines a folder of sales files into one table, appends history, merges in product and region names, summarises with Group By and unpivots a table with a column per month; Power Pivot relates the tables and DAX gives percent of total for store shares. The Power Pivot lessons need Excel for Windows.
  1. 153 SalesFusion Power Query project Excel ETL pipeline and data model overview 5 min
  2. 154 Introduction to Power Query in Excel The ETL workflow explained 5 min
  3. 155 Import a CSV with Power Query Get data from text and transform data 2 min
  4. 156 Clean columns in Power Query Remove and rename columns for modelling 5 min
  5. 157 Clean text in Power Query Trim, clean, replace values and fix text case 6 min
  6. 158 Data types in Power Query Fix date, whole number, decimal and text types 7 min
  7. 159 Date columns in Power Query Add year, month and year-month fields for reports 7 min
  8. 160 Business logic in Power Query Custom and conditional columns for profitability 6 min
  9. 161 Group By in Power Query Summarise sales by month and region to validate data 5 min
  10. 162 Close and Load in Power Query Connection only, table or Data Model 5 min
  11. 163 Merge queries in Power Query Look up product and region names with a left join 5 min
  12. 164 Reuse a Power Query pipeline Duplicate queries for a new year of sales data 5 min
  13. 165 Append queries in Power Query Combine 2024 and 2025 sales tables 3 min
  14. 166 From Folder in Power Query Combine every sales file in a folder automatically 9 min
  15. 167 From Folder automation in Power Query Profit metrics and fiscal year columns 7 min
  16. 168 Build a sales fact table in Power Query Merge lookups and append history 6 min
  17. 169 Power Pivot Data Model in Excel Relationships and Diagram View 6 min
  18. 170 DAX measures in Power Pivot Total sales, margin percent and percent of total 10 min
  19. 171 PivotTable report from the Data Model Format the SalesFusion report in Excel 5 min
  20. 172 Interactive Excel dashboard Data model PivotTables, slicers and styling 10 min
  21. 173 Unpivot columns in Power Query Turn wide month columns into a long table 8 min

Instructors

Who teaches it.

Virginia Thorn smiling at the camera, dark hair tied back, wearing a navy top.

Instructor, Excel with AI

Virginia Thorn

Emmy Award winner. Teaches Excel with AI at Coding Liquids.

Questions

Asked before starting.

Which Excel course suits merchandise planners and allocators?

The Complete Excel Course with AI Integration from Coding Liquids, for the Excel a planning week runs on: ratio formulas for sell-through and weeks of cover, SUMIFS by category, store and week, PivotTables for the trading review, FinanceGuard's budget vs actual model as an open-to-buy check, and Power Query to combine the weekly sales and stock files. The lessons use sales orders and a company budget. The path here is 56 lessons, 4 hours 45 minutes in all.

Can I calculate sell-through and weeks of cover with what it teaches?

Yes, as formulas, though The Complete Excel Course with AI Integration never names either measure. Sell-through divides units sold by units received, weeks of cover divides stock by average weekly sales, and stock turn divides sales by average stock: each is a division taught with absolute references, percentage formats and IFERROR for a line with no sales yet. SUMIFS supplies the totals by category and week.

Can Excel help me manage open-to-buy?

With the spreadsheet part, yes. The Complete Excel Course with AI Integration does not teach open-to-buy by name, but its FinanceGuard project builds the shape it takes: a budget table and an actuals table, variance and percent variance, colours for an overspend, and a dashboard with slicers. Enter planned intake as the budget and orders placed as the actuals, and the variance is what is left to buy for each category and month.

Will it help me merge weekly sales and stock exports?

Yes. In The Complete Excel Course with AI Integration the SalesFusion project uses Power Query to combine every file in a folder into one table, append history, merge in product and region names with a left join and fix the data types, so the next export only has to be dropped in the folder. A later lesson unpivots a table with a column per month into a long one, which suits a plan laid out by week. The Power Pivot lessons need Excel for Windows.

Does it teach retail planning systems or forecasting?

No. The Complete Excel Course with AI Integration teaches Excel, not merchandise planning: retail planning systems, size and assortment optimisation and sales forecasting methods are not part of it, and its data sets are sales orders and a company budget rather than a retail plan. It covers the spreadsheet work around them: the ratios, the weekly summaries, the intake budget and the combining of weekly files.

Excel with AI

See the whole course.

The Complete Excel Course with AI Integration: 16 hours of video in 186 lessons, built around 14 projects, with lifetime access, a Certificate of Completion and a 14-day money-back guarantee.

See the plans Watch free lessons

The same course

For another job, level or topic.

By job

Finance and accounting
HR and office administration
Operations and supply chain
Data and reporting
Sales, marketing and customer service
Business and management
Engineering, construction and manufacturing
Retail, property and hospitality
Healthcare and life sciences
Education and careers
Public sector, non-profit and legal
Technology

By level and topic