Post submission in Moodle in PDF format only. Please complete all tasks using MS Excel as directed. Format all tables (that you create or that are generated in Excel) and charts to APA expectations AS REQUIRED FOR ALL ASSIGNMENTS. These formatting expectations were to have been reviewed during Wk 1.
Finally, please DO NOT SUBMIT the EXCEL DATA SET, separately nor within your assignment, when submitting your assignment.
Project Part 1: Examining Probabilities
Directions: Respond to the prompts below. Follow the ‘Do Not’ and ‘Do’ instructions.
|
DO NOT : · Upload your Excel workbook to Moodle · Copy and paste your dataset into this document · Upload your work as a Word document in Moodle |
DO · Paste or type your responses directly into this document · Save your completed work as a PDF file · Upload your only your PDF file to Moodle |
A random variable is a variable whose value is determined by the outcomes of a probability experiment. For every random variable, a probability distribution can be defined. A probability distribution represents the probability of occurrence for each value of the random variable. Associated with a probability distribution, is a function that can be used to calculate the probability values. The sum of all the probabilities, for all possible values of a single random variable, should sum to 1.
Probability Distribution
1) Reference the ‘Generating Survey Data in Excel’ Excel workbook resource associated with this assignment. Examine the ‘Generating Values’ spreadsheet. Feel free to edit/update this spreadsheet while addressing the requirements for this prompt.
Use what you learned from the ‘Generating Survey Data in Excel’ spreadsheet to edit and create your own probability distribution table where there are 6 outcome values for the random variable. Feel free to label the random variable outcomes whatever you choose. Feel free to assign the probability distribution probability values, associated with the random variable outcome labels, appropriately in a manner that you choose. Copy, paste, and APA format your completed probability distribution table here.
2) In Excel and using the process outlined on the ‘Generating Values’ spreadsheet, generate 60 random values, based on your probability distribution table created in #1 above. To do this, you will likely need to update the Excel formula in that begins in cell F4. The formula is presented below
=INDEX(A$7:A$11,COUNTIF(C$7:C$11,"<="&RAND())+1)
Update ‘A$7:A$11’ to ensure it captures all of your random variable outcomes
Update ‘C$7:C$11’ to ensure it captures all of your cumulative probability values
Once you update the formula, use the Fill Handle to drag the formula to generate the 60 random values that you need.
3) Then create a frequency distribution table based on these 60 values. Use the format in the ‘Generating Values’ spreadsheet. You may need to edit the Excel formula beginning in cell J8. That formula is presented below:
=COUNTIF(F$7:F$24,I8)
Update ‘F$7:F$24’ to ensure it captures all of your 60 values
Use the Fill Handle to copy your updated formula across all rows of the frequency variable column
4) Copy, paste, and APA format your completed frequency distribution table here.
5) Generate ONE appropriate chart (your choice) using your frequency distribution table. Copy and paste your chart here. Ensure that it is APA formatted.
Binomial Probability Distribution
A binomial distribution has the following function associated with it:
Figure 1
Binomial Distribution Function
The mathematics underlying this function are built into Excel. However to ensure that the results Excel generates are reliable, you must first check to ensure that your probability experiment satisfies the Binomial Probability usage criteria.
6) You are being asked to design a small survey (questionnaire ) of questions for your business purpose of choice. However, you must generate a survey of 5 questions where the probability of the occurrence of the response options can be calculated using the binomial probability formula. List your 5 questions and response options (what respondents will be asked to select from when answering the question) , that fit this criteria, here in Table 1.
Table 1
Survey Questions and Response Options
|
Question Number |
Question |
Response Options |
7) Explain, using complete sentences, how it is that the binomial probability formula usage criteria will be met , in your proposed survey administration and design, when calculating the probability of responses to your 5 survey items. Type your explanations below in Table 2.
Table 2
Explanations in Support off Binomial Probability Usage Criteria
|
Binomial Probability Usage Criteria |
Explain |
|
Will there be a fixed number of administrations of the survey? If so, how many? |
|
|
Are there only 2 possible response choices? |
|
|
Is there a single probability of success , that is the same of all questions, that is applied to the likelihood of a favorable response? If so, what is it? |
|
|
Will the respondent responses be independent of each other? How will you ensure this? |
8) Reference the ‘Generating Survey Data in Excel’ spreadsheet resource associated with this assignment. Use the Data Analysis Toolpak to generate the numerical values associated with the binomial probability related response options to your 5 survey questions. Assume that you will administer the survey to 50 randomly selected respondents.
9) Use the =COUNTIF formula in Excel to count the number of 1 and 0 responses for each of the 5 survey questions. Calculate the percent of 1 and 0 responses for each of the 5 survey questions. Type your results into Table 3 below.
Table 3
Frequency and Percent of 1 and 0 Responses to Survey Questions
|
Question1 |
Question2 |
Question3 |
Question4 |
Question5 |
||
|
Total Number of 1 responses |
||||||
|
Total Number of 0 responses |
||||||
|
Percent of 1 responses |
||||||
|
Percent of 0 responses |
10) Is there any relationship between the binomial probability of a 1 response and the percentages of 1 responses in your table above? Explain.
11) In Excel, generate a bar chart for the percent of 1 and 0 responses for your 5 survey items. Correct APA formatting is expected. Copy and paste your chart here.
image1.png
,
Project 1 Rubric
Components Below Expectations (1pt) Approaching Expectations
(2pt)
Moderately Meets
Expectations (3)
Meets Expectations (4)
Interpret
statistical
concepts and
methods used
in analyzing
the data
Student identifies inadequate
internet resources or provides
inadequate business detail
needed for assignment
application. Requested Excel
sampling tools and formulas
are either not used, not
reported, or reported with
major errors
Student identifies internet
resources, or presents
business detail needed for
assignment application.
Requested Excel sampling
tools and formulas are used
and reported with minor
errors
Student identifies adequate
internet resources, or
presents an adequate
business detail needed for
assignment application.
Requested Excel sampling
tools and formulas are
appropriately used and
reported
Student identifies reputable
internet resources or presents
well-defined business detail
needed for assignment
application. Requested Excel
sampling tools and formulas
are appropriately used and
reported
Examine random
variables and
their probability
distributions
Student uses technology to
inappropriately or incompletely
examine and/or generate,
random variable properties.
Probability distributions have
been generated and analyzed
with many errors.
Interpretations, related to
probability distribution analysis
results, may have not been
provided
Student uses technology to
examine and/or generate,
random variable properties.
Probability distributions have
been generated and analyzed
with relatively few errors.
Interpretations, related to
probability distribution
analysis results, have been
provided
Student uses technology, as
requested, to examine and/or
generate, random variable
properties. Probability
distributions have been
generated and analyzed.
Adequate interpretations,
related to probability
distribution analysis results,
have been provided
Student uses technology, as
requested, to examine and/or
generate, random variable
properties. Probability
distributions have been
generated and analyzed
completely. Detailed
interpretations, related to
probability distribution analysis
results, have been provided
Project 1 rubric continued
Components Below Expectations (1pt) Approaching Expectations
(2pt)
Moderately Meets
Expectations (3)
Meets Expectations (4)
Apply knowledge
of statistics to
business decision
making and
management
including creating
surveys
Student doesn’t use technology to aid in generating survey data. Student generates survey prompts, with response options, are generally not consistent with assignment criteria. Student provides minimal justifications that some of the survey item creation criteria has been met
Student uses technology to aid in generating survey data. Student generates survey prompts, with response options, where some are consistent with assignment criteria. Student provides justifications that some of the survey item creation criteria has been met
Student uses technology as directed to aid in generating survey data. Student generates survey prompts, with response options, consistent with assignment criteria. Student provides adequate justifications that survey item creation criteria has been met
Student uses technology appropriately to aid in generating survey data. Student generates survey prompts, with response options, consistent with assignment criteria. Student provides well detailed justifications that survey item creation criteria has been met
Inform through
effective reporting,
written and oral
communication
Student provides inadequate
written interpretations for 50%
or more of all numerical
summaries and charts
Student provides adequate written interpretations for 70%
of all numerical summaries and
charts
Student provides adequate written interpretations for 80%
of all numerical summaries and
charts
Student provides adequate written interpretations for
100% of all numerical
summaries and charts
APA formatting Student has many APA formatting errors that lead to misrepresentations or questionable interpretation of evidences. Not all Excel generated resources are copied and paste in to the submission document. Excel gridlines may be viewable. Submission may not be saved in PDF format
Student has a 2 or more APA formatting errors that do not lead to misrepresentations or questionable interpretation of evidences. All Excel generated resources are copied and paste in to the submission document. Excel gridlines are not viewable. Submission is saved in PDF format
Student has 1 type of APA formatting errors that do not lead to misrepresentations or questionable interpretation of evidences. All Excel generated resources are copied and paste in to the submission document. Excel gridlines are not viewable. Submission is saved in PDF format
Student has no APA formatting errors . All Excel generated resources are copied and paste in to the submission document. Excel gridlines are not viewable. Submission is saved in PDF format
,
Generate Values
| Generating random values based on a predefined probability distribution | |||||||||
| Probability Distribution table | |||||||||
| Random Variable | Probability | Cummulative Probability | |||||||
| 1 | 0.35 | 0.35 | 4 | Random Variable | Frequency | ||||
| 2 | 0.12 | 0.47 | 3 | 1 | 6 | ||||
| 3 | 0.33 | 0.8 | 1 | 2 | 2 | ||||
| 4 | 0.15 | 0.95 | 1 | 3 | 4 | ||||
| 5 | 0.05 | 1 | 4 | 4 | 4 | ||||
| 3 | 5 | 2 | |||||||
| 2 | |||||||||
| 2 | |||||||||
| 4 | |||||||||
| 1 | |||||||||
| 1 | |||||||||
| 3 | |||||||||
| 5 | |||||||||
| 1 | |||||||||
| 3 | |||||||||
| 1 | |||||||||
| 5 | |||||||||
| 4 |
2) Drag fill handle to generate as many values as needed 3) Copy and Paste VALUES in another location to stop the numbers from changing
4) Generate a Frequency Table for use to generate create charts
1) Create probability distirbution table
Binomial
| Question1 | ||||||||||||||||||||
| 0 | ||||||||||||||||||||
| 0 | ||||||||||||||||||||
| 0 | ||||||||||||||||||||