PROBLEM

You have been approached by a strategic investor who is interested in firms on the Australian Stock Exchange (ASX). The investor is aware of your data analytics skill that you have acquired in ACC3201.

It is their expectation that you can provide an appropriate advice using your data analytics skill and considering their personal values. Specifically, as a believer of climate change and good governance, they are interested in the effect of emissions on firm profitability.

Using financial and regression analysis, advise the strategic investor. 

NB: You are required to use five firms for the above problem.

Part 1: Firms selection and request for data

a. Identify and select five firms on the Australian Stock Exchange (ASX) with available data. 

b. Download the following data for the selected firms from 2010 to 2018 using Thomson Reuters Eikon DataStream:

I. Financial Data

i. Net profit margin

ii. Return on Assets 

iii. Quick ratio

iv. Current ratio

v. Long-term debt to equity ratio

vi. Interest coverage ratio

vii. Total assets

II. Non-financial Data

i. Emissions Score

Part 2: Data preparation and cleaning

a. Examine the downloaded data and note any missing values

b. Critically evaluate the data, identify and report any extreme values (outliers)

c. Clean the data and prepare the spreadsheet for analysis

d. Run basic statistical analysis (summary statistics in excel) 

Part 3: Financial analysis

With the aid of appropriate graphs and charts, perform a financial analysis of each firm (NB. using the downloaded ratios). Emphasis on the following;

a. profitability 

b. liquidity 

c. solvency

Part 4: Regression analysis

a. Using any of the profitability ratios as the dependent variable, run a multiple regression in Excel with Emissions Score, firm size (natural log of total assets) and leverage (long-term debt to equity ratio) as independent variables. 

b. Write the regression equation using the output from your regression analysis.

c. Provide an interpretation of the regression output. 

NB: Provide correlation analysis as part of your regression analysis.

Part 5: Creating interactive dashboard in excel

Based on your analyses, develop an interactive dashboard in excel that highlights indicators you consider relevant for managerial decisions. 

Part 6: Documenting your findings

Write a report to document the outcomes of your analysis. The report should include the following:

a. An indication of any missing data and outliers together with how they were treated

b. Financial analysis of the firms.

c. An interpretation of your regression analysis. 

d. Based on your regression analysis, what is your recommendation to the strategic investor? Do firm emissions enhance profitability?  Justify your recommendations to the client

e. Your report must comprise of four (4) main sections: 

a. Executive summary

b. Introduction

c. Body

d. Conclusion.