Collaborate on Excel Pro Forma Template for Non-profit Organizations with Ease Using airSlate SignNow

Excel pro forma template for non-profit organizations

what's up everyone it's kenji here and in this  video we're going to be creating a dynamic   three statement model where we'll be linking the  balance sheet the income statement and the cash   flow statement and you can download the file i'll  be working with for free as i'll leave it in the   description below so let's get into it and here's  the steps we'll take to make this financial model   firstly we'll organize and build our  income statement using a set of assumptions   secondly we'll create schedules to model out more  complex line items like capital expenditures and   depreciation following that we'll build out our  balance sheet and lastly we'll build up the cash   flow statement and link the three statements  together and in case you're wondering when a   three statement model is useful on the one hand  it's just for regular financial analysis and on   the other hand this is usually the foundation  for any sort of valuation work that you might do   like for example a discounted cash flow an lbo  or an m a model as well so if we get into the   excel file here we've got the income statement if  you go control page down you can find the balance   sheet the statement of cash flows and the fixed  asset schedule so firstly let's start off with   the income statement press the alt w vg which is  going to get get rid of those grid lines which are   sometimes a bit annoying and this financial model  will be building is for a lemonade stand company   in its early stages like say a startup let's get  started with the income statement first so over   here you'll see that we have the year and let's  move that along to all of the other years as well   so for that we can do a formula like the edate so  equals e8 press the tab key the start date is the   previous years comma and then number of months  ahead so that's going to be in 12 months time   so put a 12 there that's going to give you 2023  and we want to move that along all the way to   these other years once we have that we want to  change the type of date here into something like   the year and then the e letter for the estimate  in this case so press the ctrl one key for that   and from there we're gonna go to custom and we're  going to customize the type of date here so what   we're gonna do is put the y four times which  is gonna stand for the years then we'll put   this sign over here which is like a dash and then  followed by that we'll we'll put a capital e which   is going to be the estimate equivalent press  enter that should change everything into this   the reason we just won the year instead of an  actual date is because the income statement is   usually for a range of period like one entire year  while on the other hand the balance sheet is for a   snapchat so a particular day this case is for a  whole year let's also start formatting the rest   of the income statements so we'll put some indents  over here for the gross revenue and the discounts   press the shift down arrow to select it from there  you'll go to alt h6 which is going to in them them   for us and after that let's also add some borders  here on the net revenue so press the ctrl b   first to bolt on it and then we'll go to alt  hb and let's say we want to add some borders   just above it so for that we will just press  the p key like so same thing over here so for   the cogs we would have to indent all of the  cogs here so alt h six put a bolt sign over   here for the total coke so control b and same  thing alt h b p and you get the general idea   so i'm just going to fast forward this whole  process so you don't have to keep looking at it once we have things formatted the way we like  them in the income statement we can go to the   very bottom by just pressing the ctrl and the down  arrow and over here you'll find a different set of   assumptions so these are all the assumptions  that we've made about the lemonade stand and   how we think it's going to perform so for example  for a revenue it would be the cup sold over here   the average price of the cup and any discounts  that we might have then we have the different   raw materials like what might be buying the  actual lemons the ice and things like that as   well as other operating expenses so these are  things like these salaries that we might have   any office or rent space that we might incur and  then lastly we have the tax rate which well it's   just inevitable right and you'll probably notice  that all of these assumptions are in blue that's   generally the convention you'll have them in this  color when they're hardcoded meaning that they're   not an actual formula and you just type the number  in yourself let's also format this area a bit more   just by putting a few indents so ctrl shift  down arrow to select all of that then alt h6   same thing over here ctrl shift down arrow alt h6  and same thing over here alt h6 for some of these   assumptions here you'll notice that there actually  as a percentage of revenue like the discounts here   raw materials etc so for that we're actually going  to change it so it's dynamic and it looks like it   so we'll go to equals and then control up all the  way till we reach b6 here press the ampersand and   then we'll put a quotation first quotation here  put a space and as a percentage percentage sign of   revenue of rev should be fine and then we'll go  close the quotations and just like so and then   press enter and now you can see we actually forgot  to put the space here in between the discount and   as the percentage of rev so press the f2 key  to get back inside the formula and let me just   add the space over here generally when you put an  ampersand and any quotations you can put whatever   you want within that sentence so in this case  we made it like this so now that we have that   we can actually just drag it down so we'll go  ctrl c and drag it all the way all the way to   this here so we've got raw materials fulfillment  and now because it's all dynamic it should be   linked to the right one same thing down over here  we'll press the shift down arrow and then ctrl v   you'll notice now this one's not quite right it's  not gross profit that should be in here so we'll   press the f2 key just to see what's going on and  you can see that we actually have to drag them a   bit lower down till the labor here press enter and  then control c again and control v and now it's   looking the way it should now that the assumptions  are looking good let's actually fill in the income   statement so go control up all the way to the  top and then for the gross revenue first that's   just going to be the price times the quantity so  we'll go equals go all the way to the assumptions   that's the cup sold times the average price per  cup press enter after at the discounts they're   going to be negative as we're essentially giving  away money in a way right so for that we'll go to   equals as we said it's a percentage of revenue so  we'll select the gross revenue and then multiply   it by minus the five percent so let's go ahead and  select that press enter and then for net revenue   we just consume these two so you can go the  shortcut for that is just the alt equals that's   going to automatically fill that and give you the  formula press enter moving on to raw materials and   here we're going to go equals and select the gross  revenue because it's a percentage of revenue again   and now this time around we want to press the  f4 key and then press the f4 key again till you   only have this dollar sign on this on the five if  you don't know these dollar signs essentially fix   a cell so if you move a formula along that cell  is going to remain fixed so that's what we want   for this one and then multiply it by the the raw  materials percentage which in this case is that 30   press enter and this way we actually want to make  it negative because it's going to be a cost right   so press the f2 key and at the very front of the  equal sign press the negative sign press enter   and then we'll just drag this drag this down  a bit so ctrl c and then just paste it down   over here like so and then for a total cogs  again same thing so alt equals press enter   i just noticed here that the formatting should  be a bit different actually so this one here   the total cogs should actually have that format  for the gross profit so for that we'll select   this over here press the alt h fp and then  just the down arrow that's going to give you   the same format for the one below and then this  one here we don't want this format we actually   want our normal format like the transaction fees  here so we'll select that by shift right arrow   and then alt hfp and then press the down arrow and  now that's looking better next up for the gross   profit here we're actually going to be equals  and that's going to be the net revenue plus   the total cogs here and you might be like well  isn't the cost a negative and it is but because   it's already a negative over here if we do a  negative minus another negative it's going to   be positive for us so instead we just sum on both  of them and that should be working for the gross   profit margin just below we want to go equals and  that's going to be the gross profit divided by the   revenue over here press enter now moving on  to the operating expenses and the first one   we've got is the labor here and again we want  to make it negative as it's going to be a cost   for us so equals negative sign control up all  the way to the gross revenue here and we want   to lock this one again so press the f4 key f4  key again so it's just on the number over here   and then multiply that by the percentage of labor  so control page down control down arrow sorry and   let's select this one here labor as a percentage  of revenue and same thing we want to control c   and just paste it down control v over here total  opex is going to be alt equal sign press enter   and then for the ebitda we'll go equals that's  going to be the gross profit plus the total opex   and in case you don't know what ebitda stands for  it's the earnings before the interest the tax the   appreciation and amortization you're probably  wondering what that means or why it's useful   and it's generally a measure of profitability and  the reason why you want something like this is to   be able to compare with other companies better for  example this is a bit of an extreme example well   let's say that one company pays zero and zero  dollars in tax and the r1 actually has a 30 tax   rate so you pay a ton in tax now those two aren't  very easily comparable if you're comparing the net   income of one versus the net income of the other  just because if that one's paying 30 tax rates   then that net income figure is obviously going  to suffer that's why you actually omit the tax   rates and you go to something like ebitda so you  can compare them more fairly next up here we've   got depreciation amortization and for now we'll  omit this we'll get back to it later on for the   ebit let's go ahead and set up the formula right  now so we'll go to equal sum press the tab key and   just select the two from here for the interest  expense we'll also meet up for now and get back   to it later but let's set up the formula already  so we'll go equal sum press the tab key and select   these two over here for taxes we do know them  they're the 21 assumption that we have below   so let's go ahead and do that so equals get the  ebt times the tax rate so ctrl down arrow all the   way to the bottom here the 21 percent press  enter but it should be a negative right for   attack so we'll go f2 key and put a negative sign  up front so press the f2 again sorry and just put   a negative here press enter for net income it's  just going to be the sum of that so equal sum   top key and select these two here lastly we have  the net income as a percentage of revenue so this   is just another ratio that we have so we'll  go equals net income divided by the revenue   so that's going to be the net revenue over here so  now that we've filled all the items for the income   statement we should be able to drag them along so  first press the control up to go all the way to   the top then the shift down arrow and just select  everything to the bottom here and then ctrl c   and then shift right shift right all the way  to the end and ctrl v that should automatically   populate for us and before we move on if you  like what you're seeing we do have an excel   for business and finance course which i created  with my friend michael who helped me with this and   he's a financial analyst at tesla in the course we  teach everything we know about excel specifically   for people either looking to break into the  industry or those in it trying to level up   their excel skills unlike most other theory-based  courses we try to make this one as practical as   possible based on our real experiences working  at companies like tesla amazon or goldman sachs   so aside from the typical lessons on formatting  formulas and charts we have case studies that   replicate the type of work you might be assigned  in your day to day ranging from financial modeling   to cleaning a data set and presenting some visual  insights related to this video we also have an   in-depth three statement model which goes over  apple's real financial statements so if you're   interested in checking it out we'll leave  the link in the description below alright   so we finished the income statement but we do have  some line items that are still not done like the   depreciation over here so for that let's go to the  very last tab which is the one on the fixed assets   so go to control page down all the way to the  last one from here this is going to help us fill   in the depreciation and amortization so firstly  let's clean up the formatting again over here a   bit so select all of this area here so ctrl shift  right arrow from there go to control one let's go   under custom again and we want to change this to  y y y y and then we'll put this reverse dash sign   and press the e press enter and that's going to  change it for us we also want to change a bit over   here put the indents and so on which i'm going  to fast forward so you don't have to see it all so to explain this table a bit better over here  we've got the different types of investments that   we expect to make so we're hoping to buy a lemon  crusher an ice machine and a refrigerator as well   that's going to be in 2022 and over here the asset  life we've consulted with the different vendors   and this is what they say we should estimate as  the life of the assets so basically how long till   we have to throw it away because it's no longer  useful to us so we've got the lemon crusher at   three years and then seven years for the other two  so we can go ahead and put the totals over here   so press the alt and arrow the equal sign press  enter ctrl c and just drag that cross control v   over here you'll notice that we actually buy  another lemon crusher in 2025 and that's because   it's only got a three year life and so it's  we're probably going to dispose of it by by   the end of this one so we buy a new one so  now that we know what we're buying we can go   ahead and create a depreciation schedule which is  going to be just down below and if you don't know   what depreciation is it's just a way to allocate  the cost of an asset over its useful life so for   example if you bought say a new truck for the  lemonade stand that costs 50 000 then all of a   sudden in the one year you would have a drop in 50  000 and you'd probably have a huge loss that year   but the next year things would be pretty good and  so that doesn't make much sense and instead what   you do is try to allocate it over its life let's  say the truck has a 10-year lifespan and so for   that you're going to incur the cost on a relative  basis so everything at five five thousand per year   as opposed to just having the 50 000 which looks  like something terrible happened one year and then   things are back to normal which is just a bit odd  if we look into the depreciation table over here   we can see that we have already some existing  equipment say from previous years for example   and on top of that we have what we're going to buy  so for the lemon crusher we need to allocate it   over his three-year life in this case so we'll  go to equals then we'll select the 5000 here   which is the cost press the f4 key such that it's  only locking the d sign and then divide that by   the number of years same thing we want to press  the f4 press it again and one last time press   enter from there if you love if we've locked the  things correctly we should be able to just drag   it down so ctrl c drag it down here and then ctrl  v let's just make sure by selecting this last one   pressing the f2 key like so and you can see that  it's moved on ingly to the where to the   place that it should from there we'll select  all of them so firstly let's do the first one   so ctrl c and then drag it across to control v  you'll notice that because we buy a new one here   and it's only got a lifespan of three  years we actually need to renew it here   so we'll go to equals and then select this  one again press the f4 key f4 key again and   one last time and then divide that by by three  select here and then press the f4 key again   like so and then for these ones we can just drag  them across so control c and just press ctrl v   and we actually need to do this all the way  until the end so control c control v here and   lastly we need to sum everything so for that  we'll just go to alt equals press enter and   just drag that across press ctrl v so now that  we have the total depreciation amortization we   can already link that back to the income statement  so let's go ahead to the income statement control   page up all the way here and then for the first  one go to equals control page on to the last one   and we'll select the 2022 one press enter  from there we should be able to drag it along   but first we actually need to make it a negative  here so press the f2 key and let's put a minus   sign right here up front press enter and  then ctrl c just drag it across ctrl v   and in this case you might notice that we have a  depreciation and amortization if you don't know   the difference basically depreciation has to  do with tangible assets so things that you can   actually touch like say machinery a building  other things like that on the other hand for   amortization it has to do with the intangibles so  things that you can't touch examples of that are   usually like patents copyrights and other things  like that and the last line item that we have to   fill here in the income statement is the interest  expense and for that we're actually gonna start   getting into the balance sheet and then be able  to backtrack from there so go to control page down   so first things first let's work on the formatting  here so for that you see that we have a historical   number so that's going to be an actual as opposed  to an estimate like all of these here so to change   that we'll go to control one and then we'll go  to custom here and now we don't actually want   just the years but we also want the month because  it's going to be the balance sheet it's a snapshot   of a specific time period so for that we'll go  something like mmm press the dash key and then yy   which is going to be for the years and then we'll  put this sign over here and then put on a for the   actual press enter now we've got the december 2021  actual and for all of these we want to change them   to estimates same kind of concept control one key  we'll go to custom and we'll change those so 3ms   press the dash and then we'll go to year year   put the sign here and then we'll go for e for  estimate nice now we have to do all of the   other formatting stuff which we've done before  so i'm just going to fast forward this as well all right so now we have the formatting looking  the way that we like it let's go ahead and start   looking at the historical figures so we can  see what's going on so we had cash of 5000   next to that we have the accounts receivable of  150. if you don't know what accounts receivable   is this is typically when they paid for something  but you still didn't get the cash for example a   typical example here could be with the credit card  companies where there might be a bit of a delay   between when the customer actually pays and when  you get the money in under the fixed assets we've   got the 10 000 over here which as we mentioned is  for the existing equipment if we go control page   down to the fixed assets here you'll find that  we've got all of this existing equipment that   we mentioned earlier which is being depreciated so  control page up again to get over here this is the   accumulated depreciation of it so we can start  putting the totals in for these ones so we'll   go to alt equals here for the first ones and then  alt equals again for these press enter total long   current assets is just the the net fixed assets  press enter and then for the total asset is the   sum of the current plus the non-current so equals  this one the current assets plus the non-current   assets press enter then on the liabilities side  we've got the accounts payable so for example   maybe we bought some lemons from a vendor but we  still don't pay them in full that's why we might   have something like that next to that we have  deferred revenue so this is when we've already   received the revenue from a customer but we still  haven't delivered the product for example maybe   they've they've already booked in advance for a  lemonade and they paid us money for it already   that could be the case for the third revenue so  we'll go alt equals here and press enter for the   other liabilities we have some debt over here  that's mainly because they were a startup and   so we need to borrow some money from the bank to  get started so we'll go equals and then just link   it from here and for the total liabilities it's  a sum of the current and the non-current so this   one plus this one over here press enter next up  under equity we have the common stock which is 300   this can get a bit complicated but for now just  see it as your assets minus your liabilities so   whatever is left in your company after you sell  everything and you pay off for all your debts   and then lastly we've got the retained earnings so  that's whatever you have at the end of the year at   your company so let's get the sum of that too so  alt equals press enter and for total liabilities   plus equity same thing so equals total equity  plus the total liabilities and press enter and   the balance check here is basically going to check  if the assets equal the lap the liabilities plus   the shareholders equity if that's not the case  then we probably made a mistake somewhere so this   is some sort of a health check if you will you can  press the f2 key and that's how you see what there   is currently there moving on from that and we've  got all of the different assumptions over here   under down below here and so firstly we've  got the net revenue assumption as well as   the cogs assumption which are going to help us  calculate all of this stuff here for the accounts   receivable let's make this one dynamic again so  we'll go to equals and just select the accounts   receivable here and put the ampersand quotations  and then we say as a space as a percentage of rev   then close the quotations and then press enter  and we need to do the same thing here with the   accounts payable so we'll go equals and firstly  we'll select the accounts payable here ampere sign   and then we'll go quotations quotations as put the  space here as a percent of rev revenue and then   we'll close the quotations here press enter and  lastly for the deferred revenue this is actually   the same thing so we'll just copy this one and  paste it that should work for us one thing though   for the accounts payable this is usually linked to  the cost of goods sold as opposed to the revenue   so we'll have to change that so go inside it by  pressing the f2 key and we'll just change this to   cogs there we go once this is set up here let's  go ahead and add the net revenue and the cogs   which we can find from the income statement so  we'll go equals control page up the net revenue   is going to be this one here press enter and  then for the cogs we'll go equals page up again   and that's going to be the total cogs here you'll  notice that the formatting is all messed up so   we'll go to alt h fp and then just drag it down  here once we have that ready ctrl shift down and   then ctrl c so i ctrl c and just drag it across  control v one thing though for the cox here we   don't really want it as a negative number just  because we're going to be doing percentages from   there after so we'll go to f2 key and just put  a negative sign in front so it makes it positive   press enter and then just drag it along ctrl  c ctrl v here so let's go ahead and do all of   the different percentages that we need so for  the accounts receivable first we'll go equals   control up all the way to the accounts receivable  and then we'll divide that one by the net revenue   same thing here so accounts payable equals we'll  go select the accounts payable here it is and then   we'll divide that by the cogs in this case and  then for our last one the free revenue equals   here's the third revenue and we'll divide it  by the net revenue here press enter and once we   have these historical figures we can actually go  ahead and forecast them for the future we're just   going to link them to whatever they were in the  past for simplicity so we'll go equals and just   select this one then just press ctrl c shift down  arrow then shift to right and press press ctrl v   and based on these percentages over here you'll  notice that generally the accounts receivable   is lower than the accounts payable now is that a  good thing yes because that basically means that   you're taking a lot longer for you to pay  whatever you need to pay but customers are   paying you fairly quickly and so you're actually  going to have a good cash flow in that sense   and now that we have all of these assumptions we  can go ahead and put them in to the balance sheet   here so for the accounts receivable we'll just go  equals we want to select the 1 and multiply by the   net revenue press enter same thing goes with  the accounts payable so equals control down here   accounts payable is this one and multiply that by  the cogs instead press enter and then same thing   for the last one the deferred revenue control down  arrow select this multiply it by the net revenue   and press enter now we can already get the totals  for these so let's just set up the formula you see   that the formula is already in here so just ctrl  c and then ctrl v to paste it along same thing   over here so ctrl c and then ctrl v to paste it  along and we can paste all these figures as well   so just ctrl v you'll notice that all of these  are sort of unformatted we'll press the ctrl 1 key   and from there we want to go under the numbers tab  and change the decimal places to to zero say press   enter now that's looking a lot cleaner uh these  over here let's also drag these across so ctrl v   now let's look into the non-current assets  that we have over here so for the fixed assets   this is basically going to be whatever fixed  assets that we had in the previous year and add   anything new to that so for that we'll go equals  select this one here and then plus control page   down all the way to the fixed asset side here and  we want to select the copics over here which is   our capital expenditures so whatever we've added  into the balance sheet in this case we press enter   and we can also move that across and then ctrl  v for the accumulated depreciation the concept   is pretty much the same except that it's going  to be negative right so we'll go equals select   whatever we had previously that's been accumulated  and another minus sign control page down page down   again and it's going to be total dna here press  enter then we can drag that across as well ctrl v   then the net fixed assets is going to be just the  sum of those so ctrl c we'll just drag it across   because we already have the formula and same thing  over here same thing with these two so ctrl c   and then ctrl v because we just need to drag  them across and total long current assets here   should have a bold so we'll press the shift  space key to select the row and then ctrl b   there we go now moving on to that area that we  have over here and for this we actually have   different assumptions so let's look at them down  below here here we've got all of the different   information that we're going to need so right now  you can see that we're going to need to borrow   some money in this particular year but anything  before that we're just going to be repaying   whatever we had so remember we took out a loan  over here that you can find of say 10 000 and so   that's basically paying that down and as you can  see once we incur a new loan then obviously the   debt repayment is going to be higher usually the  debt is broken down into short-term and long-term   debt but for this case just to simplify it we just  put it all into one so going back to the balance   sheet item that's the debt here what we want to  do is first go to equals we're going to have the   previous year's debt plus any new debt that we've  incurred which is going to be the net borrowing   here minus any debt repayment or whatever we've  paid back of it so we'll select this one and   then press enter then we can just drag it across  so ctrl c ctrl v here and for this we can just   select these formulas ctrl c and then ctrl v then  for the equity side we've got the common stock   which we'll just assume is the same so go equals  and select this one press enter and then we've   also got the retained earnings so for the retained  earnings it's usually the previous year's balance   plus whatever net income that you made this  current year say so go to page up under the   income statement you'll find the net income over  here at the bottom and then press enter sometimes   you will have dividends which are going to be a  negative right because the money is going to be   going out to say the investors for example but in  our case we don't have that just for simplicity   then for a total equity it's just going to be  the same formula so ctrl c and then ctrl v drag   that across same thing here ctrl c and ctrl  v now moving on to the last assumption that   we have here under the balance sheet which is  the interest payment this is going to be useful   for us under the income statement to fill in this  last item which is the interest expense here so go   to control page down again and what we're going  to do is go to equals it's just going to be the   interest rate times whatever that amount that we  have which is this one over here press enter and   then we'll just drag that across ctrl v then go  to control page up and for the interest expense   we need to be negative so we'll go equals  minus control page down and we'll select it   from down here as the interest payment press enter  ctrl c drag it across control v we finally have   a complete income statement but now if you go to  control page down and check out the balance sheet   you'll notice that it's actually not matching for  all of these years over here which is obviously   not a good sign it should always match or else  we have a mistake but go to escape here and   then you'll notice that we actually don't have  the cash over here so that's probably why it's   currently not matching to find out the cash we're  actually going to create the cash flow statement   which you'll find just going by going to control  page down again so first things first let's format   this one quickly and i'm just going to fast  forward this so you don't have to see it again all right so now that we have it formatted the  way we like it firstly let's go ahead and get the   net income so for this we just got to go to the  income statement under the last line so equals   control page down all the way to income statement  then control down arrow to get the bottom and   we'll just select the net income here and  for the operating activities firstly we've   got the depreciation so for this one we'll  go to equals under fixed assets in this case   go over here and that's a total dna here press  enter and you might wonder why depreciation is   actually being added back here that's because  there's not really a real cash outflow in the   sense that whatever is a transaction you had with  the vendor let's say for a truck you already paid   for on that day and so that's why there's not  really a cash outflow now next up we've got the   change in accounts receivable so for that it goes  to equals we'll go to the balance sheet this is   going to be the current year's accounts receivable  minus the last years so go to control page down   start page up here and we'll select the  current years minus the previous years   press enter and now we actually have to make  this negative so for that we'll just go in   f2 and go to put a minus sign first and then  put it in brackets and get to the very end and   close the brackets here press enter and you might  wonder why an increase in accounts receivable is   actually something that should be negative that's  because when you think about it in terms of cash   the cash inflows are actually going down in that  more and more people owe you money but they still   haven't paid you that's essentially what accounts  receivables are and so for that it's actually a   cash outflow for us so it's a negative and for  the change in accounts payable it's pretty much   the same logic except that this time around  is flipped so we'll go equals control page up   get the accounts payable from here minus the one  just prior because we want the change press enter   and that should give you 20. and then we can just  drag this down ctrl c and ctrl v and to explain   this for the accounts payable side this basically  means that it's taking us longer and longer   to pay the cash say to a vendor for instance and  so we're keeping all that cash that's why it's a   positive thing for us that's why we have a cash  inflow with all this we can sum the operating   cash flows so alt equals press enter then we've  got the investing activities so these are usually   things like property plant and equipment that  kind of stuff that you might invest in in our   case we've got the capex so for that we'll go  to control page down to the fixed assets here   and we want to select the total capex over here so  the control page up go to equals and we want it to   be a negative essentially because we're spending  money to acquire say a specific machinery and   so it's a negative it's a cash outflow for us so  minus control page down and we'll go to the total   capex here press enter and then for the investing  cash flow it's just going to be a link to that so   this is it and then for the financing activities  we've got the debt repayments and the borrowings   both of these line items we can actually find  from the balance sheet so for the first one   for that repayment we'll go equals control page  up and we should be able to find it over here so here it is and in this case we it's actually  going to be a negative and when you think about   it we're essentially using our cache to pay down  debt and so it's a cash outflow for us so we'll   go to f2 key and put the negative sign in front  press enter and for the borrowings it's actually   the opposite when we borrow money we essentially  get cash and so that's good for us it's a cash   inflow so we'll go equals control page up and the  net borrowing should be right here press enter and   now for the financing cash flow which is the sum  of these two so sum and select two press enter and   the net cash flow is going to be the sum of all  of them so we'll go equals the net income plus   the operating cash flow plus the investing cash  flow plus the financing cash flow press enter   once we have that we want to select all of them  and then press ctrl c and just drag across by   pressing the shift and right arrow ctrl v once we  have the net cash flow we can actually go ahead   and fill in the balance sheet where we're missing  the cache if you remember over here we're missing   it and so that's why it's currently giving us  a negative balance on the check here so let's   go ahead and link it so we'll go to equals we  actually need to do the previous years so this   cache over here and add whatever change there  was the cash flow which we figured out in the   statement of cash flows here and then press enter  and from there we'll select all of them so ctrl c   and then shift right arrow ctrl v now if we did  this correctly once we checked the balance in   the balance check here it should be zero that  would mean that we've done everything right   so we'll go ctrl down arrow all the way  here and you can see that they're all   they're all zero sorry so basically  means that we did everything correctly   so creating a dynamic three statement model  like the one we created here is going to be   the foundation for any sort of valuation that you  might want to do on top of that for example it   could be a discounted cash flow a leveraged buyout  a m a model other things like that so if you want   to learn more about valuation you can check out  this video over here or if you're interested in   knowing how to make this a bit more visually  pleasing with some cool excel charts check out   this other video over here that's all for this  one hit the like hit that subscribe and comment   down below it helps out the algorithm that's all  for this one and i'll catch you in the next one