Accounting 3-Statement Model
Okay, hello and welcome to our interview question three-statement model. This is the first of the Excel models that is included with our investment banking interview guide, provided on the Breaking Into Wall Street website.
Now, the purpose of this excel model is to help you answer interview questions where they ask you, for example, what happens in the three statements if revenue goes up by $100? Or what happens if accounts receivable goes up by $100, or down by $100 or something like that is happening?
The way this model works, if you go to the Interview Questions tab right here. You can see our assumptions at the top, the tax rate 40% for the company to make round numbers here, the initial cash balance. We see our output here, so after we make these changes, I have a summary at the top of how some of these key items like cash, shareholder’s equity, net income, assets, liabilities, equity, and so on and so forth actually change.
Then, here is where you can enter all the different assumptions and all the different changes. So, we have the income statement changes here, then the balance sheet line item changes, all the way down to deferred revenue. Then we have the cash flow statement line items right here, and for these, most of these relate to debt or equity or investments, or capital expenditures, something like that.
These items have to go on the cash flow statement, and then in turn will float into the balance sheet. So, that’s why I’ve separated them out here like this. Now, on the statements themselves, basically what we’re doing is for the income statement and the cash flow statement, since both of these only cover a period – one specific period, so a quarter or a year, or a month, or something like that – we track the changes over that period.
So, what I have here for the current period is before any of these changes, so before you enter any of these assumptions up here, this is what it looks like. Then after the changes, so after you have entered some kind of change here, this is what will show up afterward.
Now, on the balance sheet, it’s slightly different, because there we always have to show two snapshots from the very beginning of the period that we’re tracking, and then from the end of that period. So, here the previous period corresponds to what happened just before this current period begins, and then the current period here really means the end of this current period that we’re tracking on the income statement and cash flow statement.
The previous period, basically all of these are hard-coded numbers. We’re not going to be changing any of those. But the before changes and after changes, here, show you basically, once again, what happens after we change around some of these numbers and change our assumptions here at the top.
A few notes before I delve in and show you some examples here, first off, if you go to the instructions page, make sure you read all of these very closely. I’m not going to repeat all of them here because you can read them yourself and I don’t want to waste time in this video, going over these again. But, basically the high level points, this model can definitely break under the right conditions, so don’t go around and try to enter something like 5 million or 0.005%, or other types of nonsensical input for these.
I’ve done some error checking here, and generally speaking, these will hold up pretty well to reasonable assumptions, but as always, you can always break a model if you’re creative enough. So, try to avoid doing that. It’s not really going to be helpful, and it’s just going to waste time when you’re preparing for your interviews.
I also have another note here saying that many of these items on the income statement and cash flow statement are zero initially for pretty much the reasons that I state here. That we have now taught this material for many, many years, starting from the very initial version of the Breaking Into Wall Street site up to what we have now.
What we have found is that these changes are much easier to track in your mind if all of these numbers, or at least most of these numbers, whatever is possible, is set to zero initially. In other words, if we go in and change depreciation – let’s say I go up and change this to changes by $100, increases by $100, really. It is much easier to track this and compare it to the zero case than if we had some other number here like $50 or $73 or $22. So, that’s why I’m doing it.
It’s not realistic of course. You’re always going to have these types of items, depreciation, stock-based comp, amortization, but just in the interest of simplicity, I have set them all to zero. I’ve taken the same approach with many of these cash flow statement items as well. I’ve set them to zero, not because it’s accurate, but just because it makes it a little bit easier to actually analyze the statements and see what’s going on.
You can see some specific restrictions here for the income statement, balance sheet, and cash flow statement line items, and I’m going to go over these in a bit more detail. But you can read through the rules and regulations if you want to think about it like that on your own.
One other thing to note, here, is that many of these changes, in fact almost all of these changes, are modeled as increases. Then for the balance sheet, we have increases or decreases, and then cash flow statement, we are back to increases once again.
Now, the reason that I’ve set it up like that is not because you should not be thinking about negatives here, or anything like that going on, but because most of the time in interviews, they’re not going to ask you about a decrease to these items. They’re usually going to focus on increases. So, they’ll say, “What happens if revenue goes up by $1,000?” Or another very common one, as we have right here, “What happens if depreciation goes up by $100?” or, “What happens if the company’s interest income increases by a certain amount?”
So, that’s why I have left it as that. If you want to, you can go in and change this around. But do be careful, because with some of these items, especially the ones on the balance sheet, an increase actually works differently from a decrease. So, you do have to be careful of that.
Now to show you an example of what I mean, for accounts receivable, let’s say that we enter an increase of $100. Well, an increase of $100 to accounts receivable means that our revenue goes up by $100, as you can see right here. We’re using conditional formatting to format any cells that have actually changed after these changes. So, any change cells are highlighted in pink, here.
But you can see how everything changes as a result. Gross profit goes up, operating income here goes up, and then of course our pretax income and net income here also go up. Then, on the balance sheet, our cash goes down as a result of paying taxes. Accounts receivable goes up because we haven’t actually collected this in cash yet, and then you can see how everything else changes below.
Whereas, if accounts receivable goes down, what happens there? Well, this corresponds to a cash collection of those accounts receivable, of those invoices that were issued to customers and haven’t actually been paid yet. So, when it goes down by $100, well, nothing on the income statement changes because accounts receivable decreasing refers to a collection of cash.
So, there are no changes on the income statement, but you can see that on the balance sheet now, our cash number is different. It’s higher by $100 because now we’ve collected that cash. Our net income is different, but nothing below that has really changed all that much. Then, of course, retained earnings and shareholder’s equity have changed to balance everything.
Our balance sheet remains balanced, but you can see basically how increasing and decreasing many of these items is going to have a very, very different impact because they correspond to different things. Now, that cash versus non-cash treatment is pretty much what applies to accounts receivable, prepaid expenses, accrued expenses, accounts payable, deferred revenue.
Inventory is a little bit different. Here we’re assuming that a decrease in inventory represents a direct increase in costs of goods sold. Is that what actually happens in the real world? No, not necessarily, but this is just a simplified way of looking at it, because this is typically the assumption with interview-style questions like this.
So, that is a quick overview of this model, how you can use it, and some rules and regulations to be careful of as you go through. Now, if you want, you can go in and enter multi-step scenarios here. So, you can go in and enter $100 for certain items, and then $50 for other items, and then $100 for other items, and then you can have revenue increasing by $200 for example.
You can actually go through and do this for everything, and you can see how all these cells will change. As a result, the model works and is perfectly flexible. However, I would not necessarily recommend doing this, especially if you’re new to this and this model seems intimidating at first. It is usually better to stick to one or two changes at first, and make sure you understand those.
Multi-step scenarios in interviews are just not particularly common, so you shouldn’t spend too much time and you shouldn’t exert too much of your own effort on preparing for those types of questions. Focus on the single-step, sometimes double-step scenarios. If you have more advanced accounting knowledge, then you can try entering multiple different changes, as I just did here, and see if you understand what works and how it flows through correctly.
Another note here is to be really careful of reading these comments. I have entered some additional information in these that you should definitely review before you start playing around with this model yourself. For other certain items here, such as deferred income taxes, you have to be really careful with how this works.
With deferred income taxes especially, all income taxes that a company pays are split into the current portion and then the deferred portion. The current portion is what they pay in cash in the current period. Then deferred is what they owe in a current period, but which is deferred into later periods in terms of actual cash payment.
The reason why you have to be careful is because if you go in and say something like, “Increase it by $500,” this model is not going to make sense because now the current portion of your income taxes is negative. So, this deferred portion always has to be less than or equal to the total income tax provision up here.
Unfortunately, there’s no way to error check for that specifically in Excel without doing more complicated things here, so I’ve left that in as is. But just be aware that this should never exceed the total income tax provision. So, if I enter $350 there, you can see how it works, that basically, our cash balance here just goes up by $350 because we’re deferring some of these taxes to a future period.
So, as I said, feel free to play around with this. You should definitely go in. Change around some of the numbers. Start out with single-step scenario changes. Make sure you understand those perfectly first, before moving on to more complex scenarios. Then you can also take a look at all the questions in the interview guide, and then the questions in the interactive quizzes as well.
Try entering some of those. See what works and how everything flows through correctly, and make sure that you understand all the changes. Once again, as you go through and change those, the fact that we used conditional formatting to highlight cells differently will help you a lot with understanding what has changed and how these various items link together.
That’s it for our overview and explanation of this video. I hope you have found everything helpful. Once you’re done mastering the concepts here, you can start moving on to the other models that we provide in the interview guide, including the equity value, the enterprise value one, the valuation model, the merger model, and LBO model after this.