Data Analysis and Forecasting of Bank Expenses - Finance Report

This report presents a comprehensive analysis of bank expenses over a ten-month period, employing various statistical techniques to forecast future financial performance. The analysis begins with presenting the data in a tabular format, followed by visual representations using column and line charts to illustrate trends. Descriptive statistics, including mean, median, mode, range, and standard deviation, are calculated and explained to provide a deeper understanding of the data's central tendency and dispersion. Furthermore, a linear forecasting model (y = mx + c) is applied to predict expenses for the subsequent two months, with detailed calculations of the slope (m) and y-intercept (c). The report concludes with a summary of the findings, highlighting the application of statistical tools in financial forecasting, with references to relevant books and journals.

DATA ANALYSIS AND
FORECASTING

TABLE OF CONTENTS
INTRODUCTION...........................................................................................................................1
MAIN BODY...................................................................................................................................1
1. Presenting data in a tabular format .........................................................................................1
2. Representation of the data in Column chart and Line chart.....................................................2
3. Calculation of descriptive statistics along with steps..............................................................3
4. Linear forecasting model is y= mx+c......................................................................................6
Calculation of m...........................................................................................................................7
(ii) Calculation of c......................................................................................................................7
CONCLUSION................................................................................................................................8
REFERENCES................................................................................................................................9

INTRODUCTION
Data analysis refers to the evaluation of past data using the various statistical measures
and tools in order to forecast the future financial performance of the company. The current
project shall highlight the expenses of banks in the previous ten months. Hence, on the basis of
that the expenses of future two months shall be forecasted. The predictions are done by using the
various statistical techniques like mean, mode, median, range and standard deviation. Further the
linear forecasting model shall be applied.
MAIN BODY
1. Presenting data in a tabular format
These are the expenses for 10 consecutive months beginning from April to January 2020
as per the bank statement: -
1. Data arranged in the table format
MONTHS TOTAL EXPENSES (in £ million)
April 955
May 970
June 1045
July 875
August 905
September 992
October 1045
November 1120
December 895
January 950
2. Representation of the data in Column chart and Line chart
Column chart: - This tool is used for pictorial representation of the data collected and its
analysis through visualization. The discrete categories having single values are represented with
the help of rectangles, the height of which is showing the proportional values.
1

Line chart: It implies for the graphical representation of data in the form of continuous line
joining the various points of data. With the help of pictograph, the viewer can analyse the
increasing and decreasing trend of the data.
2
April
May
June
July
August
September
October
November
December
January
0
200
400
600
800
1000
1200
TOTAL EXPENSES (in £
million)
April
May
June
July
August
September
October
November
December
January
0
200
400
600
800
1000
1200
TOTAL EXPENSES (in £
million)

3. Calculation of descriptive statistics along with steps
Mean: - The arithmetic mean of the collected data is the average value of the series. It is
used to measure the central tendency of the probability distribution and is significantly used for
the purpose of financial valuations (Yadav, Singh and Gupta, 2019).
Steps for calculation:
Step 1: Calculate the sum of all the observations in the data collection
Step 2: Ascertain the total number of observations
Step 3: Divide the sum of observations by the number of observations
MONTHS TOTAL EXPENSES (in £ million)
April 955
May 970
June 1045
July 875
August 905
September 992
October 1045
November 1120
December 895
January 950
ΣX= 9752
N= 10
Arithmetic mean = ΣX / N
9752 / 10
= 975.2
3

Median: - Median represents the middle number in the data series which is arranged either in the
ascending or descending order (Mishra and et.al., 2019). It divides the data into two parts where
one is higher and the other is lower than the median value.
Steps for calculation:
Step 1: Arrange the values in the descending order
Step 2: Allocate the two middle values from the even series of data
Step 3: Add the middle values and divide by two to calculate median
TOTAL EXPENSES (in £ million)
1120
1045
1045
922
970
955
950
905
895
875
Middle values are 970 which is the fifth value and 955 which is the sixth value.
Median = 970 + 955 / 2 = 962.5
Mode: - The mode of a data set is the most frequently repeated value of the series. In the current
data of total monthly expenses of the bank, £1045 million expenses have been incurred in two
months of the year one is June and the other is October.
Mode value: £1045 million
4

Range: - The range of a series is the difference between the highest and the lowest value of the
series (Cao, Li and Li, 2019). It is the measure of variability of the data by evaluating the two
extreme ends in the data series.
Steps for calculation:
Step 1: Arrange the series in the descending order
Step 2: Subtract the minimum value from the maximum value to calculate the range of the data.
TOTAL EXPENSES (in £ million)
1120
1045
1045
922
970
955
950
905
895
875
Range = Maximum value - Minimum value
1120 – 875 = 245
Standard deviation: - The standard deviation is the statistical measure of dispersion in a
series relatively from that of mean of the data (Arosa and et.al., 2016). It is calculated in the form
of square root of the variance and the more the data spread higher shall be the standard deviation
of the data.
Steps for calculation:
5

Step 1: Find the mean value of the data
Step 2: The variance of each value in the data is calculated by subtracting the mean from the
value.
Step 3: Each of the resulting variance is then squared and summed up (Standard deviation and
variance, 2021).
Step 4: The sum of square root of the variance is then divided by number of values minus 1.
MONTHS TOTAL EXPENSES
(in £ million)
(x-x̄) (x-x̄)^2
April 955 (955-975.2)= -20.2 408.04
May 970 (970-975.2)= -5.2 27.04
June 1045 (1045-975.2)= 69.8 4872.04
July 875 (875-975.2)= -100.2 10040.04
August 905 (905-975.2)= -70.2 4928.04
September 992 (992-975.2)= 16.8 282.24
October 1045 (1045-975.2)= 69.8 4872.04
November 1120 (1120-975.2)= 144.8 20967.04
December 895 (895-975.2)= -80.2 6432.04
January 950 (950-975.2)= -25.2 635.04
ΣX= 9752 Σ(x-x̄)= 0 Σ(x-x̄)^2= 53463.6
Mean is denoted by x̄
ΣX/N= 9752/10= 975.2
Standard deviation= √ Σ(x- x̄)^2/ (n-1)
√ 53463.6/ (10-1) = 77.07
So, the standard deviation of the data is £77 million.
4. Linear forecasting model is y= mx+c
MONTHS NUMBER OF TOTAL (X*Y) X^2
6

MONTH (X) EXPENSES (in £
million) (Y)
April 1 955 955 1
May 2 970 1940 4
June 3 1045 3135 9
July 4 875 3500 16
August 5 905 4525 25
September 6 992 5952 36
October 7 1045 7315 49
November 8 1120 8960 64
December 9 895 8055 81
January 10 950 9500 100
Total 55 9752 53837 385
Calculation of m
Step 1: N*Σxy
Step 2: NΣxy – Σx Σy
Step 3: N*Σ x^2
Step 4: NΣ x^2 – (Σx)^2
Step 5: NΣxy – Σx Σy / NΣ x^2 – (Σx)^2
(ii) Calculation of c
Step 1: m*Σx
Step 2: Σy – mΣx
Step 3: Σy - mΣx / N
(iii) Forecasting the total expenditure for month 11 and month 12 by using the values of M and C
7

Particulars Formula Y = mX + c
M m = NΣxy – Σx Σy / NΣ x^2
– (Σx)^2
m= (10*53837)- (55*9752)/
(10*385) - (55)^2
m= (538370-536360)/(3850-
3025)
m= 2010/825
m=2.436
C c = Σy - mΣx / N c= 9752- (2.436*55)/ 10
c= (9752- 133.98)/ 10
c= 9618.02/10
c=961.802
Forecasting total expenses
for month 11
Y = mX + c X= 11
Y= (2.436*11) +961.802
Y= 26.796+961.802
Y= 988.598
Forecasting total expenses
for month 12
Y = mX + c X= 12
Y= (2.436*12) +961.802
Y= 29.232+961.802
Y= 991.034
CONCLUSION
It is summarized from the above project that the past data can be analysed to forecast the
future performance based on the trend that is being followed. The analysis is done by using the
various statistical measures of central tendency and dispersion to evaluate the past data. The
linear forecasting model helps in determining the expenses of 11th and 12th month. The expenses
in the 11th month is 988.598 and in the 12th month is 991.034.
8

REFERENCES
Books and Journals
Arosa, Y. and et.al., 2016. Spectrally resolved white light interferometry to measure material
dispersion over a wide spectral band in a single acquisition. Optics express. 24(15).
pp.17303-17312.
Cao, J., Li, Z. and Li, J., 2019. Financial time series forecasting model based on CEEMDAN
and LSTM. Physica A: Statistical Mechanics and its Applications. 519. pp.127-139.
Mishra, P. and et.al., 2019. Descriptive statistics and normality tests for statistical data. Annals
of cardiac anaesthesia. 22(1). p.67.
Yadav, S. K., Singh, S. and Gupta, R., 2019. Measures of Central Tendency. In Biomedical
Statistics (pp. 41-52). Springer, Singapore.Online
Standard deviation and variance. 2021. [Online.] Available through:
<https://www.mathsisfun.com/data/standard-deviation.html>
9