Question

The J&B Lawnmower Company produces small motors (e.g. 3hp,) for use in lawnmowers and garden equipment. The company instituted a quality management program in 2010 and has recorded the following quality 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 and develop quality index numbers using sales and manufacturing cost bases 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 in 2009 and the sales for that year were $10,000,000, the equation would be (2,000,000/10,000,000) * 100 and that would equal the quality index of quality cost per sale to be 20.00.

For this assignment,

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

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

As you evaluate the Quality Index numbers as well as the ratios of prevention and appraisal to failure costs, does the company appear to be taking the right measures with their quality management system? If so, what makes you think that? If not, why don't you think so? Make sure you use the data in this problem along with what you learned in Chapter 4 about the costs of quality

Answer 1

Formula:

B12 =SUM(B3:B7)

B14 =B12/B9*100

B15 =B12/B10*100

B17 =B3/SUM(B6:B7)

B18 =B4/SUM(B6:B7)

We see that over the years, ratio of prevention to failure costs and appraisal to failure costs is increasing.

It means that the company is investing more in prevention and appraisal costs but less in failure costs. Which means, less failure costs are decreasing. Therefore, the company is taking right measures with their quality management system.