Hi, I need help with a statistics assignment related to excel.

Properties sold in Melboune Property Location 3 1 8 1 28 1 29 1 30 1 31 1 34 1 37 1 45 1 47 1 51 1 54 1 55 1 56 1 57 1 58 1 60 1 61 1 62 1 64 1 65 1 70 1 73 1 74 1 75 1 77 1 78 1 81 1 85 1 87 1 90 1 91 1 94 1 96 1 100 1 102 1 103 1 104 1 105 1 108 1 109 1 114 1 118 1 128 1 4 2 Age Land Size 37 427 21 403 78 484 56 439 50 511 39 508 32 364 43 556 36 349 42 469 25 466 0 382 54 544 67 514 60 514 15 304 45 448 30 391 60 472 28 439 39 439 32 469 17 490 40 493 39 325 44 376 45 499 52 469 44 364 49 442 30 454 26 448 51 472 18 445 57 466 45 415 57 445 26 352 46 532 50 508 21 448 16 433 22 391 37 379 4 445 Bedrooms 2 3 3 4 3 3 3 4 2 2 2 2 3 3 2 2 2 3 3 2 3 3 2 3 2 3 3 3 3 3 3 2 2 3 3 3 2 2 3 3 3 2 2 3 3 Type Unit Unit Unit Unit House House Unit House Unit Unit House Unit House House House Unit Unit Unit Unit Unit Unit Unit Unit House Unit Unit Unit Unit Unit Unit Unit Unit Unit Unit Unit Unit Unit Unit House Unit House Unit Unit Unit Unit Price $333,900 $404,400 $331,500 $368,700 $441,400 $524,500 $330,500 $508,500 $307,900 $414,000 $450,000 $395,400 $434,400 $439,500 $412,800 $389,900 $389,800 $385,200 $370,200 $381,300 $368,100 $407,700 $401,100 $411,900 $332,100 $335,700 $453,000 $402,000 $382,500 $368,400 $449,600 $397,500 $354,000 $414,000 $311,400 $378,900 $389,700 $363,300 $439,200 $386,100 $443,700 $413,100 $405,300 $383,100 $522,600 9 10 14 15 16 17 18 19 21 22 23 24 25 38 39 40 42 44 46 49 50 59 63 67 68 71 79 80 82 84 86 88 89 95 97 98 110 111 115 117 120 121 122 124 1 2 5 6 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 3 3 3 3 43 18 38 4 27 0 40 49 33 36 57 50 37 45 32 30 31 33 16 25 22 44 35 29 35 42 20 64 21 58 21 49 45 44 13 27 14 23 39 50 44 12 0 24 22 45 7 14 466 592 508 337 442 379 388 553 502 412 526 526 499 544 481 601 466 502 481 385 463 406 439 532 526 448 379 547 472 442 451 514 478 517 487 493 436 430 478 592 463 469 391 406 451 544 484 556 3 3 3 2 2 2 3 5 3 3 3 3 3 3 4 3 2 4 3 2 2 3 3 4 3 3 3 3 4 3 2 3 4 4 2 3 3 2 3 3 3 2 3 2 3 4 2 5 Unit House House Unit House Unit Unit House Unit Unit House House Unit House House House Unit House House Unit Unit Unit Unit House House Unit Unit House House Unit Unit House Unit House Unit House Unit Unit Unit House Unit Unit Unit Unit House House Unit House $411,600 $503,700 $460,100 $380,400 $422,100 $394,800 $372,600 $479,400 $420,700 $370,200 $489,800 $501,900 $449,900 $465,600 $516,300 $460,500 $377,400 $418,200 $437,700 $437,700 $408,300 $409,200 $419,700 $529,800 $449,900 $377,400 $358,500 $460,900 $434,100 $307,500 $431,400 $400,100 $403,200 $504,300 $495,000 $531,900 $462,600 $429,600 $458,100 $471,000 $344,700 $441,000 $405,000 $403,500 $560,100 $549,900 $569,000 $679,100 7 11 12 13 20 26 27 32 33 35 36 41 43 48 52 53 66 69 72 76 83 92 93 99 101 106 107 112 113 116 119 123 125 126 127 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 14 57 32 19 37 23 30 5 31 33 6 22 10 46 9 42 4 17 13 24 25 3 27 12 3 38 28 4 22 45 24 36 28 10 56 529 646 601 493 454 535 553 385 511 493 487 502 403 595 475 541 496 445 517 511 544 481 421 460 403 514 445 421 427 469 448 418 517 433 628 3 4 4 3 3 4 4 3 3 4 3 3 4 4 3 4 4 3 4 4 4 4 4 2 4 4 4 3 3 4 3 3 4 3 4 House House House House House House House Unit House House Unit House Unit House House House Unit House House House House House House Unit Unit House Unit House Unit Unit Unit House House House House $603,300 $591,000 $764,200 $573,600 $560,700 $544,500 $590,100 $454,500 $493,200 $557,400 $497,100 $488,400 $498,000 $501,600 $510,300 $517,500 $526,800 $562,200 $533,400 $633,500 $625,500 $624,600 $461,400 $449,800 $491,400 $542,400 $489,900 $474,900 $451,500 $471,600 $450,000 $515,400 $512,700 $578,100 $501,300 Summary Statistics for A Statistics Mean Median Standard Deviation Minimum Maximum Range Count First Quartile Third Quartile Interquartile Range (IQR) Coefficient of Variation (CV) mary Statistics for Amount Spent in Day Time Transactions North East Southeast suburbs suburbs suburbs Excel Function Used for Evening Transactions (including cell references) MCD2080 Business Statistics EXCEL Exercise 1 Descriptive Statistics Presentation Using Excel This assignment is marked out of 99.5 marks worth 5% of the final grade. It is designed to test your understanding of Descriptive Statistics; both Graphical Presentations using Excel and interpretations. Due Date: by 5 pm Sunday (19th July) of Teaching Week 4 DATA AND IMPORTANCE OF THIS EXERCISE The file MCD2080_Exer_T220.xlsx contains data for the Excel Exercise covering week 1 - 3 Lectures and week 1 - 4 Tutorials. This data is in \"Housing Records\" worksheet. The aim of this exercise is to make you get familiar with excel tools and functions which you will require in future studies at the university and even outside world. In addition, this gives you an opportunity to be able to interpret the descriptive statistics you learn during the first few weeks in this unit. This is a very important time to learn these basic Excel skills. Data Background: Real Agent Property Sales Melbourne property market continues to deliver solid price growth in recent years. The worksheet \"Housing Records\" in MCD2080_Exer_T220.xlsx contains data collected by a Real Estate agent on the 128 properties sold in 2016 for three locations. The Real Estate agent wants to analyse the data to gain a better understanding for Melbourne property market which will guide the property price prediction. The data for each property includes the following variables: Property The property ID. Location property location (1 = north suburbs, 2 = east suburbs, 3 = southeast suburbs.) Age The age (in years) of the housing on the property. Land Size The land size of the property (m2). Bedrooms The number of bedrooms. Type The type of property (House or Unit). Price The sold price ($). OUTPUT AND RESULTS You are required to use Excel to generate the output. You are required to create your charts and tables in the \"Results (output)\" worksheet. To produce the descriptive (summary) statistics, use the excel functions you have learnt during the tutorials (or Lecture). However, it is also recommended to understand how to use Excel's Data Analysis Tools to produce summary statistics. Make sure to save and organise your excel outputs (tables and the charts) in order to answer the questions on Moodle. Also, keep your work safe until you get final grading and feedback. Make sure you organise your tables and the charts well, otherwise poor presentation will attract loss of marks. 1 HOW TO ANSWER QUESTIONS AND UPLOADING TABLES AND CHARTS TO QUESTIONS Use the link under Week 2 in Tutorial section labelled \"Excel Exercise\" and follow the instruction. There are a set of questions which you should answer all before submitting. There are 11 questions in this exercise. Question 1: requires you to provide correct answer out of the choices provided. Use the data set provided (MCD2080_Exer_T220.xlsx) to answer the rest of the questions Question 2: requires you to upload (pdf) ONLY \"the Table 2(a), Table 2(b) and Table 2(c)\" separately or together. If you upload the whole document, you will lose marks. Hint: copy these Tables to word doc, then organise them carefully and then save as pdf. Question 3: Requires you to upload (pdf) ONLY \"the Graph 3(a), Graph 3(b) and Graph 3(c)\" separately or together. If you upload the whole document, you will lose marks. Hint: copy these graphs to word doc, then organise them carefully and then save as pdf. Question 4: Requires you to upload (pdf) ONLY \"the Table 4\". If you upload the whole document, you will lose marks. Hint: copy these graphs to word doc, then organise them carefully and then save as pdf. Question 5: Requires you to interpret the data distribution. These are short answers where you chose the correct \"word(s)\". Question 6: Requires you to interpret the data distribution. These are long answers you type in the boxes provided. Question 7a: Requires you to interpret the data distribution. These are short answers where you chose the correct \"word(s)\". Question 7b: Requires you to interpret the data distribution. These are long answers you type in the boxes provided. Question 8: requires you to construct a pivot table which you will upload on Moodle question 8 (pdf). If you upload the whole document, you will lose marks. Hint: copy the Table to word doc, then organise them carefully and then save as pdf. Question 9: Requires you to interpret the pivot table. These are short answers where you chose the correct \"word(s)\". Question 10: requires you to construct a graph which you will upload on Moodle question (pdf is preferred). If you upload the whole document, you will lose marks. Hint: copy the chart to word doc, then organise them carefully and then save as pdf. Question 11: Requires you to describe the bar chart in Q10. These are short answers where you chose the correct \"word(s)\" 2 You can start answering the questions on Moodle BUT DO NOT SUBMIT the exercise UNTIL you answer ALL the question to your best level. You can go to any question AT ANY TIME using \"NEXT\" and \"PREVIOUS\" buttons. Caution: When you Click SUBMIT the Exercise (QUIZ), you will not be able to change any question that will be final. To be able to score high marks, o you need to read all the instructions and questions carefully o you need to start working on your excel exercise immediately (Monday of 29th June, week 4) and remember the due date o Watch excel Videos to help you do the exercises. If you do not understand how to do any part of the exercise, you are encouraged to discuss with your classmates and/or consult your teacher. o Check this website for Pivot Table construction and chart: https://edu.gcfglobal.org/en/excel2016/ Sections 27 and 28 Questions 1. Explain the data type for each of the following five variables below. If the variable is categorical, also explain your reasoning whether it is nominal or ordinal. If the variable is numerical, also explain your reasoning whether it is discrete or continuous. o Location, o Type, o Bedrooms, o Land Size o Price. [5 marks] 2. To understand the insight of this data, in the Housing Records worksheet, we wish to separate the Price ($) values by the location of the property. First, describe the distribution and pattern(s) of the property prices using pivot tables. Give the frequencies in percentages. To achieve this: o Sort the data by variable \"Location\". In the Results (Output) worksheet, in cell A1 type the heading North suburbs; in cell C1, East suburbs and in cell E1, Southeast suburbs. o Copy/paste the relevant data in columns A, C and E under these headings respectively. o o Use Excel's Pivot Table function to create the percentage frequency distribution tables. To do that, the class intervals (groups) should start at $300,000 for North suburbs with class width a of $25,000; start at $300,000 for East suburbs with a class width of $30,000 and start at $430,000 for Southeast suburbs with a class width of $40,000. o Show percentages correct to 2 decimal places. o o Use the Tabular Format report layout and label your tables accurately and informatively. 3 Label them Table 2(a), Table 2(b) and Table 2(c) for the North suburbs, East suburbs and Southeast suburbs property prices respectively. NOTE: Keep your work for future until you receive your final grades for this exercise. [9 marks] 3. Use the tables from question 2 to construct percentage frequency histograms and label them Graph 3 (a), Graph 3 (b) and Graph 3 (c)) for the North suburbs, East suburbs and Southeast suburbs property prices respectively. Hide/remove all field buttons from the graphs and ensure that the histograms are appropriately labelled. (Marks will be deducted for poor presentation of your tables or/and graphs) 4. [15 marks] Obtain the indicated summary measures in the following table (Table 4), for the North suburbs, East suburbs and Southeast suburbs property prices Excel Function used for \"Southeast suburbs property prices\" Report values to 2 decimal places. [15 marks] Table 4. Summary Statistics for Property Prices ($) Statistics North suburbs East suburbs Southeast suburbs Excel Function Used for Southeast suburbs property prices (including cell references) Mean Median Standard Deviation Minimum Maximum Range Count First Quartile Third Quartile Interquartile Range (IQR) Coefficient of Variation (CV) 5. 6. Refer to Questions 2, 3 & 4 results to describe the shape of the Southeast suburbs property price distribution. [9 marks] Refer to your Questions 2, 3 & 4 results above to answer the following question(s)/statement(s). (a) Interpret in context each value of central tendency measures in question 4 and 2 & 3, which can be used when reporting the \"average\" price, for Southeast suburbs property. (b) In her studies, a researcher reported that Southeast suburbs property prices were higher than any other suburbs. Do your results from Question 4 support this researcher's claim? Justify your answer with evidence. (c) Which is the \"best\" central tendency measure to use when reporting the \"average\" prices for North suburbs and East suburbs properties? Give the reasons why this is the best 7. [7.5 marks] Refer to your results in question 4 above to answer the following. (a) (i). List all the measures of variations and the values obtained in question 4 for Southeast suburbs property price only (ii) Interpret in context each value of variation measures obtained in part (a)(i) above. 4 (b) (i). Based on the above variability measures, which location (North suburbs, East suburbs, Southeast suburbs) has the greater variability of property prices? Briefly explain. (ii). Comment on the \"best\" variability measure to use in this case. Give the reason(s). [11.5 marks] 8. Using the original data, use Excel's Pivot Table function to create a table of the frequency distribution of the Land Size (ROWS) by Number of bedrooms (COLUMNS). Group Land Size variable into five groups: starting at 300 and ending at 650 with a class width of 70 square meters. Use the \"Tabular Form\" report layout. (Hint: Make sure your table values are \"Count and NOT Sum) From this table create and label the following three tables, rounding off percentage values to 2 decimal places: (a) Table 8a: the frequency distribution with values shown as \"% of Grand Total\". (b) Table 8b: the frequency distribution with values shown as \"% of Column Total\". (c) Table 8c: the frequency distribution with values shown as \"% of Row Total\". [7.5 marks] 9. Refer to the Tables 8a - c in Question 8 to answer the following: a). (i). Which property with the least proportion of bedrooms and what is the proportion? (ii). What is the proportion of property with second largest land size and the second largest number of bedrooms? b). (i). If a property is randomly selected from the least number of bedrooms, what is the probability that it has the smallest land size? (ii). What is the probability of a randomly selected property with middle sized land given it has the second largest number of bedrooms? c). (i). For the second largest land size, which propertyumber of bedrooms has the second lowest proportion, and what is the value? (ii). Given the second smallest land size, what is the highest proportion among the number of bedrooms? [8.0 marks] 10. Next Construct a percentage column chart of Land Size (ROWS) by Number of bedrooms (along the horizontal Axis) in relation to Land Size (Give values as % of column) Ensure that the chart is well-presented with title and axis labels. [7.0 marks] 11. Provide a brief description of the column chart in question 10. Hint: Give an introduction (one or two sentences) Give an overview focusing on the key features (highs, lows, differences/similarities/comparisons). This should be about 3 - 4 sentences. Give supporting evidence (values from the chart). [5.0 marks] Select the correct option (given in red) The chart illustrates the (percentage/land occupants/bedroom occupants/location of the property sizes) by the Land Sizes. /1 The chart depicts that the percentages of the property with 2 and 3 bedrooms increases with land size up to size between (440-509/370-439/580-650) square meters with values of (50% & 47.76%/43.75%&50%/43.47&47.76%/50%+47.76% = 97.76%) while with 4 bedrooms is up to (510579/580-650/440-509) with a value of (41.38%/31.03%/100%). /2 Most of the property with 5 bedrooms occupy (580-650/510-650/510-579/440-509) square meters with a value of (21.09%/100%/4.48%+13.80 = 18.28%). /1 Overall, the property with few bedrooms tend to occupy (smaller land/all the land/any land/larger 5 land) sizes while the property with many bedrooms occupy (smaller land/all the land/any land/larger land) sizes /1 6 Excel Tips The Excel Notes document on Moodle also has information that you may need for this assignment. Sorting Data When sorting several of columns of data by one variable, first highlight the whole block of data (all rows and columns of the data set). If you highlight just the column for the variable by which you are sorting, then the values of this variable will be separated from the cases to which they belong. Percentage frequency histograms Use Excel's \"Column\" Chart Type. To remove the gaps between the columns of a histogram, right-click on any column and choose \"Format Data Series\". reduce the \"Gap width\" to \"No Gap\". Data Analysis Tools If Data Analysis does not appear in the \"Data\" menu, consult Section E5 of the Excel Notes. Word Tips To copy and paste your tables or charts from excel to word, just \"copy: use Ctrl + C\" from excel and then \"paste: use Ctrl + V\" in the word document. Then do customisation/edtting to suit your style OR Capturing a screen image from Excel and \"Paste\" it into Word: To capture the image (eg, Excel page, or even from the internet page, photo, etc.) currently displayed on your screen, use Windows' Snipping Tool (available via the START button/All Programs/Accessories). go to the desired location in your Word document. Hold the Ctrl key and press the letter \"V\". Then after pasting the image, you will need to trim (\"Crop\") an image in Word. To do that, Click on the image. From the 'ribbon' at the top of the screen, select Format/Size and click on the \"Crop\" icon: Apply the \"Crop\" icon to the image. To arrange graphs and pictures within a \"Word\" document Graphs (eg, from Excel) and pictures can be more manageable if you insert a table into Word and place your graphs and pictures (and text) into cells of the table. 7