At the recent CSCMP Global Conference in Denver, the 2nd day ended with a panel on network design. The 30 or so attendees were approximately split between network design professionals and companies who might need their services.
Today, I thought I'd elaborate on an interesting topic posed by someone from a company young in network design. She asked "Why do I need a fancy network design tool? I have some SQL-savvy guys and they have Excel." Some network design professionals may scoff at this question, but I think this is a great topic to explore.
So why should a network design team grow into using a network design tool instead of using Excel spreadsheets? Well, maybe you shouldn't. I know network design pros that don't believe in using commercial applications. They feel that a combination of Excel and some add-on tools for geocoding, distance calculations, maybe a linear programming solver, etc. is more flexible and quicker than a commercial tool. I wouldn't go this far myself, but there is a time and place for Excel analysis.
Several years ago, I built an Excel model and associated Mappoint project to determine the optimal location of local delivery centers for a major metro area. I was able to try various combinations of 1-2 delivery centers using my Excel model to evaluate the trade-offs between delivery cost and real estate cost. For example, is it better to site the delivery center in a low-rent real estate neighborhood and have more "windshield" time, or in the transportation-optimal, but higher-cost real estate area? As long as the need was for 1-3 local delivery centers, the Excel model was adequate.
The benefits of Excel modeling are:
So what's not to like about Excel? Excel breaks down in some areas:
Next blog entry, I'll dig into this last point in some more detail...
Also posted as a guest blogger at UltraShipTMS Supply Chain Collaborator blog.
Today, I thought I'd elaborate on an interesting topic posed by someone from a company young in network design. She asked "Why do I need a fancy network design tool? I have some SQL-savvy guys and they have Excel." Some network design professionals may scoff at this question, but I think this is a great topic to explore.
So why should a network design team grow into using a network design tool instead of using Excel spreadsheets? Well, maybe you shouldn't. I know network design pros that don't believe in using commercial applications. They feel that a combination of Excel and some add-on tools for geocoding, distance calculations, maybe a linear programming solver, etc. is more flexible and quicker than a commercial tool. I wouldn't go this far myself, but there is a time and place for Excel analysis.
Several years ago, I built an Excel model and associated Mappoint project to determine the optimal location of local delivery centers for a major metro area. I was able to try various combinations of 1-2 delivery centers using my Excel model to evaluate the trade-offs between delivery cost and real estate cost. For example, is it better to site the delivery center in a low-rent real estate neighborhood and have more "windshield" time, or in the transportation-optimal, but higher-cost real estate area? As long as the need was for 1-3 local delivery centers, the Excel model was adequate.
The benefits of Excel modeling are:
- Anyone who does analysis - financial, supply chain, transportation - knows how to use Excel and is equipped to build a model
- Excel models are quick to build and unless they use obscure Excel functionality, anyone can understand them
- Nearly every employee of every significant company has Excel on their desktop. Commercial network design tools can cost $100k or more to purchase.
- If you have a small number of on/off decisions (1-2, maximum 3), Excel may be your quickest, most practical choice.
So what's not to like about Excel? Excel breaks down in some areas:
- There's an art to building Excel models and there are no standards. Give 5 people a business model to build and you’re likely to get 5 completely different Excel models
- Building and evaluating “what-if” scenarios can be challenging because it's difficult to build a scenario framework in Excel. You typically end up having multiple sheets or workbooks to represent different scenarios. Consider how hard it will be to maintain 15 nearly (but not exactly!) identical Excel models, one for each “what-if” scenario.
- But the following are two significant reasons you may want to consider moving to a commercial network design tool at some point.
- Excel models do not easily allow you to consider facility throughput capacity. Let's consider that local deliver center example again. We can pretty easily build an Excel model for a metro area and give the model the ability to choose the closest delivery center for each customer. Sum up the customer deliveries for each delivery center and you have the projected facility throughput. Uh oh... what happens if the sum for one of the facilities is greater than what the existing or potential facility can handle? Play around with this a bit if you don't believe me, but in Excel, it becomes - choose your term - challenging or painful - to try to model capacity on facilities. "For this customer, choose the closest delivery center, except when delivery center is over capacity, in which case consider whether this customer is closer to the delivery center than other customers..." …you get the picture. You are out of Excel's league for most capacity-related analyses.
- Excel is DESCRIPTIVE. Commercial network design tools are PRESCRIPTIVE. Excel will describe what the output will be given a set of inputs. Commercial network design tools will take your input costs, network structure and constraints, and recommend the optimal product flow path, facility locations, and other network opportunities. This is where commercial network design applications can really drive value. Because they're based on a math model of the network, their optimization isn't dependent on people's ideas, which can be limited by the way things have always been done.
Next blog entry, I'll dig into this last point in some more detail...
Also posted as a guest blogger at UltraShipTMS Supply Chain Collaborator blog.
RSS Feed