Use the fileRandom Sampler Generator.xlsto do this. Your tutor will show you how this can be done in...

Question

Use the fileRandom Sampler Generator.xlsto do this. Your tutor will show you how this can be done in EXCEL.Your answers to the assignment tasks below are to be based on your sample of 100 cases.Make sure to keep a safe copy of your sample since you cannot useRandom Sampler Generator.xlsto reproduce the first sample. Provide a printout of the data in your sample, with ID numbers in ascending order. When sorting your sample remember to highlightallof your sample data before sorting. Copy your sample file to another spreadsheet for working your assignment. Make sure you save your random sample.			Document Preview:				Sheet3 t-test Data Mean StDev n Target p-value Data 2-tail 1-tail Test Stat StdError a-level tn-1 Critical t-statistic Procedure:    ?  Copy, or paste raw data into Column B (accepts samples up to n=150)    ?  Enter a-level into cell H2 as a decimal (0.01, 0.05 or 0.10, etc.)    ?  Enter Target value into E2 (i.e. H0 - value)     ?  Decide to accept or reject H0 based on p-value or critical t-statistic     24.00 2.00 0.50   2.00 1.00     1.00                 1.00 2.00            Sheet3 t-test Data Mean StDev n Target p-value Data 2-tail 1-tail Test Stat StdError a-level tn-1 Critical t-statistic Procedure:    ?  Copy, or paste raw data into Column B (accepts samples up to n=150)    ?  Enter a-level into cell H2 as a decimal (0.01, 0.05 or 0.10, etc.)    ?  Enter Target value into E2 (i.e. H0 - value)     ?  Decide to accept or reject H0 based on p-value or critical t-statistic     24.00 2.00 0.50   2.00 1.00     1.00                 1.00 2.00	The data for this assignment is quarterly data describing the number of accidents recorded by a large company employing between 14000 and 18000 workers throughout Australia STATS1900 Business Statistics Major Assignment Semester 2, 2011 Date Due: Refer to Unit Description Total Marks: 40 marks Worth:  20% of final assessment This assignment requires a considerable amount of computer work and written comment.  You may need to seek guidance from your tutor along the way.  Do not leave things until too late!!  Each question describes in detail what you are required to do so please follow these carefully! In this assignment you will examine data on employees of a medium sized company.  The variables which have been recorded for each employee are listed below, and the data is contained in the file Salary_11.xls. 	Variable 	Description 	Id 	Employee identity number 	Gender 	Gender of staff member.  	Start Yr 	Year employee started work with the company 	Dept 	Department. 1: Warehousing; 2: Import/Export; 3:Production; 4: Management. 	Begin $ 	Initial Salary  	2011 $ 	Current Salary 	Position 	Position in Company. 1:Management; 2: Clerical; 3: Production. The company is about to enter into negotiations over the salaries of production workers and management has asked for a report on their current pay.  Random Sample: Before you begin your analysis you are required to take a random sample of size 100. Use the file Random Sampler Generator.xls to do this.  Your tutor will show you how this can be done in EXCEL.  Your answers to the assignment tasks below are to be based on your sample of 100 cases.  Make sure to keep a safe copy of your sample since you cannot use Random Sampler Generator.xls to reproduce the first sample.  Provide a printout of the data in your sample, with ID numbers in ascending order.  When sorting your sample remember to highlight all of your sample data before sorting.  Copy your sample file to another spreadsheet for working your assignment.  Make sure you save your random sample. Assignment Tasks 1. Determine the mean, standard deviation and standard error for the production workers in your sample.  Use this information to carry out a t-test to see if the average pay for production workers is less than $49,000. 2. Construct a new variable called Experience which is equal to the length of time each employee has worked for the company.  State the formula used for the new variable. Obtain a scatter-plot of Current Pay vs Experience for Production staff.  Think carefully about which variable should go on the vertical axis – remember, it is the independent variable that goes on the horizontal axis (i.e. the x-axis).  Make sure you label your axes properly and that your graph has an appropriate title.   Briefly describe the nature of the relationship between these two variables. 3. Use XL to carry out a regression analysis on these two variables. (This is for the production staff only.)  Copy the output into your assignment and use it to respond to the following: · Write down the regression equation. · State the R-Square value and the Standard Error and explain what they mean with respect to this data. · Write down the value of the gradient of the regression line and explain what it means in this case. · Is the constant or intercept value significant in this case?  How do you know this? · Briefly explain why you think this regression model is, or is not, a good model. 4. For production staff, obtain a stratified scatterplot distinguishing males and females. Make sure that the scatterplot is labeled appropriately and that the markers for males and females can be easily distinguished and are an appropriate size. Your scatterplot should include trendlines, equations and R2 values for each group.  Provide a brief comparison of the pay for the two groups, noting important differences, if any, between them.  Do males receive better pay than females?  Do males receive better pay increases than females? 5. Using information from your analyses write a short concluding paragraph about the pay of production staff and, in particular, the pay for male and female production staff. 2 marks 3 marks 2 marks 2 marks 2 marks 2 marks 2 marks 2 marks 2 marks 2 marks 2 mark 3 marks 2 marks 3 marks 2 marks 3 marks 2 marks 2 marks   Data 	 		Raw Data 	Var Names							Summary Statistics: 								Variable	Mean	Median	StDev	Max	Min	Q1	Q3	IQR	n 								Frequency Tables: 								Bin							Procedure: 															  Copy, or paste raw data into Cols B-F 															  Use the Min & Max values to determine Bin Size 															  Enter Bin reference values into H13:H27 															  Adjust Bin refs as required 									0	0	0	0	0 								Q1	0.00 								Min	0.00 								Median	0.00 								Max	0.00 								Q3	0.00 								Mean	0.00 Mult-Graphs 	 		BoxPlots										Histograms Mult-Graphs 	5 	10 	15 	20 	25 	30 	35 	40 	45 	50 	55 	60 	0 	0 	0 12 37 77 23 1 1 0 0 0 0 0 0 Sheet1 	5 	10 	15 	20 	25 	30 	35 	40 	45 	50 	55 	60 	0 	0 	0 Wages 6 11 14 35 49 54 18 16 2 2 1 0 	5 	10 	15 	20 	25 	30 	35 	40 	45 	50 	55 	60 	0 	0 	0 0 0 0 0 0 0 0 0 0 0 0 0 	5 	10 	15 	20 	25 	30 	35 	40 	45 	50 	55 	60 	0 	0 	0 0 0 0 0 0 0 0 0 0 0 0 0 	5 	10 	15 	20 	25 	30 	35 	40 	45 	50 	55 	60 	0 	0 	0 0 0 0 0 0 0 0 0 0 0 0 0 	10	0	12.5	22.5	12.4086021505	15 	Wages	Wages	Wages	Wages	Wages	Wages 	0	0	0	0	0	0 	0	0	0	0	0	0 	0	0	0	0	0	0 Q1 Min Median Max Mean Q3 20 0 25 45 24.8172043011 30 	   Instructions & Macro 		Instructions Generate Original Data 	ID	Gender	Start Yr	Dept	Begin $	2011$	Position 	25	F	2004	1	50700	71837	1 	26	F	2002	1	49800	74337	1 	27	F	2005	1	64200	84208	1 	49	M	2002	1	45500	68300	1 	163	M	2006	1	78990	98490	1 	11	F	2004	1	56000	67900	2 	139	F	2007	1	61000	68900	2 	189	M	2003	1	57500	71900	2 	192	M	2001	1	57800	75700	2 	21	M	1986	1	30000	55900	3 	24	M	1987	1	28500	50300	3 	31	M	1987	1	28750	52000	3 	40	F	1988	1	28000	50500	3 	42	M	1987	1	28300	51300	3 	46	F	2000	1	35000	37800	3 	55	M	1992	1	29500	44800	3 	69	F	1989	1	28500	48100	3 	71	M	1989	1	28300	48900	3 	73	M	1989	1	30000	50900	3 	74	F	1995	1	30200	39600	3 	75	F	1995	1	31700	42600	3 	81	M	1989	1	28950	51300	3 	82	M	1987	1	29600	52900	3 	86	M	1992	1	29100	56100	3 	92	M	1987	1	30800	57600	3 	99	M	1989	1	28500	48500	3 	111	M	1986	1	28300	51700	3 	119	F	1990	1	28950	48000	3 	125	M	1991	1	30500	48100	3 	126	F	1995	1	30700	41400	3 	131	F	1996	1	30000	36900	3 	140	F	1991	1	28500	44500	3 	141	M	1989	1	31500	56100	3 	142	F	1995	1	31200	42600	3 	148	F	1995	1	30700	41100	3 	153	M	1986	1	30500	56000	3 	155	F	2002	1	34500	39100	3 	165	M	1988	1	29000	51900	3 	171	M	1988	1	28000	49100	3 	175	F	1990	1	28500	46300	3 	34	M	2001	2	45600	72121	1 	59	M	2002	2	44500	67100	1 	89	M	2003	2	62500	76800	2 	183	F	2001	2	57300	74900	2 	5	M	1986	2	28160	53200	3 	7	M	1985	2	28160	52300	3 	13	M	1989	2	28500	47800	3 	14	M	1986	2	26800	48600	3 	23	M	1989	2	31000	53400	3 	28	F	1992	2	28800	43700	3 	30	M	1986	2	29800	58800	3 	32	F	1988	2	28160	49800	3 	33	M	1985	2	31000	59300	3 	36	F	1992	2	28800	45000	3 	37	F	1994	2	32800	47600	3 	43	M	1990	2	31100	51700	3 	44	M	1995	2	29500	40200	3 	47	M	1988	2	28160	49400	3 	48	M	1996	2	34000	46600	3 	50	M	1990	2	28500	44800	3 	52	F	1987	2	27500	49400	3 	56	F	1990	2	28500	46000	3 	65	M	1990	2	28000	47000	3 	67	M	1989	2	29200	49600	3 	68	M	1994	2	30500	43400	3 	76	M	1988	2	30600	49400	3 	78	M	1987	2	29000	52600	3 	80	M	1992	2	32000	50900	3 	84	F	1987	2	32000	58900	3 	91	M	1991	2	29600	46600	3 	95	F	1985	2	28400	52500	3 	98	M	1987	2	27500	49700	3 	107	M	1987	2	28800	52400	3 	115	M	1991	2	28800	45600	3 	116	M	1989	2	28500	48100	3 	120	M	1987	2	30000	55700	3 	122	M	1987	2	29300	53300	3 	124	M	1987	2	28500	51100	3 	128	M	1988	2	31500	56600	3 	129	M	1992	2	29500	45600	3 	130	M	1991	2	29500	48400	3 	134	M	1987	2	30200	54400	3 	136	M	1985	2	27500	52600	3 	137	M	1992	2	32000	50800	3 	138	F	1991	2	30200	50100	3 	143	F	1989	2	32550	57300	3 	145	F	1992	2	28800	45100	3 	149	M	1991	2	31500	52500	3 	150	F	1990	2	29250	49400	3 	151	M	1992	2	31000	47400	3 	152	F	1993	2	31400	47300	3 	158	M	1990	2	28500	46500	3 	166	F	1992	2	30000	46000	3 	167	M	1989	2	28300	48100	3 	1	F	2001	3	46000	70800	1 	62	M	2001	3	49000	75700	1 	64	M	2001	3	46000	70800	1 	94	F	2003	3	47000	68100	1 	101	M	2005	3	79490	99590	1 	105	F	2000	3	42960	69200	1 	108	F	2006	3	53000	70100	1 	176	M	2001	3	45900	72410	1 	177	M	2003	3	51200	74679	1 	53	F	2004	3	57550	70700	2 	70	F	2003	3	58000	72700	2 	97	M	2002	3	56000	72500	2 	106	F	2003	3	54000	68600	2 	110	M	2005	3	61500	72800	2 	112	F	2005	3	57000	67300	2 	127	M	2001	3	53750	72400	2 	162	F	2005	3	61000	71700	2 	182	F	2001	3	57600	75700	2 	184	F	2002	3	56800	73800	2 	186	F	2004	3	56800	68700	2 	187	F	2005	3	58500	68600	2 	188	M	2006	3	61300	70500	2 	190	M	2001	3	57900	76000	2 	191	M	2003	3	56800	71400	2 	2	F	1985	3	29600	55900	3 	8	M	1989	3	29000	48700	3 	9	F	1991	3	30000	47600	3 	10	M	1989	3	30300	52600	3 	20	F	1998	3	33000	38100	3 	22	F	1996	3	30000	36700	3 	35	F	1997	3	32000	38100	3 	38	F	1987	3	27500	48400	3 	39	M	2001	3	36500	36800	3 	45	M	1986	3	28000	53100	3 	57	F	1988	3	28160	49500	3 	66	F	2001	3	35000	35200	3 	72	M	1988	3	29600	52600	3 	77	M	1992	3	28200	42700	3 	79	M	1996	3	33250	45300	3 	83	M	2003	3	42000	40100	3 	88	M	1993	3	32300	48800	3 	90	F	1986	3	29800	56300	3 	100	M	2001	3	39000	41800	3 	103	M	1987	3	31600	57500	3 	109	M	1988	3	30500	54200	3 	113	M	1990	3	31500	51800	3 	117	M	1987	3	29000	53200	3 	121	M	1987	3	29100	52700	3 	132	M	1988	3	30800	56000	3 	135	M	1989	3	28300	47600	3 	146	F	1987	3	27200	48900	3 	147	F	1998	3	30000	33000	3 	160	M	1991	3	30000	47200	3 	173	F	2001	3	36500	37100	3 	3	M	2001	4	45910	70700	1 	4	M	2001	4	44450	68600	1 	87	M	2000	4	44500	71700	1 	96	F	1995	4	38900	75500	1 	178	M	2000	4	45600	73456	1 	179	M	2001	4	47600	73870	1 	180	M	2002	4	48600	73876	1 	15	M	2003	4	59000	72600	2 	172	F	1999	4	53250	77400	2 	181	F	2000	4	56400	77800	2 	185	F	2003	4	57300	71200	2 	6	M	1995	4	32000	44900	3 	12	M	1990	4	30000	49300	3 	16	M	1993	4	31000	45400	3 	17	F	1989	4	28000	47600	3 	18	M	1991	4	32000	51800	3 	19	F	1989	4	29000	48900	3 	29	F	1990	4	28200	44700	3 	41	F	1992	4	31000	48000	3 	51	M	1992	4	30000	46000	3 	54	M	1990	4	29000	47400	3 	58	M	1987	4	30300	55900	3 	60	F	1994	4	29400	40600	3 	61	M	1988	4	28500	50600	3 	63	F	1992	4	31400	48200	3 	85	M	1992	4	30500	48200	3 	93	M	1989	4	30200	52400	3 	102	M	1987	4	28500	51500	3 	104	F	1991	4	28400	44300	3 	114	F	1997	4	33000	41300	3 	118	F	1989	4	28300	49100	3 	123	M	1988	4	27500	48000	3 	133	F	1986	4	28500	52200	3 	144	M	1998	4	42200	54200	3 	154	F	1999	4	37655	45600	3 	156	M	1987	4	31000	56500	3 	157	M	1989	4	30800	52600	3 	159	M	1991	4	30500	48800	3 	161	M	1988	4	27500	49000	3 	164	F	1987	4	27200	49000	3 	168	M	1996	4	35000	46100	3 	169	F	1996	4	31000	38700	3 	170	M	1995	4	34000	49900	3 	174	F	1990	4	29750	49600	3 Random Data 	 Generate Generate   Salary Data 	ID	Gender	Start Yr	Dept	Begin $	2011$	Position 	25	F	2004	1	50700	71837	1 	26	F	2002	1	49800	74337	1 	27	F	2005	1	64200	84208	1 	49	M	2002	1	45500	68300	1 	163	M	2006	1	78990	98490	1 	11	F	2004	1	56000	67900	2 	139	F	2007	1	61000	68900	2 	189	M	2003	1	57500	71900	2 	192	M	2001	1	57800	75700	2 	21	M	1986	1	30000	55900	3 	24	M	1987	1	28500	50300	3 	31	M	1987	1	28750	52000	3 	40	F	1988	1	28000	50500	3 	42	M	1987	1	28300	51300	3 	46	F	2000	1	35000	37800	3 	55	M	1992	1	29500	44800	3 	69	F	1989	1	28500	48100	3 	71	M	1989	1	28300	48900	3 	73	M	1989	1	30000	50900	3 	74	F	1995	1	30200	39600	3 	75	F	1995	1	31700	42600	3 	81	M	1989	1	28950	51300	3 	82	M	1987	1	29600	52900	3 	86	M	1992	1	29100	56100	3 	92	M	1987	1	30800	57600	3 	99	M	1989	1	28500	48500	3 	111	M	1986	1	28300	51700	3 	119	F	1990	1	28950	48000	3 	125	M	1991	1	30500	48100	3 	126	F	1995	1	30700	41400	3 	131	F	1996	1	30000	36900	3 	140	F	1991	1	28500	44500	3 	141	M	1989	1	31500	56100	3 	142	F	1995	1	31200	42600	3 	148	F	1995	1	30700	41100	3 	153	M	1986	1	30500	56000	3 	155	F	2002	1	34500	39100	3 	165	M	1988	1	29000	51900	3 	171	M	1988	1	28000	49100	3 	175	F	1990	1	28500	46300	3 	34	M	2001	2	45600	72121	1 	59	M	2002	2	44500	67100	1 	89	M	2003	2	62500	76800	2 	183	F	2001	2	57300	74900	2 	5	M	1986	2	28160	53200	3 	7	M	1985	2	28160	52300	3 	13	M	1989	2	28500	47800	3 	14	M	1986	2	26800	48600	3 	23	M	1989	2	31000	53400	3 	28	F	1992	2	28800	43700	3 	30	M	1986	2	29800	58800	3 	32	F	1988	2	28160	49800	3 	33	M	1985	2	31000	59300	3 	36	F	1992	2	28800	45000	3 	37	F	1994	2	32800	47600	3 	43	M	1990	2	31100	51700	3 	44	M	1995	2	29500	40200	3 	47	M	1988	2	28160	49400	3 	48	M	1996	2	34000	46600	3 	50	M	1990	2	28500	44800	3 	52	F	1987	2	27500	49400	3 	56	F	1990	2	28500	46000	3 	65	M	1990	2	28000	47000	3 	67	M	1989	2	29200	49600	3 	68	M	1994	2	30500	43400	3 	76	M	1988	2	30600	49400	3 	78	M	1987	2	29000	52600	3 	80	M	1992	2	32000	50900	3 	84	F	1987	2	32000	58900	3 	91	M	1991	2	29600	46600	3 	95	F	1985	2	28400	52500	3 	98	M	1987	2	27500	49700	3 	107	M	1987	2	28800	52400	3 	115	M	1991	2	28800	45600	3 	116	M	1989	2	28500	48100	3 	120	M	1987	2	30000	55700	3 	122	M	1987	2	29300	53300	3 	124	M	1987	2	28500	51100	3 	128	M	1988	2	31500	56600	3 	129	M	1992	2	29500	45600	3 	130	M	1991	2	29500	48400	3 	134	M	1987	2	30200	54400	3 	136	M	1985	2	27500	52600	3 	137	M	1992	2	32000	50800	3 	138	F	1991	2	30200	50100	3 	143	F	1989	2	32550	57300	3 	145	F	1992	2	28800	45100	3 	149	M	1991	2	31500	52500	3 	150	F	1990	2	29250	49400	3 	151	M	1992	2	31000	47400	3 	152	F	1993	2	31400	47300	3 	158	M	1990	2	28500	46500	3 	166	F	1992	2	30000	46000	3 	167	M	1989	2	28300	48100	3 	1	F	2001	3	46000	70800	1 	62	M	2001	3	49000	75700	1 	64	M	2001	3	46000	70800	1 	94	F	2003	3	47000	68100	1 	101	M	2005	3	79490	99590	1 	105	F	2000	3	42960	69200	1 	108	F	2006	3	53000	70100	1 	176	M	2001	3	45900	72410	1 	177	M	2003	3	51200	74679	1 	53	F	2004	3	57550	70700	2 	70	F	2003	3	58000	72700	2 	97	M	2002	3	56000	72500	2 	106	F	2003	3	54000	68600	2 	110	M	2005	3	61500	72800	2 	112	F	2005	3	57000	67300	2 	127	M	2001	3	53750	72400	2 	162	F	2005	3	61000	71700	2 	182	F	2001	3	57600	75700	2 	184	F	2002	3	56800	73800	2 	186	F	2004	3	56800	68700	2 	187	F	2005	3	58500	68600	2 	188	M	2006	3	61300	70500	2 	190	M	2001	3	57900	76000	2 	191	M	2003	3	56800	71400	2 	2	F	1985	3	29600	55900	3 	8	M	1989	3	29000	48700	3 	9	F	1991	3	30000	47600	3 	10	M	1989	3	30300	52600	3 	20	F	1998	3	33000	38100	3 	22	F	1996	3	30000	36700	3 	35	F	1997	3	32000	38100	3 	38	F	1987	3	27500	48400	3 	39	M	2001	3	36500	36800	3 	45	M	1986	3	28000	53100	3 	57	F	1988	3	28160	49500	3 	66	F	2001	3	35000	35200	3 	72	M	1988	3	29600	52600	3 	77	M	1992	3	28200	42700	3 	79	M	1996	3	33250	45300	3 	83	M	2003	3	42000	40100	3 	88	M	1993	3	32300	48800	3 	90	F	1986	3	29800	56300	3 	100	M	2001	3	39000	41800	3 	103	M	1987	3	31600	57500	3 	109	M	1988	3	30500	54200	3 	113	M	1990	3	31500	51800	3 	117	M	1987	3	29000	53200	3 	121	M	1987	3	29100	52700	3 	132	M	1988	3	30800	56000	3 	135	M	1989	3	28300	47600	3 	146	F	1987	3	27200	48900	3 	147	F	1998	3	30000	33000	3 	160	M	1991	3	30000	47200	3 	173	F	2001	3	36500	37100	3 	3	M	2001	4	45910	70700	1 	4	M	2001	4	44450	68600	1 	87	M	2000	4	44500	71700	1 	96	F	1995	4	38900	75500	1 	178	M	2000	4	45600	73456	1 	179	M	2001	4	47600	73870	1 	180	M	2002	4	48600	73876	1 	15	M	2003	4	59000	72600	2 	172	F	1999	4	53250	77400	2 	181	F	2000	4	56400	77800	2 	185	F	2003	4	57300	71200	2 	6	M	1995	4	32000	44900	3 	12	M	1990	4	30000	49300	3 	16	M	1993	4	31000	45400	3 	17	F	1989	4	28000	47600	3 	18	M	1991	4	32000	51800	3 	19	F	1989	4	29000	48900	3 	29	F	1990	4	28200	44700	3 	41	F	1992	4	31000	48000	3 	51	M	1992	4	30000	46000	3 	54	M	1990	4	29000	47400	3 	58	M	1987	4	30300	55900	3 	60	F	1994	4	29400	40600	3 	61	M	1988	4	28500	50600	3 	63	F	1992	4	31400	48200	3 	85	M	1992	4	30500	48200	3 	93	M	1989	4	30200	52400	3 	102	M	1987	4	28500	51500	3 	104	F	1991	4	28400	44300	3 	114	F	1997	4	33000	41300	3 	118	F	1989	4	28300	49100	3 	123	M	1988	4	27500	48000	3 	133	F	1986	4	28500	52200	3 	144	M	1998	4	42200	54200	3 	154	F	1999	4	37655	45600	3 	156	M	1987	4	31000	56500	3 	157	M	1989	4	30800	52600	3 	159	M	1991	4	30500	48800	3 	161	M	1988	4	27500	49000	3 	164	F	1987	4	27200	49000	3 	168	M	1996	4	35000	46100	3 	169	F	1996	4	31000	38700	3 	170	M	1995	4	34000	49900	3 	174	F	1990	4	29750	49600	3   Data 											Critical t-statistic 		Data		Target			a-level				2-tail	1-tail 										tn-1 				n			Test Stat 				Mean			p-value		2-tail 				StDev 				StdError 				Procedure: 				  Copy, or paste raw data into Column B (accepts samples up to n=150) 				  Enter Target value into E2 (i.e. H0 - value) 				  Enter a-level into cell H2 as a decimal (0.01, 0.05 or 0.10, etc.) 				  Decide to accept or reject H0 based on p-value or critical t-statistic t-test 	 		Target	n	Mean	StDev	StdError 			Test Stat	0 				2-tail	1-tail 			p-value	0.0000	0 Sheet3

Use the file Random Sampler Generator.xls to do this. Your tutor will show you how this can be done in EXCEL. Your answers to the assignment tasks below are to be based on your sample of 100 cases....

Get Answer To This Question

Related Questions & Answers

Submit New Assignment