Stock Control & Inventory Management: Excel to Python

Explore inventory under uncertainty, including EOQ contrasts and policies like min-max and order-up-to, and learn to use fill rate, cycle service level, and safety stock to optimize transport.

Explore two inventory policies—continuous (reorder point with fixed order quantity Q) and periodic review—focusing on lead time, safety stock, and how demand uncertainty shapes average inventory and flow time.

Analyze cycle service level and item fill rate under uncertain demand using normal distribution with reorder point and safety stock, and compute lead-time demand probability and expected shortage in Excel.

Analyze bike inventory dynamics using fixed lot sizes, weekly demand, lead time, and safety stock to compute average inventory, flow time, cycle service level, and fill rate in Excel.

Analyze bike inventory dynamics by calculating two-week demand (5000 units), two-week std dev (707), and safety stock (1000) to derive average inventory and a 92% cycle service level.

Calculate the expected shortage from safety stock and sigma, then infer the item fill rate from the reorder point; the example shows a 25-unit shortage and a 99.7% fill rate.

Learn to set the cycle service level and compute safety stock using the norm inverse for 98% coverage, then determine the reorder point by adding lead time demand.

Learn to define and compare cycle service level and item fill rate, then adjust safety stock using goal seek to achieve desired expected shortage and reorder point in Excel.

Examine inventory aggregation across four stores, comparing per-store safety stock with centralized warehouse safety stock, and show how aggregation lowers total safety stock via the square root of stores.

Explore safety inventory aggregation and its cost impact using a Shopko example with high-value and low-value electronics, comparing per-store versus central warehouse safety stock in Excel.

Assess how high coefficient of variation in low-demand, high-value products drives centralized warehousing and per-store safety stock calculations using lead time and 95% service level, highlighting aggregation to reduce variability.

Aggregate safety stock across 2000 stores at a warehouse to reduce variability and inventory costs, showing nearly 80% holding-cost savings and a 95% service level with a two-week lead time.

Examine a Madison-based medical supplier’s periodic replenishment across 24 territories, weighing inventory and transport costs, and compare three options in Excel to optimize service levels and total cost.

Explore stock control and inventory dynamics across multi-hub and central warehouse setups, comparing current, weekly local shipments, and single-hub policies, with calculations for lot size, cycle stock, and safety stock.

Explore stock control dynamics with Excel and Python, covering cycle service level, safety stock, and average inventory. Compare continuous Q and periodic s max policies with practical order-up-to examples.

Compare four-week and one-week replenishment, showing max levels and orders for high and low value items. Consolidation lowers safety stock and total cost; next lecture analyzes transportation cost.

Evaluate shipment options to balance transportation and inventory costs, concluding that centralized aggregation with FedEx door-to-door delivery minimizes total cost.

Explore how temporal aggregation of transportation trades off delivery speed and costs, showing how delaying shipments lowers cost through fixed and variable costs while increasing response time and variability.

Explore tailored transportation setups, milk runs, private fleets, cross-docking, and third-party options, driven by supply and demand, density, and product value, plus inventory aggregation and desegregation strategies.

Explore how inventory placement, aggregation versus disaggregation, and ordering frequency affect service levels, safety stock, and costs, and learn to target fill rates using Excel and Python for distribution planning.

Install Python and Anaconda, and discover why Python dominates AI, data science, and web development. Use Jupyter Notebook or Spyder to install the inventories library with pip.

Install and set up Anaconda by following a guided download for Windows, Mac, or Linux, preparing you to use Python for stock control and inventory dynamics.

Follow along to install Anaconda by double-clicking the installer, continuing through prompts, and agreeing, in about 2 to 3 minutes, then explore inside Anaconda which applications will work.

Learn Spyder as an IDE, compare it with Jupyter notebooks, and set up a supply chain data science project in Anaconda, then open Spyder and run basic Python code.

Explore how to use jupiter notebook as a web-based interactive shell for python, create notebooks, run code, and compare its workflow with spyder inside anaconda, with setup variants.

Learn how Python libraries enable data science workflows by importing pandas, numpy, and matplotlib within Jupyter or Spyder in an Anaconda environment, for data manipulation, visualization, and forecasting.

Install inventories via pip to use the latest Python package for stock control and inventory dynamics, and follow future updates with pip install inventories.

Learn Python fundamentals, import data, create lists and dictionaries, write a function, and a for loop to build inventory simulations and apply the goal seek algorithm with Excel and Python.

Explore Python data types and data structures for inventory analytics, including floats, integers, strings, datetimes, booleans, lists, dictionaries, tuples, and arrays, with importing from Excel or CSV and cleaning tips.

Explore python basics: call functions with parentheses, assign with equals, and build lists, dicts, and objects. See data frames as excel-like tables, with observations as rows and attributes as columns.

Explore Python fundamentals in a Jupyter notebook by performing basic arithmetic, creating variables and lists, and practicing zero-based indexing to retrieve list elements.

Learn how to create and subset lists in Python, using zero-based indexing, slice notation with from start to end, and selecting specific items by indices or names (strings).

Explore how dictionaries map keys to values in Python, define them with squiggly brackets, and use keys() and values() to access data and retrieve specific elements.

Explore numpy arrays as a memory-efficient, one-dimensional data structure; learn to create arrays with the array function, perform elementwise operations like addition and division, and contrast with lists.

Import data in Python using pandas read_csv in a Jupyter notebook to load the online retail dataset and inspect its structure with head, tail, shape, and describe.

Master subsetting dataframes with iloc and loc, selecting first five rows and columns, combining row and column lists, and adding a new euro price column converted to USD.

Explore conditions and multi-condition logic in Python, using comparisons, booleans, and for loops to test values and prepare for an if function.

Define a Python function with def, indentation, and if/elif/else to determine a person’s age-based status, publish results with print or return, and apply to a list of people.

Apply a function to every element in a list using Python's map, convert the map object to a list, and inspect each person's status in the dataset.

Explore for loops as a core iteration tool in Python, using class list and ages to print values and replicate map function results.

Apply for loops to a function, switch from print to return, and build an output list by appending results; compare map and for loops and preview data frame usage.

Map a function on a data frame to flag UK customers by creating a boolean UK column that returns true when country equals United Kingdom and false otherwise.

Apply a for loop on a data frame to replicate mapping outputs, using head, range, and apply on rows, while noting loops are slower but useful for modeling and testing.

Master Python fundamentals for data tasks, including lists, dictionaries (keys, values, items), and pandas read_csv; subset data with iloc and loc; apply map and for loops.

Analyze a 400-car dataset in Python: compute dimensions and horsepower stats, identify the top price, and create a pricing category (budget, suitable, expensive) via a for loop.

Explore a cars dataset with pandas to determine shape, columns, and unique cylinder counts. Compute average horsepower, identify max and min prices, and flag most expensive car, Porsche 911 GT2.

Rename the car name column, create a car pricing subset, define a pricing category function for budget, suitable, and expensive cars, apply it across the data, and count the results.

Develop and automate EOQ ordering algorithm in Python to optimize supplier frequency and reduce costs. Extend the Excel-based method to a scalable Python tool that handles many suppliers and products.

Translate an Excel inventory optimization example to Python, using functions to automate tasks, and compute safety stock, average inventory, cycle service level, and fill rate.

Calculate sigma l using numpy to determine cycle service level, then apply the normal distribution with reorder point, demand lead time, and safety stock to achieve 92%.

Learn to compute the expected item fill rate by deriving expected shortage from safety stock and reorder point using the normal distribution, with Python implementation and Excel comparison.

Learn to set safety stock to achieve a target fill rate using goal seek, linking expected shortage to the service level with Excel and SciPy, and building reusable functions.

Define a Python function to compute safety stock and reorder point for a target fill rate, using lead time, mean demand, and demand sigma, with goal seek and pandas.

Explore EOQ model variations for multiple suppliers and multi-product orders, including fixed costs, milk runs, and frequency-based trucking. Implement in Python to automate inventory calculations beyond Excel.

Introduce a python-driven inventory case using inventories library and pandas data frames, identify the most frequent orders, evaluate costs, compute universal frequency, and build a program that outputs total cost.

Define variables and dictionaries for product inventory, compute EOQ per item with the inventories module, and analyze cycle time, cycle stock, and ordering frequencies for p1–p3.

Apply steps 2 and 3 of stock control algorithm to find most frequent item, pick the smallest ordering cost, and compute holding and universal costs with Python and a dictionary.

Compute the universal N (N star) for all products by looping through items, summing holding cost and demand data to form the numerator and denominator, to estimate global order frequency.

Learn to calculate total system cost in stock control and inventory dynamics, using Excel and Python, including order frequency, quantity per product, and combining purchasing, holding, and ordering costs.

Explore inventory dynamics through ABC analysis and safety stock to prioritize high-impact products. Segment by profit and volume, and apply demand-based policies to improve fill rate and cycle service level.

Explore segmentation in inventory management through ABC classifications, demand intermittency, and size mix, using classification to guide stock rebalancing, assortment planning, and targeted promotions.

Learn stock control and inventory dynamics with Excel and Python to set cycle service level or fill rates and compute safety stock.

Explore product classification for forecasting, contrasting ABC analysis variations, and use CV squared and average demand interval to identify smooth, intermittent, lumpy, and erratic demand for Crofton forecasting.

Explore inventory dynamics with excel and python, using pandas and plotly to simulate retail data, compute aggregate sales, mean price, and revenue, with a starter notebook and sample csv.

Analyze sales data with ABC dynamic inventories and Plotly visuals to reveal the long tail: many slow-moving items (C category) vs. a few high-impact A items, guiding inventory focus.

Explore category mix on multiple products by calculating sales revenue as price times sales, using ABC dynamic across stores to build a comprehensive inventory view.

Explore the simulations Excel sheet as a practical guide to inventory dynamics, covering dynamic vs static min-max, moving average forecast, demand lead time, inventory pipeline, and order up to policy.

Learn how to implement a min max dynamic inventory policy with simulation, ordering up to max when stock hits min, considering lead time, service level, and costs.

Explain the Minkyu policy with a fixed quantity, such as 40 units, and compare it to the periodic policy of ordering every ten days, considering lead time and safety stock.

Examine policy variations like periodic review, RSS policy with min and max levels, and base stock policy, and compare impact on service level, shortages, and inventory costs.

Explore moving average based recalculation to adapt min and max for dynamic inventory policies, compare forecasting scenarios, and evaluate impacts on costs, order quantities, fill rates, and cycle service level.

Assess and compare Poisson, normal, gamma, and negative binomial fits for article demand, guiding safety stock and service level decisions across article groups.

Link demand to service levels by simulating inventory across categories, mapping category mix to service levels, and evaluating policies like RS, base stock, and continuous replenishment.

Run an end-to-end inventory simulation for all articles in Python and Excel dataframes, implementing an error handler and evaluating min-max, periodic, and base stock policies to derive KPIs.

Analyze inventory dynamics by grouping KPIs across classes and policies, adjust service level, and compare costs using Excel and Python without forecasting.

Evaluate the optimal periodic review interval for bike replenishment under a periodic policy, considering inventory, ordering, lead time, penalty costs, and a 0.95 service level to maximize profit.

Analyze how different periodic review intervals affect profits, inventory costs, and shortages in stock control, showing that shorter cycles increase profit and reduce average inventory.

Explore inventory policies including the order-up-to (SES) approach and its variants, weekly or monthly reviews, and continuous policies like min-max, min-kyu, and base stock with replenishment.

Compare statistical and machine learning forecasting for inventory using sktime, testing models like ARIMA, exponential smoothing, Holt-Winters, Croston, GBM, and decision trees; forecast forward and simulate inventory for safety stock.

Learn forecasting in stock control with Excel and Python, comparing normal inventory policies to forecast-based safety stock, lead time, and weighted moving average methods.

Explore the max policy for inventory control, adjusting the big S using forecast, previous S, and sales to determine the order quantity and compensate shortages, demonstrated in Python and simulation.

Explore inventory dynamics with an Excel file showing expected, received, forecast, and backlogs. Learn to determine quantities under s, review period, ss, and base stock policies, and implement in Python.

Install the time library and prepare forecasting tools Auto.arima and KNeighborsRegressor in the notebook. Apply ABC analysis by store and article, and use train test split and make_reduction for forecasting.

Prepare data for forecasting by building an article-store key and converting to a daily period-indexed time series for plotting, then split 165 days into training and testing.

Forecast the end-of-year demand using absolute forecasting horizon with ARIMA and k-nearest neighbors, validating against y_train and y_test for inventory simulation.

Forecast all SKUs for a single store using ARIMA and regressor models, manage training and test splits, and assemble a forecast data frame while noting potential convergence issues.

Apply max policy dynamic inventory control using forecast data, lead time ten, service level 0.95, and one-step forecast; explore rolling error metrics and safety stock with plotting.

Compare knn and arima for inventory forecasting, evaluating how forecast accuracy affects demand, inventory costs, shortage costs, cycle service level, and ordering policies through simulation.

Explore how to identify demand distribution, compare models with forecast metrics like mean squared error, apply negative binomial forecasts, and use exponential smoothing on errors to improve stock control.

Prepare data for the simulation by parsing forecast keys, merging store data, and computing cycle service level per category; then simulate with two forecasting methods under the RSS policy.

Run a per category inventory simulation to compare forecasting methods and service levels using efficiency curves and grids across skus, with rss, arima, arema, and hybrid policies.

Compare forecasts for inventory management, showing Arema outperforming Cayenne across intermittent, lumpy, and smooth demand. Measure impact via cycle service level, inventory cost, safety stock, and item fill rate.

Explore seasonal inventory dynamics using the newsvendor model, calculating the critical ratio from shortage and excess costs to optimize stock and salvage strategies in Excel.

Explore seasonal products through the newsvendor model, analyzing single-period inventory decisions, salvage options, and profitability using critical ratio and data table techniques.

Identify the point of maximum profitability by modeling daily demand with a normal distribution and using a data table of ordering quantities and interval probabilities.

Learn how ordering quantities drive sales and profit by comparing inventory to expected demand and calculating profit as price minus cost.

Compute profit per event as revenue minus cost, constrained by stock and demand, and use a data table in Excel to identify the maximum profit and the quantity sweet spot.

Learn the critical ratio for stock levels by weighing shortage costs against excess costs, including salvage value and penalties. Practice with apple juice in Excel for Poisson and binomial demands.

Use the critical ratio in excel to set the optimal order quantity (Q*) under inventory decisions, based on mean and standard deviation of demand. Incorporate salvage and penalties.

Explain how the critical ratio signals the risk of stockouts versus overstock, guiding inventory levels across SKUs by costs, salvage, penalties, and service level.

Use Python to compute the critical ratio for seasonal inventory, leveraging single-period analysis with price, cost, salvage, penalty, and discount scenarios to optimize profitability.

Prepare seasonal product data to maximize profit by estimating expected demand and its standard deviation from yearly sales and price, using python and numpy for 2010–2011.

Identify and fill missing standard deviations by applying a 10% margin of error to the expected demand, creating a new Sd1 column via a dataframe function.

Apply mpn to all data by looping through items, computing expected price, cost, and penalty, and building a dataframe with descriptions via dictionary-to-dataframe conversion.

Apply the newsvendor model and the critical ratio to set stock levels for one-time seasonal buys. Balance stocking costs with the cost of losing the customer and the demand variance.

Explore how seasonal inventory dynamics use salvage value, penalties, and the critical ratio to optimize profit, applying MPn, MPP, EPN, and EP in Excel and Python.

Determine the optimal order quantity for 2012 for each product under seasonal inventory assumptions, using a 10% standard deviation and 40% of the price, following the lecture steps.

Learn seasonal inventory dynamics with Excel and Python on the 2011 data set, estimating expected demand, margin of error, price and cost, and optimizing order quantity with the NPN function.

Apply Walker's markdown model to forecast end-of-season leftovers and set timely price reductions for seasonal merchandise, boosting revenue and reducing obsolescence through promotions and space reallocation.

Explore markdown optimization for seasonal inventory using Python and Excel, forecasting end-of-season leftovers, modeling discount impacts on sales, and identifying profitable markdown levels.

Apply a practical inventory tool to improve stock control for retailers and industry. Expand inventory management through consulting and operations insights shared in this course.