Excel Inventory Control Course

Lesson 1 • Formatting Cells and Worksheets

Applies number formats, borders, and column widths to raw data. Creates readable inventory layouts that reduce misreading errors.

Lesson 2 • Saving, Protecting, and Sharing Files

Covers file formats, workbook passwords, and shared access settings. Ensures inventory data is secure and accessible to the right users.

Lesson 3 • Entering and Editing Inventory Data

Teaches correct data entry methods for text, numbers, and dates. Prevents common input errors that corrupt inventory records.

Lesson 1 • Data Validation for Clean Entry

Applies dropdown lists, numeric ranges, and date constraints to input cells. Stops invalid entries before they reach the inventory record.

Lesson 2 • Designing a Transaction Log

Creates a dated, append-only log of receipts, issues, and adjustments. Provides the raw data source for stock movement analysis.

Lesson 3 • Building an Item Master Table

Defines required fields for a product catalogue: SKU, description, unit, category, and cost. Serves as the reference table for all inventory calculations.

Lesson 4 • Principles of Flat-Table Database Design

Introduces one-row-per-record structure and field normalisation. Prevents the mixed-data layouts that break formulas and filters later.

Lesson 1 • Arithmetic and Absolute References

Builds addition, subtraction, and percentage formulas with locked cell references. Establishes the calculation backbone for all inventory maths.

Lesson 2 • Counting and Summing by Criteria

Uses COUNTIF, SUMIF, COUNTIFS, and SUMIFS to aggregate stock by category or location. Powers summary dashboards and exception reports.

Lesson 3 • Date and Time Functions

Calculates lead times, shelf life, and ageing using TODAY, DATEDIF, and NETWORKDAYS. Enables time-sensitive inventory decisions.

Lesson 4 • Conditional Logic for Stock Alerts

Applies IF, IFS, and nested logic to flag reorder points and overstock conditions. Automates the decision layer in inventory monitoring.

Lesson 1 • Dynamic Arrays for Live Queries

Applies FILTER, SORT, and UNIQUE functions to return live, auto-updating result sets. Replaces manual filter-copy workflows with formula-driven outputs.

Lesson 2 • Advanced Filter for Complex Queries

Uses the Advanced Filter with a criteria range to extract multi-condition subsets. Handles queries too complex for AutoFilter dropdowns.

Lesson 3 • Sorting Inventory Records

Applies single and multi-level sorts by SKU, quantity, or date. Prepares data for cycle counts, reports, and physical audits.

Lesson 1 • Building a FIFO Costing Model

Constructs a layer-by-layer FIFO schedule using transaction log data. Calculates ending inventory value and COGS for each period.

Lesson 2 • Standard Cost and Variance Analysis

Sets a predetermined standard cost per item and measures purchase price variance. Highlights cost deviations that require management attention.

Lesson 3 • Weighted Average Cost Calculation

Computes moving weighted average cost after each receipt transaction. Produces a single unit cost used for all subsequent issues.

Lesson 4 • Understanding Inventory Costing Concepts

Defines cost flow assumptions and their impact on reported inventory value. Provides the conceptual framework before any formula is built.

Lesson 1 • Calculating Reorder Points

Derives reorder points from average daily demand and supplier lead time. Triggers replenishment before stock reaches zero.

Lesson 2 • Safety Stock Modelling

Calculates safety stock using demand variability and service level targets. Buffers against stockouts caused by demand spikes or late deliveries.

Lesson 3 • ABC Classification of Inventory

Ranks items by annual spend using SUMIF and PERCENTRANK to assign A, B, or C class. Focuses replenishment effort on high-value items.

Lesson 4 • Economic Order Quantity Model

Implements the EOQ formula to minimise combined ordering and holding costs. Produces the optimal order quantity for each stocked item.

Lesson 1 • Creating Your First PivotTable

Inserts a PivotTable from an Excel Table and arranges rows, columns, and values. Demonstrates how thousands of rows collapse into a readable summary.

Lesson 2 • Summarising Stock by Category and Location

Groups inventory by category, warehouse, and supplier using PivotTable row fields. Reveals distribution patterns across the supply chain.

Lesson 3 • Calculated Fields and Items

Adds custom metrics such as margin percentage and days-on-hand inside the PivotTable. Extends analysis without modifying the source data.

Lesson 4 • PivotCharts for Visual Reporting

Generates bar, column, and pie charts directly from PivotTable data. Converts numeric summaries into visuals for management presentations.

Lesson 1 • Dashboard Layout and Design Principles

Defines layout zones, colour hierarchy, and data-ink ratio for effective dashboards. Ensures the viewer finds critical stock information within seconds.

Lesson 2 • Sparklines and Trend Indicators

Embeds sparkline charts in cells to show stock movement trends over time. Adds trend context without consuming dashboard space.

Lesson 3 • Conditional Formatting for Visual Alerts

Applies colour scales, data bars, and icon sets to highlight critical stock levels. Replaces manual scanning with automatic visual cues.

Lesson 4 • Automating Report Refresh and Distribution

Links dashboard to live data sources and sets up one-click refresh workflows. Reduces manual reporting effort and ensures data currency.