{"id":12626,"date":"2015-02-18T00:00:00","date_gmt":"2015-02-18T05:00:00","guid":{"rendered":"https:\/\/centricconsulting.com\/post\/perform-diy-production-scheduling-make-production-assets\/"},"modified":"2022-03-24T13:07:21","modified_gmt":"2022-03-24T17:07:21","slug":"perform-diy-production-scheduling-make-production-assets","status":"publish","type":"post","link":"https:\/\/centricconsulting.com\/blog\/perform-diy-production-scheduling-make-production-assets\/","title":{"rendered":"How to Perform DIY Production Scheduling to Make the Most of Your Production Assets"},"content":{"rendered":"

Useful ideas for maximizing the power of Microsoft Excel for Supply Chain Analysis, Part II<\/h2>\n

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

Introduction<\/h3>\n

In the\u00a0previous post<\/a>, performing a desktop transportation\/ network optimization was discussed. This was an interesting class of problems that produced valuable information and insights regarding the cost and service components of a distribution network. These problems require a good bit of pre-work related to data gathering and analysis, but the worksheet setup is fairly straightforward, perhaps even intuitive.<\/p>\n

The job scheduling problem requires less pre-work, but the set-up is more complicated and less intuitive. This makes it more difficult to explain\u2026at least in my mind. However, the benefits are much more tangible. If organized properly, the solution methodology can be used several times per day to schedule and re-schedule work. Further, analyzing the results will reveal the location of bottlenecks and opportunities to streamline operations.<\/p>\n

At the simplest level, the objective of these problems is straightforward: schedule production to get as much work done as possible in the shortest amount of time. However, we will modify the objective slightly to make the problem more realistic. Production orders will be organized\u00a0such that groups are completed at approximately the same time. Think of this as a way to have specific production orders completed by a certain cut-off time or cut-off date.<\/p>\n

To solve this problem, leverage the idea that solving an optimization problem consists of two distinct but connected parts: problem set-up and solution search.<\/p>\n

Like my other post, Microsoft Excel will be the tool for problem set-up and solution search. However, since this class of program does not lend itself to searches based on linear programming techniques, we will once again use the Evolutionary Solver in Excel (released in version 2010) to search for the solution.<\/p>\n

Characterizing the Problem<\/h3>\n

In this situation, pretend that you are the manager of a value-added service (VAS) operation. You are responsible for scheduling the jobs through one of eight VAS processing lines. Your lines all have the same capabilities. However, because of differences in equipment, space, experience, and leadership, some of the lines can process work faster than others. You know what these process speeds are and would like to include this information when developing schedules. The company you work for has national contracts and every day you ship completed work all over the United States. Lately, business has been good but this has resulted in a few problems:<\/p>\n