ASSIGNMENT 6

Executive Summary Your goal is to build a DSS that can be used for “stress testing banks” using the approach described in Case 7 of your textbook (see p. 134). After you build the DSS, you will subject a bank to 4 different macroeconomic scenarios. The results of this stress test have to be interpreted in order to decide whether a particular bank is ready for the 4 scenarios or not. Deliverables 1. Please submit an Excel work sheet with the following tabs: a. Tab 1 named “Stress Test DSS”. This tab should contain the DSS model described in Case 7 of your textbook (see p. 134). You can use the “skeleton file” uploaded with this assignment to get started. b. Tab 2 named “Scenario Summary”. This tab should contain summaries of the 4 scenarios (see p. 14). This tab should also contain a short (a few sentences) interpretation of the results of the four scenarios together with your overall recommendation or conclusion given those results. You may use a chart to aid your analysis, but this is optional. Cam 7 inputs Section in e.,od tune, .nd'b.rn'we's "r nigh nst, 4,7'1 Sowee soorenvers used sum ommosen from Mem.. C.w.o. FIGURE 741 inputs sect, • NIsr-k-er "Sr - Norms1)--Loter soh. fro the assumed °Lath, of the durin The thre,ye ,, period Summary of Key Results Section figure. SUMMARY OF KEY RESULTS 2015 End-0,,,,re. Cu. On nerid End-0,11,e-vem Snort Tem. Deet Owed 2,15 2017 Saxes_ Shprosok pro.. soreenshots used MP, permission from Shoo. Copomnon RGURE 7-5 Summary of KeY Results sem. For each year. your spread.shect should show net income after taxes. cash on hand at the end of the year. short-term debt owed at the end of the year. and whether the bank pas.sed or faikd the stress test for the year. The nct income. cash, and debt cells should be formatted as currency with no decimals The passifail cells should he formatted for text. All values are computed elsewhere in the spreadsheet and should be echoed here. Calculations Section You should calculate intermediate results that will be used in the income and cash tlow statements that ibllow Calculations. as shown in Figure 7-6. may be based on expwed year-end 2015 values. When called for. use absolute referencing properly. Values must be computed by cell formula; hard-code num,. in for-mulas only when you are told to do so. Cell formulas should not reference a cell uith a value of -NA.- which stands for -not applicable." An explanation of each kelt] in this section follows the figure.