Closed-Loop Supply Chain Optimization in Excel Solver | Greentek Case

How can Excel Solver connect customer deliveries, product returns, and recycling decisions in one optimization model? In the Greentek case, Professor Peter Wanke demonstrates how to select facilities, allocate deliveries, and route returned electronics while minimizing modeled logistics costs. This video complements Lesson 4 — Closed Loop Supply Chain Design — from the Green Supply Chain Management course developed for FGV’s Master in Management. Using the original workbook layout and actual Excel Solver demonstrations, you will learn how to: • Connect forward shipments to customer return rates. • Use binary variables for facility-opening decisions. • Enforce demand, capacity, and facility-activation constraints. • Balance recycling dispatches against returns at each facility. • Configure Solver and interpret its optimal solution. • Reoptimize after changes in return rates or customer demand. Two sensitivity tests show why higher logistics cost does not necessarily require a different facility configuration: doubling C4’s return rate and increasing C3’s demand by 20%. We discuss spare capacity, alternative optimal recycling allocations, and the assumptions behind the results. CHAPTERS 00:00 Greentek: connect forward deliveries and product returns 00:47 Three candidate facilities: fixed costs and capacity 01:34 Demand and return rates are different inputs 02:24 Route costs make customer assignments economically different 03:17 Forward shipment quantities are Solver decisions 04:07 Opening decisions connect fixed cost to network design 04:52 Production capacity and facility activation work together 05:38 Demand equality prevents artificial extra deliveries 06:28 Return quantities are formulas derived from deliveries 07:16 Recycling shipments must balance at every facility 08:03 Recycler capacity and the scope of the circular model 08:57 Read all four cost components and the recycling multiplier 09:47 Solver: decision ranges, balances and binary restrictions 10:40 Solve the corrected base model 11:28 Base costs, return balances and alternative recycling routes 12:23 Scenario one: double the return rate from customer C4 13:08 Higher returns increase cost without changing facility selection 13:59 Scenario two: increase C3 demand by twenty percent 14:47 Demand growth: costs, returns and network stability 15:37 Restore the base model and identify the next business data EDUCATIONAL CONTEXT Greentek is an illustrative teaching case. Costs and quantities refer to a common planning period. Scenario results are conditional model estimates, not observed company outcomes. The walkthrough uses a validated teaching copy with two corrected facility-level recycling balance formulas; the original source is preserved. It retains the supplied workbook’s recycling-leg cost multiplier of two and its assumption that returns travel back through the supplying facility at the corresponding forward unit tariff. These assumptions require confirmation before real-world use. The objective minimizes monetary cost. The model does not explicitly quantify carbon emissions, recovery yields, processing costs, recovered-material revenues, or recovered material replacing new production. Professor Peter Wanke Business Analytics and Economics #GreenSupplyChain #ExcelSolver #ClosedLoopSupplyChain #ReverseLogistics #OperationsResearch