 ### Transcription

Real World Applications for Visual Basic:Calculating Overall Equipment EffectivenessINTRODUCTIONThe Industrial Engineering program at Iowa State focuses on providing students with real world industryexamples. Microsoft Excel is arguably one of the most commonly used software programs within theworking world. IE 148 adequately prepares students to use Excel properly in order to solve a multitude ofcommon real world problems that one might experience on the job. However, a gap exists betweenbuilding this foundation and continuing to improve these skills and further the education throughout therest of the students’ coursework. There are many instances where data used to solve problems will begenerated by a computer on a much larger scale than what is taught in the classroom. This documentseeks to provide students with an opportunity to further develop their problem solving skills. One way tofurther these skills is by applying VBA knowledge acquired during the freshman year to later courses inthe core curriculum. This will better prepare students for problems they may encounter in their firstinternship experience.BACKGROUNDA productivity metric is a type of measurement used to gauge a company's throughput per unit of time. Everycompany has a set of productivity metrics that they use to determine their system’s productivity. It is a commonrole for an Industrial Engineer in a manufacturing environment to interpret these metrics in order to help identifyareas for improvement as well as track improvements over time.Productivity standards can be ambiguous because each company has the ability to define their own. Thesestandards allow a company to see whether or not it is improving when it comes to its own productivity. Onecommon way to determine a company’s productivity is to calculate overall equipment effectiveness (OEE).OEE is determined by combining three individual calculations. These three calculations are for availability,efficiency, and quality. Below are the components of each of these three calculations. It is important toremember that each company can define the terms of each equation as they see fit.Availability takes into account loss due to downtime. It is calculated by taking the operating time (uptime of themachine) and dividing by planned production time (hours of operation). The formula is as follows:Availability Operating Time / Planned Production TimeEfficiency accounts for speed loss. It is calculated by taking the ideal cycle time divided by the division ofoperating time over total pieces produced. The formula is as follows:Efficiency Ideal Cycle Time / (Operating Time / Total Pieces Produced)The final metric is quality. Quality accounts for quality loss. It is calculated by taking the number of good piecesproduced over the total pieces produced. The formula is as follows:Quality Number of Good Pieces Produced / Total Pieces ProducedAfter these three individual calculations have been computed, they are multiplied together to determine theoverall equipment effectiveness. The formula is as follows:OEE Availability * Effectiveness * QualityOne of the major causes for companies to use OEE metrics is to reduce and/or eradicate the ‘Six BigLosses’(“Six.Losses”, 2012). These so called “Six Big Losses” are sorted into categories based on which ofthe three metrics they alter; two losses per metric (“Six.Losses”, 2012).1

The losses that affect availability are breakdowns and setup/adjustments. A few examples of breakdowns couldbe: tooling failures, unplanned maintenance, and equipment failure. Examples of setup/adjustment includesetup time, material shortages, operator shortages, and warm up time (“Six.Losses”, 2012).The losses in regard to performance include small stops and reduced speed. A few examples of small stopsare obstructed product flow, component jams, misfeeds, general blockage, and cleaning. Examples of reducedspeed include tough running, equipment wear, and operator inefficiency (“Six.Losses”, 2012).The two categories that hinder quality are startup rejects and production rejects. A few examples of startuprejects are scrap pieces, reworked parts, in-progress issues, and incorrect assembly. Examples of productionrejects are the same as startup examples but occur during steady-state production, not during setup/warmup(“Six.Losses”, 2012).DISCUSSIONNow that OEE has been introduced, the next step is to look at a specific example of an OEE problem. Thefollowing problem statement explains an example OEE situation with a large number of data points. The bestway to solve a large scale OEE problems is through VBA in Excel. The steps for solving this problem follow theproblem statement below.Problem StatementGlobex Corporation has just begun a new lean manufacturing initiative. Part of this initiative is focused ondeveloping Key Performance Indicators (KPIs) that allow management to track the performance of specificwork centers in order to help identify problem areas. As an Industrial Engineer with the company, your task is totake daily production data, as shown in Tables 1 below, and develop a weekly OEE report for a work centerthat consists of four machines that make similar parts. The production operations software compiles downtimeand quality information into the excel file listed in Table 2 below. With the provided excel file of production data,use VBA to develop an OEE data display for each machine. Tables 1 and 2 below show the given productiondata.Table 1: Weekly Downtime Data from the Four Machines2

Table 2: Quality Data for Each PartSOLUTIONAs explained earlier, OEE is a multiplier of Availability x Efficiency x Quality that is used to indicate the overalleffectiveness of the work center. Developing the VBA code to solve the problem can be broken down into threecategories: variable definition, sorting techniques, and subroutines.Variable definitionThe first step is to identify what variables are needed in order to solve the problem. It is important to rememberto name the variables clearly in order to help differentiate between them while writing the code. First, define avariable for each of the components of the OEE equation: availability, efficiency, and quality. These values willeach be independently calculated as a ratio within separate subroutines. These should be defined as doubles3

because they are non-integer values. Variables defined as doubles are capable of holding more informationthat integer values.Dim Availability As DoubleDim Quality As DoubleDim Efficiency As DoubleThe next step is to define a variable for each additional value below used in the calculations.Availability Operating Time / Planned Production TimeEfficiency Ideal Cycle Time / (Operating Time / Total Pieces Produced)Quality Number of Good Pieces Produced / Total Number of Pieces ProducedIn order to sort the data define an integer variable for the machine and day as well.Sorting techniquesThere are two sorting functions that can easily be utilized in this scenario to separate the data by machine andby day. Nesting For Next loops within the Select Case function and vice versa will allow the code to navigate tothe data of a specific machine and day.SubRoutinesA subroutine is a section of the program that provides instructions to carry out a specific task. There are onlythree subroutines required to solve the problem. Write the first sub to calculate availability, the second tocalculate quality, and the third to calculate overall OEE. Since the variables used to calculate performance aredefined in the A and Q subs, we can simply define the efficiency value within the final OEE sub.AvailabilityThe main purpose of this sub is to analyze the down time logs from the “efficiency” tab of the excel report tocalculate operating time.Operating Time Planned Production Time - Down Time.Planned production time in this case is representative of a typical 8 hour shift in a manufacturing environment.Use the Select Case function to select the machine. Once the machine has been identified, use a For Next loopto reference each cell. Subtract the time of the previous stoppage from the current start time in order todetermine the downtime. Looking at machine one for example:Select Case MachineCase 1For i 3 To 7DT DT Worksheets("Efficiency").Range("C" & i).Value - Worksheets("Efficiency").Range("D" & (i –1)).ValueNext iEnd SelectThis will calculate a running sum of the total downtime for machine one on day one. Repeat this for each dayand machine by changing the values of i to correspond with the rows of the efficiency tab. Change the rangeletter to represent the start and stop time columns for each machine.4

At the end of each case, calculate the availability for each machine and each day using the modified availabilityequation.Availability (Planned Production Time – Down Time) / Planned Production TimeQualityThe quality data can be sorted by nesting two Select Case functions. First select the day, then select themachine. The two variables needed for the quality calculation are the total number of parts produced as well asthe number of parts that passed the quality inspection. Multiplying the scrap rate by the total number of partsproduced will determine how many parts did not pass the quality inspection.For example,Select Case DayCase 1 ‘Indicates Day 1Select Case MachineCase 1 ‘Indicates Machine 1NumProduced Worksheets("Quality").Range("C2").Value ‘Pulls total quantity produces from fileScrap Worksheets("Quality").Range("D2").Value * NumProduced ‘Pulls scrap rate from fileQuality (NumProduced - Scrap) / NumProduced ‘Calculates # of good piecesCase 2 ‘Indicates Machine 2NumProduced Worksheets("Quality").Range("C3").ValueScrap Worksheets("Quality").Range("D3").Value * NumProducedQuality (NumProduced - Scrap) / NumProducedCase 3 ‘Indicates Machine 3NumProduced Worksheets("Quality").Range("C4").ValueScrap Worksheets("Quality").Range("D4").Value * NumProducedQuality (NumProduced - Scrap) / NumProducedCase 4 ‘Indicates Machine 4NumProduced Worksheets("Quality").Range("C5").ValueScrap Worksheets("Quality").Range("D5").Value * NumProducedQuality (NumProduced - Scrap) / NumProducedEnd SelectEnd SelectOEEThe final sub will calculate the efficiency value and then write the values into a new sheet that will display all ofthe information in one place to allow the decision maker to evaluate OEE as a key performance indicator. Theefficiency metric will be calculated using the following equation:Efficiency (NumProduced) / (OpTime * Cycle)5

Create a new sheet with a table for each machine. Use the example below in Table 3 as a guide.Table 3: Example of How to Organize Output DataOnce again, nest the Select function within a For loop to cycle through each day and each machine. Use thecall function to reference the availability and quality subs. For each case, specify the range that each value willgo into. For example, for Monday and machine one,Worksheets("OEE").Range("C3").Value AvailabilityWorksheets("OEE").Range("D3").Value EfficiencyWorksheets("OEE").Range("E3").Value QualityWorksheets("OEE").Range("F3").Value Avail * Efficiency * QualityIn order to calculate the weekly average, use the average function and select all of the cells in OEE column asthe range. The final product will allow managers to see how well each machine is performing on a weekly basisand help them identify areas for improvement.A finished code solution can be found in Appendix A.ResultsOnce OEE values have been calculated using VBA in Excel, the problem in still not complete. It is important toknow how to interpret OEE values and the values of the components that make up the OEE value in order toimprove a system. Attaining OEE values merely explains how a system is currently functioning. Being able tochange factors within the system to increase the values of the components that make up OEE and in turn,increase the OEE value itself is where true improvement occurs. Looking at the specific results from thisexample problem, it is observed in Table 4 below that the average weekly OEE values range from 0.34 to 0.55for the four machines.6

Machine 1 has the most consistent results day to day with a weekly average OEE value of 0.46, which isbetween the highest and lowest observed values. Machine 2 has the highest weekly average OEE value, 0.55,and this value would be even greater if there had not been a decrease in the efficiency on Friday. Afterobserving this, it is important to go back and review why the efficiency dropped on Friday in order to makeimprovements in the future. Machines 3 and 4 have similar day to day OEE values as well as similar weeklyaverages. It might appear that these two systems are operating in the same way but that is not the case.Machine 3 has higher availability values while Machine 4 has higher quality values. With these two machines, itwould be beneficial to look at how quality for Machine 3 could be improved and how availability in Machine 4could be improved.Table 4: Average weekly OEE values from the four machinesFeasibilityThe goal of this document is to provide industrial engineering students with the opportunity to revisit the VBAskills learned in IE 148 later in their education. Here, the example of later application is the topic of OEE, whichis covered in IE 222. After drafting this assignment, it was shown to industrial engineering students who havealready completed IE 148 and IE 222 in order to receive their feedback. Their ideas for improving clarity andfurther defining variables were taken into consideration in order to ensure the helpfulness of this document.CONCLUSIONVBA within Excel has many applications and solving OEE equations with a large amount of data is just one ofits uses. VBA will be used in various industrial engineering classes and across many industries after college. Itis important to understand the basics of coding with VBA in order to apply its usefulness as often as possible.One limitation of this document is that the parameters for OEE have been defined one way here and may bedefined differently at companies where students intern. On the other hand, if one’s company does not havethese parameters yet defined, this document provides a reference for defining them.7

ReferencesSix Big Losses. 2012. Itasca, IL: Vorne Industries, Inc. Retrieved from:http://www.oee.com/oee-six-big-losses.html8

Appendix APublic MachineNum As IntegerPublic OpTime As DoublePublic DT As DoublePublic SchedTime As DoublePublic NumProduced As DoublePublic CycleTime As DoublePublic Scrap As IntegerPublic Avail As DoublePublic Quality As DoublePublic Efficiency As DoublePublic Machine As IntegerPublic Day As IntegerPublic Cycle As DoubleSub Qual()Select Case DayCase 1Select Case MachineCase 1NumProduced Worksheets("Quality").Range("C2").ValueScrap Worksheets("Quality").Range("D2").Value * NumProducedQuality (NumProduced - Scrap) / NumProducedCase 2NumProduced Worksheets("Quality").Range("C3").ValueScrap Worksheets("Quality").Range("D3").Value * NumProducedQuality (NumProduced - Scrap) / NumProducedCase 3NumProduced Worksheets("Quality").Range("C4").ValueScrap Worksheets("Quality").Range("D4").Value * NumProducedQuality (NumProduced - Scrap) / NumProducedCase 4NumProduced Worksheets("Quality").Range("C5").ValueScrap Worksheets("Quality").Range("D5").Value * NumProducedQuality (NumProduced - Scrap) / NumProducedEnd SelectCase 2Select Case MachineCase 1NumProduced Worksheets("Quality").Range("C2").ValueScrap Worksheets("Quality").Range("D2").Value * NumProducedQuality (NumProduced - Scrap) / NumProducedCase 2NumProduced Worksheets("Quality").Range("C3").ValueScrap Worksheets("Quality").Range("D3").Value * NumProducedQuality (NumProduced - Scrap) / NumProducedCase 3NumProduced Worksheets("Quality").Range("C4").ValueScrap Worksheets("Quality").Range("D4").Value * NumProduced9

Quality (NumProduced - Scrap) / NumProducedCase 4NumProduced Worksheets("Quality").Range("C5").ValueScrap Worksheets("Quality").Range("D5").Value * NumProducedQuality (NumProduced - Scrap) / NumProducedEnd SelectCase 3Select Case MachineCase 1NumProduced Worksheets("Quality").Range("C2").ValueScrap Worksheets("Quality").Range("D2").Value * NumProducedQuality (NumProduced - Scrap) / NumProducedCase 2NumProduced Worksheets("Quality").Range("C3").ValueScrap Worksheets("Quality").Range("D3").Value * NumProducedQuality (NumProduced - Scrap) / NumProducedCase 3NumProduced Worksheets("Quality").Range("C4").ValueScrap Worksheets("Quality").Range("D4").Value * NumProducedQuality (NumProduced - Scrap) / NumProducedCase 4NumProduced Worksheets("Quality").Range("C5").ValueScrap Worksheets("Quality").Range("D5").Value * NumProducedQuality (NumProduced - Scrap) / NumProducedEnd SelectCase 4Select Case MachineCase 1NumProduced Worksheets("Quality").Range("C2").ValueScrap Worksheets("Quality").Range("D2").Value * NumProducedQuality (NumProduced - Scrap) / NumProducedCase 2NumProduced Worksheets("Quality").Range("C3").ValueScrap Worksheets("Quality").Range("D3").Value * NumProducedQuality (NumProduced - Scrap) / NumProducedCase 3NumProduced Worksheets("Quality").Range("C4").ValueScrap Worksheets("Quality").Range("D4").Value * NumProducedQuality (NumProduced - Scrap) / NumProducedCase 4NumProduced Worksheets("Quality").Range("C5").ValueScrap Worksheets("Quality").Range("D5").Value * NumProducedQuality (NumProduced - Scrap) / NumProducedEnd SelectEnd SelectEnd SubSub Availability()'Calculate Operating Time10

Select Case MachineCase 1DT 0For i 3 To 7DT DT Worksheets("Efficiency").Range("C" & i).Value - Worksheets("Efficiency").Range("D" &(i - 1)).ValueNext iDT Left(Format((DT), "hh:mm"), 2) * 60 Right(Format((DT), "hh:mm"), 2)SchedTime 480OpTime 480 - DTAvail OpTime / SchedTimeCase 2DT 0For i 3 To 7DT DT Worksheets("Efficiency").Range("K" & i).Value - Worksheets("Efficiency").Range("L" &(i - 1)).ValueNext iDT Left(Format((DT), "hh:mm"), 2) * 60 Right(Format((DT), "hh:mm"), 2)SchedTime 480OpTime 480 - DTAvail OpTime / SchedTimeCase 3DT 0For i 3 To 7DT DT Worksheets("Efficiency").Range("S" & i).Value - Worksheets("Efficiency").Range("T" &(i - 1)).ValueNext iDT Left(Format((DT), "hh:mm"), 2) * 60 Right(Format((DT), "hh:mm"), 2)SchedTime 480OpTime 480 - DTAvail OpTime / SchedTimeCase 4DT 0For i 3 To 7DT DT Worksheets("Efficiency").Range("AA" & i).Value Worksheets("Efficiency").Range("AB" & (i - 1)).ValueNext iDT Left(Format((DT), "hh:mm"), 2) * 60 Right(Format((DT), "hh:mm"), 2)SchedTime 480OpTime 480 - DTAvail OpTime / SchedTimeEnd SelectEnd SubSub OEE()11

Cycle 4For Day 1 To 5Select Case DayCase 1For Machine 1 To 4 'Calculate Metrics for each machine individuallyCall AvailabilityCall QualEfficiency (NumProduced) / (OpTime * Cycle)Select Case MachineCase 1Worksheets("OEE").Range("C3").Value AvailWorksheets("OEE").Range("D3").Value EfficiencyWorksheets("OEE").Range("E3").Value QualityWorksheets("OEE").Range("F3").Value Avail * Efficiency * QualityCase 2Worksheets("OEE").Range("I3").Value AvailWorksheets("OEE").Range("J3").Value EfficiencyWorksheets("OEE").Range("K3").Value QualityWorksheets("OEE").Range("L3").Value Avail * Efficiency * QualityCase 3Worksheets("OEE").Range("O3").Value AvailWorksheets("OEE").Range("P3").Value EfficiencyWorksheets("OEE").Range("Q3").Value QualityWorksheets("OEE").Range("R3").Value Avail * Efficiency * QualityCase 4Worksheets("OEE").Range("U3").Value AvailWorksheets("OEE").Range("V3").Value EfficiencyWorksheets("OEE").Range("W3").Value QualityWorksheets("OEE").Range("X3").Value Avail * Efficiency * QualityEnd SelectNext MachineCase 2For Machine 1 To 4 'Calculate Metrics for each machine individuallyCall AvailabilityCall QualEfficiency (NumProduced) / (OpTime * Cycle)Select Case MachineCase 1Worksheets("OEE").Range("C4").Value AvailWorksheets("OEE").Range("D4").Value EfficiencyWorksheets("OEE").Range("E4").Value QualityWorksheets("OEE").Range("F4").Value Avail * Efficiency * QualityCase 2Worksheets("OEE").Range("I4").Value AvailWorksheets("OEE").Range("J4").Value EfficiencyWorksheets("OEE").Range("K4").Value QualityWorksheets("OEE").Range("L4").Value Avail * Efficiency * QualityCase 3Worksheets("OEE").Range("O4").Value Avail12

Worksheets("OEE").Range("P4").Value EfficiencyWorksheets("OEE").Range("Q4").Value QualityWorksheets("OEE").Range("R4").Value Avail * Efficiency * QualityCase 4Worksheets("OEE").Range("U4").Value AvailWorksheets("OEE").Range("V4").Value EfficiencyWorksheets("OEE").Range("W4").Value QualityWorksheets("OEE").Range("X4").Value Avail * Efficiency * QualityEnd SelectNext MachineCase 3For Machine 1 To 4 'Calculate Metrics for each machine individuallyCall AvailabilityCall QualEfficiency (NumProduced) / (OpTime * Cycle)Select Case MachineCase 1Worksheets("OEE").Range("C5").Value AvailWorksheets("OEE").Range("D5").Value EfficiencyWorksheets("OEE").Range("E5").Value QualityWorksheets("OEE").Range("F5").Value Avail * Efficiency * QualityCase 2Worksheets("OEE").Range("I5").Value AvailWorksheets("OEE").Range("J5").Value EfficiencyWorksheets("OEE").Range("K5").Value QualityWorksheets("OEE").Range("L5").Value Avail * Efficiency * QualityCase 3Worksheets("OEE").Range("O5").Value AvailWorksheets("OEE").Range("P5").Value EfficiencyWorksheets("OEE").Range("Q5").Value QualityWorksheets("OEE").Range("R5").Value Avail * Efficiency * QualityCase 4Worksheets("OEE").Range("U5").Value AvailWorksheets("OEE").Range("V5").Value EfficiencyWorksheets("OEE").Range("W5").Value QualityWorksheets("OEE").Range("X5").Value Avail * Efficiency * QualityEnd SelectNext MachineCase 4For Machine 1 To 4 'Calculate Metrics for each machine individuallyCall AvailabilityCall QualEfficiency (NumProduced) / (OpTime * Cycle)Select Case MachineCase 1Worksheets("OEE").Range("C6").Value AvailWorksheets("OEE").Range("D6").Value EfficiencyWorksheets("OEE").Range("E6").Value QualityWorksheets("OEE").Range("F6").Value Avail * Efficiency * Quality13

Case 2Worksheets("OEE").Range("I6").Value AvailWorksheets("OEE").Range("J6").Value EfficiencyWorksheets("OEE").Range("K6").Value QualityWorksheets("OEE").Range("L6").Value Avail * Efficiency * QualityCase 3Worksheets("OEE").Range("O6").Value AvailWorksheets("OEE").Range("P6").Value EfficiencyWorksheets("OEE").Range("Q6").Value QualityWorksheets("OEE").Range("R6").Value Avail * Efficiency * QualityCase 4Worksheets("OEE").Range("U6").Value AvailWorksheets("OEE").Range("V6").Value EfficiencyWorksheets("OEE").Range("W6").Value QualityWorksheets("OEE").Range("X6").Value Avail * Efficiency * QualityEnd SelectNext MachineCase 5For Machine 1 To 4 'Calculate Metrics for each machine individuallyCall AvailabilityCall QualEfficiency (NumProduced) / (OpTime * Cycle)Select Case MachineCase 1Worksheets("OEE").Range("C7").Value AvailWorksheets("OEE").Range("D7").Value EfficiencyWorksheets("OEE").Range("E7").Value QualityWorksheets("OEE").Range("F7").Value Avail * Efficiency * QualityCase 2Worksheets("OEE").Range("I7").Value AvailWorksheets("OEE").Range("J7").Value EfficiencyWorksheets("OEE").Range("K7").Value QualityWorksheets("OEE").Range("L7").Value Avail * Efficiency * QualityCase 3Worksheets("OEE").Range("O7").Value AvailWorksheets("OEE").Range("P7").Value EfficiencyWorksheets("OEE").Range("Q7").Value QualityWorksheets("OEE").Range("R7").Value Avail * Efficiency * QualityCase 4Worksheets("OEE").Range("U7").Value AvailWorksheets("OEE").Range("V7").Value EfficiencyWorksheets("OEE").Range("W7").Value QualityWorksheets("OEE").Range("X7").Value Avail * Efficiency * QualityEnd SelectNext MachineEnd SelectNext DayEnd Sub14

Appendix nt/automating-oee-for-the-job-shopFigure 1: Screenshot of Online Comment Posted by the Group15

Now that OEE has been introduced, the next step is to look at a specific example of an OEE problem. The following problem statement explains an example OEE situation with a large number of data points. The best way to solve a large scale OEE problems is through VBA in Excel. The steps for