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_with_Excel.xlsm fro
Read more

Six Sigma Virtual Catapult with Excel

Image
  A wooden catapult has been widely used in Six Sigma education to illustrate multiple factor process variation and optimization. We will use a catapult simulator with 4 factors to practice: Process variation Linear regression Response surface analysis Process optimization This will be done using Microsoft Excel Analysis and Solver Download this Excel simulator Catapult.xlsx from One Drive Working Plan 1. What factors affect distance? 2. What values of these factors to achieve maximum distance? 3. Run a full factorial set of experiments with 4 central points 4. Can you detect curvature? Is this linear model valid? 5. Run response surface experiments to find a better model 6. What is the resulting formula to estimate distance as a function of factors? 7. What values give us the maximum distance? 8. Make 10 replicates to estimate the confidence iterval of the distance 9. Calculate the angle a to hit a target of 20 with maximum or minimum values of f, m and l Possible Facto
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 running a full factorial experi
Read more