Introduction to R for Excel Users | Thomas Hopper | R-bloggers

“…The quality of our decisions in an industrial environment depends strongly on the quality of our analyses of data. Excel, a tool designed for simple financial analyses, is often used for data analysis simply because it’s the tool at hand, provided by corporate IT departments who are not trained in data science.

Unfortunately, Excel is a very poor tool for data analysis and its use results in incomplete and inaccurate analyses, which in turn result in incorrect or, at best, suboptimal business decisions. In a highly competitive, global business environment, using the right tools can make the difference between a business’ survival and failure. Alternatives to Excel exist that lead to clearer thinking and better decisions. The free software R is one of the best of these…”

Sourced through from:

Continue reading

Using videos to improve operations | Part 7 – Detailed review of process segments

Asenta 2011-03 Roberto Cortés

Roberto Cortés

Asenta Juan Ortega head shot

Juan Ortega

This post was co-written with Asenta’s Roberto Cortés and Juan Ortega, based on a joint project in Spain in October, 2013. A detailed analysis of the video recordings on two operations was key to generating improvement ideas that the plant has implemented since. The company had shot some videos of operations before, but not used them this way before, and it was a learn-by-doing experience for the participants. 

The demand for the company’s products is growing, and it is struggling to keep up. Its core technology is a fabrication process, and engineering has focused its attention on it to increase capacity. After fabrication, however, the product needs several assembly operations. From direct observation, it was clear that the operators were working at a pace that could not be sustained for a whole shift. The manager confirmed that the pace slackened and the quality dropped towards the end of the shift.

The challenge was therefore to change the assembly process so that the operator could complete the tasks within the takt time of about 60 seconds, at a steady, sustainable pace, without running ragged or getting exhausted. While on site, we focused on two operations, shot videos as recommended in earlier posts — from an elevated position and focusing on the operator’s hands — and coached the plant team on reviewing the videos, with the goal of enabling them to do it on their own for the other operations.

1. Preparation

The detailed review breaks the operation down into its smallest identifiable steps  to discover improvement opportunities for each. If you are going to do this on a regular basis, you should probably invest in software to collect timestamps from videos, categorize the steps, and record improvement ideas, like Timer Pro or Dartfish.  Timer Pro was developed specifically for Manufacturing; Dartfish, for sports, but it has also been used in Manufacturing.

For the first time, it is best to do it on short operations, and you can make do with an Excel spreadsheet on which you manually record the timestamps. It needs the following columns:

  • Step number
  • End time
  • Step duration, calculated from the timestamps.
  • Cumulative time, aggregated from step durations.
  • Operation Description
  • Operation Category
  • Improvement Ideas

Sufficient time has to be allowed for the detailed review. It is customary to allow between 3 and 5 times the length of the recording and even more if the recording is very short. It is recommended to have a sample of the product and components at hand where the review is being held.


Product sample in conference room

2. Review

The video is analysed and the spreadsheet completed step by step. For short steps, you can play the video in slow motion  to give time to observe details. Because you are going to be adding times, you need record the timestamps at a higher precision than you are really interested in. For example, to analyze time in second, you need to record the timestamps to one tenth of a second. The video and the form are shown on the screen at the same time.


Video and analysis form on the same screen

While conducting the analysis, do the following:

  • Describe each step with an action verb and a single object. If you find you can’t, break it down further until you can.
  • Do not criticize ideas. Write them down for later evaluation.
  • Aim to eliminate unnecessary steps (muda), reduce the variability in how the steps are carried out (mura) and their inconvenience (muri).
  • Assign a category to each step so that you can aggregate times by category.

You can generate your own categories as you go along and standardize them as you reach conclusions. There must not be too many (5 better than 10) and they are usually of the following type:

  • Pick up/put down
  • Walk
  • Assemble
  • Inspect or test
  • Wait
  • Adjust
  • Rework
  • ….

If there are large differences in how different operators perform the operation, several videos can be screened at the same time, with the same task carried out by different operators. It is essential to carry out this detailed review with the operators in the videos. They know things that nobody else knows, and have ideas that you want to use.

Asenta -- Operators participating in analysis

Operators participating in the analysis of their own work

3. Conclusions

When you analyze operations for the first time, it is common to discover that about 40% of the time is spent on activities other than assembly or test. This is due to a combination of wrong sequencing, redundant steps, multiple handling, inadequate fixtures, inconveniently located tools or parts, etc.

Of course, not all of these can be eliminated easily. Some can be, by redesigning or retrofitting the work station; others can be taken out of the assembly flow and performed in parallel so that, for example, the operator does not have to prepare a part while the product waits. The net productivity increase that can usually be accomplished is on the order of 30%, without overburdening the operator. In our client’s case, this means making the assembly jobs sustainable while absorbing a higher demand.

Once the summary of times by category has shown the “gold in the mine” — that is, the improvement potential, the team fleshes out the ideas generated during the review of the video, tries them out as much as possible immediately, and turns them into proposals. The following pictures shows the flip chart with sketches of the proposals generated in our sessions, and a snapshot of try-storming.

The team then turns the  improvement proposals into a detailed action plan for the short, medium-, and long term.

Once the improvements are implemented, the team shoots another video of the operation, for the following purposes:

  • Validating the improvements.
  • Standardizing the sequence of operations
  • Training other operators

Using videos to improve operations | Part 6 – Quick simograms

Here, we finally start collecting measurements from the video, focusing on what we can collect while watching without stopping. In this mode, we can break down operator time by broad categories like  “Waiting,” “Walking,” or “Assembling,” but we don’t have the time to name each task and collect comments or improvement ideas. This will require a more detailed and time-consuming analysis. 

One method, developed by Christophe Caberlon, involves two analysts, one viewing the video and the other one filling out an electronic spreadsheet. Instead of looking for state-change events in the video, we look at it in 5-second increments. Every five seconds, the analyst viewing the video calls out the state the operator has been in since the previous call. Each 5-second. Interval is assigned one column in the spreadsheet and there is one row for each state. Based on the call, the second analyst switches the color of the cell for the state and time interval.

Counting in 5-second intervals involves aliasing, but it is not a problem for a rough-cut estimate. The rows in the spreadsheet do show the state transitions in a Gantt-chart like format called “simogram,” and can summarized into proportions of time spent in each state, as in the following example:

Simogram example

This example uses cell background color to express content, which is not generally recommended because Excel does not provide built-in tools either for quick input or for analysis. The result, however, is graphically much more attractive than filling the cells with Xs. Changing the background color of a cell requires multiple steps, which cannot be repeated every five seconds. These steps, however can be recorded as a Macro. In this example, the macro has Ctrl+q as a hot key to mark a cell and Ctrl+w to unmark it. Also, each 5-second time segment must be assigned to one and only one category. When working your way through a video, it is impossible to avoid cases where one segment will be missed and another accidentally assigned to more than one category.

To detect these errors, we need to count the gray cells by column, and to summarize the times into relevant aggregates, we need to count them by rows. While Excel provides no built-in function to do this, you can find add-on modules to do it. The modules used above are due to C. Pearson

This method is also restricted in the number of states to track. It is feasible for two or three but not fifteen. With the limited number of choices, it is a good idea to include an “Other” state. The states should also be clear and unambiguous, such as:

  • Walking: the operator’s legs are moving.
  • Working: the operator’s hands are moving.
  • Waiting: all the operator’s limbs are still.
  • Touching: One of the operator’s hands is touching the product.

Categories that are abstract and subject to interpretation, like “Value-added” should be avoided. Note also that an operator who is Working or Touching, may be handling the work piece or transforming it, and we don’t have enough categories at this level to make the difference. 

Timer Pro provides a method called “Non-stop timing,” in which the analyst simply clicks on a category when observing a state transition, and the time since the previous click is automatically assigned to this category. This eliminates the aliasing due to using 5-second intervals, and relieves one analyst from the task of clicking the right spreadsheet cell every 5 seconds.

Forget Excel: This Was Reinhart and Rogoff’s Biggest Mistake | The Atlantic

See on Scoop.itlean manufacturing

“For an economist, the five most terrifying words in the English language are: I can’t replicate your results. But for economists Carmen Reinhart and Ken Rogoff of Harvard, there are seven even more terrifying ones: I think you made an Excel error.”

Michel Baudin‘s insight:

While not a story about manufacturing, it is a cautionary tale that manufacturing professionals who use Excel should ponder.

It is about two economists from a prestigious institution whose sweeping conclusions have been leading foreign governments to adopt disastrous policies and fueled the argument in favor of the same policies in the US.

Reviewing the Harvard paper, researchers Thomas Herndon, Michael Ash and Robert Pollin have discovered that Reinhart and Rogoff had selectively excluded data, calculated averages in “unusual ways,” and made a mistake in an Excel calculation.

On the face of it, the general sloppiness of the work would be forgivable in a summer intern, but the Excel error should give us pause. When inputting the range of a sum, they didn’t drag the cursor down far enough and left five rows out.

With Excel, this kind of error is easy to make and difficult to detect. In spreadsheets generated by others, I have found products with no sales showing positive revenues, and formulas with exponents applied to the wrong parameters. And I suspect others may have found errors in my own.

Following are a few recommendations that may protect you from egg on your face:

  1. Use meaningful names for cells and ranges. Refer to cells as “GDP” or “Viscosity” rather than “A3” or “RR1.” It will be easier to validate formulas, as they will more closely resemble their mathematical forms and errors will stand out.
  2. Break down complex formulas into simple ones, with additional cells or columns for intermediate results.
  3. Include comments explaining your calculations.
  4. Whenever possible, use built-in functions or pre-existing templates from a trusted source.
  5. Explain the innards of your spreadsheet to a colleague for validation, and return the favor for his or her spreadsheets.
  6. Use Excel for calculations and graphic displays, but NOT as a database management system (DBMS). Use a real DBMS for data storage and retrieval.

See on