Use the file
Random 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 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
allof 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 NamesSummary Statistics: VariableMeanMedianStDevMaxMinQ1Q3IQRn Frequency Tables: BinProcedure: 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 00000 Q10.00 Min0.00 Median0.00 Max0.00 Q30.00 Mean0.00 Mult-Graphs BoxPlotsHistograms 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 10012.522.512.408602150515 WagesWagesWagesWagesWagesWages 000000 000000 000000 Q1 Min Median Max Mean Q3 20 0 25 45 24.8172043011 30 Instructions & Macro Instructions Generate Original Data IDGenderStart YrDeptBegin $2011$Position 25F2004150700718371 26F2002149800743371 27F2005164200842081 49M2002145500683001 163M2006178990984901 11F2004156000679002 139F2007161000689002 189M2003157500719002 192M2001157800757002 21M1986130000559003 24M1987128500503003 31M1987128750520003 40F1988128000505003 42M1987128300513003 46F2000135000378003 55M1992129500448003 69F1989128500481003 71M1989128300489003 73M1989130000509003 74F1995130200396003 75F1995131700426003 81M1989128950513003 82M1987129600529003 86M1992129100561003 92M1987130800576003 99M1989128500485003 111M1986128300517003 119F1990128950480003 125M1991130500481003 126F1995130700414003 131F1996130000369003 140F1991128500445003 141M1989131500561003 142F1995131200426003 148F1995130700411003 153M1986130500560003 155F2002134500391003 165M1988129000519003 171M1988128000491003 175F1990128500463003 34M2001245600721211 59M2002244500671001 89M2003262500768002 183F2001257300749002 5M1986228160532003 7M1985228160523003 13M1989228500478003 14M1986226800486003 23M1989231000534003 28F1992228800437003 30M1986229800588003 32F1988228160498003 33M1985231000593003 36F1992228800450003 37F1994232800476003 43M1990231100517003 44M1995229500402003 47M1988228160494003 48M1996234000466003 50M1990228500448003 52F1987227500494003 56F1990228500460003 65M1990228000470003 67M1989229200496003 68M1994230500434003 76M1988230600494003 78M1987229000526003 80M1992232000509003 84F1987232000589003 91M1991229600466003 95F1985228400525003 98M1987227500497003 107M1987228800524003 115M1991228800456003 116M1989228500481003 120M1987230000557003 122M1987229300533003 124M1987228500511003 128M1988231500566003 129M1992229500456003 130M1991229500484003 134M1987230200544003 136M1985227500526003 137M1992232000508003 138F1991230200501003 143F1989232550573003 145F1992228800451003 149M1991231500525003 150F1990229250494003 151M1992231000474003 152F1993231400473003 158M1990228500465003 166F1992230000460003 167M1989228300481003 1F2001346000708001 62M2001349000757001 64M2001346000708001 94F2003347000681001 101M2005379490995901 105F2000342960692001 108F2006353000701001 176M2001345900724101 177M2003351200746791 53F2004357550707002 70F2003358000727002 97M2002356000725002 106F2003354000686002 110M2005361500728002 112F2005357000673002 127M2001353750724002 162F2005361000717002 182F2001357600757002 184F2002356800738002 186F2004356800687002 187F2005358500686002 188M2006361300705002 190M2001357900760002 191M2003356800714002 2F1985329600559003 8M1989329000487003 9F1991330000476003 10M1989330300526003 20F1998333000381003 22F1996330000367003 35F1997332000381003 38F1987327500484003 39M2001336500368003 45M1986328000531003 57F1988328160495003 66F2001335000352003 72M1988329600526003 77M1992328200427003 79M1996333250453003 83M2003342000401003 88M1993332300488003 90F1986329800563003 100M2001339000418003 103M1987331600575003 109M1988330500542003 113M1990331500518003 117M1987329000532003 121M1987329100527003 132M1988330800560003 135M1989328300476003 146F1987327200489003 147F1998330000330003 160M1991330000472003 173F2001336500371003 3M2001445910707001 4M2001444450686001 87M2000444500717001 96F1995438900755001 178M2000445600734561 179M2001447600738701 180M2002448600738761 15M2003459000726002 172F1999453250774002 181F2000456400778002 185F2003457300712002 6M1995432000449003 12M1990430000493003 16M1993431000454003 17F1989428000476003 18M1991432000518003 19F1989429000489003 29F1990428200447003 41F1992431000480003 51M1992430000460003 54M1990429000474003 58M1987430300559003 60F1994429400406003 61M1988428500506003 63F1992431400482003 85M1992430500482003 93M1989430200524003 102M1987428500515003 104F1991428400443003 114F1997433000413003 118F1989428300491003 123M1988427500480003 133F1986428500522003 144M1998442200542003 154F1999437655456003 156M1987431000565003 157M1989430800526003 159M1991430500488003 161M1988427500490003 164F1987427200490003 168M1996435000461003 169F1996431000387003 170M1995434000499003 174F1990429750496003 Random Data Generate Generate Salary Data IDGenderStart YrDeptBegin $2011$Position 25F2004150700718371 26F2002149800743371 27F2005164200842081 49M2002145500683001 163M2006178990984901 11F2004156000679002 139F2007161000689002 189M2003157500719002 192M2001157800757002 21M1986130000559003 24M1987128500503003 31M1987128750520003 40F1988128000505003 42M1987128300513003 46F2000135000378003 55M1992129500448003 69F1989128500481003 71M1989128300489003 73M1989130000509003 74F1995130200396003 75F1995131700426003 81M1989128950513003 82M1987129600529003 86M1992129100561003 92M1987130800576003 99M1989128500485003 111M1986128300517003 119F1990128950480003 125M1991130500481003 126F1995130700414003 131F1996130000369003 140F1991128500445003 141M1989131500561003 142F1995131200426003 148F1995130700411003 153M1986130500560003 155F2002134500391003 165M1988129000519003 171M1988128000491003 175F1990128500463003 34M2001245600721211 59M2002244500671001 89M2003262500768002 183F2001257300749002 5M1986228160532003 7M1985228160523003 13M1989228500478003 14M1986226800486003 23M1989231000534003 28F1992228800437003 30M1986229800588003 32F1988228160498003 33M1985231000593003 36F1992228800450003 37F1994232800476003 43M1990231100517003 44M1995229500402003 47M1988228160494003 48M1996234000466003 50M1990228500448003 52F1987227500494003 56F1990228500460003 65M1990228000470003 67M1989229200496003 68M1994230500434003 76M1988230600494003 78M1987229000526003 80M1992232000509003 84F1987232000589003 91M1991229600466003 95F1985228400525003 98M1987227500497003 107M1987228800524003 115M1991228800456003 116M1989228500481003 120M1987230000557003 122M1987229300533003 124M1987228500511003 128M1988231500566003 129M1992229500456003 130M1991229500484003 134M1987230200544003 136M1985227500526003 137M1992232000508003 138F1991230200501003 143F1989232550573003 145F1992228800451003 149M1991231500525003 150F1990229250494003 151M1992231000474003 152F1993231400473003 158M1990228500465003 166F1992230000460003 167M1989228300481003 1F2001346000708001 62M2001349000757001 64M2001346000708001 94F2003347000681001 101M2005379490995901 105F2000342960692001 108F2006353000701001 176M2001345900724101 177M2003351200746791 53F2004357550707002 70F2003358000727002 97M2002356000725002 106F2003354000686002 110M2005361500728002 112F2005357000673002 127M2001353750724002 162F2005361000717002 182F2001357600757002 184F2002356800738002 186F2004356800687002 187F2005358500686002 188M2006361300705002 190M2001357900760002 191M2003356800714002 2F1985329600559003 8M1989329000487003 9F1991330000476003 10M1989330300526003 20F1998333000381003 22F1996330000367003 35F1997332000381003 38F1987327500484003 39M2001336500368003 45M1986328000531003 57F1988328160495003 66F2001335000352003 72M1988329600526003 77M1992328200427003 79M1996333250453003 83M2003342000401003 88M1993332300488003 90F1986329800563003 100M2001339000418003 103M1987331600575003 109M1988330500542003 113M1990331500518003 117M1987329000532003 121M1987329100527003 132M1988330800560003 135M1989328300476003 146F1987327200489003 147F1998330000330003 160M1991330000472003 173F2001336500371003 3M2001445910707001 4M2001444450686001 87M2000444500717001 96F1995438900755001 178M2000445600734561 179M2001447600738701 180M2002448600738761 15M2003459000726002 172F1999453250774002 181F2000456400778002 185F2003457300712002 6M1995432000449003 12M1990430000493003 16M1993431000454003 17F1989428000476003 18M1991432000518003 19F1989429000489003 29F1990428200447003 41F1992431000480003 51M1992430000460003 54M1990429000474003 58M1987430300559003 60F1994429400406003 61M1988428500506003 63F1992431400482003 85M1992430500482003 93M1989430200524003 102M1987428500515003 104F1991428400443003 114F1997433000413003 118F1989428300491003 123M1988427500480003 133F1986428500522003 144M1998442200542003 154F1999437655456003 156M1987431000565003 157M1989430800526003 159M1991430500488003 161M1988427500490003 164F1987427200490003 168M1996435000461003 169F1996431000387003 170M1995434000499003 174F1990429750496003 Data Critical t-statistic DataTargeta-level2-tail1-tail tn-1 nTest Stat Meanp-value2-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 TargetnMeanStDevStdError Test Stat0 2-tail1-tail p-value0.00000 Sheet3