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 just 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 til 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 fitting 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 to refresher on them please just take our free Excel crash course it's jam-packed with all sorts of tips tricks shortcuts formulas and function alright 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 models 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 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 bottom 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 two many worksheets and it may get too complicated so our preferred method is to use a single tab model and then 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 each of each of the countries that it operated in independently so we highly recommend the single tab model and that's the 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 even 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 a balance sheet you can do ap inventory and AR then you're going to get stuck with ppm so you go to a separate schedule remodel PP any on its own and then you can get into the financing and capital structure where we make assumptions about debt and equity when the PPV 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 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 receive the 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 forecasts 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 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 why's 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 your revenue times 1 plus some growth rates cost of goods sold is going to be a function of revenue and a cost of the total margin and then gross profit is going to be equal to revenue minus cost of goods sold the leave those in place I can fill them right with ctrl R if you want to 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's that was really straightforward got a growth rate assumption for revenue and a margin assumption for cogs 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 linked to an assumption that has a dollar value same with rent and overhead we've said renting overhead is going to be constant over time it's not going to grow with revenue so we simply 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 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 have been 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 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 values so 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 average days that it takes to our inventory to turnover 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 the inventory so I can fill those right so account just for just to review this because it's not the most intuitive accounts receivable is a function of revenue because we 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 okay 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 day's assumption the 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 our cost of goods sold there could be some other items on the income statement that has payable terms but for simplicity 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're multiplied by 37 and divided by 365 so that's the percentage 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 and what we can do on the balance sheet and 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 to and it will build some schedules at the bottom here these are very simplified versions of the schedules you would build 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 closing balance add to it by spending capital on PP&E either buying additional equipment or investing in the existing and then when you deduct from it with depreciation so let's go 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 year's closing balance the additions are up in the assumptions section where we are adding this number here in the form of capital expenditures who are capital asset base and then we're saying the depreciation and this is just a very basic assumption we're saying that depreciation is is a function of the opening balance times our depreciation rate which we've said is 40% 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% 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 a balance sheet so once you have that filled you 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 periods 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 principle repayment 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 interest rate now there's all sorts of additional complexity you could add here of course the opening balance is not true reflection of what the total balance was throughout the entire year but for simplicity sake and to complete this model this is this is a great way to do it we avoid circular references or any other issues not 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 wouldn't 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 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 the 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 it accounts payable both of us 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 a 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 net working capital I'm just going to copy this formula over 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 is calculating it's saying what 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 draining 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 it'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 and now we can go back up to the income statement and you'll remember where we got stuck here with depreciation and interest in 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 J 94 and press enter I can also pull forward the interest expense this down in this debt schedule so here's my interest expense in J 101 and then I can take these two rows and selecting the area pressing ctrl R I feel it right these formulas down here total expenses and earnings before tax these can actually just be copied over if we had them in those other cells so we're simply copying over the total for expenses and then taking the difference between gross profits and expenses here to calculate earnings before tax with earnings before tax in place I can finally calculate our tax expense which is DB T 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 etc but for now the exercise is to complete three financial statements so we're going to keep all of our assumptions Barry's and 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 is completely empty but let's trip away here we built this property plant and equipment scheduled down below so so we can now fill in the PPM e line on the balance sheet so let's go down to our PPV schedule and we can select the closing balance or PG&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 a total asset 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 our final piece that tells us that the models working or not so we're going to save that but 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 J 100 and just link that in I can then also copy across this subtotal so like I'm filling all these right with ctrl 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 that quitting capital it's going to be sort of like a corkscrew where you bought 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 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 end in 2020 so what I can do is link that link that up and I can fill it right with ctrl R and you'll see that it goes down in 2020 as a company repurchase and retired shares ok retained earnings works in more or less the same way it's equal to the prior period plus this period 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 so 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 models 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 you put that in place we then need to add back any non-cash items as you as you know earnings is not a cash metric and expenses like depreciation and amortization stock based compensation unrealized gains or losses there are all sorts of non-cash items but depreciation and amortization is 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 our need to 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 a dustman here once that's in place I can copy this subtotal across and I'm going to fill the hole in right with control R so 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 and in this case it's a fairly simple assumption that we are only form as investing is in property plant and equipment and that's found on this line here J 93 where we have our capital expenditures so then the total cash that's spent on investing this is a little bit redundant but this this total line is just equal to whatever we spent 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 of that 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 is going to be any increase or repayment of debt and with equity you 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 subtotals 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 did 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 a outflow of cash so we subtract that and then cash from financing is a source of cash you could have it so the disc 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 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 they simply be 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 cheque as well so with all these formulas and in place I can now go down your own press control are to fill these right and let's just 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 so let's just review what we've done we filled in the income statement we 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 we what we do is we take 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 day then you celebrate okay it works so now you can press control R let's go all your cash across then you get super happy because everything balances it's all live linked at all it all works and best of all there's no plugs there's no like force solution in here if anything if any of the formulas were off it wouldn't balance it would work so this is great news we build this together here hopefully you've been working along with this 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 to feel those questions right now or you can oh email later and I'm happy to take questions offline so let's just 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 you 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 be 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'd have to actually change these formulas here we're in this one in accounts payable you see how it's it's taking or let me use accounts receivable actually it's easier so it's taking a full year of revenue and it's taking a full year of revenue multiplying by eighteen days and dividing by 365 in your case and the monthly model you would only have one month or revenue so you wouldn't multiply it by eighteen and divided by 365 what you 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 data to do with accounts receivable inventory and accounts payable but otherwise you more or less builder the same on a monthly basis so maybe that's a good idea though we could do like a monthly model on on next live demonstration because we get a lot of questions about monthly okay 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 business company a what I would do is I would essentially copy this copy this tab move it over I just have to click udesky x here okay and then this would be like let's call this target code so I would have you know our company here target the code here of course all these numbers would change these are the duties of utility 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 it's reading more questions here yes we'll share the video after the session that was another question yeah and then 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 of monthly and quarterly so you can just see how it would be modified it would take far too long to to go into any more detail on this call but what I will do is it sent something out to give you some advice on how to do this monthly or quarterly okay 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 you buy discrepancy you mean the models not working the law 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 interests 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 balance sheet so that would be the bigot but if the question is about what's the difference between a forecast and like actual results you would prove 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 the same thing that this goes down you know the numbers going to the numbers in the 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 no that's that's probably the biggest sense participe yeah there was a question this is I guess I'm getting pretty detailed about is it is it common to always use 365 and ignore leap years like I guess the short answer is it depends how details need to deep so if you use certain date functions in Excel Excel will capture leap years for 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 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 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 slowly 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 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

Channel: Corporate Finance Institute

Views: 394,618

Rating: 4.956532 out of 5

Keywords: link the 3 financial statements, link the 3 financial statements together in excel, how to link link the 3 financial statements, linking the 3 financial statements together, financial modeling, cfi webinar, corporate finance, corporate valuation, excel training, finance training, finance education

Id: mt5Ve01n_X0

Length: 41min 42sec (2502 seconds)

Published: Wed Jun 28 2017