Business Analytics 1: Comprehensive Data Analysis and Reporting

This document presents a comprehensive solution to a Business Analytics 1 assignment. It begins with an introduction to business analytics and its importance. The main body addresses four key questions, including breakeven point analysis, demonstrated through calculations and a one-way and two-way table. The assignment also covers data analysis techniques, such as descriptive statistics, identifying data types, and interpreting debt levels. Furthermore, it includes a cross-tabulation table and regression analysis to evaluate the relationship between variables, providing a thorough analysis of the provided data. The document includes the use of Excel for calculations and graph generation. The assignment also provides recommendations to students regarding the Covid-19 virus.

Business Analytics
1

Contents
Introduction......................................................................................................................................3
MAIN BODY..................................................................................................................................3
Question 1....................................................................................................................................3
Question 2....................................................................................................................................4
Question 3....................................................................................................................................5
Question 4....................................................................................................................................7
CONCLUSION................................................................................................................................9
REFERENCES..............................................................................................................................10
2

Introduction
In present time, it is very important to implement best analytic process for every kind of
business in order to evaluate the trends and patterns within the industry and also determine the
overall performance of different internal operation (Cocârţă, Stoian and Karademir, 2017).
Business analytic help in recognising the best possible business activities which can be profitable
in future also as well as it also supports to make certain plans for reducing the reasons for non-
profitable activity. In this report, different questions are analysed with the help of excel and
different concepts are covered such as breakeven point, one way and two way Anova,
recommendation to student regarding Covid-19 virus, data screening and interpreting and
descriptive analysis.
MAIN BODY
Question 1
A) Breakeven point:
Fixed cost £100
Variable cost £0.40
Units to be sold 500
Selling price £1
Contribution per unit
Selling price-variable cost per
unit
0.6
BEP (in units) Fixed cost/contribution per unit
Pens to be sold 166.67
B) One-way table:
Estimated units to be
sold
Pric
e
Sales
revenue
Fixed
cost
Variable cost per
unit
Variable
cost
Prof
it
400 3 1200 350 0.5 200 650
450 3 1350 350 0.5 225 775
520 3 1560 350 0.5 260 950
350 3 1050 350 0.5 175 525
260 3 780 350 0.5 130 300
C) Two-way table:
Estimated units to be
sold
Pric
e
Sales
revenue
Fixed
cost
Variable cost per
unit
Variable
cost
Prof
it
3

400 5 2000 500 0.4 160
134
0
490 5 2450 500 0.6 294
165
6
290 5 1450 500 0.7 203 747
350 5 1750 500 0.5 175
107
5
260 5 1300 500 0.8 208 592
D) Graph
1 2 3 4 5
0
100
200
300
400
500
600
700
800
900
1000
350 350 350 350 350
200 225 260
175 130
650
775
950
525
300
Chart Title
Fixed cost Variable cost Profit
Question 2
A) Sample and pollution size.
In the presented data, there have been 200 total observations those are respective student of listed
UK universities, they all are consumer of Ryanaires and have been travelling to their specific
destination. The main reason for their traveling is fear of getting infected due to Covid-19 as they
all are worried of getting sick if they were in contact of any other person. In order to collect the
accurate data, student was provided with a score rating number on their email ID which they use
for booking. The total number which each student give reflects the proportion of fear they are
because of Covid-19 and the be safe they want to travel their home as early as possible. This is
very useful data which is collected by primary means and will give a proper understanding about
the student awareness regarding Covid-19 impact. A benefit using primary data would be that
scientists are gathering knowledge for the specific audiences of their analysis. In general, the
questions posed by the investigators are designed to extract the knowledge that will assist them
throughout their research (Corbett and Mellouli, 2017).
4

B) Type of Graph.
Bar graph: A bar chart is a chart consisting uses rectangular columns (named bins) to large
datasets reflecting the cumulative number of findings within that segment in the data. Vertical
columns, diagonal bars, quantitative bars (several bars to show a distinction of values), or
layered bars will show bar graphs (bars containing multiple types of information). The aim of a
bar chart is to easily communicate relational knowledge as the quantities for a given group is
displayed by the lines. The vertical dimension including its bar graph is considered the y-axis,
whereas the x-axis is named the top of the bar chart. In order to compare the data or the
responses of two groups men and women bar chart can be most useful chart which help in clearly
showing the responses of these group regarding a specific topic. Form the collected data of
students it is assumed that there were total 80 females and 120 males, in order to show their
responses a specific bar chart is prepared. The same is displayed below:
male Female
0
20
40
60
80
100
120
140
120
80
Chart Title
Question 3
A) Data type: In the respective data set for all the variables there is a specific data type such
as:
Household ID: Categorical (Ordinal)
Family size: Numerical (ratio)
Location: Categorical (Nominal)
Ownership: Categorical (Ordinal)
First Income: Numerical (regular)
5

Second Income: Numerical (interval)
Monthly Payment: Numerical (regular)
Utilities: Numerical (ratio)
Debt: Numerical (ratio)
B) Categorical variables:
From the data set, it is determined that Household ID, Location and Ownership are variables
which have certain kind of categories (Gao, Chen and Yuan, 2017). Furthermore, they are also
segmented into ordinal and nominal categories such as household ID and ownership is ordinal
and location is nominal. It is observed that the data is divided in the respective manner as this
data form is descriptive, often obtained using survey questions, since often it gives participants
the right to type answers. While this trait tends to arrive at clearer conclusions, investigators
often have difficulties when they have to work with too much random knowledge. Although
mainly categorised as categorical data, also categorical and numerical data features are said to be
present, rendering it in between. Its designation as structured variable relates to the fact that it
may have certain normative data characteristics. Likert size, interval scale, bug intensity,
customer satisfaction information, etc. are some ordinary data instances. Each of these examples
is a
C) Histogram
6

D) Debt level
The maximum debt level from the collected data is $9,104 and the Household ID is 301. The
minimum debt level is $227 and the household ID for the same is 127.
E) Indebtedness level
The level of in-debtedness at specific quartile is as follows:
25th: 2968.75
50th: 4271.5
75th: 5676.5
F) Interquartile range interpretation:
7

The above determined quartile values for 25th is 2968.75 which states that it is the value of 1st
quarter of the data. The value of 50th quartile is 42.71.5 which define the average value of the 2nd
quarter of the collected data. The 70th quartile value of is 5656.5 which shows the mean value of
3rd quarter of the total number of observation (Huysegoms and Cappuyns, 2017).
Question 4
A) Descriptive analysis for Gross sales:
Column1
Mean
17189484.2
6
Standard Error
1746820.55
2
Median 411184
Mode #N/A
Standard
Deviation
42537860.1
9
Sample
Variance
1.80947E+1
5
Kurtosis
22.5515154
3
Skewness
4.16624022
2
Range 380995484
Minimum 15735
Maximum 381011219
Sum
1019336416
6
Count 593
Descriptive analysis for number of ticket sold:
Column1
Mean 2159483
Standard Error
219449.
8
Median 51656
Mode 5749
Standard
Deviation 5343952
Sample
Variance
2.86E+1
3
Kurtosis 22.5515
8

2
Skewness 4.16624
Range
4786375
4
Minimum 1977
Maximum
4786573
1
Sum
1.28E+0
9
Count 593
b) Cross tabulation table
crosstabulation between genre and movie classification rating
9

Regression Statistics
Multiple R 1
R Square 1
Adjusted R
Square 1
Standard
Error
2.382089
7
Observations 593
ANOVA
df SS MS F
Significan
ce F
Regressi
on 1
1.07E+1
8
1.07E+1
8
1.89E+1
7 0
Residual 591
3353.54
2
5.67435
1
Total 592
1.07E+1
8
Coeffici
ents
Standard
Error t Stat
P-
value
Lower
95%
Upper
95%
Lower
95.0%
Upper
95.0%
Intercept
0.17238
5 0.105518 1.6337
0.102
855
-
0.03485
0.37962
1 -0.03485 0.379621
X
Variable
1 7.96 1.83E-08
4.34E
+08 0 7.96 7.96 7.96 7.96
Observat
ion
Predict
ed Y
Residu
als
Standa
rd
Residu
als
1
381011
218
1.3035
05
0.5476
73
2
352390
544
-
1.2693
3
-
0.5333
2
3
276125
474
1.9232
86
0.8080
77
4
254464
308
-
3.2269
8
-
1.3558
3
5
241063
871
3.8495
56
1.6174
08
6 210031 - -
10

326 0.6911
0.2903
7
7
191450
871
4.1286
26
1.7346
61
8
181030
626
-
1.9451
7
-
0.8172
7
9
176760
181
3.5209
74
1.4793
53
10
176654
505
0.4806
31
0.2019
39
11
169499
547
-
1.1025
8
-
0.4632
5
12
169211
721
-
3.4635
1
-
1.4552
1
13
165249
066
-
3.3163
7
-
1.3933
9
14
146408
304
1.4225
21
0.5976
79
15
145738
326
0.7003
48
0.2942
54
16
143619
812
-
3.1065
2
-
1.3052
2
17
142614
162
-
3.6297
9
-
1.5250
7
18
141186
647
-
1.0744
2
-
0.4514
2
19
136910
217
2.3117
12
0.9712
76
20
127004
180
-
0.8004
2
-
0.3363
21
123477
605
1.7081
4
0.7176
83
22
117538
562
-
2.7511
2
-
1.1559
23
116601
168
3.7258
35
1.5654
26
24 115865 - -
11

051
3.3565
5
1.4102
7
25
109946
966
0.5042
51
0.2118
63
26
108085
306
-
0.5817
9
-
0.2444
4
27
104007
827
0.5649
86
0.2373
81
28
103028
111
-
1.6381
9
-
0.6882
9
29
100368
563
-
3.2068
2
-
1.3473
6
30
100292
856
0.3529
36
0.1482
88
31
999676
74
-
3.7281
2
-
1.5663
9
32
987800
42
0.2680
29
0.1126
13
33
978481
17
-
2.7749
9
-
1.1659
3
34
845726
20
3.1419
44
1.3201
02
35
844932
43
-
1.7383
1
-
0.7303
6
36
843511
97
0.4612
26
0.1937
86
37
835610
71
1.9786
63
0.8313
44
38
835524
26
2.5386
35
1.0666
19
39
826994
33
-
0.8641
3
-
0.3630
7
40
803608
64
1.5282
82
0.6421
15
41
792494
57
-
2.4353
2
-
1.0232
1
42 768022
27
-
2.0832
-
0.8752
12