Assessment overview For this assessment, you need to produce a report by generating responses to six tasks presented below. For some of these sections, you will need to use Excel to generate statistical output (statistical analyses and graphs). The report should be presented in the form of a business report to a senior manager who cannot be assumed to have any particular knowledge of statistical methods. Microsoft Word and Excel should be used to complete this assessment. Your statistical calculations should be carried out using Excel only. You will submit the Excel file and a pdf version of the report as soft copies via the submission folder in Canvas. A hardcopy is not to be presented. . Assessment details You will need to download the Excel dataset ‘Major Assignment data Company Profits.xlsx’ from Canvas. The data set contains companies listed on the ASX with their sector area and financial information. There are 1536 companies in this data set and eight variables as follows: 1. ASX code 2. Company Name 3. Status: Trading, Delisted or Suspended 4. GICS Sector: 11 types and described in file 5. GICS Industry Group: 24 types and described in file 6. Market Capitalisation – in AUD 7. Total Assets in AUD 8. Total Revenue in AUD 9. Net profit after tax in AUD FIN10002 Semester 1 2019 Major Assignment 2 You will use this data set to generate responses to the following six tasks. 1. Select a random sample Select a random sample of size 50 customers from the 1536 companies in the Major Assignment data Company Profits file. You will use this sample data to complete tasks 2 to 6. 2. Descriptive statistics Use data summary methods to describe the returns in your sample using seven variables – items 3 to 9 above. (Note: Do NOT do ASX code and Company Name) Use an appropriate graphical and summary statistical technique, chosen according to the type of variable (note that less appropriate/inappropriate techniques will receive fewer/no marks). Choose your techniques from: Tabular Techniques: frequency tables and grouped frequency tables Summary Statistics: mode, median, mean, standard deviation, range, coefficient of variation and interquartile range Graphical Techniques: pie chart, bar graph, histogram, frequency polygon. (See topics 1 and 2) Do not draw an ogive curve, stem plot, or a box plot in this assignment and do not draw 3-D graphs. 1. For a nominal or an ordinal variable draw a graph and present a frequency table in percentages. 2. For a ratio or an interval, variable draw a graph and a summary statistics table, including summary statistics appropriate to the type of distribution only. 3. Try to use variation in drawing graphs e.g. pie chart/bar chart or histogram/polygon. 4. Do not draw two different graphs for the same variable. You can draw the same type of graph for two variables. 5. Do not include any information that you will not include in your discussion such as kurtosis. 6. Display and describe one variable at a time. 3. Confidence intervals Estimate the following quantities, using 95% confidence intervals. Explain the meaning of your confidence intervals. a. The average Total revenue for GICS Sector of Materials only b. The average Total Assets for all types of companies that are trading. Compare both confidence intervals with their respective true means by calculating the actual population mean for the full 1536 companies, and comparing the true population mean to the sample mean and confidence FIN10002 Semester 1 2019 Major Assignment 3 interval (note: it is not usual to do this, so you are asked to do this for the purpose of this assignment). Your confidence interval should start with ‘We are 95% confident that…”. This section should take half a page or less. NB: Please make sure you provide sufficient information in the appendix for your confidence interval calculations to be replicated, so they can be checked. 4. Hypothesis testing 1. It is often felt that the average total revenue for Financials (GICS) is more than the average total revenue for Health Care (GICS). Investigate this contention by carrying out an appropriate hypothesis test. 2. It is often felt that the average market capitalisation, differs for Financials and materials. Use GICS sector for this test. Only report a non-technical explanation of your methodology and your findings in the main section of the report. The computations and output should be placed in an appendix, including the test statistic, p-value and degrees of freedom. This should take half a page or less. 5. Correlation and regression In this section, you will investigate the relationship between Total Assets and the net profit after tax Using these two variables (Total Assets and Net profit after tax) develop a regression model to predict net profit after tax from the total assets. Make sure that you undertake a full regression analysis, with appropriate discussion and include: 1. a scatterplot and a brief discussion 2. an estimate of the linear regression model 3. the coefficients of correlation and determination 4. a test of the hypothesis that there is no linear relationship between total assets and net profit after tax. Ensure your scatterplot includes a line of best fit. Also, make sure you describe the relationship between the variables using R and R-square and interpret the slope, coefficients and the results of the hypothesis test. Use a significance level of 0.05. 6. Conclusion Provide a brief, concise summary of all of your findings and briefly mention any limitations in your findings. Make sure you do not give tables or graphs here. FIN10002 Semester 1 2019 Major Assignment 4 Presentation The report should be presented in the form of a business report to a senior manager who cannot be assumed to have any particular knowledge of statistical methods. Make your report informative but concise and use a non-technical style. Do not just quote statistics or analysis results but explain what they mean. In general, do not include in the report formulae, calculations, definitions of statistical terms or discussions on how graphs are constructed. Where appropriate all these items should be included in the appendices. It is important that the values which have been calculated are correctly analysed, discussed and interpreted, and that a written description of the main features of the tables and graphs that have been constructed is included. The emphasis in this assignment is on interpretation and analysis, not just the computation of statistics and construction of graphs. It will be assumed that all computations have been correctly performed and that graphs have been properly constructed. Nevertheless, marks will be deducted if these are inaccurate or incorrect. The presentation is an important feature of a business report. The guide to the presentation that follows gives a general outline to report writing. Executive Summary 1. Report only the highlights of the findings. 2. Entice an Executive to read on. 3. Essentially a lively summary of the main conclusions. 4. No longer than one page; this is not counted in the word count and must be on a separate page from the rest of the report. Introduction 1. State the purpose of the report i.e. what you will discuss in the report 2. Outline the contents of the Report 3. Provide a brief description of the methodology 4. Describe the source of the data and state its location in the report. 5. This should contain information about what we expect to read in the project. This should take about half a page. Analysis 1. Contains a thorough yet non-technical description of all the findings (graphs and tables will be included only where they help this discussion). 2. Details the results that were highlighted in the Executive Summary 3. Do not include any calculations here but include appropriate graphs, results and tables which are needed to support your discussions. FIN10002 Semester 1 2019 Major Assignment 5 Conclusion 1. Report the findings and results of your work. 2. Essentially an expansion of the executive summary written from the point of view that the Executive Summary has not been read. 3. End with a discussion of the limitations of your analysis (e.g. reference to sample size if small, or comment on the data if it is old). Appendices 1. Must be referred to in the main body of the report. 2. Must contain your selection of random numbers and related random data. 3. Include the raw data, charts and tables that are not essential, but support the ANALYSIS section. 4. Include your EXCEL output for descriptive statistics, confidence intervals, hypothesis testing and regression. 5. Include any other relevant calculations. Please make sure the information in the appendix is sufficient for all calculations to be replicated, so they can be checked e.g. if you include the output for your confidence intervals, please show how this output was used to calculate the confidence intervals. Graphs must be in the Main body along with relevant tables and discussion. Graphs kept only in the appendices will not score any marks for graphs. Keep the appendices to a moderate size. Marks are only given for the report itself. Your Excel work confirmation will be checked in the Appendices. The emphasis in this assignment is on interpretation and analysis, not just the computation of statistics and construction of graphs. Make your report informative but concise and use a non-technical style. Do not just quote statistics or results of analyses but explain what they mean. In general, do not include formulae, calculations, definitions of statistical terms, or discussions on how graphs are constructed. Where appropriate these may be included in the appendices. Submission details This assessment will be submitted in softcopy via Canvas in the Major Assignment submission. You are to submit both the report in pdf format and the excel file. Make sure that your student id and name is in the footer of the assignment. You will need to attach an Assignment coversheet with the assignment.
The report has provided relationship overview between net profit after tax and total assets for the 50 sampled companies through random sampling technique from 1536 companies provided in the excel sheet. The main analysis techniques in the report are correlation and linear regression analysis. It was evaluated that net profit after tax and total assets for the 50 selected random sample indicated strong positive association (rho=0.790). Additionally, the linear regression analysis indicated that total assets positively influence on the net profit after tax.
In the contemporary market, net profit in a company is determined by several econometric factors such as asset level a company has, market capitalization, total revenue among other factors in the economy. In light of this, the report will devise a model that can be applied to predict net profit after tax with total assets as the main explanatory variable. Additionally, the report will explore the relationship between total assets and net profit after tax for the 50 sampled companies using random sampling.
Exploratory Data Analysis
In this section, presents descriptive statistics for the variables in the analysis. Descriptive analysis infers much on the spread of the data. The main graphical features are histogram, pie charts, bar graphs for the ordinal variables.
Figure 1: Histogram for Market Capitalization
Table 1: Descriptive analysis for Market Capitalization
Illustration on figure 1 and table 1 above displays histogram and descriptive analysis respectively for market capitalization. Based on the histogram, it is evident that the data is skewed to the left. This implies that data violate normality assumption. The variable market capitalization has mean, kurtosis and skewness of 288586452.9, 16.8388 and 4.05595 respectively. The variable has leptokurtic distribution since kurtosis value is greater than value 3. There is lack of symmetry in the data.
Figure 2: Histogram of total assets
Table 2: Descriptive analysis for total assets
Illustration on figure 2 and table 2 above displays histogram and descriptive analysis respectively for total assets. Based on the histogram, it is evident that the data is skewed to the left. This implies that data violate normality assumption. The variable total assets has mean, kurtosis and skewness of 1294236558, 49.1953 and 6.9899 respectively. The variable has leptokurtic distribution since kurtosis value is greater than value 3. There is lack of symmetry in the data.
Figure 3: Histogram for total Revenue
Table 3: Descriptive Analysis for total Revenue
Illustration on figure 3 and table 3 above displays histogram and descriptive analysis respectively for total revenue. Based on the histogram, it is evident that the data is skewed to the left. Therefore, there is lack of symmetry in the data. This implies that data violate normality assumption. The variable total revenue has mean, kurtosis and skewness of 308442407.6, 20.1765 and 4.2467 respectively. The variable has leptokurtic distribution since kurtosis value is greater than value 3.
Figure 4: Histogram for Net Profit after tax
Table 4: Descriptive analysis for net profit after tax
Illustration on figure 4 and table 4 above displays histogram and descriptive analysis respectively for net profit after tax. Based on the histogram, there is evidence of unimodal shape with zero value as the center of the origin. However, this is numerically evaluated using skewness value presented in table 4. The variable total revenue has mean, kurtosis and skewness of 10951803.94, 20.5411 and 4.4667 respectively. The data is negatively skewed thus the line of symmetry is shifted to the left side. The variable has leptokurtic distribution since kurtosis value is greater than value 3.
Figure 5: Pie Chart for Company Status
Table 5: Descriptive analysis for Company Status
Illustration on figure 5 and table 5 above displays pie chart and frequency tabulation for company status. Evidently, delisted, suspended and trading company proportions stood at 32.0%, 4.0% and 64% respectively.
Figure 6: Pie Chart for GCIS Sector
Table 6: Frequency tabulation for GCIS Sector
Illustration on figure 6 and table 6 displays pie chart and frequency tabulation for GCIS sector respectively. It is established that consumer discretionary, consumer staples and energy stood at 10.0%, 4.0% and 8.0% respectively. Additionally, it is established that financials, healthcare, industries and information technology stood at 20.0%, 10.0%, 8.0% and 12.0% respectively. The GCIS sectors materials, real estate and utilities stood at 24.0% , 2.0% and 2.0% respectively.
Figure 7: Pie chart for GCIS Industry Group
Table 7: Descriptive analysis for GCIS Industry Group
Illustration on figure 7 and table 7 above displays pie chart and frequency tabulation respectively for GCIS industry group. It is established that banks, capital goods, consumer durables& Apparel, consumer services stood at 2.0%, 6.0%, 2.0% and 2.0% respectively. It is established that diversified financials, energy, food &staples stood at 18.0%, 8.0% and 4.0% respectively. Health care equipment& services and materials stood at 2.0% and 24.0% respectively. Pharmaceuticals, biotechnology & life and real estate, retailing, software & services and utilities technology hardware & equipment stood at 8.0%, 2.0, 4.0%, 12.0% and 2.0% respectively.
Confidence interval for total revenue and total assets for the sample
In this section 95% confidence, CI is -22032.6328 for the upper limit while the upper confidence level is 933407.2994 for the total revenue for the GCIS sector of materials. The CI level is presented as follows:
The above confidence level implies that the researcher is 95% sure that the mean value for the total revenue for GCIS sector of materials is found between -22032.6328 and 933407.2994 range. Similarly, 95% confidence level for the total assets for all types of companies that are trading is presented as follows:
The range implies that the researcher is 95% sure that the mean for the total assets for all types of companies trading to be in the range -781092600 as the lower range while 3369565715 is the upper level.
The 95% confidence level for whole population for GCIS sector for materials is presented as shown below (type=materials):
The 95% confidence level for whole population (total assets) for GCIS for all types of industries is presented as shown below:
Evidently, there are clear variations in relation to mean and confidence levels for the two aspects in considerations. All the calculations for confidence level calculations are presented in the appendix section.
In this section, there is need to attest whether average total revenue for financials (GCIS) is greater than average total revenue for health care (GCIS) and whether average market capitalization differs for financials and materials industries. The main analysis technique is independent t test analysis.
Illustration on table 8 above displays right tailed t test to evaluate whether indeed average total revenues for financials (GCIS) is more than average total revenue for health care (GCIS). Hypothesis is set as follows:
It is established that t-stat (1.402) that is statistically at 0.05 alpha levels (p-value=0.0973) under right tailed test. It is established that t (0.05, df=13) =1.771. The stated null hypothesis is not rejected at 95% confidence level. The mean difference is positive one implying that total revenue for financials GCIS is greater than health care GCIS revenue.
Illustration on table 9 above displays two tailed t test to evaluate whether indeed average market capitalization for financials (GCIS) differs from average total capitalization for materials care (GCIS). Hypothesis is set as follows:
It is established that t-stat (1.245) that is statistically at 0.05 alpha levels (p-value=0.2446) under two tailed test. It is established that t(0.05, df=20)=2.086. The stated null hypothesis is not rejected at 95% confidence level. The stated null hypothesis is not rejected at 95% confidence level for two-tailed test. Therefore, the average levels for market capitalization do not differ significantly for financials and materials.
Correlation and regression analysis
Figure 8: Scatter plot between net profit after tax and total assets
Table 10: Correlation Matrix
Figures 8 and 10 displays scatter plot and correlation matrix respectively to check the relationship level between net profit after tax and total assets. The scatter plot indicates that there is a positive linear relationship between net profit after tax and total assets. Numerically, it is established that the two variables have strong positive correlation levels (rho=0.790). This indicates presence of association between the two variables.
In this section, a linear regression model is fitted to predict net profit after tax with total asset as the main independent variable.
Table 11: Summary Statistics
Illustration on table 11 displays model’s summary. It is established that total asset accounts for 62.4514% variation of the net profit after tax (R-Square=0.6245). Additionally, adjusted R-Square is 0.616691. This is the variation accounted for by the independent variable after adjustments made with number of cases in the data. Anova table below evaluates whether the model is adequate at 0.05 alpha levels.
Table 12: Anova Analysis
The F-ratio statistics is applied to test the following hypothesis for model adequancy.
H0: β1=β2=…=βi=0 for i=1, 2, 3…
H1: β1≠β2≠…≠βi≠0 for i=1, 2, 3…
It is established that F (1, 48) =79.83432, p-value<0.010. The stated null hypothesis is rejected in favor of the alternative one at 95% confidence levels. This implies that the model is adequate at 0.05 alpha levels. In this case, total assets significantly influences on net profit after tax. Subsequently, a linear regression model is fitted based on results presented in table 13 below.
Table 13: Estimated Model Coefficients
A linear regression model is fitted as shown below:
The constant (β0=2467675) is net profit level after tax when explanatory variable (total asset) is set equal to zero value. The constant is statistically insignificant at 0.05 alpha levels (p-value=0.6569). It is established that the variable “total assets” has a positive coefficient of 0.006555 that is statistically significant at 0.05 alpha levels (p-value<0.010). One unit increase of the variable “total assets” increases net profit after tax with a magnitude of 0.006555 units.
The report has evaluated the relationship between net profit after tax and total assets level for the 50 selected companies. The sample was randomly selected from a population of 1536 companies. The main analysis technique in the analysis was correlation and regression analysis. Correlation analysis established presence of strong positive relationship between net profit after tax and total assets level for the 50-selected sample.
CI is calculated as follows:
For total revenue for GCIS group sector of materials we have
For total assets for all types of companies, trading is calculated as follows:
For total revenue for all types of companies, trading is calculated as follows (whole population=materials):
For total assets for all types of companies, trading is calculated as follows (whole population):