This is a Data Analysis assignment where in the assignment has two parts - one the excel sheet and the second part is a business report. The question to be answered (PDF file) and the excel sheet to be used are attached to this query.
MIS770, T3 2019 Assignment Two 1 | P a g e Assignment 2 Particulars Due: Week 9, 11:59pm, Thursday 23rd January, 2020 Marks: 30% Words: 2000 Submission: Online to the MIS770 assignment TWO drop box in the Cloud Unit site Email submissions will not be accepted Note: This assignment is to be completed individually Assurance of Learning This assignment assesses following Graduate Learning Outcomes and related Unit Learning Outcomes: Graduate Learning Outcome (GLO) Unit Learning Outcome (ULO) GLO4: Critical thinking: evaluating information using critical and analytical thinking and judgment ULO2: Manipulate and summarise data that accurately represents real world problems ULO3: Interpret and appraise statistical output to assist in real‐world decision making Overview The purpose of this assignment is to investigate a dataset utilising the knowledge learned in Modules One and Two. This will enable conclusions to be drawn that ultimately assist in decision making. The assignment requires you to analyse a given dataset, interpret the results, and then draw conclusions such that you are able to reply to specific questions being asked of you in the form of a report. (These questions are asked in the following memorandum). The aims of the assignment is to: • provide you with some examples of the application of data analysis within an organisation • test your understanding of the material in the relevant topics • test your ability to analyse and interpret your results • test your ability to effectively communicate the results of your analysis to others Before tackling the assignment, make sure you have prepared yourself well. As a minimum, please read the relevant sections of the prescribed text and listen/watch the pre‐recorded material for Modules 1 and 2. FACULTY OF BUSINESS AND LAW Department of Information Systems and Business Analytics MIS770 – Foundation Skills in Data Analysis MIS770, T3 2019 Assignment Two 2 | P a g e Scenario Note: All data, reports, people and scenarios in this assignments are either fictitious or have been modified from their original state. You play the role of Emma Thomson, a data analyst in the Research and Analysis Group at the Real Estate Institute of Victoria (REIV). You are often required to report outcomes of your analysis to senior managers at the Institute who have little or no knowledge of data analysis. Of specific interest to the REIV is the increasing house prices in Melbourne and potentially what this will mean for buyers yet to enter the market. A recent analysis, which received publicity in the media, showed that there were more Melbourne suburbs with a median house price of over $1 million, with this trend only set to continue as evidenced from recent auction and private sales figures (refer to the documents million dollar suburbs.pdf and buyers smash records.pdf – sourced from the Melbourne’s Herald Sun). Just recently you have been working on a pilot survey from the City of Kingfisherbay concerning house prices, rental costs and returns, as well as other related data. In light of the above, the REIV is very interested to see where the City of Kingfisherbay, and more specifically its suburbs are at when it comes to house prices and affordability. Paul Anderson, Director of the Housing Affordability Division at the Real Estate Institute has written to you regarding the Kingfisherbay housing survey. His memo to you is reproduced below: Memorandum Memorandum To: Emma Thomson From: Paul Anderson Subject: Analysis of Kingfisherbay’s housing and rental data Dear Emma, You will have seen the recent media articles about how Melbourne’s house prices are increasing and how more Melbourne suburbs now have a median house price of over $1 million. I am concerned about the direct effect this will have on housing affordability in Melbourne, especially for buyers yet to enter the market. I know that you recently undertook a survey of house sales in the City of Kingfisherbay. Could you please provide me with answers to the following questions: 1. An overall summary of the house prices in Kingfisherbay? In addition, the media articles focus on median house prices and not the mean. I have never been able to understand why this is so; surely the “average” is the “average”. Can you clarify that for me? 2. Does there appear to be any difference in the house prices between the suburbs in Kingfisherbay? 3. Can you supply me with a brief summary on the condition of houses in Kingfisherbay? In addition, can you further analyse to see whether there appears to be any differences among the suburbs in terms of condition? 4. Is house price related to any of the variables, namely: Rooms, Land Size, House Area and Weekly rent that you have collected in the sample? A short description of the relationship would be fine. Is there any one which stands out as having a stronger influence on house prices? Which of these variables has the least influence on house prices? MIS770, T3 2019 Assignment Two 3 | P a g e I realise that the survey relates to a random sample of 120 houses sold in the (fictitious) Melbourne City of Kingfisherbay, and that this information can be used to draw inferences about all houses in the City of Kingfisherbay. With that in mind, I hope you are able to provide me with answers to the following questions: 5. The newspaper article “Buyers smash records” mentions how new land is in high demand and the ridiculous situation of buyers having to camp out for days to purchase land. I’m interested in whether there is the opportunity to make more land available in areas such as Kingfisherbay by subdividing. Generally if a land size is at least 1000m2 or more then it can be subdivided. Can you work out what percentage of all houses in Kingfisherbay are suitable for subdivision? Also, what is the estimated average land size across all of Kingfisherbay? 6. As well as housing affordability, the REIV is also concerned about the lack of rental properties available right across Melbourne including areas such as Kingfisherbay. At a recent meeting it was claimed by a senior manager that the proportion of rental properties that were vacant had dropped below 25%. Furthermore, it was recently reported that the average selling price for bay side houses, that is, for all cities and suburbs located around the Bay in Melbourne, had now exceeded $825,000. Can you check whether these two claims are correct for all of Kingfisherbay? 7. For next year’s study, we would like to be able estimate the true proportion of houses of land size 1000m2 or more to within 2% and the average house price to within $20,000 with a high level of confidence How many houses would be needed to be included in next year’s survey to achieve these two requirements? Finally, assuming we take a big enough sample from Kingfisherbay next year, would it be possible to generalise the results to all of Melbourne? This will save us a lot of work duplicating the study in other suburbs. I look forward to your responses. Sincerely, Paul Anderson Requirements: • Your report should be no longer than 2000 words and there is no need to include, Charts and Tables, or Appendices in the report • Your Charts/Graphics and Tables are only to be placed in the Data Analysis file i.e. the Excel spreadsheet • The report is to be written as a stand‐alone document (assume Paul will only read your report). Thus, you should not have any references in the report to your data analysis output. Eg. “According to Table 1 in the analysis…” • Your report must have an informative title • Your report must contain an executive summary that explains in plain language what the report is for and summarises the main findings. The executive summary should be no more than a page • The body of your report must be set out in the same order as in the originating memorandum from Paul Anderson, with each section (question) clearly marked • Use plain language and your explanations succinct. Avoid the use of technical or statistical jargon as Paul Anderson will not necessarily understand statistical terms. As a guide to the meaning of “Plain Language”, imagine you are explaining your findings to a person without any statistical training (e.g. someone who has not studied this unit). What type of language would you use in this case? • Marks will be lost if you use unexplained technical terms, irrelevant material, or have poor presentation/ organization • All Microsoft Excel data analysis output associated with each question in the Memorandum is to be placed in the corresponding tab in the yourstudentid.xlsx file MIS770, T3 2019 Assignment Two 4 | P a g e Data Analysis Instructions/Guidelines In order to prepare a reply to Paul’s memorandum, you will need to examine and analyse the dataset KingfisherbayData.xlsx thoroughly. Paul has asked a number of questions and your Data Analysis output (i.e. your charts/tables/graphs) should be structured such that each question is answered on the separate tab/worksheet provided in your Excel document. There are also extra tabs in KingfisherbayData.xlsx called CI, SampleSize and HT and you should use the various templates contained in these tabs in your “CI_Mean/CI_Proportion”, “SampleSize” and “HT_Mean/HT_Proportion” answers. In order to effectively answer the questions, your Data Analysis output needs to be appropriate. Accordingly, you’ll need to establish which of the following techniques are applicable for each question: • Numerical Summary Measures (Inc. Outlier detection) • Suitable tables and charts or graphics (Module One) that will illustrate more clearly, other important features of a variable Numerical variables can be converted to categorical variables • Comparative Summary Measures, Scatter diagrams, Correlation analysis and Cross Tabulations (sometimes called Contingency Tables), used to establish the relationships (dependencies) between two variables • Confidence Intervals: You can assume that a 95% confidence level is appropriate. We use Confidence Intervals when we have no