程序辅导案例 > Program >

代写辅导接单-Data Analytics and Insights

欢迎使用51辅导，51作业君孵化低价透明的学长辅导平台，服务保持优质，平均费用压低50%以上！ 51fudao.top

Data Analytics and Insights

Assessment

Case study

Bellami Residential Property

Business background

Bellami Residential Property (Bellami) is a real estate agency founded by Lucas and Sophie Bellami in Sydney in 2006.

The agency has been very successful in the market for premium residential properties and has expanded across

Australia with offices in all major cities. Bellami consistently features on The Fast 100, a list of Australia's strongest

growing companies, and its achievements have not gone unnoticed with the broader public. The name ‘Bellami’ has

become an iconic brand in the Australian property industry.

Real estate agents play a key role in Bellami’s success story. Before being recruited, an agent must pass a rigorous

selection process. In the first three months of employment, an agent must spend all their time deepening their

knowledge of the local market, honing their marketing and negotiation skills, and understanding Bellami’s exceptional

client service standards. Lucas holds quarterly performance review meetings with every agent. Agents find these

meetings to be professionally confronting, and underperforming agents don’t last long in Bellami’s high-achiever

culture. In contrast, successful agents are rewarded generously, with an above-market percentage allocation of the

commission earned by Bellami.

Five years ago, Bellami introduced a web-based predictive analytics tool that uses machine learning algorithms to

identify potential buyers for properties in Bellami’s portfolio. The software uses different variables, such as past

purchasing history and household income, to predict the likelihood of potential buyers being interested in a specific

property. This allows Bellami to launch targeted marketing campaigns and approach pre-selected prospects.

While Bellami has experienced tremendous growth under their leadership, Lucas and Sophie feel that the time has

come to prepare a succession plan to sell Bellami, so their involvement diminishes over time. They have appointed a

team of experts to help them. The plan should determine how much Bellami is worth (based on critical success factors).

It should also identify parties who might be interested in acquiring Bellami and have the financial strength to do so.

Your role

You are Bellami’s accountant with an excellent knowledge of data analytics. Lucas and Sophie have asked you to

contribute to the succession plan. You have the following information to analyse so you can provide

recommendations:

• Stakeholder interview notes (Appendix A).

• Dataset A (DAI_T424_A1.4.3_Dataset_A) relates to Bellami's Sydney operations only and requires cleansing before

analysis. It includes data on clients, agents, offices, listings and transactions, and a data dictionary.

• Dataset B (DAI_T424_A1.4.3_Dataset_B) relates to all Bellami’s office locations and has been cleansed so is ready

for analysis. It includes data on clients, agents, offices and transactions, and a data dictionary.

You will complete four tasks, which are described below.

Page 1

DAI_T424_A.1.4.1_Case-study_v1-8

Part A: Written submission (80 marks)

Task 1: Business success factors and data preparation (40 marks)

Required

(a) Describe four (4) key factors that explain Bellami’s business success from the stakeholder interview notes in

Appendix 1. Translate each answer into one (1) measurable analytics question.

Note: You do not need to take the datasets into account. You are not required to answer the measurable

analytics questions you create in this task.

For this task, use the following template to set out your answer:

Success factor Measurable analytics question

(8 marks)

(b) For each of the four (4) variables ComMed_Pref_C, Sat_Score_C, Comm_Am, and Net_Result_YTD_A in

Dataset A:

i. Identify the appropriate measurement levels: 1) Categorical – nominal, 2) Categorical – ordinal,

3) Numerical – ratio, or 4) Numerical – interval.

ii. Determine if the variables can be used to calculate the following: 1) a frequency distribution, 2) a

median, 3) the sum of variable values, 4) a standard deviation, and 5) a ratio of variable values. Answer

with ‘Y’ (Yes) or ‘N’ (No) in the appropriate cells in the template provided below.

Variable name Variable Codification Measurement Calculation

description levels

ComMed_Pref_C Client's preferred T – Text message Frequency distribution Y/N

medium of

E – Email Median Y/N

communication

S – Social media Sum Y/N

C – Phone call Standard deviation Y/N

L – Live chat Ratio Y/N

Sat_Score_C Score of how 1 – Not at all Frequency distribution Y/N

satisfied a client is satisfied

Median Y/N

with Bellami's

2 – Not very satisfied

property services Sum Y/N

3 – Somewhat

Standard deviation Y/N

satisfied

Ratio Y/N

4 – Very satisfied

5 – Extremely

satisfied

Page 2

DAI_T424_A.1.4.1_Case-study_v1-8

Comm_Am Amount of Open-ended dollar Frequency distribution Y/N

commission that amount >$0

Median Y/N

Bellami charges

on the property Sum Y/N

sale transaction

Standard deviation Y/N

Ratio Y/N

Net_Result_YTD_A Net result of agent Dollar amount Frequency distribution Y/N

year-to-date,

Median Y/N

defined as:

Sum Y/N

commission

earned less Standard deviation Y/N

• agent's share Ratio Y/N

of commission

• base salary

• allocated

marketing

expenses

(12 marks)

model (using Model view in Power BI) in your written submission.

(1 mark)

(d) Identify the primary key(s) and the cardinalities in the data model from Task 1(c).

You can use the following template:

Data table Primary key

Clients

Agents

Offices

Listings

Transactions

First data table Second data table Type of cardinality

(3 marks)

Page 3

DAI_T424_A.1.4.1_Case-study_v1-8

(e) The variable Perc_Comm in the Transactions worksheet of Dataset A refers to the commission percentage. It is

calculated as: amount of commission on the property sale transaction ÷ amount of the property sale transaction ×

100%. In this case, outliers are defined as commission percentage values with a Z-score greater than 3.

Calculate the Z-score for each value of the variable Perc_Comm and identify any outliers (one or more). For

each identified outlier, include:

iii. the Trans_Nb (the unique transaction number)

iv. Perc_Comm (the commission percentage)

v. the corresponding Z-score

vi. a plausible reason why the outlier may have occurred.

You can use the following template (you don't need to show your calculations; add rows if needed):

i. Trans_Nb ii. Perc_Comm iii. Corresponding Z-score iv. Why outlier may have occurred

(4 marks)

(f) Excluding the presence of any outlier(s) you identified for Task 1(e), explain six (6) data quality issues in

Dataset A and recommend how each should be addressed. Ignore any outliers you identified in Task 1(e).

You can use the following template:

Quality issue Data table Cleansing category Explanation of quality Recommendation to address

issue quality issue

(Example) (Suppliers) (Inconsistent data) (Supplier 102 delivers (For supplier 102 replace

widgets but the company widgets with the correct product

does not use widgets in category OR remove supplier

its production process.) 102 from the supplier data

table.)

(12 marks)

40 marks

Page 4

DAI_T424_A.1.4.1_Case-study_v1-8

Task 2: Data analytics in Power BI (20 marks)

The business succession team wants more insight into the financial performance of agents and offices, the profile of

clients and agents, and the properties that Bellami sells. Lucas has asked you to analyse the data to assist in

answering their questions.

Load Dataset B into Power BI and establish the following data model.

Note that the inactive relationship (dotted line) between Clients and Transactions has been removed.

Page 5

DAI_T424_A.1.4.1_Case-study_v1-8

Required

(a) Create the following four (4) table visuals in Power BI:

i. A table visualisation with four (4) columns: 1) City_O, 2) Comm_Am, 3) Number of Transactions, and

4) Commission per Transaction (Commission Amount ÷ Number of Transactions). Sort the table in

descending order from high to low Commission per Transaction.

ii. A table visualisation with three (3) columns: 1) States (NSW, VIC, QLD, WA, SA, ACT, TAS and NT),

2) the Number of Clients per State, and 3) the Ratio between Number of Clients per State and Total

Clients, rounded to one decimal place. Sort the table in descending order from high to low Number of

Clients per State.

iii. A table visualisation with the top five (5) best-performing agents. The table must have four (4) columns:

1) First Name Agent, 2) Last Name Agent, 3) City, and 4) Total Commission Amount. Sort the table in

descending order from high to low Total Commission Amount.

iv. A table visualisation with properties sold by Bellami (in the period 1 January – 31 October 2024) that

have a size of less than or equal to 200 m2 and a transaction value of greater than or equal to

$3,200,000. Sort the table in descending order of square metres (SQM).

Screenshot the Power BI table visuals you create in i–iv and copy them into your written submission.

(8 marks)

(b) Develop the following six (6) visualisations in Power BI. Ensure each visual is ‘polished’ (that is, appropriate

for the specific information purposes and containing all relevant chart elements such as a title and a

description of each axis).

i. A stacked bar chart displaying the number of clients in each of the 10 cities where Bellami has an

office(s), where each bar is labelled to show the number of clients.

ii. A line and stacked column chart for each month of 2024 (January–October) that clearly indicates

months on the x-axis. The left y-axis must show the total commission amount as a line (in whole dollars)

and the right y-axis must show the number of transactions as columns. Use contrasting colours to

separate the commission amount (line) from the number of transactions (columns).

iii. A Power BI card visual displaying the number of agents that have been employed by Bellami for

10 years or more on 31 October 2024.

iv. A donut chart of the commission amount for each of the 10 cities (where Bellami has offices). This chart

must include a slicer to make a distinction between total commission amounts earned in urban areas

and total commission amounts earned outside urban areas (apply the slicer to provide two (2) versions

of the donut chart: 1) Urban = 'Yes', and 2) Urban = 'No').

v. A Power BI card visual with the total commission amount earned by Bellami's offices in New South

Wales in the third quarter of 2024 (rounded to the nearest whole dollar).

vi. A scatter chart with the date of birth of agents (DoB_A) on the x-axis and for each date of birth the

average commission per transaction (commission amount ÷ number of transactions) on the y-axis. The

chart must include data points aggregated over the whole period from 1 January to 31 October 2024,

and a trendline that represents the relationship between the x-axis and y-axis variables.

Take screenshots of the Power BI visualisations and copy them into your written submission. Submit your

Power BI file as supporting evidence – without this, your Task 2 response will not be marked.

(12 marks)

20 marks

Page 6

DAI_T424_A.1.4.1_Case-study_v1-8

Task 3: Predictive analysis (20 marks)

Lucas has asked you to develop a model that can predict the likely transaction amount (sale price) of a property. From

prior research you have identified that the following variable in Dataset B could explain the transaction amount:

• SQM: square metre floor size of the property (all rooms and garage included).

• Bedrooms: number of bedrooms in the property.

• Baths: number of bathrooms in the property. Half bathrooms are bathrooms with partial components, e.g. toilet or

shower only.

• Garage: number of garage units in the property (a garage unit can park one car).

• Urban: location of property either in an urban area (Yes) or in a rural area (No) according to Australian postcodes.

Required

Your task is to analyse the above relationship with the data in Dataset B by performing a multiple linear

regression analysis using the Regression tool from the Data Analysis tools in MS Excel (located in the Data tab if

the Analysis ToolPak add-in has been installed). Submit your Excel file as supporting evidence – without this,

your Task 3 response will not be marked. Complete the following steps to perform the analysis:

(a) In Dataset B, add a dummy variable ‘Dummy_Urban’. The dummy variable must have a numerical value of 1

if the variable Urban is ‘Yes’ and a value of 0 if the variable Urban is ‘No’. Provide a screenshot of the first

five (5) transactions (Trans_Nb 1 to 5) that shows the values of all variables in the data table, including

Dummy_Urban.

(2 marks)

(b) In MS Excel perform a multilinear regression analysis of the independent variables SQM, Bedrooms, Baths,

Garage and Dummy_Urban on the dependent variable Trans_Am.

i. Provide a screenshot of the results of your regression analysis encompassing parameter estimations

and corresponding t-statistics/p-values, and regression statistics including the R-square coefficient

(coefficient of determination).

(5 marks)

ii. Explain the statistical significance of each independent variable (including the intercept term) and the

meaning of each associated coefficient.

You can use the following template to record your answer.

Independent Statistical significance of the coefficient Meaning of the coefficient

variable

Intercept

SQM

Bedrooms

Baths

Garage

Dummy_Urban

Page 7

DAI_T424_A.1.4.1_Case-study_v1-8

(6 marks)

iii. Interpret the 'goodness of fit' of the model.

(3 marks)

garage located in an urban area. Round your answer to a whole dollar amount. Provide a screenshot of your

Excel calculations in your written submission.

(4 marks)

20 marks

Part B: Video presentation (20 marks)

Required

Create a three (3) minute recorded presentation that communicates insight into the financial performance of

agents and offices, the profile of clients and agents, and the properties that Bellami sells.

Ensure you are communicating with influence and using the six (6) key elements of effective data storytelling.

Note: you are free to use the visualisations from tasks 2(a) and 2(b) or any other visualisations you feel are more

appropriate for this task. You will be marked on the quality of your communication.

20 marks

Page 8

DAI_T424_A.1.4.1_Case-study_v1-8

Appendix 1 – Stakeholder interview notes

Stakeholder City Extract

Lucas Bellami Sydney ‘In this industry it all comes down to excellent client service. We are providing our

clients with a hassle-free and smooth experience when they sell or buy what is often

their biggest asset.’

Sophie Bellami Sydney ‘I would dare to say that our machine-learning algorithm, which predicts potential

buyers for properties, is the best in the industry. We have a success rate of more than

50%.’

Seller #1 Melbourne ‘Our agent proved to be an excellent negotiator. He was creative in finding workable

solutions and never gave up. I am convinced he got us the best price for our home.’

Branch Melbourne ‘Providing excellent client service is essential for building trust and credibility with

manager #1 clients. Agents must be responsive, reliable and accessible. They need to go above

and beyond to meet the needs and expectations of their clients.’

Agent #1 Adelaide ‘Bellami is a great company to work for. There is constant drive to improve negotiation

skills and get the results your clients expect.’

Branch Perth ‘Our IT systems are second to none: they help us to quickly match the right buyers with

manager #2 our vendors.’

Buyer #1 Hobart ‘We were impressed by the agent’s knowledge of properties. She understood exactly

what we wanted and didn’t waste time showing us properties that we were not

interested in.’

Agent #2 Darwin ‘I am already 14 years with the company. I am still proud if I can close a deal with a

happy seller and a happy buyer.’

Seller #2 Newcastle ‘Bellami’s agent in Newcastle, Jamie Howard, has an excellent understanding of the

local property market. For us that was a decisive reason to engage Bellami.’

Buyer #2 Brisbane ‘After I completed my wish list on Bellami’s website, I immediately received a couple of

interesting properties. It didn’t take long before I bought my ideal home.’

Seller #3 Gold ‘Every week the agent called to give us an update. Agents we used in the past did not

Coast provide this level of client service.’

Seller #4 Sydney ‘I expected it would take some time to sell our property. I was pleasantly surprised that

it took Bellami only one week to get the deal over the line. The agent was such a

brilliant negotiator.’

Agent #3 Brisbane ‘As Bellami agents, we keep our ears to the ground in the local market. We know

exactly what is selling and why.’

Seller #5 Adelaide 'You pay a bit more commission, but you know that you are getting a first-class client

experience.’

Buyer #3 Melbourne ‘Once I registered on Bellami’s website, I wasn’t bombarded with emails promoting

every new listing.’

Page 9

DAI_T424_A.1.4.1_Case-study_v1-8