Control Chart Excel Template | How to Plot Control Chart in Excel | Download Template

Control Chart Excel

Control Chart Excel Template |How to Plot Control Chart in Excel | Download Template:

Hi! Reader, today we will guide you on how to plot a control chart in Excel with an example. To take more concentration on Process Improvement, the control chart always takes vital rules to identify the Special causes and common causes in Process Variation. Control Chart Excel Template is available here; just download it by clicking on the below link.

Download the Control Chart Excel Template.

Control Chart Excel Template

[Figure 1-X- Bar Control Chart Excel Template]

control chart excel

[Figure 2-R-Control Chart Excel Template]

A Control Chart is a graphic representation of a characteristic of a process, showing plotted values of some statistic gathered from that characteristic, a centerline, and one or two control limits. It has two basic uses as an adjustment to determine if a process has been operating in statistical control and to aid in maintaining statistical control.

Control Chart Approach for Continual Process Improvement:

  • Data Collection.
  • Control.
  • Analysis and Improvement.
data
  • Data Collection:-
  1. To Collect Data and Plot the Control Chart.
  • Control:-
  1. Calculate control limits from process data.
  2. Identify Special Causes of Variation and Act upon them.
  • Analysis & Improvement:-
  1. Quantify Common Cause Variation, and take action to reduce it.
You will also like to read the CAPA Process

7QC Tools for Problem Solving | What are 7 QC Tools

How to Plot Pareto Chart in Excel ( with example)

How to Create Control Chart Excel Template| Step-by-Step Guides (X-Bar & Range Chart) with Example:

Step-1: Collect The Data day-wise/shift-wise.

control chart excel

As you can see in the above figure, we have collected data with a sample size of 5 for A-Shift with frequency (5 samples per 2 hours). So we have only one shift data for 5 days. Total 100 number observations.  You are supposed to collect the data as per the Control Plan or Quality Assurance Plan.

Step-2: Select the Data types and applicable Control Chart.

So we have variable type data and the sample size is 5. Hence the applicable Chart is the Average and Range Chart (X-Bar & Range).

Step-3: According to data type and Sample size, presently we are going to plot the X-Bar & R-Chart. So individually we will plot both charts (X-Bar Chart & Range Chart). First, we will plot the X-bar chart and then the R-chart.

3.1 X-Bar Chart:
Control Chart Excel

 Before we start, just go through the green highlighted terms in the above figure as [1] Average

[2] X-Double Bar means an average of average. [3] Standard Deviation. [4] UCL. [5] LCL.

Calculation:

[1] Average:
Control Chart Excel

Make sure that your attention is now on the right side corner of the above figure. To calculate the average value of individual subgroup size. You have to type as (=average)and then double click on the average function and next select the sample value from x1 to x5.

[2] X-Double Bar: After calculating the Average value of all Subgroups (Individual Date wise), now we have to calculate the average of Average (Average of X-Bar).  

[3] Standard Deviation: Standard Deviation of Average (X-Bar),

steps

Type as (=Stdev) and select all X-Bar Data to Calculate the Std. Dev. of Average.

[4] UCL: 

UCL=X Double Bar +3*Sigma

UCL= X Double Bar +3*Standard Deviation

For the calculation of the UCL in Excel use the above formula.

[5]LCL:

LCL=X Double Bar -3*Sigma

LCL= X Double Bar -3*Standard Deviation

Use the above Formula in Excel.

3.11 Plot X-Bar Chart: This is the last step to plot the X-Bar Chart by using Line Graph in Excel, follow the below steps:

steps
steps

Simply Follow Sl. No.1 to 4.

In Sl. No.1, Select X-Bar, X-Double Bar, UCL, LCL, and then select Insert Option and next to Line Chart. After selecting the Line Graph/Chart, The X-Bar Control Chart Excel Template will be ready as below.

control chart excel
3.2 Range Chart:
control chart excel

To Plot the R-Control Chart, we have to calculate the [1] Range. [2] R-Bar (Average of Range). [3]UCL. [4]LCL.

[1] Range: R=Max. Value – Min. Value of Subgroup.

control chart excel

[2] R- Bar (Average of Range): Put the Excel formula of average.

[3] UCL:

UCL= D4 x R-Bar

UCL= 2.114 x R-Bar Value of individual Subgroup. (Note for Subgroup Size 5, D4=2.114).

Use this formula in Excel to calculate the UCL.

[4] LCL:

LCL=D3 x R-Bar

LCL=0 (Note Foe subgroup size 5, D3=0)

Simply put the “0” in the Excel sheet.

3.22 Plot R-Chart: Just follow steps 1 to 3, and select the line chart.
control chart excel

In step-1, you have to select the “Range, R-Bar, UCL, and LCL” simultaneously and then select the Line Chart, after selecting the line chart R-Control Chart Excel Template will be ready as below 

Control chart excel
R-Control Chart
FAQ:

Q1: What are control chart rules?

A1: Read the full article What is SPC”.

Q2: How to add upper and lower control limits in Excel?

A2: Carefully read the aforesaid Articles.

Q3: How to create a control chart in Excel 2013?

A3: Step by Step guide is described above with Statistical process control chart examples. Please go through it.

Q4: How to create a Six Sigma control chart in Excel?

A4: Control charts are classified into two types [1] Variable type and [2] Attribute Type. Both two types are further classified into several as

[1]Variable types
  1. X and MR Chart
  2. X-Bar and Range
  3. X-Bar and S
[2] Attribute Chart
  1. np-chart
  2. p-chart
  3. u-chart
  4. c-chart

In the above articles, we have described only how to create an X-bar and range type Control Chart in Excel with a process control chart example. As you can see all these above types of control charts are used in Six Sigma projects but the applicable chart depends on Data type and Subgroup size (Sample size).

Q5: How to calculate upper and lower control limits (UCL & LCL) in Excel?

A5: For X-Bar Chart-UCL: 

UCL=X Double Bar +3*Sigma

UCL= X Double Bar +3*Standard Deviation

For the calculation of the UCL in Excel, use the above formula.

LCL:

LCL=X Double Bar -3*Sigma

LCL= X Double Bar -3*Standard Deviation

Use the above Formula in Excel.

For R-Chart:

UCL:

UCL= D4 x R-Bar

UCL= 2.114 x R-Bar Value of individual Subgroup. (Note for Subgroup Size 5, D4=2.114).

Use this formula in Excel to calculate the UCL.

LCL:

LCL=D3 x R-Bar

LCL=0 (Note Foe subgroup size 5, D3=0)

Simply put the “0” in the Excel sheet.

Q6: What are the types of control charts?

A6: [1] Variable types
  • X and MR Chart
  • X-Bar and Range
  • X-Bar and S
[2] Attribute Chart
Useful Articles:

Scatter Diagram Template.

Pareto Chart Template.

Fishbone Diagram Template.

Histogram Template.

Run Chart Excel Template.

More on TECHIEQUALITY

Thank you for reading…….keep visiting Techiequality.Com

I hope the above article is useful to you…

Popular Post:

8D Report Example | Download Case Study Report:

8D Report Example

How to fill up 8D Report Template |8D Report Example:

Hi. ! Reader, today we will discuss the 8D Report Example, case study, and How to fill up the 8D Report Template, if you have not yet downloaded the 8D Template then download the Format /template /form by clicking on the below link.

Download-8D Template/Format.

8D Report Example | Step-by-Step Guides: Example-1: There is a Customer Complaint i.e. Shrinkage on the Sump (Automobile casting part). And we have done the case study of said customer complaint and filled up the 8D template. Details are illustrated below

Case Study 1: Ingate Shrinkage (Casting)

8d report example
DOWNLOAD-TEMPLATE.

EXAMPLE: Shrinkage Defect (Casting):

D1- SUPPLIER TEAM MEMBER NAMES: Champion name, Team Leader name, and Team Member name.

D2- PROBLEM DESCRIPTION e.g. Shrinkage Defect.

What Shrinkage 
Who Customer name 
Where  In Process 
When Last batch 
Why SH in Ingate 
How Much 2% 
D-2 (5W1H Form)

D3- IMPLEMENTING CONTAINMENT ACTION:

Target dateActual date
Immediate stop the consignment & segregate good parts2.2.192.2.19
D-3-Containment Action Table

D4- IDENTIFY PROBLEM ROOT CAUSE:

Why1  Why SH at Ingate area
Why2  Why high pouring temperature
Why3  Why pyrometer reading was not correct
Why4  Why checking of pyrometer’s condition was not done
Why5  
Why-Why Analysis
Root CauseChecking of pyrometer’s condition was not done
D-4

D5- PERMANENT CORRECTIVE ACTIONS:

Corrective Action Plan Resp. by
PM/ Condition of Pyrometer will be checked periodically w.r.t Master one. Maintenance Supervisor
D-5-CAP

D6- IMPLEMENT PERMANENT CORRECTIVE ACTIONS:

Corrective Action Plan Resp. by Target date The actual date of Completion
A weekly PM/Condition checking Schedule has been made and checked all Pyrometer Maintenance Supervisor 10.02.2019 10.02.2019
D-6

D7- PREVENT RECURRENCE:

Preventive Action Plan Resp. by Target date The actual date of Completion
A weekly schedule will be made Maintenance Supervisor Weekly Continuing
D-7-PR

D8- TEAM AND INDIVIDUAL RECOGNITION: Congratulations to the team member.

In the first case study, we have discussed in ingate shrinkage of the sump and filled up all the analysis in 8D-Template, and now, we are going to discuss another case study i.e. CS No-2, details of the analysis are illustrated below.

Case Study 2: Pin-hole Defects (Casting)

A Company PQR Ltd found nearly 3% of the last batch of BOP items as defective after machining operation due to a pinhole defect in casting. So the purchase head decided to ask and submit the full analysis of the said problem to their supplier in the 8D report. The same Analysis is illustrated below.

8d report example
DOWNLOAD-FORMAT

EXAMPLE: Pin-hole Defects (Casting)

D1- SUPPLIER TEAM MEMBER NAMES: Champion name, Team Leader name, and Team Member name.

D2- PROBLEM DESCRIPTION e.g. Pin-hole Defect.

WhatPin-hole
WhoM/S PQR Ltd
WhereIn-process
WhenLast batch
WhyPin-hole at casting base
How much3%
5W1H

D3- IMPLEMENTING CONTAINMENT ACTION:

ICASegregate the defective part (pin-hole defect)
Containment Action

D4- IDENTIFY PROBLEM ROOT CAUSE:

Why-1Why pin-hole?
Why-2Why the core problem?
Why-3Why core was not cured properly?
Why-4Why the curing/drying time was not modified?
Why-5
5W Analysis
Root CauseCuring/drying time was not validated
RC

D5- PERMANENT CORRECTIVE ACTIONS:

Corrective action planResp. by
Process and product will validate w.r.t new drying timeProcess QA Engineer
CA

D6- IMPLEMENT PERMANENT CORRECTIVE ACTIONS:

Corrective action planResp. byTarget dateActual Date
10 samples will be made & respective process & product characteristics will be checked whether the characteristics are meeting the specifications or not.Process QA Engineerxx/yy/2020xx/yy/2020
IPCR

D7- PREVENT RECURRENCE:

Preventive action planResp. byTarget DateActual Date
Validation process SOP will be made including change controlQA.Engineerxx/yy/2020xx/yy/2020
PR

D8- TEAM AND INDIVIDUAL RECOGNITION: Congratulations to the team member.

FAQ:

What is 8D in quality?

The 8D is eight disciplines of problem-solving, these are mainly, 1-supplier team member names, 2-problem description, 3-implementing containment action, 4-identify problem root cause, 5-permanent corrective actions, 6-implement permanent corrective actions, 7-prevent recurrence, 8-team, and individual recognition.

What are the 8 disciplines of an 8D-DMN report?

The 8D has 8 nos disciplines which makes it a systematic way to resolve the problem and the disciplines are

  1. Team Formation or Establishing the team or creating a team.
  2. Problem Description or defining the problem.
  3. Implementing containment actions or Interim action.
  4. Identify the problem’s root cause or RCA.
  5. Developing permanent corrective actions or corrective action
  6. Implementing permanent corrective actions or implementing & validating corrective actions
  7. Preventing reoccurrences or preventive actions
  8. Congratulate the team or Team & individual recognition.

The 8D-DMN is a very popular methodology that is frequently used in manufacturing industries to resolve the notified defective material in all the stages including customer complaints as well. we have already discussed two no case studies or practical manufacturing examples for a better understanding of the concept, application, and thorough knowledge of 8D template or format.

This concept enhances your depth of knowledge on 8D and its principles on how to fill up the template or format, which is described above. After doing so your confidence in 8D activities for internal application and for application on customer complaints will be enhanced.

Useful Post

QA Excel Template | Top Skills of Quality Assurance Engineer

5 Whys Excel Template , Download why why analysis excel format

What is Quality 4.0 | What Type of Skill Set Needs to be Developed as an Employee

Why Why Analysis | Template | 5 Why Analysis Method with Manufacturing Examples

Normal Distribution Probability Formula, Calculation & Manufacturing Examples

More on TECHIEQUALITY

Thank you for reading…….Keep visiting Techiequality.Com

Popular Post

Control Chart in Minitab | How to plot a Control Chart in Minitab

Control Chart in Minitab

How to Create a Control Chart by Minitab | Minitab Control Chart :

Hi! Reader, Today we are going to learn on how to Create a Control Chart in Minitab.

Download the guide in PDF.

A control Chart is a popular tool to identify the process Variations and causes (Common or Special Cause). If you would like to know more about different types of Control Charts then read “What is SPC?”. You will also like to read on “how to plot a control chart in Excel.

Download the Control Chart Excel Template.

Steps of how to Create Control Chart in Minitab 18:

Step-1:

When you will open the Minitab 18, the main screen will appear like below.

Control Chart in Minitab
Step-2:

Let’s take an example here to understand better. We are having 100 numbers reading of a block’s length as,

Length of Block=120±1mm, Subgroup Size=5, Sample frequency=5 samples per hour.

Date Observations/ Reading
12.03.2019 119.5
12.03.2019 119.3
12.03.2019 119.4
12.03.2019 119.5
12.03.2019 119.7
12.03.2019 120
12.03.2019 120.1
12.03.2019 120.5
12.03.2019 120.3
12.03.2019 120.4
12.03.2019 120.4
12.03.2019 120.6
12.03.2019 120.3
12.03.2019 119.7
12.03.2019 119.8
12.03.2019 119.2
12.03.2019 119.5

…..

….

 13.03.2019 121.1
13.03.2019 120.2
13.03.2019 120.3
13.03.2019 120.4
13.03.2019 120.5
13.03.2019 120.9
13.03.2019 120.8
13.03.2019 120
13.03.2019 120.1
Control Chart in Minitab

As you can see in the above figure, the same 100-number reading has been entered in box no.1. Now we are supposed to select the Average and range type Chart (because length readings are variable data and we have taken subgroup size is 5). If you would like to know the selection process of different types of Control Charts then read the article “What is SPC?”. Just follow the step-2 to step-5 in the above figure to select the “Average & Range” type control chart.

Step-3:

After selecting the “X-bar & Range chart”, such a dialog box will appear on the screen. Now just give more focus on below dialog box, because I will be describing it one by one here. In the box no-1, you can able to see the data point’s column. Now you have to select the data point in box no-3, and then enter “5” in box-4. Next, we have to enter labels to write the Title name.

Control Chart in Minitab
 Step-4:

Title name

Control Chart in Minitab
1. Enter on Labels.
Control Chart in Minitab
2. Name the title as you wish.
Step-5

After selecting the data point’s column, subgroup size value, and Label, now you have to enter in “ok”. After doing so Control Chart will appear on the screen. 

Control Chart in Minitab

Minitab Control Chart:

Control Chart in Minitab
FAQ:

Q1: How to change the “X” axis value?

A1: Double click on the “X” axis then you can able to see a dialog box where you have to click on the “time” icon (marked in red color box in below figure) then, select the stamp option, and next to select the column which you would like to add in “X” axis.

steps
Download Templates:

Thank you for reading ….Keep visiting Techiequality.Com

Popular Post:

MTBF and MTTR Template, Format, Calculation | Manufacturing Example

MTBF and MTTR

MTBF and MTTR Template, Format, Calculation | Manufacturing Example:

What is MTBF?

Hi, readers in this article we will be covering both MTBF and MTTR calculation with a manufacturing example. So read carefully, learn the concept, and implement it in your organization. Mean time between failures (MTBF) is the arithmetic average time between failures. It helps to measure the performance of a machine or assets. In manufacturing industries, we are always worried about the breakdown of machine or equipment failure, which can make a large loss and ultimately increase the downtime (Stop time) of the machine. Uptime is a big factor to enhance productivity. The higher the MTBF means the machine runs a long time before failing.

MTBF and MTTR
[Figure]

As you can see in the above figure operation has stopped after 8 hours of duration but meanwhile, three failures have occurred. Due to this failure machine was stopped for maintenance. In this case, 8 hours was the available operational time but 6 hours was the total running time. Considering all the above data, the MTBF value is 2 hours. It means the average time between 1st failure & 2nd failure or 2nd failure & 3rd failure is 2 hours. In the manufacturing industry, there are multiple operations and several machines are being used for production. So during the calculation of MTBF, you have to think about multiple factors.

DOWNLOAD– Template of MTBF and MTTR. (mttr, mtbf formula excel)

MTBF Formula:

The total operational time or total run time divided by the total number of failures.

MTBF= (Total Operational Time ÷ Total Number of Failures)

Calculation of MTBF (Mean Time Between Failures):

Let, for example, a product “XYZ” has manufactured in a simple method by using a single machine [see the below figure]. This machine runs three shifts per day with monthly 26 working days. But in last month’s operation, the maintenance person had booked the machine’s total number of failures (Breakdown) was 5. And total break-down time was 3 hours. According to all the above data, Monthly MTBF has been calculated and mentioned below;   

MTBF and MTTR
[Example-1]

MTBF = Total operational time ÷ Total number of failures

Total operational time (Run time):

= Planned production time – Stop time

(624 hours – Stop time)

= (624- 81), here stop time included the lunch time, dinner time, tea time, breakdown time, tool changeovers  time)

=543 hours

Now, the Total Operational Time of last month = 543 hours, Total number of failures of last month =5 times.

MTBF of last month = (543÷5) hours

=108.6 hours

Similarly, we have calculated the MTBF of a complex process having multiple numbers of machines.

Mean time between failure example
[Example-2]

In example 2, all 13 machines run together to produce a product. There is a total of three sub-process i.e. process-1, 2 &3. And process-1 has 1 machine and process-2 &3 have 6 machines each. First of all, we will calculate the MTBF value of each process and then the overall process. Details of data like a machine running time, failure frequency, etc. are given below,

MTBF of Last month:

Total Operation Shift = A, B, C (3 shifts).

Total Number of Machines = 13

Working days of last month = 25 days

Total Number of failures (B/D) =12 times [process-1 =2, process-2 =6, process-3 =4]

Total breakdown time = 10 hours [process-1 =1, process-2 =8, process-3 =1]

Stop time of each machine per day due to Lunch, Dinner, and tea = 3 hours

MTBF of Process-1:

MTBF = Total operational time ÷ Total number of failures

Total operational time (Run time):

= Planned production time – Stop time

(600 hours – Stop time)

= (600- 76)

=524 hours.

Now, Total Operational Time of last month = 524 hours, Total number of failures of last month = 2 times.

MTBF of last month = (524÷2) hours

=262 hours

MTBF of Process-2:

MTBF = Total operational time ÷ Total number of failures

Total operational time (Run time):

= Planned production time – Stop time

(3600 hours – Stop time)

= (3600- 458)

=3142 hours

Now, Total Operational Time of last month = 3142 hours, Total number of failures of last month = 6 times.

MTBF of last month = (3142÷6) hours

=523.666 hours

MTBF of Process-3:

MTBF = Total operational time ÷ Total number of failures

Total operational time (Run time):

= Planned production time – Stop time

(3600 hours – Stop time)

= (3600- 451)

=3149 hours

Now, Total Operational Time of last month = 3149 hours, Total number of failures of last month = 4 times.

MTBF of last month = (3149÷4) hours

=787.25 hours

MTBF of Overall process: = {(262 + 523.666 + 787.25) ÷3}

=524.305 hours

ProcessMTBF in hours
Process-1262
Process-2523.67
Process-3787.25
Overall Process524.305
MTBF-Table

What is MTTR?

It is the average time required to correct and repair a failed component or equipment or device to put it in working order. It reflects how much average time is spent to correct or repair the component.

MTTR Formula:

Total maintenance time or total B/D time divided by the total number of failures.

MTTR = Total maintenance time ÷ Total number of repairs.

MTTR Calculation (Mean time to repair):

Example-3.

It’s a simple manufacturing process consisting of a single machine. In last month 3 times machine was stopped due to breakdown & the total breakdown or repair time was 2 hours. So here we are going to calculate the MTTR, details of the calculation are given below.

MTBF and MTTR
[Example-3]

MTTR = Total maintenance time ÷ Total number of repairs.

= (2 ÷ 3)

= 0.67 hours

Example-4:

mean time between failure
[Example-4]

In Example-4, the whole process is covered by the two sub-process i.e. process-1 and process-2. In first sub-process has a total of 4 machines and similarly, the second process has a total of 6 machines in operation. All data related to breakdown and frequency of failure are given in below table;

ProcessBreakdown or Maintenance timeFrequency of failure or total number of repairs
Process-15 hours3
Process-24 hours6

MTTR of Process-1: 1.67 hours

MTTR of Process-2: 0.67 hours

Overall process MTTR = (1.67 + 0.67) ÷2

=1.17 hours

ProcessMTTR in Hours
Process-11.67
Process-20.67
Overall Process1.17
MTTR-Table

Why MTBF and MTTR are important for manufacturing industries?

MTTR indicates the efficiency of corrective action of machine failure and similarly

MTBF indicates the average machine run time between failures.

Monitoring both indicators can give the idea to control your maintenance method and indicate the opportunity for improvement in the same field. You can easily identify the training needed for your maintenance personnel after analyzing the values. You can also categorize the production loss w.r.t breakdown time.

What is the requirement of IATF 16949 w.r.t MTBF and MTTR?

As per the standard requirement, an organization shall document the maintenance objectives, for example, the objective of MTTR and MTBF.

How do you calculate MTBF and MTTR?

We have calculated the MTBF and MTTR with a manufacturing example, please read the above example.

What is the MTTR formula?

MTTR = Total maintenance time ÷ Total number of repairs.

How do you calculate MTBF?

MTBF= (Total Operational Time ÷ Total Number of Failures)

How to reduce the MTTR hours and How to increase the MTBF hours?

For improving both the parameters, we have to keep the focus on below things, like

  • Plan and execute preventive maintenance.
  • Plan and execute the predictive maintenance.
  • Reduce the breakdown time and frequency.
  • optimize the operation time.
  • Rank the machines and monitor the health of machines and equipment.
  • Do the periodic CLIT (Cleaning, Lubrication, Inspection, and tightening).
  • Do the Kaizen on machines.
  • provide the training to the maintenance team.
  • Display the OPL for the machine operators for a better understanding of the machine’s operation.
  • Visual marking on the machine and introducing the poka-yoke

The above points are not limited to the given list but you can do more best practices to improve the MTTR and MTBF.

Free Templates / Formats of QM: we have published some free templates or formats related to Quality Management with manufacturing / industrial practical examples for better understanding and learning. if you have not yet read these free template articles/posts then, you could visit our “Template/Format” section. Thanks for reading…keep visiting techiequality.com

Useful Post:

Types of Fishbone Diagram |Dispersion Analysis |Enumeration |Process Classification

Dispersion Analysis Cause & Effect Diagram Template |Download Excel Format

Strategies for Manufacturing Process Improvement |11+ Strategies

More on TECHIEQUALITY

Popular Post:

OEE Means | Full Form | Download OEE Template

OEE Means

OEE Means | Full Form | Download OEE Template:-

OEE Means, Overall Equipment Effectiveness. Seiichi Nakajima has invented overall equipment effectiveness in the 1960s to evaluate the manufacturing operation how effectively it is utilized.

Overall Equipment effectiveness is a methodology to measure the productivity of Process industries.

OEE = A x P x Q
(A-Availability. P-Performance. Q-Quality). OEE is the main key performance indicator for Total Productive Maintenance and also for IATF 16949.

Download OEE Template (Per Shift Per Machine Basis).

overall equipment efficiency calculator

Download OEE Template (3-Shift Per Machine Basis).

overall equipment efficiency template

 How to Use the Above OEE Template:-

After downloading the above Overall Equipment Effectiveness Template, please carefully read the instructions as:-

  • All yellow color boxes are changeable means you just enter the value accordingly.
  • The unit of measurement of time is in minute form, so convert all time values into the minute form.
  • Rather than the yellow color box, others will calculate automatically.

Example:-

OEE calculation (Per Shift per Machine Basis):-

Item Data
Shift Length 8 hours (8*60=480 Minutes)
Breaks ( Tea and Lunch) 60 minute
Downtime 47 Minutes
Ideal Cycle Time 1.0 Minute
Total Production 373 Pieces
Reject Quantity 20 Pieces
Data Table

OEE Calculator (Per Shift Per Machine)
OEE = 84.048%
Availability(A) Performance(P) Quality(Q)
A = 0.88810, P= 1.0000, Q= 0.94638

Run Time= 373, Ideal Cycle Time= 1 , Good Quantity = 353

Planned Production Time= 420, Total Production Quantity= 373

Rejection Quantity= 20
Stop Time/Down Time= 47

Shift Length= 480
Breaks= 60

UOM of Time is Minute.

oee means

Example 2:-

Item Data
Shift Length 8 hours (8*60=480 Minutes)
Breaks ( Tea and Lunch) 30 minute
Downtime 35 Minutes
Ideal Cycle Time 1.2 Minute
Total Production  345 Pieces
Reject Quantity 8 Pieces

Overall Equipment Effectiveness Calculator (Per Shift Per Machine)
Overall Equipment effectiveness = 89.867%
Availability(A) Performance(P) Quality(Q)
A = 0.92222 ,P= 0.9976 ,Q= 0.97681

Run Time= 415 ,Ideal Cycle Time= 1.2 ,Good Quantity = 337

Planned Production Time= 450 ,Total Production Quantity= 345

Rejection Quantity= 8
Stop Time/Down Time= 35

Shift Length= 480
Breaks= 30

UOM of Time is Minute.

overall equipment efficiency full form

OEE calculation (3-Shift per Machine Basis):-

Item Data
Shift Length(3-Shift) 24 hours (24*60=1440 Minutes)
Breaks ( Tea and Lunch) 180 minute
Downtime 141 Minutes
Ideal Cycle Time 1.0 Minute
Total Production  1119Pieces
Reject Quantity 60 Pieces

Overall Equipment Effectiveness Calculator (3-Shift Per Machine)
Overall Equipment Effectiveness = 84.048%
Availability(A) Performance(P) Quality(Q)
A = 0.888, P= 1.000, Q= 0.946

Run Time= 1119 , Ideal Cycle Time= 1 ,Good Quantity = 1059

Planned Production Time= 1260, Total Production Quantity= 1119 Rejection Quantity= 60
Stop Time/Down Time= 141

Shift Length= 1440
Breaks= 180

UOM of Time is Minute.

overall equipment efficiency fullform

Example 2:-

Item Data
Shift Length(3-Shift) 24 hours (24*60=1440 Minutes)
Breaks ( Tea and Lunch) 90 minute
Downtime 100 Minutes
Ideal Cycle Time 1.5 Minute
Total Production  833Pieces
Reject Quantity 30 Pieces

Overall Equipment Effectiveness Calculator (3-Shift Per Machine)
OEE = 89.222%
Availability(A) Performance(P) Quality(Q)
A = 0.926, P= 1.000 , Q= 0.964

Run Time= 1250 , Ideal Cycle Time= 1.5 ,Good Quantity = 803

Planned Production Time= 1350, Total Production Quantity= 833 Rejection Quantity= 30
Stop Time/Down Time= 100

Shift Length= 1440
Breaks= 90

UOM of Time is Minute.

oee fulform

Now, I hope it’s clear to all “how to calculate the overall equipment effectiveness” by using the above Template. If you would like to calculate manually and would like to know more about the formula then click here.

I hope you understand the concept of OEE, but I would like to request you calculate the Rate of Quality of the below conditions and match your answer with our solution.

suppose a company produced 50000 quantities of product A and rejected quantities are 200 nos. Calculate the rate of quality and rate of quality percentage.

Solution:

Total parts Produced = 50000 nos

Total parts rejected = 200 nos

Rate of quality = [Total parts produced – Total parts rejected] / Total parts produced

= Good parts produced / Total parts produced.

ROQ = [50000-300]/50000

= 49700/50000

0.994

ROQ = 0.994

ROQ% = ROQ x 100

=0.994 x 100

= 99.4%

Similar and Useful Articles:

OEE Calculation-How To Calculate OEE (Overall Equipment Effectiveness) with Example.

Process Capability Analysis.

Histogram Example | Foundry Industries Examples.

Histogram Template with example | Download

Download Free Template

More on TECHIEQUALITY

8D Report | Free Download of 8D Template or Format

8D Report

8D Report | Free Download of 8D Template  

8D Report, Format, or Template is ready for you just click on Download. Here we will describe the 8D Report with a Manufacturing related example. 

DOWNLOAD-(8D-DMN Report Template /format /form in Excel Format)

Basic Info. of 8D Report:

It’s a Problem-solving approach followed by Eight Critical Steps. This is used to provide excellent guidelines to identify the Root cause of The Problem or Issue. Moreover, an 8D approach is used to implement the solutions to prevent recurring problems. It was first used in the automotive industry.

Generally, Customer asks their Suppliers / Vendors / External Providers to submit the 8D Report as of and when they find the Defective material at their ends as BOP, Raw Materials, etc.

Eight Steps of 8D Report:
  • Team Formation
  • Problem Description
  • Implementing Containment Actions
  • Identify Problem Root Causes
  • Developing Permanent Corrective Actions
  • Implementing Permanent Corrective Actions
  • Preventing Re-occurrences
  • Congratulating the Team
8D Report
D1:- Team formation

A Cross-Functional team with multi-skilled Members needs to be selected. 

D2:- Problem Description

Describe the Problem in the form of 5W 2H as Who, What When, Where, Why, How, and how much.

D3:- Containment actions

Temporary Action needs to be implemented until a permanent solution is implemented.

D4:- Identify Problem Root Cause

After the implementation of Containment Action, We have to do the Root Cause Analysis to find out the Root Cause for the implementation of the permanent solution. The common tool used for RCA is Why-Why Analysis.

D5:- Developing permanent corrective actions

After getting the Root Cause of a Problem, we have to prepare an Action plan for the Possible solution. From there Permanent Corrective actions need to be selected.

D6:- Implementing permanent corrective actions

As soon as possible, Developed Permanent Corrective Actions need to be implemented. Implementation Plan / Activity Plan / Milestone Plan will help you better monitor and track the status of Activities.   

D7:- Preventive Re-occurrences

Here Preventive Action needs to be taken to minimize the Reoccurrences. In doing so, a review of the Management system, SOP, Control plan, FMEA, and Risk Management, so that it will prevent the Reoccurrence.

D8:- Congratulating the team

Now, it’s time to congratulate the Recognize your team for the joint effort. It is the most important step among All steps, which will help you to improve the moral part of people’s engagement. 

Example-1 Customer asked 8D report for casting pin-hole issue.
8d report example
DOWNLOAD-Format / Template
Details of the above Example:

D-1: Supplier team member name: Organization is supposed to form a team with a Team leader and Champion.

D-2: Problem Description: You may describe the problem in a 5W1H manner or 5W2H.

8D Report
D-3: Implementing Containment action:

Immediate action needs to be taken so that the defective product will not be dispatched to the customer.

8D Report
and also segregate the pinhole casting.

D-4: RCA: You can find out the Root cause by why-why analysis, hypothesis testing, etc.

8D Report
D-5: Corrective Action:

Action to eliminate the root cause of the problem.

pin hole corrective action

D-6: Implement CA: here in this stage we have to implement the action of the corrective action plan.

implement permanent corrective action
D-7: Preventive action:

Action to eliminate the potential cause of the problem. according to the new ISO 9001:2015 standard requirement, Risk analysis is there. but in the above example we have mentioned the PA as;

preventive recurrence
Example-2:

We have discussed here another practical manufacturing example for your better understanding, For Example, At the customer end, 80% of the last consignment material found a machining problem, so the customer asked their supplier to submit the 8D report. That 8D report has been described below, kindly go through it to know the details.

Illustration of above example-2:

D-1- SUPPLIER TEAM MEMBER NAMES: There should be a team member, leader, and champion. so choose team members smartly covering the several functions as CFT, so that your team’s technical strength will be enhanced.

D-2- PROBLEM DESCRIPTION:

This is the vital step where you have to confirm the problem and similarly need to describe it.

D-3- IMPLEMENTING CONTAINMENT ACTIONS: Take immediate action so that your customer will not receive a non-conforming product/ material /item. In the above example, the supplier has stopped the consignment of the mix-up item ( Good and NG material) to the customer.

D-4- IDENTIFY THE PROBLEM ROOT CAUSE:

You can use several techniques or tools to find out the root cause, the commonly used technique is the why-why analysis.

D-5- PERMANENT CORRECTIVE ACTIONS: An action to eliminate the root cause of a problem, so whatever the RC will be found by root cause analysis then you have to take action on it. SO here covering the CAPA part is the important part.

D-6- IMPLEMENT PERMANENT CORRECTIVE ACTIONS & D-7- PREVENT RECURRENCE:

Refer to CAPA.

D-8- TEAM AND INDIVIDUAL RECOGNITION: Congratulations to your team members.

FAQ:
What are the 8D steps?

The 8D is [1] Team Formation [2] Problem Description [3] Implementing Containment Action [4] Identify Problem Root Cause [5] Developing Permanent Corrective Action [6] Implementing Permanent Corrective Action [7] Preventing Reoccurrence [8] Congratulate the Team.

What is the difference between CAPA and 8D?

The CAPA and 8D are problem-solving approaches. During the RCA (Root cause analysis) of the problem, we generally develop the Action plan and represent it in different and different formats/templates like CAPA, 8D, G10, etc. The main objective or purpose of both methods is to develop the action plan, implement the action plan, and measure the effectiveness of the action plan. I meant to say that both are problem-solving approaches/ methods.

More on TECHIEQUALITY

Risk Management Process | Risk related to IATF 16949, ISO 9001, ISO 14001, and ISO 45001& download sample template

Risk Management

Risk Management :

Risk Management related to IATF 16949, 9001, 14001, and 45001 will be discussed here. And also will discuss how to address Risks and its mitigation plan.

Download Risk Register Template.

Risk Definition: Effects of Uncertainty is called Risk. An effect has two properties, Positive and Negative.
Negative effects are also called Risk and Positive Effects are called Opportunity.

All the latest business standards like IATF 16949:2016, ISO 9001:2015, ISO 14001:2015, and ISO 45001:2018 are based on risk-based thinking. To comply with the standard requirement we have to identify the Risks and opportunities and need to do the mitigation plan for those that have the significant effects.

Risk Management Process:

  • Step-1 : Identification of Risks
  • Step-2 : Analysis of Risks
  • Step-3 : Evaluation of Risks
  • Step-4 : Treatment of Risk
  • Step-5 : Monitoring, Review, and Control
risk management
Identification of Risks:
Risk Related to ISO 9001:2015 and IATF 16949:2016:

During the identification of Risks, we shall consider the [1] Internal and external issues, [2] Needs and Expectations of Interested Parties, [3] Significant effects of QMS Intended Results like Significant Objectives, Process related Significant Effects, warranty, Field Failure, lacking of technology, Bossiness Competition, market value,  Shortage of Raw materials, outsource Process Effects to Organization, etc.

Apart from the above, we shall also include in its risk analysis at a minimum lessons learned from Product recalls, Product audits, Field Returns, and Repairs, Scrap and rework, etc.

Examples of Risks: High Warranty Percentage, Lack of Technology, High Scrap, High B/D, Less Selling Value, etc.

Identified risks are generally represented in the Risk register.

 Risk Related to ISO 14001:2015 and ISO 45001:2018:

While Identifying the risks related to ISO 14001, we have to consider the [1] Internal and external issues, [2] Needs and Expectations of Interested Parties, [3] Significant environmental Impacts [4] Compliance Obligations.

When determining the risks related to ISO 45001, we have to address the risk by accounting for [1] hazards [2] OHS risks, [3] Legal and other requirements, [4] Internal and external issues, [5] Needs and Expectations of Interested Parties.

Examples of Risks: High Noise, Water Pollution, Discharge of untreated Water, Solid Waste Spilled at the outside boundary of the factory, etc.

Analysis of Risks:

The main goal of Risk Analysis is to calculate the risk score/ rank and categorize the different types of Risk. In this method, we have to collect the data for the Probability and Impact score.

Example:

[1] High Noise at XYZ Area, let probability is 3 out of 10 scales and Impact is 5 out of 10 scales.

Risk Score = 15

51-100 High Risk
25-50 Medium Risk
<25 Low risk

In the above example, High noise falls under the Low Risk.

Evaluation of Risks:

The Organization will decide on the Significant Risks Cut-off Value. Let us decide here 51 to 100 is the Cut-off value, then we can surely say the above Risk [High noise] does not come under the Significant Risk.

Here we just need to evaluate the Risk whether significant or insignificant.

Treatment of the Risks:

A mitigation plan has to be taken to bring down the significant Score to an Insignificant Score

Monitoring, Review, and Control:

After the implementation of the Action plan, the Effectiveness of Risk needs to be done by regular monitoring of data. 

You Could also like to read these Articles:

More on Techiequality

Thank You for reading……. Keep visiting Techiequality.Com

Popular Post:

Root Cause Analysis | 8 Steps + Free RCA Template

Root Cause Analysis

Root Cause Analysis or RCA:

Root Cause Analysis is a frequently used and Popular Method to aid in catching the exact reason for a problem. It will help you to find out the primary cause of the problem so that we can determine what happened, and why it happened and also formulate the Prevention so that the problem will not occur again.
It’s a vital part of the Continuous Improvement.

Why-Why Analysis and 7-QC tools are the key inputs to execute the RCA.

Root Cause Analysis Process:-

Root Cause Analysis has Eight Steps:

Root Cause Analysis Steps
Step One:

Define the Problem: – This step will help you to understand the problem definition.

Step Two:

Identification of Problem: – What exactly happening, Where the problem is being occurred and what are the symptoms of the problem?

Step Three:

Collect Data – Before collecting the Data, You have to plot the Pareto Chart of Existing Past data for the last six months at least. Then formulate the template according to the higher contributing causes with the help of the Pareto Principle (80/20 rules). Set up the Template machine-wise, process-wise, and shift-wise etc. At least collect the data for three months.

Download [Pareto Chart Template].

Step Four:

 Represent The Potential Cause: – Now you have to plot the Pareto chart with the Present collecting data. Next, to apply the Pareto principle to identify the Problems among the set, that are coming under the 80% contribution.

All Problems that are coming under the 80% contribution need to be plotted in the Fishbone Diagram individually to represent the Potential Causes.

Download [Fishbone Diagram Template].

Step Five:

Find out the significant Causes: A hypothesis test needs to be executed here to find out the significant reasons.

For example, let us take the Shrinkage as the problem, which is coming under the 80% contribution (The decision will come from the Pareto chart considering its Principle rules).  Let Shrinkage has three potential causes [1] High Pouring Temperature, [2] Wrong Gating System Design, [3] High Carbon Equivalent. To find out the significant causes of the three problems. We have to do the Hypothesis test as per the below pattern as

[1] High Pouring Temperature vs. Shrinkage.

[2] Wrong Gating System Design vs. Shrinkage

[3]High Carbon equivalent vs. Shrinkage.

After doing the hypothesis testing as per the above pattern, one or a number of causes will come to the point as significant Causes.

Now you have to follow step six to identify the Root Cause.

Step Six:

Identify the Root Causes:-

Before you execute the root cause identification. List all significant causes. Thereafter, we have to do the Why-Why Analysis of all individual significant causes until to get the Root Causes. Once you completed the 5-whys analysis try to document these in why why analysis template.

Step Seven:

CAPA: Corrective and Preventive Action Plan to be Prepared.

Click here to learn more about the CAPA Process.

Download [CAPA Format / Template].

Step Eight:

Effectiveness of CAPA: – After implementation of CAPA, Trend Analysis needs to be plotted to figure out the effectiveness of CAPA or Action Plan. If the Action Plan is fully effective then the control mechanism and action plan need to the incorporated in relevant documents (e.g. FMEA, SOP, Control Plan, etc.).

Download [Root Cause Analysis PPT].

Root Cause Analysis Tools:

[1] Pareto Chart

[2] Fishbone Diagram

[3] Hypothesis tools

[4] 5 Whys

RCA Template, Format: DownloadRCA format in Excel | RCA template Word | PDF Format

Root Cause Analysis Template

Root Cause Analysis Examples

RCA or root cause analysis is a very important methodology to identify the root cause of any problem, issues, defects, non-conformities, customer complaints, warranty analysis, variation, deviation, abnormal activities, etc. There are many improvement projects being implemented in manufacturing industries such as the six sigma project, Quality Circle project, Kaizen, and small group activity project, where RCA is a vital milestone to successfully achieve the project goal. Proper RCA will help you to address the root cause for formulating the action plan to resolve the problem. You can follow the below 8 steps to do the proper RCA.

Root Cause Analysis
Root Cause Analysis

Before taking any example, we are going to know the tools used in RCA, for the problem statement i.e. [problem definition and identification] you can use the 5W1H or 5W2H tools. similarly, we have mentioned the tools used in the rest of the RCA steps are given below;

8 steps of RCACommon Applicable Tools & Template
Define the problem5W1H or 5W2H
Identification of the problem5W1H or 5W2H
Data collectionData collection Format, Pareto chart, etc.
Represent the potential causeFishbone or Cause & Effect or Ishikawa diagram
Find out the significant causeHypothesis test, validation of potential causes
Identify the root causeWhy-Why analysis [5W analysis]
CAPACAPA template, 8D, etc.
Effectiveness of CAPA, standardization & monitoringInspection report template, SOP, WI, CP, FMEA, etc.

RCA Examples:

Let’s consider a company manufacturing automobile parts and supplying those parts to OEM customers, but one day one complaint was received from the customer for a blow hole problem. for the same problem, the customer asked for an action plan. To resolve the problem and form an action plan the process QA engineer started the RCA [root cause analysis] of blow hole issues. They have followed the above steps for RCA and the same is given below.

Problem Statement:

What: Blow hole problem

Where: The part had been rejected at the customer’s end, the problem is related to moulding & core making process

When: Problem found during machining operation at the customer end, the problem may have occurred during the manufacturing of parts in moulding & core making operation

Who: Core shop and moulding process operators

Why: Reason unknown

How often: last consignment date in dd/mm/yy

How much: 10 parts

Problem Statement by 5W2H
Problem Statement by 5W2H

Now, with the help of a cause & effect diagram, we have to identify the potential causes for the blow hole problem, below are the listed potential causes but these are not limited to

  • Wet core fitted in moulding.
  • Inadequate venting system.
  • Wrong gatting system
  • High moisture in mould
  • sand permeability issue
  • Thick mould coating.

We plotted a cause-and-effect diagram using the above potential causes, and we show the diagram below;

C&E Diagram of blow hole

Now, we have to find out the significant cause with the help of a hypothesis test or validation of potential causes. after doing the validation of all the above potential causes by following the validation methodology, we found that “wet core” was the significant cause. so the next step is the identification of the root cause.

RCA of blowhole by why-why analysis:

SC: Wet core fitted in moulding

Why: Core was wet

Why: The team did not follow the drying procedure properly.

Root Cause: Lack of awareness

After doing the root cause analysis, you have to formulate the CAPA and need to monitor the effectiveness of the action plan. then you can standardise the document and if applicable you can do the horizontal deployment of the same.

Root Cause Analysis Template – Download

Root Cause Analysis Template

Many tools, techniques, templates, and formats help conduct root cause analysis, but here we will discuss only some common and popular ones listed below.

FAQ:

1. What is Root Cause Analysis (RCA)?

Root Cause Analysis is a systematic process for identifying the root causes of a problem rather than just addressing symptoms, enabling effective corrective action.

2. Why RCA Matters

RCA matters because it:

  • Prevents recurrence of problems
  • Improves process reliability.
  • Reduces costs from rework, failures, and incidents
  • Supports continuous improvement and learning
  • Encourages fact-based decision-making
  • Strengthens accountability without blame
3. When to Use RCA

RCA should be used when:

  • A significant incident or failure occurs
  • There are repeated or chronic problems
  • A problem has high risk, cost, or impact
  • Regulatory, safety, or quality requirements demand it
  • A process deviation leads to undesired outcomes
  • You need to understand system weaknesses, not just fix an error
4. When RCA is Useful

RCA is especially useful when:

  • The problem is complex or multi-factorial
  • The cause is not immediately obvious
  • Multiple teams or processes are involved
  • You need long-term corrective actions
  • Data, evidence, and subject matter experts are available
5. Benefits of RCA
  • Identifies true root causes, not symptoms
  • Leads to sustainable corrective actions
  • Improves process design and controls
  • Enhances organizational learning
  • Reduces repeat incidents
  • Strengthens risk management
  • Builds a culture of improvement
6. Limitations of RCA
  • Time- and resource-intensive
  • Results depend on data quality
  • Can be ineffective if:
    • Poorly facilitated
    • Politicized or blame-focused
  • Not ideal for:
    • Simple, one-off issues
    • Situations requiring immediate action only
  • May miss causes if the system boundaries are too narrow
7. Best Practices for Effective RCA

a. Define the Problem Clearly

  • Be specific, factual, and measurable
  • Focus on what happened, where, when, and the impact

b. Focus on Systems, Not People

  • Ask why the system allowed the error
  • Treat human error as a symptom, not a root cause

c. Use Structured Tools

Common RCA tools:

  • 5 Whys
  • Fishbone (Ishikawa) Diagram
  • Fault Tree Analysis
  • Pareto Analysis
  • Process Mapping
  • 7QC Tools
  • CAPA

d. Use Evidence and Data

  • Rely on facts, records, observations, and timelines
  • Avoid assumptions or opinions

e. Involve the Right People

  • Include process owners and subject matter experts
  • Encourage open, blame-free discussion

f. Identify Root Causes, Not Just Contributing Factors

  • Validate that removing the cause would prevent recurrence

g. Develop Strong Corrective Actions

Effective actions:

  • Address the root cause directly
  • Are measurable and realistic
  • Include ownership and deadlines
  • Prefer engineering or system controls over training alone

h. Verify Effectiveness

  • Monitor outcomes
  • Confirm the problem does not recur
  • Adjust actions if needed
8. Common Mistakes in RCA
  • Stopping at human error (operator mistake)
  • Jumping to solutions before analysis
  • Confusing symptoms with root causes
  • Asking “why” too few times
  • Lack of data or evidence
  • Bias, blame, or fear affecting honesty
  • Poor documentation
  • Weak corrective actions.
  • No follow-up to verify effectiveness

Useful Articles:

7QC Tools For Problem Solving.

Kaizen.

OEE Calculation.

8D Report Example | Download Case Study Report:

CAPA Process

Free Tools, Formats, Templates:

More on TECHIEQUALITY

How to Plot Pareto Chart in Excel | Manufacturing example | Download Format

How to Plot Pareto Chart in Excel

How to Plot Pareto Chart in Excel | Manufacturing example

How to Plot Pareto Chart in Excel ( with example), step-by-step guide and illustration with example is given below, just follow to prepare the Pareto chart in Excel.

DOWNLOAD-Pareto Chart Excel Template/Format.

History and Definition:

A Pareto Chart is named after the Italian Economist Vilfredo Pareto. It is a type of chart which contains both bars and a line graph, where the individual values are represented in the bar graph in descending order (largest to smallest value) and the cumulative percentage is represented in the line graph.

Purpose of Pareto Chart:

The purpose of the Pareto Chart is to indicate the Contributions among the set of data.

E.g. let us have six types of defects that we would like to know the most cumulative contributions among them those contributing the 90%, in such a scenario, we have to plot the Pareto Chart to know the 90% contribution with the help of line Chart, we simply cover the 90% level of line Chart, those are coming under the line graph will represent the 90% contribution. so simply Pareto Chart is helping here to identify the contribution.

Understanding the Pareto Chart principle (The 80/20 rule):

The Pareto principle is also known as the 80/20 rule derived from the Italian Economist Vilfredo,

The principle is understood as –

20% of the input creates 80% of the results

Or

80 % of the effects come from 20% of the causes.

Illustration of How to Plot Pareto Chart in Excel

How to Plot Pareto Chart in Excel

In the above Pareto Chart, we can see the cumulative% in the line graph, According to the Pareto Chart principle 80/20 rule, the 80% cumulative in the line graph is filling under the low hardness, which means BH, Damage, SH and Low hardness defers are coving the 80% of contribution over total types of defects. And those 80 % contributions were due to the 20% of the cause.

Advantages of Pareto Chart:

1. To optimize the production

2. Reduces the Rejections

3. Reduce the COPQ/COQ

4. Improve the quality

5. Improve the performance of the product

6. Improve the customer satisfaction

7. Reduce the rework cost.

Etc.

How to Plot Pareto Chart in Excel ( with example):

Step -1

We have six types of defects BH(Blow Hole), Damage, SH(Shrinkage), Low hardness, Crk( Crack), and pinhole, and the total rejection quantity is 199. So now we have to arrange/sort the rejection Quality value in descending order (largest to smallest)

Then we have to calculate the cumulative rejection quantity and cumulative % as per the below step ( from steps 1 to 3)

How to Plot Pareto Chart in Excel

As described above, the Rejection quantity should be sorted in descending order(Largest to smallest), then you have to calculate the cumulative rejection quantity i.e C2=B2, C3=B3+C2, respectively other values need to be calculated, and finally Cumulative %, D2=C2/B8x100, for getting the D3 onward value calculation, we have to press “F4” after selecting the cursor button before “B8” in the above formula then press the enter, now drag.

Step -2

steps

Now you have to select defects, rejection quantity, and cumulative % column as per the above, and then go to the insert column and select the bar chart. Simply follow the steps(From S-4.1 to S-4.5) given in above.

Step -3

After selecting the bar chart, just follow the steps (select “insert” in the Excel sheet then line and finally select the “Line” graph) as described below ( convert the red colour bar into the line Chart )

After the conversation of the secondary red colour bar into the Line Chart, the Pareto Chart will be ready to use as

steps

In the above Pareto chart, you can see the bar represents the defect’s Rejection quantity and the line represents the contribution of the cumulative percentage.

Example:

A total of 44 numbers SHE-related incident has been registered in the manufacturing industry in the last couple of financial year. To know the contribution of the individual incidents the SHE officer prepared the Pareto chart. The same Pareto chart is given below.

How to Plot Pareto Chart in Excel
DOWNLOAD-Pareto Chart Excel Template

How do you analyze a Pareto chart?

you can follow the below steps to analyze the Pareto chart;

  1. Collect the data.
  2. plot a Pareto chart with the help of the above steps.
  3. Apply the 80:20 rules/principle.
  4. select the defects under 80% contribution
  5. Brainstorm the potential cause.
  6. Do the hypothesis test or validate the potential cause to find out the significant cause.
  7. Do the RCA
  8. Take the action plan
  9. Implement the plan.
  10. Monitor the effectiveness.

Example-2 of How to Plot Pareto Chart in Excel

Plot the Pareto chart of the given below data.

DefectsRejection Quantity
A100
B80
C70
D50
E30

follow the below steps to plot the Pareto chart

Step-1:

First of all, calculate the cumulative rejection quantity

DefectsRejection QuantityCumulative Rejection Quantity
A100100
B80180
C70250
D50300
E30330

Step-2:

calculate the cumulative rejection %

DefectsRejection QuantityCumulative Rejection QuantityCumulative rejection %
A10010030
B8018055
C7025076
D5030091
E30330100

Step-3:

Select the Defects, Rejection quantity, and cumulative rejection % column, and then go to the Insert—>>Line chart. once you select the line chart then, select the cumulative rejection% line chart as the secondary axis. go through the below image for a better understanding.

How to Plot Pareto Chart in Excel

Step-4:

Select the rejection quantity line chart and then, go to the insert——>>Column chart. now the Pareto chart is ready, if you want to customise the colour, and gap then do it manually for a better visual effect.

How to Plot Pareto Chart in Excel

Some useful Articles;

Pareto Chart Example of Manufacturing Units.

Pareto Chart Excel Template.

Control Chart Excel Template |How to Plot Control Chart in Excel | Download Template.

SPC Format |DOWNLOAD Excel Template of SPC Study.

7QC Tools Excel Template |DOWNLOAD Format

More on TECHIEQUALITY

Thank you for reading…keeps visiting Techiequality.Com

Popular Post:

Pareto Chart Excel Template | Download format

Pareto Chart Excel Template

Pareto Chart Excel Template | Step by Step guide of template usages:-

Hi Readers! In this article, we have discussed on Pareto Chart Excel Template with a manufacturing example. and also you can learn here, the Pareto chart principle (80/20 rule). if you would like to download our excel template or format then, go through the below link.

DownloadPareto chart Excel Template.

Pareto Chart Excel Template

[Figure 1]

How to Use Pareto Chart Excel Template:

After downloading, the above Pareto Chart Excel Template Carefully read the Note and red highlighted box marked in excel.

Note 1:- White cells are only changed values. The sky colour cells will automatically calculate based on the formula within the cells.

Note 2:- Starting from the top, enter the name of causes into the table below in descending order (Largest to Smallest Values)

Example of Pareto chart:

Let us have ten causes as Damage, Crack, Shrinkage, Short-run, Blowhole, Pin-hole, Extra Metal, Sand-wash, Rough Surface, Low hardness, and High elongation.

Causes Rejection Quantity
Damage 23
Shrinkage 20
Crack 11
Short-run 7
Blow-hole 8
Extra Metal 5
Sand wash 6
Rough surface 3
Low hardness 4
High elongation 1

Now you have to do the sorting of Rejection Quantity in Descending order (Largest to smallest value)

Descending order of Rejection Quantity of above causes are,-

Causes Rejection Quantity
Damage 23
Shrinkage 20
Crack 11
Blow-hole 8
Short-run 7
Sand wash 6
Extra Metal 5
Low hardness 4
Rough surface 3
High elongation 1

Now directly we have to enter the name of causes and Rejection quantity (After sorting the value in descending order) into white cells of the Excel template sheet. After entering the values the Pareto chart will look like as below.

Pareto chart example
Pareto Principle (80/20 Rule):-

The 80/20 Rule or Pareto Principle is the most important part of Pareto Analysis. The rule 80/20 says that 80% of the effects come from 20% of the causes.

In Italy, Vilfredo Pareto has originally observed that 20% of people were owned 80% of the land. This principle was applied to quality control and favoured the use of the statement of phrase, which is “The Vital few and useful many” to define the 80/20 rule in the 20th century by Dr. Joseph M. Juran. Nowadays this principle is so popular and very useful in describing the contribution of the causes.

Understanding of Principle:-

Let’s get started with this principle, and how it is applicable in different sectors like manufacturing and non-manufacturing unit or service sectors. This principle is not limited to any particular sector or unit’s problems or defects to identify the contribution. It will help you to resolve 80% of problems/causes/defects among the 100% of problems.  

How this principle is related to the different fields: – (Example)-

  • Filed failure (for example (a)-80% of the field failure comes from 20% of the Causes.
    (b)-80% of the field failure comes from 20% of the Customer).
  •  80% of the results come from 20% of the Team.
  • Risk Management (e.g. 80% of the Risk comes from 20% of the Causes).

Let us have ten types of Causes and individual causes having a number of defects. Now we need to work on merely an 80% contribution to resolve the problem. But the things are how to identify the causes those are coming under the 80% contribution. So to identify the contribution we need to use the Pareto chart for knowing the contribution. So I would recommend you to download the above Pareto Chart Excel Template then, follow the steps and identify the contribution.

Advantages of Pareto Chart:

1. Production Optimisation.

2. Rejection Reduction.

3. Cost of Poor Quality Reduction.

4. Quality level Improvement.

5. Product Performance Improvement

6. Customer satisfaction Enhancement.

7. Rework cost reduction.

Etc.

The Pareto chart is the most commonly used tool in manufacturing industries, I remember when I was working in the quality department, how frequently I used this tool in our daily quality issue analysis. I used this tool on a daily line rejection analysis, as well as in different types of QA or QC projects like quality circle projects, SGA projects, Six Sigma projects, etc. With the help of the Pareto chart, you can easily visualize the defect’s contribution and accordingly, you can do an analysis of the majority contribution for improvement.

FAQ:

The Pareto chart is one of the commonly used 7 QC tools in manufacturing industries.

Similar Articles:

Histogram Example | Foundry Industries Examples.

Histogram Template with example | Download.

How to plot Histogram in Excel (Step by step guide with example)

SPC Format |DOWNLOAD Excel Template of SPC Study

7QC Tools for Problem Solving | What are 7 QC Tools

Root Cause Analysis | 8 Steps of RCA

More on TECHIEQUALITY

Thanks for reading…Keeps Visiting Techiequality.Com

Popular Post: