This question is from "Problem Solving Cases in Microsoft Access and Excel (13th Edition) Case 7, 2AB"

Can you write a memo in MS Word documenting the analysis and address it to the bank's president?

Summarize the results by using a table, Address the following questions and issues management has.

Should bank management be concerned if the Bank Regulators conduct a surprise "stress test"?

Does the bank have a possibility of failing? If so, under what conditions and when?

How long would the bank last in a worst case scenario? How much additional cash would the bank need to survive all three years? (Use a real number from the worksheet)

2015 NA NA NA 2016 2017 2018 10% 10% 10% $ 50,000 $ 50,000 $ 50,000 $ 975,000 $ 990,000 $1,000,000 $ 200,000 $ 210,000 $ 220,000 $ 220,000 $ 240,000 $ 260,000 $ 120,000 $ 120,000 $ 120,000 NA NA NA 1 THE VERY BIG BANK STRESS TEST 2 ($ in millions) 3 CONSTANTS 4 Tax Rate 5 Cash Needed To Start Year 6 Deposits 7 Trading Account Assets 8 Long Term Debt Owed 9 Base Equity for Test Purposes 10 11 INPUTS 12 Market Volatility ("H" = High, "N" = Normal) 13 Economy ("G" = Good, "O" = OK, "P" = Poor) 14 15 SUMMARY OF KEY RESULTS 16 Net Income After Taxes 17 End-Of-The-Year Cash On Hand 18 End-Of-The-Year Short Term Debt Owed 19 Pass / Fail Stress Test 20 2015 NA NA 2016 H G 2017 2018 NA NA 0 LP 2015 NA NA NA NA 2016 $ 57,173 $ $ 300,000 $ $ - $ PASS 2017 46,745 $ 343,918 $ - $ PASS 2018 (168,410) 175,508 HOLD 21 CALCULATIONS 22 Consumer Loans 23 Corporate Loans 24 Interest Rate on Consumer Loans 25 Interest Rate on Corporate Loans 26 Interest Rate on Trading Assets 27 Interest Rate Paid on Deposits 28 Interest Rate Paid on Short Term Debt 29 Interest Rate Paid on Long Term Debt 30 Trading Account Expense Ratio 31 Loan Loss Percentage 32 INCOME STATEMENT AND 33 CASH FLOW STATEMENT 34 Beginning-Of-The-Year Cash On Hand 35 36 Revenue 37 Interest on Consumer Loans 38 Interest on Corporate Loans 39 Trading Account Revenue 40 Total Revenue 2015 2016 2017 $ 500,000 $ 535,000 $ 545,700 $ $ 400,000 $ 420,000 $ 424,200 $ NA 14.0% 14.0% NA 8.0% 8.0% NA 10.0% 10.0% 1.5% 1.0% NA 1.5% 1.0% NA 4.0% 3.5% NA 60% 60% NA 3% 5% 2018 523,872 415,716 10.0% 7.0% 10.0% 0.5% 0.5% 3.0% 110% 25% NA 2015 NA 2016 $ 300,000 $ 2017 297,173 $ 2018 343,918 NA NA NA $ 74,900 $ 76,398 $ $ 33,600 $ 33,936 $ $ 20,000 $ 21,000 $ $ 128,500 $ 131,334 $ 52,387 29,100 22,000 103,487 NA NA 5,000 NA 41 Expenses 42 Interest on Deposits 43 Interest on Short Term Debt 44 Interest on Long Term Debt 45 Trading Account Expense 46 Loan Losses 47 Total Expenses 48 Income before Taxes 49 Income Tax Expense 50 Net Income After Taxes NA NA NA NA NA $ $ $ $ $ $ $ $ $ 14,625 $ 900 $ 8,800 $ 12,000 $ 28,650 $ 64,975 $ 63,525 $ 6,353 $ 57,173 $ 9,900 $ . $ 8,400 $ 12,600 $ 48,495 $ 79,395 $ 51,939 $ 5,194 $ 46,745 $ 7,800 24,200 234,897 271,897 (168,410) (168,410) 51 NA 175,508 Net Cash Position (NCP) Before Borrowing and Repayment of Debt (Beginning of 52 Year Cash + Net Income) 53 Add: Increase in Borrowing 54 Less: Repayment of Debt 55 Equals: End-Of-The-Year Cash On Hand 56 NA NA $ 300,000 $ 357,173 $ 343,918 $ $ - $ - $ $ 60,000 $ - $ $ 297,173 $ 343,918 $ 175,508 NA Net Cash Position (NCP) Before Borrowing and Repayment of Debt (Beginning of 52 Year Cash + Net Income) 53 Add: Increase in Borrowing 54 Less: Repayment of Debt 55 Equals: End-Of-The-Year Cash On Hand 175,508 - NA NA $ 300,000 $ 357,173 $ 343,918 $ $ - $ - $ $ 60,000 $ $ 297,173 $ 343,918 $ 175,508 56 2016 60,000 2018 $ 57 SHORT TERM DEBT OWED 58 Beginning-Of-The-Year Short Term Debt Owed 59 Add: Increase in Borrowing 60 Less: Repayment of Debt 61 Equals: End-Of-The-Year Short Term Debt Owed 2015 NA $ NA $ NA $ 60,000 $ 60,000 $ $ 63 FINAL STRESS TEST COMPUTATIONS 64 Return on Base Equity 65 Pass / Fail Stress Test 2015 NA NA $ 2016 0.48 PASS $ 2017 0.39 $ PASS 2018 (1.40) HOLD 2015 NA NA NA 2016 2017 2018 10% 10% 10% $ 50,000 $ 50,000 $ 50,000 $ 975,000 $ 990,000 $1,000,000 $ 200,000 $ 210,000 $ 220,000 $ 220,000 $ 240,000 $ 260,000 $ 120,000 $ 120,000 $ 120,000 NA NA NA 1 THE VERY BIG BANK STRESS TEST 2 ($ in millions) 3 CONSTANTS 4 Tax Rate 5 Cash Needed To Start Year 6 Deposits 7 Trading Account Assets 8 Long Term Debt Owed 9 Base Equity for Test Purposes 10 11 INPUTS 12 Market Volatility ("H" = High, "N" = Normal) 13 Economy ("G" = Good, "O" = OK, "P" = Poor) 14 15 SUMMARY OF KEY RESULTS 16 Net Income After Taxes 17 End-Of-The-Year Cash On Hand 18 End-Of-The-Year Short Term Debt Owed 19 Pass / Fail Stress Test 20 2015 NA NA 2016 H G 2017 2018 NA NA 0 LP 2015 NA NA NA NA 2016 $ 57,173 $ $ 300,000 $ $ - $ PASS 2017 46,745 $ 343,918 $ - $ PASS 2018 (168,410) 175,508 HOLD 21 CALCULATIONS 22 Consumer Loans 23 Corporate Loans 24 Interest Rate on Consumer Loans 25 Interest Rate on Corporate Loans 26 Interest Rate on Trading Assets 27 Interest Rate Paid on Deposits 28 Interest Rate Paid on Short Term Debt 29 Interest Rate Paid on Long Term Debt 30 Trading Account Expense Ratio 31 Loan Loss Percentage 32 INCOME STATEMENT AND 33 CASH FLOW STATEMENT 34 Beginning-Of-The-Year Cash On Hand 35 36 Revenue 37 Interest on Consumer Loans 38 Interest on Corporate Loans 39 Trading Account Revenue 40 Total Revenue 2015 2016 2017 $ 500,000 $ 535,000 $ 545,700 $ $ 400,000 $ 420,000 $ 424,200 $ NA 14.0% 14.0% NA 8.0% 8.0% NA 10.0% 10.0% 1.5% 1.0% NA 1.5% 1.0% NA 4.0% 3.5% NA 60% 60% NA 3% 5% 2018 523,872 415,716 10.0% 7.0% 10.0% 0.5% 0.5% 3.0% 110% 25% NA 2015 NA 2016 $ 300,000 $ 2017 297,173 $ 2018 343,918 NA NA NA $ 74,900 $ 76,398 $ $ 33,600 $ 33,936 $ $ 20,000 $ 21,000 $ $ 128,500 $ 131,334 $ 52,387 29,100 22,000 103,487 NA NA 5,000 NA 41 Expenses 42 Interest on Deposits 43 Interest on Short Term Debt 44 Interest on Long Term Debt 45 Trading Account Expense 46 Loan Losses 47 Total Expenses 48 Income before Taxes 49 Income Tax Expense 50 Net Income After Taxes NA NA NA NA NA $ $ $ $ $ $ $ $ $ 14,625 $ 900 $ 8,800 $ 12,000 $ 28,650 $ 64,975 $ 63,525 $ 6,353 $ 57,173 $ 9,900 $ . $ 8,400 $ 12,600 $ 48,495 $ 79,395 $ 51,939 $ 5,194 $ 46,745 $ 7,800 24,200 234,897 271,897 (168,410) (168,410) 51 NA 175,508 Net Cash Position (NCP) Before Borrowing and Repayment of Debt (Beginning of 52 Year Cash + Net Income) 53 Add: Increase in Borrowing 54 Less: Repayment of Debt 55 Equals: End-Of-The-Year Cash On Hand 56 NA NA $ 300,000 $ 357,173 $ 343,918 $ $ - $ - $ $ 60,000 $ - $ $ 297,173 $ 343,918 $ 175,508 NA Net Cash Position (NCP) Before Borrowing and Repayment of Debt (Beginning of 52 Year Cash + Net Income) 53 Add: Increase in Borrowing 54 Less: Repayment of Debt 55 Equals: End-Of-The-Year Cash On Hand 175,508 - NA NA $ 300,000 $ 357,173 $ 343,918 $ $ - $ - $ $ 60,000 $ $ 297,173 $ 343,918 $ 175,508 56 2016 60,000 2018 $ 57 SHORT TERM DEBT OWED 58 Beginning-Of-The-Year Short Term Debt Owed 59 Add: Increase in Borrowing 60 Less: Repayment of Debt 61 Equals: End-Of-The-Year Short Term Debt Owed 2015 NA $ NA $ NA $ 60,000 $ 60,000 $ $ 63 FINAL STRESS TEST COMPUTATIONS 64 Return on Base Equity 65 Pass / Fail Stress Test 2015 NA NA $ 2016 0.48 PASS $ 2017 0.39 $ PASS 2018 (1.40) HOLD