In: Statistics and Probability

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

- 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.
- Identify the null and the alternate hypotheses for this study.
- 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.
- Under what situation would a Type-I error occur? What would be the consequences of a Type-I error?
- 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 |

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 |

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 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 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: (
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 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 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 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...

Create an
Excel spreadsheet that can do the calculations for the credit card
below. Using formulas and the drag function of Excel (see the
notes), find the values for 1
year of charges.
You can start
this by taking the finance charge in month 1.
With a
beginning balance of $2500
and no
additional charges, there is no
grace-period, you will just
pay the minimum payment each month for the next year. The
APR is 24.99% and
the minimum payment...

Solve the problems below using well-formatted
Excel solutions. Do not hardcode numbers in the formulas…..only use
cell references to the input data. I will change the input data in
your problem to check alternate solutions. You will turn in a
complete working Excel spreadsheet with your solution.
1) What is the price of a
semiannual $1,000 par value bond with four years left until
maturity that pays a coupon of 3.75% and is yielding 5.25%? What
would it be yielding...

#2: Inventory valuation:
All calculations must be indicated via Excel formulas.
1. Calculate the $ ending inventory and $ cost of goods sold using
each of the following inventory methods: a. FIFO b. LIFO c. Average
cost
Unit
Unit Cost
Total Cost
Beg. Inventory
1000
4
4000
Purchase 1
1200
5
6000
2
1600
6
9600
3
800
8
6400
Sales: 3200 units
2. Which inventory method would you recommend for reporting for
income tax purposes to minimize taxable income?...

ADVERTISEMENT

ADVERTISEMENT

Latest Questions

- Problem 15-1 On January 5, 2017, Crane Corporation received a charter granting the right to issue...
- Consider the combustion reaction of hydrocarbon. CH + O2 -----> CO2 + H20 a. Write out...
- Que 1: a. Describe four different materials used for curing. b. Explain how ponding curing...
- Calculate the atomic percent of tungsten in an alloy prepared with 56 grams of tungsten and...
- 1. Calculate [H3O+] in the following aqueous solution at 25 C: [OH−]=1.1×10−9 M. 2. Calculate [H3O+]...
- In what ways can economic incentives and cost/benefit analysis be used to strengthen existing policies? What...
- Write in your own words some of the things that you have seen or heard which...

ADVERTISEMENT