Hello,

Any help on the assignment below will be appreciated.

Question 1: SAMPLING (Part A)

Suppose you wish to evaluate the prices (rent asked, per week) of 1to3bedroom rental properties. Your population of interest is: '1to3bedroom rental properties midsize central New Zealand cities'.

You will obtain one sample of about 50 properties from the 'trademe' website. Using your sample data, you will compare prices of houses with prices of apartments in these midsize central NZ cities: Lower Hutt (Wellington), Kapiti Coast (Wellington), Nelson (Nelson), and New Plymouth (Taranaki).

[Note: we will define apartments as a combination of 'apartments', townhouses' and 'units'.]

To perform this study, you must first obtain, from the trademe website (http://www.trademe.co.nz/), population frequency data for each of your property categories.

You are required to:

a)Develop a two-way population frequency table, with rows for each of the two types of property (one row for "houses", another row for "apartments"), and columns for each city.

[There are 8 property categories, meaning that there are 8 strata for this research exercise.]

Be sure to show row and column totals, and an overall population total (N properties).

[2 marks]

... [copy from Excel to Word here, replacing this line of text; the blue font will be retained]

b)Make a copy of your table in (a), then replace numbers with Excel-calculated percentages (of the overall total) to 2 decimal places.[1 mark]

... [copy table to here]

c)Find the 'k'-value that you would need if you were going to take a systematic sample of 50 midsize central NZ city properties. [Note, you will not actually use this method.][1 mark]

...[select and write over these ... lines!!]

d)What are clusters within cities called? Give an example for Kapiti Coast.

[Note, you will not actually use the cluster-sampling method.][1 mark]

...

e)In a new table, show how many properties of each category - 8 categories (strata) in all - will be required for a proportionate stratified sample of 50 properties (n=50). These are the required sample sizes for each category. Use conventional rounding; your overall sample size n may come to 49 or 51.

Show row and column totals, and an overall sample total (n properties).[1 marks]

... [copy table to here]

f)Using all properties from these 8 categories as your population - make a proportionate stratified sample. Use Excel's RANDBETWEEN function to generate random numbers for each category. (You should sample the properties in each category separately.) For each selected property, write the random number you use, and the weekly rent asked (in $).

[2 marks]

...

g)Using Excel's AVERAGE function, calculate - and place into a two-way table - the sampleaverage asking rent for each of the 8 property categories.[2 marks]

... [copy table here]

h)From your table in (g), draw a Excel column chart to show the estimated average asking rent (based on your sample-averages) for each property category.[3 marks]

... [copy chart here]

i)Split your sample into two: houses and apartments. Using your split sample create one table - neatly edited - of descriptive statistics of the asking rents for these sampled properties.

Show houses in one data column, and apartments in a second data column. (Your finished table should have one column of statistical names, and two columns of data.)

Use Excel's Data Analysis ToolPak to develop an initial table, then edit your table.

Include an extra row for the coefficient of variation.

Be sure to delete any measures that you think are not required.

Do not include quartiles.[4 marks]

... [copy table here]

j)Comment on your findings shown in each of the following tables/charts that you created:

(b), (h), and (i) above. [One sentence per table/chart.][3 marks]

...

Question 2: Charting a time series

Using Student ID number (12437), calculate, in Excel, =MOD(student ID,12)+1. Your selected industry will be the one that corresponds to the calculation result.

1 Manufacturing

2 Wholesale Trade

3 Retail Trade

4 Accommodation and Food Services

5 Transport, Postal and Warehousing

6 Information Media and Telecommunications

7 Financial and Insurance Services

8 Rental, Hiring and Real Estate Services

9 Professional, Scientific, Technical, Administrative and Support Services

10 Public Administration and Safety

11 Education and Training

12 Health Care and Social Assistance

Using the Statistics New Zealand Infoshare tool (http://www.stats.govt.nz/infoshare; browse 'Work, Income and Spending', 'Earnings and Employment Survey'), you are required to gather quarterly data on persons employed in your selected industry.

a)Save your quarterly data of Full-Time Equivalent (FTE) Average Weekly Earnings by Industry for females and for males as an Excel table of Earnings from Q2 1993 to Q1 2019.

Then calculate the four-quarter rolling (annual) average percentage change in earnings for each sex separately, for each quarter from Q11995 to Q1 2019.

Copy all four data columns of your table (plus Title, row headers, column header.) to Word using Paste Special, 'Picture'.

Reduce the Picture size to fit your table on a single page.[4 marks]

... [copy table here]

b)Using an appropriate chart type, create in Excel a singlechart showing these two series ('female' and 'male') of annual percentage growth (rolling annual averages) of weekly earnings. Copy your chart as a 'Picture' or 'Bitmap' to Word.[3 marks]

... [copy chart here]

c)Identify two main features of increase or decrease in earnings growth for your selected industry, as you see from your chart in (b). Do not try to explain these changes; just observe. (One of your comments should compare female with male earnings' growth.)[2 marks]

...

Question 3: Hypothesis Test

A large sample from the 2011 New Zealand Income Survey is available from:

http://archive.stats.govt.nz/~/media/Statistics/services/microdata-access/nzis11-cart-surf/nzis11-cart-surf.csv.

Save this as an Excel file. You will also need the Data Dictionary from:

http://archive.stats.govt.nz/~/media/Statistics/services/microdata-access/nzis11-cart-surf/nzis11-cart-surf-data-dic.xls to decode category data such as region and gender.

The Department of Labour is interested in whether there is a difference between the mean weekly income of workers by age and region. Using the sub-SURF Survey data, you should conduct the appropriate test to establish whether, for your allocated region, the mean weekly income of young workers (aged 2034) was different than for middle-age workers (aged 4559). Include only people working from 35 to 40 hours per week

To determine the region to use, calculate in Excel =MOD (student ID, 11) + 1.

Your allocated region will be the one that corresponds to the calculation result. then use =MOD(Student ID, 4)+1

to determine your significance level

1a = 0.10

2a = 0.05

3a = 0.01

4a = 0.001

1 Northland

2 Auckland

3 Waikato

4 Bay of Plenty

5 Hawkes Bay

6 Taranaki

7 Manawatu

8 Wellington

9 Nelson

10 Canterbury

11 Otago

For this task, you will filter your raw data by 'age', 'region [lgr]', and 'hours worked'. [Remember you need to copy filtered data to a new worksheet.] You will then sort your data by age.

Any tables or charts should be produced in Excel, and then copied into this Word template.

a)State the hypothesis that you are testing, and state its alternative.[3 marks]

...

b)Before doing your test, comment on which age group you suspect would earn more than the other. Suggest a reason for your prior expectation.[1 mark]

...

c)Produce an appropriately formatted 2 sample equal variance t-test output table, for young and middle-age workers. Delete the one-tail data.

Include, in your table, the level of significance (a) for your test.[3 marks]

...

d)From your table, determine whether your test result is significant, using both the critical value method and the pvalue method.[3 marks]

...

e)State your conclusion. (If you get a significant result, state which group earns more.)[1 mark]

...

f)Using data from your table in (c), calculate the confidence interval for the difference in average weekly income, for your allocated region, between young and middleage workers.

[3 marks]

...

g)Comment on whether an equal-variance test was the most appropriate test, given your result. [1 mark]

...

h)Suggest any variable other than 'region' (and not relating to sex or age) that might influence mean weekly incomes of fulltime workers.[1 mark]

...

Question 4: REGRESSION

The Department of Labour is also interested in, for each region, the relationships between incomes and hours worked per week for those people with different ethnicities.

For this, using the same downloaded set of data as in Question 3, you will filter by 'ethnicity' and 'region', using the same region as in Q3. (Remove the 'age' and 'hours' filters.)

To determine your assignment ethnicity, calculate, in Excel, =MOD(student ID,3)+1.

Your allocated ethnicity will be the one that corresponds to the calculation result. Your allocatedcategory will be this qualification combined with your region from Q3.

Ethnicity Category

1 Maori and/or Pacific

2 European

3 all other ethnicity (exclude 'Residual')

Using the same downloaded set of data as in Question 3, you are required to:

i)Develop a scatterplot chart (in Excel), to see if there is a linear association between hours worked (x) and income (y) of the people with your allocated region/ethnicity category.

Your Excel-created chart should include the Excel-calculated least-squares linear regression equation ('trendline', appropriately edited), plus your name as a digital signature (in blue, in English letters, and in a different font). [Copy your chart into this assignment.][5 marks]

...

j)Comment on the correlation between income and hours worked.[1 mark]

...

Now add another variable, age, to help explain variation in people's incomes. Use Excel's

Data Analysis Toolpak to conduct a multiple regression to examine the effect of both 'age' and 'weekly hours worked' on the weekly incomes of the people in your allocated category.

You should present below:

k)your edited regression results table[2 marks]

...

l)the resulting regression equation[1 marks]

...

m)Interpret the meaning (not the significance) of each of the two (independent) xvariable coefficients.[2 marks]

...

n)Comment on your multiple regression results with respect to:

(i)the statistical significance of each of the two independent variables[2 marks]

...

(ii)the overall explanatory power of the regression[1 mark]

...

o)Use your regression to predict the income for a 20-25 year old person in your allocated category, who works 60 hours per week.

You should explain whether your prediction is an extrapolation or an interpolation.[2 marks]

...

p)Comment on how you could check one of the assumptions of the overall validity of your multiple linear regression model. [Do not produce a chart.][1 mark]

...

Question 5: Index NUMBERS

q)Create two Simple Relative Index Number Series - one for males, one for females - from the weekly earnings' data you used in Question2 (Part A). Use only your quarterly data from 2008Q1 to 2019Q1, with 2008Q1 as your base period.

Copy your Excel table into your assignment template.[3 marks]

...

r)Using only your two index numbers for 2019Q1, decide whether male or female weekly earnings in your selected industry increased proportionally more, in the period from the beginning of 2008 to the beginning of 2019.[1 mark]

...

s)Using only your weekly earnings' index numbers for 2010Q1 and 2019Q1, calculate, for the current decade so far:

(iii)the percentage increase of both male and female weekly earnings in your industry

[1 mark]

...

(iv)the annual average percentage increase of both male and female weekly earnings in your industry[1 mark]

...