7318AFE Business Data Analytics Assignment 2 Questions Trimester 3 2025-26 | Griffith University

7318AFE Business Data Analytics Assignment 2

Instructions:

• Data files are provided in Excel in the assessment section of the course canvas website.
• All numerical calculations and graphs/plots should be done using EXCEL as much as possible.
• Your answers should be typed in Word. Present the answers to all questions as efficiently as possible in the main assignment with all relevant (Excel) output (eg, plots, tables etc) inserted into the main document.
• While working on your assignment, always save your work in two devices so that if one is lost, you will have a backup copy.
• The completed assignment must be submitted electronically.
• You are required to keep a copy of the submitted assignment to re-submit in case the original submission is lost for some reason.
• GOOD LUCK!

QUESTION 1: Return and Portfolio Diversification (2+3.5+3.5=9 marks)

An investment consultant collected quarterly performance data for three mutual funds and saved the information in a file named FUND_RETURNS.xlsx. Using this dataset and carrying out the required calculations in Excel, answer the following questions.

A. a. Calculate the mean and variance of each of the three funds.
b. If you wish to construct a portfolio that maximises the expected return, what should you do?
c. If you wish to construct a portfolio that minimises the risk, what should you do?

B. d. Find the expected value and variance of the following portfolio: Fund A    20%
Fund B    55%
Fund C    25%
e. How do the expected value and variance of the portfolio compare with those of Part A?

C. f. Find the expected value and variance of the following portfolio: Fund A    30%
Fund B    10%
Fund C    60%
g. How do the expected value and variance of the portfolio compare with those of Part A and Part B?

QUESTION 2: Solar Adoption in Queensland [1+ 3 + 6 = 10 marks]

A local council in Queensland is considering a new subsidy program for residential solar battery storage. They are specifically targeting households with an annual income between $80,000 and $120,000 that currently have solar panels but no battery system. To gauge the potential demand, the council surveyed 400 households within that income bracket. Each household was asked to identify their current energy setup:
1.No solar panels installed.
2.Solar panels installed, but no battery system.
3.Solar panels and a battery system are already installed.
4.Off-grid (completely independent of the local council grid).

The responses are recorded and stored in the file Solar_Energy.xlsx.

a.Calculate the proportion of households in the surveyed income bracket that belong to the council's target market (those who have solar panels but no battery).
b.Estimate with 95% confidence, the true proportion of all households in this income bracket in Queensland that belong to the target market.
c.The Council Mayor claims that more than 42% of households in this income bracket are in the target market and would benefit from the subsidy. Test the Mayor’s claim at the 5% level of significance using the 6-step hypothesis testing process.

QUESTION 3: Drivers of Global Economic Growth [1+3+3+1+8+3+8=27 marks]

The Scenario

You are a Lead Data Analyst for an international economic consultancy. A client wants to understand which factors most significantly contribute to the Gross Domestic Product (GDP) per capita of a nation. They are particularly interested in the "Knowledge Economy" and how education, innovation, and technological infrastructure impact wealth.

The Question

Develop a multiple regression model to predict a country’s GDP per capita (constant 2015 US$) based on the following independent variables.

Variables (Independent)

• Research & Development (R&D) Expenditure (% of GDP)
• High-Technology Exports (% of manufactured exports)
• Individuals using the Internet (% of population) These data are available in the file GDP_Data.xlsx.

a.Develop a multiple regression model.
b.Estimate the model using the recorded data. Write the estimated regression model with standard errors and p-values in the standard format.
c.Interpret the coefficient estimates of the independent variables.
d.Provide a measure of goodness-of-fit and discuss the fitness of the model.
e.Test to determine whether each of the independent variables is linearly related to GDP per capita. ( = 0.05)
f.Test the overall utility of the model. Is the model likely to be useful in predicting GDP per capita? ( = 0.05)
g.List the required conditions for the estimation of the multiple regression model. Verify whether these conditions are satisfied by the data.

QUESTION 4: Predicting Quarterly Tourist Arrivals [1+2+4+2+2+1+2=14 marks]

The tourism industry in Australia is characterised by substantial seasonal variation. The Australian Bureau of Statistics (ABS) publishes a range of statistics related to tourism activity. Quarterly short-term inbound tourist arrival data for Australia, covering the period from the first quarter of 2010 to the fourth quarter of 2019, are provided in the file Tourism.xlsx.

a.Plot the time series using an appropriate chart. Does there appear to be any seasonal pattern?

Measuring the seasonal effect using the trend method:

b.Estimate the linear trend line (from regression analysis) for the data and present the estimated trend line.
c.Calculate the quarterly seasonal indexes using the trend line, calculate the seasonally adjusted series and plot the unadjusted and adjusted series.
d.Forecast the number of inbound tourist arrivals to Australia during the four quarters in 2020- 2024.
Updating data and comparing with the forecast – Effect of the Covid-19 pandemic in 2020 and beyond:
e.Collect the actual quarterly tourist arrivals data for 2020-2024 and present it in a table (source should also be provided, may use monthly data to derive the quarterly data).
f.Compare the actual and predicted quarterly tourist arrivals for 2020 (Covid-19 pandemic year) and comment on the effect of Covid-19 on the quarterly tourist arrivals in 2020.
g.Considering the actual quarterly tourist arrivals for 2021-2024, comment whether the increases are on track to reach the pre-pandemic 2019 quarterly tourist arrival levels in Australia.