Ask Basic Finance Expert

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

Basic Finance, Finance

  • Category:- Basic Finance
  • Reference No.:- M92755836
  • Price:- $90

Guranteed 48 Hours Delivery, In Price:- $90

Have any Question?


Related Questions in Basic Finance

Question utilizing the concepts learned throughout the

Question: Utilizing the concepts learned throughout the course, write a Final Paper on one of the following scenarios: • Option One: You are a consultant with 10 years experience in the health care insurance industry. A ...

Discussion your initial discussion thread is due on day 3

Discussion: Your initial discussion thread is due on Day 3 (Thursday) and you have until Day 7 (Monday) to respond to your classmates. Your grade will reflect both the quality of your initial post and the depth of your r ...

Question financial ratios analysis and comparison

Question: Financial Ratios Analysis and Comparison Paper Prior to completing this assignment, review Chapter 10 and 12 in your course text. You are a mid-level manager in a health care organization and you have been aske ...

Grant technologies needs 300000 to pay its supplier grants

Grant Technologies needs $300,000 to pay its supplier. Grant's bank is offering a 210-day simple interest loan with a quoted interest rate of 11 percent and a 20 percent compensating balance requirement. Assuming there a ...

Franks is looking at a new sausage system with an installed

Franks is looking at a new sausage system with an installed cost of $375,000. This cost will be depreciated straight-line to zero over the project's five-year life, at the end of which the sausage system can be scrapped ...

Market-value ratios garret industries has a priceearnings

(?Market-value ratios?) Garret Industries has a? price/earnings ratio of 19.46X a. If? Garret's earnings per share is ?$1.65?, what is the price per share of? Garret's stock? b. Using the price per share you found in par ...

You are planning to make annual deposits of 4440 into a

You are planning to make annual deposits of $4,440 into a retirement account that pays 9 percent interest compounded monthly. How large will your account balance be in 32 years?  (Do not round intermediate calculations a ...

One year ago you bought a put option on 125000 euros with

One year ago, you bought a put option on 125,000 euros with an expiration date of one year. You paid a premium on the put option of $.05 per unit. The exercise price was $1.36. Assume that one year ago, the spot rate of ...

Common stock versus warrant investment tom baldwin can

Common stock versus warrant investment Tom Baldwin can invest $6,300 in the common stock or the warrants of Lexington Life Insurance. The common stock is currently selling for $30 per share. Its warrants, which provide f ...

Call optionnbspcarol krebs is considering buying 100 shares

Call option  Carol Krebs is considering buying 100 shares of Sooner Products, Inc., at $62 per share. Because she has read that the firm will probably soon receive certain large orders from abroad, she expects the price ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

Why might a bank avoid the use of interest rate swaps even

Why might a bank avoid the use of interest rate swaps, even when the institution is exposed to significant interest rate

Describe the difference between zero coupon bonds and

Describe the difference between zero coupon bonds and coupon bonds. Under what conditions will a coupon bond sell at a p

Compute the present value of an annuity of 880 per year

Compute the present value of an annuity of $ 880 per year for 16 years, given a discount rate of 6 percent per annum. As

Compute the present value of an 1150 payment made in ten

Compute the present value of an $1,150 payment made in ten years when the discount rate is 12 percent. (Do not round int

Compute the present value of an annuity of 699 per year

Compute the present value of an annuity of $ 699 per year for 19 years, given a discount rate of 6 percent per annum. As