Investment Analysis for Pittwater LGA

QUESTION

Background

You are working for a financial advisory company that specialises in real estate in Sydney and surrounding areas. A typical client of your company is an everyday person looking for professional investment advice identifying an investment property, and seeks to hold the property for an extended period of time (say at least 5 years).
There is regular debate among senior managers about the best places to recommend to clients. As an analyst for several managers occasionally you are asked to gather evidence to suit different agendas. Note that for simplicity, we will ignore the specific size of a dwelling (including factors such as number of bedrooms and bathrooms), and you are only required to work with the median data (see Resources).

For the purposes of this assignment, we will also ignore data on rent and only look at housing prices (in more technical language, we will only look at “capital gain”).

Resource

For this assignment, you will need 3 data sets:

1. Sales, Trend March 1991 – March 2017, Metropolitan LGAs which shows historic median home prices in a number of LGA’s.
2. Sales tables June 2021 quarter which shows median housing sales data for the Apr 2021 to Jun 2021 period.
3. Table 5 or Table 9 of the Consumer Price Index data , click on “Data downloads” on the left and then chose TABLE 5, specifically the CPI data for Housing.

For your own interest, you may want to access other publicly available data (e.g. ABS demographics) from the QuickStats website at the Australian Bureau of Statistics. In practice, additional data like this can provide more context and insight on demographics and other relevant factors. Note that some census data may be out-of-date. However, for the purposes of this assignment, we will only use the three data sets listed above.

Cautions

Data presented in these Excel files are in their raw form. You will note that there are some redundant or additional columns, and there are missing values for those cases where data were not available, or where the number of transactions was too few to give a meaningful median measure. You will need to clean up the data before you begin your analysis. For example:

• Remove the redundant or useless columns or rows;
• Well handle the missing values, there are a variety of ways for handling missing values, like deleting the corresponding rows (data samples) which contain missing values, or filling in the missing values using the mean values from other rows in that column. You can choose any way appropriate;
• Delete those very infrequent transactions;
• …

Your Task

You will each be allocated one Local Government Area (LGA).
As mentined above, the data sets will require you to clean them before you begin your analysis.

The dollar amounts in the first 2 data sets are not adjusted for inflation. You will first need to use the CPI values in the third data set to adjust the dollar amounts in the first 2 spreadsheets. If you are unsure about how to adjust for inflation using CPI, there are many resources available online. For example, see.

Please choose the CPI value of the second quarter (indicated by Jun-12, Jun-13, …)  of each year in the Consumer Price Index table as the CPI of that year. And put them into the formula in the video for calculating price adjustment.

Once you have cleaned the data for your assigned LGA and adjusted for inflation, you will need to run analysis to answer the following questions:

1. Without using the second data set (i.e. using only data from 2017 or earlier), what evidence is there to suggest your LGA a good place to invest?
2. Without using the second data set (i.e. using only data from 2017 or earlier), what evidence is there to suggest your LGA a bad place to invest?
3. Based on your findings using only the data from 2017 or earlier, would you recommend this LGA to a prospective client?
4. Imagine that a client has followed your recommendation from Q3. Using the 2021 data (i.e. the second data set), reflect on your response to Q3. This may include whether the patterns you noticed using the pre-2018 data continued into 2021, comments on whether you believe you made the right decision given the 2021 outcome, and the impact of your recommendation on the client.

As part of your analysis, you may wish to consider strata and non-strata dwellings separately, as well as overall dwellings (i.e. the combined performance of strata and non-strata dwellings).
What you need to submit
Your submission will need to be a single Excel document, containing the folllowing:

1. A report addressing why your LGA is a good place to invest. Maximum 100 words of explanatory notes (not counting cover sheet, tables or graphs).
2. A report addressing why your LGA is a bad place to invest.
3. Your overall recommendation for a prospective client.
4. Reflection using 2021 data.

Note: Your graph or graphs should tell your story – explanatory notes are only to help the reader understand how to read the graph, if needed, and to point out what the reader should learn from looking at the graph(s).

Altogether, there should be 5 worksheets:

• The first sheet will be a cover sheet, with your name, student id, and allocated LGA, and also lists the contents of the spreadsheets in your document. (The first spreadsheet is not included in the word-count.)
• Clearly identify the other 4 spreadsheets corresponding to each of the 4 tasks listed above.

Additional information
Helpful (maybe) notes:

1. This assignment is a simplified real-world open-end problem, and there are no absolutely right/wrong formats and/or answers. With that being said, some formats/answers are more appropriate than others in reality. Please read my iLearn posts “suggestions and learning tips” and “assignment 1 information” in the announcement regarding the purpose of the assessments.
2. In any case, you should consider the aim and demand of the audience of your task. As a rule of thumb, you need to deliver essential, clear and concise information to help the audience make the business decision. It may be helpful to ask yourself this question: If you were looking for real estate investment advice, what kind of information would you like to have? You may also find the marking guide useful for this purpose.
3. Recall that you are tested for both the analytical skills (translate the decision of property purchasing to a sequence of statistical tasks, i.e. figures/tables to report and metrics based on which the decision is to be made) and application of Excel functions (use the appropriate functions to address those statistical tasks). The first point may be reflected by your chosen statistics to argue the pros/cons of your LGA, as well as your explanatory notes to justify your choices and conclusions. The second point could be inferred from the reported figures/tables (including their formats/styles) and the Excel formulas.
4. Please only include the most relevant outputs (i.e. figures/tables). The clients would only want to use concise and critical information to make decisions. Excessive information is often redundant and thus would be given demerits. For instance, you do not have to include the complete raw data from the resources, and only the adjusted/modified data should be included, if they are used directly for any table/figure/statistics reported in the relevant worksheet. The adjustment/modification process can be explained in the explanatory notes. Also, providing repetitive information, such as using the same figure/table for both pros/cons reports, is considered inappropriate. Conciseness means that only distinct information should be delivered.
   my lga is pittwater

ANSWER