SG4002 Business Intelligence and Data Analysis: Financial Report

This report provides a financial analysis of 40 companies, examining variables such as number of employees, net profits, revenues, price, and operating expenses. Percentage frequency distribution tables and charts are used to present the data for each quantitative variable. A cross-tabulation of sectors and profits is performed, and a stacked bar chart illustrates profit distribution within each sector. The report includes calculations of mean, median, standard deviation, and IQR for the number of employees, along with skewness analysis. Point estimates and confidence intervals for the population mean are computed. A regression model is developed to analyze the relationship between operating expenses and net profits, with interpretation of the slope variable, coefficient of determination, and t-statistic. The study concludes that there is a significant relationship between operating expenses and net profit, with operating expenses accounting for 39.92% of the variation in net profit.

Module Title: Business Intelligence and Data Analysis
Module Code: SG4002
Module Title: Introduction to Data Analytics
Module Code: EC4002
Academic Year 2018/19
1

Abstract
The main aim of this study was to analyse the financial point of different
companies. We analysed factors such as number of employees, net profits,
revenues, price and even the operating expenses of 40 selected companies.
We finally constructed a regression model to predict the net profit based on
the operating expenses. The following were the key findings;
 Profits made by the selected 40 companies ranged between 0-10000
 The data on number of employees was skewed (positively skewed)
 There was significant relationship between operating expenses and net
profit made by the companies.
 Operating expenses was found to account for 39.92% of the variation
in the net profit
2

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.
Answer
This is done in the excel file attached.
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.
Answer
Price
Class Freque
ncy
Percent
100-500 8 20.0%
501-1000 15 37.5%
1001-2000 5 12.5%
2001-7000 12 30.0%
3

The above figures shows that the most frequent prices (37.5%) fall between
501-1000.
Operating Expenses
Class Frequenc
y
Percent
0-500 14 35.0%
501-1000 3 7.5%
1001-2000 9 22.5%
2001-10000 3 7.5%
10001-50000 11 27.5%
4

Most companies had their operating expenses range between 0-500 followed
by those that spent between 100001-500000.
Number of employees
Class Frequenc
y
Percen
t
0-1000 4 10.0%
1001-10000 10 25.0%
10001-50000 12 30.0%
50001-100000 8 20.0%
100001-200000 6 15.0%
In terms of employees’ numbers, most companies had employees ranging
between 10001 - 50000. Second in the list is the companies with employees
between 1001 – 10000.
Revenue
Class Frequenc
y
Percent
0-1000 4 10.0%
1001-10000 17 42.5%
5

10001-50000 16 40.0%
50001-200000 3 7.5%
In terms revenue, most companies had revenue between 1001-10000 while
minority had revenue ranging between 50001-200000.
Net profit
Class Frequenc
y
Percent
0-500 14 35.0%
501-1000 10 25.0%
1001-10000 16 40.0%
6

In terms of net profit, most companies (40%) had net profit ranging between
1001-10000. 35% had net profit ranging between 0-500 while 25% had net
profits between 501-1000.
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.
Answer
Count of Net profit Profit
Row Labels 0-500 1001-10000 501-1000 Grand Total
Banks 2 2
Capital Goods 1 1 2
Commercial & Professional Services 1 1
Consumer Durables & Apparel 1 1
Consumer Services 2 2
Diversified Financials 1 1 1 3
Energy 2 2
Food & Staples Retailing 1 1 2
Food, Beverage & Tobacco 2 1 3
Health Care Equipment & Services 1 1
Household & Personal Products 1 1
Insurance 1 2 3
Materials 1 1 2 4
Media & Entertainment 1 1 2
Pharmaceuticals, Biotechnology & Life Sciences 1 1
Real Estate 2 2
Retailing 2 2
Software & Services 1 1
Technology Hardware & Equipment 1 1
7

Telecommunication Services 1 1
Transportation 1 1
Utilities 1 1 2
Grand Total 14 16 10 40
Count of Net profit Profit
Row Labels 0-500 1001-10000 501-1000 Grand Total
Banks 0% 100% 0% 100%
Capital Goods 50% 0% 50% 100%
Commercial & Professional Services 100% 0% 0% 100%
Consumer Durables & Apparel 0% 0% 100% 100%
Consumer Services 0% 0% 100% 100%
Diversified Financials 33% 33% 33% 100%
Energy 0% 100% 0% 100%
Food & Staples Retailing 50% 0% 50% 100%
Food, Beverage & Tobacco 0% 67% 33% 100%
Health Care Equipment & Services 100% 0% 0% 100%
Household & Personal Products 0% 100% 0% 100%
Insurance 33% 67% 0% 100%
Materials 25% 25% 50% 100%
Media & Entertainment 50% 50% 0% 100%
Pharmaceuticals, Biotechnology & Life Sciences 0% 100% 0% 100%
Real Estate 100% 0% 0% 100%
Retailing 100% 0% 0% 100%
Software & Services 100% 0% 0% 100%
Technology Hardware & Equipment 100% 0% 0% 100%
Telecommunication Services 0% 100% 0% 100%
Transportation 0% 100% 0% 100%
Utilities 0% 50% 50% 100%
Grand Total 35% 40% 25% 100%
8

The stacked bar chart shows that there is significant relationship between the
sector and the profit made by the company. Some sectors made a profit of
between 501-1000 while others had profits ranging from 0-500 and others
ranging between 1001-10000.
Task 4
Compute the mean, median, standard deviation, IQR (Interquartile Range) for
the number of employees and comment on your results.
Answer
Number of Employees
Mean 43530.0
5
Median 22016
Standard Deviation 47439.5
1
9

Interquartile range
(IQR)
1658.32
5
The mean number of employees was found to be 43530.05 while the median
number of employees was 22016. This shows that the data is not normally
distributed since the difference between the mean and the median is so big.
The non-normality of the data is further confirmed by the large standard
deviation and large interquartile range.
Draw a histogram using the data on the number of employees and calculate
the skewness. Comment on the shape of the distribution.
Answer
The data shows that the number of employees are skewed as can be seen
from the given histogram (Shimazaki & Shinomoto, 2007).
Using excel, we computed the skewness value as 0.9934. The computed
value of skewness further shows that the data is skewed
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.
Point Estimate Population parameter
Population Mean 43530.05 43530.05
Population Standard deviation 46842.76 47439.51
10

From the computations, we observe the point estimate of the mean to be
43530.05 and it is the same for the population parameter. However, the
population parameter for the standard deviation is slightly larger than the point
estimate of the standard deviation.
Construct a 95 percent confidence interval for the population mean for the
number of employees. Interpret your results.
Answer
C . I=x ± zα/ 2 S . E
x=43530.05; zα / 2=1.96
S . E= 47439.51
√40 =7500.845
C . I=x ± zα/ 2 S . E → 43530.05± 1.96∗7500.845=43530.05± 14701.66
Lower limit: 43530.05−14701.66=28828.39
Upper limit: 43530.05+ 14701.66=58231.71
The results shows that the 95% confidence interval for the true population
mean number of employees is between 28828.39 and 58231.71.
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.
Answer
11

The scatter plot above gives the relationship between operating expenses and
net profits. It can clearly be seen that a positive linear relationship exists
between the two variables (operating expenses and net profits).
Estimate the regression equation using the above variables and comment on
the slope variable.
Answer
From excel, the regression analysis for predicting the net profit was performed
and the results presented in the table below;
Coefficient
s
Standar
d Error t Stat
P-
value
Lower
95%
Upper
95%
Intercept 734.7660
245.318
5
2.995
2
0.004
8
238.144
6
1231.387
3
Operating
Expense
(in £million) 0.1007 0.0200
5.024
6
0.000
0 0.0601 0.1413
Based on the results table, we construct the regression model as;
Net Profit=734.7660+0. 1007(Operating Expense)
12