In this Business Analytics lecture tackles about Random Sampling. The data Random Sampling.xlsm is available at https://github.com/alcadelina/business-analytics-excel-data. The extension is .xlsm because the file contains macro hence when you open it you have to enable it.
We want to choose a simple random sample size of 10 out of the 40 population.
How can this be possible? And how do the summary statistics of the chosen families compare to the corresponding summary statistics of the population?
Objective: To illustrate how Excel’s random number function, RAND, can be used to generate simple random samples.
Excel Solution:
Step 1. Random numbers next to a copy. Copy the original data to columns D and E.
Then enter the formula: =RAND() in cell F10 and copy it down column F.
Step 2. Replace with values. To enable sorting, you
must first “freeze” the random numbers ----that is replace their formulas with
values. To do this, copy the range F10:F49 and select Paste values from the Paste
dropdown menu on the Home Ribbon.
Step 3. Sort. Sort on Column F in ASCENDING ORDER. Then the 10 families with the 10 smallest random numbers are the ones in the sample.
Step 4. Use the AVERAGE, MEDIAN, AND STDEV.S Functions
in Row 6 to calculate summary statistics of the first 10 incomes in Column E.
Similar summary statistics for the population have already been calculated in
Row 5. (cell D5 uses STDEV.P function because this is the POPULATION standard
Deviation)
For the Population Mean:
For the Population Median:
For the Population Standard Deviation:
For Sample Mean:
For Sample Median:
For sample Standard Deviation:
So the complete Excel data is:
You now have 10 sample size out of the 40 population using simple random sampling.
Practice set.
Use the data “Practice Random Sampling” from https://github.com/alcadelina/business-analytics-excel-data
. Generate 20 sample size out of 204 population.
Arnel Lopez Cadeliña, MBA, REA, CFA (Investment Foundation Certificate)
No comments:
Post a Comment