Question

The file P02_35.xlsx contains data from a survey of 500 randomly selected households. a. Suppose you decide to generate a systematic random sample of size 25 from this population of data. How many such samples are there? What is the mean of Debt for each of the first three such samples, using the data in the order given? b. If you wanted to estimate the (supposedly unknown) population mean of Debt from a systematic random sample as in part a, why might it be a good idea to sort first on Debt? If you do so, what is the mean of Debt for each of the first three such samples? Please provide answer in Excel format with steps how to do it. I am not able to upload full table.

Household	Family Size	Location	Ownership	First Income	Second Income	Monthly Payment	Utilities	Debt
1	2	2	1	$58,206	$38,503	$1,585	$252	$5,692
2	6	2	0	$48,273	$29,197	$1,314	$216	$4,267
3	3	4	0	$37,582	$28,164	$383	$207	$2,903
4	1	1	1	$56,610		$1,002	$249	$3,896
5	3	3	0	$37,731	$21,454	$743	$217	$3,011
6	4	1	0	$30,434	$26,007	$991	$208	$3,718
7	1	1	1	$47,969		$849	$243	$5,907
8	1	1	1	$55,487		$752	$242	$2,783
9	3	2	1	$59,947		$1,498	$256	$6,275
10	6	1	0	$36,970	$31,838	$991	$222	$4,845

Answer 1

How many such samples are there?
Number of observation = 500
Sample size = 25
No of samples = 500/25 = 20

What is the mean of Debt for each of the first three such samples, using the data in the order given?
Systematic random sampling is done is by taking ever nth observation. Say every 5th observation.

This can be done in excel as follows.
Number the observation from 1 to 20 in a cyclic manner as shown below.

Use the filter option and filter only sample 1

Take note of the formula used to calculate the mean

Sample 1 - mean = 3888.12
Sample 2 - mean = 4321.88
Sample 3 - mean = 3684.96

b. If you wanted to estimate the (supposedly unknown) population mean of Debt from a systematic random sample as in part a, why might it be a good idea to sort first on Debt? If you do so, what is the mean of Debt for each of the first three such samples?

step 1 - Sort the data based on Debt as shown

Step 2 - Repeat the process of numbering the sample as done before

Step 3 - Filter the sample and find the mean for the first three samples.

Sample 1 - mean = 4153.84
Sample 2 - mean = 4177.88
Sample 3 - mean = 4199.8