3 Statement Financial Model | Building From Scratch

hi everyone Eric here today we are gonna learn  how to build a three statement financial model in   Microsoft Excel the income statement the balance  sheet and the cash flow statement how to connect   them and what they really mean okay let's get  started okay let's talk about each of these three   financial statements and what they are and what  they represent so first off the income statement   and the cash flow statements are flow statements  meaning they represent periods of time whereas   the balance sheet represents one specific moment  in time so the income statement tells us how much   money we made and the expenses related to those  sales but the cash flow it's missing many things   for instance changes in what you owe and what  you own so if we raised debt if we raised growth   investments things like this you would never  see that on the income statement you would only   see that on the balance sheet and then you would  convert the balance sheet into a flow statement   which is actually what the cash flow statement is  so you need each of these three statements to get   the full picture of what the company actually  looks like and how it's doing so in order to   build this three statement financial model we  need to first build the income statement and   we are going to leave depreciation and interest  blank because those we have to calculate from the   balance sheet next we're going to create a capex  and depreciation schedule and those are basically   just the assets that we are buying and how we  depreciate them over time step three is build   the balance sheet based off of historical numbers  and based off the capex and then finally we need   to build a cash flow statement and tie it back  to the balance sheet so that our balance sheet   actually balances this is usually the trickiest  part of building the model so first let's get   started building the income statement all income  statements we need to start with assumptions so   what kind of business do we have and really what  are we doing here so let's create an assumptions   section and start so we will obviously have  revenue let's say revenue and so we're going to   use example of a business I used in a previous  lecture which is a subscription-based coffee   company so you you basically sign up to buy coffee  and we send it to you in the mail and so we'll use   those basic numbers so to calculate our revenue we  would of course need average order value we need   refunds as a percentage of revenue discounts okay  so everything else will be a percentage of revenue   so let's say our new customers and remember when  you're doing this you always want to put this   stuff in blue so any assumption hard coded number  if it's not a formula you put it in blue when   you're doing financial modeling so new customers  let's say 75,000 for the first year okay so we   start at 75,000 and then okay subsequent years  let's say we go to 120,000 then we go to 250,000   and then 450,000 okay great next average order  value so the average order value we're gonna say   for you know each of these orders is $40 and we  can just say that that never changes next refunds   as a percentage of revenue okay so if we're gonna  put in a percentage of revenue here i'm gonna say   5% and then discounts as a percentage of revenue  let's say 8% so then if we have the revenue we   can easily calculate those numbers next we will  have cost of goods sold and the things that we'll   need to calculate that are product fulfillment and  merchant services let's say so product basically   represents okay what percent of revenue it goes  to basically just buying the coffee itself let's   say 35% next we have fulfillment which is let's  say 5% and merchant services this is what we pay   the credit card companies three percent so these  are all percentages of revenue and we'll be able   to calculate the cost of goods sold from that and  finally you have your operating expenses and let's   say we have personnel and that is mm let's say 20  percent of revenue and next we have from marketing   let's say that's 10 percent of revenue and finally  some kind of other category that's five percent   of revenue and so with all of this we should  be able to calculate most of the things on the   income statement there's a couple other important  categories depreciation but we need to get this   from our other model so we will be linking this  back in in the minutes to come interest so this   is interest payments on any outstanding debt you  have and this will also actually be from the other   model because we don't have the amount of debt yet  and then finally we'll have to have some kind of   tax rate so tax rate and the debt the tax rate  currently in the United States right now is 21%   so I'll just put that in there for the tax rate  okay so up to now we have all the assumptions to   make a pretty simple income statement insert some  rows here get some space and we obviously start   the income statement itself with just a revenue  section revenue make bold put an underline and   then we'll have gross revenue and then we can sort  of steal some of these categories from down below   refunds discounts so we'll control-c control-v and  I'll just take that out here and then we'll have   net revenue which we'll put in bold and okay so  gross revenue will just be orders times the price   which is the average order value and so that's  easy enough so here let's put a dollar format on   that and for refunds it'll be basically you know  whatever our assumption says it's 5% of revenue   but remember put a negative sign revenue times  asterisk the 5% and in this case I want to put   a dollar sign in front of the 5 why that means  in this formula I will lock that row so that   I can copy this formula to other rows but that  particular row won't change okay let me show you   what I mean so let's put a some commas in this  and let's say I want to copy this down one row   so I do control C and then I do control V what  happened this row continued to be locked on row   five so that's what I mean so if I highlight this  hit ctrl C shift right arrow three times control   V now I have the full section now I want to put a  bottom border on this and now I can sum up the net   revenue okay so our net revenue is 2.6 million and  then it goes up and up and up and next let's move   on to the cost of goods sold bold and now we can  go steal some of these names just from down below   control C copy that and these these are basically  exactly the same so we're gonna take in this case   net revenue not the gross revenue and lock the row  but I can hit f4 multiple times and toggle through   different kinds of locks so I'm looking for the  dollar sign just in front of the eight got it   that's all right there and you can see now that  I can just copy this formula it references the   revenue every single time and it works great so  in this case control C shift right arrow control   V and now if I want to put the bottom border hit  alt that brings up the menu and I can hit H that   brings up the Home tab now i see this B here where  the borders are hit B and now I can just hit the   letter associated with whatever I want I want  the bottom border o so I'll just hit o and then   you'll see I've got a bottom border so that's a  little trick using Excel without the mouse makes   you really really fast and it's basically the only  way to survive if you have a really rigorous Excel   job if you're gonna do something like banking  or venture capital or something like this you   need to be really fast so gross margin now is of  course revenue minus cost of goods sold so revenue   minus cost of goods sold let's put the fancy top  and double bottom border on this and then we can   even show a percent a gross margin percent  here so that's just the gross margin divided   by revenue just so we can see okay what you know  what percent at the product level are we making   in profit in terms of our revenue so you can see  it's flat every year and then finally we'll have   the operating expenses again bold what are all of  our costs down here we've got personnel marketing   other depreciation copied this back up above for  personnel we're going to take the net revenue and   we're going to multiply it by our assumption and  again don't forget to put a dollar symbol in front   of the row great so now we've got all of this we  can just copy the the formula because we organized   our model very very nicely and for depreciation  depreciation represents the expenses of things   that we bought in the past this is actually  something we count we calculate on the balance   sheet and on that capex schedule so we will get  this number later on once we have calculated it   but at this point we don't know what it is and  there's no way for us to calculate it so now we   have the total opex here again I want to put the  bottom border you can see I just did that with   a keyboard shortcut and it's already there it's  really fast and so we've got total opex operating   operating income again another important metric  which is the gross margin minus total opex and   again this is not our real operating income  because we're missing depreciation but we will   get that filled in momentarily here and then here  we're gonna have our interest payments interest   again this is something we need to calculate  based on how much debt we have we're gonna be   paying an interest based on our outstanding debt  we don't know what that is yet so now we have net   interest before net income for taxes so this will  be operating income minus interest and you'll see   okay why do we have the second metric because you  actually pay taxes on your net income after you   pay the interest so the interest sort of shields  you from taxes which is something very interesting   about interest which is that there's sort of an  incentive to take on debt so now we calculate   our taxes tax rate twenty one percent okay we've  got the model all set up and finally just two last   things here or maybe three let's say net income  net income percent and just for fun let's throw   in ebitda because that's a very important kind of  private equity metric and so we have a net income   before taxes - taxes then we take our net income  divided by our net revenue and we're gonna get   some percent so I can copy this forward I can you  know put some some different formats on this make   this bold and then our ebitda is our operating  income plus our depreciation obviously again we   can't really see it right now but this is sort of  a cash flow proxy people a lot of times compare   this number to how much debt a company has to  try to see okay can this company pay off its   debt can it have more debt should have less debt  so it's it's a it's an important metric as well   so now let's jump to the capex and depreciation  schedule so what is the capex and depreciation   schedule this is the area where we show any  assets that we're going to buy throughout   the year any equipment software physical stuff  and then we also calculate the depreciation and   depreciation is where we basically take the cost  of the asset and we put it on the income statement   over the the useful life time of the object so  if we buy something for $100,000 in cash but we   can use it for five years we put 100,000 divided  by five which is 20,000 every year on the income   statement so that we match the expense with the  actual asset so here's what I mean first off we   need to create a section that says useful life  in years so that will that will help us here   calculating the depreciation so we have our capex  and that just means asset purchases so let's say   for our business we are going to need to buy  some servers we're going to need to buy some   custom software maybe we have someone build it  for us and then finally for our warehouse we're   going to need to buy a forklift and that'll  be our total capex here and we can put some   of these nice formats on it and so then so then  you need to estimate okay how long can I actually   use this asset okay first off we have servers  let's say they're good for five years and then   we have custom software you know we think we can  use that for three years and then a forklift for   six years it lasts a long time and then over here  let's put a dollar sign on format on these numbers   let's say we buy $75,000 of servers in year  one fifty thousand of custom software in year   one and then a hundred thousand more we need some  more software near to and then a hundred thousand   more and you three and then finally we end up  buying forklift for $30,000 in year three now   you're going to calculate okay each year what  were our investments in assets and in year four   we didn't need to buy any assets okay this is  going to go straight on to the balance sheet but   we also need the depreciation which is going to go  on to the income statement so if we just copy what   we have from above and then we'll have total D&A  usually it's called depreciation and amortization   and amortization is the depreciation of like an  intellectual asset so it's not a physical asset   but something that has value order on this okay  so here we have our depreciation we have these   servers that cost $75,000 and I'm going to hit  f4 you'll see you lock the the column and the   row and then we are going to divide that by five  years and again I'm going to hit f4 so we lock   the cells so we know that that's going to cost us  one year two year three year four years so at that   point it's four out of the five years have been  accounted for and we're just spreading the cost   of our original investment over time so we can put  it back on the income statement next you have your   custom software so in year one we have 50,000 and  we know that that 50,000 is good for three years   okay and we have nothing else so let's copy the  format down here and so we know in year one it's   gonna be 50,000 and so let's put this here so now  we get the 50,000 in the second year of its three   years but now we had another investment so now  we have a hundred thousand divided by three years   so they're both the depreciation of both items is  happening at the same time and then in year three   we have an even more extreme thing where we have a  third investment and the depreciation of that item   is happening also at the same time and finally  in year four we have the depreciation of a few   of the items but now the first 50,000 three years  have passed so we know that's fully depreciated so   we're just depreciating the last two investments  and then finally for the forklift we know that's   going to be good for six years divided by six  here copy it over so now you can see the sort   of waterfall of depreciation that we need to  calculate on top of you know rolling forward   throughout all the years okay so at this point  we can tie the D&A back in and into the income   statement so if we go back to the income statement  and we hit equals and we come back to the capex we   can say okay you know the D&A this year is 32,000  let's of course fix the formats take off the weird   color and then copy C shift right arrows and then  control V so now we have this so we actually can   see our real operating income at this point you  can see our real ebitda at this point but in order   to calculate the regular interest we're going to  need to go to the balance sheet so you'll see in   order to count do a forecast of the balance sheet  we are going to need the historical numbers so   here you'll see the assets you'll see okay how  much cash did we have in year zero you know at   the end of last year and then accounts receivable  accounts receivable are things that people owe us   and are going to pay us with in less than one  year but for instance let's say I gave you a   service and I gave you an invoice and I said okay  you have 60 days to pay me if I haven't received   the cash yet I put that here but it's it's very  similar to cash because you'll receive it soon   and then your fixed assets are the things that  you own so what we just calculated on the capex   you know all the assets you buy and below - the  amount of depreciation that they have so this   shows you the net value okay I bought it you know  three years ago but we've sort of depreciated it   by half so now it's worth less and that's your  total asset so last year we had 4.4 million in   liabilities you have accounts payable this is like  accounts receivable but the opposite this is where   I owe someone money and I'm planning to pay them  soon but I haven't paid them yet so I need to pay   them in one year or less if it's in this account  deferred revenue is basically when I sell someone   a service but I haven't given them the service  yet so you paid me you know ten thousand dollars   to do ABC thing but I haven't done it yet I put  that revenue here because I can't put it on the   income statement because you need to match the  revenue with whenever you do it long-term debt   we've got three million dollars on the balance  sheet so that's the total liabilities total things   that that our company owes to someone else okay  next we have the common stock and this is related   to basically people investing in the company  this number is pretty meaningless if you look   at like big public companies it has to do with  the original share price and some other things   but it doesn't really tell you much retained  earnings are your net income from the balance   from the income statement and you basically add  it up every year on the balance sheet and the   one thing you need to know is that liabilities  plus shareholders equity these two sections if   you add them together it must equal the same as  your assets if these two numbers aren't the same   you're in big trouble and there's a mistake  and for that reason we put a section called   the balance check where we take the assets and we  subtract liabilities plus equity and this number   should be 0 if it's not 0 you screwed something  up so always have this always keep this in your   mind so now let's forecast the balance sheet  so start with revenue let's start with revenue   so and let's just specify this is net revenue  so the first things let's try to forecast our   accounts receivable and this is going to be a  percent of revenue accounts payable percent of   revenue and deferred revenue deferred revenue  again these are these are all just percentage   of revenue and you know generally some percentage  of your revenue people are going to owe you you're   gonna owe other people so revenue is really easy  on this one so let's put in some some years here   so year 1 2 3 4 copy just copy it in up here  and we want to calculate accounts receivable   so let's put in our revenue let's link it in  equals let's go to the income statement where's   our net revenue there it is let's put a little  format on it I don't know if it needs to be so   what control C copy it over control V so now  you can see that you you have your each year's   revenue okay and so accounts receivable let's say  a reasonable number is 5% of revenue at any given   time your clients are in the process of paying  you and your accounts payable I think this is   the wrong blue yeah I want a darker one and your  accounts payable let's say 6% so that's what you   owe you generally want your accounts payable to  be higher you want to owe more money you want to   basically have your clients pay you really fast  and then you want to pay people slowly because   then you get to keep more cash so that's a kind  of a rule of thumb and deferred revenue let's say   it's like 3% okay control C control V and let's  say this deferred revenue is actually going up   over time we're selling some sort of long-term  plan where we sell a bunch upfront and then we   give it to them over time so this number is let's  say going up eight and then maybe even 10 and that   would be a good business decision so let's go  up to accounts receivable and in this case we   will say okay net revenue times 5% gives us an  accounts receivable number control-c control-v  and then you know we want to sum this up you can  see this is summing so we can just copy this over   and let's see here we want to put a bottom border  here let's take the the dollar signs off and so   we've got accounts receivable now let's go down  to accounts payable so we can sort of just copy   this this format down below makes it easy and so  in the case of accounts payable we want to just do   the exact same thing I'm just trying to take off  the border here so we take revenue multiply it   by accounts payable and then in this case we take  revenue multiply it again by the deferred revenue   assumption so in this case all we just need to  do is control-c control-v okay so we've got a   chunk of our balance sheet forecasted what's next  next we can look at fixed assets okay so how many   assets did we buy this year well let's take the  assets from last year and add to it the capex we   already calculated this so we add the 125 then  your total assets are 40 plus 125 165 so you   can just copy that over and then our accumulated  depreciation it's negative because it's reducing   the value it's showing that the asset is losing  value so you take your negative 10,000 and then   you subtract the depreciation so you'll see this  makes the value actually the negative value higher   and higher so your net assets the net value of  your assets is is basically staying pretty flat   because you're buying more but you're depreciating  them so this sort of shows you what the real value   is and then you'll have your total assets cash we  cannot calculate this number without making a cash   flow statement so we have to leave this empty  and again you see all these statements are very   connected so you need to know how to do all three  in order to make any of them next long-term debt   so let's make some assumptions around our debt so  let's say we have basically net borrowing so this   is like new debt that we take on then we have our  debt payments and then we have our interest rate   and then we have our interest payments payments  okay great so let's say these two numbers we'll   just put in you know some numbers that make sense  here so let's say we're paying like six hundred   thousand a year in debt payments but in year two  we want to take on another 1.5 million let's see   what that looks like so we take our debt we add  new debt which in this year at zero and when we   subtract debt payments so you can see okay we  paid off six hundred thousand then in year two   okay we added a new debt so probably our debt  payment has to go up it's probably gonna be say   nine hundred thousand now and so then probably for  every year so because what we're going to need to   do is we're gonna have to pay this debt off over  time with our cash flow so debt's going down then   you see you have your total liability section so  this shows at any given time year 1 year 2 year   3 what do we own what do we owe finally common  stock we're just gonna do equal and then the   previous cell let's just assume we're not taking  on any new investments so this number doesn't   change unless you mess around with your with your  ownership table and then retained earnings so this   is really key this is extremely important you need  to take last year's retained earnings and add your   net income of the year to it net income if you do  not do this your balance sheet will not balance it   will never ever ever balance so just looking back  to the income statement I'm seeing our interest   is still empty so let's go down and finish up  that part of it so let's say our interest rate   is let's say it's 8% that might be you know an  actual interest and then 8% times however much   debt we have so we have this much debt so you  know we see it here again you know our debt's   going down so our interest is going down so let's  let's link that into the income statement and get   that finalized equals interest payment take  off the weird color and now you can see our   interest payment is totally done finished 100%  so that's our real net income then we have our   balance sheet we linked in the net income here  in retained earnings and so this shows you know   our earnings over time how much earnings do we  have and we show it right there on the balance   sheet and then you know each year it goes up and  up and up of course and your balance sheet will   not balance mathematically unless you do that so  now let's see does our balance sheet balance know   why not because we need the cash flow statement  in order to calculate the cash after we forecast   the cash flow statement the balance sheet will  balance in this number will be zero so let's go   to the cash flow statement okay every cash flow  statement begins with net income and you start   with net income and then you basically adjust it  for changes in the balance sheet which were not   on the income statement so first things first  let's start with net income so you put that at   the top and then you know put a nice format on  it and then you will start with your operating   activities so what are your operating activities  you have depreciation and first off we're gonna   have a section for operating activities a section  for investing activities so investing activities   are assets that you buy and then we're gonna  have financing activities so these are like   any debt or equity you raise so this is just  related to running your business this is stuff   you buy and this is money you raise so the cash  flow statement is split into these three sort of   sections so you can see you can see what's going  on in the business so depreciation change in ar   accounts receivable change in accounts payable  change in deferred rev and that's going to be   your operating cash flow so first off we need to  figure out okay we have this depreciation going   in the income statement but it's not actually cash  it's actually just a kind of a made-up expense   whereas the cash already went out so we first take  the depreciation go back to the capex schedule and   grab the depreciation and put it on this thing  so we've got our depreciation let's copy that   forward next change in accounts receivable so we  go to the balance sheet we say okay what's our   balance in accounts receivable last year what's  minus our balance in accounts receivable this   year okay it's negative 10,000 why because people  owe us more money now than they did last year so   it's negative for our cash so we can just copy  that forward change in accounts payable let's   go down to the balance sheet this is the opposite  what do we owe people compared to what do we owe   them last year eighty one thousand it's because  we haven't paid them and that number has grown so   that's actually positive for our cash and deferred  revenue is exactly the same so we can even just   copy that down one because we know that deferred  revenue is right below accounts payable okay so   now we have our operating cash flow so what we  do is we basically total all of this we take our   net income and then we add all of this to it so  you can see it's 432,000 and this is this is how   much cash our company is basically making but now  let's look at okay what do we actually buy capex   those are investments in fixed assets so in this  case we just link in okay you know we spent one   hundred and twenty five thousand dollars in year  one and you know every year we spent some money   but not in year four and this actually gives you  your free cash flow which is you know the business   the cash from your operations minus any assets  you bought so here's your free cash flow and then   finally you have your financing activities so you  know you have your debt repayment so that's going   to be cash that goes away but then you have your  net borrowings so that's going to be money you   borrowed so that's going to be an increase in  cash and then you know cash flow from financing   so let's look here debt repayment debt repayment  so we can just link this in but remember make it   negative because that's negative cash and I'm  pulling it out of the assumption section but   these two repayment and borrowing they're in the  same account on the balance sheet so I can use   one account or I can split it into two and then in  this case and that borrowing is going to be just   any new cash that came onto our balance sheet  so you'll see that net cash flow from financing   every year we have cash going out because we're  paying our debt but in this one year we have cash   coming in because we're borrowing so finally we  have our net cash flow the total amount of money   or actual cash money that went that our business  produced this year so we have our free cash flow   plus net cash flow so in the first year it's  actually negative but we had a big cash balance   so we should be fine and then it turns positive  so here's how we tie this into the balance sheet   first off I want to just freeze this pane and  then we go to the balance sheet we take cash   from the previous year and then we add to it net  cash flow if we did everything correctly correctly   and I'm praying right now our balance sheet should  balance oh look at that incredible so copy copy it   over you can take the formats off this look down  again Wow balance sheet is balanced assets equal   liabilities plus equity in every year this means  you calculated everything correctly so a couple   more points troubleshooting the biggest problem  that you're gonna have building these models to   suit your balance sheet does not balance because  balance sheets can look really different depending   on what kind of company it is so here's a little  checklist of things to double-check to see if you   have a problem to try to find it is your net  income linked to the retained earnings yes or   no it should be yes is your cash flow your net  cash flow linked to cash it should obviously   be yes have you included all of your balance  sheet accounts in your cash flow statement if   you forgot to put accounts receivable in your  cash flow statement your balance sheet will   not balance so do not forget an account and the  longer the balance sheet the longer the cash flow   statement do your historical financials balance  does this historical year balance if this thing   never balances then you'll never get any year  afterwards to balance so those are some tricks for   for troubleshooting so that's it in this video we  learned how to build an income statement a balance   sheet and a cash flow statement if you like this  content please subscribe to my channel like this   video leave me a comment it would really help me  out and I'll keep making content like and if you   want to learn more about Microsoft Excel check  out my discounted course links below I released   some courses would love to see you there and  keep learning together thank you for your time

Channel: Eric Andrews

Views: 191,039

Rating: 4.964045 out of 5

Keywords: 3 statement financial model, building a 3 statement financial model, 3 statement financial model template, income statement, balance sheet, 3 statement model excel, three statement financial model, build 3 statement financial model, financial modeling, financial modeling in excel, balance balance sheet, 3 statement financial modeling course

Id: xlXDZyZ9azk

Length: 37min 59sec (2279 seconds)

Published: Wed Jun 03 2020