How to Link the 3 Financial Statements in Excel

Hi everyone, welcome to the webinar on how to link the three financial statements. We're just waiting for everyone to dial in. We've got quite a few people logging on to this session, so we'll just give everyone a minute to get logged on and then we'll get going. If you have any questions throughout the webinar, you can use the tool for Q&A to ask questions, and some of them I may answer as we go, and the rest I may leave till the end, just sort of depending on the nature of the questions. But it looks like just about everyone is logged in now, so I'm going to get going. Thanks everyone for joining us on how to link the three financial statements in Excel. This is brought to you by the Corporate Finance Institute, and our mission is to advance your career. So everything we do is extremely practical and extremely skills-based, so that we can teach you how to be a world-class financial analyst. The objectives of this session are a few things. First of all, let's just talk about why we would link the three financial statements together. What's the purpose of doing this? And then what's the best layout to do that? There are a few different ways to structure your model, and I'll talk about the pros and cons of different approaches to laying out your model. Then we'll break down the actual approach to linking the three statements. It's a bit of an iterative approach, as there are steps that you can only do after you've completed other steps, so you sort of have to go back in this circular fashion. And then I'm going to do a live demonstration of linking these statements in Excel. So this whole session will take about 45 minutes, with at least about half an hour of Excel work. Just quickly, before we get started, we assume that you have a basic understanding of accounting in taking this webinar. If not, we do offer a free accounting fundamentals course on our website. We also assume that you have a basic understanding of reading financial statements and interpreting them. And again, if not, we've got a free course on our site that will teach you just that. And finally, Excel skills. We will be using some Excel shortcuts, formulas, and functions. So if any of these are new to you, or you want a refresher on them, please just take our free Excel crash course. It's jam-packed with all sorts of tips, tricks, shortcuts, formulas, and functions. All right, so let's talk about why we would link the three financial statements together. It's really the building block of all other financial analysis. Before you can do anything else, you need the income statement, the balance sheet, and the cash flow statement all linked together and dynamically flowing. As soon as that's in place, you can start to do financial analysis. You can test scenarios, you can look at ratios, you can look at profitability metrics, all sorts of things. And then above your financial analysis, you could look at layering on a discounted cash flow model where you'd be valuing the company. You could get even more advanced by looking at an M&A model where you've got two companies that are going to merge, and you would look at pro forma metrics like accretion and dilution. And then you can get into scenario analysis and sensitivity analysis. So really, this is just to say that the three statement model is the foundation. That's why we're doing this webinar today for free, is we want to make sure everyone has a solid understanding of how to link the statements. And from there, you can progress to more advanced types of financial models. So there are two standard layouts when it comes to a three statement model. The first one is based on multiple tabs. So you'll see on the here, we've got different sheets for the assumptions, the income statement, the balance sheet, the cash flow, and supporting schedules. This is a very traditional way of laying out a financial model. It's common practice to do it in this fashion. But the problem with doing it in this way is that one thing is linking can be harder because you have to go between worksheets to link your formulas. And the other thing is, if the model becomes very large, and it's a very complicated business, there may be too many worksheets and it may get too complicated. So our preferred method is to use a single tab model. And you'll notice in this screenshot here, we just have one worksheet in the file. And instead of organizing everything horizontally, like on the left example, we've organized everything vertically. So you have your assumptions at the top, then your income statement, balance sheet, cash flow, and supporting schedules. This would make an M&A model much easier, because you could have each company on its own worksheet. It would also make it a lot easier if it's a business that has multiple units, and you wanted to model each of those units independently, or each of the countries that it operated in independently. So we highly recommend the single tab model. And that's method we're going to be demonstrating in this webinar today. Now let's take a look at the actual approach. There is an iterative approach to linking the statements. You can't simply build them from top to bottom. You start with revenue and work your way down to EBIT on the income statement. However, you're going to run into a problem with depreciation and interest, as you won't have the assumptions in place that you need to know what those expenses are. So at that point, you move down to the balance sheet. On the balance sheet, you can do AP, inventory, and AR. Then you're going to get stuck with PP&E, so you go to a separate schedule where you model PP&E on its own. And then you can get into the financing and capital structure, where we make assumptions about debt and equity. When the PP&E schedule and the capital structure schedules are finished, then you can go back to the income statement and plug in depreciation and interest and finish off the income statement. Then you can go to the balance sheet and finish that off and finally do the cash flow statement. So we're going to be working through little sections of each part of the three financial statements, and then finally putting it all together at the end. With that, I'm going to flip over to Excel. And I trust everyone has received this file in the email that we sent out. If not, please go to your email and check. You should have received a copy of this file. I'm going to quickly walk you through the layout. So we've got this single tab model. As you see, there's only one tab at the bottom here. It's organized by assumptions, income statement, balance sheet, cash flow, and supporting schedules. So in the assumptions area, we've got these numbers here that are going to drive the forecast of the model. And they're based on looking at what the business did historically in this section here. So we've got the historical section, and then to the right of it, the forecast section. We can then open up the income statement. These are the historical numbers for this company. We simply took their financial statements and typed them into Excel. We can then see their balance sheet below that. Again, these numbers are blue because that indicates that they're hard-coded numbers. They're not formulas. Finally, this company's cash flow statement is published here. And then below that, what's not an actual statement but just some working information is these supporting schedules. So there's a working capital schedule, a fixed asset schedule or depreciation schedule, and then debt and interest schedule. So what we're going to do now is we're going to go through and build out each of these sections one by one to fill in the forecast of this model. So we'll start with the income statement. We'll do this top section. Then we'll get down into the balance sheet and we'll focus on these working capital items. Then we'll move all the way down to these supporting schedules and fill those in. Back up to the income statement to do interest and depreciation, taxes. Then back to the balance sheet and you get the idea. So this is that iterative approach that we spoke about earlier where we can only do things one at a time. So let's start with revenue. If you have this open, please work along with me. Otherwise, you can just watch my screen. But we're going to calculate revenue based on a year-over-year growth rate. So it's going to be equal to prior year revenue times one plus some growth rate. Cost of goods sold is going to be a function of revenue and a cost of goods sold margin. And then gross profit is going to be equal to revenue minus cost of goods sold. So with those in place, I can fill them right with control R. If you want a review of these Excel shortcuts, please take our Excel crash course. It's free and it will teach you how to quickly fill things in just the way I am. So that was fairly straightforward. We've got a growth rate assumption for revenue and a margin assumption for costs. Then we get into the expenses. So we've said that salaries and benefits is a fixed cost. It's not a percentage of revenue. So we simply link to an assumption that has a dollar value. Same with rent and overhead. We've said rent and overhead is going to be constant over time. It's not going to grow with revenue. So we put those in place and we can fill them right with control R. But then we get down to depreciation. And you'll notice in the assumptions that depreciation is based on a percentage of property, plant, and equipment. Or it could be a more complicated form of depreciation, like straight line. But we've said, okay, it's a declining balance. Whatever type of depreciation assumption you want, it's going to be a function of property, plant, and equipment. So we're kind of stuck. And then interest here, we also can't complete because interest is dependent on the debt balance. And we don't have anything on our balance sheet yet. Taxes we also can't do because we don't know what the earnings before tax are. So we're kind of stuck here. So now what we do is we go down to the balance sheet and we say, okay, we can model accounts receivable, inventory, and accounts payable because we have revenue and cost of goods sold already in place. So accounts receivable is simply the percentage of revenue that's left unpaid at the end of the period. So our accounts receivable is going to be based on revenue multiplied by the assumption we have for the number of days receivable are outstanding divided by 365 to convert it to dollar value. So that would be our accounts receivable at the end of the period. We can also calculate our inventory because we have an assumption about inventory turns. And inventory is a function of cost of goods sold multiplied by the average days that it takes for our inventory to turn over, which in this case we're saying is 73 days divided by 365. So we multiply the cost of goods sold by the average inventory days and then divided by 365 to get this dollar value of inventory. So I can fill those right. So account, just to review this because it's not the most intuitive, accounts receivable is a function of revenue because we've recognized revenue for this business but we have not necessarily been paid for all that revenue. And if we have 18 days on average that it takes to receive our revenue as actual payment, then we can multiply revenue by 18 divided by 365. So that's the proportion of the year that's unpaid at the end. For inventory, we do the same thing. We take cost of goods sold and multiply it by the days that it takes to turn inventory divided by 365. Now we can move down and do accounts payable as well. So just like accounts receivable, accounts payable is going to be based on a days assumption, a number of days, but it's going to be applied to expenses, not to revenues. So the expenses that we will assume that we have payable terms on are cost of goods sold. There could be some other items on the income statement that have payable terms, but for simplicity's sake, we're just going to assume that cost of goods sold is the only thing where we have payment terms. And we'll say 37 days is what we take to pay our bills. So we'll multiply it by 37 and divide it by 365. So that's the percentage of cost of goods sold that are unpaid for at the end of the year. So I can fill that right. So at this point now, we've got our working capital completed with our accounts receivable, our inventory, and our accounts payable. We've kind of chipped away at what we can do on the balance sheet. And once again, we're kind of stuck though because we don't know what property, plant, and equipment is going to be. We don't have a debt number, and we don't have what we need to finish this section either. So we're kind of stuck. So this is where we now continue to move on. We don't get discouraged even though we don't have those numbers. We just move on down and we'll build some schedules at the bottom here. These are very simplified versions of the schedules you would build for an operating company. Typically there would be a lot more detail here, but what we can do is first let's do our property, plant, and equipment schedule. So what we have here is an opening balance, which is the same as last period's closing balance. We add to it by spending capital on PP&E, either buying additional equipment or investing in the existing. And then we deduct from it the depreciation. So let's fill in this schedule. We call this a corkscrew calculation because it pulls the numbers through like a corkscrew shape. So the opening balance is equal to last period's closing balance. The additions are up in the assumption section, where we are adding this number here in the form of capital expenditures to our capital asset base. And then we're saying the depreciation, and this is just a very basic assumption, we're saying that depreciation is a function of the opening balance times our depreciation rate, which we've said is 40 percent. Now in reality you may have a more detailed schedule than this, there may be a more complex calculation, but for illustrative purposes this will show you how to connect the statement. So depreciation is 40 percent of the opening balance. So then we get our closing balance, which is opening, plus our capital expenditures, and then minus our depreciation. The closing balance is what we need to put on the balance sheet. So once we have that filled, we can select all this area and press control R to fill it right. Let's also build out the debt and interest schedule. So it's a corkscrew calculation again, just like the depreciation schedule. So our opening balance is equal to last period's closing balance, there's no time difference between these two, and then we have an assumption about any debt that was either repaid or additional debt that was borrowed. So this assumption here is any principal repayments or additions to our debt balance. The closing balance is simply the opening plus any issuance or repayment, and then we calculate our interest expense. Again, a very simple assumption, that is the opening balance times the interest rate. Now there's all sorts of additional complexity you could add here. Of course, the opening balance is not a true reflection of what the total balance was throughout the entire year. For simplicity's sake and to complete this model, this is a great way to do it. We avoid circular references or any other issues that more complicated schedules create. So now we've got this debt schedule where we have our closing balance of debt. We've got our depreciation schedule where we've got a closing property plant and equipment value. And that allows us to go up and complete part more of the income statement and balance sheet because we were missing these items earlier. But before doing that, let's quickly fill in this working capital schedule as well. This will help us on the cash flow statement because we need to take the changes in working capital to adjust our net income to get operating cash flow. So just as a quick recap, if accounts receivable goes up, that indicates we have not been paid for more revenue that we've recorded. So if accounts receivable goes up, that's going to be a drain on our cash flow. Same with inventory. If inventory goes up, presumably we paid for that inventory. So that's also going to be a reduction of cash. Accounts payable works in the opposite direction. With accounts payable, if accounts payable goes up, that means we have not paid bills. So that's a source of cash. If our accounts payable went down, we would lower our cash because we cleared out all those bills that we owed. So that's the quick recap on changes in working capital. So all we need to do here is just link to the balance sheet where we've already calculated accounts receivable. Inventory is right below that so I can copy the formula down. And then accounts payable, I can link to the balance sheet right there. So to calculate the networking capital, I'm just going to copy this formula over. I take accounts receivable plus inventory minus accounts payable. I get my total networking capital. But what I need to know for cash flow calculations is the change in networking capital, not just the total balance. And that's what this line below is calculating. It's saying what has been the change in networking capital over this period. And that impacts my cash flow. Because as you will see here, AR has gone up, so that's a bit of a drain on cash. Inventory has gone up, also a drain. But accounts payable has gone up, which is an offset. So the net effect is actually very small. There's just a very small increase in working capital. So with this in place, I can press control R to fill that right. So our supporting schedules are done here. Now we can go back up to the income statement. And you'll remember where we got stuck here with depreciation and interest and taxes. Well, now we can fill those in because we have depreciation down in this schedule. So I can go to my depreciation assumption here, J94, and press enter. I can also pull forward the interest expense down in this debt schedule. So here's my interest expense in J101. And then I can take these two rows and selecting the area and pressing control R, I fill it right. These formulas down here, total expenses and earnings before tax, these can actually just be copied over since we had them in those other cells. So we're simply copying over the total for expenses and then taking the difference between gross profit and expenses here to calculate earnings before tax. With earnings before tax in place, I can finally calculate our tax expense, which is EBT times the tax rate assumption of 28%. Again, just a disclaimer, this is a very simplified approach to calculating taxes. There are all sorts of nuances with current taxes and future taxes, et cetera. But for now, the exercise is to complete three financial statements. So we're going to keep all of our assumptions very simple. And we can now complete this income statement here with a very simplified approach to taxes. That gets us to net income. So that's great news. We've got the income statement done. But if I go back down here, still quite a few holes on the balance sheet. And of course, the cash flow statement is completely empty. But let's chip away here. We've built this property plant and equipment schedule down below. So we can now fill in the PP&E line on the balance sheet. So let's go down to our PP&E schedule. And we can select the closing balance for PP&E right here. And I'm going to fill that to the right. With that in place, I'd also like to take this subtotal here, total assets, and fill it to the right. So now this is all linked up. Cash is going to be the very last thing we do. This will be our final piece that tells us if the model's working or not. So we're going to save that. But scrolling down here, we know that we've got the debt schedule now. So I can fill in debt. So I'm going to go down to the debt closing balance, J100, and just link that in. I can then also copy across this subtotal. So I'm filling all these right with control R. So now what I've got here is liabilities completed because of our debt schedule and our accounts payable that we calculated earlier. But then we've got equity capital. So how do we pull this forward? Well, with equity capital, it's going to be sort of like a corkscrew where you've got like a prior period and then a change. So our equity capital is going to be whatever it was in the last period plus the assumption that we've made at the top here about any equity that's issued or repurchased. So you can see here that in most cases it's zero, but this company did raise some money in 2012. And then we've made an assumption that they're going to buy back some equity in 2020. So what I can do is link that up and I can fill it right with control R. And you'll see that it goes down in 2020 as the company repurchased and retired shares. Okay. Retained earnings works in more or less the same way. It's equal to the prior period plus this period's net income. Now, if this company paid any dividends, which it does not, and there's no assumption for it, then those would be deducted from retained earnings. But we don't have that assumption in this case. So now I can fill that right. And then finally, I can calculate these subtotals here. So when I do that, I'm going to copy across this check. This check is in place to see if the balance sheet is balancing or not. I'm going to fill it across. And of course we get this error message across the top here that is not balancing, but that's perfectly fine. We know it's not going to balance. We haven't put in the cash number yet. And our cash number is not going to be a plug. We're not just going to make this equal to the number down here so that we can make everything balance. We're actually going to have to build out the entire cash flow statement to get our closing cash balance number. That will let us know that the model's working properly. So let's move down to the cash flow statement section here. The cash flow statement is mostly just a linking exercise. We've basically got everything that we need up above. So the first thing we need to do is link our net income or net earnings. That's the starting point for operating cash flow. So we put that in place. We then need to add back any non-cash items. As you know, earnings is not a cash metric, and expenses like depreciation and amortization, stock-based compensation, unrealized gains or losses, they're all sorts of non-cash items. But depreciation and amortization is typically the main one. So I'm going to link to depreciation and amortization, which is the only non-cash expense we have in this simple example. But this is still not quite our cash flow yet because we need to adjust for changes in working capital. And we built a schedule below so that it's easy to simply take the total change in non-cash working capital, which in this case is 375. So as you recall, 375, really what that represents is the net change from accounts receivable, inventory and payable that we need to adjust for because we didn't receive all of our revenue. We bought some inventory and we didn't pay all of our bills. So we've got this small amount of adjustment here. Once that's in place, I can copy this subtotal across and I'm going to fill the whole thing right with control R. So just to make sure it's crystal clear, the reason that we deduct this increase in working capital is that if net working capital has gone up, that means AR and inventory have gone up more than accounts payable. That's a reduction of cash for us because AR means we haven't been paid, whereas AP means we haven't paid our bills. So it's a reduction of cash. Then we can move on to the investing cash flow. In this case, it's a fairly simple assumption that our only form of investing is in property, plant and equipment. That's found on this line here, J93, where we have our capital expenditures. So then the total cash that's spent on investing, this is a little bit redundant, but this total line is just equal to whatever we spent on property, plant and equipment. This section could have a lot more detail to it depending on the business. There could be acquisitions of other companies in here, there could be dispositions of assets, there could be all sorts of things, but this is a nice simple example. Okay, then we have to look at the issuance and repayment of debt. So the easiest way to do this is to go to the balance sheet and take the difference in the current period debt and last period debt. Whatever that difference is, it's going to be any increase or repayment of debt. And with equity, it will be the same thing. We'll take our equity capital in this period and subtract our equity capital in the prior period. So that gets us our change in equity. And then we can copy across the subtotal here. So we've got the change in debt, change in equity. If there were dividends paid, the dividends would flow through here. So this company does not have any dividends nor an assumption about dividends. So you'll see what happens here is, when debt goes down on the balance sheet, it gets picked up on the cash flow statement. When equity gets repurchased on the balance sheet, it gets picked up on the cash flow statement here. So with all this in place, what we can now do is copy across this formula. This formula is just simply taking the net of these three. And you'll be careful to watch your signs here. So cash from operations we have as an addition to our cash balance. Cash that's invested is an outflow of cash. So we subtract that. And then cash from financing is a source of cash. You could have it so that these numbers here are expressed as negatives. And then you would have to adjust your sign down here and sum them. So it doesn't matter which way you do it. I've seen both with many companies. But just be careful which sign you're using. So then we have our opening balance that gets pulled forward from last year's closing balance of cash. And then this year's closing balance is simply the opening balance, right? The opening balance that came from last period plus the net increase in cash from this period. And I can copy across this check as well. So with all these formulas in place, I can now go down here and press Control R to fill these, right? And let's just take a quick scroll through the model. Now, we still have this error message. That's totally fine. That's totally normal. So let's just review what we've done. We've filled in the income statement. We've built all the balance sheet except the cash. We filled in the cash flow statement. And all of the above statements were supported by these schedules here. So we had our depreciation and fixed asset schedule, our debt and interest schedule. And now we sort of have the moment of truth where what we do is we take the cash number here and we link it to down on the cash flow statement, the closing cash balance. This is where you cross your fingers and you hope it all worked and press Enter. And yay, then you celebrate. Okay, it works. So now you can press Control R to fill all your cash across. And you get super happy because everything balances. It's all live linked. It all works. And best of all, there's no plugs. There's no like forced solution in here. If any of the formulas were off, it wouldn't balance. It wouldn't work. So this is great news. We've built this together here. Hopefully you've been working along with the example that I sent out. But if not, I will email this out to everyone as well so that you have the solution. And what we can do now is just open it up for questions. If people want to type in questions using the Q&A function, I'm happy to field those questions right now. Or you can always email later and I'm happy to take questions offline. So let's just pause a minute here while people email in some questions. Okay, so there's a couple coming in. I'm just going to answer them one at a time. So one question is, what if I wanted to do this on a monthly basis? Okay, that's a great question. And we have some monthly cash flow modeling courses as well. And it's, you know, it's very common to do monthly cash flow modeling. So really, the only differences would be, you know, first, of course, in the setup, what you instead of years here, obviously, you would have months, I guess that's pretty obvious. But otherwise, it would be the same, except for certain things like accounts receivable inventory, and payable days assumptions. So you would have to actually change these formulas here, where in this one in accounts payable, you see how it's, it's taking, or let me use accounts receivable, actually, it's a bit easier. So it's taking a full year of revenue. And it's taking a full year of revenue, multiplying by 18 days and dividing by 365. In your case, in a monthly model, you would only have one month of revenue. So you wouldn't multiply it by 18. And divide by 365, what you would have to do is adjust that formula, because it's only based on 30 days or whatever, to divide by the appropriate amount, which would, which would be 30. So, so that would be an adjustment that you'd have to do with accounts receivable inventory, and accounts payable. But otherwise, you more or less build the same on a monthly basis. So maybe that's a good idea, though, we could do like a monthly model on on next slide demonstration, because we do get a lot of questions. Okay, there's a few more here. Okay, so so here's one about M&A. And the question is, do you suggest that the company do it in the same tab or create a new tab for the other company? This, this is exactly why I like to do the three statement model on one tab. Because then what I would do is, let's say this is company A, what I would do is I would essentially copy this, copy this tab, move it over, I just have to click yes a few times here. Okay, and then this would be like, let's call this target code. So I would have, you know, our company here, target code here, of course, all these numbers would change these, these would be totally different numbers for the target. And then what you would do is have yet a third tab, where you would you would take the two companies and add them together. So you could actually copy this one again, create a third copy, so then then they're identical. And that makes it very easy to consolidate them. So so this is the preferred method of doing it. If the businesses are totally different and unrelatable, you won't, you won't be able to have the same format. But a lot of times you can actually put them into the same format. And it's very easy, then to do an M&A model. Okay, I'm just reading more questions here. Yes, we'll share the video after the session. That was that was another question. Yeah, and then so so there's been a few questions about time period. So a couple of people have asked about monthly someone's actually asked now about quarterly. These are all great questions. I'm just thinking out loud here, the best thing might actually be to provide a small example in the follow up monthly and quarterly. So you can just see how it would be modified. It would take far too long to go into any more detail on this call. But what I will do is send something out to give you some advice on how to do this monthly or quarterly. Okay, here's here's a question about, I guess, auditing the model. The question is, what are some of the biggest discrepancies that lead to to forecast misses? So I guess I can think of a couple different things of what that question might be asking. But one thing is, if by discrepancy, you mean the model is not working, the model is not balancing. It generally happens from how you calculate changes in accounts on a complex balance sheet. So this is a very simple balance sheet. You know, the current assets are just are just here and then the current liabilities are just here. So changes in non cash working capital, easy to calculate. And then for non current items. Again, it's pretty simple here. But what you would have to do to audit the model is if you're not getting it to balance, there could be all sorts of other assets and liabilities here like related to taxes or related to to prepaid or accrued expenses or minority interest or all sorts of other things. And you need to very carefully monitor that they're being picked up somewhere in the cash flow statement that the cash flow statement is truly capturing the changes and everything on the balance sheet. So that would be the biggest but if the question is about what's the difference between a forecast and like actual results, you would you would make a sensitivity analysis at the bottom here to see what the model is most sensitive to. But most likely, it's going to be changes in revenue and and changes in margin. So those two items will really move the needle if if all of a sudden revenue growth is 10% instead of five, it's going to be a huge difference in actual versus forecast. Same thing if this goes down, you know, the numbers can the numbers can move quite a bit. So if I put this to zero, you know, you can see the net impact there. And if you were to make it zero, all the way across, that actually adds up to quite a big difference. So you know, that's, that's probably the biggest sensitivity. Yeah, there was a question this is I guess is getting pretty detailed about is it is it common to always use 365 and ignore leap years? Like, I guess the short answer is, depends how detailed need to be. So if you use certain date functions in Excel, Excel will capture leap years for you, you using the date functions, it will actually tell you if it was 365 or 366 days. If you use those functions, instead of dividing by 365, automatically, you could have a formula like here or a cell that actually has the days per period. And then you would have, you know, one of the years would have 366. And so you would actually have it flowing through the model with these with those days laid out there. So you could add that level of detail if you need it. Okay, for there's more questions about drip, which I'm happy to take offline. So so I will follow up on that with email. It's been exactly 45 minutes now. So I do want to stay true to the time. And thanks, everyone for joining us. We had a lot of participants on this call. I'm going to send everyone the completed model. And I'm also going to send out the recording of this video. If you have any further questions, please email learning at corporate finance institute.com. And thanks, everyone so much for joining us. We really appreciate having you on this call. Thank you.