New Excel Software Tools to Practice for SMTA Certification #1: Line Balancing

I recently developed some Excel-based software to help those who are planning to take the SMTA certification exam to practice. 

In this post, I will discuss the tool that performs line balancing. In a typical SMT assembly line, the placement machines are the “gate” in the cycle time of the line. To assure that their cycle time is the lowest, the placement machines must be time balanced. For example, suppose a simple SMT assembly line has one chip shooter and one flexible placer. Let’s say that the chip shooter takes longer to place all of the chips than the flexible placer takes to assemble the simple and complex integrated circuits. So, in this case, chips should be removed from the chip shooter and be placed on the flexible placer. But how many should be moved to the flexible placer? Determining the number requires algebra, and to understand how to do it, we need a numeric example.

Let’s do an example. In an assembly line, the “gate” in the cycle time is component placement.

  • The chipshooter (CS) places passives at 60,000/hr and Simple ICs (SICs) at 4,000/hr
  • The flexible placer (FP) places complex ICs (CICs) at 3,000/hr and SICs and passives at 8,000/hr
  • The bill of material (BOM) is 354 passives, 12 SICs, and 4 CICs
  • If the FP takes less time to place the CICs and SICs than the CS takes to place all of the passives, then move some of the passives to the FP to time balance the line
  • Let’s check the situation that the FP takes: (4 CICs / 3,000/hr) + (12 SICs / 8,000/hr) = 0.001333 + 0.0015 = 0.002833hrs
  • The CS takes: 354/60,000/hr = 0.0059hrs
  • So, move chips to the FP—but how many? Let’s call the number x. The times should be equal, so:
    • 0.002833+x/8000 = (354-x)/60,000.  Solve for x to time balance the line.
    • 0.002833+x/8000 = (354-x)/60,000, multiply each side by 60,000
    • (60,000*0.002833) + (60,000x/8000) = 354 – x
    • 170 + (60/8)x = 354 – x,  gather x terms
    • 170 + ((60/8) +1)x = 354, gather numbers
    • (68/8)x = 354-170 = 184, solve for x
    • x = (8/68)*184 = 21.65 or 22 passive moved to FP

Let’s see if the times on each machine are the same.

  • CTCS= 332 passives/60,000 passives/hr =0.005533 hrs or 19.92 secs

Is the FP the same?

  • CTFP = 0.002833 + 22/8,000 = 0.005583 hrs or 20.10 secs

Why the difference?

The times can’t be exactly the same as we rounded the number of passives moved to the FP.

Figure 1. The “Line Balancer” answer to the problem

Figure 1 shows the calculations from the Excel® software tool I developed called “Line Balancer.”  Note the answers are the same. If you would like a copy, send me an email at [email protected].

Here is a problem for you to solve:

  • The chip shooter (CS) places passives at 50,000/hr and Simple ICs (SICs) at 3,000/hr
  • The flexible placer (FP) places Complex ICs (CICs) at 4,000/hr and SICs and passives at 7,000/hr
  • The bill of material (BOM) is 390 passives, 14 SICs, and 6 CICs

How many components need to be moved, and to which placement machine? What is the cycle time?

To the first person that sends me the answer, I will send them a Dartmouth hat.

Searching for Lost Profit

After just finishing her department’s monthly activity report, Patty took a break to stare out her window, admiring the beauty of last night’s fresh snowfall. Her mind quickly went to the events of the past week.

Rob had “popped the question” and Patty had quickly said yes. Their mothers were ecstatic. Both Patty and Rob liked and enjoyed each other’s parents. Patty recognized this as a blessed situation, but both mothers were now spending 10 hours each day planning the wedding. A result, Patty and Rob were both fielding several calls a day from each mom. Patty decided to go “with the flow” and count her blessings that both she and Rob had great parents.

She briefly looked down at the ring Rob had given her. It was a striking two carat emerald with 0.4 carat diamonds on either side. Rob was concerned that Patty might not like an emerald, but he explained that the price of diamonds is controlled and that “you could pave your driveway with diamonds for each equally good sapphire, ruby, and especially emerald that exists in nature.”

“All the emerald mines of Colombia produce only one or two good 2 carat emeralds per year,” he went on to say.

Well, one of them was right there on her finger. In addition to the uniqueness of emerald, the setting was in rhodium, the hardest and rarest of the precious metals. “Five hundred times more rare than gold,” Rob told her. She was especially impressed when she looked up rhodium on the Web and found this quote: “Rhodium has been used for honours, or to symbolize wealth, when more commonly used metals such as silver, gold or platinum are deemed insufficient.” Gold and platinum insufficient!?

Rob was secretive about how he found such an apparently rare ring. But it was consistent with his many other successes in life. She was thrilled to have him as a future hubby, even if she did beat him at golf.

These happy and a little stressful thoughts were interrupted, by Pete coming to her door.

“Hey, kiddo, get packed, looks like will be going on another trip. Guadalajara, this time. Como es su espanol?” Pete said with gusto.

“Mi espanol es muy bueno. Why do you think we will go to Guadalajara?” Patty asked.

“Well, I just talked to Pedro and he said that they performed our productivity audit. Uptime is 29%, and all lines are time balanced to +/-2%, about as best as could be hoped.”

Patty and her team developed a Productivity Audit from what they learned with The Professor in their recent adventures together.

“Then what is the problem?” Patty inquired.

Pete responded, “Jane, the finance exec we met on our trip to South Carolina, implemented a company-wide profitability software program. It was implemented and Guadalajara is 10% too low. No one can figure out why. I think we’ll want The Professor for this one.”

Patty called and was stunned that The Professor was again available. Apparently this was his off term teaching at Ivy University, as he teaches over the summer.

When our trio arrived at ACME’s Guadalajara facility they all spoke in Spanish. Patty had taken Spanish in 4th grade through high school, Spanish was one of the 7 or 8 languages The Professor spoke and Pete’s grandparents hailed from Puerto Rico. They were surprised that the site GM, Harry Hopkins, asked them to speak in English.

“Give me a break, I grew up in Boston, I can barely speak English,” he joked in his heavy Boston accent. “We want you to help us find that lost 10%, we must be doing something wrong. Help us find it,” Harry implored. “One thing I can tell you is that I am really proud of my team; they are really working hard, you can tell by all of the product that is out there. It makes me proud just to walk out on the shop floor and see all of the product!”, he went on.

Patty was relieved that Harry was so supportive. Apparently Jane had sent the “good word” about how the trio had helped ACME’s South Carolina plant.

As the trio went on a tour, one thing immediately struck Patty, there was hardly room to walk around. There were partly assembled boards all over the place.

At the end of the tour Patty spoke up, “This facility is striking in how much partially completed product is on the shop floor.”

And therein lies the problem,” responded The Professor.

How can profits be off when uptime and line balancing are so good? Could it be that Guadalajara uses poor solder pastes, fluxes or performs? Will our illustrious trio find the problem? Does Patty really like her emerald engagement ring?

Stay tuned for the latest.

Saving a House, Losing a Mansion

Patty and The Professor agreed to work with two of the local process engineers to develop a plan of attack to try and find the lost productivity.

Patty spoke first. “It’s tempting to look just at the new solder paste, but this approach wouldn’t be thorough.”

The Professor and the two process engineers, Joe and Ann, agreed. So they went ahead and developed a thorough productivity assessment plan, including uptime and line balancing measurements and evaluating changeover and assist times. Ann pointed out that one of the five lines was still using the old paste. All agreed that this situation was good news as they would have a new paste to old paste comparison. It was already lunch time and everyone was hungry, so off they went to a local Outback. While riding in the car, Patty’s cellphone rang. It was Rob.

“Hey Patty,” Rob cheerfully started. “Guess what I shot last night at the Golf Club of New England — a four under par 68! The pro told me it was the best round this year at the course from the back tees.”

“Rob, that’s great!” Patty cheerfully responded. Truth be told, she was really happy for Rob. He was the No. 2 golfer on the men’s team at Tech a few years ago as a senior. She was a junior then and was the best women golfer in Tech’s history. The few times they played then, she beat him. Ever since her dinner date, after their success at AJAX, they had been a couple. At the time she had been thinking of breaking up with Jason and Rob’s invite to dinner was all the catalyst that she needed. In the past year or so, Jason would just watch sports on TV and drink beer. He didn’t have a fitness program or a real plan for his life. Rob was so much different. He worked out, mostly to improve his golf game and he was getting a master’s degree part time.

After they started dating, Rob and Patty played golf together with some other guy friends from Tech. She usually shot the low score, but the three other guys were longer off the tee than she was. Her superior iron play and short game made the difference.

At lunch this working foursome talked about the audit they were about to perform.

“There is one comical thing we should tell you before we start,” Joe said with a twinkle in his eye. “I’ts about the ‘Saving a House Program.’ ”

At that, Ann started laughing and inadvertently started choking on her “sweet tea.” Patty was about to perform the Heimlich maneuver when Ann revived.

With Ann still red in the face and laughing, The Professor requested, “Yes, please tell us.”

Joe chimed in, “So that Ann doesn’t choke to death, let me take a stab at it. The new cheaper solder paste has not been very popular and has generated many complaints. The new COO, Fred, decided he had to do something. He estimated that the new paste saves $100,000 a year on all five lines; that’s about what a modest house costs locally. So he tells all of the complainers that using the new paste saves enough money in a year to buy a new house. He even found a house for sale on the internet for $100,000 and had posters of it made with the saying: ‘Saving Enough for a House.’ It worked; people stopped complaining.”

“Joe, can you tell us what some of the complaints were about the solder paste?” asked The Professor.

“Well, for one thing, it is stiff coming out of the tubes or jars, we have to knead it or it won’t print,” Joe responded.

“Hmm,” both Patty and The Professor mused.

“Also, if we stop a line for a few minutes the paste stiffens up and we have to perform some dummy prints to kneed it,” chimed in Ann. “Sometimes even after this, the first print has to be discarded due to poor hole fill. It wastes time and solder paste.”

“Don’t forget the smell,” Joe teased.

At that, Ann just about spit up her sweet tea.

“The new paste literally stinks,” Joe added. “Fortunately, the vendor added some perfume recently.”

“What about reliability of the finished product?” The Professor asked evenly.

“That’s what is surprising. It’s as good as the old paste.” Ann replied. “We performed some tests and asked around, the reliability is very good.”

“A pleasant surprise indeed,” The Professor said.

The little group finished lunch and headed back to get to work on the audit. Ann and Patty and Joe and The Professor formed teams and went off to the factory. They performed detailed analysis of changeover times, assist times, line balancing, uptime, etc., on the four lines using the new solder paste and the one line using the old solder paste.

As Patty approached one of the lines she saw a cheerful looking gent about 45 years old replenishing the solder on one of the stencil printers. Ann introduced her to Wilbur and asked if it was OK for Patty to ask him some questions.

“Darlin,” he said to Ann in his backwoods drawl, “Anything you gorgeous gals want to ask me is jus fine.”

“How does replenishing the new paste compare to the old paste?” Patty asked.

“Well, it takes a lot longer, stirring the paste and all, but to “Save a House” I’m willing to put up with it, sighed Wilbur.

After a day-and-a-half of work, the team reassembled. The Professor suggested that Patty lead the discussion. Many calculations and comparisons were performed, finally after several hours they were ready to meet with Fred Perkins and Jane Wilson. Patty agreed to speak.

Patty, addressed the small gathering. She presented the approach they used to collect data, their analysis techniques and the fact that they had reached a consensus. The evidence, she said, is persuasive that:
1. The site productivity is down about 8%, which will reduce profits about 12%.
2. The main culprit appears to be the new solder paste.

At this Fred slammed his fist on the disk. His face a bright crimson, he shouted at Patty, “Liar, you corporate types are all alike! You come here from your Ivory Tower and tell us how to assemble a product. You have never had to meet a payroll and make a profit in your life. I’ve been out on the line. It only takes two or three minutes longer per changeover with the new paste and replenish times are even less.”

At these comments Jane rolled her eyes and glared at Fred. It was clear she wasn’t intimidated by him.
Patty shot back, “Fred you are correct; let’s look at the numbers. We measured your average uptime at about 25%, which is quite good. That means the lines are running two hours in an eight hour shift or 120 minutes. Eight percent of 120 minutes is about ten minutes a day. A typical line has two changeovers a day each requiring 2 extra minutes and 6 solder paste replenishments ,taking an extra 1 minute each with the new paste. This totals 10 minutes, hence cuts production by 8%.”

Fred screamed back, “This is mathematical gobblygook. I saved the company $100,000 a year.” At this he stormed out of the room.

The remaining folks stared at each other. Finally Jane broke the silence, “It never occurred to me how precious a few minutes here and there can affect profit. With the new paste, we will lose about 12% of our total profit of $10 million, or $1.2 million per year. It appears that while Fred was ‘saving a house,’ we were ‘losing a mansion.’ ”

Epilogue: Three weeks later Fred was “promoted” to corporate compliance officer. Jane became the new site CEO/COO. The old solder paste was reinstated a day after Fred left. A few of the old-timers kept some of the “Saving a House” posters for future reminiscing.

‘Paste is Paste’: The Professor Hits the Road

Patty was staring out the window of her new office. She had just been made manager of a new department responsible for corporate wide productivity and advanced processes. At 25 years old, she was the youngest manager at ACME by six years. She was surprised that Pete was one of the folks in the new department and was concerned that Pete might have trouble accepting her as his boss.

When Patty mentioned her concern, Pete replied, “Hey ‘kid,’ are you joking? I’m thrilled to be in your department. Not only is the work the most interesting, but you are one of the few managers that really knows what they’re doing.”

Considering that Pete was Patty’s father’s age, this vote of confidence meant a lot.

The view out of her office window was terrific. She could see some of the local hills in Southern New Hampshire and the fall color change was striking.  A few coworkers claimed that on a clear day you could just see the top of Mount Monadnock. Patty had made some calculations using the position of her office, the distance and height of the local hills and had proven to herself that “the most-climbed mountain in the US” was 100 meters to short to be seen from her window. But the sky was so clear she couldn’t help by strain her eyes to see it she could get a glimpse of that majestic beak. Her pleasant interlude was jarred by the rude, demanding ringing of her office phone.

“Advanced Processes, Patty speaking,” she cheerfully spoke into the phone.

“Patty, it’s Sam. Can you come to my office now?” the voice on the other end spoke to her.

It was Sam Watkins, the site GM, and his call now did not make her nervous. Ever since the first visit of The Professor, Sam had treated her like a valued member of the team. It was his suggestion to launch a corporate center of competence for productivity and advanced processes and name Patty the manager. When he gave her the job, he said that a lot of the work would be corporate troubleshooting. She expected that Sam’s call related to this topic.

When she entered his office, Sam got right to the point. “Patty, there’s a crisis in our plant in Columbia, SC. A new COO took over four months ago and went on a cost-cutting spree. Since then, the plant’s profit is down 8%. We can’t figure out why. Go there and find out what’s going on. And develop a plan to fix it,” he ordered.

Patty excitedly returned to her office. She called Pete in and they discussed plans for their trip. Patty was trying to limit her use of The Professor, but this assignment seemed to beg for his participation.  His uptime improvement recommendations so impressed management, he was now on a permanent consulting retainer.

As Sam said, “Every time The Professor visits one of our facilities, they make several more million dollars a year. I wish he lived here!”

Patty made a call to The Professor and to her surprise he was available. They agreed to meet at the Columbia airport at noon in two days.

The time passed quickly and before she knew it she was in a rental car driving to the ACME facility in Columbia. Their first meeting was with the assembly process engineering team and the new COO. After introductions, the COO, Fred Perkins, spoke.

“I really don’t know why you are here,” he commented brusquely. “Profits are only down 5%; it’s probably just a random fluctuation.  I came here with a mandate to cut costs and dammit I did. I couldn’t believe what we were paying for solder paste, and I found a vendor that would charge 25% less. This was the first cost savings I implemented. Solder paste is solder paste. It’s just like butter. When I was COO of American Foods, they were paying too much for butter, and I found a vendor that would charge 9% less. Butter is butter, solder paste is solder paste,” he concluded.

“How much money will you save on paste this year,” Patty inquired.

“For all of our five lines, $100,000,” Fred proudly answered.

“How much profit do your lines produce per year?” The Professor asked.

“We have five, 20 -2 lines,” replied Jane Wilson, the site CFO.

“What’s a 20-2 line? “ asked Patty.

“Oh, sorry. It’s a term we use to here to describe line financial metrics. The ’20’ stands for $20 million in sales and the ‘2’ stands for $2 million in profit.” Jane responded.

“Thanks,” said Patty.

“Oh, but I guess we would have to call them 18.4 -1.84 lines now that the productivity and profit are down by 8%.” Jane sarcastically said as she glared at Fred.

At this comment, Fred lost his cool, he slammed his fist on the table and shouted at Jane. “It isn’t 8%, its only 5% and I told you it’s just a random fluctuation,” he fumed.

Is the lost profit just a random fluctuation? If not, what is the cause? And how are Patty and Rob doing?

Stayed tuned for the latest.

Patty Gets Her Man

Patty and Rob went to balance the line. They found that this very complex board had 1350 passives, 24 simple ICs (SICs) and nine complex ICs. Currently the chipshooter (CS) is placing all the passives and nine of the SICs.

The flexible placer (FP) is placing the nine complex ICs and 15 SICs. The placement rate for the chip shooter is 60,000/hr. for passives and 3,000/hr. for SICs. The CS cannot place CICs. The FP places complex ICs at 4,000/hr. and passives and SICs at 8,000/hr.

To balance a line, Rob and Patty start by seeing how long it will take the CS to place all of the passives and the FP to place all of the SICs and CICs.

For the CS to place all of the passives

timeCS = 1350 passives/60K passives/hr. = 0.01945 hr. = 81 sec.

The time FP takes to place all of the SICs and CICs is

timeFP = 24 SICs/8K SICs/hr   + 9 CICs/4K CICs/hr = 0.003 hrs + 0.00225 hrs = 18.1 seconds

Since the FP is waiting for the CS, Patty and Rob should move passives to the FP. They will determine the number of passives, x, to move to the FP by equalizing the CS and FP times

tCS = (1350-x) passives/60K passives/hr  = x passives/8K/hr + 18.1 secs/3600 sec/hour

Solving for x:

(1350-x)/60,000 = x/8,000 +18.1/3600

1350 – x = 7.5 x + 301.667

8.5 x = 1048.333

x = 123.33

In other words, 123 passives go to FP (the reason Patty and Rob round down to 123 is due to the much faster placement speed for the CS).

So Patty and Rob moved to 123 passives to the flexible placer.  Each machine’s time should be about the same now.  They performed the following calculation to check their results

tCS = (1350-123)/60,000 = 0.02045 hrs = 73.62 sec.

tFP = 18.1/3600 + 123/8000 = 0.02043 hrs = 73.45 sec.

Patty and Rob then went to the line to physically move the passive feeders to the FP.  They then measured the placement time of the both the CS and FP and found them to be 73.4 and 73.5 sec., respectively, quite close to their calculations.

A meeting was held to discuss the results and the tremendous productivity improvement (81 vs 73.5 second cycle time, an (81-73.5)/81 = 9.26 % improvement.)  Everyone at AJAX was pleased (maybe not Charlie!) and felt the experience was one of great learning.

At the end of the meeting Patty and Rob had a brief chat.

“Patty, I really appreciate all of your help,” said Rob said.

“Don’t forget The Professor. I don’t think I could have handled Charlie without him,” responded Patty.

“Anyway, to show my gratitude, will you allow me to take you to dinner?” asked Rob.

Finding Your ‘Herbie’

“Your chipshooter is “the Herbie” in your process…, ” The Professor continued.

“That’s it!” a confident voice boomed from the back of the room.

“Who’s that?” Patty asked Rob.

“That’s Chris Conrad, our general manger. He’s a pretty sharp guy.”

Chris continued, “I remember “Herbie” from reading The Goal when I was at Tuck in the 1980s. Herbie was a chubby boy scout, during a hike, he held up all of the scout troop because he was loaded down carrying all of the soda. Finally the scouts realized that they had to help Herbie to make good time in the hike.   My professor assigned us problems to find where there is a constraint in a process. He even called it, ‘Finding the Herbie in the process’.”

“You don’t mean to tell me you’re  gonna take work off them chipshooters are ya?” Charlie impatiently inquired?

“No, the chipshooters will be working just as hard,” The Professor calmly replied.

“How can this be?”asked Charlie in disbelief.

“I made some calculations,” responded The Professor.  “In them, I estimate that if you move passives from the chipshooter to the flexible placer, both machines will have a cycle time of about 73 seconds. Your productivity will increase by about 10%. The chipshooter will be working full time, but the flexible placer will not be waiting for it.”

“Wow,” chimed in Chris, “Our CFO, Tom Stevens, will be thrilled … as am I.”

“Your increase in profit may actually be more than 15 percent,” The Professor added.

The Professor went on, “My calculations were only estimates,  Patty knows how to balance a line.  Give her the exact metrics and she can calculate the actual minimum cycle time. Patty, can you do this?”

“Sure thing Professor,” responded Patty.

Patty and the AJAX team, led by Rob, went off to perform some calculations. Stay tuned to see the results and how they got them.


Dr. Ron

Line Balancing: The Professor’s Tale


Business was good at ACME.  Even in these challenging times, the company’s three assembly lines could not keep up with demand.  John, the manger of the assembly lines, decided to request the funds for an additional assembly line.  A member of his team, Patty, suggested he might want to consult “The Professor,”*before getting a new line.

The Professor taught a course on line balancing that Patty took at the SMTAI conference last summer.  Line balancing is an important part of optimizing productivity in electronics assembly. A balanced line ensures that the component placement process, usually the “constraint,” is the fastest possible by ensuring that each placement machine spends the same amount of time placing components.  If any machine is waiting for the others, assembly time is being wasted.  In a sense line balancing is an application of Goldratt’s Theory of Constraints. John remembered that when Patty applied what she learned from The Professor, throughput increased 25%.  Unfortunately, Patty did not attend The Professor’s other class on “Increasing Line Uptime.”

John decided to have a chat with Patty about The Professor.

“Patty, why do you think I should consult with The Professor, about getting a new line?”

“Well John, perhaps with some effort to improve our uptime , we wouldn’t have to buy another line,” said Patty.

“That’s a good point,” replied John.

Patty contacted The Professor and he agreed to fit ACME into his busy schedule. Upon his arrival, The Professor was given a tour. As part of the tour he was shown the process that ACME used to minimize changeover time between jobs. The Professor appeared impressed. After the tour, The Professor asked if a brief meeting could be held with the engineers and managers to discuss the situation.

“What is the average line uptime?” The Professor asked the assemblage.

There was some hemming and hawing, finally Pete, the senior process engineer replied, “I’d say at least 95%, we work our fannies off out there.” There was a murmur of agreement from the 9 or 10 people in the room.

Finally John spoke up, “Professor, what is your definition of uptime?”

The Professor responded, “Simply the percent of time an assembly line is running.” Pete again responded that 95% was the right number.

The Professor asked for some production metrics and performed some calculations on his laptop. In a few moments he commented, “From the data you gave me, I estimate that your average line uptime is about 10%.”

Upon hearing this, Pete became red in the face, especially after Patty whispered in his ear, “I told you so.” The noise in the room became so loud that John was concerned he might have a riot on his hands. The Professor asked to speak and John, in a booming voice, asked for calm.

“Let’s not become angry, perhaps my calculations are off. Why don’t we measure the uptime for a few weeks to be certain.”

“How do we do that?” asked Pete, his face still crimson.

“Each day one process engineer will go out to the lines every 30 minutes. If the line is running, he will put a 1 in an Excel spreadsheet cell. If the line is not running, a 0 will be entered,” responded the professor.”

It was agreed that this will be done and The Professor would come back in two weeks.

Will Pete’s red face return to normal? Will the line uptime be 95%? Will Patty and Pete ever be on speaking terms again?  Stay tuned for the next episode.


Dr. Ron

*The Professor, as he is affectionately called by his many students, is a kindly older man who works at a famous university. Few know his real name. The Professor is an expert in process optimization.