Question

Analyze the cost data below for the Costbusters Company.

1. Graph the percent of quality costs by product?

2. What are the dollar costs for each cost of quality category, by product? Graph

3. How might this information be used to help define a Six Sigma project?

   	PRODUCT A	PRODUCT B	PRODUCT C
   Total Sales
   Total quality cost as a percent of sales	24%	19%	15%
   External Failure	35%	22%	8%
   Internal Failure	52%	29%	24%
   Appraisal	10%	38%	38%
   Prevention	3%	11%	30%

   Can this problem be solved in minitab with graphs or Excel showing each steps? This is for my lean six sigma class

Answer 1

i am solving this problem in excel sheet

first question Graph of the percent of quality costs by product:-

I am attaching the screenshot of graph after image i will explain the steps.

Step 1: here you have to first add data to excel sheet as i did.

Step 2: then select row no.2 (Total Quality cost as a percent of sales)

Step 3: then go to Insert section of excel sheet

Step 4: then go to chart section and select column chart use any of 2D chart and select it.

Step 5: you will see a chart is generated then you have to edit data if neccessary for edit data first select chart and click right click from the option select edit data then select your data and you will get your graph.

now for question 2 dollar cost :

for calculating dollar cost we need more data like share amount, unit amount, invested amount then we will calculate dollar cost value.

now for question 3 sigma level:

The goal of six sigma is of cours for your orgamization to reach six sigma level of defects,

we can calculate sigma level by two types

1. by DPMO data

2. by Failure data

and here Failure data is present so we can calculate sigma level using failure

so first of all make row for total failure rate because here there are two failure rate

1. external failure

2. internal failure

so add both and store in a new row for each product

then for calculating sigma level use this formula :- " =ABS(NORM.INV(total failure rate cell no. for product A,0,1))+1.5 "

" =ABS(NORM.INV(total failure rate cell no. for product B,0,1))+1.5 "

" =ABS(NORM.INV(total failure rate cell no. for product C,0,1))+1.5 "

and store these value in a new row and make sure it round up to 4-6 decimal otherwise you have to extent column area to view value, and round up select cell and right click and go to frormat then go to number then select round up value then ok.

for ploting graph again select sigma row and click insert menu go to chart section and select insert line chart and select 2D line chart and you will see a another graph for giving data if neccessary so select graph and clcik right click and go to edit data then add new data cell. here i am attaching sigma level graph picture.