Seasonal Trends Analysis with Excel

 

Process variation analysis is essential in order to improve it. There is always variation.
Sometimes we only have random variation such as in the case of dice throwing:

Normally we have additional variation caused by special causes and these are the ones we should analyse and reduce first.

Hourly Production 

We are going to analyse the hourly production data collected during one month:

Download this Excel file Trend Analysis.xlsx from OneDrive folder

Process stability

SPC charts allow us to check for stability: see if all variation is random.


We now apply an individuals SPC chart to our production data to check this:

The green and red dots are a clear indication of lack of stability: there are special causes affecting production.

Timestamp Data Collection

When we collect process data either manually or automatically it is essential to collect the timestamp associated with each piece of data specially since this can be done with no additional cost:


This timestamp is very useful for any time relatad analysis of the data as we will see.

Some Critical Factors we can Extract from the Timestamp

From a brainstorming session with process experts we have identified these possible critical factors affecting production:
  • Day of the week
  • Shift
  • Hour in the shift
We will now extract these factor values from the Timestamp with formulas:


C2 =WEEKDAY(A2;2)

D2 =HOUR(A2)

E2 =IF(D2<6;"N";IF(D2<14;"M";IF(D2<22;"A";"N")))

F2 =IF(E2="M";D2-5;IF(E2="A";D2-13;IF(D2>21;D2-21;D2+1)))

The resulting extended table is:



Day of the Week Pivot Table


We need to compute the sample size, the average and the standard deviation in order to calculate the confidence interval of the average:


Average Production Confidence Intervals


L3 =IF(I3="";"";J3-TINV(0,05;I3-1)*K3/SQRT(I3))

M3 =IF(I3="";"";J3+TINV(0,05;I3-1)*K3/SQRT(I3))

The resulting 95% confidence intervals:


We conclude that production om Mondays ( DOW = 1) is significantly lower than the other days.

We also notice that production on Fridays (DOW = 5) is significantly higher. 

Maybe the two are related: The line wants to meet the weekly target on Friday and to do that it empties the line so the last process steps have nothing to work on on Monday.

With this rigorous analysis we make sure we make decisions based on data rather than impressions.

Shift Confidence Intervals

We can now do the same analysis for shifts:


Production in the night shift is significantly lower than in the other two.

Hour of the Shift Confidence Intervals


We notice a significant inefficiency at the beginning and the end of the shift. Also in the 3rd hour (maybe there is a scheduled brake).

Process With no Trends

Let us do this analysis of a manufacturing process with the same average and standard deviation but no significant trends.

First we check stability with an SPC chart:

It is indeed a stable process: no out-of-control symptoms.
Let us calculate the confidence intervals of the average production for each of the factors as before:

The overlap of the confidence intervals confirm that there is no significant differences.

Conclusions









Comments

Post a Comment

Popular posts from this blog

Design of Experiments (DOE) with Excel

Image
Design of Experiments (DOE) is a very useful process improvement methodology.  Microsoft Excel has some powerful data analysis tools, which I have, successfully, used for DOE. When it comes to analyzing cause and effect, we want to know what process factors affect our outputs.   Correlation and Regression could give us an indication of the main factors, and the extent they affect the outputs. In some cases, there may be interactions among the different factors, or the mathematical relation between factors and outputs may not be linear. In these cases, it is useful to run a systematic set of experiments, to test all possible combinations of factors, to relate them to the outputs. Factorial Experiment Example We want to minimize process loss, and after some brainstorming among the process specialists, we concluded that 5 factors may affect loss. Based on current factor levels, we have selected the following levels to experiment with:    Download this Excel file DOE_w...
Read more

Response Surface Design Of Experiments with Excel

Image
Design of Experiments is a useful methodology for process improvement. The purpose is to find a relationship between process variables we can control and key process outputs in order to increase process capability . You can use Excel statistical analysis tools, Solver, Pivot charts, etc. to plan and analyse the results of these experiments. The first approach is to look for a linear relationship as shown in:   Excel DOE But in some cases this relationship may not be linear, in which case we will try a quadratic model with Response Surface DOE . We will use an example in this Excel file you can download: Download file   ExcelResponseSurface.xlsm   from OneDrive to your PC. In this example we are trying to maximize process yield acting on the critical factors pH , Temperature and Time . We will run the experiments in the Experiments simulation sheet using coded values.   Code pH Temperature Time -1 2 120 7 1 12 150 15 Factorial Experiments We start by ru...
Read more

Virtual Obeya Room

Image
The Obeya room is a "war room" used by process improvement teams to meet and solve critical multi functional problems.  The room walls are lined up with boards, highly visual charts and graphs showing program timing, milestones and progress to date and countermeasures to existing timing or technical problems.  The team meets in this room regularly but team members can also visit the room, which is fully dedicated to the project, throughout the day. What is an Obeya? Obeya Room Benefits Remove organizational barriers Visual management by displaying all relevant data required in the improvement project Encourage a collaborative environment through regular meetings Implement quicker, more effective solutions The whole team knows what is going on in real time Some Practical Constraints in the Implementation Some companies can't afford the luxury of a fully dedicated room Some of the team members may be located far away so they will only attend the meetings The data displayed ...
Read more