Link the 3 Financial Statements in Excel - Tutorial | Corporate Finance Institute

Hello everyone, welcome to the webinar on

link

ing the three

financial

statements

. We are waiting for everyone to call. We have quite a few people logging in this session, so we'll give everyone a minute to log in. and then we will continue. If you have any questions during the webinar, you can use the Q&A tool to ask questions and some of them I can answer as we go and the rest I can leave until the end, depending on the nature of the questions, but it seems which almost everyone is logged in now, so I'm going to continue, thank you all for joining us on how to

link

the three

financial

statements

in Excel.

This is offered by the Institute of Corporate Finance 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 goals of this session are a few things, first of all, let's talk about why. we would link the three financial statements, what is the purpose of doing this and then what is the best design to do, that there are a few different ways to structure your model and I will talk about the pros and cons of different approaches to designing your model, then We'll break down the actual approach to linking all three statements.

An iterative approach is appropriate, as there are steps you can only take after you have completed other steps, so you have to go back in this circular fashion. and then I will do a live demonstration of how to link these statements in Excel, so this entire session will take about 45 minutes with at least about half an hour of work in Excel before we start, we assume you have a basic knowledge. of accounting by taking this webinar, otherwise we offer a free accounting fundamentals course on our website, we also assume that you have basic knowledge of reading financial statements and interpreting them and, again, if not, we have a free course on our site we will teach you just that and finally Excel skills, we will use some Excel formulas and shortcut functions so if any of these are new to you or you want to refresh them just take our free crash course of Excel, which is packed with all kinds. of tips, tricks, shortcuts, formulas and functions, so let's talk about why we would link the three financial statements.

It is really the basis of all other financial analysis. Before you can do anything else, you need the income statement, balance sheet, and cash flow statement. all linked and flowing dynamically as soon as it's implemented you can start doing financial analysis, you can test scenarios, you can look at ratios, you can look at profitability metrics, all kinds of things and then on top of your financial analysis you can look at layers. a discounted cash flow model where you would be valuing the company, you could go even further if you look at an M&A model where you have 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 statements model the basis.

That's why we're doing this webinar today for free. We want to make sure everyone has a solid understanding of how to link. financial statements and from there you can move on 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 at the bottom that we have different ones. sheets for assumptions the income statement the balance sheet the cash flow and supporting schedules this is a very traditional way of designing a financial model it is common practice to do it this way, but the problem with doing it this way is that One thing This linking can be more difficult because you have to go between worksheets to link your formulas and the other thing is that if the model gets very large and it's a very complicated business, there can be two worksheets and it can become too much. complicated, so our preferred method is to use a single tab model and then you'll notice that in this screenshot here we only have one worksheet in the file and instead of organizing everything horizontally like in the example on the left, We've organized everything vertically so you have your assumptions at the top. 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 much easier if you are a company that has multiple units and wants to model each one. of those units independently or from each of the countries in which it operated independently, so we highly recommend the single tab model and that is the method that we will demonstrate today in this webinar. Now let's take a look at the actual focus there. It is an iterative approach to linking account statements; you can't just build them from top to bottom;

It starts with income and continues until you reach the income statement; however, you will 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 go down to the balance sheet on a balance sheet, you can do an inventory ap and AR, then you will be stuck with ppm, so it goes to a separate schedule to reshape the PP on its own and then you can go into the financing and capital structure where we make assumptions about debt and equity when the PPV schedule and the capital structure are finished, then you can return to income. statement and plug in the depreciation and interest and finish the income statement, then you can go to the balance sheet and finish it and finally do the cash flow statement, so let's work in small sections of each part of the three statements financial. statements and finally put it all together at the end that I'm going to pass to Excel and I trust that everyone received this file in the email that we sent, if not, go to your email and check that you should receive the copy. from this file, I'll quickly walk you through the layout, so we have this single tab model, as you can 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 have these numbers here that will drive the model forecasts and they are based on looking at what the business did historically in this section here, so we have the historical section and then to the right of In the forecast section, then we can open the income statement.

These are the historical numbers of this company. We simply take your financial statements and write them into Excel. Then we can see its balance below. Again these numbers are blue because that indicates that They are coded numbers, they are not formulas, finally, the cash flow statement of this company was published here and then below, which is not an actual statement, but just cash flow information. work, are these backup schedules, so there is a working capital schedule, a fixed asset schedule or a depreciation schedule and then the debt and interest schedule, so what we are going to do now is review and develop each of these sections one by one to complete the forecast for this model, so we'll start with the income statement which we'll make this top section, then we'll go down to the balance sheet and focus on these working capital items, then we'll go down to these supporting tables and we'll fill them up to the income statement to do the taxes on interest and depreciation, then go back to the balance sheet and you'll get the idea, so this is that iterative approach we talked about earlier, where we can just do things one by one. the time, so let's start with the income, if you have this open, go ahead. with me, why can you just look at 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 your previous revenue multiplied by 1 plus some growth rates, the cost of goods sold is going to be a function of revenue and a total margin cost and then gross profit will be equal to revenue minus cost of goods sold. Leave them in place. I can fill them in directly with Ctrl R if you want to check out these Excel shortcuts. please take our Excel crash course, it's free and it will teach you how to quickly complete things just like I am, so it was very easy.

I got a growth rate assumption for revenue and a margin assumption for gears, then we moved on to expenses. We've said that salaries and benefits are a fixed cost, not a percentage of revenue, so we simply tie it to an assumption that it has a dollar value, the same as rent and overhead. We have said that the rental overhead will be constant over time, it is not. they're going to grow with revenue so we just roll them out and we can populate them correctly with control R, but then we get to the depreciation and you'll notice in the assumptions that the depreciation is based on a percentage of the 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 kind of depreciation assumption you want, it's going to be a function of property, plant and equipment, so we're stuck and then we're stuck. interest.

Here we cannot complete either because the interest depends on the balance of the debt and we do not have anything on our balance sheet yet. Taxes we can't do it either because we don't know what the pre-tax profits are, so we're kind of stuck here, so now what we do is we go down to the balance sheet and say, "Okay, we can model the accounts receivable inventory and accounts payable because we already have revenue and costs sold, so accounts receivable is simply the percentage of revenue that is not paid at the end of the period, so our accounts receivable will be based on revenue multiplied by assumption we have for the number of days receivable outstanding divided by 365 to convert them to dollar values, 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 the inventory turnover and the.

Inventory is a function of the cost of goods sold multiplied by the average number of days it takes our inventory to turn over, which in this case. The case we're saying is 73 days divided by 365, so we multiply the cost of goods sold by the average days of inventory and then divide by 365 to get this dollar value of inventory so we can fill them correctly, so just account to review. This because it is not the most intuitive account receivable is a function of revenue because we recognized revenue for this business, but we have not necessarily been paid for all of that revenue and if we have an average of 18 days that it takes us to receive our revenue as payment real.

We can then multiply the revenue by 18 divided by 365, so that is the proportion of the year that is not paid at the end for inventory. We do the same thing, we take the cost of goods sold and multiply it by the days it takes to turn inventory divided by 365. Okay, now we can go down and do accounts payable as well, just like accounts receivable, accounts payable are They will be based on the assumption of one day, the number of days, but they will be applied to expenses, not income, so the expenses we will assume. Since we have terms payable on our cost of goods sold, there could be other items on the income statement that have terms payable, but for simplicity's sake we're going to assume that cost of goods sold is the only thing we have terms of payment on. and we'll say 37 days is what it takes to pay our bills, so we multiply that by 37 and divide by 365, so that's the percentage cost of goods sold that isn't paid for at the end of the year, so I can cover that right.

So at this point, we have our working capital complete with our accounts receivable, our inventory and our accounts payable, which we have reduced and what we can do on the balance sheet and, once again, we are a little bit stuck. Because we don't know what property, plant and equipment it will be, we don't have a debt number and we also don't have what we need to finish this section, so we're kind of stuck, so this is where we are now. Keep moving forward, we're not discouraged even though we don't have those numbers, we're just moving forward and we'll create some timelines at the bottom.

These are very simplified versions of the schedules that an operating company would normally create there. There would be a lot more details here, but what we can do is first do our property, plant and equipment schedule, so what we havehere is an opening balance that is the same as the closing balance of the last period and add to it by spending capital on PP&E, either purchasing additional equipment or investing in the existing one and then when you deduct it with depreciation, we go to this schedule, We call it a corkscrew calculation because it pulls out the numbers like a corkscrew, so the beginning balance is equal to last year's ending balance. the additions are in the assumptions section where we add this number here in the form of capital expenditures which are base capital assets and then we say depreciation and this is just a very basic assumption, we say that depreciation is a function of the beginning balance multiplied by our depreciation rate, which we have said is 40%.

Now, actually, 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. depreciation is 40% of the beginning balance, so we get our ending balance, which is the opening plus our capital expenditures and then minus our depreciation, the ending balance is what we need to put on a balance sheet, so once Once you have completed it, you can select this entire area and press control R to complete it correctly. We're also going to build the debt and interest schedule so it's a corkscrew calculation again. like the depreciation schedule, so our beginning balance is equal to the ending balance of the last period, there is no time difference between these two and then we have an assumption about any debt that was paid or additional debt that was borrowed, so that this assumption here is any principal payments or additions to our debt balance, the closing balance is simply the opening balance plus any issuances or repayments and then we calculate our interest expense again, a very simple assumption that is the opening balance multiplied by the interest rate.

Now there's all kinds of additional complexity you could add here, of course. The beginning balance is not a true reflection of what the total balance was throughout the year, but for simplicity and to complete this model, this is a great way to do it, we avoid circular references or any other problems that do not create more schedules complicated. now we have this debt schedule where we have our ending debt balance, we have our depreciation schedule where we have a closing value of property, plant and equipment and that allows us to go up and complete more of the income statement. and the balance sheet because we wouldn't, we were missing these items before, but before we do that, let's quickly complete this working capital schedule, this will help us in 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 as a quick summary, if accounts receivable increases, that indicates that we have not been paid for more revenue than we have recorded, so if accounts receivable increases, that will be a loss to our cash flow. inventory inventory goes up presumably we pay for that inventory so there will also be a reduction in cash payables.

It works in the opposite direction with accounts payable, if it's accounts payable for both of us that means we haven't paid bills so it's a source of cash, if our accounts payable went down we would reduce our cash because we paid off all of those invoices that we owed, so this is a quick summary of the changes in working capital, so all we have to do here is just link to the balance sheet where we have The accounts receivable inventory already calculated is right below, like this that I can copy the formula and then the accounts payable.

I can link them to the balance sheet right there, so to calculate net working capital I'm just going to copy this formula. I take accounts receivable. plus inventory minus accounts payable. I get my total net working capital, but what I need to know for cash flow calculations is the change in net working capital, not just the total balance, that's what this line below calculates, it says what has been the change in network capital over this period and that affects my cash flow because as you'll see here, AR has increased so it's a bit cash draining. Inventory has also increased, but accounts payable has increased, which is an offset, so the net.

The effect is actually very small, it's just a very small increase in working capital, so with this in place I can press control R to complete that correctly, so that our support schedules are ready here and now we can go back to the income statement and you remember where we got stuck here with depreciation and interest on taxes. Okay, now we can fill them in because we have the depreciation on this schedule, so I can go to my depreciation assumption here J 94 and press enter. I can also advance the interest expense. down on this debt schedule, so here's my interest expense on J 101 and then I can take these two rows and select the area by pressing Ctrl R.

I think that's right, these formulas down here, total expenses and profit before taxes , they can actually be copied if we had them in those other cells, so we just copied the total expenses and then took the difference between the gross profits and the expenses here to calculate the pre-tax profits. With pre-tax earnings in place, I can finally calculate our tax expense, which is DB T. times the 28% tax rate assumption 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, etc. but for now the exercise is to complete three financial statements, so let's keep all of our Barry assumptions and now we can complete this income statement here with a very simplified approach to taxes that takes us to net income, so that's a great news that we have the income statement ready, but if I come back here there are still quite a few holes in the balance sheet and of course the cash flow statement is completely empty, but let's go here.

We build this scheduled property, plant and equipment next, so now we can complete line PPM e on the balance sheet. Let's go to our PPV calendar and we can select closing balance or PG&E right here and I'm going to populate that on the right with that instead. I would also like to take this subtotal here as an active total and populate it for Right now, this is all linked. Cash will be the last thing we do. This is going to be our final piece that tells us whether the models work or not, so we're going to save that, but scrolling down here, I know we have the debt schedule now, so I can fill in the debt, so I'm going to go down to Closing balance of debt J 100 and simply link it.

Then I can also copy this subtotal, so I'm filling in all of this okay with Ctrl R, so now what I have here is liabilities filled out because of our debt schedule and our accounts payable that we calculated earlier, but then we have equity, So how can we pull this off well with that exit capital? It's going to be kind of a corkscrew where you bought like in a previous period and then a change, so our equity will be what it was in the last period plus the assumption that we've made up here about any equity. that is either issued or bought back, so you can see here that in most cases it is zero, but this company raised some money in 2012 and then we assume they will buy back some of the equity at the end of 2020, so what can I do? is the link that links up and I can complete it correctly with Ctrl R and you will see that it decreases in 2020 as a company buyback and retired shares.

Retained earnings works more or less the same way, it is the same as the previous period. plus the net income for this period now if this company paid dividends that it doesn't pay and there is no assumption about that then they would be deducted from the retained earnings so we don't have that assumption in this case so now I can comply with that right and then finally I can calculate these subtotals here, so when I do that, I'll copy this check. This check is in place to see if the balance sheet is balanced or not. I'll complete it and of course we get this. error message at the top here saying it's not balancing, but that's perfectly fine, we know it's not going to balance, we haven't entered the cash number yet and our cash number is not going to be a socket we're not going to do simply. this is equal to the number down here so we can balance everything out, we're actually going to have to build the full cash flow statement to get our ending cash balance number which will let us know that the models are working correctly, so let's move on to below. to the cash flow statement section here the cash flow statement is mostly just a linking exercise we basically have everything we need above so the first thing we need to do is link our net income or net profits which is the starting point for operating. cash flow, so you implement it, then we need to add back any non-cash items, since as you know, profits are not a cash metric and expenses like depreciation and amortization, stock-based compensation, unrealized gains or losses, there are all kinds of cash items, but depreciation and amortization are usually the main ones, so I'm going to link them to depreciation and amortization, which is the only non-cash expense that we have in this simple example, but it's still not our cash flow because we need to adjust for changes in working capital and we created a schedule below so it's easy to just take the total change in non-cash working capital, which in In this case it is 375, so, as you remember, 375 really represents the net change in the accounts. inventory receivable and payable that we need to adjust for because we don't receive all of our revenue, we bought some inventory and we don't pay all of our invoices, so we have this little bit of dump here once, that's Instead, I can copy this subtotal and I'm going to fill the hole with control R, so just to make sure it's very clear, the reason we deduct this increase in working capital is that if the net working capital has increased. that means AR and inventory have increased more than Accounts Payable, that's a cash reduction for us because AR means we haven't been paid while AP means we haven't paid our invoices so it's a reduction of cash, then we can move on to investment cash flow and in this case it's a pretty simple assumption that we're just formed because the investment is in property, plant and equipment and that's on this line here J 93 where we have our capital expenditures, so the total cash that is spent on investment This is a bit redundant, but this total line item is equal to what we spend on property, plant and equipment.

This section could have a lot more detail depending on the business, there could be acquisitions of other companies here, there could be asset disposals there could be all sorts of things, but this is a good simple example of that. Well 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 debt of the current period and debt of the last period, whatever the difference is, it will be any increase or payment of debt and with equity will be the same - we will take our equity in this period and subtract our equity in the previous period, so that gives us our change in equity and then we can copy the subtotals here to have the change in debt, the change in equity if dividends were paid the dividends would flow here so this company had no dividends or assumptions about dividends so you'll see what happens here is that when the debt goes down on the balance sheet, it's picked up on the cash flow statement, when the capital is repurchased on the balance sheet, it's picked up on the cash flow statement here, so with all of this in place what we can now do is copy this formula.

This formula just takes the net of these three and you'll be careful to look at their signs here, so the cash from operations that we have as an addition to our cash balance, the cash invested is a cash outflow, so we subtract that and then Financing cash is a source of cash. You could have it so that the disk these numbers here are expressed as negative and then you would have to adjust their sign here and add them so that they don't No matter which way you do it, I've seen both at many companies, but be careful which sign you're using , so we will have our opening balance that is taken from last year's closing cash balance and then from this year's closing balance.

They will simply be the initial balance, the initial balance that came from the lastperiod plus the net cash increase for this period and I can also copy this check, so with all these formulas and in place now I can download your own press control. to fill them out correctly and let's just quickly scroll through the model, now we still have this error message, that's totally fine, that's totally normal, so let's review what we've done, we filled out the income statement, we created all the balance. sheet except the cash we filled in the cash flow statement and all the previous statements were supported by these schedules here, so we had our depreciation and fixed assets schedule, our debt and interest schedule and now we have the timing of the true where we have what we do is take, we take the cash number here and we link it down to the cash flow statement, the closing cash balance, this is where you cross your fingers and hope that everything worked out and you press ENTER and the day, then you celebrate, okay, it works. so now you can press control R, let's spend all your money and you'll be super happy because everything balances out, everything is live linked, everything works and the best of all is there are no plugs, there is no similar strength solution here, If anything, if any of the formulas were off, they wouldn't balance, it would work, so this is great news, we built this together here.

I hope you've been working along with this with the example I sent, but if not, I'll email it to everyone as well. that you have the solution and what we can do now is just open it up for questions if people want to write questions using the question and answer feature. I'm glad I can feel those questions right now or you can email me later and I'm glad I can take questions offline, so let's pause for a minute while people email some questions. Okay, a couple are coming. I'm 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 also have some monthly cash flow modeling courses and, you know, it's very common to be a monthly cash flow model, so really the only differences would be that, for Of course, first you know in the setup that instead of years here you would obviously have months, I guess it's pretty obvious, but otherwise it would be the same except for certain things like accounts receivable inventory and payday assumptions , so you would have to change these formulas.

Here we are in this accounts payable business. See how it's taking or let me use accounts receivable. It's actually easier, so it's taking a full year. income and it takes a full year of income multiplying by eighteen days and dividing by 365 in your case and the monthly model would only have one month of income so you wouldn't multiply it by eighteen and divide it by 365 which you have to do is adjusting that formula because it's only based on 30 days or whatever to divide by the appropriate amount, which would be 30, so it would be adjusting data related to accounts receivable inventory and accounts payable, but of Otherwise, you more or less build the same thing monthly, so maybe that's a good idea, although we could use a monthly model in the next live demo because we get a lot of questions about monthly M&A, okay, there are a few more here, okay, here's one about mergers and acquisitions and the question.

Do you suggest the company do it in the same tab or create a new tab for the other company? This is exactly why I like to do the three statements in one tab model because then what you would do is, let's say, a business venture. What I would do is basically copy this tab, move it around. I just have to click on udesky utility numbers for the target and then what I would do is have a third tab where I would take the two companies and add them together so I can copy this one again and create. a third copy so that they are identical and that makes it very easy to consolidate them, so this is the preferred method of doing it if the companies are totally different and cannot be related, you will not be able to have the same format, but many times you can Put them in the same format and it is very easy to make an M&A model.

Okay, read more questions here. Yes, we will share the video after the session. That was another question. Yes, and then. There have been some questions about the time period, so a couple of people have asked about monthly, someone actually asked now about quarterly. These are all great questions. I'm just thinking out loud. It might be best to provide a small example below. monthly and quarterly summary so you can see how it would be modified. It would take too long to go into more detail on this call, but what I'll do is send something to give you some advice on how to do this on a monthly or quarterly basis, okay, here's a question about, I guess, auditing the model.

The question is what are some of the biggest discrepancies that lead to forecast errors, so I guess I can think of a couple of different things than what that question might be asking, but one thing is that if you buy discrepancy you mean that the models don't work, the law isn't balancing, it usually happens from how changes in accounts are calculated on a complex balance sheet, so this is a very simple balance sheet, you know the current assets are just here and then the current liabilities are just here, so the changes in non-cash working capital are easy to calculate and then for the non-current items again, it's pretty simple here, but what you would have to do to audit the model is if you are not understanding it.

To balance, there could be all kinds of other assets and liabilities here, like tax-related or related to prepaid or accrued expenses or minority interests or all kinds of other things, and you need to monitor very carefully that they are being collected somewhere in the cash flow statement says the cash flow statement really captures the changes and everything on the balance sheet so I would be a fan but if the question is about what is the difference between a forecast and results similar real ones, it would show that a sensitivity analysis would be done. at the bottom here to see what the model is most sensitive to, but most likely it will be changes in revenue and changes in margin, so those two elements will really move the needle if suddenly revenue growth is 10% instead of five, there's going to be a big difference between actual and forecast, the same as this goes down, you know, the numbers change quite a bit, so if I set this to zero, you know you can see the impact net there and If you made it zero all the way, that actually adds up to a pretty big difference, so no, that's probably the biggest sense.

Yes, there was a question. I guess I'm getting pretty verbose about whether it's common. always use 365 and ignore leap years, I guess the short answer is that it depends on how deep the details go, so if you use certain date functions in Excel, Excel will capture leap years using the date functions and actually will 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 here or a cell that actually has the days per period and then you would know that one of the years would be 366, so actually Flow through the model with these with those days set there so you can add that level of detail if you need it slowly.

Okay, there are more questions about the drip that I'm happy to tune out, so I'll follow up. which with email is now exactly 45 minutes, so I want to be faithful to the time and thank you all for joining us. We had many participants on this call. I'm going to send everyone the full model and I'm also going to send the recording of this video if you have any more questions, email learning at Corporate Finance Institute com and thank you all so much for joining us. We really appreciate having you on this call, thank you.