Go to the Federal Deposit Insurance Corporation Website at

Go to the Federal Deposit Insurance Corporation Website at fdic.gov and find the latest balance sheet information available for commercial banks. To do this, click on Industry Analysis. Then click Bank Data and Statistics. Then click on "Statistics on Depository Institutions (SDI)" On the next page click the "Enter SDI" button at the bottom.

Run a report with the following criteria in both columns:

Type of Report:  Dollars/Percent of Assets

# of columns: 2

Standard Peer Group

All Institutions

National

Report Date: March 31, 2010 in Left column and use March 31, 2014 in Right column

Click "Next"

Click "Next" again on the next page leaving Income Basis as YTD and Report as Assets and Liabilities

Once the reprt is generated click "Save to Excel File".  Excel will warn you about a problem with the format, but just accept it.

Do the following to this report you now have in Excel:

1. Calculate the percentage change in total assets for the commericial bank industry since 2010.

2. Calculate the percent of investment securities to total assets, loans to total assets, deposits to total assets, and equity to total assests for 2010 and 2014.

3. Calculate the change in these ratios between 2010 and 2014 and insert a textbox to comment on the change.

Label each result and make sure that the relevant cells contain formulas.

*Notice that you have some choice in the presentation-- again, make it clear and easy to read. Points are awarded for this quality.

4. Create a pivot table utilizing the four ratios and change in these ratios over the four year period.

5. Insert a V-lookup table for the Hybrid Life Insurance assets and the Tier 2 based risk capital.

Go to the Federal Deposit Insurance Corporation Website at fdic.gov and find the latest balance sheet information available for commercial banks. To do this, click on Industry Analysis. Then click Bank Data and Statistics. Then click on "Statistics on Depository Institutions (SDI)" On the next page click the "Enter SDI" button at the bottom.

Run a report with the following criteria in both columns:

Type of Report:  Dollars/Percent of Assets

# of columns: 2

Standard Peer Group

All Institutions

National

Report Date: March 31, 2010 in Left column and use March 31, 2014 in Right column

Click "Next"

Click "Next" again on the next page leaving Income Basis as YTD and Report as Assets and Liabilities

Once the reprt is generated click "Save to Excel File".  Excel will warn you about a problem with the format, but just accept it.

Do the following to this report you now have in Excel:

1. Calculate the percentage change in total assets for the commericial bank industry since 2010.

2. Calculate the percent of investment securities to total assets, loans to total assets, deposits to total assets, and equity to total assests for 2010 and 2014.

3. Calculate the change in these ratios between 2010 and 2014 and insert a textbox to comment on the change.

Label each result and make sure that the relevant cells contain formulas.

*Notice that you have some choice in the presentation-- again, make it clear and easy to read. Points are awarded for this quality.

4. Create a pivot table utilizing the four ratios and change in these ratios over the four year period.

5. Insert a V-lookup table for the Hybrid Life Insurance assets and the Tier 2 based risk capital.