Select 40 companies from the excel file called Assessment Data —BloombergFTSE100 and create a data file and make sure you select at least one company from each sector.
Task 1
Select 40 companies from the excel file called Assessment Data –BloombergFTSE100 and create a data file and make sure you select at least one company from each sector. The spreadsheet contains 22 sectors which are – Household & Personal Products; Materials; Banks; Food, Beverage & Tobacco; Telecommunication Services; Pharmaceuticals, Biotechnology & Life Sciences; Utilities; Insurance; Transportation; Commercial & Professional Services; Consumer Services; Food & Staples Retailing; Capital Goods; Diversified Financials; Health Care Equipment & Services; Consumer Durables & Apparel; Energy; Retailing; Media & Entertainment; Real Estate; Software & Services ; Technology Hardware & Equipment.
Make sure for each company you have the data on the 6 variables namely Sector, Price, Operating Expense, Number of Employees, Revenue and Net Profit. [10 marks]
Task 2
For each of the 5 quantitative variables construct a percentage frequency distribution table and present the data using appropriate charts. Comment on your findings.
[20 marks]
Task 3
Prepare a cross tabulation using sectors (rows) and profits (columns). Compute row percentages for the cross tabulation and construct a stacked bar chart showing the percentage distribution of profits within each sector type. Comment on the relationship between variables. [10 marks]
Task 4
Compute the mean, median, standard deviation, IQR (Interquartile Range) for the number of employees and comment on your results.
[20 marks]
Draw a histogram using the data on the number of employees and calculate the skewness. Comment on the shape of the distribution.
[10 marks]
Task 5
Calculate the point estimate of the population mean and population standard deviation of the number of employees. Compare the estimates to the population parameter.
[5 marks]Construct a 95 percent confidence interval for the population mean for the number of employees. Interpret your results.
[5 marks]
Task 6
Develop a scatter diagram to analyse the relationship between operating expenses and net profits, where operating expenses is the independent variable and net profits the dependent variable. Comment on the relationship.
[5 marks]
Estimate the regression equation using the above variables and comment on the slope variable.
Find the coefficient of determination (R-squared) and comment on its value.
Calculate the t-statistic and test whether the slope variable is significant using hypothesis testing.
Sample Solution