Flexo Print Production Scheduling with a Spreadsheet
By Frank Burgos, FlexoExchange, Flexo Consultant/Trainer
Scheduling for a fast-paced, multi-press, multi-shift pressroom is a dynamic, challenging, and sometimes overwhelming endeavor. Even in pressrooms with one or two presses and a handful of jobs at any given time, effective scheduling can be tricky. If it were as simple as lining up orders by due date, job titles like “Production Scheduler” wouldn’t exist. Orders would simply be scheduled to print in the order they were received. The fact is that many variables must be considered in order to strike a good balance between efficiency on the pressroom floor and shipping the printed jobs when promised. Often, the most efficient job sequence is not always readily apparent.
Deadlines and “hot-rush” jobs often make it necessary to arrange jobs in sequences that are less than ideal with respect to production efficiency. An extreme example of this would be cutting in on a job in progress in order to print one that is either due very soon, or over-due. On the other hand, ensuring productivity sometimes means that jobs with due dates that are further out are scheduled to print before those with closer due dates. Grouping jobs by similar ink color, for example, may mean that due dates advance and retreat a little in sequence.
Variables other than due date and ink color also influence schedules. Shortages in raw materials can mean delays, interruptions, and changes in schedule. Conflicts with jobs requiring the same plate cylinders may force the delay of one job so that another may be printed. Substrates can be processed in so many ways that any number of criteria has to be evaluated in order to determine the order that jobs should be printed in.
The ability to neatly and conveniently view and arrange jobs on paper or a computer monitor can help make the task of scheduling much less confusing and time consuming, and there have been many tools developed for this purpose. From paper forms, to wall boards, to sophisticated software, the purpose of these tools is to facilitate sorting and grouping print jobs by criteria that makes the best sense, all factors considered.
Somewhere between paper and expensive software programs or modules is a tool that I think may be unrivaled for scheduling. It is the common spreadsheet and it is powerful! Since you’re reading this, you very likely have a PC. And if you have a PC, you either have a spreadsheet program on it, or you should! Spreadsheets are useful for so many purposes that everyone should be familiar with using them. They’re not just for accountants. For quick and easy sorting and sequence manipulation, they are unbeatable.
I happen to use Microsoft Excel as my spreadsheet program, but programs like Lotus 123 and others provide functionality similar to Excel, and the methods I am about to describe can be adapted to them. If you use a spreadsheet program other than Excel, substitute the terms and methods I use for those more appropriate for you. For this discussion, I will assume a basic understanding of spreadsheets, but I will make a note or two where I think it might help.
Among the functions that make Excel so useful for scheduling are cutting, copying, inserting, pasting, formatting, coloring text, sorting, and filtering of records. Become comfortable with these functions, and you’ll become adept at manipulating work orders on the sheet. For printing and posting of schedules, it’s also helpful to learn how to set print area, and set-up page layouts for printing. There are many other features to Excel and programs like it, such as integration with other software tools for the office, the ability to e-mail files, compatibility with databases, and so on. However, I will resist the temptation to explore those areas and limit this discussion to using Excel as a tool for scheduling on the pressroom floor.
What you’ll end up with when you’re done is a spreadsheet on which all of your print jobs can be easily seen and rearranged by whatever criteria you prefer. You’ll then be able to sort them in seconds with just a click of your mouse! Sort by Press Number, Order Number, Length, Color, Due Date, whatever you need to see at the moment.
Scheduling for a fast-paced, multi-press, multi-shift pressroom is a dynamic, challenging, and sometimes overwhelming endeavor. Even in pressrooms with one or two presses and a handful of jobs at any given time, effective scheduling can be tricky. If it were as simple as lining up orders by due date, job titles like “Production Scheduler” wouldn’t exist. Orders would simply be scheduled to print in the order they were received. The fact is that many variables must be considered in order to strike a good balance between efficiency on the pressroom floor and shipping the printed jobs when promised. Often, the most efficient job sequence is not always readily apparent.
Deadlines and “hot-rush” jobs often make it necessary to arrange jobs in sequences that are less than ideal with respect to production efficiency. An extreme example of this would be cutting in on a job in progress in order to print one that is either due very soon, or over-due. On the other hand, ensuring productivity sometimes means that jobs with due dates that are further out are scheduled to print before those with closer due dates. Grouping jobs by similar ink color, for example, may mean that due dates advance and retreat a little in sequence.
Variables other than due date and ink color also influence schedules. Shortages in raw materials can mean delays, interruptions, and changes in schedule. Conflicts with jobs requiring the same plate cylinders may force the delay of one job so that another may be printed. Substrates can be processed in so many ways that any number of criteria has to be evaluated in order to determine the order that jobs should be printed in.
The ability to neatly and conveniently view and arrange jobs on paper or a computer monitor can help make the task of scheduling much less confusing and time consuming, and there have been many tools developed for this purpose. From paper forms, to wall boards, to sophisticated software, the purpose of these tools is to facilitate sorting and grouping print jobs by criteria that makes the best sense, all factors considered.
Somewhere between paper and expensive software programs or modules is a tool that I think may be unrivaled for scheduling. It is the common spreadsheet and it is powerful! Since you’re reading this, you very likely have a PC. And if you have a PC, you either have a spreadsheet program on it, or you should! Spreadsheets are useful for so many purposes that everyone should be familiar with using them. They’re not just for accountants. For quick and easy sorting and sequence manipulation, they are unbeatable.
I happen to use Microsoft Excel as my spreadsheet program, but programs like Lotus 123 and others provide functionality similar to Excel, and the methods I am about to describe can be adapted to them. If you use a spreadsheet program other than Excel, substitute the terms and methods I use for those more appropriate for you. For this discussion, I will assume a basic understanding of spreadsheets, but I will make a note or two where I think it might help.
Among the functions that make Excel so useful for scheduling are cutting, copying, inserting, pasting, formatting, coloring text, sorting, and filtering of records. Become comfortable with these functions, and you’ll become adept at manipulating work orders on the sheet. For printing and posting of schedules, it’s also helpful to learn how to set print area, and set-up page layouts for printing. There are many other features to Excel and programs like it, such as integration with other software tools for the office, the ability to e-mail files, compatibility with databases, and so on. However, I will resist the temptation to explore those areas and limit this discussion to using Excel as a tool for scheduling on the pressroom floor.
What you’ll end up with when you’re done is a spreadsheet on which all of your print jobs can be easily seen and rearranged by whatever criteria you prefer. You’ll then be able to sort them in seconds with just a click of your mouse! Sort by Press Number, Order Number, Length, Color, Due Date, whatever you need to see at the moment.
Not all of the specifications on the work order need to be displayed on the spreadsheet, however. Only the criteria that influence scheduling decisions need to be included. Feel free to include anything else you feel might be useful as you develop your spreadsheet. Just note that the more you include, the more you’ll be required to either manually enter or import from another program. Also, it’s possible to include so much that you’ll either have to scroll left and right to see all criteria associated with a work order or you’ll need to make your view smaller to see it all.
Here are six criteria you might consider important in your decisions:
Arrange your specifications in rows, with the same specification for each order in the same column. For example, we would begin by typing the following terms in the following cells of the first row on the spreadsheet: (Or, type in whatever is appropriate for your situation)
Here are six criteria you might consider important in your decisions:
- Order Number
- Length (feet, yards, meters, units, etc.)
- Colors
- Cylinder Size
- Substrate
- Due Date
Arrange your specifications in rows, with the same specification for each order in the same column. For example, we would begin by typing the following terms in the following cells of the first row on the spreadsheet: (Or, type in whatever is appropriate for your situation)
- Cell A:1 = Press
- Cell B:1 = Order Number
- Cell C:1 = Length
- Cell D:1 = Colors
- Cell E:1 = Cylinder Size
- Cell F:1 = Substrate
- Cell G:1 = Due Date
- Cell H:1 = Comments
Make the font bold and format each cell to have a border around it. This row, and copies of it, will serve as column header for each set of orders on each press. I’ve found that this helps provide a sense of separation of the orders on each press, without disrupting the sheet too much. It allows a row of column header for sorting the work orders at any given press, and also provides column headers for each press schedule if printed out individually. For the initial set up of the sheet, we’ll start with only one header row. We’ll separate the orders after they’ve all been entered and assign them to the appropriate press towards the end.
Now, enter work order specifications matching the criteria we listed above (or on your schedule), one row per work order, until you have all work orders on the sheet. Don’t concern yourself yet with the sequence. We’ll soon begin to sort jobs according to certain criteria, relying on Excel’s sorting capabilities.
Now, if you’re using Excel 97, click on the grey rectangle where row and column headers meet to select the entire spreadsheet. Now click Data > Sort. A dialogue box pops up. Make sure the radio button next to “Header row” near the bottom of the dialogue box is selected, and sort by (Order Number) in ascending order. Your spreadsheet should now have the orders arranged by Order Number, with the lowest number at top and the largest number at bottom. This is a convenient sequence for the first few steps.
Now, if you’re using Excel 97, click on the grey rectangle where row and column headers meet to select the entire spreadsheet. Now click Data > Sort. A dialogue box pops up. Make sure the radio button next to “Header row” near the bottom of the dialogue box is selected, and sort by (Order Number) in ascending order. Your spreadsheet should now have the orders arranged by Order Number, with the lowest number at top and the largest number at bottom. This is a convenient sequence for the first few steps.
Sort Dialogue Box shown, sorting by order number.
|
After sorting, orders are arranged by number, lowest at top.
|
One of the nice features of using a spreadsheet for scheduling is that font can be color coded to indicate the status of work orders at a glance. Allow me to propose the following color scheme:
- Green = New order, status undetermined
- Red = Don’t have plates, or plates not mounted yet
- Pink = Job is Hot (Rush, late, whatever you prefer)
- Blue = There is a comment associated with this job (“substrate due in the morning”, “remove registration marks before printing”, etc.)
- Black = Everything is ready; nothing unusual about this job
Another feature you might find useful when searching through a long list is the Filter function.
Try this:
Select the top left-most cell on the sheet (it should be “Press” in this discussion). Now click on Data > Filter > AutoFilter on your menu bar. You should see little grey boxes with downward pointing arrows to the right of each cell in row 1. (If some of the headings become partly obscured by the Filter buttons, simply “Auto-Format” the column width. See appropriate Help Section in Excel to learn how to do this.)
See what happens when you click on them and select items from the pull-down lists. Experiment for a while to get a feel for how to use “Filter”. This feature can help you find things in a hurry. Note that when you select from the pull-down menus, all but those orders matching the selected criteria disappear. Also note that the arrow on the selected menu button becomes colored, indicating that the list is filtered by at least that criterion. You may filter by multiple criteria, each time rendering the list of orders down to shorter lists. Click Data>Filter>Show All to bring them back, or select the top choice “All” from the pull down lists involved in filtering. To remove the filters altogether, click on Data > Filter > AutoFilter again, and the buttons disappear.
(You may find it very useful to create a custom toolbar for scheduling, and create buttons that turn filters on and off, as well as other frequently used functions.)
Every pressroom has a unique set of requirements and circumstances that make it impossible to generalize scheduling criteria for. What I’ll do here is run through a few simple steps using some of the features of Excel just to demonstrate some of what can be done. It’s then up to you to apply the methods to your particular situation.
If we make the following assumptions:
To do this, select the header row and all of the rows containing work orders (see “Help” in Excel). Click on Data > Sort and again make sure that the “Header row” option is selected. Based on the above assumptions, sort first by Colors, then by Cylinder Size and then by Substrate. Click on OK. Your orders should now be sorted into groups that are beginning to make sense. If you like, select grouped rows and sort by date. What you’ll end up with is efficient groups of work orders, sorted by date within each group. Fine-tune the sequence by moving individual rows or small groups of rows, if necessary. This brings me to another two Excel functions: Cut and Insert Cut Cells. I’ll demonstrate these functions to create separate press sub-schedules, and you can then use the methods to fine-tune the schedule. Let’s create a separate section for each press.
Say you have three presses to schedule for (your situation may vary). For this, we will create a total of four column header rows; one for each press, and one for our “reserve” or “staging” section, which is where orders reside until we’re ready to assign them to a press. The rule here is to create one more header row than the number of presses you are scheduling for.
Select the three rows immediately under the top column header row. Now, right-click anywhere on the darkened selection and click on Insert. You should have three new rows. Select the top column header row, right-click on it and click on Copy. Select the three blank rows you created, right-click on the selection and click on Paste. You should now have a total of four column header rows that look identical to each other. We’re almost there.
(You may find it very useful to create a custom toolbar for scheduling, and create buttons that turn filters on and off, as well as other frequently used functions.)
Every pressroom has a unique set of requirements and circumstances that make it impossible to generalize scheduling criteria for. What I’ll do here is run through a few simple steps using some of the features of Excel just to demonstrate some of what can be done. It’s then up to you to apply the methods to your particular situation.
If we make the following assumptions:
- Changing ink colors is more time consuming than changing cylinder sizes.
- Changing cylinder sizes is more time consuming than substrate changes.
To do this, select the header row and all of the rows containing work orders (see “Help” in Excel). Click on Data > Sort and again make sure that the “Header row” option is selected. Based on the above assumptions, sort first by Colors, then by Cylinder Size and then by Substrate. Click on OK. Your orders should now be sorted into groups that are beginning to make sense. If you like, select grouped rows and sort by date. What you’ll end up with is efficient groups of work orders, sorted by date within each group. Fine-tune the sequence by moving individual rows or small groups of rows, if necessary. This brings me to another two Excel functions: Cut and Insert Cut Cells. I’ll demonstrate these functions to create separate press sub-schedules, and you can then use the methods to fine-tune the schedule. Let’s create a separate section for each press.
Say you have three presses to schedule for (your situation may vary). For this, we will create a total of four column header rows; one for each press, and one for our “reserve” or “staging” section, which is where orders reside until we’re ready to assign them to a press. The rule here is to create one more header row than the number of presses you are scheduling for.
Select the three rows immediately under the top column header row. Now, right-click anywhere on the darkened selection and click on Insert. You should have three new rows. Select the top column header row, right-click on it and click on Copy. Select the three blank rows you created, right-click on the selection and click on Paste. You should now have a total of four column header rows that look identical to each other. We’re almost there.
If you’re building your actual schedule as we go, you already have jobs assigned to certain presses. Start by selecting the row(s) corresponding to the press you’ll have at the top of your schedule. Right-click on the selection and click on Cut. Select the second row of column headers, right-click on it and click on Insert Cut Cells. The Press 1 order(s) should now be under the first row. Do the same for the order(s) for Press 2, Press 3, and so on, each time selecting, cutting, and inserting cut cells. You should end up with at least one job scheduled for each of your presses, and the rest of the jobs at the bottom, which I like to call the “reserve” section.
Note that we cut, rather than copy, so that we don’t accidentally leave behind an order and end up with duplicates. Also note that we inserted rather than pasted the orders into place. By pasting we may accidentally paste over existing records. Inserting displaces, rather than replaces, records.
Using the techniques outlined here, experiment with sorting, cutting, and inserting cut cells. Sort by different criteria and study the results. You can select and cut more than one row at a time. Play with the schedule until you end up with all jobs scheduled on a press, or purposely held in reserve.
All that’s left is to print out a master schedule and sub-schedules that can be posted at the press, if you like. Rather than detail how that’s done here, let me invite you to teach yourself how set print area and set up a page layout for printing by using “Help” in Excel if you’re not already comfortable with the print functions.
You will want to familiarize yourself with formatting cells. For example, if you have work order numbers or product identification numbers that have zeros at the left, you’ll want to format the column they’re in as “Text” so that the zero is not dropped. If you include either units or yards to print, you’ll want to format those columns as “Number” to however many decimal places you like before entering numerical data. This will allow you to use the numbers in calculations and sum the total of two or more jobs, if you like.
In the pressroom I manage, I use the above spreadsheet methods combined with a little help from an MS Access tool I’ve developed to schedule for 14 presses, 3-shifts, 33 operators. We print an average of 100-120 work orders a week, with anywhere from 200-300 work orders on our plate at any given time. If you don’t currently have an effective scheduling method in place, or if your not satisfied with your current method, I encourage you to try what I’ve described here. We’ve seen an increase in production of 25%-40% since refining these methods, and I can’t tell you how often operators comment on how the way jobs are lined up is so much friendlier than in the past. The key lies in the increased visibility of work order specifications, and the ease with which records (orders) can be manipulated. If you can’t see efficient patterns or combinations, it’s unlikely you’ll routinely group by them. And for facilitating the identification of these combinations, you just can’t beat a spreadsheet.
If you need help setting this up and training your pressroom employees how to implement and use it, give us a call or send an email. Frank Burgos is fluent in English & Spanish with more than 30 years experience in the flexographic printing industry. Put his knowledge and know-how to work in your shop!
Frank Burgos, Flexo Industry Consultant/Trainer
Phone: (336) 327-4404
E-Mail: frankb@flexoexchange.com
© 2000; FlexoExchange, LLC; All Rights Reserved
Using the techniques outlined here, experiment with sorting, cutting, and inserting cut cells. Sort by different criteria and study the results. You can select and cut more than one row at a time. Play with the schedule until you end up with all jobs scheduled on a press, or purposely held in reserve.
All that’s left is to print out a master schedule and sub-schedules that can be posted at the press, if you like. Rather than detail how that’s done here, let me invite you to teach yourself how set print area and set up a page layout for printing by using “Help” in Excel if you’re not already comfortable with the print functions.
You will want to familiarize yourself with formatting cells. For example, if you have work order numbers or product identification numbers that have zeros at the left, you’ll want to format the column they’re in as “Text” so that the zero is not dropped. If you include either units or yards to print, you’ll want to format those columns as “Number” to however many decimal places you like before entering numerical data. This will allow you to use the numbers in calculations and sum the total of two or more jobs, if you like.
In the pressroom I manage, I use the above spreadsheet methods combined with a little help from an MS Access tool I’ve developed to schedule for 14 presses, 3-shifts, 33 operators. We print an average of 100-120 work orders a week, with anywhere from 200-300 work orders on our plate at any given time. If you don’t currently have an effective scheduling method in place, or if your not satisfied with your current method, I encourage you to try what I’ve described here. We’ve seen an increase in production of 25%-40% since refining these methods, and I can’t tell you how often operators comment on how the way jobs are lined up is so much friendlier than in the past. The key lies in the increased visibility of work order specifications, and the ease with which records (orders) can be manipulated. If you can’t see efficient patterns or combinations, it’s unlikely you’ll routinely group by them. And for facilitating the identification of these combinations, you just can’t beat a spreadsheet.
If you need help setting this up and training your pressroom employees how to implement and use it, give us a call or send an email. Frank Burgos is fluent in English & Spanish with more than 30 years experience in the flexographic printing industry. Put his knowledge and know-how to work in your shop!
Frank Burgos, Flexo Industry Consultant/Trainer
Phone: (336) 327-4404
E-Mail: frankb@flexoexchange.com
© 2000; FlexoExchange, LLC; All Rights Reserved