Produce histograms and descriptive statistics. Conduct statistical analysis in Excel to examine what factors can be related to firm profitability. Produce scatter plots to graphically examine the relationship between the variables. Perform Correlation Analysis to check whether there is linear association between your chosen profitability ratio and each of the other two variables.

The purpose of this report is to analyse firm financial characteristics and examine the determinants of firm profitability.  To do this, you are required to select a sample of 50 non-financial firms listed on the FTSE 350 Index of the London Stock Exchange and collect data that represent firm financial characteristics. The data should be collected for the year 2020.

Tasks to be performed using Fame and Microsoft Excel include the following:

1.     Select a sample of firms using an appropriate sampling method in statistics. The sample of firms and their data should be gathered from FAME Database that contains data for UK and Irish publicly listed companies. All your selected companies should be firms in FTSE 350 Index and should exclude firms in the financial sector (such as banks and insurance companies. These can be found under BvD Sectors: 32 – Banking, Insurance & Financial Services).

Note that the sample should be collected by each of you individually to avoid any potential collusion.

2.     Collect the following data for your sample of companies for the year 2020:

1)     Company Name

2)     BvD Sector – sector a company operates in

3)     One Measure of Firm Profitability ONLY. You need to select either Return on Total Assets (ROA) or Return on Capital Employed (ROCE), Return on Shareholder Funds/Equity (ROE) or Profit Margin.

4)     Any other TWO of the following variables ONLY:

–       Total Assets for 2020 (to measure of Firm Size)

–       Turnover in 2020, Turnover in 2019 (to measure of Firm (Sales) Growth)

–       Current Assets, Stock & WIP, Current Liabilities – all for 2020 (to measure Liquidity)

–       Long-Term Debt, Current Liabilities, Total Assets – all for 2020 (to measure Financial Leverage)

You should have three variables in total, i.e., one measure of profitability and two other variables. Please ensure that all of your 50 firms have data available for all three of your chosen variables.

3.     Export the data you collected from Fame into Microsoft Excel

4.     Perform the following transformations/calculations of your data in Excel.

–       Firm Size = Natural Log of Total Assets

–       Sales Growth = (Turnover2020 – Turnover2019)/ Turnover2019 *100

–       Liquidity (Acid Test Ratio) = (Current Assets – Stock & WIP)/Current Liabilities

–       Leverage (Debt Ratio) = (Long-Term Debt + Current Liabilities)/Total Assets*100

5.     Produce histograms and descriptive statistics (min/max, mean/median, standard deviation, skewness and kurtosis) for profitability and the other two variables you have selected (Note that if you selected firm size, then it is advisable to produce statistics for both Total Assets and LN of Assets). Use descriptive statistics to identify outlying observations and make appropriate adjustments to your variables to ensure that there are no extreme outliers.

6.     Conduct statistical analysis in Excel to examine what factors can be related to firm profitability.

a)     Produce scatter plots to graphically examine the relationship between the variables.

b)    Perform Correlation Analysis to check whether there is linear association between your chosen profitability ratio and each of the other two variables. Use correlation table to check whether there is any potential problem of multicollinearity (this is especially important if you conduct multiple regression analysis).

c)     Perform either Simple Linear Regression or Multiple Linear Regression Analysis to examine factors that can be related to firm profitability. You should also conduct hypothesis testing in this section.

7.     Produce a Report in Microsoft Word based on the discussion of the results from your statistical analysis (see below for details).

 Your report should contain the following:

a)     Introduction, where you specify the aim of the report, variables you selected and the main methods of analysis.

b)    Explanation of the process of sample selection with reference to an appropriate sampling method.

c)     Outline of the variables you have selected.

d)    Discussion of the main statistics for your variables from histograms and descriptive statistics (min/max, mean/median, standard deviation, skewness and kurtosis), including comments on the distribution of each variable. This section should also include an explanation and justification of the adjustments made to your variables (e.g., outliers or natural logarithm).

e)     Discussion of the results from scatterplots and correlation analysis (direction and strength of the relationship).

f)     Discussion of the results from either simple or multiple regression analysis (coefficients and their p-values; results from the overall model such as R² and Adjusted R², F stats for multiple regression). You should discuss results from the hypothesis testing in order to identify whether there is any statistically significant relationship between the variables. In this section, you should also provide a discussion of your results in relation to the findings in the previous literature.

g)    Discussion of the main limitations of your study and recommendations for further research.

h)     Conclusion of the report where you provide a summary of the key findings from your analysis.

NOTE: You are required to submit the data that you collected and the graphs and tables you produced in EXCEL on Canvas.