We Helped With This Excel Programming Homework: Have A Similar One?

All the details and calculation steps needed to be appeared in Excel sheet.

Part I: The game of Keno: keno is an ancient Chinese game that has become popular in recent years. In one electronic version of this game, a player selects 20 numbers from the set of numbers I through 100. The computer then randomly draws another set of 20 numbers from the set I through 100, and the player is rewarded according to how many of his selected numbers have been matched by the 20 numbers drawn by the computer. 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. For parts (i) - (ix) below, all cells should contain formulas. (1) (ii) (iii) (iv) (v) (vi) (vii) (viii) (ix) In Part I worksheet of the Excel workbook provided, construct a tabular probability distribution for X in column E of the worksheet. In Part I worksheet of the Excel workbook provided, construct a tabular cumulative probability distribution for X in column F of the worksheet. In Part I worksheet of the Excel workbook provided, create a graphical probability distribution for X. In Part I worksheet of the Excel workbook provided, create a graphical cumulative probability distribution for X. 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 In column M of the worksheet, use the Excel function "=RAND()" to generate 1000 random values according to the standard uniform probability distribution. Next in column N of the worksheet, use the Excel "=VLOOKUP()" function along with the available tabular cumulative distribution of part (ii) to randomly generate 1000 values of X according to the described Hyper-geometric probability distribution. Calculate the experimental (simulated) expected value (mean), the experimental variance, and the experimental standard deviation of X in the spaces provided for those quantities. 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 (v). 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 becomes larger, the experimental values of the means approach to their theoretical value).

Part 2: Complete this part in the designated Excel worksheet. (i) (ii) (iii) (iv) (v) (vi) (vii) 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. Construct a Relative Frequency Histogram of the given population. Discuss the shape of the distribution. 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. For each sample, calculate the sample mean, the sample variance, and the sample standard deviation in the designated cells. 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. Compare your results of part (v) above with those obtained for the population in part (i). Discuss similarities and contrasts in the context of the Central Limit Theorem. Construct a relative frequency histogram for the 30 sample means obtained from part (v) above. Comment on the shape of the distribution of the sample means.