Tag: Online Training

22 Seconds to Burn – Excel VBA Teaches Lean Execution

Cover of "Excel 2003 Power Programming wi...
Cover via Amazon

Background:

VBA for Excel has once again provided the opportunity to demonstrate some basic lean tenets.  The methods used to produce the required product or solution can yield significant savings in time and ultimately money.  The current practice is not necessarily the best practice in your industry.  In manufacturing, trivial or minute differences in methods deployed become more apparent during mass production or as volume and demand increases.  The same is true for software solutions and both are subject to continual improvement and the relentless pursuit to eliminate waste.

Using Excel to demonstrate certain aspects of Lean is ideal.  Numbers are the raw materials and formulas represent the processes or methods to produce the final solution (or product).  Secondly, most businesses are using Excel to manage many of their daily tasks.  Any extended learning can only help users to better understand the Excel environment.

The Model:

We recently created a perpetual Holiday calendar for one of our applications and needed an algorithm or procedure to calculate the date for Easter Sunday and Good Friday.  We adopted an algorithm found on Wikipedia at http://en.wikipedia.org/wiki/Computus that produces the correct date for Easter Sunday.

In our search for the Easter Algorithm, we found another algorithm that uses a different method of calculation and provides the correct results too.  Pleased to have two working solutions, we initially did not spend too much time thinking about the differences between them.  If both routines produce the same results then we should choose the one with the faster execution time.  We performed a simple time study to determine the most efficient formula.  For a single calculation, or iteration, the time differences are virtually negligible; however, when subjected to 5,000,000 iterations the time differences were significant.

This number of cycles may seem grossly overstated, however, when we consider how many automobiles and components are produced each year then 5,000,000 approaches only a fraction of the total volume.  Taken further, Excel performs thousands of calculations a day and perhaps even as many more times this rate as numbers or data are entered on a spreadsheet.  When we consider the number “calculations” performed at any given moment, the number quickly grows beyond comprehension.

Testing:

As a relatively new student to John Walkenbach’s book, “Excel 2003 Power Programming with VBA“, speed of execution, efficiency, and “Declaring your Variables” have entered into our world of Lean.  We originally created two (2) routines called EasterDay and EasterDate.  We then created a simple procedure to run each function through 5,000,000 cycles.  Again, this may sound like a lot of iterations but computers work at remarkable speeds and we wanted enough resolution to discern any time differences between the routines.

The difference in the time required to execute 5,000,000 cycles by each of the routines was surprising.  We recorded the test times (measured in seconds) for three separate studies as follows:

  • Original EasterDay:  31.34,  32.69,  30.94
  • Original EasterDate:  22.17,  22.28,  22.25

The differences between the two methods ranged from 9.17 seconds to 8.69 seconds.  Expressed in different terms, the duration of the EasterDay routine is 1.39 to 1.46 times longer than EasterDate.  Clearly the original EasterDate function has the better execution speed.  What we perceive as virtually identical systems or processes at low volumes can yield significant differences that are often only revealed or discovered by increased volume or the passage of time.

In the Canadian automotive industry there are at least 5 major OEM manufacturers (Toyota, Honda, Ford, GM, and Chrysler), each producing millions of vehicles a year.  All appear to produce similar products and perform similar tasks; however, the performance ratios for each of these companies are starkly different.  We recognize Toyota as the high velocity, lean, front running company.  We contend that Toyota’s success is partly driven by the inherent attention to detail of processes and product lines at all levels of the company.

Improvements

We decided to revisit the Easter Day calculations or procedures to see what could be done to improve the execution speed.  We created a new procedure called “EasterSunday” using the original EasterDay procedure as our base line.  Note that the original Wikipedia code was only slightly modified to work in VBA for Excel.  To adapt the original Wikipedia procedure to Excel, we replaced the FLOOR function with the INT function in VBA.  Otherwise, the procedure is presented without further revision.

To create the final EasterSunday procedure, we made two revisions to the original code without changing the algorithm structure or the essence of the formulas themselves.  The changes resulted in significant performance improvements as summarized as follows:

  1. For integer division, we replaced the INT (n / d) statements with a less commonly used (or known) “\” integer division operator.  In other words, we used “n \ d” in place of “INT( n / d)” wherever an integer result is required.  This change alone resulted in a gain of 11 seconds.  One word of caution if you plan to use the “\” division operator:  The “n” and “d”  are converted to integers before doing the division.
  2. We declared each of the variables used in the subsequent formulas and gained yet another remarkable 11 seconds.  Although John Walkenbach and certainly many other authors stress declaring variables, it is surprising to see very few published VBA procedures that actually put this to practice.

Results:

The results of our Time Tests appear in the table below.  Note that we ran several timed iterations for each change knowing that some variations in process time can occur.

EasterDay = 31.34375 Original Code uses INT( n / d ) to convert Division Results
EasterSunday = 20.828125 1.  Replaced INT ( n / d) with (n \ d)
EasterDate = 22.28125 Original Code – Alternate Calculation Method
Re-Test to Confirm Timing
EasterDay = 30.9375 Original Code uses INT( n / d ) to convert Division Results
EasterSunday = 20.921875 1.  Replaced INT ( n / d) with (n \ d)
EasterDate = 22.25 Original Code – Alternate Calculation Method
Re-Test to Confirm Timing
EasterDay = 30.90625 Original Code uses INT( n / d ) to convert Division Results
EasterSunday = 21.265625 1.  Replaced INT ( n / d) with (n \ d)
EasterDate = 22.25 Original Code – Alternate Calculation Method
Re-Test to Confirm Timing
EasterDay = 31.078125 Original Code uses INT( n / d ) to convert Division Results
EasterSunday = 9.171875 2.  Variables DECLARED!
EasterDate = 22.1875 Original Code – Alternate Calculation Method
Re-Test to Confirm Timing
EasterDay = 31.109375 Original Code uses INT( n / d ) to convert Division Results
EasterSunday = 9.171875 2.  Variables DECLARED!
EasterDate = 22.171875 Original Code – Alternate Calculation Method

The EasterSunday procedure contains the changes described above.  We achieved a total savings of approximately 22 seconds.  The integer division methods used both yield the same result, however, one is clearly faster than the other.

The gains made by declaring variables were just as significant.  In VBA, undeclared variables default to a “variant” type.  Although variant types are more flexible by definition, performance diminishes significantly. We saved at least an additional 11 seconds simply by declaring variables.  Variable declarations are to VBA as policies are to your company, they define the “size and scope” of the working environment.  Undefined policies or vague specifications create ambiguity and generate waste.

Lessons Learned:

In manufacturing, a 70% improvement is significant; worthy of awards, accolades, and public recognition.  The lessons learned from this example are eight-fold:

  1. For manufacturing, do not assume the current working process is the “best practice”.  There is always room for improvement.  Make time to understand and learn from your existing processes.  Look for solutions outside of your current business or industry.
  2. Benchmarking a current practice against another existing practice is just the incentive required to make changes.  Why is one method better than another?  What can we do to improve?
  3. Policy statements can influence the work environment and execution of procedures or methods.  Ambiguity and lack of clarity create waste by expending resources that are not required.
  4. Improvements to an existing process are possible with results that out perform the nearest known competitor.  We anticipated at least being able to have the two routines run at the similar speeds.  We did not anticipate the final EasterSunday routine to run more than 50% faster than our simulated competitive benchmark (EasterDate).
  5. The greatest opportunities are found where you least expect them.  Learning to see problems is one of the greatest challenges that most companies face.  The example presented in this simple analogy completely shatters the expression, “If it ain’t broke, don’t fix it.”
  6. Current practices are not necessarily best practices and best practices can always be improved.  Focusing on the weaknesses of your current systems or processes can result in a significant competitive edge.
  7. Accelerated modeling can highlight opportunities for improvement that would otherwise not be revealed until full high volume production occurs.  Many companies are already using process simulation software to emulate accelerated production to identify opportunities for improvement.
  8. The most important lesson of all is this:

Speed of Execution is Important >> Thoughtful Speed of Execution is CRITICAL.

We wish you all the best of this holiday season!

Until Next Time – STAY Lean!

Vergence Analytics

At the onset of the Holiday project, the task seemed relatively simple until we discovered that the rules for Easter Sunday did not follow the simple rules that applied to other holidays throughout the year.  As a result we learned more about history, astronomy, and the tracking of time than we ever would have thought possible.

We also learned that Excel’s spreadsheet MOD formula is subject to precision errors and the VBA version of MOD can yield a different result than the spreadsheet version.

We also rediscovered Excel’s Leap Year bug (29-Feb-1900).   1900 was not a leap year.  The leap year bug resides in the spreadsheet version of the date functions.  The VBA date function recognizes that 29-Feb-1900 is not a valid date.

Advertisements

OEE For Manufacturing

We are often asked what companies (or types of companies) are using OEE as part of their daily operations.  While our focus has been primarily in the automotive industry, we are highly encouraged by the level of integration deployed in the Semiconductor Industry.  We have found an excellent article that describes how OEE among other metrics is being used to sustain and improve performance in the semiconductor industry.

Somehow it is not surprising to learn the semiconductor industry has established a high level of OEE integration in their operations.  Perhaps this is the reason why electronics continue to improve at such a rapid pace in both technology and price.

To get a better understanding of how the semiconductor industry has integrated OEE and other related metrics into their operational strategy, click here.

The article clearly presents a concise hierarchy of metrics (including OEE) typically used in operations and includes their interactions and dependencies.  The semiconductor industry serves as a great benchmark for OEE integration and how it is used as powerful tool to improve operations.

While we have reviewed some articles that describe OEE as an over rated metric, we believe that the proof of wisdom is in the result.  The semiconductor industry is exemplary in this regard.  It is clear that electronics industry “gets it”.

As we have mentioned in many of our previous posts, OEE should not be an isolated metric.  While it can be assessed and reviewed independently, it is important to understand the effect on the system and organization as a whole.

We appreciate your feedback.  Please feel free to leave us a comment or send us an e-mail with your suggestions to leanexecution@gmail.com

Until Next Time – STAY lean!

OEE for Batch Processes

Coke being pushed into a quenching car, Hanna ...
Image via Wikipedia

We recently received an e-mail regarding OEE calculations for batch processes and more specifically the effect on down stream equipment that is directly dependent (perhaps integrated) on the batch process.  While the inquiry was specifically related to the printing industry, batch processing is found throughout manufacturing. Our more recent experiences pertain to heat treating operations where parts are loaded into a stationary fixed-load oven as opposed to a continuous belt process.

Batch processing will inherently cause directly integrated downstream equipment (such as cooling, quenching, or coating processes) to be idle. In many cases it doesn’t make sense to measure the OEE of each co-dependent piece of equipment that are part of the same line or process. Unless there is a strong case otherwise, it may be better to de-integrate or de-couple subsequent downstream processes.

Batch processing presents a myriad of challenges for line balancing, batch sizes, and capacity management in general.  We presented two articles in April 2009 that addressed the topic of  where OEE should be measured.  Click here for Part I or Click  here for Part II.

Scheduling Concerns – Theory of Constraints

Ideally, we want to measure OEE at the bottleneck operation.  When we apply the Theory of Constraints to our production process, we can assure that the flow of material is optimized through the whole system.  The key of course is to make sure that we have correctly identified the bottleneck operation.  In many cases this is the batch process.

While we are often challenged to balance our production operations, the real goal is to create a schedule that can be driven by demand.  Rather than build excess inventories of parts that aren’t required, we want to be able to synchronize our operations to produce on demand and as required to keep the bottleneck operation running.  Build only what is necessary:  the right part, the right quantity, at the right time.

Through my own experience, I have realized the greatest successes using the Theory of Constraints to establish our material flows and production scheduling strategy for batch processes.  Although an in-depth discussion is beyond the scope of this article, I highly recommend reading the following books that convey the concepts and application through a well written and uniquely entertaining style:

  1. In his book “The Goal“, Dr. Eliyahu A. Goldratt presents a unique story of a troubled plant and the steps they took to turn the operation around.
  2. Another book titled “Velocity“, from the AGI-Goldratt Institute and Jeff Cox also demonstrates how the Theory of Constraints and Lean Six Sigma can work together to bring operations to all new level of performance, efficiency, and effectiveness.

I am fond of the “fable” based story line presented by these books as it is allows you to create an image of the operation in your own mind while maintaining an objective view.  The analogies and references used in these books also serve as excellent instruction aids that can be used when teaching your own teams how the Theory of Constraints work.  We can quickly realize that the companies presented in either of the above books are not much different from our own.  As such, we are quickly pulled into the story to see what happens and how the journey unfolds as the story unfolds.

Please leave your comments regarding this or other topics.  We appreciate your feedback.  Also, remember to get your free OEE spreadsheets.  See our free downloads page or click on the file you want from the “Orange” box file on the sidebar.

Until Next Time – STAY lean!

Vergence AnalyticsVergence Analytics

OEE: Frequently Asked Questions

We added a new page to our site to address some of the more frequently asked questions (FAQ’s) we receive regarding OEE.  We trust you will find this information to be of interest as you move forward on your lean journey.  We always appreciate your feedback, so feel free to leave us a comment or send an e-mail directly to LeanExecution@gmail.com or Vergence.Consulting@gmail.com

We have had an incredibly busy summer as more companies are pursuing lean manufacturing practices to improve their performance.  OEE has certainly been one of the core topics of discussion.  We have found that more companies are placing a significant emphasis on Actual versus Planned performance.  It would seem that we are finally starting to realize that we can introduce a system of accountability that leads to improvements rather than reprimands.

Keep Your Data CLEAN

One of the debates we recently encountered was quantity versus time driven performance data when looking at OEE data.  The argument was made that employees can relate more readily to quantities than time.  We would challenge this as a matter of training and the terminology used by operations personnel when discussing performance.  We recommend using and maintaining a time based calculation for all OEE calculations.  Employees are more than aware of the value of their time and will make every effort to make sure that they get paid for their time served.

Why are we so sure of this?  Most direct labour personnel are paid an hourly rate.  Make one error on their pay or forget to pay their overtime and they will be standing in line at your office wondering why they didn’t get paid for the TIME they worked.  They will tell you – to the penny – what their pay should have been.  If you are paying a piece rate per part, you can be sure that the employees have already established how many parts per hour they need to produce to achieve their target hourly earnings.

As another point of interest and to maintain consistency throughout the company, be reminded that finance departments establish hourly Labour and Overhead rates to the job functions and machines respectively.  Quite frankly, the quantity of parts produced versus plan doesn’t really translate into money earned or lost.  However, one hour of lost labour and everyone can do the math – to the penny.

When your discussing performance – remember, time is the key.  We have worked in some shops where a machine is scheduled to run 25,000 parts per day while another runs a low volume product or sits idle 2 of the 5 days of the the week.  When it comes right down to the crunch for operations – how many hours did you earn and how many hours did you actually work.

Even after all this discussion we decided it may be an interesting exercise to demonstrate the differences between a model based on time versus one based (seemingly) only on Quantitative data.  We’ll create the spreadsheet and make it available to you when its done!

Remember to take advantage of our free spreadsheet templates.  Simply click on the free files in the sidebar or visit our free downloads page.

We trust you’re enjoying your summer.

Until Next Time – STAY Lean!

Vergence Business Associates

OEE Topics for 2009

We changed our theme!

Today was another day to do a little maintenance. We spent a little time revamping our look and feel. We hope you enjoy the changes and find our site a little easier to navigate.  We updated our Free Downloads page to present another easier and more direct venue to get your files instantly using Box.Net. If you’re already familiar with WordPress, you know how great this widget is. Downloads could never be faster or easier.

We also took some time to update some of our pages. We would suggest, however, that the best detailed content appears in the individual articles that we have posted.

Upcoming Topics for 2009

  1. Tracking OEE Improvements:  We have noticed an increase in the number of requests to discuss tracking OEE improvements.  We have been working on a few different approaches even for our own consulting practice and look forward to sharing some thoughts and ideas here.
  2. How OEE can improve your Cost of Non-Quality.  It’s more than yield.
  3. What OEE can do for your Inventory.  Improvements should be cascading to other areas of your operation – including the warehouse.
  4. Innovation – Defining your future with OEE
  5. OEE and Agile – Going beyond lean with OEE.
  6. Best Practices – OEE in real life, in real time

If you would like to suggest a topic for a future post, ask a question, or make a suggestion, please leave a comment or simply send an e-mail to LeanExecution@gmail.com or vergence.consulting@gmail.com.  We do appreciate your feedback.

Until Next Time – STAY lean!

Vergence Business Associates

We respect your privacy, your information will not be shared, sold, or distributed to any third parties.  We will only use your e-mail to communicate with you at your request.  You will not be subject to any advertising or marketing campaigns.

OEE Training – Online

Getting Started

Online Training is more rampant now than ever.  If you want to learn about OEE and how to calculate it correctly then we have all the information you need right here.  Simply click on the categories of interest to you and research your specific topic or Click Here to get started.  This is the first article that got us started in November of 2008.  All of our online content is presently available at no charge.

Free Spreadsheet Templates

We offer several OEE Spreadhseet Templates that are available at no cost to our visitors and clients. Feel free to click on the “Free Downloads” template on the sidebar.  This is a new feature and trust that you will find this a much easier solution that provides immediate access to our documents.  If you can’t find what you are looking for, contact us by e-mail (leanexecution@gmail.com) or leave a comment with your suggestions for other templates that you would like to see available on our site.

Advanced Visitors

We trust that the content presented here is of interest to you as well.  We have provided many articles of interest related to OEE and Lean.  Simply review the categories and posts available or visit our pages for more information.  Our articles present detailed discussions and best practices applicable to the featured topic.

If you have any questions, comments, or suggestions for a future topic, simply leave a comment or send an e-mail to leanexecution@gmail.com or vergence.consulting@gmail.com.  We respect your privacy.  We will not share, disclose, sell, or distribute your e-mail or personal information with any third parties.  Your e-mail will only be used to contact you at your request.  You will not be subject to any advertising or marketing campaigns.  See our privacy policy for more details.

Until Next Time – STAY lean!

Vergence Business Associates

How to Reduce Costs with OEE: Cost Control

OEE is a great metric to help identify where you may be incurring losses in your processes or operation.  As one of the goals of implementing a Lean strategy is to reduce costs, it only seems natural that we should be able to determine what processes to focus on that are driving the greatest losses.

From the example developed in our previous posts we determined that the OEE and related factors for our three processes were as follows:

Machine Availability Performance Quality OEE
A 92.97% 88.26% 97.77%  80.22%
B 96.04% 77.23% 94.44% 70.05%
C 95.16% 61.70% 95.20% 55.90%

Based on the OEE results, one would be inclined to take a look at Machine C as it has the lowest OEE.  Is this really the greatest opportunity?  The only way to answer the question is to understand what factors are driving costs and ultimately affecting profitability.

The performance factor for machine C is definitely pulling down the OEE for this process.  What would you think if the machine is 100% automated (no labour) and the cycle time, although it may be less than standard, is still meeting the takt time to meet customer demand?  Is there really a cost?  Of course there is, but the impact to your business may be minimal in terms of cost when compared to the other machines.

It is clear that we need to develop a model to understand what losses and ultimately costs are associated with each of the factors.  In turn, we will be able to better understand the overall OEE.

What costs do we consider?  We recommend keeping the model simple.  There are typically three cost components associated with any given process or product:  Material, Labour, and Overhead.  Burden is another term used for Overhead and we will use these terms interchangeably.

Our goal over the next few posts will be to develop a simple cost model for each process and, in turn, determine which one may be the process of choice for improvement.  For now, we will provide a general discussion of some of the potential cost considerations.

Improving quality typically yields the greatest return on investment because all of the cost elements stated above are impacted by the Quality factor.  Raw material, Labour, and Burden are all expended to produce a part scrap part.

The costs associated with Quality losses are further challenged when considering the number of parts that would have to be produced in order to recover these lost costs.  If you are lucky enough to enjoy a 10% profit margin (clear), then, at a minimum, 10 parts would have to be produced for every part scrapped.  Of course, more parts would have to be produced to recover other infrastructure costs incurred including documentation, record keeping, and scrapping of the actual parts.

Performance losses typically affect labour and overhead.  Labour losses are easy enough to understand.  If a machine is operator dependent, then we will have to pay a person to stand at the machine to run it.  If it is running slowly, more costs are incurred to cover the additional labour time.

In many cases, direct losses related to overhead are sometimes difficult to assess unless a truly activity based costing system is in place.  The reason for the complexity arises because some of the costs are “fixed”.  Because the equipment exists, expenses such as depreciation or property taxes are incurred whether or not the equipment or, for that matter, the plant is running.  The performance of the machine or any of the other factors for that matter won’t change this fact.

Availability then becomes somewhat more obscure when it comes to calculating hard costs.  If the labour can be redeployed to another process when a machine goes down, perhaps some of the labour losses can be avoided.  If not, then waiting for a machine to be repaired or material to be delivered is a real loss that should be addressed.

Intangible costs are also difficult to quantify but we should be aware of their existence.  The costs associated or related to poor OEE may include overtime, expedited freight, and infrastructure costs related to extra handling of material or management of non-conforming material (containment, extra inspection, rework, and scrap).  Although this is a relatively short list, it addresses the most obvious potential losses.  With a little more thought, the list could easily grow longer.

Other key metrics in your facility such as customer delivery or quality performance indicators may also point to problems that can be traced directly to poor OEE performance.  Although difficult to measure, a company’s competitive position is compromised when efficiencies are low and eventually the costs of poor performance make their way into the “burden” costs required to manage the operation.

While OEE is an effective metric for operations, on its own, it does not provide a direct indicator of real financial losses.  As Lean Practitioners we are challenged to provide an analysis that not only improves the metrics of the business but also translate into real financial improvements on the balance sheet and ultimately – the bottom line.  We would suggest that OEE is a time driven metric (asset time management strategy) versus our proposed COEE which is Finance or “Value” driven (cost management strategy).   We are presently developing a model that will allow your OEE data to be sensitized with cost data as demonstrated by the table below.

We have coined the term COEE or Cost of Overall Equipment Effectiveness.  Consider the following OEE results converted to Cost based drivers using standard costs as our baseline.  The sample data and spreadsheet used to calculate this data will be available as a download soon.  The overall spreadsheet is quite large and based on a fully detailed three shift operation.

Cost driven OEE model - Summary
Cost driven OEE model - Summary

Our OEE cost model clearly presents the real costs or “losses” incurred per part.  Our Weighted OEE Cost Model will change the way you view OEE data, enabling you to set priorities and identify real, quantifiable, opportunities for improvement.  The above snapshot represents the goal of our COEE project – a clean, clear, summary of the losses incurred correlated directly to your OEE index.  Another advantage is that the Availability, Performance, and Quality factors are recalculated based on cost and presents a realistic breakdown of losses for each of these factors from a financial perspective.  Our spreadsheet presents an advanced OEE example that will bring real value to your OEE implementation strategy.

NOTE:  The fully developed spreadsheet is available from our FREE Downloads page or from the FREE Downloads box on the sidebar.

A well implemented OEE strategy should become evident on the balance sheet through improved material utilization, reduced labour variance (straight and overtime reductions), reduced scrap costs, reduced rework costs, and other burden account reductions.

Take quick, effective, and efficient action to solve the problems having the greatest financial impact to your business.  Last but not least, don’t confuse activity with action.  Decisions are not actions and talking about a problem or even writing about it could be construed as activity.  Real actions produce real, measurable, results.

Change requires Change.  Profit is to business as oxygen is to humans – you need it to survive. 

We have created a number of Excel spreadsheets that are immediately available for download from our FREE Downloads page or from the Free Downloads widget on the side bar.  These spreadsheets can be modified as required for your application.  There are no hidden files, formulas, or macros and no obligations for the services provided here.

If you have any questions or comments, feel free to send an email to LeanExecution@gmail.com

Until Next Time – STAY Lean!

"Click"