Dividends Issued & Share Issuances Excel Tutorial – Shawbrook Case Study (23:56)

Table of Contents:

• 1:09: Part 1: How Much in Dividends Can a Bank Issue?
• 3:36: Part 2: Projecting the Dividends
• 10:57: Part 3: Projecting Share Issuances and Shares Issued
• 18:25: Finishing the Dividend Projections
• 20:32: Part 4: Comparing Our Results to Those of Equity Research
• 22:20: Recap and Summary

Transcript: Bank & Financial Institution Modeling – Dividends Issued & Share Issuances

Welcome to our next lesson in this module on the Shawbrook operating model. In this lesson, we are going to go into the dividends and stock issuances of the company and look at what these have been like historically and then project them going forward.

Remember, this part or these two parts, the dividends and stock issuances are among the more complex items on the cash flow statement, which is why we looked at them separately. We projected the entire cash flow statement in the previous lesson and this time around we’re going to focus on these two items.

We’ll divide this lesson into four main parts. First, we’ll talk about how to think about the dividends a bank can issue and the stock issuances and repurchases it has. Then watch the projected dividends and go through the math for it. Then we’ll look at the share issuances and the number of shares that are issued each year.

[00:56]

Then we’ll compare our results to those of Equity Research and see how our dividends compare because it’s going to make a major impact on the valuation of the company and then we’ll do a recap and summary at the end.

So to start with and thinking about how much in dividends a bank can issue, remember that unlike with a normal company, everything here has to be tied to regulatory capital. So you can’t just assume a simple payout ratio and say that the bank can issue 30% of its net income as dividends. Whatever dividends you assume have to ensure that the bank also maintains its regulatory capital at above a certain level.

So in the simplified model in module one, for example, when we looked at the regulatory capital and dividends, we assumed a targeted payout ratio and then we compared this by looking at the minimum amount of Common Equity Tier 1 that is actually required by multiplying the percentage by the risk-weighted assets, then seeing our Targeted Dividends and then how much we were actually allowed to issue. We’re going to do something very similar here, it’s just that it’s more complicated because it’s for a real company.

[02:04]

You also have to think about other ratios like the liquidity coverage ratio, the net stable funding ratio and the leverage ratio, but usually in a schedule like this, you pick one ratio to target and you base the dividends on that and then you check the others afterwards.

The Stock Issuances and Repurchases are also going to affect things here because if you think about how these work and you think about a bank’s Common Equity Tier 1, you’re taking Total Equity and then subtracting Preferred Stock, NCI and then Goodwill and Other Intangible Assets and maybe making other adjustments. So of course, if you issue more shares, equity is going to go up. If you repurchase shares, equity is going to go down. So those are also going to impact this.

Many large banks have a lot in share repurchases each year. Shawbrook doesn’t have anything for that. It’s not even listed in their statement, so we’re going to ignore it. However, they have been issuing quite a lot in shares over the years, so we will factor that in.

[03:00]

This is pretty common for a high growth company or a high growth bank. They like to fund a lot of their growth with share issuances. And especially in this case, the company recently went public, so share issuances are certainly going to come up there.

So our overall approach here will be to start with the old CET 1, just like we did in the simplified model. We’ll add in this year’s Net Income. We’ll factor in other changes that impact CET 1 in this year, like stock issuances and then we’ll compare the capital available with the minimum capital the company needs at all times.

So let’s now move into Part 2 and project the dividends here. Let’s go up to the top first and I’m going to group everything so we can see this a bit better. So first off, we need to have something for the Risk-Weighted Assets because if we’re targeting a certain regulatory capital ratio, everything is going to be linked to that.

[03:59]

Now, we have the Risk-Weighted Assets projected up above in our interest earning asset and interest bearing liability section, so I’m just going to link them in from there.

Now for the Pre-Dividend and Stock Repurchase CET 1, you just have to think about what goes into this. We don’t want to link to entirely numbers from this year because it’s going to create a giant circular reference. So what we do instead, I’ll ungroup this once again, is go to the balance sheet first and take the company’s Total Equity from there. So that’s the first component of this.

And then you have to think about what goes into the CET 1 for the year that we’re in. The answer is that first off, to move form equity in one year to equity in the other year, we have to add the company’s Net Income to Common. So that’s the first part of it. But then moving down, there are a couple of other things. First off, we have other items that will impact the company’s equity.

[05:00]

Namely the Repayment of Third Party Funding, the Change in Reserves and also Proceeds from Common Share Issuances. So we have to factor in all of those items.

Dividends Paid we’re not going to factor in because that’s going to create an unnecessary circular reference and then once we have this, we can subtract the Goodwill and Intangible Assets on the balance sheet because these are the most common deduction when you calculate Common Equity Tier 1.

So we have our number from here. Let’s just copy this across now. So we get to this. Now, the next thing we need to think about is how much capital is actually required at all times. This is based on the bank’s regulatory capital requirements.

Now, in the first module, we went over Basel III and CRD IV and we talked about how as of the time of this case study, the Minimum Common Equity Capital Ratio is 4.5%. That’s not changing over time.

[06:03]

But what is changing is the Capital Conservation Buffer. This is increasing to eventually 2.5%. And then there’s also a Counter-Cyclical Buffer which is not shown here, but it’s increasing in the same way.

We also know going back to their investor presentation that they’re targeting a Common Equity Tier 1 ratio of greater than 13%. We can simplify that a little bit and just make it exactly 13%. Based on all this, we can now get to calculating some of these numbers. A lot of this is not really necessary.

Really all we need is the targeted Common Equity Tier 1 ratio, which of course we already have because it’s listed up at the very top, Common Equity Tier 1 target of 13%.

So that’s actually all we need, but just for your reference, I’m going to fill in these other numbers. We know that the required Common Equity Tier 1 is always going to be 4.5%.

[07:00]

And then the Capital Conservation Buffer will be 0% initially and the 0.625% and then 1.25% and then 1.875% and then 2.5%. The Counter-Cyclical Buffer is actually the same. The Systemically Important Financial Institution Buffer is EU-specific, though there is an equivalent in the US. This one is going to be zero, though, because Shawbrook is so small that it’s not a systemically important financial institution.

With all those, we can now take our Targeted Common Equity Tier 1 and then subtract everything here. We get to something called the Bank’s Discretionary Buffer. Now, this doesn’t directly factor into any of our calculations, but I’m showing it because we want to show the difference between what they’re officially required to have in the form of common equity tier one and then what they’re targeting in the given year.

We can see how that ratio or the buffer goes down over time because what they actually have to have moves closer and closer to the target.

[08:00]

So we have these numbers. As I said, really the most relevant one is just the targeted one of 13%. For the Minimum CET 1 Required, just ungroup this. So you might look at this and think, “Okay, well, it’s easy. You just take the 13% and multiply by risk-weighted assets.”

The problem, though, is that this is going to create a circular reference. The reason it will create a circular reference is because this Minimum CET 1 Required is going to impact the amount of dividends the bank can issue. Those dividends in turn will impact the bank’s equity because their Retained Earnings will change as a result.

And then as a result of that, if you go up to the balance sheet Balancers, the Loans and Advances to Banks and then Due to Banks, these are also going to change because the balance sheet changes as a result of those dividend changes.

[08:56]

And then what ends up happening is that under Interest-Earning Assets and Interest-Bearing Liabilities, since these balance sheet Balancer items count as Interest-Earning Assets and Interest-Bearing Liabilities, they affect net interest income and net income. So you get a giant circular reference here eventually, when you finish the model, if you just calculate it like this.

So in this model, we have the option of enabling or disabling circular references. I’m going to do something similar here. I’m just going to say that if we have circular references enabled. Yes, we can use the risk-weighted assets in this year. Otherwise, we should use the risk-weighted assets from the year before because if we use the number from the year before, then our balance sheet in this year is not going to affect the Interest-Earning Assets and that means that it’s not going to affect this number, the risk-weighted assets from the prior year.

So let’s just do that. At this point, you should also go to Alt, T, O in Excel.

[10:00]

Go to Formulas and make sure that workbook calculations are set to automatic except data tables and iterative calculations are enabled, otherwise circular references are not going to work and you’ll see where they pop up very soon.

So we have our Minimum Capital that’s required each year. But then now we have to see what is actually available for issuing dividends based on these numbers. So we’re going to take the maximum between the Pre-Dividend and Stock Repurchase Common Equity Tier 1 and then the Minimum CET 1 required and then have a zero there because we want to subtract these numbers. If this is negative, it means we can’t issue dividends. If it’s positive, it means we can issue some amount of dividends.

We can see here in every single year, it turns out to be a negative number and the max function takes it to a zero. You can see why looking at it because our Minimum CET 1 is higher than the Pre-Dividend and Stock Repurchase CET 1.

So at this point we could go in and finish the rest of the schedule.

[11:00]

But what I actually want to do is before finishing this, I’m actually going to jump down here to the Share Issuances and the reason I want to do this is because I want to show you what it looks like when you have actual numbers filled in. Once the company starts issuing shares, their Common Equity Tier 1 is going to go up and then they can actually start issuing dividends in some future period. So we’re going to deviate from the order on the right-hand side a little bit and just start entering some of these numbers.

With the Share Issuances and the Shares Issued, remember that the company went public midway through this first year. We can get some of the numbers from their IPO prospectus. After the first year, of course, the company is not going to keep going public. That doesn’t make any sense. But they will continue to issue shares most likely because they have been doing that historically.

We have to do some guesswork here to back into some of the numbers. I’m going to show you how that works right now.

[12:01]

So for the Proceeds from Shares Issued in IPOs and FOs, if you go to their IPO prospectus, they pretty much tell you what it is right here, £82 million. So we can just enter that number. Now, for the number of shares issued in the IPO, we want to get the number of New Ordinary Shares of 31 million and then the number of Ordinary Shares subject to the Over-allotment Provision.

We don’t want the 43 million number here because these are just shares that already exist that selling shareholders are selling to other parties, but they’re not new shares. So they’re not going to increase the company’s shares outstanding count. Only the new shares and then anything subject to the Over-Allotment Provision, also known as the Green Shoe in an IPO, only those are going to contribute to the share count here.

So I’m going to say 31.034483 and then we’re going add the 11.25 to it.

[12:59]

So we have the Shares Issued in the IPO. Then for the number of Shares Issued outside the IPO, just on the normal course of business each year, we know from the prospectus that the total number of shares outstanding will be 250 million after this takes place. We also know from the income statement that the company’s current ending common shares around 185.3 million.

So what we can say is that the Shares Issued in the course of the year are equal to our basics shares, the 250 million number, which we’ve defined up at the top and then we can subtract the 185.3 there outstanding right now and also subtract the 42.3 right here. We’ve got the 22.5. I’m going to copy and paste this as a value. So we just kind of backed into that one. And then for Total Shares Issued, we can just add those up.

Now, we also need to think about the Common Share Issuance Proceeds right here. So how much in proceeds was actually generated by these?

[13:59]

This one is a little bit tricky because if you look at the company’s interim report, they’re saying the net proceeds from issuance of ordinary share capital are 86.3 million. So about 4 million or 5 million higher than what they received in the IPO. This interim report was from midway through the year.

As a simple approximation, we can say that it will be around 10 million for the entire year because it appears to be around 4 million or 5 million for the first half and then we can just add these up.

Now, you might be looking at this and looking at the math and thinking in the past, they always issued shares at around one pound per share. Then this year, it looks like a much lower number. It looks like more around 50 pence per share or half a pound per share or something like that. I don’t have a good answer to this one. It’s a bit of an inconsistency, but it also doesn’t make a tremendous difference. So we are just going to go with what we have here and not worry about this issue too, too much.

[15:00]

So we know it was issued in this year. Already you can tell that our schedule has changed because now the Pre-Dividend and Stock Repurchase CET 1 is significantly higher. We have more Capital Available for Dividends. So we can actually issue dividends now.

But before we jump back to that, let’s just finish what’s here. For this row on IPOs and FOs, we’re just going to set this to zero because the company only goes public once and for FOs, we’re really going to group it down here along with Common Share Issuances.

Now, for this number, for the amount issued, it’s pretty clear that the company has been doing this historically on a pretty regular basis. So one very simple approach is just to assume that they continue to issue some amount each year and we can take the five-year average for these. So we get to around 37 million and copy this across.

Now, the company never discloses what they expect to issue in the future. So we don’t really know.

[15:58]

Unlike some of the others like the cost to income ratio or the dividend payout ratio or others, they just never really say anything about this. So we’re making a rough guess here and picking something that’s in line with the average over the past five years. And then we can sum up all of these.

The other thing we need to do is think about how many shares these represent. Remember, yes, they can issue more shares and then therefore as a result, have more in regulatory capital and therefore be able to issue more on dividends. The problem is that issuing these shares will create additional shares, as you’d expect. So it’s going to dilute the existing investors in the company and this is going to come up later on when we look at the valuation, but we want to set it up now and get these numbers now.

As a simple approximation, for the Average Stock Price of Issued Shares, we can just go up to the top and take the company’s current Share Price right here. No, this is not going to be accurate.

[17:00]

But to get anything better than this, we’d have to go into the future and somehow project what they’ll be worth in two years or three years or five years from now. That is also very error prone. So just to keep things simple, we will take the current Stock Price and assume that it stays at that level going into the future.

And then for the Shares Issued in the IPO, we can take the proceeds and divide by this price and copy it across. And then for the number of Shares Issued outside of these events, we can do the same thing and copy this across and then we can sum these up.

So we have that. Let’s just check the links quickly on the cash flow statement and make sure that these are coming in properly. It looks like they are. We have proceeds from Common Share Issuances down here. So this part appears to be correct. Our cash at the bottom still roughly matches up.

[18:00]

On the income statement, we are going to change this calculation a little bit for Ending Common Shares. We’re just going to link it to what we calculated above the Total Number of Shares Issued. So we’re taking the old number and then we’re adding the new number and we are copying that across. It’s the same number, but it’s a little bit better to link to the specific schedule here.

So we have that and we can clearly see that now the company is definitely capable of issuing dividends. So let’s go back in and fill this out.
Remember that the way this works is we assume some targeted ratio and then see if the company can actually issue dividends that correspond to that ratio.
In this case, they make it very easy because in the investor presentation, they say directly here that there’s a modest maiden dividend expected in 2016 rising to 30% of post-tax statutory profits by 2017 with a progressive policy thereafter. They’re intending to keep this 13% CET1 ratio.

[19:00]

So they don’t give us the exact percentage, but it might be reasonable to assume 0% in 2015 and then 10% in 2016. They’re saying 30% by 2017 and then a progressive policy thereafter, which means they’ll probably increase it gradually over time. So I’ll say 35% and 40%. I’ve also just added some comments here from another window explaining where all these numbers are coming from. So we have our Targeted Ratio.

For our Targeted Dividends, let’s just take this ratio and then multiply it by Net Income to Common, which is on the income statement right here. Copy this across. And then for the Issued Dividends, it’s pretty simple. We take the minimum between our Capital Available for Dividends and then the amount that we’re actually targeting. You can see that in pretty much all these cases, the Targeted Dividends are well below what we have available. This is going to change as we fully link the model, but we’re still going to end up with a good amount of excess capital here.

[20:00]

And you can see the Capital Available for Dividends does go down over time because of how dividends in turn flow in and are producing the company’s Common Equity Tier 1.

So we have our Issued Dividends. Let’s check the cash flow statement to make sure they show up there. It looks like they are showing up there. That’s good. As always, I have some notes over here on the side for your reference. If you want to look at this afterward, where we’re getting some of the numbers from, how we projected different things here and so on.

But since this is all setup now, let’s move into Part 4 and compare our results to those in equity research, specifically let’s go back and look at the Barclays initiating report. They make it a little bit difficult here because they don’t directly state the dividends. They do have net income, though, and they do give us the payout ratio. So they think that it’s going to be a 20% payout ratio followed by a 25% one. We have a 10% one followed by a 30% one.

[20:59]

So in the end, it adds up to roughly the same amount but it is distributed a little bit differently. So they’re saying 20% and then 25%. If you think about the math here, 20% times 76 million, which is what they have for net income is 15 and then 25% times 99 million is 25. So they have 15 million and 25 million in dividends.

In our case here, where we’ve projected it, we have much lower numbers. We have 5 and 18. Of course, we also have a much more pessimistic view of the company than they do, so it’s not necessarily wrong. If we change it to the Upside Case, let’s do that right now, then we have 6 and 21 or 22. So again, we are definitely below their numbers. It’s not quite as much of a difference in the Upside Case, but we’re still a good amount lower.

[21:57]

It’s really consistent with the other findings so far, which is that across the board our loan portfolio is lower, our net interest income is lower, our net income is lower than what they have. We’ll end up probably adjusting some of these, at least in the Upside Case later on to get something that’s a little bit more optimistic and a little bit more consistent with the consensus views of this company.

So with that, let’s now do a recap and summary since we’re at the end of this lesson. The dividends for a bank are completely dependent on the regulatory capital and its net income and the bank’s targeted CET 1 ratio. You could try to factor in other ratios, but it’s best to keep it simple and stick to something like this, especially for a relatively straightforward bank like Shawbrook. Stock issuances and repurposes will also factor in. Often you have to project those before you can even get to the dividends because they’re going to determine the dividends as they did here.

We assume this 13% CET 1 ratio and looked at the minimum capital required to meet that.

[23:00]

And then how much was available from the company to actually issue dividends. We assumed targeted payout ratio. If the targeted dividends were less than or equal to that available capital required to meet that 13% target, we could issue them as planned, otherwise we would have to reduce them.

We also need to forecast the share count based on issuances and repurchases and then link everything properly and then we did a quick comparison at the end with much lower numbers, once again. We’ll we address that towards the end of this module when we go in and make one final tweak of the numbers.

That’s it for this lesson. Coming up next, we’re going to get into regulatory capital. You’ll see how to calculate all the numbers beyond just common equity tier one and also how to get to the measures and metrics introduced in Basel III and CRD IV, namely the liquidity coverage ratio, the net stable funding ratio and others.