STAT QUESTIONS WITH EXCEL AND A REPORT

Electronic Keno

Project 3

Overview and Rationale

This assignment is designed to provide you with hands-on experience in using discrete and continuous probability distributions. In this assignment you will use technology to generate random samples and explore the samples’ relationship with the underlying population. Finally, you will have an opportunity to apply the Central Limit Theorem to inferential statistics.

Course Outcomes

This assignment is directly linked to the following key learning outcomes from the course syllabus:

CO2: Create distributions and graphical representation based on given data and identify which distribution models best fit the data

CO3: Apply the theory of probability to calculate events’ likelihoods, understanding the differences between experimental and theoretical probabilities (the Law of Large Numbers), and calculate posterior probabilities by using the Bayes’ Law with emphasis on applications

CO7: Interpret meaningful relationships and patterns in the data in relation to a given business question

Assignment Summary

Read the scenario below and follow the instructions in the project description below (Parts 1 and 2) to analyze the data presented in the Excel workbook (Module 3 Project_Keno_v1.xlsx). Complete all parts in the designated Excel workbook. Submit both the report and the Excel workbook. The Excel workbook contains all statistical work. The report should include all your findings along with important analysis.

Project Description

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 1 through 100. The computer then randomly draws another set of 20 numbers from the set 1 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.

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

And be 800-900 words and be presented in the APA format.

The Excel spreadsheet must be completed as outlined in the assignment above.

Rubric

Category Above Standard Meets Standards Approaching Standards Below Standards Not Evident

Excel (or R): Problem Modeling & Set-up ALY6010_CO3

Thoroughly and concisely modeled the problem in Excel (or R) for each method

Accurately modeled the problem in Excel (or R) for each method

Satisfactorily modeled the problem in Excel (or R) for each method.

Partially modeled the problem in Excel (or R) for each method, but there are some gaps in the problem modeling and setup

Did not submit or incompletely modeled the problem in Excel (or R)

Excel (or R): Problem Solution & Accuracy ALY6010_CO3

Thoroughly and efficiently obtained correct and accurate solutions in Excel (or R) by using the appropriate analytic tools of the software

Thoroughly obtained accurate solutions in Excel (or R) by using the appropriate analytic tools of the software

Satisfactorily obtained correct solutions in Excel (or R) by using the appropriate analytic tools of the software

Partially obtained accurate solutions in Excel (or R) by using the appropriate analytic tools of the software

Did not submit or did not obtain accurate solutions in Excel (or R) using the appropriate analytic tools of the software

Word/Report: Problem Description & Introduction ALY6010_CO5

Thoroughly provided a summary of the problem descriptions and introduced the problem using rich and significant ideas

Thoroughly provided a summary of the problem descriptions and problem introduction

Satisfactorily provided a summary of the problem descriptions and problem introduction

Partially provided a summary of the problem descriptions and problem introduction

Did not submit or did not provide a summary of the problem descriptions and problem introduction

Word/Report: Description of Problem Analysis ALY6010_CO5

Thoroughly and accurately described the analytic concepts and theories used in analyzing the problem

Accurately described the analytic concepts and theories used in analyzing the problem

Satisfactorily described the analytic concepts and theories used in analyzing the problem

Partially described the analytic concepts and theories used in analyzing the problem

Did not submit or did not provide a summary of the problem descriptions and problem introduction

Category Above Standard Meets Standards Approaching Standards Below Standards Not Evident

Word/Report: Description of Conclusions ALY6010_CO5

Thoroughly described the conclusions and results obtained in the project using a high level of critical thinking and reasoning

Thoroughly described the conclusions and results obtained in the project

Satisfactorily described the conclusions and results obtained in the project

Partially described the conclusions and results obtained in the project

Did not submit or did not describe the conclusions and results obtained in the project

Word/Report: Writing Mechanics, Title Page, & References

Completely free of errors in grammar, spelling, and punctuation; and completely correct usage of title page, citations, and references. The report contains a minimum of 1000 words

There are no noticeable errors in grammar, spelling, and punctuation; and completely correct usage of title page, citations, and references. The report contains a minimum of 1000 words

There are very few errors in grammar, spelling, and punctuation; and completely correct usage of title page, citations, and references. The report contains a minimum of 1000 words

There are more than five errors in grammar, spelling, and punctuation; or the usage of title page, citations, and references are incomplete; or the report contains less than 1000 words

Did not submit; or there are many errors in grammar, spelling, and punctuation; or the usage of title page, citations, and references are totally incomplete; or the report contains very few words