Advanced Financial Data Analysis: Ratio Analysis, Time Value of Money Calculations, Investment Appraisal

This report covers Ratio Analysis, Time Value of Money Calculations, and Investment Appraisal for Advanced Financial Data Analysis. It includes Liquidity Ratio, Leverage Ratio, Coverage Ratio, Activity Ratio, and Profitability Ratios. It also covers Calculation of future value, effective annual interest rate, present value, simple interest, payback period, present value of annuity, and maximum amount borrowed by the customer.

ACC3115
ADVANCED
FINANCIAL DATA
ANALYSIS

Table of Contents
PART 1 – RATIO ANALYSIS.......................................................................................................3
Interpretation of above calculated ratios.....................................................................................6
PART 2 – TIME VALUE OF MONEY CALCULATIONS..........................................................6
1. Calculation of future value......................................................................................................6
2. Calculate of effective annual interest rate...............................................................................6
3. calculation of present value.....................................................................................................6
4. Calculation of simple interest..................................................................................................7
5. Calculation of payback period................................................................................................7
6.Calculation of present value of annuity...................................................................................7
7. Calculation of maximum amount borrowed by the customer.................................................8
PART 3 – INVESTMENT APPRAISAL........................................................................................9
REFERENCES..............................................................................................................................11

PART 1 – RATIO ANALYSIS
Ratio analysis is an easy and popular technique to strategically evaluate the financial
performance of an organisation for a specific time period range. This report presents ratio
analysis of Coca-cola company for the year 2021 and 2020. calculations of several ratios are
presented under in the following manner:
Particulars Year 2021 Year 2020
Liquidity Ratio
 Current ratio
=current assets/current liabilities
22545/19950
=1.13
19240/14601
=1.32
 Quick ratio
=liquid assets (current assets – inventory –
prepaid expenses)/ current liabilities
(22545-3414-
2994)/19950
=0.81
(19240-3266-
1916)/14601
=0.96
 Cash ratio
=(cash and cash equipments + marketable
securities)/current liabilities
(10926+1699)/
19950
=0.63
(8566+2348)/
14601
=0.75
 Basic defense interval
=Liquid assets (current assets – inventory –
prepaid expenses)/ Daily operating expenses
where, daily operating expenses
= (cost of goods sold + selling and
distributions expenses + administrative and
office expenses + general expenses – non-
cash expenses)/no. Of days in a year
(22545-3414-
2994)/78.75
=205
,where daily
operating
expenses are
(15357+12144+
846)/360
(19240-3266-
1916)/67
=211
,where daily
operating
expenses are
(13433+9731+8
53)/360
 Net working capital
=current assets – current liabilities
(22545-19950)
=2595
(19240-14601)
=4639
Leverage Ratio
 Capital structure ratio
◦ Equity ratio 22999/ 19299/

=shareholder's ratio/net assets
where, net assets includes net fixed and net
current assets
(2595+71809)
=0.31
(4639+68056)
0.28
◦ Debt ratio
=total debts/ net assets
(3307+1338+38
116)/
(2595+71809)
=0.57
(2183+485+401
25)/
(4639+68056)
=0.59
◦ Debt to equity ratio
=total debts/shareholder's equity
(3307+1338+38
116)/22999
=1.86
(2183+485+401
25)/19299
=2.217
◦ Debt to total assets
=total debts/total assets
(3307+1338+38
116)/95354
=0.45
(2183+485+401
25)/87296
=0.49
◦ Proprietary ratio
=proprietary fund/total assets
22999/95354
=0.24
19299/87296
=0.22
 Coverage ratio
◦ Debt service coverage ratio
=earning available for debts/interest
(9804+1597)/
1597
=7.14
(7768+1437)/
1437
=6.40
Activity Ratio
 Total asset turnover ratio
=sales/total assets
38655/95354
=0.40
33014/87296
=0.38
 Fixed asset turnover ratio
=sales/fixed assets
38655/71809
=0.54
33014/68056
=0.48
 Net asset turnover ratio
=sales/net assets
38655/
(2595+71809)
=0.52
33014/
(4639+68056)
=0.45
 Current asset turnover ratio 38655/22545 33014/19240

=sales/current assets =1.71 =1.72
 Inventory turnover ratio
=costs of goods sold/average inventory
where, average inventory
=(opening inventory+closing inventory)/2
15357/3340
=4.60
where, average
inventory is
(3414+3266)/2
=3340
13433/1633
=8.22
where, average
inventory is
(3266)/2
=1633
Profitability Ratios
 Based on sales
◦ Gross profit ratio
=gross profit/ sales *100
23298/38655*1
00
=60.27%
19581/33014*1
00
=59.31%
◦ Net profit ratio
=net profit/sales*100
12425/38655*1
00
=32.14%
9749/33014*10
0
=29.53%
◦ Operation profit ratio
=operating profit/sales*100
10308/38655*1
00
=26.67%
8997/33014*10
0
=27.25%
◦ COGS expenses ratio
=Costs of goods sold/sales*100
15357/38655*1
00
=39.73%
13433/33014*1
00
=40.69%
 Based on return
◦ Return on Assets
=net profit after tax/average total assets*100
where average total assets
=(opening total assets + closing total
assets)/2
12425/91325*1
00
=13.60%
where, average
total assets is
(94354+87296)/
9749/43648*10
0
=22.33%
where, average
total assets is

2
=91325
(87296)/2
=43648
◦ Return on Equity
=net profit after taxes/shareholders fund*100
12425/22999*1
00
=54%
9749/19299*10
0
=50.51%
Interpretation of above calculated ratios
From the above calculated ratios, it can be said that the company is performing well as its
liquidity position is strong and sound as represented by the liquidity ratios and it have a good
profitability ratios which depicts that the company have sound business practices to earn
revenues. It has efficient ability to turn its revenue into cash flows which increases its overall
ability to pay short-term debts and liabilities.
PART 2 – TIME VALUE OF MONEY CALCULATIONS
1. Calculation of future value
Loan amount (PV)= 12500
Rate= 7.5% compounded annually that is interest rate = 7.5 / 4 = 1.875%
Time= 4 years compounded annually that is time = 4*4 = 16
F.V. = P.V(1+r/100) n
F.V= 12500( 1 +1.875 / 100) 16
FF.V = 16900
The final payment includes both interest and principles = 16900
2. Calculate of effective annual interest rate
N= monthly payment
Interest rate =3% annually
Interest= (1+r / m)m-1
Interest = (1+0.03/12)12-1
Interest = 0.030416
Interest= 0.030416*100 = 3.0416%
The effective annual interest is = 3.0416%

3. calculation of present value
F.V.= 50000
T = 10 Years
R= 4 %
P.V= F.V/ert
P.V = 50000/ e(.04)(10)
P.V = 50000/ 1.4918
P.V = 33516.56
The required investment is now = 33516.56
4. Calculation of simple interest
Loan (P)= 14000
Interest rate = 9%
Time= monthly payment
I= principle* time * rate/100
I =(14000* 9 * 1) / 12*100
Interest = 105
The amount of interest paid monthly= 105
5. Calculation of payback period
Year Annual
Cash flow
Annual Cash
Outflow
Annual Net Cash
flows
Cumulative
Cash
Inflows
0 900 -900 0
1 150 0 150 150
2 350 0 350 500
3 350 0 350 850
4 350 0 350 1200
5 150 0 150 1350
Payback period = number of years completed + (Total amount invested – cash flow received
cumulatively)/ cash inflow in that year
Project A = 3 + (900-850) / 350
= 3 + 50 / 350
= 3 + 0.143

= 3.143 years
6.Calculation of present value of annuity
Monthly annuity = 720
interest rate= 3% p.a that is monthly payment = 3/12= 0.25%
year= 5 year monthly payment that is = 5*12= 60
Present value of the ordinary annuity = 40066.29
7. Calculation of maximum amount borrowed by the customer
P.V = 10000
interest rate = 3% p.a compounded annually = 3/12 = 0.25%
time = 3 *1 2= 36 month
F.V. = P.V(1+r/100) n
F.V=10000(1+ 0.25/ 100)36
F.V= 11391.96
Saving = 500 per month
t = 36 month
interest = 4% p.a monthly= 4 / 12 = 0.33%
F.V. = P.V(1+r/100) n
F.V=500(1+0.33/100) 36
F.V= 16944.06
(I) the amount accumulated by in three years time = (11391.96+ 16944.06) = 28336.02
The maximum amount of the mortgage to be taken out = 28336.02
(ii) P.V = 28336.02
Time = 25years
rate= 6%
payment = p *(r / n) * ( 1 + r / n)*n(t))/ 1 +r / n) *n(t) – 1
Payment = 28336.02(0.06 / 25)*(1 + 0.06 / 25)*1(25)) /(1+0.06/25)* 1 (25)-1
The monthly mortgage payment, assuming this maximum amount is borrowed=73.7842 monthly

PART 3 – INVESTMENT APPRAISAL
Particulars Year 1 Year 2 Year 3 Year 4 Year 5 Year 6
Sales units 50000 60000 72000 54000 40500 30375
Sales prices 55 56.38 57.78 59.22 60.7 62.22
Sales
revenue (a)
amount in £
£2,750,000.
00
£3,382,800.
00
£4,160,160.
00
£3,197,880.
00
£2,458,350.
00
£1,889,932.
50
Scrap value £150,000.00
Total
inflow
£2,750,000.
00
£3,382,800.
00
£4,160,160.
00
£3,197,880.
00
£2,458,350.
00
£2,039,932.
00
Less-
Cost of sales
65% of sales
£1,787,500.
00
£2,198,820.
00
£2,704,104.
00
£1,822,791.
60
£1,401,259.
50
£1,077,261.
53
Market
research
£500,000.00
Selling and
administrati
ve expenses
£750,000.00

Lease rent £150,000.00
Working
capital
requirement
£400,000.00 £492,044.00 £605,115.00 £465,147.00 £357,579.00 £274,900.00
Total
outflow
£3,587,500.
00
£2,690,864.
00
£3,309,219.
00
£2,287,938.
60
£1,758,838.
50
£1,352,161.
53
Net profit
before tax
-
£837,500.00
£691,936.00 £850,941.00 £909,941.40 £699,511.50 £687,770.47
Less: tax
expense @
21%
-
£175,875.00
£145306.56 £178,697.61 £191,087.69 £146,897.42 £144431.80
Net profit
after tax
-
£1,013,375.
00
£546,629.44 £672,243.39 £718,853.71 £552,614.08 £543,338.67
Cost of
capital @
18%
1 0.85 0.72 0.61 0.52 0.44
Net
discounted
cash flows
-
£1013375.0
0
£464,635.02 £484,015.24 £438,500.76 £287,359.32 £239069.01
As per discounted payback period technique
=2 year + £64724.74/438500.76
=2.15 year
As per Net present value technique
= net present cash inflow – net present cash outflow
= £1913579.35 - £1013375.00
= £900204.35

As per Present value index technique
= total discounted cash inflow/ total discounted cash outflow
= £1913579.35/£1013375
= 1.89
As per simple payback period technique
= 1 year + £205497.83/£672243.39
= 1.31 year

REFERENCES
Books and Journals
Cavender-Bares nad et.al,2021. Kg remote sensing with ecology and evolution to advance
biodiversity conservation. Nature Ecology & Evolution.6(5), pp.506-519.
Fennell and et.al, 2021. Wd With Advance Care Planning by Race. American Journal of Hospice
and Palliative Medicine®, p.10499091221094779.
Junzhou and et.al, 2021. Tion method for rock mass stability of tunnel boring based on deep
neural network of time series. Proceedings of the Institution of Mechanical Engineers, Part
C: Journal of Mechanical Engineering Science, 236(10), pp.5618-5633.
Riordan, and et.al,2021. Vcare planning in older dialysis patients: health care literacy qualitative
study. BMJ Supportive & Palliative Care.
Sharma, A., 2022, April. MTN: Tranformation from Mixed Dataset into Pure Numeric Dataset.
In 2022 2nd International Conference on Advance Computing and Innovative
Technologies in Engineering (ICACITE) (pp. 799-802). IEEE.
Wang and et.al, 2021.Au nanoparticles advance SERS detection of cellular
glutathione. Biosensors and Bioelectronics, p.114388.