Production: Excel is the Swiss Army Knife for Production Metrics

by: W. Eric Schult

Production: Excel is the Swiss Army Knife for Production Metrics

Were it not for the elegant use of a tired-old instrument of measurement and reporting, I’d still be banging my head over why my packaging center couldn’t seem to notch up sustainable improvements in productivity. Instead, that team is consistently posting 15 to 20 percent gains— and climbing—in its overall net throughput speeds since the launch of an initiative last fall. Goals that were—at the time—widely thought unachievable are within grasp eight months earlier than the most optimistic of us had predicted.

These are savings I gladly lay at the feet of the machine leads, mechanics and inserters who made it happen, and their department manager who showed them how. For my part, I was merely on the sidelines, waving my Excel spreadsheets like pom-poms.

I’ve been a productivity zealot for most of my newspaper career and am an evangelist for the unorthodox use of Excel as the Swiss Army knife of any good production/technology executive.  Seventeen years ago this month, my first byline published in E&P was about this very subject, and it’s never been more important than it is today—with the imposition of extreme process and expense control made necessary by pressures on the industry—to make the best use of our available tools.

Three newspapers and half a career later, I’m still finding novel ways to create and deploy spreadsheet tools that would make many controllers’ heads spontaneously explode. One of my former CFO colleagues—otherwise a power user with enviable Excel authoring skills—liked to refer to some of my more complicated time-management workbooks as “voodoo.” What I’m describing, though, is neither cultish pseudoscience nor a technological specialty conceptually beyond anyone who isn’t a spreadsheet savant.  Some of the concepts are simply alien to anybody who hasn’t had the need to dig into, for example, how to calculate elapsed time when a production run starts before midnight and ends after.


Excel in productivity

For a useful productivity initiative, the trick is in combining an understanding of what performance metrics would be useful to capture, figuring out a low-maintenance and accurate way to capture them, and then embellishing the reporting to provide statistics and trends while the information is still fresh, relevant and actionable. Posting yesterday’s performance and a running tally for the month ought to be about as easy and rewarding as retrieving your first cup of coffee in the morning. If you’re communicative that early—without your caffeine infusion fully absorbed—it makes for a worthwhile conversation starter with your production leads, too.

A pet peeve of mine—and I’ve seen this to some extent at all of the larger newspapers where I’ve worked—is the prevalence of emailed end-of-shift reports that represent a barrage of information: often redundant, sometimes contradictory, frequently absent any meaningful context, and always in danger of being accidentally shunted, unread, into a folder where emails go to die. If your plateroom, pressroom, packaging center and distribution teams are all keyboarding, press start and finish times independently from different clocks that don’t agree; if none of their reports provide a complete story about what transpired across all production departments on a given shift; and worst of all, if it’s a separate, manual effort to consolidate the statistics from individual shifts into a summary report that tracks history and identifies trends, you should at least be relieved to know you’re not alone, and that you have a huge potential for improvement.

Scratch the surface, though, and you’ll also probably find that the author of your end-of-shift reports didn’t know how to write logical cell functions in Excel, so your shift leaders are manually entering whether a deadline was missed or met. They may be mentally calculating and manually entering how many minutes late they started or finished a run, for want of a simple time formula to calculate that detail for them. The truly conscientious and most well-meaning among them may be highlighting workflow deviations with colorized cell backgrounds or bolded text for emphasis, when that is also a very simple feature in Excel (conditional formatting) used to enhance the application’s value as a reporting tool.

If the very effort of reporting detracts from shift leaders’ attentiveness to what’s occurring on the production floor, you’re missing out on the true value provided by Excel.


Excel in packaging

I offer by way of example a packaging end-of-shift report (Figure 1). I modified and modified again so that even though I added data entry points for setup times and gross pieces, the work necessary to fill out the form was reduced. Pop-up lists for the job name, operator name, machine type and machine configuration were added, simplifying data entry and assuring uniformity; time formulas were exploited for run duration and productivity calculations; the data entry cells (in yellow) were placed in tab order and all other cells locked, so shift leaders could tab through the sheet, entering data, without concern about overwriting critical formulas.

More importantly, the form was linked to a summary sheet that consolidates packaging performance across multiple shifts, accumulating running productivity results for the month (Figure 2). Using nested array formulas and lookup/match functions (the “voodoo” to which my former colleague was referring), I’m able to report progress toward net throughput goals that my packaging manager and I negotiated early on (Figure 3). The net throughput goal (in thousands per hour) is a floating target, depending upon insert load, machine type and setup configuration (single- or double-out) on any given run, but we can show broader trends in productivity performance as a percentage of that goal. Where we were achieving 78 percent of goal in September, for example, we hit 89 percent by December, and have had individual weeks in both December and January in which we achieved results as high as 95 percent.

When (not if) we start hitting our net throughput goals on a regular basis, we’ll nudge the minimum and maximum rates upward for each machine and challenge the packaging operators and their teams to achieve the new targets.

However, before we start raising the bar on net throughput goals, there will also be the matter of refining staffing levels. None of what has been accomplished so far in the packaging center has been the result of throwing people at the problem. That certainly would have been easy to do: station one person at every hopper and a mob downstream from the stacker; turn up the run speed; and turn off miss/double detection on the inserter controller. The result would be shorter shifts, but no labor savings. The circulation and advertising directors might also have a few things to say about this approach after the calls started pouring in.



Excel with your employees

You’ll notice in Figure 3 that in addition to setting goals for net throughput, there is accompanying guidance for appropriate staffing, and that the body count also slides with the insert load. How could it be otherwise?

Since we have all the variables we need (run quantity, projected pieces per hour and body count), it isn’t a leap to calculate total projected man hours by run and compare that number to actuals from the end-of-shift reports. In that way, we’re able to deliver a true productivity metric: pieces per man hour.

For my paper, with some 40 insert runs per week (many of them commercial print runs that are deadline driven), the challenge in refining staffing will be to reorder runs, where possible. Each shift will need to begin with runs that are heavy on insert loads (and body count) and part-time employees will need to be sent home as the load lightens, later in the day. My packaging manager and his shift leaders have their work cut out for them.  My Excel sheet stands ready to tell us how we’re doing.

To be sure, many packaging centers are tracking pieces per man hour, but what I think is novel here is that we understand it on a more granular basis, we’re keeping running tabs on it and can act upon our observations promptly, and none of this has come with an undue burden to me or my supervisory team.

An entirely different but no less useful implementation of an Excel spreadsheet solution is one I built for doing skills assessments in my pressroom. I’ve used this at two different papers to analyze the relative skill sets of my press operators, helping guide me in decisions about wage equity, performance coaching and career counseling, training needs and opportunities, shift realignments, and staff reductions.

This is not appropriate for (or a replacement of) an annual review, but with names excluded, it can be supplemental to that conversation.

Constructed originally for a shift consolidation and staff reduction project I orchestrated at another paper, it helped me make better decisions for my employer and justify to the HR director’s satisfaction that my actions wouldn’t leave the company vulnerable to spurious claims of unfairness. More recently, I’ve used it to support the argument for wage equity adjustments for apprentice-level operators whose skills were outpacing their compensation.

I started by asking the pressroom manager for a laundry list of press-specific skills he expected of his journey-level operators, e.g. setting folder, roll tending, registration, building impositions, Webbing the press, etc. He provided 30-plus skills for assessment, and we grouped them into three categories: maintenance, press operation, and mechanical/electrical. I added an “other” category to assess things like attention to detail, attitude, teamwork, communications, initiative, etc.

Based on the goals I had for my department and the needs the publisher had articulated for the company, I assigned a priority to each skill that would give it more or less weight in comparison to the others. Then I asked the pressroom manager and shift leaders to score their employees on a scale of 1 to 10 in each area of assessment.

Bar charts made it a cinch to see how the operators stacked up against each other in specific categories, and overall. Because some of our newer, apprentice-level employees scored very high in motivation, attitude and teamwork, and because I tended to weigh those work-ethic characteristics very high, the results in the case of my shift consolidation/staff reduction made a persuasive case for retaining my strongest apprentices in preference to our weaker operators who demonstrated attitude and motivational deficiencies.

I should note: seniority was not a skill upon which we evaluated the team, and it didn’t play a part in our determinations.

I’ll chalk up another win for a great measurement and reporting tool and its ability to help sidestep the “yeah, buts …” and “what ifs …” that might have otherwise derailed the optimum outcome for the paper.

More recently, I added a grid to the spreadsheet that calculates a skill-to-wage ratio for each operator, comparing him to a designated model employee and determining (for my benefit) whether the operator is over-or under-paid by comparison, and by how much. This is not a tool I’d use to broadly adjust compensation across a department, since there are always wage inequities and addressing them is not always possible, but it did highlight for me where and from whom we were getting a lot of value for our money, and where and from whom that wasn’t so much the case. It has helped inform me about the merit of wage adjustment requests as they arise so I can be selective about those I might pitch to my publisher. I’ve also used it to turn the question on its head, advising an employee or two where they need to grow and add value before I’ll go to bat for them on a wage adjustment. One operator who took that advice seriously is now an assistant shift supervisor. A win for us both!

I could go on and on about how I’ve used Excel to keep running track of on-time press starts and finishes, build elaborate tools for estimating commercial print jobs and generating quotes, and assist in managing our paper inventory and resupply. But some of these solutions are more tailor-made for our needs than universal.

As usual, I invite my peers to exchange ideas and solutions. Look me up on


W. Eric Schult is the operations director at The Fayetteville (N.C.) Observer. Contact him on or at [email protected]

Like & Share E&P:
Follow by Email

Leave a Reply

Your email address will not be published. Required fields are marked *