Welcome everyone. Good afternoon.

Just going to give it a couple minutes for people to come in.

Just got a screen up here so everyone knows they're in the right place.

Okay, we seem to be holding steady, so, um, I'm gonna start.

Welcome everyone back to our fifth session. The time is really flying here, and we're going to be covering today in our Felix webinar, the three statement modeling, which is usually near and dear to most analyst's heart. Again, I'm Chris Cordone, your instructor for this series. I've got, our TA Yolanda Wadowski here as well.

Hi everyone.

And we are going to do another quick hit. Because you know, these two hour sessions really seem to fly by, well, they fly by from me.

I hope they're not the opposite for you, but, it does normally take I think about sessions as a teacher. You know, we do a morning sessions, kinda like nine to noon sort of thing. And, you know, normally it takes a good session to kind of like a morning session to tee up a model. So we've got two hours, and that means I'm gonna have to kind of just get into it and and start cranking through it. I wanna make sure we can get through this from, from soup to nuts. So it, it is a three statement model.

The three statement model is kind of a traditional sort of backbone model of banking.

When do you do a three statement model? Well, we typically do it when we are either engaged with a client or, or perhaps in a competition to win business.

And we need to kind of see some results of a potential transaction.

The three statement model is certainly, you know, the thing that you would probably want to start with you don't always need to have one. However, as we get into things like DCF and, and leverage finance, we can actually get by without a full three statement model.

But you know, as the saying goes, if you can do this, you can do anything.

So this is probably the, the more challenging because there is the, the tying together of the three statements. So what, what makes this kind of pull together where we are having covered income statement, working capital cash flows.

We did that little dip into advanced accounting m and a accounting last week. But what, what makes this a little bit different is, let me share my screen is that if you think about what's happened how this is gonna tie together, what we've done, you know, we didn't do a lot of the entry level accounting stuff, but if you think about, you know, you've got a, you know, you've got like, let's say your, we'll call it year zero, right? You've got your balance sheet here and your balance sheet basically, you know, forecasts some level of assets, liabilities, and equity. And of course we've got the income statement here is providing that kind of link, right? And then as we discussed, you know, that cashflow statement as we looked at it two weeks ago, was really merely a reconciling statement, which is to say, how did we get from year zero kind of from the previous year, zero minus one, two year zero? And the cashflow statement just kind of sits there sort of as that reconciling document that helps us figure out how cash changed.

So that's something that we, you know, that we've kind of covered.

So now we're in a model, and, you know, what do we model? Do we model the past? Well, no, you don't model the past. The past is done, it's already there.

It's in audited statements, or it's in the company provided statements. So we're not auditing. What we're trying to do is we're trying to get to a forecast. Now.

The forecast is there to help us value the company.

It's there to help us model a transaction help us model or understand how debt pays down, right? So the forecast is needed because that's sort of where, you know, where we get paid here, we get paid to do the stuff that's going to impact future shareholder value, right? So, you know, the historical part of it is kind of done for us. So we're in year, we're sitting at year zero, time zero, and we're going to, now project our year, year one, right? So year one, let's say is over here.

And h how do we get to year one? Well, you know, we're gonna see this, but we're, we're not doing what the accountants do, which is essentially totaling up a bunch of actual transactions.

We're making assumptions, right? We're making assumptions based on how we think things are gonna change.

We're forecasting an entirely new income statement, right? Which is, again, entirely made up.

And what that's going to do is that's gonna go ahead and, plug that equity section. But overall, the balance sheet's gonna, is gonna change, the balance sheet's gonna change, and we don't exactly know how it's gonna change until we see the results of it in the model. So, if I were an accountant and I were looking from one, you know, how things change from one year to one, I would do have all the transactions where in balance, every step of the way, balance, balance, balance, make a sale, revenues, retained earnings, cash, accounts receivable, whatever it is, it's all in balance. So that's not gonna be the case here. What's gonna happen here is we're gonna have, let's say, an asset balance that that changes a little bit, and maybe our equity grows quite a bit because of the profits.

And that therefore creates a situation where our funding side, because we've got a lot of retained earnings from this one year, creates a funding side, which is out of balance with the left hand side, which is the asset side, how we used them.

We didn't forecast use for all of that equity or that all that new funding.

So our model is now kind of sitting here in year one, and we're no longer in balance.

And this is how we're going to incorporate that skill that we learned on the cashflow statement. The cashflow statement is going to tell us how cash changed, and then that's gonna enable us to kind of plug that gap, so to speak. So the cashflow statement in the model is really, really, really kind of where the money is at, no pun intended. You know, it's where the, it's where we're gonna figure out, you know, what really happened.

And we'll, basically get to do a lot of that kind of practice that we did a couple of weeks ago. Now, obviously, what I've shown here in year one could go the other way, right? We could forecast, for example, a significant growth in assets, inventory, CapEx, you know, whatever it is, in which case we might find ourselves short on the funding side.

And then that, what that is going to do is one, it's gonna show us, one, how the model can be flexible, right? How the model can be flexible and change. And, and also you know, it, it'll, it'll show us kind of how it plugs the other side of things, how it, how it tells us that we need funding. So while I'm on that subject, I'm not gonna do a lot of theory here upfront.

I'm gonna come back to it at the end.

So I make sure we have time to get through an entire model with the, uh, circularity of interest expense, which is something, you know, we'll talk more about the, the, the complexity of this type of model.

But there's kind of three things, three qualities of a model. So if we, as analysts give birth to a bouncing baby model, and we look at that model and we say, oh, what qualities would I like you to have? When you grow up and you get into a deal and you're working, you know, a deal and bringing in money for the bank, what qualities would I like you to have? Those qualities are going to be these three things.

The first thing is we want the model to be, as we discussed, hang on, we want the model to be, as we discussed, flexible.

So it's gotta be able to change, and we will see how that's going to work. It's gotta have assumptions.

It's gotta have the ability through formulas, right? Through formulas, not hard codes to change.

So first thing is gonna be flexible.

The second quality that we would like our model to have is that it's gotta be user friendly. So by that, what I mean is that, you know, we spend a lot of time in our models, kind of going over them, building them, going over them, reviewing them, adding on, but ultimately, the model is going to be sent to other people.

And it may even be passed on, you know, to other analysts if the deal kind of goes away and then comes back, or if it moves to a different area of the firm.

So somebody needs to be able to pick up the model, and they need to be able to see in the model immediately, how is this thing set up. So by user friendly, there needs to be a flow of information. Uh, that flow of information is kind of what I would call from inputs to outputs.

And if we think about what the inputs are in the model, well those are like the assumptions, the data that we started with.

And then what are the outputs in the model? The outputs are the financial statements, the ratios, the sensitivities, right? So inputs that would be assumptions, hard-coded data, to the financial statements themselves.

Maybe the valuation, the sensitivities, the ratios, those are the outputs. So there's, there should be a flow to this.

We also should have kind of a style, friendliness.

We should be able to tell what an input is versus an output. We should be able to tell what an assumption is versus a hardcoded.

So we need to have kind of a format for these different types of inputs and outputs.

Now, uh, there's a lot of other user friendliness stuff that, that comes with experience, but these are just kind of things we'll, we'll think about, you know, to start.

And then the last thing that every model needs to be in the world is it needs to be accurate.

So we need to have a way to build the model with some checks, uh, in place, some sense checks, you know, just some kind of, you know, ways to look at the model as we're building it.

And then we also need to be able to go back to the model and figure out sort of, you know, if it reads correctly. So we need sense checks, along the way.

And then we need sense checks at the end.

And one of the things that you'll hear me saying, as I'm building this is, we've gotta get out of the mentality, particularly in the early stages of learning how to build models. But, but particularly as well, you know, as we get better at modeling, and I can be kind of guilty of this as well as I work on models and, you know, I don't want say, rush through them, but sort of you get comfortable and you just start, you start trusting your skills and your skills are growing, and then next thing you know, you've made a bunch of errors because you, took your eye off the ball., So I like to implore that as we're building it, we've got to get away from how wonderful my formula building, how wonderful my knowledge of Excel is, how many different ways to copy, and I'm just so great. I'm so great.

Look how quick I am. You know, I'm just knocking this thing out, right? It's gonna be early bedtime tonight, and then that's sort of where the mistakes come in. So I will, I will be working on that, you know, with you as we go. So Yolanda has just sent out a chat about this, but just as a reminder these are a little bit of I guess a one person show in the sense that we don't do a lot of interacting just because of the nature of the way we're set up with people working.

And there's some confidentiality stuff going on.

So I do a lot of talking and you have questions.

We have a question and answer feature, which everybody can use.

Please dump your questions in there, and Yolanda will tackle it if she thinks I can address it, she will punch it out to me. And then if you also, we have chat as well.

Some people can't see the chat, which is why we try to encourage people to use the, the q and a.

So I'm gonna also keep a kind of a running list of sort of my steps to build a model and we'll, we'll just keep coming back to this. Hopefully I won't forget about it as we go. But this will be sort of our running tally of where do we begin. So, you know, where do we begin? Well, look, a lot has to do with what the purpose of the model is. I mean, you might be coming from equity research, there's no transaction, right? Maybe you're just starting coverage. You probably inherited a model if it's a, if it's ongoing coverage. And those models can be insane in terms of how much detail is in it and how long they go back.

And I do some auditing of equity research models for a big bank. And they're, they can be intense, within kind of the private side of the business, you might get numbers from management and or you might have to download them from like a database like FactSet or CapIQ or something.

So obviously that's gonna be the historicals.

We always start with historical data. So we enter in the historical data, and we then, what we typically do at this point is we get the raw data in, and then we manipulate it to get it to look the way we want it to look in the model. So, you know, what I mean by that is if I pop over to if I pop over to, uh, Felix for example, and I look at let's just say we're going to model Nvidia, there's kind of a hot company right now, and if I just take a peek over it, what's on their income statement? Their income statement is typical accounting income statement.

This is what you'd be downloading from capiq, if you just did like, kind of a basic download or factset or something. Or if you just went to Felix and you can copy the now this is a quarterly one. Let me just go to an annual one. We typically model off of the annual ones.

Let's say you were starting from scratch on Felix. You can come to Felix, hit the copy button, and Felix will copy this table for you into Excel, right? So that, that's kind of a, saves you a big step, like the database downloads from having to go ahead and type all this stuff in.

Yes. Yolanda, did I see your hand up? No, she's saying no.

I thought Yolanda was gonna ask a question. Okay.

Sorry. Sorry, something that was a mistake.

That's all right.

So let's say you copied all this in. Well, you know, you, you, you pro you might not wanna model all this detail, depends on the model.

You might just wanna model revenue opex EBITDA, right? Or you may, you know, you may be building your entire forecast off of EBITDA, so you don't need all this stuff.

So the first step after the historicals is to kind of manipulate the data to look the way you want it to look.

And then we, we can go to the next step, kind of the next part of step one, which is, once we get this historical data in, we need to total it up ourselves. Now, obviously the accountants have done totals and their totals are correct, I'm not implying that they're not.

But once we start manipulating the data, we need to kind of benchmark back to what the accountants have, what the auditors have to make sure that we, in our manipulation of this income statement, we didn't get off track, we didn't add something instead of subtracted, et cetera, et cetera. So, you know, if I were modeling and for Nvidia here, if I wanted to model based off of EBIT, right? You know, EBIT might be this income from operations number, I may add back the acquisition termination cost.

Because we know that that's not traditionally part of EBIT, whatever it is.

The point being is that ultimately when we get to the bottom of our income statement, which is reported net income, that's gotta match this 4,368.

And what that's gonna tell us in our model is that we built our formulas correctly, no matter how we manipulated things, that we built our formulas correctly. And that's like check number one on the model.

So let me have us pull up the file that we're gonna use today. So I can work from an example. This is the file for today, which is in the three statement modeling October 25th.

If you just kind of click on that three statement interest circular workout empty, that's what that stands for.

And if you pop that open, we'll see this file here and go over to the tab.

And we see there's a model one, model two, model three. So there's some gonna be some opportunity for you to practice on your own. If we can build a second one, I doubt we'll have time. I will they're very simple models, so it shouldn't be too hard.

But the first thing we we see here is that the, the historical data has been added for us.

And as I was saying to you now this is based on, on Hershey kind of the opposite of Nvidia in terms of growth.

You know, high flying stock, Hershey is not flying that high.

But if I were to look at Hershey's income statement, for example you know, Hershey's income statement is, looks a lot like NVIDIA's in terms of its content, right? So clearly we didn't model that way. Well, we don't really need that. We want to get to EBIT. Because EBITs gonna kind of get us, you know, it's gonna get us kind of the building block of EBITDA.

So that's sort of why we did it this way. But regardless I would want to make sure that my net income, you know, kind of matches, you know, kind of matches what's on the bottom. Now, I, I gotta be very clear about something. I know that this says Hershey.

And I know this says, 19 20 19, 20 21.

I'm not entirely sure that these numbers, let's see, 8,971. Yeah, this looks pretty good actually. 8,971 8,149.

So we should be able to get to the net income that matches here.

Typically, I don't do this when I'm teaching this model. I don't try to match to her. She, because I don't know what, who built this model at Financial Edge. I wasn't there for it.

I didn't build it myself, and I don't think Yolanda did.

So it's possible that some other things were done to a bridge it.

But the whole point is that once we get to the bottom of this net income, we should be able to match the source data where this came from.

And, you know, and I, and I think it probably will.

But I just wanted to point that out before I get into a, you know, big pot of boiling water over this.

So we're gonna build a formula first. Now, before I do let's just take a quick tour of what's in this file of this one tab model one income statement. This data has been downloaded.

We've got some data here as well that's come in regarding CapEx. I'll deal with that momentarily.

We've got some data in here from dividends. Now, as you recall from the cashflow statement section, CapEx and dividends, you know, they only appear kind of on the historical cashflow statements. They're not gonna appear on an income statement or balance sheet.

So that's why this data kind of sits separately here.

And then we move down into the balance sheet, and we've got two years here of historical balance sheet.

We have three years of historical income statements. Do we need three years of historical balance sheet? Well, I guess it depends how picky your next level up associate or whatnot is.

As you know, they only give you two years of balance sheet per, you know, per 10 K. So you've gotta, you've gotta go back into another document. And if you're doing this by hand through CapIQ or FactSet, it should not, or, you know, should not be that hard to get that third year of balance sheet data.

Why do the historicals also help us? Well, the historicals also help us because we're gonna make assumptions and we're gonna check assumptions based on the historical performance. So the more data we have, the more we can check those trends.

So I think this is a teeny weeny, weeny bit lazy, not having that third year.

But because the purpose of this model is very technical in terms of how to build things I can understand why that was done. Now, moving on down. Oh, by the way, you'll notice even here we don't have totals because we will often consolidate accounts on a balance sheet. Balance sheets can have a lot of accounts on it that we just don't need. Like we don't, we tend to not model current portion of long-term debt. That's an accounting thing. There may be a lot of small accounts in here that we just kind of shrink into one. So again, more manipulating is done on the balance sheet and we've gotta make sure that we did it right.

The beauty of the balance sheet is we don't need anything to check it either balances or it doesn't. If it balances, we did it, right? If it doesn't, we left something out or doubled up on something.

Now moving on down, you'll notice that there is no historical cash flow, and that's by design. Now, I pulled out a couple of those historical cash flow items that are critical CapEx and dividends, but I don't want historical cash flows. Why don't I, well, all they're doing is reconciling the, the previous years and we don't need to reconcile the previous years.

We trust that the auditors got that right.

And we have the cash balances very clearly, you know, laid out for us. So we don't need to reconcile cash, we don't need to prove that this stuff adds up. Ernst and Young all, you know, they all did that. So we generally do not model historical cash flows.

And that's why this is blank. Where this is gonna come into play again, is in the future. And then we've got some debt stuff down below, and we've got some already kind of pre-done ratios that'll pop in nicely for us once we're done.

If I skip up to the very top, I've got the assumptions here.

So I'll say more about the assumptions in a moment. Because that's come after we do the historicals.

So the first thing I'm gonna do now is just start building out my historical totals for the income statement.

This model has been done.

Positive revenues are positive, expenses are positive. That is a choice.

A lot of people feel very strongly that expenses should be negative.

I'm not here to persuade you one way or the other. I believe, one of our models here flips it around. Yeah, model three kind of goes the other way and does it positive, negative.

So if you, you know, if you want to try one like that, try model three on your own. But sometimes they're done this way.

It really is, you know, as I said, up to you.

So one thing you cannot do, by the way, is you can't start out with, you know, positive expenses in the forecast, in the historicals and then decide you're just gonna switch in the forecast.

Because that's just, that's just not gonna look good and it's gonna be confusing.

Okay? User friendly violation of rule number two. Now, let's, start this out. So I'm gonna build my EBIT calculation.

EBIT very simply here is revenues minus operating costs.

So I'm going to take my operating costs and subtract them from my revenues.

And I'm going put in some formatting here just because I like to see it.

And now after I enter a formula, brief pause, brief pause, right? Does that look right? It's all you gotta do. Does that number look like, I think it should look now, very simple calculation. Well, how can I get this wrong? Well, you know, you probably won't get this wrong, but if I was working previously on a positive negative model and I accidentally did revenues minus costs in a positive negative model, I get an EBIT that's not right.

And again, you need to be able to stop and look at that and say, whoa, how can my EBIT be more than my revenues? Right? So I'm just showing you that that is an example of formula.

Take a quick look at it, okay? Now if that looks good, we can go ahead and copy this to the right to get it into the other three historical years. I'm going to use control R, which is the fill to the right function.

Starting in the starting starting formula cell shift arrow select select those three and then hit control R. Okay, now I've got interest expense, and then I've got other expense, and then I've got earnings before tax.

So that's gonna be equal to my EBIT minus my interest, minus my other expense. Now, can you do EBIT minus the sum of these two? By all means knock yourself out. There's no real difference there.

I see both done all the time, as long as they're both expenses and they should be, you know, subtracted.

So I'm gonna take this now and I'm going to copy this over as well.

I'll just show my formulas out to the right. And lastly, I've got my net income, which is the EBIT minus the taxes, and that's gonna give me my net income.

So I'll put in a alt HBP gives me a border here and then above earnings before tax as well. I've got a border in there.

Alt HBP is a shortcut for that.

Now I just gotta be, I'm so curious now if these actually match Hershey's net income or not, if they do, my spiel looks very prescient. If they don't I gotta go back to whoever built the model and bang on their door at six o'clock in the morning and say, hey okay net, so net income attributable to the her. So that's, see this is some of the complications is that we're not modeling NCI here.

Some of these things can be, you know, a little bit confusing to beginning students. That's why, you know, I think we'll see 1,275.4. Yes.

1482.8. Yes. So that would tell me, right, that, For 20 and 21 the model's a little old at this point, right? So 2021 2022 is now a historical year, but when the model was built, it's gonna be the forecast year, right? So that's just the nature of teaching materials. So looks good, right? We have confidence in these formulas. And now when I build the forecast where I have nothing to check, because it's all made up, at least I can say with certainty that the formulas themselves are correct, right? I just gotta make sure that everything else is good, but the formulas are golden. Okay? Let's move down and do the balance sheet.

This is easy as pie because again, all I need to do is total up everything and my balance sheet will balance if in fact I have entered it correctly and added it up correctly.

And it looks indeed like it does. Now, the last thing I'm gonna do is just put in a little balance check.

Now I can see that these match, so I'm not, you know, I'm not gilding a lily here, but what I am doing is in the future, this isn't gonna balance not at first.

And I need to be able to check along the way as I'm solving the imbalance that I'm getting close or get far away or what, how much am I off or whatnot.

So I'm gonna do this little balance sheet check, which is liabilities and equity minus the assets. You could do it the other way too.

I prefer it this way because it's just the way I look at the numbers.

But you can do however you like. And at this point, again, we're, you know, we're kind of ready to start building the model, so to speak.

I'm just gonna pause there and see if there are any questions or anything at this point that you're curious about or want to want to question something that I said. Please by all means raise it in the q and a, the chat, digital hand, whatever works for you. Okay? Looks like we're pretty good.

Now step two in this totals calculated match, historic match source document.

Okay? Step two, we gotta build the assumptions.

Now that is probably when you first were introduced to the concept of modeling, you thought, well, that's the cool part.

Or that's, you know, that's the interesting part of modeling, right? Coming up with like, you know, figuring out, coming up with the assumption, figuring out where the revenue's gonna go, where the cost gonna land, et cetera, et cetera. And you know, that is kind of neat, probably will not in your early stages of your career do a lot of assumption building. Now you might, it depends on the nature of the model and you know what you're doing.

But this is hard.

Building assumptions are hard and they're hard because, you know, you gotta have knowledge of the company, you have knowledge of the industry and, you should also be pretty good at building models so that you understand the implications of certain choices.

And I'll get into that more momentarily.

But building assumptions generally will come from somebody more experienced or you will get it from an actual source yourself.

So for example, if you were just using Felix, you know, we have the earnings estimates here for Hershey for the next three years.

And that's typically all you're going to get in equity research. These are the, you know, the earnings estimates from consensus on Wall Street. And, these can be sort of the assumptions that you use.

Most people that are modeling are not, you know, kind of pulling stuff out of the air. They're not guessing they're going to sources of people with more inside knowledge. So who might that be? Well, it could be the management of the company. If you've got a relationship, what they do budgeting, they know, I mean, they don't know for sure, but they certainly know how to forecast. They're gonna give you assumptions if not them, you can go to equity research and I think I'll just do this all in once because I'm gonna need all this space equity research or from senior team member, right? So that's kind of where the assumptions are gonna come from. Okay? So we look up here and we see, you know, that we've got some assumptions that have been given to us.

We've got the income statement assumptions, we've got the balance sheet assumptions. Again, no cashflow assumptions here.

Not as a statement because the cash flow's going to do the business of balancing force. We don't, it's, it's kind of the add-on feature. It's not, it shouldn't be bringing anything new into, into the model.

We've also got some assumptions here for the debt.

So the revenue build in a model is probably, if you are building out a model is where you're gonna spend most of your time, the revenue build and then probably the, you know, the costs, the EBITDA part of it, that's where you're gonna be spending a lot of your time.

And that those can get super complicated and super detailed.

And then again, sometimes they're not. I mean, if you've got a really good research report or a really good set of numbers for management and a research report, well you don't really need much more than that.

So you can kind of cut through a lot of the, a lot of the fluff and just get to the numbers you need.

So this is obviously very simplified, but I have to say I've seen live inaction models that were also pretty simple in terms of how they modeled out revenue and EBITDA.

So what we're gonna do here is with the assumptions is not very much because it's been done.

So the next step in the model is actually to begin building out the three statements.

And the first thing that we do here is we, we build the income statement.

Now the income statement is handled first, unless we're talking about a financial business insurance or banking.

Those are balance sheet driven businesses, right? So we start with a balance sheet. There, there, there is no income until the balance sheet is in place.

Because the income is derived from, you know, the trading, the lending, et cetera. Now, in a more traditional or manufacturing company, even a service company, a software company, et cetera, et cetera, the income statement is the driver because that's really, you know, where it all begins. Now, you know, obviously you gotta have assets and all that stuff in place before you can start making money, but, but we're talking about an ongoing business here.

So we're gonna model the income statement and we're going to leave out interest.

We're gonna leave out interest for right now, we're gonna come back to that.

So we should be able to get from revenues down to net income, leaving, interest blank and, and then still be able to kind of get to a finished place in the model without interest.

Interest is going to be the challenge, right? That's gonna bring in the, the complexity to this kind of model.

So what I'm gonna do here is I'm gonna go over to my income statement and I'm going to begin building out the formulas.

The way we teach to build formulas at Financial Edge is we, we teach them to build we teach people to build the formula with the assumption first.

And that's sounds like it's being nitpicky and you may or may not want to kinda leave that behind you. It also depends. There's some, there's some nice add-ins in in Excel through through CapIQ and whatnot that that does some of the formula auditing that I'm gonna talk about automatically.

But if you're just working off of basic Excel we teach the put the assumption first. And the reason why is because when we put the assumption first, it becomes easy for us to audit this formula.

So I'm, I'm gonna show you why we do equals open parentheses one plus doing a growth rate if and the reason why, if I look at what the revenue assumption tells me, it is a growth formula.

So it's this 15.3% on top of the previous year, 100% of the previous year plus the 15.3% of growth in the new year.

So I'm gonna go up and grab that F6 close the parentheses or the bracket, and then multiply by the previous year.

And again, just a quick sense check it's a little jarring because I went from eights to 10, but I am growing by 15%. So that looks about right to me. Now, if, if I, if I doubted that and I wanted to go and check to make sure I had the right assumption or just check the assumption in general, if I hit control and the left square bracket, if I hit control and that left square bracket it, it'll hop me up to that first argument of the formula.

And that's just a nice, some nice little feature.

It only works for the first argument. It stops there.

So I can't keep pressing it, it just stops there.

So having the assumption or the farthest cell contributing to the formula first makes that kind of really handy.

Now, I could get there on my own without that, but I mean, you know, it's nice and handy. Now if I want to jump back to where I was, I hit F five and then enter, and it brings my cursor back down here.

This is a pretty small model so it doesn't go very far, but in a larger model, multiple sheets stuff coming from different sheets.

I worked with an analyst last night that had, there were like 30 sheets in the model and it was nuts.

Having that ability to audit is really nice.

So Ctrl left square bracket and then F5, the F5 key, not f in five, but F5 and enter gets you back. Okay? So lemme put my formula out here and we're gonna move on.

Operating costs are no longer a growth function, it is a percentage of revenue.

So I'm gonna take my 77.7 times the revenues and that gets me what, what looks like a reasonable number there.

So I'll put that in, move these out here.

Now, I said that I was gonna leave interest last, so I meant that.

So I'm gonna just highlight that. So we don't, we don't forget other expense. Well, first of all, like what is other expense? I mean, you know, it could be anything, could be a lot of things and it'd be nice for us to do a little bit of work and figure out what's in that other expense. It's possible, as we talked about last week, that this could be income from another investment that Hershey has, maybe in a Brazilian chocolate company or something like that.

It's possible. It could be a number of things, but without the document here to look through, and I don't wanna spend a lot of time looking through it, I'm kind of skipping over that sort of stuff in this, this session.

But it would be nice to know what that is so that we can figure out, you know, perhaps how we should project it. Now what we've got here is, just sort of a very even kind of slightly appreciating number.

And so I'm gonna link to that number because that is the assumption itself, that one 20 is the assumption itself. Now, you might ask, well, could I have just could I have just put that one 20 assumption right in my output? Probably. In other words, take modeled it so that this one 20 is kind of already in here, right? I could do that.

I think most of the models I look at generally have, assumptions separate from the output so that you don't have a lot of kind of odd formats and whatnot in the out. This is, you're gonna, you're either gonna print out, you know, or I know nobody prints anymore, but, or print or put it into a presentation.

So you want it looking clean. Now, having your assumption format popping up in the middle of this, I think it's a little weird, but yeah, in theory, yes you could have that right there.

So I've got my other income in the box.

And now what I'm going to do here I forget if I mentioned this.

I was talking so much I overlooked it. So I'm gonna, I'm gonna say it now.

When we build, it's time to do EBIT or it's time to do EBT I get on in class, I always at this point turn to somebody in the room and say you know, Jane, how do I calculate my EBIT? And if Jane's been listening and paying attention, which she usually is, she'll say, copy this from the previous cell.

Because what that again does is it, it takes a formula that we've checked as being golden and copies it into the forecast where we're a little bit kind of uncertain of the outcome.

Now could you do it this way and sub, do the calculation again, you could, but generally, you know, one of the unwritten rules of modeling, or at this point, they're probably all written by somebody is you never build a formula twice when you can copy you copy.

And part of that is that what I, it's what I call the lazy modelers rule, right? That all modelers by nature are a little lazy.

They're not careless, but they're a little lazy because we like to do whatever we can to build a copyable formula. We don't want to do a formula twice.

Part of it is that it takes time.

The other part of it is that we know the more times you build a formula, the more times you're likely to make a mistake.

So earnings before tax copy to the right net income, I haven't done my taxes yet, but I will be copying that to the right as well. So let me pop up now and do my tax expense. Now, you might be saying, but Chris, wait a minute, how can you do this? You don't have interest expense.

These numbers are just dead wrong. I know that, I know that they're wrong.

But they will eventually be right And we will have interest flowing.

And once interest is flowing, I want it already in the formula.

I don't wanna have to go back and fix a formula because I will forget on the three and a half hours of average sleep that most analysts get, you will forget that by morning.

And at my age, I forget on a full night's sleep. So, you know, definitely get that into the formula.

So tax expense will be high, right? It's gonna be high because we don't have the tax shield from interest.

So that's like a trigger in our head. Like, okay, I'm gonna do my, my tax calc, I'm gonna go up here, I'm gonna get my tax percentage, which is 18% and I'm gonna multiply it by earnings before tax.

And that's gonna gimme 393.6. Now, that actually looks somewhat normal to me, so I'm not that concerned about it, but in reality, this number is gonna come down once we have the tax shield in on the numbers, right? So let me just put my formulas here for everyone to see.

Take a quick pause, see if we've got any questions.

It looks like we're we're moving along, moving along.

And, now what I ca what I'm gonna come to next is this just in terms of the order of the, model. We've got the calculation section, which is stuck in between the net income, the income statement and the balance sheet. So first of all, like what is the calculation section? I didn't, I didn't talk a lot about that.

The balance sheet is technically, you know, kind of where we are next, but the calcs is, oh boy, the calcs is necessary for several of our balance sheet calculations. And what the calculation section is, is it's kind of just a little scratch paper for us to build out some of the more complicated accounts like property plant equipment, like retained earnings.

If you remember from when we did cash flows a couple weeks ago, there were a couple of different, there were a couple of different things impacting those accounts.

PP&E went up by CapEx, but it went down by DNA retained earnings, went up by net income, but went down by dividends.

So we have the ability in, in a model because we've got a lot of cells and once you pay for an Excel license, they're all kind of free to spread this thing out a little bit, not build overly complex formulas if you don't need to, you're gonna want to see how these accounts change. They're very important.

So we're gonna do that in the calc section and then we'll bring that into the balance sheet. So, you know, we can, you know, we can kind of do that first. I would, I would I call it a step in and of itself? No.

but we are gonna have to, you know, do the calcs for several of these kinds of accounts, PP&E intangibles, if it's perhaps a software company or something, maybe healthcare, it's got some, you know, some intangible stuff in there.

We would need that.

Retained earnings.

Can't really think of any other calcs that are, you know, in there.

You'll notice that we've also got working capital in here and working capital is in here to help us with our cashflow statement. It's not really to calculate the accounts themselves, but that's technically calculations of scratch paper.

You can do anything you want here.

So I'm gonna move on down to the balance sheet, and I'm gonna do the balance sheet except for cash and accept for the revolver.

I'm gonna leave out, I guess I've got to do it in caps to be consistent.

Leave out cash and revolver, leave out the cash and the revolver.

Now why am I leaving out the cash and the revolver? Well, um, if we think about, let's just think about cash for a moment, right? If we think about what happens at the end, end of the year of lots of transactions, right? Accountants totaling them up we, you know we present our financial statement, what what happens there is that we learn at the end of all that through the cash flow statement, right? Through the cash flow statement, how did cash changed? And ultimately that cash is sort of like the result of how our business did. It's the result, right? Did we generate it? Did we use it? Did we run out of it, right? Generate it, use it or run out of it, right? Those are kind of the three things if you're running a business that you're keeping your eye on with cash.

So cash is the result. Now, you know, in theory we, we could forecast, we could forecast cash and sometimes you will, but in this very simple kind of model we're not, we're just going to use cache as the plug. So again, if I go back to my, if I go back to my, little drawing here, what I drew out basically said that, you know, in this first year of the forecast, which is where we are, what happened, well, what happened was, I, I just created, you know, graphically a situation where, you know our funding side of the balance sheet was forecast to increase faster than we could spend it.

So I didn't forecast a lot of inventory and I didn't forecast a lot of CapEx. I just kept that very simple, but I maybe forecast that we took out a loan or maybe I forecast that we had a very high income year and didn't pay any dividends.

And that created this sort of growth in the right hand side of the balance sheet.

So the cash here is the result of overfunding and not having anywhere to spend that. Because I didn't tell the model to spend the extra cash, right? I just made the assumptions that I made for the balance sheet.

So the model is basically saying, but I have all this cash from the funding and you know, ultimately it's gonna stick it in the cash line until we figure out maybe, maybe something to do with it next year. So we're, we're leaving cash out of this, you know, kind of for now until we get to that point where we can understand what happened.

And all I can say is the same exact thing, you know, for the revolver, the same exact thing for the revolver.

If I have that same situation where I've got, This growth from year zero to year one, I won't draw year zero over again. Well, yes, I will draw you, I will draw year zero over again.

And now maybe in my year one, I'm forecasting a massive amount of inventory buildup and capital expenditure to grow out the distribution network.

And so my asset growth is quite high.

Now, I didn't think when I was making those assumptions, I didn't quite think about what that would entail, but whatever I had in terms of my existing funding and existing profitability from my net income doesn't cover it, is not enough.

And therefore I've got this kind of problem the other way, right? Where I've got this sort of need to fund my balance sheet, need to fund the assets that I've already forecast.

And this is going to be the revolver, just like this is the excess cash or cash. So for that reason, and we'll get to see it on paper in black and white for that reason.

For right now, I wanna leave out cash and revolver.

I'm gonna let the model tell me what those amounts are.

And we're just gonna focus on kind of the more driver accounts for the balance sheet, which are, you know, the other assets and then the other liabilities and, and an equity account.

Any, any questions on on that? Any questions on that? I'm gonna come back to it again in a moment. But for right now, let's just go ahead and leave these two accounts yellow and let's go ahead and do the remainder of our balance sheet, which is going to be current operating assets.

I've got an assumption for that, and that's 25% times revenues, and now my property plant and equipment,

is something I'm going to have to go up to my calcs to do. I can't really I don't have one assumption for that. I've got two. So I'm gonna go up to my, my calculations and I'm going to build out the property, plant and equipment using the assumptions for CapEx and for DNA.

So the way this is gonna work is we, we did this in the cashflow statements, but it was a couple of weeks ago. So I will, I will review it. This is what we call a base analysis, and it's a base analysis because, you know, we've got a beginning and additions, which is the CapEx, the subtractions, which is the depreciation, and then an ending.

And yes, this is all happening in the historical year, but we don't care about the historical year.

The historical years have already been done, and they're on the ending.

PP&E account is on the balance sheet in each of these years.

So I don't need this here. What I need is a place to jump off from to build the forecast.

So this ending property planned equipment, I'm just gonna take from my last historical balance sheet.

And what that does is when I roll it forward becomes the beginning balance in the first projected year.

Now, you might say, well, you know, can I still get to this number of 7,826? You know, using the, you know what I, what I know. In other words, I made a big speech about, hey, we've gotta check, make sure this all adds up. These formulas are golden. We can check our number, you know, and I said all that.

And yet here I am saying don't bother to do this in the historical year. Don't, don't take the ending PP&E from if I take, if I take the ending PP&E from 2020 and I bring that forward into 2021, now this is the historical year that is, that doesn't add up. If I take 6,153 plus the CapEx, plus or minus the depreciation, I should be at 6.334.7.

And yet Ernst and young, if that's, that is in fact the auditor is telling me I should be at 7,826.

So why am I being in inconsistent here? Why am I saying something? If this had happened in the income statement, I would say, oh, I must have entered my numbers wrong, right? Or something like that. Well, actually, not necessarily the case. What what's happening here is, again, the reconciliation of this account, similar to the cashflow reconciliation is not necessary because the accountants have already told us that these are the ending balances in these years, and we don't need to double check them.

If I'm sure that 7,826 is the number that's on the balance sheet, if I'm positive, I do not need to question, you know, Ernst and young. Now, as for why that still doesn't add up, it doesn't add up because in reality there are many, many, many more things impacting PP&E than CapEx and depreciation. You could, you could have sales and retirements of property plan equipment, you can have write downs, you could have currency adjustments.

And these things can do all kinds of crazy things to the number.

And we're not gonna model those things because they, they're not modelable, if that's a word. Yolanda, is that a word? Modelable? No, It can be if you want.

It's a word now. It is a word now.

So we are gonna distill this down to the, the drivers of the account.

Most companies are not in the business of selling their PP&E, they're just not. But they do have to grow it, and they do have to maintain it and depreciate it. So, long story short, beginning PP&E comes from the ending balance sheet. Don't reconcile that number please. CapEx is going to be from the assumptions, which is up here, five and a half percent of my revenues.

And then my depreciation I'm gonna do here is a negative that's gonna be 5.1% of the previous year's, PP&E which is the same thing as saying the beginning balance because the 7,826 is the same as the previous year ending, previous year ending balance. Right now I do alt equals, which is, is a nice shortcut for, uh, summing groups of numbers.

And I have my, my balance sheet account here for that. I can go ahead and lump load right in equals 7,955.8 looks good. Now, Jane, I'm gonna call on you again.

How do I total up my assets here? Well, again, Jane's paying attention, so she's gonna say copy from the left.

If Jane stepped out, you know, for a cup of coffee and missed the spiel.

And she came back in a little bit flustered and I said, Jane, how do you get total assets? She would say, well I don't know. How about alt equals? That's a nice shortcut and it's good except it's leaving out this cash box here.

So when cash is ultimately in the model's, never gonna balance.

And Jane will be crying at 11:00 PM at night with a model that doesn't balance just like I have on many a night.

Even recently. So copy across, that's not gonna be a problem.

We get the automatic formula correct And that is how that works. Okay? Revolver, as I said, is on hold.

Current liabilities, same as the current assets.

Just gonna go up and grab that assumption.

That is the 17.3% of revenues. Put that in here.

Long-term debt.

Now I have assumptions for the long-term debt and the long-term.

Debt assumptions are actually in the debt assumptions.

They will often have kind of their own little separate place.

It's just the way they like it. They like to be a little bit different.

And what the debt assumptions are saying is that we're gonna pay down a bit of this debt each year.

So the way we we're gonna model this is I'm gonna equal the previous year plus this repayment, which is negative.

And that's going to take care of that minusing the 50 here, right? Now, you might say, well why don't we create a separate set of calcs for the debt? Typically you will, and when we get into probably the LBO stuff later on, you'll see there's a separate debt section.

Because debt is something where it's just nice to have separately. In fact, you, you know, we actually do have a debt section here, but we kind of chin on it a little bit. We just do the interest there. In reality, you would do all of the repayments interest, et cetera, kind of in this separate section. Okay? Now, once again, I'm just gonna copy this over from the left and I'm gonna get total liabilities.

And last thing I've gotta do here is do the equity.

So now my equity section is the same kind of deal. It's a base analysis, it's got income, it's got dividends.

I do not care about the historical reconciliation.

So I'm just going to get my ending equity from the previous, last historical balance sheet and then roll that forward.

Now, one question you might have is you might say, well, you know, it looks like all the equity here has kind of been condensed. You know, there's no, there's no share capital, there's no common stock, there's no treasury stock. If you know about these accounts, you know that's true.

We might condense everything into one equity line and then just do a big calc for it and have assumptions for stock issuance and stock repurchase. You could totally do that.

You might actually just do retained earnings. And then a common stock. You can do this a lot of different ways.

Most companies do not issue equity as kind of a thing, right? Like it's a rare, it's a rare event when, when you, when if you work on leverage buyouts, sometimes you have to put more equity into the deal if things go badly.

But that's very advanced modeling. Most modeling that you do, you will not ever just say, Hey, how about a new stock issuance in year three? That sounds like a good assumption.

Doesn't happen. I mean, equity issuances are rare, they're expensive.

If a company's got access to the debt markets, they're going to go to debt first. 'cause it's cheaper, it's easier.

So this is not terribly uncommon. So I've got my beginning equity, I take my net income from my income statement, right? So this has already been done for me.

So we can start to see all these connections in the model coming through.

And the, the flexibility in the model is the links here is the links. So every time we change the income statement, now the equity's gonna change and it's gonna change that funding side of the balance sheet, right? So we can really start to see why this needs to all be all be connected. You cannot have hard, hard-coded numbers just popping into these cells.

They've all gotta be linked or else the model's broken.

And then once you change it, it'll never, ever work.

So I've got my net income and now I've gotta do my dividends. My dividends here.

I need to see this assumption 'cause I don't know how we did it.

What we're assuming here is that Nestle is going to pay out 75% of its net income to its shareholders. That's a pretty, pretty hefty amount.

What that would imply to me is that Disney is in a low growth state.

It does not really have a lot to do with cash any longer. It doesn't have a lot of places to invest.

It's kind of just paying out the value of the company to its investors and the people that invest in Disney invest on Disney and Hershey invest in it for that reason. Now, I don't know how it looks like they've been doing a lot of dividend payouts, so I'm just curious 'cause we've got handy, handy dandy Felix here.

Let's take a look at the cashflow statement and see what in fact, Disney has been paying out in dividends.

So, go to the financing section.

Cash dividends paid. Yeah, that's a lot.

$775 million in the last year.

And how much net income did they gen did they generate? They generated, 1.65 billion, call it.

So that that was, you know, 50, you know, 50 ish percent of their net income previous year looks like the same thing. So they're definitely paying out a lot in dividends. And again, that just means that they're not putting it back into the company. Okay? So 75 might be a little high, but that's all right. We're not here to win any equity research awards.

I'm gonna go ahead and link to the 75% and I'm gonna multiply it by the net income and then I can do the net income right here 'cause it's right there. Or I can go back to the net income above it doesn't really matter.

I do need to make it negative though, 'cause that's how we're building this.

And now my sum total is showing me that my retained earnings or my equity grew really just grow. It grows by the difference in those two, right? How much I took in versus how much I paid out.

Put my formulas here, pardon me for that.

So now I've got my equity account and I can go ahead and plug that into equity.

Again, copying over and just sort of on you know, on first glance what, what this looks like to me is that, my assets are greater than my liabilities and my equity. Now, obviously, you know, there's some, there's some stuff missing here because I, I had some cash and I had some funding, right? The revolver is funding and right now neither of those are in here.

But just, just looking at kind of where the numbers come out.

What it's showing me is that my assets are greater than my liabilities and my equity just for right now.

And that's telling me sort of on first, first glance that I, I'm kind of underfunded, right? I'm underfunded in the sense that I've got that situation where my assets are up here and my liabilities and equity are here and I'm underfunded there right now. Why might that be? Well, we're paying out a lot of dividends. So we're shrinking when we pay out dividends, we shrink the equity, we shrink the funding of the company.

We don't put that capital in to support the business.

So that's why this is potentially happening. And again, we've also got some accounts we haven't finished yet. We can't forget about the fact that Hershey had cash and had some existing debt.

We can't forget about that. So we're gonna come back and deal with that momentarily. But this is just kind of one of those sense check moments of the model where we just say, okay, how, how does this look? How does this look now? What do we do next? How do we solve this? Well, now is where the cashflow statement comes in to help us.

So the cashflow statement is next, and the cashflow statement is going to reconcile what happened to cash? Did it go up? Did it go down or did I run out? Now, a couple of things we need to kind of keep in mind., The first thing is that we need to, we need to look at all of these accounts to all of these balance sheet accounts and see what happened with them. Like how, how did we get to the, you know, from 10, 10,412 to 10,581, how did we get from ten four one two to 10,107? How did we get there? And that's gonna help us see these flows, right? And then of course we have to kind of deal with the the cash and the revolver because we did come into this year with 329.3 of cash and we did come into this year with 942.2 of borrowings.

So we can't forget about that because as you recall from when we built a cashflow statement, every single change in the balance sheet must be accounted for.

Now, thinking back to that exercise, right? Thinking back to that exercise, we also were isolating for cash.

We were solving for cash.

Remember we said let's do all of the other changes except for cash.

And then by virtue of the balance sheet equation, we're gonna come to cash.

Here we have two accounts that we have to deal with, two accounts.

So that change, that makes it almost algebraically a little bit challenging, right? We're not solving for one variable anymore, we're solving for two.

So that is the challenge here.

And the first things first, let's go through the cashflow statement and let's just see, let's do the easy stuff. Let's see what what happened to the other accounts, and then we'll figure out how we're going to handle this cash and, and revolver situation.

Now, not to beat a dead horse here, but we don't care about the historical reconciliation of the cash flow statement. So I'm gonna put that in gray, okay? Don't care about that. So I'm gonna start over here with my net income.

Now this has been, you know, this is a gift, right? This was given to us I think it's in all the files they gave it to us, the labels, right? If this were, you know, a more challenging situation, we'd have to create these labels ourselves because then we, then we'd have to make sure that we got everything right. Remember there,

the patented trademarked method to completing a cashflow statement that I have is that if, if you include everything, every change, if you capture the change in every account, you can prove that you did that and you get the four rules of cash, correct? Then in fact, you will have a balanced model.

So just to quickly review, the accounts have been given to us, so I'm not going to I'm not gonna do that. But the four rules of cash are one, you never have enough. No, that's actually not one of the four rules of cash, but, but also very true about cash, assets go up, cash goes down.

So buy buy assets, you have to pay for them. Assets go down, cash goes up, selling assets, collecting on receivables, et cetera, et cetera.

Liabilities and equity go up.

That means our funding went up and cash goes up and liabilities and equity go down, cash goes down just like paying dividends, just like paying back debt, et cetera, et cetera. Cash goes down. Okay? So those are the four rules of cash. So now coming back to the model, um, we're going to do the cashflow statement in the traditional way. We always start with net income. Because the net income, the first thing we're trying to do is reconcile net income to cash.

So take it out of accrual accounting and put it into, put it into cash accounting. And you know, these still kind of still kind of work according to those rules.

So net income makes the equity account go up.

So net income is a positive cashflow, of course, when it's positive. And here it is at 1793.1.

So that's that.

Now, what about depreciation and amortization? Well, we're adding that back here because it impacted our, our net income in a non-cash way. Now, we we're not showing DNA on the income statement here.

Most of your models will 'cause it's usually important that we break it out, to get to EBITDA, but here we're not doing that.

So where is DNA here? Well, it's probably buried in, it's buried in operating expenses, right? So buried in opex and that means that, we have to add it back. We know that it's there.

We just have to add it back. Now, why are we adding it back from a four rules of cash perspective? Well, if we go up to our base analysis, what happened to D&A. D&A is lowering our asset.

Asset goes down, cash goes up. So either way you look at it, depreciation is always going to be an added back item to the cashflow statement.

Now, change in working capital. Well, I don't have the working capital totaled as of yet.

I'm gonna go back up to my calcs and I'm just gonna go and pull those numbers in from the balance sheet and I'm gonna do that calculation very quickly.

So here are my two balances for year zero and year one are 2021 and 2022.

And my networking capital or my operating working capital here is the difference between the two.

So this is the assets, working assets minus the working liabilities. Now, assets minus liabilities, it's a net asset balance.

My net assets increased.

So that means assets increase, this implies that cash went down.

So if we think about that, you know, what is this saying again? Well, it's saying that our, that our inventory grew, that our accounts receivable grew, but we didn't collect on them quick as quickly.

We also didn't perhaps pay our bills as quickly. So what's happening here is that the cash is, because the assets are greater than the liabilities and growing, that represents an asset increase.

So if you do your working capital this way, assets minus liabilities, regardless as to whether this is negative or positive, as long as you subtract liabilities from the assets, then the proper way to do this formula is to do last year minus this year. And that will always, always, always, always give you the correct cash flow implication.

And again, we have to do that here because these accounts are impacting our net income. They're impacting our income statement.

We recorded these sales on the income statement, even though they're not cash.

We purchased this inventory with cash even though we didn't expense it on the income. We have to adjust for these things here. Okay, so there's my total operating cash flow, and now I'm going to switch down to my investing cash flow.

I only have one investing cash flow and that's gonna be the CapEx.

So I'm just gonna go and tick off these accounts here just to kind of be consistent. I got my operating assets. I've got my depreciation component here.

I got my OWC here and I've got my equity component, my net income component of my equity.

So my CapEx is going to be equal to the CapEx from the base calculation. But you must please, I beg of you, please remember to flip this to negative, just like the DNA, we had a flip to positive.

The PP&E based calculation is a mind, you know what? Because it's showing us the opposite of the cashflow.

It's correct for the asset account, but it's the opposite for cashflow.

So the CapEx makes cashflow go down and the depreciation makes cashflow go up. So we gotta remember to flip these.

I can't tell you how many models I see that have positive CapEx. Okay? So now I've got my pp and e done completely and I've got my, um, investing section done. Now I gotta go do dividends.

I also get from my calc dividends because we're on the right side of the balance sheet. That's already negative.

It's gonna stay negative and that's fine there.

Now last thing here, let me just x out my, is the, excuse me, the long-term debt, long-term debt. Again, this is just the long-term, not the revolver.

And the long-term debt is going to be equal to this year minus last year. So liabilities this year minus last year, assets last year, minus this year.

And that's going to give me that negative 50 that indicates that I repaid the long-term debt.

And so now my financing cash flow is complete.

I'm just gonna put in some totals here because I like them and my colleagues don't always like them. I like them.

And now I can total up the net cashflow. I can look and see how much do I expect to see cash change? Well, that's gonna be the sum of the 2,101.5, the 568.9 and the 1394.8.

And that's saying that my, that is saying that my cash flow from the year was positive 137.8 positive, 137.8. And just looking at this, it's saying that my operations kind of what Hershey's in business to do, brought in 2101.5, I invested 568.9 of that, that into the business.

So that leaves me with, call it 1500 and then I paid out, you know, most of that in terms of dividends and debt repayments. So that put, pulls me way down to a very small amount of kind of net cash flow.

So this cash flow here is going to impact what I already have in the bank.

This is telling me what I already have in the bank is going to change. Now you might be saying, well we still have those two accounts to deal with, right? We've got the revolver and we've got the revolver and we've got the cache itself.

Now this is I guess where I, in this type of model you know, which is sort of a basic three statement model.

This is kind of the biggest leap of faith that I will, I will ask of you now, if you remember again, when, when we did the cash flow statement, I just said let's isolate cash and solve for it.

If we have the change in every other account, then the total of those changes must total the change in cash.

That's just kind of again, basic algebra of it, right? So the, the problem is that in this particular model, we have two variables.

We have cash and then we have what I'm gonna kind of call the antica.

So the way we're going to use these accounts in this model is we're actually gonna make them siblings or, yeah, I'll just call 'em siblings in a sense or, or kind of opposites of those, maybe siblings still applies in that sense. But what we're basically saying here is that when I have a situation like I first described where I'm overfunded, I want to see, see the model full of cash.

When I have a situation where the model is underfunded, I want to see that I have a place, a way to fund that gap, fund those assets, and I will call on my revolver.

It's just like your own bank account. When you know you, you get paid, that's like your cash flow.

When your expenses are less than, than your cashflow, you've got cash at the end of the year when you overspend, you go into overdraft or you see it show up on your credit cards.

That's exactly what's happening here.

So in order for that to work, as we described it, and in order for us to still, in order for us to still have the the cash flow statement work, we need to net the existing cash from the existing revolver. I'm sorry, lemme say that backwards. We need to net the existing revolver from the existing cash.

We need to net the two.

We need to figure out what is my net cash position going into this.

Because just like your own bank accounts don't start fresh every month. You carry over what you had the year, the month before, the year before whatever it is, right? So if you had cash from the end of the previous month, you want that to carry in. If you had a credit card balance from the, from the month before, well you gotta deal with that too.

And now let me pose this.

What if you had a credit card balance from the year, from the month before and at the end of the current month you found out you had extra cash, you would pay down the credit card, right? You would pay down the credit card. And by the same token, what if you had extra cash and then you generated more cash? Well, you'd have even more.

So this account is gonna work together as one account.

And the way we're gonna do this is we're going to start the cash flow statement with the net of these two, the net of these two.

So my ending cash and equivalence net of the revolver is going to give me my net cash position coming in.

Might be positive, might be negative, doesn't really matter.

We need to net the two.

So in this case I had 329.3 of cash and I'm going to subtract the revolver of 942.2 and that gives me a negative cash position.

So if at the end of 2021 we said to Nestle, take your cash and pay down the revolver, Nestle could only pay down three. Say Nestle, sorry, Nestle used to be a case of ours, Hershey could only pay down 300 hundred 29.3.

So they're basically carrying still a ne a ne a negative net cash position into 2022 into the forecast here. So Kiri had a question here. So basically you're taking out the revolver from cash.

Because revolver doesn't repre represent true positive cashflow.

Well neither the cash nor the revolver is a great question.

So neither the cash nor the revolver at this point represent any cashflow.

They represent the starting balance again, what we're carrying in. So again Kiri your, you know, your cashflow in November is gonna be what you make on the job or however you're making, you know, if you're making, if you are earning, that's, but I still have to take what you bring in from October 31st into November to figure out kind of what's the ending balance. I can't ignore the starting point.

So we're netting the two to show that if Hershey paid down as much of this revolver as they could, they could only get to a negative balance of 612.9. They, they are negative kind of net negative cash, net negative cash. Now, is that a bad thing? Well, no it's not.

Because all these companies fund this way, right? All companies fund this way.

It's not, it's not a bad thing. And also don't forget, a balance sheet is a point in time. We don't know what bills Hershey, Hershey paid right before the books closed or what funding, you know, you know what, what funding happened or whatnot loans had to get repaid, et cetera, et cetera. It's a point in time, point in time. So, curious is that if, if that makes sense, great, if not let us know and maybe Yolanda can, can jump in and help.

So what I'm gonna do is I'm gonna take this 612.9, that's again what I'm carrying in from the last year and that's gonna bring me into 2022.

Now what I need to do is I need to take the existing 137.3 of cash cashflow that I generated and see what that does to this balance.

So I'm taking the 137.3 and I'm adding the 612.9 and that is getting me to an ending balance of 475 .1.

So let's just, again recap, I had a little bit of cash coming into the year.

I had 329.3, I had 942.2 of revolver.

I net the two and that shows me that I'm actually negative cash 612.9 I generated 137.8 of actual cashflow.

And so I can pay down a little bit more of that credit card balance from the year, from the year or the month before.

And that leaves me at the end of this first full year with an outstanding credit card balance of only 475.1.

Now the question of course is whether or not this is actually right.

And if we go back up to our balance sheet, you'll notice that we are out of balance by 475.1.

So this amount here is exactly matching the amount that our balance sheet was uneven by unequal.

And so now we just have to figure out how to put this into the model.

So again, I've got two places to plug so to speak. I've got two places to plug.

So it's like a little bit more complicated than if it were just a simple cache plug. I could go up here, put equals and go down to my balance.

Oh, and I did that wrong, my fault.

Go down to my cashflow statement and link to my cashflow statement and I'd have, I'd be showing that negative 475.1 It puts my model into balance. But it also looks kind of funny, doesn't it? Because we we're not showing negative cash on a balance sheet. You can't do that.

What this whole thing has been telling me all along is that I actually have a revolver balance.

I have a net revolver balance or a net negative cash balance, net revolver balance.

So what I need to do is I actually need to show that 475.1 in the revolver.

The problem is that if I do that as a negative, that doesn't solve my balance problem does it because it's, it's just kind of making it worse. It's doubling it.

And the reason why is because it wants this to be negative. It want does, it doesn't want this to be negative, it wants it to be positive because we don't show liabilities as negative on our balance sheet. We show them as positive.

So what we need to do here is we need to put in a formula that handles this and that formula is going to say, take a look at this ending cash revolver, take a look at that, and if it's positive, then it's cash, then it's telling me that I have cash, put it in the cash line. If it's negative, then put it in the revolver line, but put it in as a positive number so I'm describing it logically.

So you would think that an if statement is probably a good thing to use here and you could, but we can also do this a little bit more easily by using a max and a min statement and the max and the min, if we use the, use this F81 number here, this number here, 475.1, that's in F81. If we just compare it to zero, it'll basically be doing the work of a greater than equal, greater than or less than zero, right? So I'm gonna go up here into the cash and cash equivalence and I'm gonna say equals max open parentheses of the ending cash and equivalences in F81 comma zero.

So it's saying, show me the greater of what's in this cell or zero.

Well, lo and behold, since 475.1 is negative zero is the number zero wins the battle here.

And I'll put that formula here for you to see. And then lastly for the revolver, we're just going to kind of play around with that and use the kind of the min formula. Because now what we want is we want it to recognize the smaller number between x and zero and X is negative when it's a revolver, right? So I'm gonna do min of that 475.1 and zero and that's going to show me the negative number. Now, I already decided I don't want a negative number here though. I really, I still need it to be positive.

So I'm gonna just gonna go and flip my min to negative and just that just kind of, you know, tricks it into displaying as a positive number and now the model is in balance and everything is beautiful in the world.

Now the last thing that I have to do here is, um, the last thing that I have to do is well I need to copy.

Now you notice I haven't been copying, right? I'm, I like to show you the formulas.

I also wanna make sure if I balance and then I copy, I'm, I'm golden if I don't balance and I've copied, now I gotta keep copying over and over and over again. Now that's a battle I never win in the classroom.

I walk around the room and everybody's got the model entirely wrong and they've all copied because there's just joy in copying. I know it's, it's a great thing to do. But I beg you, I'II advise you to try to get one year right and then copy.

Now, I have to copy and I have to do the interest last, right? And then I'll be, you know, in kind of good shape here. So no, I haven't, I haven't actually calculated any interest here yet.

So what I need to do is calculate the interest on my outstanding balances and then I need to run that through the model. This is not, not something I would ever want to try to rush into 12 minutes, but I do, I'm going to so that you can at least hear me say it.

And then we have the videos again on Felix.

If you really want to go back and revisit the concept of a circular reference, circular argument, then this is kind of where it's coming in. Now, I, when I met you made, you saw me accidentally make a mistake and I linked one of my cells to the balance number and I got that message that said, oh, your model's circular.

Now I have that because I made sure that I turned my, sorry, there it's, I turned my there is, sorry, I'm lagging a bit here.

I turned my iterative calculations off.

So if you hit alt ft options alt F for file, options, go into the formulas, you'll see that my iterative calculation is off.

Now if I had honestly to do this list over again that I've, that I've been making for you, step number one would be turn iterations off, right? So that would be kind of step, you know, zero turn iterations off.

And what that guarantees you is you don't accidentally make a circular reference turn iterative calcs off like mine are. Now you may, maybe yours are not.

This takes a lot of this section takes a lot of explaining. There's a lot of, I'm just not gonna have time to do it. But, but you know, I do wanna show you what's happening here.

So the first thing I'm gonna do is I'm gonna keep these off for right now.

I'm gonna just calculate out my interest.

Now I'm gonna do the interest on the average balance.

Why am I doing the average balance? Well, I don't know when I paid this down, did I pay it at the beginning of the year? Did I pay it at the end of the year? I don't know. But if I choose one of the other, I could be overstating or understating the interest expense, right? So the, assuming that the loan or the debt was repaid middle of the year, I'm as, I'm gonna take the average of these beginning and ending balances to calculate interest. So the interest on the revolver, I'm gonna do the average of the revolver balance times the interest rate on the revolver, which is the 2%. And I'll put that formula there.

And now the interest on the long-term debt, same deal average of my two debt balances times the interest rate on the long-term debt. These are very, very, very old interest rates.

No more days of cheap debt are over and the interest on the cash will be the same thing. Now, we don't generally make a ton of interest on our cash, but you know, some times, and the reason why we don't make a ton of interest on the cash is the cash is usually at work, right? So we might be getting a little bit on the stuff that's in the bank, but a lot of our cash is at work.

So my net interest expense is going to be the sum of the revolver and the long-term debt minus the cash and there's my net interest expense, okay? Now, if we think about what's happening, if I were to go ahead and, and bring this into the model, the model would get very angry at me and it would say, Hey, you have, you have a circular reference, right? So if I were to just go ahead and pipe this in right now, it would tell me that we have a circular reference.

Now why is that? Well, if you think about what's happening here, just kind of go over to my pad really quickly.

If you think about what's happening here with the circular reference, you know, we have just done a, a surgeon's job of getting this to balance, right? We have a balance sheet, right? That has a cache and a revolver plug, right? These are our plugs and it's got also an equity section and that equity section is being fed from our income statement, right? And then that net income is then feeding our cash flow statement, right? That's what we start with net income, that gives me my ending cash and revolver.

And then the ending cache and the revolver are, are plugging here, right? So we just did this and that, that plugs beautifully and I gotta balance 475.1. I even remember it, it's so monumental in my life to balance a model.

I remember the plug 475.1.

Now the minute I say aha, but that revolver balance of 475.1 is also generating interest. My good man, you have to put that in there.

Okay? Well now I go and I take that interest expense, which I calculated over here in that other section, my interest expense and I add it to my income statement.

Now what happens? Well, my net income changes, I now have net income version two and my net income version two is obviously going to impact my cashflow statement.

That's gonna change my ending cash slash revolver balance.

I no longer have 475.1, I have another number.

So I've got a new cash revolver version two. And so now that has to plug here as well. But then you say, well wait a minute, if I have a new cash slash revolver balance, now I have a different interest income and expense number again, now I gotta do this all over again.

So I I now have to do another interest calc that one was wrong.

I'm gonna do another one that's gonna feed here that's gonna change this, that's gonna change this, that's gonna change this, that's gonna change this.

And this just goes and goes and goes.

And that is the circular reference. Now Excel can handle this and it can handle this because it's got enough power and it's, you know, calculations to run this many, many times until there's not much of a change. Now, you know, won't it always be changing? Well actually no, because if we think about it really quickly, right? If I have a 10% loan, if I have a 10% loan at a thousand bucks, right? If so, if this was the plug, if the 1000 were the plug, right? Not the 475.1, I could do the 475.1. Actually, why don't I just do that? And let's just say the interest rate's 10% just so you can see the math clearly.

Well now I gotta run this interest into the model.

So now I'm generating 47.5 of interest.

I know there's a tax thing too, but let's just ignore that for a minute.

Now the next time I run it through, there's an additional 4.8 of interest on the interest and then there's an additional 0.48 and then there's an additional 0.048 and we just, um, keep going with this all the way down till all of a sudden, you know, you keep, you get to a certain point where you're not even at, not even at a penny any longer. You're not at, you're not, you're like beyond this 0.00048 is not materially impacting the model.

And so that's when Excel basically says, Hey, we're cool.

So when I put this into the model as I have, what I need to do at this point is give Excel the ability to, to run that little side calculation.

And so if I go into those settings again and I turn this on, you'll see that Excel says, look, I'll run this a hundred times or until I get that 0.001, that was one less than we did, we did three, we had three zeros. So Excel doesn't have to work that hard to do this, but it can do this. And once it does it, you see that what happens is we look in the lower left hand corner and that, becomes calculate.

It tells us that Excel is doing math behind the scenes and the model is now, you know, kind of perfect and a lot more accurate. So at this point I, you know, I could copy over with confidence and I would be fine.

Now there's a bunch of stuff that happens when you create circular references.

Excel gets a little bit, you know, kind of, you know, unstable.

So we like to just, when we add a circular reference, we like to link it to what we call a toggle so that we could turn it on or off.

So what I'm gonna do in here is you'll notice on the cover page I've got this circular switch. I prefer this to be on the income statement, but I'm not gonna change the world with a minute and 12 seconds left.

So I'm just gonna say, um, if I go over there, you'll notice that that cell is named, it's called Switch.

And so what I'm gonna do in this moment is I'm gonna say equals if switch equals one, if it's on show me F87.

If it's off, show me a zero except I spelled switch wrong.

And now because the switch is off, it's zeroing out the interest.

And then if I go back to the switch and turn it on, it runs the interest through just a very simple thing.

It has to be an if statement, don't do a multiplier, has to be an if.

Um, and now at this point, again, you know, we can copy the model over.

I'm at the top of the hour, so I'm not going to, I'm gonna post the solution for you and then I'll post my version as is. So you can see it kind of, you know, as I did it and I'll post the notes as well. I'll finish up, that little list for you so you have that. And you know, we basically did, you know, a three hour model and two and hopefully you're able to follow along. If not, please feel free to, you know, ping us with any questions after the fact.

There'll be practice models for you to, to try.

You're welcome ki I appreciate the, the kindness.

I'll put some practice models. You can try model two and three. There'll be a Felix challenge if you wanna try that as well.

And we'll get back added again next Wednesday.

Same time I think we're moving on to valuation.

So that should be fun. So, I thank you again for your time and look for those files to be uploaded by the end of the evening. And I'll see you here next week.

Thank you Yolanda for your help as well.

Thanks everyone. Goodnight.