{"id":12446,"date":"2015-02-11T00:00:00","date_gmt":"2015-02-11T05:00:00","guid":{"rendered":"https:\/\/centricconsulting.com\/post\/how-to-conduct-a-diy-network-analysis-to-understand-how-location-impacts-service-and-transportation-expense\/"},"modified":"2023-10-23T09:03:27","modified_gmt":"2023-10-23T13:03:27","slug":"how-to-conduct-a-diy-network-analysis-to-understand-how-location-impacts-service-and-transportation-expense","status":"publish","type":"post","link":"https:\/\/centricconsulting.com\/blog\/how-to-conduct-a-diy-network-analysis-to-understand-how-location-impacts-service-and-transportation-expense\/","title":{"rendered":"How to Conduct a DIY Network Analysis to Understand How Location Impacts Service and Transportation Expense"},"content":{"rendered":"

Useful ideas for maximizing the power of Microsoft Excel for supply chain analysis, Part I<\/h2>\n

This is the first post in a three-part series that describes how to perform\u00a0desktop network optimization, job scheduling<\/a> and vehicle routing<\/a> with nothing but Microsoft Excel.\u00a0<\/em><\/span><\/p>\n

Introduction<\/h3>\n

Performing a network analysis is one of the bread and butter tasks for a supply chain consultant. Typically, it takes specialized software, weeks of working with the data going into to the software, and another couple of weeks working with the data that comes out of the software.<\/p>\n

The most valuable pieces of information this analysis yields are warehouse locations and approximate transportation spend. However, it can also be extended to understand the classic cost – service tradeoff that results from the cost of placing and operating more warehouses closer to customers and the service benefits they receive because of faster response times.<\/p>\n

Given that warehousing and transportation typically accounts for 60 percent to 90 percent of logistics cost, maintaining a solid understanding of the relationship between location and transportation expense is always a valuable exercise.<\/p>\n

The problem is that there is seldom the time or the budget to retain a consultancy to do even a small network analysis. Instead, there may only be two weeks, between other duties, for an in-house supply chain analyst to develop a directionally correct, 80 percent solution that could be understood, vetted, and evaluated by a larger audience. This is really like asking if there is a way to do this in Microsoft Excel.<\/p>\n

Getting Started<\/h3>\n
\n

As of Microsoft Office 2010, the answer is yes, this can be done in Microsoft Excel, because of the inclusion of a new algorithm in the Solver: the Evolutionary Solver. Without going into too much detail, this algorithm is excellent for finding solutions to non-smooth, non-continuous, optimization problems. For those with experience using the Excel Solver, you know setting up a problem is as much science as craft. This is no different. The data that is required to perform this analysis is:<\/p>\n