ISE Magazine –Volume 49: Number 05
By Robert W. McClure
One of an industrial engineer’s most important duties is to provide manufacturing floor supervisors with the tools they need to maintain their areas productively and efficiently. These tools include access to solid work measurement data that will assist in their day-to-day operations. Interactive spreadsheets are an excellent method of achieving this objective. These spreadsheets must be easy to use and give a complete picture of the operations necessary to produce the requirements.
Setting up an interactive spreadsheet requires several important steps. And although it should be an easy task, there is more to it than one would think.
The spreadsheet examples displayed in the figures of this article required significant legwork to accomplish. They provide information for a mixedmodel line of surgical seals and obturators used in abdominal surgery. The work is carried out in a clean room environment. It is a mix of touch labor and mechanical tools and fixtures.
The first step is to study the work with intense time study so that you will have a good sound base of standards that will go into the spreadsheet. If you miss operations and their details, the data may be useless to the supervisor. Get enough data that you can assume a 90 percent to 95 percent confidence level in the numbers. Proper time study technique should be used in that the operation descriptions should be as accurate as possible and in the proper sequence of events.
There are a couple of options in this part of the analysis. If you are proficient at it, pace-rated (leveled) time study is a good way to go. If you are trained in predetermined times, you can observe on the shop floor and then determine the motion patterns that will give you a good methods description and time for each operation. Predetermined times are also the method that you must use if the product is not yet in its standard configuration. In that case, you might have to observe trial runs and mentally think through the methods involved, then adjust later as the product comes to production status. Another possibility is the use of video recording to record the data. In any case, you still will need considerable analysis time to accomplish the task.
This is a good time to make certain that the operators can actually carry out their manufacturing instructions. Many times, the engineer who designed the operation does not do an adequate job of describing the work with understandable language that the floor personnel can decipher.
This also is a good time to determine if they have added shortcuts that are not in the manufacturing instructions. Most shops experience creeping change over the life of the assembly lines, and sometimes the operations are completely different from the documented instructions.
Aside from the obvious detail required in the body of the spreadsheet, the sheet should have a summary table giving the reader an overall look at what the line must produce and the labor hours required to accomplish that task. The summary also is required to hit the schedule in terms of the takt time and the equivalent number of people. These calculations are all built into the spreadsheet so that when the required weekly volumes are input based on what is scheduled, the sheet will automatically give the correct information.
Each column on the spreadsheet should yield understandable data. The first column is a description of the manufacturing actions required to produce this particular assembly.
The actions should be in clear and understandable terms that are commonly used in that shop. The second column indicates the time in terms of standard minutes. This is the better understood time for engineers using decimal-minute watch equipment to record their times.
The next column shows the number of seconds to accomplish the action. This is the number understood by most supervisors, and, if they choose to check an operation, it can be their benchmark even if they check it with a wristwatch. The next column is the bare operation time. This time is used for line balancing and head count calculations. The next column has a heading input cell for putting the number of weekly units required. Below that cell, the column contains the calculated head count (H/C) required for that product’s volume. The last column contains the takt time required for this product’s production run.
This table has a column for each model number being produced. Under each model number are the weekly hours required for each model produced on this line. In the third line down, the first cell has the total line hours needed. The table also includes the equivalent head count, which shows the supervisor the number of workers needed for those hours. The last line in the table gives the weekly volume and the overall takt time needed to maintain the line flow. This is information that the supervisor can use for planning and analysis purposes.
The sheet works in the same manner. First, it gives detailed information as well as a summary line for the overall production and an overall takt time for the required flow of products off the line.
In this spreadsheet, the data integrity must be protected against unauthorized changes that could compromise its usefulness. Some people will change the numbers just because they don’t think they look good. The only cell that needs to be interactive for the supervisor is the weekly requirements cell; all others should be locked. Each week’s production requirements can be saved for future reference by the supervisor so that products can effectively be analyzed. It’s a win-win for everyone.