Question

SunLight Company is planning to manufacture a new lightbulb with an estimated mean lifetime run of 36,500 hours. Management also believes that the standard deviation is 5,000 hours, and that the lifetime hours are normally distributed.

Essay Questions

A. Use Excel to simulate the hours obtained from a sample of 500 lightbulbs, and use the COUNTIF function to determine the number of bulbs that last longer than 40,000 hours. What is your estimate of the percentage of bulbs that will exceed 40,000 hours? Hint: See Appendix A for a description of the Excel COUNTIF function.

B. Use COUNTIF to find the number of lightbulbs expected to last fewer than 32,000 hours. Then, find the number with fewer than 30,000 hours and the number with fewer than 28,000 hours.

C. If management would like to advertise a light bulb guarantee such that approximately no more than 10% of the bulbs would last long enough to qualify for the guarantee, what is your recommendation as far as the life of the lightbulbs, in hours, that should qualify for the guarantee?

USE MICRSOFT EXCEL AND SHOW FORMULAS

Answer 1

Formula sheet

A	B	C	D	E	F	G	H	I
2		A)
3		Mean lifetime	36500	hours
4		Standard Deviation	5000	hours
5
6		Bulb	Life (Hours)
7		1	=NORM.INV(RAND(),$D$3,$D$4)	=NORM.INV(RAND(),$D$3,$D$4)
8		2	=NORM.INV(RAND(),$D$3,$D$4)
9		3	=NORM.INV(RAND(),$D$3,$D$4)
504		498	=NORM.INV(RAND(),$D$3,$D$4)
505		499	=NORM.INV(RAND(),$D$3,$D$4)
506		500	=NORM.INV(RAND(),$D$3,$D$4)
507
508		Number of bulbs with life higher than 40,000 hours	=COUNTIF($D$7:$D$506,">40000")	=COUNTIF($D$7:$D$506,">40000")
509
510		Total number of bulbs	500
511
512		% of bulbs having life more than 40,000 hours	=D508/D510	=D508/D510
513
514		Hence % of bulbs having life more than 40,000 hours	=D512
515
516		B)
517
518		Number of bulbs with life lower than 32,000 hours	=COUNTIF($D$7:$D$506,"<32000")	=COUNTIF($D$7:$D$506,"<32000")
519
520		Total number of bulbs	500
521
522		% of bulbs having life lower than 32,000 hours	=D518/D520	=D508/D510
523
524		Hence % of bulbs having life lower than 30,000 hours	=D522
525
526
527		Number of bulbs with life lower than 28,000 hours	=COUNTIF($D$7:$D$506,"<28000")	=COUNTIF($D$7:$D$506,"<28000")
528
529		Total number of bulbs	500
530
531		% of bulbs having life lower than 28,000 hours	=D527/D529	=D527/D529
532
533		Hence % of bulbs having life lower than 28,000 hours	=D531
534
535		C)
536
537		Assuming the guarantee is that no more than 10% of bulb will last lower than the guaranteed hours.
538
539		For 28,000 hours,
540		% of bulbs having life lower than 28,000 hours	=D531
541
542		Thus % of bulbs having life lower than 28,000 is less than 10%.
543
544		Thus recommended life should be	28000	hours
545