Battle Royale: Excel vs. Network Design Software

What’s the difference between tried and true network design performed using Microsoft Excel and design performed using more recently developed network design tools? To put it succinctly, Excel is best for producing “descriptive” models whereas network design tools are better at building “prescriptive” models.  What does this mean and why does it matter? 

Excel is a very good and fancy calculator – it is descriptive.  Set up a number of formulas, enter input values and you will get output values.  Change the input values and recalculation will give you new output values.  Yet, an Excel model will never suggest what you should do to improve your network.  It will never tell you where to put a new local delivery center or distribution center.

Commercial network design tools are all prescriptive.  That is, they take input costs, network structure and constraints and use a math solver to decide how an optimal network should look.  Given the network’s structure, costs and constraints, the design tool can evaluate all the trade-offs between different combinations of flows.

As an example of how a prescriptive model works, consider a retailer with a network of stores across the US.  Imagine the retailer currently has three distribution centers – New Jersey, Atlanta and Southern California – and they’re growing.  Let’s also imagine these distribution centers are at or near capacity.  Clearly, the retailer needs another.  Where should they put it?

We could build an Excel spreadsheet – a descriptive model.   We’d create a table of current vendor and import locations as well as formulas that compute the inbound/outbound transportation costs, the fixed and the variable facility costs.  This would be our “baseline” model.  Next, we look at where to introduce a new distribution center.  How about Indianapolis?  We adjust the formulas to assign some stores to Indianapolis, perhaps by state or metro area and see the resulting cost.   Perhaps we locate the new DC in Chicago instead?  How about closing Atlanta and going with Dallas and Detroit?  In each case, we can adjust the formulas and get the resulting cost.  As the number and type of changes increases, the formulas become increasingly complex and hard to maintain.  And in each case, we are telling Excel what to do and Excel’s telling us the resulting cost.

Or, we could build a model using a network design tool.  With these tools, we don’t build formulas as in Excel.  We populate provided tables describing the contours of our network – some tables hold vendor, import, distribution center and customer locations while other tables contain product details, flow possibilities, capacity constraints and, of course, cost.  With these tools, we can introduce a new DC site in Indianapolis just as we did with Excel.  But, instead of us specifying states or areas to be served by the new distribution center, the solver considers all possibilities and selects the lowest cost alternative.  That is, the solver prescribes what we should do given this new alternative.  It may even tell us not to introduce any new DC if the overall cost structure doesn’t support doing so.

Taking this a step further, we can much more easily consider combinations of sites.  Instead of inventing clever new potential arrangements of distribution centers, we could pass the solver 20 or more potential sites, each with its own fixed and variable cost structure, and allow the solver to select how many and which candidates to use.  We can even allow the solver to decide whether any of our existing sites should be replaced with new sites in different locations.  While we were able to consider 1-2 combinations adequately, if not elegantly or optimally, with Excel, once we get beyond 2 decisions, we really need the power of true optimization.  Using an optimization model, we can also rigorously consider capacity and fixed costs associated with providing different throughput capacity.

You could potentially develop a prescriptive model like this with Excel Solver, but unless you’re really after academic enrichment, consider employing a commercial network design tool. These tools shield us (as the practitioners) from the math so that we can focus on the business aspects of the design exercise.  They also provide considerable value with tools for geocoding, road distance calculations, standardized reporting & output and scenario management.

Sara Curtis Ms. Sara Curtis, principal at Peak 5 Supply Chain, has been a product manager, business unit director, consultant, trainer and subject matter expert for i2/JDA Supply Chain Strategist and LLamasoft Supply Chain Guru. Through this work, she has consulted with all types of supply chain entities, developing robust models & repeatable processes in supply chain & transportation network design.

Leave a Reply

Your email address will not be published. Required fields are marked *