Read Case 7: The Very Big Bank Stress Test in pages 135-144 of "Problem-Solving Cases in Microsoft Access and Excel", 13th Edition, 2016

https://books.google.com.hk/books?id=rEJ-BAAAQBAJ&pg=PA135&lpg=PA135&dq=case+7:the+very+big+bank+stress+test+excel&source=bl&ots=EpRXW5Yktu&sig=ACfU3U2DbU7c9WLm3o9La1jwQvylzym5CQ&hl=en&sa=X&ved=2ahUKEwijvOermJjtAhVSw4sBHZzrAtoQ6AEwCXoECAYQAg#v=onepage&q=case%207%3Athe%20very%20big%20bank%20stress%20test%20excel&f=false

• Finish the assignments in pages 135-143 (from Assignment 1 up to the end of Assignment 2A only; completion of Assignment 2B is not required);
• Your final Excel file should contain two sheets: (1) your Excel DSS model that deals with Assignment 1; (2) the Scenarios Manager summary sheet that summarises results of 4 scenarios in Assignment 2.

copy the link and paste it on Google to read the book

A B D E F G 2015 NA NA NA NA NA NA $ $ $ $ $ 2016 10% 50,000 $ 975,000 $ 200,000 $ 220,000 $ 120,000 $ 2017 10% 50,000 $ 990,000 $ 210,000 $ 240,000 $ 120,000 $ 2018 10% 50,000 1,000,000 220,000 260,000 120,000 2016 2017 2018 2015 NA NA NA NA 2016 2017 2018 THE VERY BIG BANK STRESS TEST ($ in millions) 3 CONSTANTS Tax Rate 5 Cash Needed To Start Year 5 Deposits - Trading Account Assets 3 Long Term Debt Owed Base Equity for Test Purposes 0 1 INPUTS 2 Market Volatility ("H" = High, "N" = Normal) 3 Economy ("G" = Good, "O" = OK, "P" = Poor) 4 5 SUMMARY OF KEY RESULTS 6 Net Income After Taxes 7 End-Of-The-Year Cash On Hand 8 End-Of-The-Year Short Term Debt Owed 9 Pass / Fail Stress Test 0 1 CALCULATIONS 2 Consumer Loans 3 Corporate Loans 4 Interest Rate on Consumer Loans 5 Interest Rate on Corporate Loans 6 Interest Rate on Trading Assets 7 Interest Rate Paid on Deposits 8 Interest Rate Paid on Short Term Debt 9 Interest Rate Paid on Long Term Debt o Trading Account Expense Ratio 1 Loan Loss Percentage 2015 NA NA NA NA 2016 2017 2018 $ $ 2015 500,000 400,000 NA NA NA NA NA NA NA NA INCOME STATEMENT AND CASH FLOW STATEMENT Beginning-Of-The-Year Cash On Hand 2016 2017 2018 2015 NA NA NA NA NA Revenue Interest on Consumer Loans Interest on Corporate Loans Trading Account Revenue Total Revenue Expenses Interest on Deposits Interest on Short Term Debt Interest on Long Term Debt Trading Account Expense Loan Losses NA NA NA NA NA NA NA Total Expenses Income before Taxes Income Tax Expense NA NA NA Net Income After Taxes NA 51 NA NA NA $ 300,000 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 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 2016 2017 2018 2015 NA NA NA $ 60,000 62 2015 2016 2017 2018 63 FINAL STRESS TEST COMPUTATIONS 64 Return on Base Equity 65 Pass / Fail Stress Test NA NA A B D E F G 2015 NA NA NA NA NA NA $ $ $ $ $ 2016 10% 50,000 $ 975,000 $ 200,000 $ 220,000 $ 120,000 $ 2017 10% 50,000 $ 990,000 $ 210,000 $ 240,000 $ 120,000 $ 2018 10% 50,000 1,000,000 220,000 260,000 120,000 2016 2017 2018 2015 NA NA NA NA 2016 2017 2018 THE VERY BIG BANK STRESS TEST ($ in millions) 3 CONSTANTS Tax Rate 5 Cash Needed To Start Year 5 Deposits - Trading Account Assets 3 Long Term Debt Owed Base Equity for Test Purposes 0 1 INPUTS 2 Market Volatility ("H" = High, "N" = Normal) 3 Economy ("G" = Good, "O" = OK, "P" = Poor) 4 5 SUMMARY OF KEY RESULTS 6 Net Income After Taxes 7 End-Of-The-Year Cash On Hand 8 End-Of-The-Year Short Term Debt Owed 9 Pass / Fail Stress Test 0 1 CALCULATIONS 2 Consumer Loans 3 Corporate Loans 4 Interest Rate on Consumer Loans 5 Interest Rate on Corporate Loans 6 Interest Rate on Trading Assets 7 Interest Rate Paid on Deposits 8 Interest Rate Paid on Short Term Debt 9 Interest Rate Paid on Long Term Debt o Trading Account Expense Ratio 1 Loan Loss Percentage 2015 NA NA NA NA 2016 2017 2018 $ $ 2015 500,000 400,000 NA NA NA NA NA NA NA NA INCOME STATEMENT AND CASH FLOW STATEMENT Beginning-Of-The-Year Cash On Hand 2016 2017 2018 2015 NA NA NA NA NA Revenue Interest on Consumer Loans Interest on Corporate Loans Trading Account Revenue Total Revenue Expenses Interest on Deposits Interest on Short Term Debt Interest on Long Term Debt Trading Account Expense Loan Losses NA NA NA NA NA NA NA Total Expenses Income before Taxes Income Tax Expense NA NA NA Net Income After Taxes NA 51 NA NA NA $ 300,000 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 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 2016 2017 2018 2015 NA NA NA $ 60,000 62 2015 2016 2017 2018 63 FINAL STRESS TEST COMPUTATIONS 64 Return on Base Equity 65 Pass / Fail Stress Test NA NA