The J&B Lawnmower Company produces small motors (e.g. 3hp,)for use in lawnmowers and garden equipment. The company instituteda quality management program in 2010 and has recorded the followingquality cost data and accounting measures for five years.

	Year	Year	Year	Year	Year
	2010	2011	2012	2013	2014
Quality Costs ($)
Prevention	$27,000	41,500	74,600	112,300	120,000
Appraisal	155,000	122,500	113,400	107,000	95,000
Failure Costs ($)
External Failure	386,400	469,200	347,800	219,100	150,000
Internal Failure	242,000	196,000	103,500	106,000	100,000
Accounting Measures ($)
Sales	4,360,000	4,450,000	5,050,000	5,190,000	4,750,000
Manufacturing Costs	1,760,000	1,810,000	1,888,000	1,890,000	2,000,000

The company wants to assess its quality assurance program anddevelop quality index numbers using sales and manufacturing costbases for the five-year period.

The formula for quality index would be the following

Quality Index=((Total Quality Costs) / Base ) *100

For example, if the total quality costs come to $2,000,000 in2009 and the sales for that year were $10,000,000, the equationwould be (2,000,000/10,000,000) * 100 and that would equal thequality index of quality cost per sale to be 20.00.

For this assignment,

use Excel to calculate the Quality Sales Index by year and theQuality Manufacturing Cost Index by year

use Excel to calculate the ratios of prevention and appraisalcosts to total failure costs.

As you evaluate the Quality Index numbers as well as the ratiosof prevention and appraisal to failure costs, does the companyappear to be taking the right measures with their qualitymanagement system? If so, what makes you think that? If not, whydon't you think so? Make sure you use the data in this problemalong with what you learned in Chapter 4 about the costs ofquality