How to Build a Supply Chain Model in Microsoft Excel

Name: How to Build a Supply Chain Model in Microsoft Excel
Rating: 3.6 (132 reviews)

Learn how to develop an end-to-end multi-echelon supply chain model in Microsoft Excel.

Created byEric Moore

Last updated 7/2015

English

English [Auto],

What you'll learn

Understand & develop the supply chain conceptual model
Understand the static & dynamic data required for the model
Develop a monthly bucket supply chain model in Microsoft Excel
Understand inventory coverage calculations
Understand multi-echelon supply chain modeling

Course content

3 sections • 16 lectures • 1h 55m total length

Course Overview & Description of the Conceptual Model4:53
Modeling a Single Finished Good at a Single Location15:35
Learn to build a single finished good model in Excel, with a macro for periods of cover, inventory balance, receipts, and replenishments at one distribution center.
Modeling Multiple Finished Goods at a Single Location17:57
Learn to model multiple finished goods at a single location in Excel, using forecasts, inventory balance, receipts, period of cover, and tablet-based aggregation to drive replenishment.
Balance Equation to Check Model's Integrity3:50

Start of a Multi-Echelon Supply Chain Model10:03
Set up a blank Excel workbook, apply a macro for months of cover, and model multi-echelon demand, safety stock, starting inventory, and receipts across three markets.
Inventory Valuation & Common Unit of Measure Modeling11:29
Modeling Finished Goods at a Second Location13:24
Model finished goods across the UK and Germany with a scalable excel setup, implement inventory balance checks, apply standard costs, and forecast demand to update regional inventory values.
Modeling a Packaging Site13:55
Build a next-level packaging site model in Excel by applying a two-month lead time, start-to-complete offsets, and linkages to the distribution center, while balancing starting inventories and testing results.
Determine Inventory Coverage at the Packaging Site4:55
Compute months of inventory cover at the packaging site by combining packaging and distribution center stock against 12-month demand, using the period of cover function to reach steady state.
Modeling a Second Packaging Site11:19
Determine Inventory Coverage at the Second Packaging Site3:37
Compute inventory coverage for the second packaging site in a Microsoft Excel model by aggregating 12-month and 6-month demand, subtracting distribution center stock, and stabilizing at two months of cover.

Requirements

Basic experience using Microsoft Excel

Description

A detailed demonstration of the development of a multi-echelon supply chain model in Microsoft Excel is provided. First, the conceptual model for the supply chain is explained and developed in Microsoft PowerPoint. The monthly bucketed supply chain model is then developed in Microsoft Excel starting with a blank workbook. Inventory coverage, inventory value, replenishment planning, production planning, etc is covered at multi-levels of the supply chain. The student should leave the course with an understanding of the key requirements for modeling a supply chain as well as how to create a model in Excel.

Who this course is for:

Supply chain professionals
Professionals in manufacturing industries looking to learn more about supply chain management
MBA students
Operations students

How to Build a Supply Chain Model in Microsoft Excel

What you'll learn

Explore related topics

Course content

Modeling a Single Location4 lectures • 42min

Modeling Multiple Locations7 lectures • 1hr 9min

Additional Resources5 lectures • 5min

Requirements

Description

Who this course is for: