financial projection model: step-by-step -- introductory...

Financial Projection Model: Step-by-Step -- Introductory Case

NOTE: Text within brackets [ ] refer to worksheets by that title within the FPM in Excel. A capital letter followed by a number

denotes cells in the worksheets.

Case Copy-Right Reserved. 1 of 32

PURPOSE

Designed as an introduction to the FPM‟s core capabilities and basic operations, the following

steps will take you through running an elemental projection without any previous background.

You will be projecting only a local currency (LC.CY.) book. Later on we will introduce a

simplified foreign currency (FG.CY.) balance sheet, time permitting. Detailed technical

references can be found separately in the User‟s Guide (our real aim is that you lose your fear of

using the FPM!). Keep in mind that you are using a compiled binary version of the FPM.

Every time you use the FPM to implement these steps, keep in mind that real life is much more

complicated and that the „Devil is in the details‟. It is through systematic analysis that you

understand and discover how much you do or do not know about a particular bank. Then,

there is your candor and curiosity to understand how the banker operates and how he is

controlling the performance of his businesses. Therefore, it is crucial that you understand well

the complexity, reliability, and integrity with which the banker‟s management information

systems and accounts (not the set of prudential returns he reports to us) reflect the contribution

of his bank‟s business lines and activities to earnings, risks, and capital. The FPM is a

simplification to approximate real life.

Case Background:

You have been selected as a candidate to serve on the Board of Directors of Krakovia‟s Zero Bank. The board is going to have a preparatory meeting with a few professional bankers identified as key senior managers, including a financial controller and a seasoned lending officer. The purpose of the meeting is to foresee the viability of incorporating Zero Bank. The board would like to develop a very rough zero budget vision for further discussion and for attracting additional potential investors of paid-in capital into the bank. Together with the directors, the pros are going to prepare an assessment of feasibility to evaluate whether it makes sense to set up and capitalize a bank in Krakovia (which surprisingly has no banks today in operation). Board and pros are going to use a simple excel spreadsheet available to the effect (of course, none other than the FPM!).





PREPARATION

Step 1: Ensure that Excel is set for manual recalculation with iterations

· In the top left corner in an Excel screen, click on the Office Button, then “Excel Options” in

the bottom right corner. Click on “Formula” in the list of menu items, then click a check on

“Manual” and “Enable iterative calculation” (say at “100”).

· (In pre-Windows Vista versions of Excel, you will find the properties under “Tools”; then

“Options”; then click on the “Calculation” tab.)

· Leave the “Recalculate workbook before saving” box unchecked.

· Save a copy of the file to preserve the original intact (rename it “Zero Bank”).

Step 2: Review entry sheets of the “zero version” FPM, and set up the [FPM Cover]

· Get comfortable navigating the sheets, from [Macro] to [Assumptions] and to [Summary].

· Review the yellow cells in the entry sheets and ensure that they are filled with zeroes.

· Look into [Macro], [Entry], [Assumptions-BS], and [Assumptions-PLA].

· Leave the few cells filled with “1”; [Macro] lines 8 and 9 should never be zero.

· See that all [Summary] cells are at zero. See the blocks of columns as available reports.

· Select the desired frequency in the 12 periods in [FPM Cover] line 5 – “Annual” in this case.

· Enter the name for the bank in H2. Select the desired scenario in [FPM Cover] H6, say “1”.

· See that [FPM Cover] H12 is at “0” meaning that no “Actions” are being considered.

· Set cell H13 at “1” to ensure you are projecting a single entity (only one bank).

· Ensure that [Macro] lines 169 and 170 have a “1” (the scalar to simulate a “downturn”).

· FPM is governed by several of these choices (listed at bottom of [FPM Cover] sheet. See

Annex 4 in the User‟s Guide for more information).

· Press <F9> to recalculate FPM at zeros and see that no errors appear in the spreadsheet.

· Check that [Summary] I75 shows “0” (present value of dividends available for distribution).

SET UP OF A MACROECONOMIC SCENARIO AND INITIAL BALANCE SHEET

Step 3: Set up the most elemental lines needed in [Macro]

· Nominal GDP figure, the size of the Krakovian economy, say “10,000” in R16.

· The experts consulted believe it relatively easy to mobilize about 800 million in deposits.

· Enter the expected size of transactional deposits in LC.CY. in Krakovia to be “800” in R38.





· Zero Bank will be the only bank in Krakovia, so assume its local market share will be 100%.

· Enter then “800” in LC.CY. in R71, which is the expected market share of Zero Bank.

· Notice that we are using only one and the same line of transactional deposits.

· Select the product in which the FPM will allocate deposit growth: “100%” in AL84 to AW84.

· The lines in this section serve to simulate shifts among different deposit products.

· We will have a banking system with only one deposit line (FPM allows seven deposit lines).

Step 4: Create the initial balance sheet of our Zero Bank in [Entry]

· Assign the “800” units to the first line of the deposit products in line H85.

· Invest 100%,” 800” units, into the first line of credit products in H30.

· Change the names of both lines if you wish to distinguish them in the projection:

· Say, E85 to “One Line Deposits” and E29 to 33 to “One Line Loans”.

· Say the bank will have “80” units of common stock capital (H115) – 10% of loans.

· Invest the “80” units in Available for Sale Securities (H17) to keep capital liquid.

· In D17 enter “100%” indicating full eligibility of AFS as part of the liquidity ratio.

SELECTING BASIC REGULATORY AND MANAGEMENT POLICY VARIABLES

Step 5: Input elemental assumptions, in [Assumptions-BS], unless otherwise noted

· Set the regulatory capital adequacy required ratio in line 6 columns H to T to “10%”.

· Set the reinvestment rate in AFS to “9%” (dividing 80 by 880) in line 15 columns I to T.

· Set the risk weight for AFS securities to “0%” in D46 (implied risk of the government).

· Set at “100%” line 66, columns I to T to force reinvestment into our single line of loans.

· Set the grade allocation rule for new loans to “100%” investment in “A” loans, line 75

columns I to T so all projected fund flow will go to “A” loans of our “One Line Loans” only.

· Set the asset risk weight in D75 (“A”), D93 (“B”), and D122 (“C/D”) loans for the One Line

of loans to “100%”.

· In [Assumptions-PLA] line 36 columns H to T, “100%” for all AFS to be government debt.

Step 6: Recalculate the first projection, and review the results in [Summary]

· Go to [Summary] and “hit” <F9>. Notice what has happened. It is fully static since:

· We have not yet set growth, so the balance sheet is flat as we specified.

· We have not yet established a profit and loss account so there are no dynamics.





ADDING DYNAMICS TO THE PROJECTION

Step 7: Add expected real GDP growth and inflation for starters

· Lets us decide that GDP is expected to grow 2% in real terms in every subsequent period.

· Enter “2%” in [Macro] line 6, columns R and AL to AW (Scenario 1).

· Lets us decide that CPI is expected to be 3% in all subsequent periods in the same Scenario.

· Enter “3%” in line 7 in [Macro], columns R and AL to AW.

· With these figures, the deposits of the bank(ing system) will grow at a nominal 5%...

· …unless we modulate the relationship between nominal GDP and deposits in line 39.

· Alternatively, one could historically regress deposits to its economic drivers.

Step 8: Recalculate and review

· Go to [Summary] and recalculate by hitting <F9>.

· We grow in “One Line Deposits” only, while loans and AFS contribute to assets growth.

· The % of deposits to total assets is not constant (line 25, X to AJ) as capital loses weight.

· The capital adequacy decreases from 10% to about 5.8% (line 78).

· The mix AFS/Loans continues the same as we reinvest in the same proportion (9%/91%).

· Go to [Funds Flow] and see sources (lines 14-15) and uses (lines 9-10) of funds.

· Trace them to the balance sheet funds flow in [Projected-BS]: line 147 columns BU to CF,

and to the projected operational cash flow [Projected-PL]: line 103, columns I to T.

· Notice in [Summary] the Period 12 liquidity proxies in lines 108 (96%) and 110 (9.6%).

· If you apply a liquidity discount to AFS in [Entry] D17, the liquidity ratio will decrease.

GENERATING A PROFIT AND LOSS ACCOUNT

Step 9: Add a deposit rate, using a chosen reference rate and a spread

· Experts believe Zero Bank can attract depositors (?) with a rate slightly below inflation (for

simplicity since inflation is already in [Macro], but can use any other reference rate coded).

· [External] Column D lists the rates available to link the behavior of assets and liabilities.

· Note that CPI is coded as “2” in line 7 of that column D.

· In [Assumptions – PLA] D40, input “2” to choose CPI as the reference rate.

· This will link the interest rate for the “One Line Deposit” to the projected inflation rate.

· Let us pay 1% below the inflation rate.

· Enter “-1.0%” in line 42 columns I to T to set the spread for this deposit line.

· Go to [Summary]. Hit <F9>. See the P/L results, the cash flow, and how losses erode capital.





The selection of the rates of interest that will be used as reference rates in the projection is a

crucial step. These reference rates in the economy are used to link the rates of all interest

earning assets and interest bearing liabilities by means of spreads that you have to calculate

(estimate) as part of your initial analysis. In this case, we have selected the only rate we have:

inflation (CPI). You can build any other relevant reference rate with a view on how you expect

that rate to evolve consistently with the evolution of other macroeconomic aggregates and

policy variables. For example, you can build a government short-term rate which is on average

(1%) above inflation, or a prime rate that is zero and then the spreads of any interest earning

asset or interest bearing liability are the full rate applicable (an example will follow).

Step 10: Reflect on the effect of what we have done and how the FPM interprets it

· In [Summary], we have interest expenses, but no earnings at all (losses, in fact). · The losses erode capital and in a few periods the bank would be insolvent. · In [Funds Flow], the operational cash flow losses would burn out the new deposits. · The proxy ratios and spreads in [Summary] identify what happens with bank performance. · Note that all interest expense is cash paid since accrual in [Assumptions-BS] line 204 = 0%. · FPM uses this and 157 to project accruals and simulate changes in cash flow earnings.

Step 11: Add operational costs in [Assumptions-PLA] to forecast the overall expense level

· Double check that you are projecting a single entity and have “1” in [FPM Cover] H13. · Choose the option to relate operational costs to total assets of the bank:

· In [Entry], set C207 and C211 which govern this option to “1”. · This is the most common and simpler alternative for most projections. · You can learn later about the other options available by reading the Guide.

· Input simple operational cost assumptions as percentage of total assets: · Go to [Assumptions-PLA] and input in columns I to T, “1.5%” for staff cost in line 121

· Input “2%” for general expenses in line 134.

Step 12: Recalculate and review the outputs obtained in this round

· Go to [Summary] and hit <F9>. Review and reflect on the results. · Logically, things are much worse than before; the bank has much bigger losses. · Capital is consumed much faster with no earnings at all to cover the expenses. · Without any earnings, Zero Bank would have a negative net operating margin (NOM).





· This negative NOM starts at -5.4% of TA in Period 1 (Y60) and reaches -6.7%. · See [Funds Flow] in line 16, the bank loses more cash than the deposits it mobilizes.

· The bank is burning all its new resources to stay afloat. · There are no funds to re-invest in new loans and AFS until Period 7. · The bank has to price and charge interest on its loans to break-even on its costs.

· See how the bank would be financing its overall cash flow loss: · Note in [Capital Market] line 6 how the balance of AFS decreases. · The FPM is selling AFS to finance the overall net outflow of resources. · The FPM does this automatically since D13 in [Assumptions-BS] is set at “1”.

· We are not assuming any loss in selling AFS (but in real life there could be some losses). · Before adding the next part (we need to price the loans for revenue to cover the losses), we‟ll

ask: How will the net outflow of cash be financed if the bank runs out of AFS to sell? · Looking at this will explain how the mechanics of the FPM and [Funds Flow] work:

· [Funds Flow] is the heart of balancing the FPM and cash flows are its blood. · The next steps digress into a hypothetical liquidity situation to see how this works.

HOW FUND FLOW WORKS (This is Crucial!)

Step 12 bis 1: Use [Actions] to simulate an unbalanced deposits withdrawal

· FPM uses [Actions] to input absolute (not %) changes to assets and liabilities, including implementing restructuring decisions.

· Go to [FPM Cover] H12 and include all [Actions] in the next projection by inputting “1”. · We are going to simulate a deposit prepayment in Period 4, of say, 97 through [Actions]:

· Note in [Capital Market] line 6, the bank has 60 units of AFS at the end of Period 4. · So, in [Actions] L127, input “-97” to simulate the prepayment of about 10% of deposits.

· Notice that this is an unbalanced action and that FPM will balance the outflow by default. · Alternatively, we could balance it, entering a counterbalancing action in a given line. · Go to [Funds Flow] and hit <F9> to see the results of this [Action].

Step 12 bis 2: Follow in [Summary] line 25 what has happened in Period 4.

· We can see the combined effect of a 5% deposit growth (+46) on a stock of 926 in Period 3, with the prepayment (or was it a deposit run?) of -97.

· The result is a reduction of 51 in deposits to 875: (926 + 46 – 97 =875). · See [Funds Flow] L14 to 16: the bank has to finance a negative funds flow of -97.

· -46 come from operational losses of the period (see the P/L in [Summary] L60). · -51 come from the net loss of deposits (growth of 5%, or 46, and run of 97).





· FPM forces a sale of AFS, all 63 units available at end of Period 3, which can be seen in [Capital Market] line 6 - the reduction of AFS to zero.

· After selling all AFS, Zero Bank still has a net cash outflow of 33: see [Funds Flow] line 27. · Go to [Money Market] line 14 to see who is financing this net cash outflow of 33. · Go to [Funds Flow] and see how losses continue to burn the new deposits acquired. · Thus, FPM is investing (actually, wasting or burning) new deposits into financing losses. · Once the overall net flow is slightly positive in Period 5, FPM starts repaying the ELA. · This is only a dream: by then, the market will already know the trouble this bank is in and

have pulled out deposits. · As a consequence, there is no new lending or investment in AFS until Period 10.

Step 12 bis 3: See what happens if the CB charges a punitive rate for its financial support

· Assign a reference rate code in D51 to link the CB rate in [Assumptions-PLA]. · For example, use code “2” in D49 to link the rate of interest of ELA to inflation.

· Add a punitive spread in line 49 columns I to T, say “6%” in real terms. · Hit <F9> and look at the results in [Summary], [Funds Flow], and [Money Market]. · The operational cash flow losses and accounting losses increase as expected. · To finance these additional cash flow losses, FPM borrows even more cash from ELA. · Any further deposit and creditor run will need to be covered by the CB.

The following table compares some results: Period 1 2 3 4 5 6 7 8 9 10 11 12

P/L OCF

ELA without

Cos

t

-47 -48 -48 -46 -46 -47 -48 -49 -50 -52 -53 -55

B/S BFF 40 42 44 -51 49 51 54 56 59 62 65 68

Initial FF -7 -6 -4 -97 3 4 6 7 9 10 12 13

Sell existing AFS

63

Net FF

-33 3 4 6 7 9 10 12 13

Cost of ELA

0 0 0 0 0 0 0 0 0

ELA Needs (balance eop) 33 31 27 21 14 5 0 0 0

P/L OCF

ELA with

Cost

-47 -48 -48 -48 -49 -50 -51 -52 -52 -53 -53 -55

B/S BFF 40 42 44 -51 49 51 54 56 59 62 65 68

Initial FF -7 -6 -4 -98 -1 1 3 4 7 9 12 14

Sell existing AFS

63

Net FF

-35 -1 1 3 4 7 9 12 14

Cost of ELA

2 3 3 3 3 2 1 1 0

ELA Needs (balance eop) 35 36 35 32 28 21 12 0 0





Step 12 bis 4: Come back to where we were before the deposit repayment

· Go to [FPM Cover] and change governor H12 from “1” to “0” and hit <F9>. · This governor of the FPM easily lets you take asset and liability [Actions] in and out. · This is one way to implement sensitivity analysis and “what if” questions in the FPM. · This can be combined with more scenarios using [Macro] variables. · To be on the safe side, change also D49 in [Assumptions-PLA] back to “0”. · Even if you leave the 6% spread on CPI, the FPM will not calculate costs since there is no

reference rate code of “0”.

PRICING LOANS AND INVESTMENTS (SIMPLE VIEW FOR NOW)

Step 13: Price loans and AFS step by step to attempt to reach equilibrium

· Go to [Summary] lines 121 and 132 and see the spreads that we have in the projection. · Go to [Summary] line 60 and see the level of NOM of (-5.4%). · Go to [Assumptions-PLA] line 15 and assign a code for the reference rate to link to loans:

· Enter “2” in D15; with this you are linking average loan rates to CPI. · We do that to continue our projection with the least amount of variables.

· Enter in line 15 a spread for each period of “7%” to provide room for taxes and dividend. · For AFS, enter “2” in D12 and in line 12 set the spread of “0.5%” (50 bp above CPI).

Step 14: Hit <F9> and analyze the results of pricing loans and AFS in the following sections

· In [Summary], review the overall performance trends and proxy ratios. · In [Funds Flow], see how much cash is available for reinvestment and how it is invested. · Now we have a bank with a NOM on TA of about 4% (in Period 1) to 4.5% (in Period 12). · Since the bank is not paying taxes nor dividends, all net income is retained. · As a consequence, the CaR explodes from 10% to close to about 41% (exorbitant).

PAYING TAXES AND DIVIDENDS

Step 15: Our controller reminds us of the need to pay taxes and distribute dividends

· Enter in [Assumptions-PLA] the following variables: · In line 168 columns I to T, set the tax rate at say, “30%”. · In line 174 columns I to T, set the dividend pay out to say, “60%”.

· Hit <F9> and evaluate the results in [Summary] and [Funds Flow]: · Net worth continues to grow and CaR grows from 10% to 16.3%.





· Is there margin to pay out more dividends or further expand asset growth? · Not clear yet! Zero Bank is not allocating loan loss reserves yet.

FACTORING IN COST OF CREDIT AND LOAN QUALITY (MOST DIFFICULT)

Step 16: Factor in some behavior for problem loans to the cost of credit

· Go to [Assumptions-BS] and let us assume a regulatory PD and LGD play. In real life we can learn that from the banker and attest his estimations scanning his MIS: · How does the banker estimate (data & method) how much he might lose in lending? · You would like to understand this process for each significant loan segment. · How much is charged into the loan rate for recovering his cost of credit loss? · Another question is whether he records that in the P/L and retains provisions for it. · The bean counters and Mr. Taxman prefer that the banker does not do the latter.

· If you have better information on the behavior of NPL use it now. · Otherwise, until you have it, use this common rule:

· A PD of 2% times a LGD of 50% will result in a generic provision of 1%. · For all periods enter PD of “2%” in line 84 and LGD of “50%” line 102 for “A” loans.

· This is based on our old fashioned rule of thumb of 50% provision for doubtful loans and 1% generic provision for standard “pass” or “A” rated loans.

· For all periods enter PD of “10%” in line 93 and LGD of “50%” line 112 for “B” loans. · This will come in later creating a generic provision of 5% (10%*50%) for “B” loans.

· For all periods, also enter LGD of “50%” line 122 for “C/D” loans. · With this we create the dynamics of loan migration (simplified process of loan

default). · “A” and “B” loans will migrate to impaired “C/D” grade at 2% and 10% rate per period. · We do not yet have “B” graded loans but will appear later. · (In real life the PD of less well rated “B” loans would probably be 3 to 10 times higher) · In addition, “C/D” impaired loans will be covered by a specific 50% provision.

· Hit <F9> and analyze the results in [Summary] and [Funds Flow]. · Notice that line 60 in [Assumptions-PLA] is at “0”.

· This means that we are suspending all interest income on impaired loans. · There would be lower interest income and less funds flow for reinvesting. · We are not considering any dynamics of loan work-out, charge-off and recoveries.





ASSESSING THE BANK’S PERFORMANCE (WHAT HAPPENS IN EACH STEP)

Step 17: Review where we stand now in [Summary]

· CaR (line 78) stabilizes at 12.6%, so there is not much margin to grow as such. · RoA (line 101) stabilizes at around 1.7% and RoE (line 103) in the 14.8% level. · Review the spreads of the projection in lines 121, 132, and 136. · See how in this simple exercise we have started to stabilize spreads. · These spreads narrow towards the end of the projection because:

· We now have a growing amount of non-performing loans (NPLs) in line 85. · Unless these NPLs are worked out, interest earning loans are lower. · The level of “performing” loans decreases to about 83% from 91% of total assets (line 9). · See the overall structure of the P/L in [Summary] on TAA columns X to AJ. · Most spreads of the P/L on TAA are trending down as the bank accumulates NPLs.

· Notice that with a PD/LGD combination of 2%/50% we get the following net cost of credit: · A “1%” on average loans as reported in [Loans] line 87. · This is without computing work-outs, charge-offs and recoveries of amortized loans.

· At this stage the experts have a stabilized projection for reporting to the board of Zero bank. · At this stage, perhaps you have noticed cell I75 in [Summary].

· In some concrete situations, this gives an idea of the value of the bank. · It tells you the present value (PV) of the discretional dividends available for distribution. · The PV is shown as calculated in [Valuation] as an approximation. · This number can help you to sense the different effects of running the FPM. · But it is also a crucial number to discuss with the potential investors in Zero Bank.

· The PV is calculated using the discount rate built with inputs in [Macro] lines 134 to 142.

Step 18: Build discount rate to calculate present value of future dividends

· Build the discount rate for discretional cash flows now in [Macro] lines 134 to 142 columns R and AL to AW: · Risk Free Rate of say “6%” (long term international risk free rate of which country?); · Country Risk Premium of say “4%” (of Krakovia); · Market Risk Premium of “2%” (an inflated beta since in Krakovia there is not a market); · Idiosyncratic Risk Factor of say “1%” (we never had a bank in Krakovia); · Add also a growth of dividends of “3%” (could be the 2% GDP real expected growth).

· The PV of dividends available for distribution is reported in [Summary] I75: · It should be about 2.3 times net worth as calculated in [Valuation].

· How much would the potential investors be ready to disburse in Zero Bank? · Notice that lines 10 and 140 in [Macro] to input nominal FX depreciation are at zero.





· This means that the discount rate does not include FX depreciation yet. · An investment banker will do a series of runs to report the sensitivity of the value to drivers:

· Volume rate, spreads, operational expenses, and asset quality.

Step 19: Review asset quality

· In [Summary] line 85, large accumulation of NPL from 0% to 16% based on a PD of 2%. · What are the average PDs that the banks in our country have estimated? · How are these PDs estimated and validated by whom? · In [Summary] line 86, coverage of NPL is at 50% since we used a LGD of 50%. · In [Summary] line 62, net provisioning charge per period as % of TA stabilizes at 0.9%. · This might have been high for developed countries before the crisis (not anymore). · It is nevertheless a lower bound for middle income countries, depending on loan mix - only

if subject to ensuring good accounting for problem loans (e.g., the evergreen issue). · How does it compare to our country‟s long term average and recent actual losses? · For example, see the average actual credit loss rate for the U.S. (all segments).

· See the jump in Period 1 of net provisioning to TAA to 1.8% (Y62) and then trend to 0.9%. · This happens because Zero Bank did not have any generic provision at entry for pass loans. · Except for Period 1, the net provisioning charge is below 15% of the gross operating margin.

· You can see this in [Summary] line 62, columns BD to BO (a very narrow 12.7%). · This might be too slim. Go to [Loans] and observe the following lines and facts:

· In line 79, the first charge of a 1% generic provision to cover the new loans granted. · This is from the initial charge of 8 (1%*800) in Period 1, line 69, for the generic provision. · The additions to the generic provision look to be “0” due to rounding (about 0.04%). · See in line 89 how the coverage of “well rated/good” “A” loans is kept at 1%. · This is the intended generic provision from a PD of 2% times a LGD of 50%, which = 1%.

· This PD=2% forces the migration on each period of “A” loans to “C/D” loans. · See in line 78 the cost of provisioning on average gross loans equal 1%. · This credit cost should be compared to existing benchmarks (trend, peer, cycle). · Including considering segment mix and credit underwriting practices per segment. · The initial 2% in line 78 for Period 1 includes the charge for the generic provision. · This generic 1% provision can be phased out in three to four periods. · But it will fail to capture credit loss and expected losses when the loans are granted.

· With three separate sections dedicated to specify LGDs for each loan grade, you are able to

implement any expected changes in levels of provisioning for each segment for each period. · Obviously you need some well grounded analytics. That takes time: onsite in the bank!





LOAN WORK-OUTS, CHARGE-OFFS, AND RECOVERIES

· In [Summary] line 85 you have seen the accumulation of NPL to 16%. · We have not analyzed the re-aging, charge-off, and loan recovery patterns. · Actual practices in these areas can only be understood onsite within the bank. · As for loan migration and provisioning, the assumptions in these areas are crucial. · Let us suppose we know past trends, current practices, and related issues, and we find that:

· On average, in each period the bank re-ages 25% of the loans that become impaired. · On average, in each period the bank charges off 25% of the impaired loans. · On average, in each period the bank recovers 5% of the loans charged-off.

· Implement these three drills one after the other and then simultaneously in the next steps. · You can do your own sensitivity analysis and deviate from the steps proposed below.

Step 20: Implement loan work-out of 25% (the banker “cures” about ¼ of the impaired loans)

· Go to [Assumptions-BS] and enter that “25%” in line 134 for all periods and hit <F9>. · With this variable you can replicate the “ever-greening” practices of some bankers. · This variable is very dangerous because it allows the user to “make up” a nice projection. · Use it with care and only when you have done a decent analysis and loan review. · See the table below that compares results. Document to report your sensitivity analysis. · As expected, we see a huge reduction of NPL in Period 12 (from 15.9% to 5.7%). · Loans that are “cured” with this workout return to grade “B” always, not to “A”.

· You may like to try and assign “B” loans an interest rate in [Assumptions-PLA] in line 25.

· If you leave the spread at “0”, these “B” cured loans will pay the same as “A”. · You can decide that they are “weak” loans and set a spread of -4%. · Or, if you believe that these are super, price their higher risk with a +2% spread. · In any case, this will produce more interest earning loans and revenue.

· OCF is lower because the bank pays more taxes and dividends due to higher NIBT. · Net provisioning to NOM and to average credit falls to low (unrealistic?) levels.

· Re-aging and roll-over practices mask in many places the true level of NPL. · Loans never get to the point of default or being impaired (cleaned up before that point).

Step 21: Implement Loan charge-off of 25% (See comparison table. You can do your own sensitivity analysis.)

· In [Assumptions-BS] undo the previous move, set line 134 for all periods back to “0%.”





· Go to [Assumptions-BS] line 143, enter “25%” for all periods, and hit <F9>. · You got less growth in interest earning loans (in previous step these could be evergreens!). · Slightly less revenue but much cleaner balance sheet (see sensitivity analysis ). · Less revenue leads to less taxes and dividends paid (more stable and better OCF to NOM). · The use of loan charge-off ratios (CoR) is more adequate to simulate credit dynamics. · It results in more realistic NPL and provisioning levels to GOM and to average credit. · Combined with the PD and LGD, the charge-off rate is crucial to simulate loan losses. · If CoR are low, one should expect a higher accumulation of NPLs. · Except for the distortions created by continued roll-over and re-aging of problem loans.

Step 22: Implement recoveries of charge-offs

· Leave on the assumption the previous assumption to have some charged-off loans. · In [Assumptions-PLA] line 162, put “5%” for all periods (recover 5% of charged-off loans). · Hit <F9> and note in [Operations] line 59 that FPM is recording charged-off loans. · Also in [Operations] line 34 the recoveries at a 5% rate of amortized loans (prior to

deducting recovered loans from volume of charged-off loans).

Steps 20, 21, and 22 Compared

([Summary] line) After Step 19 25% Work-out 25% Charge-off

CO+ 5% Recoveries All in One

Period 1 Period 12 Period 12 Period 12 Period 12 Period 12

PV/NW (I75) 2.3 3.1 1.9 2.1 2.6

CaR (78) 10.0% 12.6% 14.5% 11.6% 12.1% 13.4%

NPL (85) 1.9% 15.9% 6.1% 4.8% 4.8% 2.2%

Net Cost of Credit (148) 2.0% 0.9% 0.3% 1.8% 1.8% 1.3%

A loans 840 1350 1319 1417 1424 1385

B loans 0 0 155 0 0 90

C/D loans 17 255 96 72 72 33

Specific provisions 8 128 48 36 36 17

RoA (101) 1.5% 1.7% 2.4% 1.4% 1.7% 2.0%

RoE (103) 16.2% 14.7% 18.7% 13.9% 15.5% 17.1%

Retained Earnings (72) 6 11 16 9 11 13





The selection of the rates for work-out, charge-off and recoveries is a delicate task. If these are not factored into the projection, the level of adversely classified assets (impaired or defaulted, or NPL) grows without more limit than the PD used (the rate at which good loans become NPL). Most prudential report formats do not provide clear information on the flow of performing and non-performing loans. This flow can be only ascertain within a bank using the reports generated by the credit information systems (CIS) that the lending officers, loan review function, senior manager, and the board have available to track loan performance and credit quality, NPL, recoveries, re-aging, and interest performance and provisioning. Due to under-investment, these CIS remain often primitive and underdeveloped.

ALTERNATIVE SCENARIOS AND SENSITIVITY ANALYSIS

Step 23: Prepare to create alternative scenarios for the projection

· Consult with your dedicated senior economist (Financial Stability or Research Dept). · It is also good to keep some periodic contact with the lead economists of some banks. · Learn about the basic macro assumptions from them. · How do they think that their changes will affect earnings performance and asset quality? · Find the consensus views regarding GDP, CPI, FX depreciation, and reference interest rates. · Discuss which reference rates the market uses to price loans, investments, and deposits. · Agree with your dedicated economist to do some econometric work and regression tools. · For example for the evolution of deposits and credit to relevant macro drivers. · In the meantime, as he works on these scenarios and regression tools…

Step 24: In [Macro], build two additional external scenarios (to do sensitivity analysis)

· Enter in [Macro] AY1 and BL1 different scenario names, say “Scenario 2” and “Scenario 3”. · Remember that we are keeping all variables “flat” for all periods for now to better isolate

the effects of changes we make to learn the FPM. · Set GDP growth and CPI level:

· Copy line 6 and 7 from scenario 1 into all periods for both additional scenarios. · Also, allocate all deposits growth to our single product without any product mix:

· Copy line 84 from scenario 1 into all periods for both additional scenarios. · Replicate all the lines that build the discount rate (lines 134 to 142) into both scenarios. · See that lines 169 and 170 are filled with “1” in all scenarios.





· Now let us build additional reference rates and varying FX rates in the two scenarios: · 3-month LIBOR (Scenarios 2 and 3): input in line 20 the best consensus, say, “3%”. · Nominal FX depreciation:

· In line 10 for Scenario 2 cells AY to BJ, enter a flat trend of say “7%”; · In line 10 for Scenario 3 cells BL to BW, enter a flat trend of say “4%”.

· For the LC.CY. government TB rate in [Macro] line 26, link the TB to other rates:

· In Scenarios 2 and 3, code TB = (1 +FX) * (1+international short term interest rate) – 1. · Thus, in AY26 enter “= (1+AY20)*(1+AY10)-1” and copy across all periods.

· Copy the block of cells to Scenario 3 to replicate the same formula of covered FX interest.

· This should produce a TB rate of 10.2% (Scenario 2) and of 7.1% (Scenario 3). · For the Prime rate in [Macro] line 153, link this rate to the TB (FX covered) rate:

· Enter by hand a new line 151 in both Scenarios 2 and 3 to hold the spread. · Enter in this line the spread for both Scenarios, say “1%” · Link the Prime rate to the TB rate as follows AY153 “=AY26+AY151”. · (Entering in a new line will only be allowed in certain versions of FPM. If your version

does not allow you to do so, simply enter a formula AY153 “=AY26+0.01”.) · Copy across all periods and the full block to Scenario 3. · This should produce a Prime rate of 11.2% (Scenario 2) and of 8.1% (Scenario 3).

Step 25: Select to use scenario 3 (=scenario 1 but with FX depreciation) and see results

· Take note of the PV of dividends in [Summary] I75, which should be about “2.58”. · In [FPM Cover] H6 and change to “3” to select Scenario 3. Back to [Summary] hit <F9>. · The projection is exactly as under Scenario 1, but the PV in I75 is lower, to about “1.8”. · This is the effect of the discount rate of flows now including the FX depreciation.

Step 26: Assume in Scenario 3 that the economy has deteriorated

· The deterioration increases the loan losses expected. · In this step, you will “scale” the regulatory PD and LGD in use. · [Macro] line 169 contains a “scaling” factor for each scenario. · Enter for Scenario 3 in Periods 2 to 6 the following values for the “scalar”:

· “1.50”, “2”, “2”, “2”, “1.50”, leaving “1” in the other periods. · This affects the regulatory PD and LGD. See what happens with scalar of “2”:





· It increases the speed by which loans default (PD) or migrate to impaired loans: · 2% x √ 2 = 2% x 1.41 = 2.828% new PD for “A” loans used by the FPM. · 10% x √ 2 = 10% x 1.41 = 14.1% new PD for “B” loans used by the FPM.

· It increases also the provision level (LGD x √ scalar): · 50% x √ 2 = 50% x 1.41 = 70.71%, which is the new LGD used by FPM

· The two new PD and LGD multiplied now give a higher generic provision: · 2.828% x 70.71% = 2%, previously 1%. · 14.1% x 70.71% = 10%, previously 5%.

· Note that if you scale a LGD of 50% with a scalar of “4” you will get a 100% provision. · In this manner you can simulate a low intensity downturn (EL is multiplied by 2).

Step 27: Recalculate and review the effects of the scaling factor

· Go to [Summary] and hit <F9> to review the results of these changes. · See the level of NPL peaking in Period 5 to about 2.7% and then descending progressively. · See the average level of provisioning for impaired loans at 70.7% in those periods. · In [Loans] line 87, you can see that the net cost of credit losses peaks at about 2.4%. · Also in [Loans] line 90, notice the change in impaired loans. · Revert to the original scalar of “1” in Scenario 3 and press <F9> (out of trouble). · The challenge is to develop a reasonable macroeconomic link for a realistic scalar. · This can be done by a regressing the long term average actual loss rate with macro variables. · For example, with some lags: ∂ GDP, ∂ employment, ∂ interest rates, etc. · See the “practitioner” rule of thumb using the US example

Step 28: Adjust entry and assumptions according to new asset quality information

· You should be still in Scenario 3. Suppose the following: · The expert credit officer with which we are consulting thinks that it would be difficult to

originate top rated loans in Krakovia. In 6 out of 20 cases the board should expect: · Poor borrower documentation and little credit history; LTV above 80%; · Debt service conditions above (50%) the borrowers annual discretional income; · Lower than acceptable original scoring, frequent arrears and difficult work-outs; · Lower revenue performance if the loans are fully priced (higher arrears).

· The expert thinks that the average loss on NPL and less well rated loans is closer to 70%. · He also believes that charge-offs will be faster at 50% or more of newly impaired loans. · Based on his opinion, the financial controller decides the following alternative assumptions:

· To migrate 30% of the “A” loans to “B” loans





· 30% * 800 of the loans in our portfolio is 240. · Go to [Entry] and enter “-240” in K30 and “+240” in K31.

· To run a projection assigning a higher PD of 10% to these “B” loans: · In [Assumptions-BS] line 93 have “10%” across all periods. · Also change [Assumptions-BS] line 75 to “70%”, assigning only 70% of new

loans to grade “A”, since about 30% of new originated loans are lower rated “B”. · In [Assumptions-BS] lines 112 and 122 input “70%” to increase the LGD of “B”

and the specific provision of “C/D” loans to 70%. · Increase also to “50%” the charge-off ratio in [Assumptions-BS] line 143.


· Go back to [Summary] before hitting <F9> and take note of some core indicators. · Hit <F9> and reflect on the impact of this sensitivity test driven from examination:

· The PV of dividends falls to about 0.6. · The CaR in Period 12 falls to about 10.1%. · Zero Bank will start to pay dividends in Period 5 (Bank cannot pay dividends until

required CaR are met – This can be changed to allow dividend payment through a “governor” in [Entry] C116).

· The level of NPL will continue on around 1.3% and the cost of credit loss goes to 2.8%.

Step 30: Perform some additional analysis

· What would the potential investors think of the proposition to invest in Zero Bank? · Forcefully, the pricing of the less well rated “B” loans should be higher to reach breakeven. · How should the higher risk of “B” on “A” loans be priced? · To analyze the break-even point and the pricing of loans, you may wish to test with

different frequencies in [FPM Cover]. · If you do these changes in frequencies, return them to annual.

· In [Assumptions-PLA] line 25, set the spread of “B” loans on “A” at “4%” (higher). · Hit <F9> and see in [Rates] the resulting rates. · Save this stabilized projection with a name you can recognize later easily. · This run of FPM should have performance proxies similar to the following:

· PV of dividends available for distribution (DAD)P0 = 110, or around 1.3 times initial Net Worth;

· NWP12 = 179 and Retained IncomeP12 = 11; and · CaRP12 = 12.2%; NPLP12 = 1.3%; Cost of CreditP12 = 2.8%; RoAP12 = 1.7%.

· In [Entry] you can also use the reclassification columns to change the level of problem loans.





FEE SERVICE AND OTHER TYPICAL ITEMS (Fixed Assets, Reserve Requirements, etc.)

Step 31: Input other revenue items and review effects

· Let us add some simple fee revenue (without analyzing the bank in depth): · Go to [Assumptions-PLA] line 89 and enter “0.9%”.

· Projecting fees as a percentage of total average assets is the simplest method. · Hit <F9> and observe the effect on the performance of the bank. · There are more lines that can be projected and different ways to do so. · These are explained in the User‟s Guide in more detail.

Step 32: Input a central bank reserve requirement

· The Central Bank of Krakovia (CBK) mandates a 5% reserve requirement (RR) of deposits: · Deduct 40 (5% x 800) from AFS by entering “-40” in [Entry] K17 (20 AFS left!) and enter

this “40” in [Entry] K7. · Enter “5%” for all periods in [Assumptions-BS] line 23.

· Note that if we had skipped the initial allocation of RR in [Entry], the FPM would have sold AFS to comply.

Step 33: Input fixed assets and other expenses

· Invest in the Zero Bank‟s building and branch network through AFS: · Deduct 20 from AFS (i.e. replace “80” with “60”) in [Entry] H17 and enter this “20” in

[Entry] H71 · Let us add depreciation costs for Property in [Assumptions-BS] line 155, of say, “5%”.

· The cost structure of Zero Bank is incomplete and the controller recommends to add these:

· In [Assumptions-PLA] line 123, “0.5%” of TAA for the staff Social Security costs; · In [Assumptions-PLA] line 128, “0.5%” of TAA for IT and Communication costs; and · In [Assumptions-PLA] line 132, “0.15%” of TAA for Management bonuses;

Step 34: Recalculate and review a basic stabilized projection

· Hit <F9> and see how CaR, RoA and RoE, and all other key proxies change. · This run of FPM should have performance proxies similar to the following:

· PV of DADP0 = 80, or 0.99 times initial NW; · NWP12 = 156 and Retained IncomeP12 = 9; · Zero Bank would start distributing dividends in Period 3; · CaRP12 = 11.0%; NPLP12 = 1.3%; Cost of CreditP12 = 2.8%; RoAP12 = 1.3%; and





· Overheads/TAAP12 = 4.8%; Overheads/GOMP12 = 53.3%.

· With these assumptions, we have already prepared a basic stabilized projection. · Hopefully, you have been able to familiarize yourself with the mechanics of the FPM. · Print out the reports of your choice found in [Summary].

Save the File now with “Save As” and name it with some form of “end of Step 34”

Then, for the next three drills you would like to save three separate copies with different names: One ending with “FX” (we will see incorporate foreign currency activities); another with “MPA” (we will incorporate multipurpose assets in this file); and another with “MPL” (we will incorporate multipurpose liabilities in this file).

ADDING FOREIGN EXCHANGE ACTIVITIES

Step 35: Add foreign currency bonds and foreign currency loans (closed FX position)

· Go to [FPM Cover] H6 and ensure you are in Scenario “3” (FX depreciation of 4%). · Ensure you have an International Short Term Rate ([Macro] line 20) of say, a “3%” rate p.a. · Assume you borrow FG.CY. through a bond issue 50 bonds to fund FG.CY. loans:

· In [Entry] I100 enter “50” under FG.CY. activities. · In [Entry], FG.CY. activities should always be entered in equivalent LC.CY. denomination at

the spot FX0 rate. · Notice that in this case, since the spot FX0 rate is “1” (from [Macro] R8), (1* 50 in FG.CY.)

= 50 in LC.CY. · Invest the 50 units obtained into “A” loans for a new loan line:

· Go to I35 for FG.CY. and enter “50” (again, this is denominated in LC.CY at the “1” FX0 rate).

· You can rename the set of loans in column E to say, “Refinancing Loans”.

Step 36: Add reference rates and spreads for both the bonds and loans in [Assumptions-PLA]

· For the FG.CY. bonds issued in line 53 (columns U, and Y through AJ) choose and enter: · A reference rate from [External], say a link to LIBOR entering into U53 a “4”. · A spread in line 53 columns Y through AJ, say “1%” (on selected LIBOR).

· For the FG.CY. loans in line 16 (columns U, and Y through AJ) choose and enter:





· A reference rate from [External], say also a link to LIBOR entering into U16 a “4”. · A spread in line 16, same columns, say “7%” (1% funds +5% OpEx+1% risk and return). · A risk weight for capital requirements, say “100%”, in [Assumptions-BS] U76 and U123,

for “A” and “C/D” loan grades in play here. · Add loan segment dynamics in [Assumptions-BS] columns Y to AJ:

· For “A” loans: PD of “0.5%” in line 85 and LGD of “20%” in line 103. · We need to also add LGD of “20%” in line 123 for impaired “C/D” loans to

calculate specific provisions. · Since there are no “B” loans in the [Entry] and we are not allocating any new

funds to loans (in step below), we do not need dynamics for the “B” loans here.

Step 37: Complete the foreign currency activities assumptions

· Now that we will have FG.CY. activities, we need to input reinvestment criteria for the FG.CY. fund flow in [Assumptions-BS] line 15 (columns Y to AJ): · You wish to store liquidity allocating “100%” to AFS in FG.CY. (CDs in a foreign bank). · Also enter “100%” in [Assumptions-PLA] line 36 columns X through AJ that all AFS is

to be debt securities. · Assign interest income behavior to the FG.CY. AFS in [Assumptions-PLA]:

· A reference rate from [External], say a link to LIBOR, entering into U12 a “4”; · A spread in line 12 (columns Y through AJ) say, “-2%” (two below LIBOR).

· Assign a risk weight for the new FG.CY. AFS, say “20%” in [Assumptions-BS] U46.

Step 38: Recalculate and review additional FPM features with foreign currency activities

· Check that that there are no forwards or swaps at play: · Go to [Assumptions-BS] X11 and check that it is at “No” (for no FX constraint); · In [Assumptions-BS] check that lines 375 to 394 are at “0” (no active derivative).

· Hit <F9>. Observe and reflect on the results:

· [Summary] line 114 reports a growing FX spot position starting from zero. · This growth is due to the funds allocated to AFS in [Funds Flow] line 9. · This takes place because you selected to allocate all [Funds Flow] in FG.CY. to AFS. · All the FG.CY. [Funds Flow] originates from operational cash flow generated in FG.CY. · There are no other elements in the FG.CY. book that change or grow in this projection. · This allocation is decreasing and is lower than the FG.CY. net interest income (NII)

projected.





Automatic allocation of overheads:

· Zero Bank does not pay overheads in FG.CY. since it is a LC.CY.-based bank. · In [Projected-PL] see this NII compensated in line 127 by an automatic FPM cost allocation. · This automatic allocation compensates cash flows between LC.CY. and FG.CY. · The FPM allocates costs in LC.CY. to the FG.CY. book (at the average % cost per TAA). · To do this the FPM sells operational cash flow in FG.CY. at the eop FX rate if the latter > 0.

Manual allocation of overheads:

· In [Funds Flow] lines 33 and 34 provide manual allocation of cross-currency funds flow. · If the gross operating margin in FG.CY. were less than the cross-currency allocation, the

final OCF would be < 0. · Then, the FPM will finance the negative OCF in FG.CY. with ELA… · …unless another liability (increase) or asset (decrease) funds the negative OCF in FG.CY.,

or, if this negative OCF in FG.CY. is financed by the manual cross currency allocation. · The NII in FG.CY. decreases as FG.CY. loans migrate from “A” to “C/D” at the 0.5% PD. · We set at “0” the ratio of the impaired FG.CY. “C/D” loans‟ income performance in

[Assumptions-PLA] line 61. · Changing this to “1” will indicate that all “C/D” loans in FG.CY. will be accruing and

paying interest income, at the rate of “B” loans.

· Note the following features of FPM: · In [Projected-PL] line 11 and 15, there are interest income in the FG.CY. section (line 11

for AFS will appear to be zero, but cells contain a small amount of interest income). · In [Projected-PL] line 36, there is interest expense in the FG.CY. section. · The effect of any changes in FXR on the FG.CY. loans‟ PD is an exogenous input. · The FPM does not have any satellite add-on model to simulate impact of devaluation. · FX depreciation results in FG.CY. translation profits in [Summary] line 54 because the

spot FX position is > 0 and growing (rounding makes it appear as zero).

Save the File now with “Save As” and name it with some form of “end of Step 38”.

Step 39: Input cross-currency funding of LC.CY. loans with FG.CY. borrowing (open FX position)

· Go back and open the copy saved after implementing Step 34. · See [FPM Cover] H6 and ensure we are at Scenario 3.

· Let us first check our reference rates in [Macro]:

· Ensure we have the International Short-term rate (line 20) and the TB rate (line 26).





· In line 153, for our Prime rate, we would like to now add an additional 1% spread on the TB. (Remember that we linked the Prime rate to TB in Step 23.) · Input “2%” in line 151. · (*If your version of FPM did not allow you in Step 23 to add a number to line 151, you

will have to change the Prime Rate formula to “=BL26+0.02”). · This is similar to Scenario 2 but adding 2% instead of 1%.

· Let us assume we borrow FG.CY. through a bond issue for 50 bonds to fund LC.CY. loans. · We sell spot the 50 units of FG.CY. at the prevailing FX spot rate (denoted as “FXR0” in

the FPM) of “1” for the base year, see [Macro] R8 . · In [Entry] I100 under US$ activities, enter “50” denominated in LC.CY. · Notice that since the spot FX rate is 1, then 1* 50 in FG.CY. = 50 in LC.CY. · Entries in FG.CY. are always done in the equivalent LC.CY. units at the spot FX.

· Let us now invest the 50 units obtained of LC.CY. into “A” loans for a new loan line. · Go to [Entry] H35 for LC.CY. and enter “50” (again, denominated in LC.CY.). · You can rename the set of loans in column E to say, “Refinancing Loans”.

Step 40: Add reference rates and spreads for both the bonds and loans in [Assumptions-PLA]

· For the FG.CY. bonds in line 53 (columns U, and Y through AJ) choose and enter: · A reference rate from [External], link it to LIBOR, entering into U53 a “4”. · A spread in line 53 (columns Y through AJ), say “1%” (on selected LIBOR).

· For the cross currency loans (LC.CY.) in line 16 (columns D, and I through T) enter: · A reference rate from [External], link it to our Prime, entering into D16 a “14”. · (Notice the entry of rates assumptions depend on currency; thus, D16 instead of U16!) · A spread in line 16, same columns, say “2%” (OpEx + return). · Recall from previous steps that we arranged the following relationships:

· Prime = TB + 2%; and · TB = (1 + FX Depreciation) x (1+ LIBOR short term interest rate) – 1. · Thus, Prime is already a “covered” rate that includes a credit risk premium.

· A risk weight for capital requirements, say “100%” in [Assumptions-BS] D76 and D123. · PD of “0.5%” and LGD of “20%” in [Assumptions-BS] lines 85 and 103 (cols. I to T). · Specific Reserves of “20%” in [Assumptions-BS] lines 123 for all periods. · Assume that NPLs are performing by entering “100%” in line 61 [Assumptions-PLA].

· The following steps are not essential in this case, since there won’t be OCF in FG.CY.:

· Since there is not a FG.CY. interest earning asset the OCF will be likely < 0. · Income is generated in LC.CY. by the cross-currency loans in LC.CY.





· Select in [Assumptions-BS] line 15 (columns Y to AJ) a reinvestment criteria for the FG.CY. fund flow:

· Say you would like to store liquidity allocating “100%” to AFS in FG.CY. · Assign interest income behavior to FG.CY. AFS in [Assumptions-PLA]:

· A reference rate from [External] - link it to LIBOR, entering into U12 a “4”; · A spread in line 12 (columns Y through AJ) say, “-2%” (two below LIBOR).

· Assign a risk weight for the new FG.CY. AFS, say, “20%” in [Assumptions-BS] U46.

Step 41: Recalculate and review additional FPM features with foreign currency activities

· Be sure that there are no forwards or swaps in play: · Go to [Assumptions-BS] X11 and check that it is at “No” (no FX constraint); · In [Assumptions-BS] check that lines 375 to 394 are at “0” (no active derivative).

· Enter the regulatory limit for the FX exposure in [Assumptions-BS] line 10 (columns Y

through AJ), say “25%”. · This will not be applied in this case since we set X11 to “N” (no FX constraint). · Hence, the FPM will not automatically enter into a forward to keep the FX position

within the limit. · Hit <F9> and observe and reflect on the results of this cross currency strategy:

· [Summary] line 114 reports a constant short FX position of 50 units (the bond liability). · Also line 115 shows FX exposure ratio to capital (decreasing due to retained earnings). · [Summary] line 54 shows FX translation losses because the FX position (FXP) is negative. · This comes from line 57 in [Projected-PL] that records the FX translation from

[Operations]. · The calculation of the FXP is reported in lines 149 to 158 in [Projected-BS]. · Note in [Projected-PL] line 36 that there is an interest expense in the FG.CY. section

(columns Y to AJ) of negative 2 (cost of the bonds). · Also, OCF < 0 in line 103 (same columns) since there is no interest income in FG.CY. · Therefore, the FPM does not (cannot) allocate cross currency costs to FG.CY. in line 127. · All the operational costs are generated, borne and allocated to the LC.CY. book, where

you have the income of the cross-currency loans denominated in LC.CY. · See in [Funds Flow] that the negative OCF in FG.CY. is absorbed by the OCF in LC.CY. · The combined OCF in LC.CY. in line 30 (col. I to T) is net of the negative OCF in FG.CY. · You can compare this with Step 38 which had OpEx allocation to FG.CY. activity book.





Step 42: Test the risk of the negative FX position created by your funding strategy

· Go to [FPM Cover] and change the governor H6 to “2” to activate your Scenario 2. · Scenario 2 should be similar to Scenario 3 but with a larger FX depreciation in line 10 (7%),

and smaller spread on the TB for the Prime Rate. · Changing the level of FX depreciation also changes the TB and Prime rates linked to it. · With a negative open FX position, you should expect higher FX translation losses.

· Hit <F9> and observe the results projected, including:

· The short spot and overall position in [Projected-BS] lines 152 and 154, both in (50); · 1.5 increase of the FX Translation losses in [Summary] line 54 when you move to

Scenario 2 from 3; · Consequently a decline in PV from about 0.46 to 0.11 times initial net worth, and a slight

decline in CaRP12; · A general deterioration of margins and of profitability: RoA = 0.8%, RoE = 8.9%; and · Higher cost of financing the cross-currency loan portfolio: NWP12= 152; REP12= 7.

· Set back [FPM Cover] H6 to “3” in and hit <F9> to return and recalculate at Scenario 3.

Step 43: Close the short FX open position created

· Buy a FX Forward in [Assumptions-BS] entering for each period the following terms:

· In line 375 (columns Y to AJ) the notional amount of the forward in FG.CY.: “50”. · In line 376 (same columns) “Time until Delivery”, enter “1”. · Note that line 376 should always be in years and match the frequency period of the

projection: · If Y4=“4” (a quarterly projection), then set “Time until delivery” to “0.25”. · This denotes a 3-month maturity to expiration of the forward. · Otherwise, if maturity=“1”, no other forward contract should be entered until

Period 5. · If another contract is entered before the maturity of the previous contract, the total

forward contract will be handled under the terms of the new contract. · Remember to adjust the maturity of the forward to the frequency of the projection.

· We assume that the future spot is exactly the one expected today: · In line 377 set the “Forward Rate” = the expected spot rate in [Macro] line

8 (columns BL to BW) · Note that in [Assumptions-BS] line 396 currently shows the expected

spot rate. Copy and paste this line over to line 377 as forward rates.





· (If you have not done so already, return to Scenario 3 with 4% p.a. FX depreciation).

· In real life you will add a premium to the expected spot a premium.


· Hit <F9> and observe results and outputs: · The FXP becomes “0” in [Summary] line 114 from its initial (50) in Period 0. · The forward contract has balanced the overall FX position. · See the composition of the FX position in [Projected-BS] lines 152 to 156. · See the costs of the spot and the forward position in [Projected-PL] lines 57 and 58. · The cost of the forward is zero, since the price of the forward = expected future spot. · PV=0.42; CaRP12=10%; RoA=1.0%; RoE=11.2%; NWP12=149; RE P12 = 7. · Nothing has changed significantly and the cost effect of the forward is neutral.

· See what happens if you add a premium to the forward rate, say a 1%, to approximate a

market price: · You can use [Assumptions-BS] line 395 to put a spread, so to speak, on the expected spot

rate from [Macro] line 8. · The result of this will be shown in line 396, which you may copy over to line 377 to be

applied to the forward contract in this case. Or, you may simply use this line as reference of the expected spot rate by leaving 0% in line 395. · Enter 1% in [Assumptions-BS] line 395 and hit <F9> to see line 396 change. Copy

and paste these results into line 377.

· Hit <F9> and review: · Now the exercise price agreed is higher than expected spot rate projected. · You should see the cost of the forward recorded in [Assumptions-PLA] line 58. · And the proxy performance indicators will reflect the slightly less favorable status.

· Now, revert back to the previous spot rate as the forward price (before adding the 1% spread) to see the effect of a higher FX depreciation: · Before you hit <F9> again, ensure that you have reverted back to the Scenario 3 spot

price as the forward price used above. · Change now in [FPM Cover] to Scenario 2 (FX depreciation of 7%). · Hit <F9>. · The forward insures the bank from further FX losses: see [Projected-PL] lines 57 and 58. · In fact, given the beneficial price of the forward, Zero Bank has now net FX profits. · The FX translation losses from the short spot position are higher with a 7% depreciation. · However, these losses are compensated now by profits from the forward valuation.





· PV=0.7; CaRP12=11.8%; RoAP12=2.0%; RoEP12=20.3%; NWP12=179; RE P12=14. · The profits made on the forward are assumed to be earned in cash and part of the OCF.

Save the File now with an indicative file name, for example: FPM 44.exe

MULTIPURPOSE ASSETS AND LIABILITIES

Step 45: Try using the “Multipurpose Assets” (MPA) Line

· Recover the file saved after Step 34. · Check that you have the following performance proxies:

· PV of DADP0 = 80, or 0.99 times initial NW; · NWP12 = 156 and Retained IncomeP12 = 9; · Zero Bank would start distributing dividends in Period 3; · CaRP12 = 11.0%; NPLP12 = 1.3%; Cost of CreditP12 = 2.8%; RoAP12 = 1.3%; and · Overheads/TAAP12 = 4.8%; Overheads/GOMP12 = 53.3%.

· Say you identify “B” loans for 140 in nominal value which need to be restructured. · Provisioned by 7%, their current value and future performance is difficult to ascertain. · You wish to segregate and isolate them in a projection to test different assumptions on them. · You decide to take 140 “B” loans into a MPA line, in which to allocate restructured loans. · You will do the swap of “B” loans in the Period 2 of the projection. · (Alternatively, you could reclassify these in the base period of the projection, in [Entry].) · In [Entry] line 22, change the line title to “Troubled Restructured Loans” (TRL) · In [Actions], Period 2, you migrate from loans to the MPA line 140 units:

· In J24, enter “-140” to swap out the “B” loans to MPA (now “TRL”, when you hit <F9>). · In J164, enter “-7” to recognize the 5% loss related to these. · In J115, enter the difference of “133” to reflect the new portfolio of restructured loans. · Check in [Actions] that there are no other actions already:

· For example, you may need to change L127 to “0” (where you entered the deposit run of -97 in a previous step).

Step 46: Set maturity, reference rate and spread, and interest payment behavior for the MPA

· You expect that these restructured loans will be amortized in the next 8 years: · In [Assumptions-BS] J57, set the maturity, enter “8”.

· The new asset line needs to be assigned an interest rate (a reference rate and a spread):





· Suppose that the TRL are semi-performing and can only pay half the interest of other loans. · In [Assumptions-PLA] line 14, use CPI as a reference rate and enter “2” in D14; · Enter in the same line for the 12 periods “2%” for the spread. · Through this (only visible in [Rates] lines 13 and 14 after we hit <F9> later), we arrive at

a 5% interest rate, as compared to 10% for our “One Loan Only” line. · Set the cell governors for this MPA line made of restructured loans in:

· [Entry] C22 =1 (standard interest accrual asset) and D22 = 0 (cash payment).

· Activate the [Actions] by setting the governor in [FPM Cover] H12 equal to “1”. · Before hitting <F9>, go to [Summary] and note the performance indicator of your choice.


· Hit <F9> and see the effect on value and performance of the asset swap of “B” for “TRL”. · You should observe the following performance proxies:

· PV of DADP0=75 or 0.94 times initial NW; · NWP12 = 148; Retained IncomeP12 = 8; · CaRP12 = 10.5%; NPLP12 = 1.2%; Cost of CreditP12 = 2.6%; · RoAP12 = 1.3%; Overheads/TAAP12 = 4.8%.

· In [Capital Market] line 11, see the 133 units of MPA show up in Period 2 through 8 and see it disappear in Period 9 since it has been fully paid.

· In line 28, see that the MPA‟s interest income contribution starts with 3.3 in Period 2 and doubles to 6.7 through Period 8, then drops again to half for Period 9.

· This half income shown in Period 2 and Period 9 is due to the moment in a period in which FPM computes an Action: · Actions are computed at the end of the period and, hence, yield interest by half a period. · Interest is calculated on the average balance of two periods (0 + 133, in this case) · This is as if the asset enters the B/S in the middle rather that at the beginning of a period

· This can be manually adjusted through the following: · To increase the first and last period income to account for a full period accrual you can

double the interest rate for those relevant years to arrive to the desired amount. · To try this: Input 7% in [Assumptions-PLA], J14 and Q14, Period 2 and 9.

· However, this only takes care of new assets and liabilities entering the B/S ex-novo. · For items already in the B/S, the item being swapped yields also half the rate. · For the “B” loans being restructured, (133 + 0) * ½ = 66.5 * 14% = 9.31 of yield income; · For the TRL, the average balance is (0 + 133) * ½ = 66.5 * 5% = 3.3 of yield income; · Thus, if adjusting the income, both assets being swapped would need to be adjusted.

· Now, look at [Loans] and see the effect of decreasing 140 of “B” loans from Period 1 to 2.





· The change in the amount of B loans is not the decrease of 140 that one might expect. · Can you tell what has happened? Remember that you had applied several loan dynamics:

· PDs, LGDs, and workouts, charge offs, and recoveries in previous steps. · To see the effect of the asset swap you need to neutralize the effect of certain items. · In that manner you can isolate the full effect of the loan swap.

Save the File now as, for example: FPM Step 47 MPA.exe

You do not need to do reversal listed below (skip to the next step) except if you wish to see how the MPA works.

Step 48: Reversing steps to observe how MPA works (Optional)

· Go to [Assumptions-BS] and reverse the following changes: · In line 75, grade allocation to “A” in line 75 was set to “70%”. · This allocation increases the stock of new B loans. Change this back to “100%”. · Set lines 84, 93, 102, 112, and 122 to “0”, reversing the effect of PDs and LGDs. · Set to “0” lines 134, 143 in [Assumptions-BS] and line 162 in [Assumptions-PLA]. · This reverses the effects of migration, workouts, charge-offs, and recoveries. · In [Assumptions-PLA] line 14, match the rates of MPA and loans line with spread to “7”.

· Go to [Loans] and hit <F9>. See that in Period 2, B loans decrease exactly 140. · Observe now the isolated effect through the [Funds Flow] of undoing the MPA play:

· In Period 2, OCF=9, and there are 56 units of final discretionary funds flow to reinvest; · [Funds Flow] from the B/S are -93 units; · See in [Projected BS] (columns BU to CF) where these 93 units come from:

· Deposit increases 42 in Period 2 and reserve requirement is 2, leaving a net of 40. · The MPA line of restructured loans absorbs 133 units (140 less 7 of loss). · Thus, 40 units of inflows less 133 units of outflows = 93 net outflow.

· Plus 9 units of OCF and the decrease of “B” loans of 140 (in [Funds Flow] line 22) FPM has 56 to reinvest.

· In the [Projected-PL], lines 105 to 127, you can see the calculation of the OCF. Save the file now with a new name, for example: FPM Step 48 MPA reversed.exe

Step 49: Set up to use the “Multipurpose Liability” (MPL) Line

· Recover the file save after Step 34. · Check that you have the following performance proxies:

· PV of DADP0 = 80, or 0.99 times initial NW;





· NWP12 = 156 and Retained IncomeP12 = 9; · Zero Bank would start distributing dividends in Period 3; · CaRP12 = 11.0%; NPLP12 = 1.3%; Cost of CreditP12 = 2.8%; RoAP12 = 1.3%; and · Overheads/TAAP12 = 4.8%; Overheads/GOMP12 = 53.3%.

· Say you identify uninsured creditors whose investments might be lost in liquidation. · For obvious reasons, the bank cannot recover its viability and repay those investors. · A possible restructuring action could be to consolidate their debt into a long-term bond. · Under certain conditions that bond could be swapped, for example, for subordinated debt. · In Period 2, you migrate 140 from uninsured depositors to the MPL line. · You label the MPL in [Entry] E102 to “Restructured Creditors”

Step 50: Set maturity, reference rate and spread, and interest payment behavior for the MPL

· You decide the MPL/Restructured Creditors will be amortized in 8 years: · Set the maturity in [Assumptions-BS] line 194 equal to “8” in Period 2.

· Set interest payment behavior governors in [Entry] C103=”1” and D103=”0”. · This equates to a standard interest accrual liability, with cash payment. · Interest is expected to be nominal 0.25%, no matter what happens in the economy. · To set this up, go to [External] and see that code “8” (average deposit rate) in line 23 is 0%.

· So, select this as the reference rate for MPL by entering “8” in [Assumptions-PLA] D57. · This is a nice short cut to generate a fixed rate not anchored to a non-zero existing rate. · Enter “0.25%” in line 57 as spread on 0% to get a fixed 0.25% in the projection.

Step 51: Input [Actions] for the MPL

· In [Actions], take out any other actions in play: · For example, change L127 to “0” (deposit run of -97 in a previous step).

· Enter in J127 of [Actions] “-140” to swap deposits for MPL. · Enter in J144 of [Actions] ”140” to reflect the new resulting MPL. · Activate the [Actions] by setting the governor placed in H12 in [FPM Cover] equal to “1”.


· Hit <F9> and see the effect on value and performance of the swap. · See in [Projected-BS] lines 85 and 102 the effect of the swap among liabilities. · See in [Funds Flow] in Period 9 the final payment and how AFS are sold for 43 to pay for it. · See in [Capital Market] the projected Restructured Creditors in line 22 and its interest

expense in line 34 (value may appear to be zero, since too small with rounding). · You can further test sensitivity, changing the spread and see the impact.





· In [Actions] line 144, you can simulate additional principal repayments:

· Insert, say “-10” per period from Period 3 to Period 8. · Now the amortization goes more smoothly and there is no need to sell AFS at the end.

· You can also simulate that the MPL line accrues but does not pay interest in cash. · To do that, set the governor cell in [Entry] D103 = “1”. · You will see interest to accrue (not served in cash) and paid at maturity with the principal.

Save the File, FPM Step 52 MPL.exe





Recapitulation:

· You have stabilized at this stage a reasonable baseline projection for Zero Bank. · You should be able to explain to yourself what the projection shows and to report any

significant variations. · Keep in mind that results are normally more sensitive to growth, spreads, and costs. · Pay attention to:

· Trends from the effect of external and management policy decisions. · The mixed effect on performance from volume and spread changes. · The implication of any “best guess” assumptions for some variables. · The consistency of performance with the external environment assumed.

· You should understand the evidence and implications of: · Gains in market share or growth above trends or the economy · Increase in spreads · Reduction in the cost of provisioning (for expected credit loss).

· Review in [Summary] the indicators of financial performance. · Except for the initial hit of the generic provision and the loan recoveries, most relevant

margins are stable. · The evolution of spreads (lines 121, 132, 136) is very stable, showing that there are not

significant changes in assumptions. · Hence: changes in performance are mostly due to volume and B/S mixed effect · Notice that (these figures may be different depending on the version of comparison):

· The growth of total assets (about 5.5%) traces the growth in deposits (5%) · The growth of deposits (5%) traces the growth of nominal GDP (5%) · The growth of NW (about 9%) exceeds the growth of assets (5.5%) · As a consequence CaR increases from 10% to 15% (pay-out policies given) · But you have not modulated the risk of the loans granted · All are supposed to be “A” graded with a relatively modest PD of 2%

· Ask: · Do you know anything about the (credit) strategy of the bank? · Which target borrowers and risk pricing for our one line credit? · Which credit underwriting standards and pricing is the bank following? · Is this reflected reasonably in your projection? · Are the assumptions on PD, LGD, work-out, etc reasonable?

· Review the following in [Summary] against existing benchmarks, norms, or best-performer banks: · All ratios of P/L to TAA in columns X to AJ · All ratios of P/L to GOM in columns BC to BO · All growth ratios of P/L in columns AO to AZ · All ratios of B/S structure in columns X to AJ





· All ratios of B/S growth in columns AO to AZ · All (CAEL) proxy ratios of performance

· If you like what you see: save this spreadsheet as your FPM Base Scenario. · You may be ready now to implement some sensitivity “what if” analysis. · By now you know all what you need to READ THE GUIDE and LEARN ABOUT THE FPM!

Abbreviations:

AFS: Available for Sale Securities

B/S or BS: Balance Sheet

GOM: Gross Operating Margin

NIBT: Net Income Before Taxes

NOM: Net Operating Margin

NW: Net Worth (net assets)

OCF: Operational Cash Flow

P/L or PL: Profit and Loss Account

TAA: Total Average Assets

financial projection model: step-by-step -- introductory...

Documents