Assignment
Unit Learning Outcomes
a. Assess situations in which statistical analysis may be useful.
b. Apply a range of statistical measures and techniques that may be useful in analysing various situations.
c. Solve statistical problems using analytical methods manually and interpret the results.
d . Solve and interpret appropriate output from statistical computer packages.
e. Demonstrate the ability to apply statistical tools and techniques to solve problems to improve business decision-making process.
Assignment Description
The assignment has a total of six tasks to complete.
For the assignment you need to create your own data set using the data provided as LGAData.xls excel file.
Presentation
Your answers must be presented in task number order and be clearly labelled with the appropriate task number. Your assignment must be presented in Microsoft (MS) Word. Copy and paste any relevant Excel outputs to this document immediately before (above) any relevant written answers to each task. If you are unfamiliar with the use of the MS Word Equations Editor, you may write algebraic/ mathematical or statistical symbols and notation in neat handwritten form.
Your answers must be clear. You must highlight relevant items on any required Excel outputs and make reference to them in your written answers. When asked to perform a manual calculation (i.e. the use of MS Excel is not specified) you must show all working. This must include intermediate steps where relevant. Failure to do so will result in a loss of marks. Do not include the assignment questions nor the data with your submitted assignment.
Introduction
The Assignment Data (LGAData.xls) file contains, LGA data (sourced from ABS) for a population of 400 LGA in Australia. You are required to select a random sample of 50 LGA from this population. Variables in the data set are as below:
|
PN
|
ID number
|
ID
|
|
V1
|
LGA name
|
Name
|
|
V2
|
Gender
|
Male=1; Female=0
|
|
V3
|
Age Group
|
30-34 =1; 35-39=2; 39-44 =3; 45-50=4
|
|
V4
|
Age1
|
number of people 30-34 years old
|
|
V5
|
Age2
|
number of people 35-39 years old
|
|
V6
|
Age3
|
number of people 39-44 years old
|
|
V7
|
Age4
|
number of people 45-50 years old
|
|
V8
|
Income category
|
$650-$800=0; $801-$850=1
|
|
V9
|
Income1
|
number of people with $650-$800 weekly income
|
|
V10
|
Income2
|
number of people with $801-$850 weekly income
|
|
V11
|
Occupation type
|
Managers=1; Professionals=2;Sales workers=3;Administration=4
|
|
V12
|
Occupation1
|
number of Managers
|
|
V13
|
Occupation2
|
number of Professionals
|
|
V14
|
Occupation3
|
number of Sales workers
|
|
V15
|
Occupation4
|
number of Administration
|
|
V16
|
Industry of occupation
|
Mining=1; Agriculture=2;Manufacturing=3;Education=4
|
|
V17
|
Industry1
|
number of people in Mining
|
|
V18
|
Industry2
|
number of people in Agriculture
|
|
V19
|
Industry3
|
number of people in Manufacturing
|
|
V20
|
Industry4
|
number of people in Education
|
|
V21
|
Transport
|
0=car; 1=public transport
|
|
V22
|
Living with children
|
0=with; 1=without
|
Column A (PN), contains the LGA identification numbers from 001 to 400 LGA.
Selecting Random Sample and Creating your Sample Data File
To select your random sample, you need to open the PopulationLGAData.xls file and create a SampleLGAData Excel file of 50 samples (rows) which will include 50 LGA. To select these samples randomly one member of the group starts with 2 last digits of their student ID and adds 50 to it to create the data set. For example a student with ID of MIT172256 will start with row 56 to add 50 rows to it which will include rows 56-106. Then the data set for this group will include LGA 56-106.
You will need to submit a SOFT copy of your sample LGAdata. You will also need to report what student ID was used for random data selection. This sample data set will form the basis of the statistical presentation and analysis tasks of the assignment.
Assignment requirements
Answers to the assignment tasks must be based on the sample data file that you have created. All tasks in this assignment require you to obtain an Excel output prior to performing some analysis. Copy and Paste these outputs to your assignment MS- Word document immediately preceding any subsequent analysis. Charts and tables must have appropriate titles and numerical values must be rounded to an appropriate number of decimal places and accompanied by the correct units of measure.
Task 1
Use Excel to produce a Frequency Column Chart and; a Relative Frequency Pie-Chart for your sample to show the number and proportion, respectively, of people under each age group. Use these graphical summaries to answer the following questions:
(a) How many LGA in your sample indicate use of public transport?
(b) Which occupation type occurs most frequently in your sample?
(c) What proportion of LGA in your sample consists of people within 30-34 years old age group?
Task 2
(a) Use Excel to sort your sample "occupation 4" data. Use the percentile location formula;
Lp = (n +1).(P/100), and the three associated rules to determine:
(i) The 70th percentile.
(ii) The first and third quartiles.
Remember to show all working!
(b) Briefly explain what the 70th percentile that you have determined informs you about your sample "occupation 4" data.
(c) Determine the Inter-Quartile Range of your sample "occupation 4" data and provide a brief explanation of what information this statistic provides about your sample data.
Task 3
(a) Use Excel to produce a Descriptive Statistics table for your sample "occupation 4" data.
(b) Use results from Task 3 to determine manually for this data, the upper and lower inner fence limits;
IFUL = Q3 + 1.5 x IQR
IFLL = Q1 - 1.5 x IQR
(c) Based on the limits calculated in (b), choose from the numerical summary measures provided in the Descriptive Statistics table, and/or measures calculated previously in Task 4;
(i) an appropriate measure of central tendency, and,
(ii) an appropriate measure of dispersion for your sample "occupation 4"data.
Remember to show all working!
Provide a brief explanation of the reasoning behind your choice in both cases.
(d) Write a brief report on the "occupation 4" data paying particular attention, on the mean, median, quartiles and measures of variation.
Task 4
Remember to show all working! Failure to do so will result in the loss of marks.
(a) From the Descriptive Statistics table obtained in Task 4, identity three pieces of evidence that indicate whether your sample "occupation 4" data has been obtained from a normally distributed population or not. What is your conclusion?
Note: Make sure only one piece of evidence relates to the shape of the sample data.
(b) Regardless of your conclusion in above, assume the "occupation 4" data is normally distributed. Applying the Standard Normal tables, calculate how many "occupation 4" observations in your sample would expect to lie within 1.5 standard deviations of the mean (i.e. between z = -1.5 and z = +1.5).
(c) Use the mean and standard deviation from the Descriptive Statistics table of Task 4 to calculate the bound for 1.5 standard deviation spread from the mean. Using the "occupation 4" sample data, manually count the number of observations fall within the bound. State whether this count matches, approximately, your answer to (b) and hence whether this result confirms (or not) your conclusion in (a).
Task 5
(a) Use Excel to produce a Descriptive Statistics table for the "Occupation 4" variable in your sample suitable for constructing an interval estimate of the population mean "Occupation 4"
Hence determine:
(i) A point estimate of the mean "Occupation 4" of the population.
(ii) A 90% confidence interval estimate of the mean "Occupation 4" of the population.
(iii) Make a brief verbal statement explaining the meaning of the confidence interval estimate obtained in (ii) in the context of the variable in this task.
(b) If the population mean "Occupation 4"is actually 59, would you consider the interval estimate obtained in (a), to be satisfactory? Explain why or why not.
Task 6
(a) Use Excel to produce a Descriptive Statistics table for the $650-$800 income earners in your sample suitable for constructing an interval estimate of the population proportion of income earners. Hence determine:
(i) A point estimate of the proportion of $650-$800 income earners in the population.
(ii) A 99% confidence interval estimate of the $650-$800 income earners.
(b) Using the following formula:
(sample statistic) ± (critical z or t) x (standard error of the sample statistic)
Use the Empirical Rule for a Normal distribution to determine a 95% confidence interval estimate of the
$650-$800 income earners in the population.
(c) Compare, in terms of the precision, the interval manually calculated in (b) with the interval obtained from the Descriptive Statistics table in (a). Explain why the direction of the change in precision is expected.
Attachment:- Data Variables.rar