Two ongoing questions are:

  1. When an organization migrates from an Excel-based central/supply/master plan is it best to go to a rule-based method or an optimization-based method?
  2. Is optimization a compass that identifies true north or is it an advanced navigation system?

For the first question, there is a body of knowledge that says making the leap from Excel to optimization is doomed to failure since it is more than an organization can absorb and simply leads to failure and repression of other analytics. My view is if there is complexity in the demand-supply network, but not too much complexity, then the better path is to use optimization methods where experts in industrial-strength optimization (ISO) are able to guide through some of the “rough patches” in implementation. For the second question, since optimization methods can at times be a bit opaque, it can leave the impression that it is just a compass. Clearly, a firm with expertise in ISO can demonstrate it is an advanced navigation system. And much like vehicle navigation systems, it will make decisions in the absence of any other information. At times these decisions will appear arbitrary or ‘wrong’ (“We would never do this” being the classic reply). An ISO expert can ensure this is corrected in quality implementation. Whether you go with rule-based or optimization, staying in Excel carries real risk.  In Excel (especially native excel) effective testing is impossible, and the code rapidly becomes very cumbersome and almost impossible to follow – even for the author.  You would not risk your business on a machine that was prone to failure and had no clear support when it breaks.  Why would you do that for your CPE?  I have and continue to write some mission-critical applications in Excel – this advice is based on personal experience.

Introduction: An ongoing conversation in the world of central/supply/master planning involves two related questions:

  1. Is it best for an organization to spend time in “rule-based” methods before tying optimization when they are migrating from “Excel” to “better” methods of central planning?
  2. What is the nature of optimization methods in central/supply/master planning?

This blog outlines my view and alternative views on each question.  No claim is made that my view is correct.

First some definitions:

What is Central Planning?  Central Planning is the control point for the flow of material within an organization and focuses on how to best meet prioritized demand and business policy without violating temporal, asset (WIP and inventory), or capacity constraints.  “Best” means being on the efficient frontier – for example, avoiding unnecessary starts or excessive inventory or limiting the number of low priority demands not met on time.

Read More: What’s the Core Purpose of Supply Chain Management?

An extended but readable discussion of central planning can be found in Chapter 14 in Planning Production and Inventories in the Extended Enterprise: A State-of-the-Art Handbook, Volume 2

What is the Central Planning Engine (CPE), solver, or model? At its core, the CPE matches assets with demand (MAD) to best meet business objectives across a demand-supply network (DSN). This requires “searching and evaluating” different options. Conceptually no different than evaluating moves in chess or bridge! The solver does this search and evaluation.

Learn more: When Optimization Is Helpful in Supply or Central Planning

What is a Demand Supply Network?  The DSN is the production and distribution flow from raw material to meeting exit demand aka your supply chain.  I am a strong advocate of Dr. Karl Kempf’s view the “chain” is best seen as a network. Let’s use the bakery example in Figure 1.  The basic players and relationships are:

  1. Bottom dwellers – purchased products (ingredients): chips, nuts, butter, flour, sugar, rum, and vanilla.
  2. Top dwellers – only exit products: Cookies (Chip and Nut) and cakes (Rum and Vanilla).
  3. Middle dwellers – in this example the only intermediate product is cookie dough which is a component product and an exiting product.
  4. Resources – mixers, ovens, fridge.
  5. Bill of Material – basics on how a product is made: ingredients, resources, process time, yield
    1. Cookies consume dough, chips, nuts, mixer_cookie, oven_cookie
    2. Cakes: consume vanilla, rum, flour, butter, sugar, batter mixer, oven_cake
    3. Dough consumes flour, butter, sugar, batter mixer, fridge; cycle time 1 full day, the shelf life of 2, shares capacity with Cakes
  6. “The great 20th-century revelation that complex systems can be generated by the relationships among simple components” applies to supply chain planning (and almost all aspects of planning, scheduling, and dispatch).


Learn More: Supply Chain Efficiency & Smart Planning Engines

What methods are there for CPE’s

  1. HGE – Homegrown in Excel – Often a firm initially handles central planning with an application developed in Excel. Since Excel is readily available, getting started is easy. One can grow in the sophistication of use, and information on how to use it is readily available. The features and functions in such CPE vary considerably from firm to firm.  Typically, any CPE developed in Excel is referred to as “The Excel” system without much awareness of the level of complexity the application is handling.  In the 1970s and 1980s, this same naming convention often applied to applications developed in APL. Today one is starting to see that with Python.  There is a certain “disdain” that some supply chain software professionals have with regards to “Excel”.
  2. Rough Cut Capacity Planning (RCCP) – The planner manually makes production starts, allocation decisions quasi-manually, and uses a reasonably simple structure to project its impact.
  3. Rule-Based, which is really Rule-Based Navigation of the Demand-Supply Network (RBN), mixes traditional Material Resource Planning (MRP) explosion with mechanisms to maintain feasibility and use rules to generate decisions
    1. Where resources or components need to be allocated between competing requirements.
    2. Where there are alternative ways to produce or ship a product
  4. Optimization Based Methods (OBM) aka “Genie Math” uses search methods from computational optimization (mixed-integer linear programming and “heuristic methods” such as local solver) to methodically identify and evaluate alternative feasible solutions until an intelligent solution is found with the ability to determine there may be no “great” solution available.

Learn How Arkieva Helps With Creating an Optimal Supply Plan

Question 1: Migrating from “homegrown” Excel-based CPE – which is the next method?

There are many professionals that believe the jump from HBE (“homegrown”) to OBM (“optimization”) is too large a leap for organizations in its journey for ever more intelligent central planning to make in one step.  The organization must first pass through either RCCP or RBN and may well find at this point OBM is not needed. For this blog, we will limit the discussion to RBN.  The argument is based on the following points:  

  1. Many organizations that are doing HBE are using incomplete data and rules of thumb to guide their decisions. In fact, they are unable to articulate all their requirements. When the optimization model is run, the response is, “We would never do that, this optimization stuff is bogus”, and return to their HBE. In fact, what happens is the requirements are never articulated.  Hence it is necessary to stage across different solvers. 
  2. The substantial core infrastructure work that is needed in this transition:
    1. Capturing a detailed description of the DSN in a computational form
    2. Capacity Available, which is a key input not usually available in your ERP system
    3. Capturing the critical nuances of your DSN
    4. Reporting and connection with other applications
    5. Compare and contrast results between RBN and HBE
  3. The user has control over key decision points on the allocation of assets by specifying rules
  4. It is far easier to understand the results of the RBN
  5. The RBN has a higher probability of being successfully installed and replace the HBE, where Gary Sullivan defines success as “cannot imagine life without this application”.
  6. Most organizations don’t have the smarts to make this leap – they will avoid using the term “smarts”

There is great wisdom in this logic and an organization should give it careful thought.  My experience is the decision should be made based on the complexity of the DSN, which generates substantial challenges to improving organizational performance in terms of being efficient.  The following is a shortlist in a presentation I did for Arkieva’s 25th anniversary titled “The Ever-Evolving Role of Optimization in Supply Planning and other areas – the path to Community Intelligence” (contact Arkieva marketing for a copy of this presentation and webinar)

Read also: Beyond the Supply Chain Maturity Model Buzz

When to use OBM

  1. If your DSN involves “shared stuff” – capacity, materials, transport
  2. If your DSN involves demand classes, sweet spots for age, date effective parameters
  3. If your DSN is large, but not too large
  4. If your DSN has conditionals, but not too many
  5. When you are far enough along in your journey – your spreadsheet is more than 3 sheets, takes 5 minutes to load

When to use RBN (or not use OBM)

  1. Less complex
  2. Very, very large
  3. Must be able to know the exact reason a decision is made
  4. Early in your journey
  5. Challenge in just finding a feasible solution – for example, lot size into lot size

The blog, How to Determine the Best-Fit Plan with Supply Chain Optimization, illustrates where rule-based methods do not do well. 

At this point, it is important to address item 1 in the logic not to go directly to OBM.  The user sees a solution and immediately says “we would never do it this way”.  This is a huge concern.  From personal experience and the experience of Industrial Strength Optimization (ISO) Experts (ISOE) and Agents of Change (AOC), this will happen. First, ISOE and AOC folks know that it is difficult for any user to simply write down all requirements.  This is not a standard IT implementation, but an ongoing investigation.  A quality team will spend time “studying” the HBE in detail and asking questions.  Second, a critical component of successful OBM is crafting the model to enable the user to provide guidance to the solver.  A simple example is the bakery model.  The firm only wants to bake one of the two cakes each day, but not both.  You can structure the model such that the solver picks which cake on which day, but you can also let the user select the days, and in fact, put in a toggle switch.  The team you contract with should also be able to warn you ahead of time that “new requirements” will emerge and make clear when this happens to the governing group without getting into the blame game.  As in surgery, you want a team led by folks with lots of experience.

Third, ISO experts and AOC will point out that rules of thumb are crafted over time and linked back to regularity of flow in the DSN.  Similar demand patterns, similar resources available, similar production flows.  When the game board changes, coming up with and evaluating new rules of thumb is a non-trivial process and letting the OBM explore with limited restrictions can help the planner identify new rules that can be institutionalized through the bookkeeping side of Industrial Strength Optimization (ISO).  There is an adage saying the purpose of a model is insight. Returning to the bakery model what if demand for vanilla cakes increases and clients want “fresh that day” cakes?  How does this work with the preference for alternative days to bake cakes?  Is additional equipment needed?  Based on demand do we make both cakes on the same day on a few days each week?  Once the exploration is completed, then new rules of thumb can be institutionalized.

Question 2: What is the nature of optimization methods in central/supply/master planning?

Some professionals maintain that optimization methods are simply a compass to find true north. 

From the movie Lincoln “A compass, I learned when I was surveying, it’ll… it’ll point you True North from where you’re standing, but it’s got no advice about the swamps and deserts and chasms that you’ll encounter along the way. If in pursuit of your destination, you plunge ahead, heedless of obstacles, and achieve nothing more than to sink in a swamp… What’s the use of knowing True North?”  There is a good bit of wisdom here.

I would contend OBM is not a compass, but an advanced navigation system that can do a spectacular job at navigating difficult situations. However, as with software that provides navigation assistance – it must decide, and in the absence of other information, it will make that decision even if it is different then my preference.  I was driving with a friend and he asked why did the navigation system make a certain decision?  It made decisions based only on certain metrics (primarily minimizing travel time) and it was indifferent to other factors.  If I am on avenue AA and need to get to avenue BB there maybe 10 cross streets to get me there.  I might prefer street 10 since it is a bit wider and the light has an arrow. The navigation system doesn’t know this.  However, ISO experts can enable your advanced navigation with this local preference.


Venturing into optimization without the right mindset and support is not advised.  There are some advantages to homegrown. I have personal experience developing homegrown models first in APL and later in Excel that has made substantial contributions.  But support and maintainability are always an issue. In Excel, especially native excel, effective testing is impossible, and the code rapidly becomes very cumbersome and almost impossible to follow – even for the author.  You would not risk your business is a tool that was prone to failure and had no clear support when it breaks.  Why would you do that for your CPE?

Enjoyed this post? Subscribe or follow Arkieva on LinkedinTwitter, and Facebook for blog updates.