Bank Management Assignment -
Overview of Requirements
Form into teams of three students. The teams must not mix students from years 2 and 3. I expect team members to show courtesy and respect towards one another.
Prepare an Analyst Report on the team's findings from an analysis of the financial statements of two banks. The data are in the Excel spreadsheet called "Assign 2017-18.xlsx".
The report should be around 2,000 words. It should include the tables and charts required by the analysis.
Requirements -
Your assignment for Bank Management will deepen your knowledge of how analysts examine financial statement data to evaluate the performance of financial institutions. The assignment will sharpen your analytical skills and your ability to work comfortably with Excel, which is widely used in the banking and financial industry. Thus, the assignment is an excellent opportunity for you to develop your transferable skills as well as to improve your academic knowledge.
The assignment requires an analysis of the consolidated financial statements of two Global Systemically Important Banks, namely, the Industrial and Commercial Bank of China and Citigroup for the period from 2011 to 2015. The data are in an Excel spreadsheet called "Assign 2017-18.xlsx", which is in the Blackboard. The denomination of the data is millions of US dollars.
Your task requires you to construct indicators of aspects of bank performance, and to write a comparative analysis based on trends in the data. Your analysis will reveal some of the trade-offs facing bank management by graphing the ratios that you will calculate.
Working in teams of three students, each group should create a separate sheet in the Excel file for each of the seven tasks listed below. For example, the profitability analysis requires you to construct return on assets. In the "Profitability" sheet, you should enter the following:
=ICBC!B44/ICBC!B$98
Where ICBC is the worksheet containing the financial statements of that bank; B44 is the cell containing net income in 2011; and B98 is the cell containing total assets in 2011.
Please express the ratios in percentages using the % instruction. Please leave the cells in formula format i.e. do not use paste special to convert formulae to values. Please insert graphs into the appropriate sheet.
Please use a Word document to present tables and graphs, and to discuss your findings. Please submit your reports and Excel file into TurnItIn. Failure to submit both the Word file and Excel file (in formulae and not values) will result in a zero grade for the assignment. Please ensure the names and student identification numbers of each student are included on the title page of the Report.
1. Balance sheet structure and percentages
The first sheet in the spreadsheet shows the financial statements, sourced from Orbis Bank Focus. Click the + and create a new sheet. Label this sheet using the name of the bank and the percentage symbol, for example, "ICBC %". Express the income statement and balance sheet as percentages of total assets for each year.
Hint: use the $ to lock cells i.e. if total assets in 2011 are found in column D row 98 and gross loans in row 82, the ratio of gross loans-to-assets in Excel language will be =D82/D$98 which means you can copy the formula across the items in the financial statements. Since the original data for ICBC are in the sheet "ICBC", create a new sheet called "ICBC %) and copy the names of the financial statements items (i.e. columns A to C) from "ICBC" and paste into "ICBC %".
To express each item of the income statement and balance sheet as a percentage of total assets, put the cursor on the first item in "ICBC %", i.e., "Interest on loans". Enter the "=" sign and move to the sheet "ICBC". Click on "interest on loans" and then enter the sign for division, i.e., "/" and move the cursor to total assets. Press return. This will return you to "ICBC %" and the following formula will be in the formula bar =ICBC!D5/ICBC!D85. Insert a "$" sign into the denominator to hold it constant on total assets as follows, =ICBC!D5/ICBC!D$85. Click the "%" symbol and state the number to one decimal place. You can now drag or copy the formula.
1a: Lending and securities
Look at the balance sheet structure of both banks focusing attention on assets. Consider the loan portfolio of the banks. Which type of lending business, if any, is the dominant activity at each bank? Tabulate the percentage of loans-to-assets ratios by type of lending for 2011 to 2015 inclusive for each bank. What information does the table reveal about the similarities and differences across the banks' lending activities?
1b: Funding
Next, and based on the structure of liabilities, explain how the bank funds its asset-side activities. Insert a new sheet called "Funding" and examine the banks' funding structures in terms of core deposits versus volatile or non-core liabilities. To measure core deposits, copy the ratio of customer deposits-to-total assets from the sheets that show the percentages of each item in total assets. To measure non-core liabilities, subtract from the percentage of total liabilities both "Other liabilities and provisions" and "customer deposits".
Construct a table that shows the percentages of core deposits and non-core liabilities for the banks over time. What do the results tell you about bank funding structures and over time?
1c: Concentration of lending and securities
Insert a new sheet called "HHI". Calculate a measure of lending concentration using the Herfindahl Hirschman index (HHI). HHI is the sum of the squared shares of each type of loan in gross loans. In the sheet "HHI", calculate the percentage share of mortgage loans-to-gross loans, consumer loans-to-gross loans and so on, and sum to find the HHI for each year. For example, for ICBC and mortgage loans, enter the following instruction into the command line in Excel: =100*(ICBC!D65/ICBC!D$69). Use ctrl h to see the search and replace box. In the find box type "icbc" and in the replace box type "hsbc" and the cells will automatically change (Do not put "" around the bank identifiers).
Tabulate the results for each year and each bank. What do you infer from the table about the concentration of lending between banks and over time?
Examine the securities of the banks and construct the HHI for the items financial assets: trading at fair value through P/L; financial assets: available for sale; financial assets: held to maturity; other securities. Tabulate the percentages and HHI and describe what you observe.
Copy "Total earning assets" into sheet "HHI". Calculate the ratio of "Net loans"-to-"Total earning assets". Calculate "Securities" as 1 - the ratio of "Net loans"-to-"Total earning assets". Show both ratios in percentage format and calculate ratio of total earning assets-to-total assets in percentage to one decimal place. What do the results tell you about the structure of each bank's balance sheet?
2. Profitability analysis
Insert a new sheet called "Profitability" and carry out a profitability analysis of each bank over time.
Calculate the Net Interest Margin (net interest income-to-total earning assets). NB: you will have calculated total earning assets in sheet "HHI". Therefore, NIM is =ICBC!D$15/HHI!T5. Show the margin to two decimal places. Calculate return on assets and return on equity using net income as the numerator. Present the results in three graphs showing the evolution of each indicator for the banks over time. Describe what you observe and outline what you believe to be challenges for the banks.
Calculate the interest spread for each bank and each year. Construct the interest spread as the ratio of "Total interest received"-to-"Total earning assets" minus the ratio of "Total interest paid"-to-"Total liabilities". Express the result as a percentage to two decimal places. Calculate the loan spread and deposit spread. For the former, construct the indicator as the ratio of "Interest on loans"-to-"Gross loans", and the latter as the ratio of "Interest on customer deposits"-to-"Customer deposits". Express the results as percentages to two decimal places. Tabulate the results and discuss what you find.
Hint: Total earning assets has been reported in the sheet "HHI". Copy the results into the sheet "Profitability" and use the "=" sign to copy total liabilities for each bank into the row beneath TEA. This will make it easier for you to see the construction of the interest spreads.
3. Leverage
Construct the equity multiplier or leverage ratio (total assets-to-equity) for each year and bank.
Using ROE decomposition, prove the relationship between ROE, leverage and ROA. Copy ROE from the sheet "Profitability" to compare the result for ROE from the ROE decomposition with the ROE computed directly from the financial statements. They should be identical.
Graph leverage and ROE for the banks over time. What information do the graphs tell you about each series and the differences and similarities between the banks? Use a clustered line graph.
4. Liquidity
As part of liquidity management banks must maintain sufficient liquid assets to meet expected and unexpected customer demand for withdrawals of cash. However, the yield on liquid assets is less than that on less liquid assets, which implies there is a trade-off between bank liquidity and bank profitability.
In a new sheet called "Liquidity", construct a measure of liquid assets as the sum of "Cash & balances with central banks", "Net loans and advances to banks", and "Reverse repos, securities borrowed and cash collateral". Show the ratios of liquid assets-to-total assets, and liquid assets-to-customer deposits. From the sheet "Profitability", copy the ROE ratios for the banks.
Use a clustered line graph to show the trade-off between liquidity (liquid assets-to-customer deposits) and profitability (ROE) for each bank over time. What information do the graphs tell you about each series and the differences and similarities between the banks?
5. Capital
Create a new sheet "Capital" and copy the Tier 1 capital ratios for the banks into the sheet. (For ICBC, you must construct the indicator as the ratio of "Tier 1 capital"-to-"Total risk weighted assets - transitional". Copy the bank ROE data and create graphs that show the trade-off between solvency (tier 1 ratio) and profitability (ROE). Discuss your results.
6. Efficiency
Construct the cost-to-income ratio, which is a crude measure of bank efficiency (total operating expenses-to- operating revenues). Plot the cost-to-income ratio. Draw a line graph to show the trend in bank efficiency. Include a data table on the graph and start the y-axis at 20%. Which bank is more efficient and what do the trends in bank efficiency suggest?
7. Asset quality
Construct indicators of bank asset quality. First, construct the ratio of "Total impaired/nonperforming loans"- to-"gross loans". Second, construct the ratio of "Net charge offs"-to-"Net income + Total impairment charges". Draw a clustered line graph to show the two asset quality indicators for individual banks. Describe your results.
8. Stability analysis
Calculate the Z score indicator of bank stability in a new sheet called "Stability". From the sheet "Profitability", copy the ROA data for each bank. From the financial statements, construct the ratio of "Total equity"-to-"Total assets". You will need the standard deviation of bank profitability for a bank across the period. Calculate this using the STDEV command. Use the three pieces of information to calculate the Z score for each bank over time.
Draw a bar chart to show the results. Discuss your findings. Which bank is more stable and what do you consider is the driving force behind individual bank Z scores?
Attachment:- Assignment Files.rar