FACULTY OF BUSINESS AND LAW

School of Information and Business Analytics

MIS770 – Analytical Skills for Managers

Assignment 2 – Kingfisherbay Housing survey

Particulars:
• Due: Week 9, 11:59 pm, Friday 18th September, 2015
• Marks: 20%
• Submission: Online, to your individual/group assignment dropbox in CloudDeakin
• This assignment may be completed individually

Assignment Overview
The purpose of this assignment is to utilise the knowledge learned in Module Two to
investigate a dataset that will enable you to draw conclusions from the data and assist you in
your decision making. You will also draw on information/techniques covered in Module One.
The assignment requires you to analyse a given dataset, interpret them, and then draw
conclusions such that you are able to reply to specific questions being asked of you.
(These questions are asked in the attached memorandum.)

The data relate to a random sample of 120 houses sold in the (fictitious) Melbourne City of
Kingfisherbay and are to be used to assess the underlying factors associated with variation in house
prices, rental costs and returns. Note: All data, reports, people and scenarios in this assignments
are either fictitious or have been modified from their original state.
The assignment may be completed individually.

The aims of the assignments are to:

• provide you with some examples of the application of data analysis and statistics within
an organisation,
• test your understanding of the material in the relevant topics,
• enable you to demonstrate your ability to use Excel to analyse statistical data,
• test your ability to analyse and interpret your results, and
• test your ability to effectively communicate the results of your analysis to others.

Before tackling an assignment, make sure you have prepared yourself well. As a minimum, read
the relevant sections of the prescribed text.

MIS770 Analytical Skills for Managers Assignment 2 Page 1

Scenario

You play the role of Kim Williams who is the Manager of the Research and Analysis section at the Real
Estate Institute of Victoria (REIV). Your role includes analysing a variety of housing data including house
prices, affordability, investment returns, rental availability and rental returns. 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. Kerry Tan, Director of the
Housing Affordability Division at the Real Estate Institute has written to you regarding the
Kingfisherbay housing survey. Her memo to you is reproduced below:

MIS770 Analytical Skills for Managers Assignment 2 Page 2

 Memorandum
To: Kim Williams – Manager, Research and Analysis, Real Estate Institute

From: Kerry Tan, Director, Housing Affordability Division, Real Estate Institute

Subject: Analysis of Kingfisherbay’s housing and rental data

Dear Kim,

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.
I would be very interested to find out what the housing prices are like in the City of Kingfisherbay and
more specifically within its suburbs.

Could you please provide me with answers to the following questions.

1. An overall view of house prices

(a) Can you provide an overall summary of house prices in Kingfisherbay? A summing up of
the variable just by itself would be useful.
(b) 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 provide a straight
forward answer for me?
(c) Based on the article “Price bubble expands our $1 million suburbs”, would the average house
price in the City of Kingfisherbay fall between any of the Melbourne suburbs listed in the article? If
so, between which two suburbs?

2. House Price vs Suburb

Does there appear to be any difference in the house prices between the suburbs in Kingfisherbay?

3. House Prices vs Condition

(a) Can you supply me with a brief summary on the condition of houses in Kingfisherbay?
(b) Does there appear to be any difference in house prices by condition in Kingfisherbay?
(c) Does there appear to be any differences among the suburbs in terms of condition?

4. Average Price by Rental status

(a) Can you provide an estimate of the average selling price for all houses in Kingfisherbay?
(b) Also, can you work out the estimates of the average house selling price for each of the three
categories for rental availability?
(c) On the basis of your findings, does there appear to be any difference in selling price based on
rental availability?

MIS770 Analytical Skills for Managers Assignment 2 Page 3

5. Estimating the owner occupied rate
(a) What is the estimated proportion of all Kingfisherbay houses that are owner occupied?
(b) I am concerned that our sample of 120 is too small for estimating the owner occupied rate. If I
wanted to be very confident of estimating the true proportion in a city like Kingfisherbay to within
3%, what sized sample would we need?
(c) Also, 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.

6. Comparisons with other Bay-side locations

It was recently reported that the average (using the arithmetic mean) selling price for bay side
houses, that is, for all cities and suburbs located around the Bay in Melbourne, had reached $760,000.

It is generally claimed that Kingfisherbay is more expensive than other bay side
areas. Can you check if this claim is true for Kingfisherbay overall?
Also, can you please check this claim for each of the three suburbs in Kingfisherbay?

I look forward to your reply.

Signed, Kerry Tan

MIS770 Analytical Skills for Managers Assignment 2 Page 4

Requirements and Guidelines: Part One - Data Analysis
In order to prepare a reply to Kerry’s memorandum, you will need to examine and analyse the dataset
Kingfisherbay_2015.xls thoroughly. Ensure you use the correct variables from the database.
The following guidelines for each question should be followed:
Q1. An overall view of house prices
For question 1a, you should only analyse house price by itself. The importance of other
variables is considered in other questions. You should, at the very least, thoroughly investigate
relevant summary measures (Descriptive Statistics) for this variable. Also, there may well be
suitable tables and graphs that will illustrate, further and more clearly, other important
features of house price.
For question 1b and 1c, ensure you check the meaning of the word “average” and ,
include relevant values from the analysis you have carried out in 1a. There is no extra
analysis that needs to be done for these questions.
Q2. House Price vs Suburb
Your notes and textbook give methods for investigating the relationships (dependencies)
between two variables for these situations. You should use Comparative Summary Measures
and/or Cross Tabulations (sometimes called Contingency Tables) as well as appropriate chart/s,
tables/s and/or graph/s.
Q3. House Prices vs Condition
Your notes and textbook give methods for investigating the relationships (dependencies)
between two variables for these situations. You should use Comparative Summary Measures
and/or Cross Tabulations (sometimes called Contingency Tables) as well as appropriate chart/s,
tables/s and/or graph/s.
Q4. Average Price by Rental Status
For question 4a and 4b, you need to complete Confidence Intervals. Use the relevant Excel
templates provided in the Dataset file. You can assume that a 95% confidence level is
appropriate. For question 4c, no additional Data Analysis output is required.
Q5. Estimating the owner occupied rate
For question 5a, you need to complete Confidence Intervals. You can assume that a 95%
confidence level is appropriate. Use the relevant Excel templates provided in the Dataset file.
For question 5b, you need to estimate the required sample size assuming 95% confidence, but
include comparisons for 90% and 99% and a recommendation for the appropriate sample size.
For question 5c, no additional Data Analysis output is required.
Q6. Comparisons with other Bay-side locations
This question will require you to perform Hypothesis test. Use the relevant Excel
templates provided in the Dataset file. Use 5% significance in any tests you perform, and
provide a summary of your conclusions. Where appropriate, make comparisons with
other levels of significance (2%, 1%).

MIS770 Analytical Skills for Managers Assignment 2 Page 5

Data Analysis instructions:
• Please ensure you analyse the data thoroughly but do not go beyond what the question asks
– for example, if one question requires a location by location comparison, it does not mean
you must do it for any other question, unless specifically asked to do so. There is a tendency
to treat a question superficially, when in fact a much more thorough and in-depth analysis is
required.
• Overall you will generate a great deal of output for this assignment, but you should trim it
down to only show the most relevant results in your maximum of 10 pages. Superfluous
output will be penalised, so ensure you only include relevant materials (graphs, tables,
calculations, tests, etc.) that are essential for writing up your memorandum.
• Your output must be set out in the same order as set out in the originating memorandum
of the assignment. And ensure each section is clearly marked so we know to which part of
the memorandum it relates. This will facilitate easier marking for your assessor, and
ensure no material you have submitted is overlooked.
• In your output you may also include, as annotations, appropriate comments in either plain
or technical language. But note that these will be assumed to be your personal working
comments, and will not be read by the marker as part of your submission.
• To answer some questions you may need to make certain assumptions about the data set
we are using. Mention these in your data analysis, where relevant. There is no need to
mention this in the memo.
• We assume you will be using Excel to perform you data analysis.
• Detailed algebraic responses are not expected. Thus, avoid including extensive derivations,
formulae, etc. You are to use the computer where possible to complete your answers.

Requirements and Guidelines: Part Two - Memorandum

• Complete your response Memorandum while completing each stage of your Data Analysis,
or after completing the whole analysis. The memorandum must be based on the data
analysis carried out as instructed above.
• You are required to reply to Kerry by memorandum, detailing essential information and
conclusions from your Data Analysis.
• You are allowed no more than two pages to convey your written conclusions.
• Keep the language simple and the explanations succinct. Avoid the use of technical or
statistical jargon. Your reader will not necessarily understand even simple statistical terms,
thus your task is to convert your analysis into plain, understandable language.
• The memorandum is to be written as a stand-alone document i.e. Kerry must be able to read
and understand the Memorandum, without ever needing to see the data analysis. Thus, you
should not have any references in the memo to Part 1 of the assignment.
• When composing your reply, make sure that you actually answer the questions raised in
Kerry’s memorandum. Do not simply include the computer analysis completed in Part 1.
• Number your answers 1, 2, etc. to match Kerry’s memorandum. And ensure each section is
clearly marked. This will facilitate easier marking for your assessor, and ensure no material
you have submitted is overlooked.
• Include an introduction at the start of the memo and a summary/conclusion at the end.
• Marks will be deducted for the use of technical terms, irrelevant material, poor
presentation/organisation and memos over two pages long.

MIS770 Analytical Skills for Managers Assignment 2 Page 6

General guidelines:
• Remember to save your work often as you proceed keeping appropriate backups.
• If you do work with a group, one member of the group must upload the Data Analysis and
Memorandum on behalf of all group members. All members will receive the same mark
for those uploads.

Submission
Your submission will comprise of ONE document (a Microsoft WORD file). If you submit
multiple documents, you will be penalised. To get the Microsoft Excel output into Microsoft
Word, you need to copy your relevant analysis output and paste it with the paste special
option (paste as a picture).

Your submission document should contain the following information in exactly the
order indicated:
1) the written memorandum, comprising of no more than two pages, then after this, in
a section titled APPENDIX
2) your analysis (output that you have copied into your Word document from
Microsoft Excel), which will be no more than 10 pages of output.

The assignment is to be submitted to CloudDeakin before 11:59 pm on Friday 18 th

September, 2015. Please name the assignment using the format yourstudentid.docx. Groups
of more than 1 student should use the format yourstudentid_yourstudentid.docx etc.
Note: CloudDeakin is the only method of submission acceptable.
If you need to request an extension, please email the Unit Chair ([email protected]) by
no later than Wednesday 15th September, 2015. You will need to provide a valid reason together with
supporting documentation (e.g. doctors’ certificate, etc.) as well as the work completed to date.

Materials

The following materials are available for download from CloudDeakin:

• Kingfisherbay_2015.xlsx
• Million_Dollar_Suburbs.pdf
• Buyers_Smash_Records.pdf

MIS770 Analytical Skills for Managers Assignment 2 Page 7

Marking Rubric

Poor Below Average Acceptable Well Done Excellent

Data Analysis
(Marks: 9) Does not use any Uses irrelevant or Uses appropriate Comprehensive Skilful and
appropriate inappropriate data analysis and analysis of the data comprehensive
exploratory data techniques to visualisation tools using appropriate analysis of data
analysis tools. analyse the data to analyse the data techniques but using many
and/or there are but there are some there are minor different
many errors in the errors in the errors in the techniques.
analysis. analysis. analysis.

Uses data Uses data

visualisations to visualisations to
understand the produce novel
patterns in data. insights.

0 – < 4.5 Marks 4.5 – < 5.4 Marks 5.4 – < 6.3 Marks 6.3 – < 7.2 Marks 7.2 – 9.0 Marks
Memorandum
(Marks: 9) Does not Interpretation and Explains most of Provides detailed Provides an
communicate any communication of the main findings and accurate outstanding
of the main findings is at a of the analysis descriptions of the descriptions and
findings of the basic level or does accurately and most important conclusions that is
analysis in an not adequately enables reader to features of the carefully
accurate or useful explain the main draw some analysis along with considered and
way. findings of the reasonable appropriately insightful.
analysis. conclusions. qualified
conclusions.

0 – < 4.5 Marks 4.5 – < 5.4 Marks 5.4 – < 6.3 Marks 6.3 – < 7.2 Marks 7.2 – 9.0 Marks
Overall Assignment
Presentation A very poor The analysis is The presentation of The analysis is well An exemplary
(Marks: 2) presentation of the poorly presented the analysis is organised and presentation of the
analysis. or mostly satisfactory. follows principles analysis.
disorganised. of good graphical
display.

The memorandum The memorandum The memorandum The memorandum The memorandum
is un-professional, is difficult to follow is clear and easy to is professional, is truly
difficult to follow and/or contains follow but it easy to follow with professional, logical
and/or contains some spelling or contains minor good structure and easy to follow
numerous spelling grammatical errors spelling or
or grammatical grammatical errors
errors

0 – 0.9 Marks 1.0 – 1.19 Marks 1.2 – 1.39 Marks 1.4 – 1.59 Marks 1.6 – 2.0 Marks

MIS770 Analytical Skills for Managers Assignment 2 Page 8