1 Descriptive statistics This assignment is designed to test Unit Objective 1 Due date: Midnight, Monday of Week 4 (15th August 2016) or as specified by your lecturer. This assignment is marked out of 100 and is worth 10% of the assessment in this unit. You must use Excel to generate the relevant output. The data for this assignment is in the file BUGEN1502_A1_S216.xls. which can be found in the Assessments block in Moodle. The file has two worksheets, labelled Data, and Results. The file is arranged so that all the required results needed for the assignment can be obtained from the worksheet labelled Results. The appropriate places for graphs and tables are set out in this worksheet but you need to create your own textboxes. It is recommended that you first create the graphs and tables in the data worksheets, and then copy them to the appropriate places in the Results worksheet. In fact on some computers it is essential that you work in this way, as some networked computers have difficulty dealing with data analysis tools when data is selected from a different worksheet. This assignment is to be submitted using the Moodle assignment submission system. Copy and paste the relevant Excel tables, graphs and comments into a Word document, which should consist of the answers to the 10 questions, numbered and in order. Ensure that all graphs and tables used are well labelled, including titles. The assignment is to be handed in as an electronic copy of the completed Word document or as a pdf document. Do not submit the Excel file in which you completed your working. Be sure to include your name and student ID on the assignment. While the presentation of the assignment is important, and some marks are designated for presentation, elaborate features are not required. Your work must be easy to read. The quality of the presentation should be worthy of submission to your employer or project manager. Question Background Rental costs are significant part of the overall cost of a University education for many international students. The student union at a large citybased university is concerned about the affordability and availability of reasonably priced student housing with good public transport access to the campus. They take a survey of two bedroom apartments within half an hour's travelling time of the campus. Properties are split into two categories, Near which contains properties which had access to public transport to the university within a five minute walk and Other which contains similar properties in the same area but which have poor access to public transport. Data was also collected on the level of Availability of such rental properties within the surrounding area, classified as Low, Medium and High availability. The results are given in the sheet labeled Data in the file BUGEN1502_A1_S216.xls. All tables and graphs for this question should be placed in the designated spaces in the Worksheet Results. The first three questions aim to give a picture of the level of availability of rental properties in the areas sampled. 1. Using COUNTIF, construct a frequency distribution for the availability of rental properties. (Complete Table (a)). [8 marks] 2. Provide an appropriate chart showing the percentage of rental properties in each availability category. (Complete Graph(b)). [6 marks] 3. Briefly summarise the information obtained in the above table and graph about the number of rental properties in each category (3 to 4 sentences - Textbox (c)). [4 marks] The remaining questions are based on analysis of the similarities and differences in weekly rent for those properties which are within a fiveminutes walk of public transport (labelled as Near) and properties which do not have easy access to public transport (labelled as Other). 4. Provide a table of summary stats for the weekly rents for each of the Two categories (Near and Other) (Table (d)). Give your answers in this table to two decimal places and make sure you indicate units. [18 marks] 5. Complete the grouped frequency table for the weekly rents for the two categories for the sample (Table (e)). Find frequency and hence calculate percentage frequency for the two categories. [12 marks] 6. Construct percentage frequency polygons of the weekly rents for each of the two categories of rental properties as one chart as Graph (f). [12 marks] 7. Comment on the shapes of the two distributions (Textbox (g)). [6 marks] 8. Based on your answers to questions 4, 5 and 6, discuss and compare the central locations of the distributions for different categories of rental properties (Textbox (h)). [8 marks] 9. List the four measures of variability given in the table. The rents of which category of rental property show more variability? (Textbox (i)). [8 marks] 10. Comment on any particular interesting and unusual features of the weekly rent distributions for the two categories of rental properties. What conclusions do you think the student union should draw about the affordability of student housing which has easily accessible public transport options to the university? (Textbox (j)). [8 marks] Presentation and readability: 10 marks (If your presentation is easy to read, you will get these 10 marks. Ease of reading is assisted by appropriate font size, borders, colour choice and labelling in graphs, and some care in spelling, grammar and punctuation. ) Excel tips Sections 7 and 8 of the Key Information Documents provide technical information about Excel that you may need in completing this assignment. Some extra Excel information is given below. Sorting Data When sorting a number of columns of data by one variable, remember to highlight the whole block of data (all required rows and columns) first. If you just highlight the column for the variable you are sorting by, then the values of this variable will be separated from the cases to which they belong. Paste special When copying and pasting a table, for example into a different worksheet, it is frequently necessary to use the option: EditPaste Special Values. Textbox In order to insert a textbox, first make sure the Insert Toolbar/Ribbon is showing and then click on the textbox icon, , click in one corner of where you want the textbox, and drag to the required size. Percentage frequency polygons When creating the polygons, remember to introduce fictitious empty classes at both ends in the table (rows have been arranged so there is room) so that each polygon touches the X axis at both ends. Use Excel's \"Scatter graph\" option (including connecting lines) to create the polygons. Due to axislabelling issues, it is inappropriate to use Excel's Line Graph Chart type to produce frequency polygons. Choosing which columns of a table to graph The series tab in Step 2 of the Chart Wizard provides a convenient way to specify exactly which data you wish to graph. For example, if you highlight all of a table including frequencies and percentage frequencies, and use XY scatter, Excel will graph frequency polygons and percentage frequency polygons all on the same axes. You can use the series tab to remove the frequency polygons. Near Rental properties Other 202 231 224 152 188 210 211 196 225 210 214 210 261 187 194 186 222 183 191 213 216 222 197 216 222 205 198 223 196 248 214 199 200 187 209 246 212 201 201 184 207 182 226 200 208 213 202 250 222 244 177 198 145 98 175 163 204 160 182 208 145 177 195 186 209 144 172 178 156 201 121 172 188 248 140 243 184 185 170 187 196 199 137 57 198 143 156 211 147 194 217 252 200 225 194 165 196 140 203 207 215 183 105 196 Availability Medium Medium Medium High High Medium Medium High Medium Medium Medium Medium Medium High High High Medium High High Medium Medium Medium High Medium Medium Medium High Medium High Medium Medium High Medium High Medium Medium Medium Medium Medium High Medium High Medium High Medium Medium Medium Medium Medium Medium High High 191 231 231 249 187 228 218 231 193 215 185 178 207 189 206 171 211 222 200 209 188 212 203 176 174 225 216 236 198 155 241 198 236 206 190 242 210 242 176 217 193 230 205 213 192 220 206 225 396 446 408 399 441 418 259 176 228 235 194 220 268 178 199 174 175 228 144 187 202 192 217 210 146 163 153 228 191 154 208 147 162 150 218 210 161 112 168 175 187 243 223 218 181 196 194 227 219 210 186 191 138 147 197 169 222 186 164 228 High Medium Medium Medium High Medium Medium Medium High Medium High High Medium High Medium High Medium Medium Medium Medium High Medium Medium High High Medium Medium Medium High High Medium High Medium Medium High Medium Medium Medium High Medium High Medium Medium Medium High Medium Medium Medium Medium Low Low Medium Low Low 408 359 334 391 354 480 379 439 515 422 414 398 397 422 370 440 425 398 279 401 381 427 428 444 403 418 470 275 462 446 514 403 463 469 369 414 425 360 477 366 409 368 401 410 183 196 165 134 215 195 211 168 198 136 150 157 170 160 226 222 216 140 219 160 191 173 219 183 250 132 96 166 158 162 192 156 192 140 228 145 230 199 150 216 176 185 205 194 Low Medium Medium Medium Medium Low Medium Low Low Low Low Medium Medium Low Medium Low Low Medium Medium Low Medium Low Low Low Low Low Low Medium Low Low Low Low Low Low Medium Low Low Medium Low Medium Low Medium Low Low Name Student ID Table (a) Availability Low Medium High Total Graph (b) Textbox (c) Frequency Percentage Table (d) Summary Statistics Near Count Minimum Maximum Range Mean Std Dev Coefficient of variation Median Other Lower quartile Upper quartile IQR Table (e) (all tables should be formatted) Lower limit Upper Limit Midpoint 10 50 50 90 90 130 130 170 170 210 210 250 250 290 290 330 330 370 370 410 410 450 450 490 490 530 530 570 TOTAL Textbox (g) Frequency Near Other Percentage Frequency Near Other Cummulative Percentage Frequency Near Other Graph (f) Precentage Frequency Polygons Textbox (h) Textbox (i) Textbox (j)