Use Excel for the following: You will turn in the print of your completed Excel worksheet.

i) First sheet: Discrete probability distribution

Enter your name in cell A1.

Starting in row 5 make columns as follows:

Column            contents

B: Enter title Y in row 5: Enter the numbers 1-9 in rows 6 to 14

C: Enter title UIN in row 5: Enter the values of your UIN in rows 6 to 14. Call each number UIN(i)

D: P(Y) = UIN(i)/Sum(UIN(1):UIN(9)) in rows 6 to 14

E: cumulative Prob,

F: Y*P(Y),

G: Y^2 * P(Y),

H: Y-E(Y),

I: (Y-E(Y))* P(Y),

J: (Y-E(Y))^2 * P(Y),

K: (Y-E(Y))^3 * P(Y),                                                                                                                           Where the sum of any column has meaning, put it at the bottom of the column. E.g., for P it should equal 1, but nothing should be at the bottom of Y-E(Y).                                       Also find the median and put it below E(Y).                                                                                 Is this an appropriate discrete probability distribution?  how you know it is. State on the worksheet below the table                                                                                                              Find the variance by using both methods V(Y) = E [(Y − E(Y))2] = E(Y2) − (E(Y))2

ii) Second sheet:  How to simulate Bernoulli experiments?                                                      First, we create 100 random numbers, Ui     i = 1, ⋯ , 100 from uniform distribution          between zero & one. Then, define any realization equal to or below p (where 0 < p <1),  as success and any realization greater than p as failure. Define the Bernoulli random       variableXi , which takes value of 1, when the realization is a “success” and zero, when    the realization is “failure”, i.e. Xi  = 1   if Ui  ≤ p and Xi  = 0   if Ui  > p. The probability of success = P(Xi  = 1) = P(Ui  ≤ p) = p                                                                                    Now, do the following in Excel:                                                                                                      a) Create 100 random numbers from uniform distribution between zero & one in cells    A1:A100.

b) Create a random variable defined as follows: In cell B1 enter IF(A1< = 0.4, 1, 0). Copy

and paste in the cells B2: B100. This assigns 1 if the outcome of uniform distribution is 0.4 or below and 0 if the outcome of uniform distribution is above 0.4.

c) In cell C1, find the sum of success (=SUM(B1:B100)) and In cell C2, find the proportion of success. Is it equal to 0.4?

d) What happens if you create 1000 random numbers (Do this in cells D1:D1000 and find the sum and proportion of success in cells F1 and F2).

e) What happens if you create 10,000 random numbers (Do this in cells G1:G10000 and find the sum and proportion of success in cells I1 and I2).

iii) Third sheet: Binomial distribution Enter your name in cell A1.

Let p = X(9)*.05 + .25  where X(9) is the ninth digit of your university identity number. This is the p in the binomial formula.

Now for a sample of size 10, n=10, we will calculate the probability of each event from Y=0 to 10 successes.

Make columns as follows:

Y,Cy(n)  = (y(n)), py , (1 − p)n−y, P(Y = y)), Y*P, Y2  ∗ p, (Y − E(Y))2  ∗ p

Where the sum of any column has meaning, put it at the bottom of the column. (Label the mean and variance.)

Compute the mean and variance using the binomial formulas from class.

iv) Fourth sheet: Poisson as an approximation of Binomial distribution

Enter your name in cell A1.

Find the probability values for two binomial distributions and contrast them with the Poisson approximation.  You will find two binomial distributions and a Poisson.

For the first binomial: set n = last three digits of your university identification number.    If the value is less than 100, add 100.  Then, set p = next preceding digit*.001, but if zero use the prior value.

For the second binomial: let n be 10 times greater and p be 1/10 of the values of the first binomial.

The purpose is to observe how closely the Poisson distribution is to the binomial.

Make a table like the following but let Y range up to 20.

n

p           lambda

Y          Binomial  Binomial  Poisson

339        3390

0.008     0.0008

2.712 0 0.065684 0.066332 0.066404 1   0.17957 0.180036 0.180087

(These columns look at the difference   between each Binomial column and the

Poisson column.)