##### Question

In: Statistics and Probability

# USING EXCEL FORMULAS SOLVE THE PROBLEM. MUST USE EXCEL CALCULATIONS AND FORMULAS.!!! Find the data for...

USING EXCEL FORMULAS SOLVE THE PROBLEM. MUST USE EXCEL CALCULATIONS AND FORMULAS.!!!

1. Find the data for the problem in the first worksheet named LightbulbLife of the data table down below It gives the data on the lifetime in hours of a sample of 50 lightbulbs. The company manufacturing these bulbs wants to know whether it can claim that its lightbulbs typically last more than 1000 burning hours. So it did a study.
1. Identify the null and the alternate hypotheses for this study.
2. Can this lightbulb manufacturer claim at a significance level of 5% that its lightbulbs typically last more than 1000 hours? What about at 1%? Test your hypothesis using both, the critical value approach and the p-value approach. Clearly state your conclusions.
3. Under what situation would a Type-I error occur? What would be the consequences of a Type-I error?
4. Under what situation would a Type-II error occur? What would be the consequences of a Type-II error?
 lightbulb Lifetime 1 840.08 2 960 3 953.38 4 981.14 5 938.66 6 1051.14 7 907.84 8 1000.1 9 1073.2 10 1150.66 11 1010.57 12 791.59 13 896.24 14 955.35 15 937.94 16 1113.18 17 1108.81 18 773.62 19 1038.43 20 1126.55 21 950.23 22 1038.19 23 1136.67 24 1031.55 25 1074.28 26 976.9 27 1046.3 28 986.54 29 1014.83 30 920.73 31 1083.41 32 873.59 33 902.92 34 1049.17 35 998.58 36 1010.89 37 1028.71 38 1049.92 39 1080.95 40 1026.41 41 958.95 42 985.17 43 988.49 44 1012.99 45 1070.82 46 1063.13 47 948.57 48 1156.42 49 973.79 50 845.85

## Solutions

##### Expert Solution

The Calculations for the mean and standard deviation are given after the Test.

Right Tailed t test, Single Mean

Given: = 1000 hours, = 997.87 hours, s = 511.49 hours, n = 50, = 0.05

The Hypothesis:

The Null Hypothesis: H0: = 1000: The mean lifetime of a bulb is equal to 1000 burning hours.

The Alternative Hypothesis: Ha: > 1000: The mean lifetime of a bulb is greater than 1000 burning hours..

This is a Right tailed test

The Test Statistic: Since the population standard deviation is unknown, we use the students t test.

The test statistic is given by the equation:

t observed = -0.03

The Excel Calculations

 Single Mean - t - Right Tail x1 997.87 μ 1000 σ 511.49 n 50 df = n - 1 49 a x1-μ -2.13 b sqrtn 7.071067812 c s/sqrtn 72.3356095 z/t a/c -0.0294 tround -0.03

_________________________________________________________

The P Value Approach

The p Value: The p value (Right tailed) for t = -0.03, for degrees of freedom (df) = n-1 = 49, is; p value = 0.4881

Use Excel Formula TDIST(-0.03,49,1) to get the right tailed p value.

The Decision Rule:  P value is < , Then Reject H0.

The Decision:

At = 0.05: Since P value (0.4881) is > (0.01) , We Fail to Reject H0.

At = 0.05: Since P value (0.4881) is > (0.05) , We Fail To Reject H0.

The Conclusion: There is insufficient evidence at the 95% or the 99% significance level to conclude that the mean lifetime of a bulb is greater than 1000 burning hours..

_______________________________________________

The Critical Value Approach:

The Critical Value: Use the excel formula TINV (Significance level * 2,49). For eg for = 0.05 use TINV(0.1,49)

The critical value (Right Tail) at = 0.05, for df = 49, tcritical= +1.667

The critical value (Right Tail) at = 0.01, for df = 49, tcritical= +2.405

The Decision Rule: If tobserved is > tcritical.

The Decision:

At = 0.05: Since tobserved (-0.03) is < t critical (1.667), we Fail to Reject H0.

At = 0.01: Since tobserved (-0.03) is < t critical (2.405), we Fail to Reject H0.

The Conclusion: There is insufficient evidence at the 95% or the 99% significance level to conclude that the mean lifetime of a bulb is greater than 1000 burning hours..

_________________________________________________

Type I and Type II Errors

A Type I error is the incorrect rejection of a True Null Hypothesis. In this case it would mean that we reject the hypothesis that the mean lifetime of a bulb is equal to 1000 burning hours, when it actually is 1000 burning Hours.

A Type II error is the failure to reject a false null Hypothesis. In this case it would mean that we Fail to rejctt the hypothesis that the mean lifetime of a bulb is equal to 1000 burning hours, when it actually is greater than 1000 burning Hours.

________________________________________________

Calculation for the mean and standard deviation:

Mean = Sum of observation / Total Observations

Standard deviation = SQRT(Variance)

Variance = Sum Of Squares (SS) / n - 1, where

SS = SUM(X - Mean)2.

 # X Mean (X - Mean)2 1 840.08 498.93 116383.3225 2 960 498.93 212585.5449 3 953.38 498.93 206524.8025 4 981.14 498.93 232526.4841 5 938.66 498.93 193362.4729 6 1051.14 498.93 304935.8841 7 907.84 498.93 167207.3881 8 1000.1 498.93 251171.3689 9 1073.2 498.93 329786.0329 10 1150.66 498.93 424751.9929 11 1010.57 498.93 261775.4896 12 791.59 498.93 85649.8756 13 896.24 498.93 157855.2361 14 955.35 498.93 208319.2164 15 937.94 498.93 192729.7801 16 1113.18 498.93 377303.0625 17 1108.81 498.93 371953.6144 18 773.62 498.93 75454.5961 19 1038.43 498.93 291060.25 20 1126.55 498.93 393906.8644 21 950.23 498.93 203671.69 22 1038.19 498.93 290801.3476 23 1136.67 498.93 406712.3076 24 1031.55 498.93 283684.0644 25 1074.28 498.93 331027.6225 26 976.9 498.93 228455.3209 27 1046.3 498.93 299613.9169 28 986.54 498.93 237763.5121 29 1014.83 498.93 266152.81 30 920.73 498.93 177915.24 31 1083.41 498.93 341616.8704 32 873.59 498.93 140370.1156 33 902.92 498.93 163207.9201 34 1049.17 498.93 302764.0576 35 998.58 498.93 249650.1225 36 1010.89 498.93 262103.0416 37 1028.71 498.93 280666.8484 38 1049.92 498.93 303589.9801 39 1080.95 498.93 338747.2804 40 1026.41 498.93 278235.1504 41 958.95 498.93 211618.4004 42 985.17 498.93 236429.3376 43 988.49 498.93 239668.9936 44 1012.99 498.93 264257.6836 45 1070.82 498.93 327058.1721 46 1063.13 498.93 318321.64 47 948.57 498.93 202176.1296 48 1156.42 498.93 432293.1001 49 973.79 498.93 225492.0196 50 845.85 498.93 120353.4864 Total 49893.43 12819661.46
 n 50 Sum 49893.43 Average 997.87 SS 12819661.46 Variance = SS/n-1 261625.74 Std Dev 511.49

## Related Solutions

##### For this problem, you must use Excel to perform the necessary calculations. Below are the formulas...
For this problem, you must use Excel to perform the necessary calculations. Below are the formulas and equations you will need to use. Click on the image below to download a power point version if the text is blurry or too small. If it is appropirate to use the binomial distribution, use the formula=binom.dist(number_s,trails,probability,cumulative) to calculate the probability. In this formula, number_s is the number of successful trails, trails is the total number of trails, probability is the probability for...
##### ***Excel is required to solve this problem. Please use excel and show all formulas used in...
***Excel is required to solve this problem. Please use excel and show all formulas used in each cell I would really appreciate the work*** Three-Stage FCFE Model: Biomet Inc., designs, manufactures and markets reconstructive and trauma devices, and reported earnings per share of $0.56 in 1993, on which it paid no dividends. (It had revenues per share in 1993 of$2.91). It had capital expenditures of $0.13 per share in 1993 and depreciation in the same year of$0.08 per...
##### Use the data below to solve the following problem using excel: 1 a) Import the data...
Use the data below to solve the following problem using excel: 1 a) Import the data into an Excel file. Done! b) Create a new column in the spreadsheet to assign the category of each car according to the engine horsepower. For this exercise use IF statements in each cell to determine the class for each vehicle. i. Class 1 if the vehicle horsepower is less than 80 HP. ii. Class 2 if the vehicle horsepower is between 81 and...
##### (PLEASE READ ) :) Use the data below to solve the following problem using excel: (...
(PLEASE READ ) :) Use the data below to solve the following problem using excel: ( I would like to know how do you input the formula for each category, so please explain the process) I will RATE and comment your answer accordingly 1 a) Import the data into an Excel file. Done! b) Create a new column in the spreadsheet to assign the category of each car according to the engine horsepower. For this exercise use IF statements in...
##### 2. Solve using Microsoft Excel: Use the following data to find the equation of the regression...
2. Solve using Microsoft Excel: Use the following data to find the equation of the regression line. X-Bar 2 4 5 6 Y-Bar 7 11 13 20
##### , Excel allows us to create our own formulas and functions to perform calculations and solve...
, Excel allows us to create our own formulas and functions to perform calculations and solve problems within the spreadsheet. Which of the functions or formulas that you experimented with in Presentation 1 did you find the most valuable? How do you think this particular function will save time in using Excel spreadsheets? What other functions do you think you will use on a regular basis? Why? Explain. Was there a function that is new to you?
##### Please show formulas using excel Build a spreadsheet: Construct an Excel spreadsheet to solve all of...
Please show formulas using excel Build a spreadsheet: Construct an Excel spreadsheet to solve all of the preceding requirements. Show how the solution will change if the following data change: the April 1 work-in-process costs were $66,000 for direct material and$18,000 for conversion. Work in process, April 1—10,000 units: Direct material: 100% complete, cost of .........................................................................................................$22,000 Conversion: 20% complete, cost of ................................................................................................................. 4,500 Balance in work in process, April 1 ..................................................................................................................$  26,500 Units started during April ......................................................................................................................................100,000 Units...