4.
10 Analyzing Financial Spread Sheet
Analyze of financial spreadsheet of a firm/ organization for lending is the second major credit risk
management system. A Financial Spread Sheet (FSS) has been developed by City Bank which may be
used while analyzing the credit risk elements of a credit proposal from financial point of view.
The FSS is well designed and programmed software having two parts. Input and Output Sheets.
The financial numbers of borrowers need to be inputted in the Input Sheets which will then
automatically generate the Output Sheets. Various financial ratios are calculated by this software which
is given on Table:
Profitability Ratios:
Gross Margin
Operating Margin
Net Margin
Return on Equity
Return on Paid up Capital
Return on Assets (Geared)
Return on Assets (Ungeared)
Earning Retention Rate
Operating Efficiency Ratios:
Total Asset Turnover
Inventory Turnover
Equity Turnover
Liquidity Ratios:
Stock Turn Over Days
Debtors Days on Hand
Creditors Days on Hand
Cash Cycle Period
Current Ratio
Quick Ratio
Debtors Turnover
Net Working Assets to Net Sales
Total Short Term Debt Coverage
Debt Management Ratios:
Leverage Ratio
NPBIT : Interest
Net Cash after Opn. : Interest
Interest on Avg. Financial Debt (%)
Debt Ratio
Interest Coverage
Debt/Lease Service Coverage Ratio
Growth Ratios
Sales Growth
Net Profit Growth
Total Asset Growth
Total Liabilities Growth
Gross Profit Growth
Sustainable Growth
Safety Margin
Bibliography
1. Credit Policy Manual-2008 of The City Bank Limited.
2. Credit Instruction Manual-2008 The City Bank Ltd.
3. Annual report-2008 of The City Bank Limited.
4. Research Methodology, By-Richard Daniel & John Morgan, Published in USA, 2005 By
International Research Institute of Minnesota.
5. Prudential guidelines of credit risk management.
6. Managing core risks in Banking : Credit Risk Management - Published by : Bangladesh Bank-2005
7. Bangladesh Bank circular and guidelines-BRPD Circular No.18 dated 11.12.2008
8. Credit Risk Grading Manual_ Introduced by Bangladesh Bank, November 2005
9. Annual Report of Bangladesh Bank-2007
10. Economic Report of Bangladesh-2007
11. Bangladesh Bank Website. [Link]
12. Goggle : [Link]
Wikipedia : [Link]
Gross NPL ratio(%)
2019 5.8
2020-4.0%
2021- 4.9%
2022-3.9%
Net NPL Ratio(5)
2019 3.4%
2020 2.6%
2021 2.9%
2022 2.3%
Provision Coverage Ratio
2019 77.9%
2020 94.9%
2021 90.1%
2022 104.7%
Total loans and advances
City Bank's total loans and advances increased to Tk. 354,744 mn, up sharply from Tk. 286,380 mn in
2021.
Total deposits
City Bank's total deposits rose to Tk. 331,890 mn in 2022, up significantly from Tk. 282,064 mn in the
prior year.
Capital Adequacy Ratio (CAR) City Bank's CAR improved to 14.5% in 2022, up from 14.2% in 2021 due to
successful capital mobilisation activities.
Market capitalisation
City Bank's m-cap stood at Tk. 29,135 mn, comprising 4.4% of the total domestic banking sector's m-cap.
Non-performing loans
City Bank's NPLs reduced sharply from 4.9% in 2021 to 3.9% in 2022, representing a substantive
moderation by 100 bps
Total Deposits(in millions)
350000 331890
300000 282064
246704 254781
250000
205170
200000
150000
100000
50000
0
2018 2019 2020 2021 2022
400000
Loans and Advances(in million)
354,774
350000
300000 286,380
268,202
246,944
250000 231,391
200000
150000
100000
50000
0
2018 2019 2020 2021 2022
NPL (%)
7
6 5.8
5.3
4.9
5
4 3.9
4
0
2018 2019 2020 2021 2022
NPL Volume (in million)
16000
14,244 13,906
14000 13,671
12,326
12000 10,850
10000
8000
6000
4000
2000
0
2018 2019 2020 2021 2022
CBL vs. Industry NPL(%)
12
10.3
10 9.3 9.36
8.12
8 7.7
5.8
6 5.3
4.86
4 3.9
4
0
2018 2019 2020 2021 2022
Loan to Deposit Ratio(%)
0.86
84.70%
0.84 83.20%
82.70%
0.82
0.8 79.40%
0.78 77.70%
0.76
0.74
2018 2019 2020 2021 2022
Loan to deposit ratio 83.2% 84.7% 77.7% 79.4% 82.7% (22-18)
NPL to Total Loans and Advances(%)
7
6 5.8
5.3
4.9
5
4 3.9
4
0
2018 2019 2020 2021 2022
NPL to total loans and advances 3.9% 4.9% 4.0% 5.8% 5.3%
Provision for Unclassified Loans 6,911 4,973 5,159 4,240 3,486
Provision for Unclassified Loans(in million)
8000
6911
7000
6000
5159 4973
5000
4240
4000
3486
3000
2000
1000
0
2018 2019 2020 2021 2022
Provision for Classified Loans 5,451 5,602 3,933 5,830 4,488
Provision for Classified Loans(in million)
7000
6000 5830
5602 5451
5000
4488
3933
4000
3000
2000
1000
0
2018 2019 2020 2021 2022
Provision for Off-balance Sheet Exposure 1,952 1,952 1,202 1,032 991
Provision for Off-balance Sheet Exposure(in million)
2500
2000 1952 1952
1500
1202
991 1032
1000
500
0
2018 2019 2020 2021 2022
NPL coverage
Capital Adequacy Ratio (Goal- achievement) (total capital/total risk requirement)
2022 14.5%
2021 14.2%
2020 4.0%
2019 15.2%
2018 13.4%
Capital Adequacy Ratio
0.16
15.50%
0.155
15.20%
0.15
14.70%
0.145
14.20%
0.14
0.135 13.40%
0.13
0.125
0.12
2018 2019 2020 2021 2022
CAR (capital adequacy ratio is the comparison of the capital that is available to a bank on hand to its risk
weighted asset. Banks in general have a CAR of 8%-10%. A high ratio indicates a better position that a
bank can deal with unexpected losses due to availability of adequate capital. CBL has CAR more than
10% for the last 5 years.
ROE (18-22) 8.2% 9.9% 14.8% 15.8% 14.1%
ROE
0.18
15.80%
0.16
14.80%
14.10%
0.14
0.12
9.90%
0.1
8.20%
0.08
0.06
0.04
0.02
0
2018 2019 2020 2021 2022
Net interest margin (18-22) 4.1% 5.0% 3.7% 4.5% 4.3%
Net Interest Margin(%)
0.06
5.00%
0.05
4.50%
4.30%
4.10%
0.04 3.70%
0.03
0.02
0.01
0
2018 2019 2020 2021 2022
Liquidity coverage ratio (22-18) 220.1% 151.1% 173.5%
Liquidity Coverage Ratio(%)
250
220.1
200
173.5
151.1
150 142.6
116.94
100
50
0
2018 2019 2020 2021 2022
Debt to income ratio