Part 1

Let X be the number of matches between a player’s 20 selected numbers and the 20

numbers drawn by the computer. Then X may range from 0 (no match) to 20 (all match)

and follows a hyper-geometric probability distribution.

Complete all of the following steps (a – j) in worksheet Part 1 of the Excel workbook

provided. All cells should contain formulas.

a. Construct a tabular probability distribution for X in column E of the worksheet.

b. Construct a tabular cumulative probability distribution for X in column F of the

worksheet.

c. Create a graphical probability distribution for X.

d. Create a graphical cumulative probability distribution for X.

e. Calculate the theoretical expected value (mean), the theoretical variance, and the

theoretical standard deviation of X in the spaces provided for those quantities. Interpret

those values in your Word report

f. In column M of the worksheet, use the Excel function “=RAND()” to generate 1000

random values according to the standard uniform probability distribution.

g. In column N of the worksheet, use the Excel “=VLOOKUP()” function along with the

available tabular cumulative distribution of part (b) to randomly generate 1000 values

of X according to the described Hyper-geometric probability distribution.

h. Calculate the experimental (simulated) expected value (mean), the experimental

variance, and the experimental standard deviation of X in the spaces provided for those

quantities.

i. Complete the table in columns Q, R, and S of the worksheet. In completing this table,

you should calculate the experimental means successively after n = 20, 40, 60, 80, 100,

200, 300, 400, 500, 600, 700, 800, 900, and 1000 simulations. It is natural that the

calculated experimental means are refreshed after each new operation in the

worksheet. For the Theoretical mean of X in column S, use the fixed value of the

theoretical mean calculated in part (e).

j. Create a line plot of the Experimental mean values versus the number of simulations

(n). Add the horizontal line plot displaying the theoretical mean of X. Use the f9 function

of your keyboard (Mac: fn+f9) to run several simulations of the successive experimental

means. Interpret your observation in the context of the Law of Large Numbers (as the

number of simulations become larger, the experimental values of the means approach

to their theoretical value).

Part 2

Complete all of the following steps (a – i) in worksheet Part 2 of the Excel workbook

provided.

a. A normal population is given in column E of the worksheet. Calculate the mean, the

variance, and the standard deviation of this population in the designated cells.

b. Construct a Relative Frequency Histogram of the given population. Discuss the shape of

the distribution.

c. Using the random sampling method described in the Instructor Perspective (or

otherwise; e.g., using the Data Analysis ToolPak), randomly draw 30 samples with each

sample consisting of 30 measurements from this population. These samples will occupy

columns N through AQ.

d. For each sample, calculate the sample mean, the sample variance, and the sample

standard deviation in the designated cells.

e. Calculate the average of 30 sample means, the average of 30 sample variances, and the

average of 30 sample standard deviations in the designated cells K2, K3, and K4

respectively.

f. Compare your results of part (e) above with those obtained for the population in part

(a). Discuss similarities and contrasts in the context of the Central Limit Theorem.

g. Construct a relative frequency histogram for the 30 sample means obtained from part

(e) above. Comment on the shape of the distribution of the sample means.

h. Summarize your findings of this experiment in your Word report

Format & Guidelines

The report should follow the following format:

(i) Introduction

(ii) Analysis

(iii) Conclusion.