Building a Financial Model in Excel: Speed Test with Dynamic Arrays

[00:00:00][Music] hi everybody and welcome to the financial modeling podcast youtube channel with me today i've got adam west so adam is doing um freelance consulting and financial modeling in australia he's based out of sydney australia and what we're going to do today is quite cool we're going to do a 30-minute speed test to see can we build up a financial model in 30 minutes or less so adam over to you i'm going to set the the clock starting we've got 30 minutes from now let's see if we can build one up quickly perfect it's perfect so i'll share my screen let's start with a blank excel spreadsheet and go from there and let's build our penile our balance sheet and cash flow and get a few challenges from you see how we go from there we're ready ready to rumble excellent let's um i guess let's start and take this away so do you really have to set this up i'll get all my fonts nicely sized so it looks pretty well formatted we'll start this off as our input sheet generally when i sort of build the models i sort of take away the grid lines so i find in terms of just how i've sort of built them in the past aesthetically it looks looks a bit nice build out the headers we'll go inputs for here so what i'm going to do first of all the first time i it's not really a challenge but one thing that i find with every single financial model is the noise in them and when you sort of setting them up is building out those forecast 12-month cash flows for the next 12 months but being able to change your growth rates and be able to switch them off in time so what i'll do is i'll set up just a fake example here of a couple of expense categories so we'll start off without we'll have a revenue up here and we'll come back to that and put some seasonality in but we'll go with some expenses some fixed expenses we'll start off we'll say just high level utilities rent and wages have some we've already put wet power we'll put wages we'll put ram we'll put some utilities and if there's any others we'll put some oversight government taxes in there so generally these um what i find is a lot of these expenses just come out year on year they're pretty consistent pretty average but it's good to have some nice growth rates and because we're building a five-year financial model i'll set this little table up so we can refer back to it we'll have our years here and we'll pull all our use from this so while you're doing this adam i'm going to ask some questions my first question to you is you know a lot of financial modelers kind of like the the cell styles to show you know what what the difference is between an input cell and output cell what cells cannot change etc is that something that we're also going to be looking at in the steady minute speed test or would we leave that till later when we go and format the model we might have a look if we can um we can squeeze in but actually you know now that you sort of mentioned it let's just get it all on the front page generally i'd have a cover sheet here where we would have our styles or our format guide so we'll set it all up so it's good whenever i do my models all set up so we've got our input cells i usually have them a different color so they're just very obvious you can sort of see what's going on make them a nice light blue and then we'll put a nice little border around the outside style different so we'll get our so these will be our input cells and i'll do my lint cells so generally whenever i'm doing this if i'm linking between sheets so i'll do another color just so we can easily sort of trace through the model this is coming from this sheet or if we have our calculations i generally keep these as black and then if we have it inconsistent so it's a good point settle settle these up in the beginning and that way we can just go and refer back to this when we're building out all our cells so what i'm doing here i'm just setting up my growth rates table so i can put all my growth rates in there and uh set up because generally what i'll find is first thing i'll get is i'll get a 12 month budget from the client again we'll just say after dinner so so what sort of financial model are we looking at building here adam i mean is this kind of a typical corporate budget type model is it a valuation model talk us through what sort of model you build busy building yeah so we're gonna today we're gonna build building a three-way for more sort of corporate budgeting purposes but you can also use it for i guess generally i sort of built these in terms of doing a lot of ecm transactions as well so putting them in place but whenever the investors need to jump in building a robust little model that they can sort of sit there play with the drivers and put their own assumptions into the business to sort of see what the valuation is at the end of the day so i'll set it up because generally what i find is that the key things to pull out is what the revenue lines are what their cost of goods sold is and then we'll have a look at pulling together what our forecasts are for a lot of the immaterial items but i found in the past are arrived at the immaterial items and what i'd be spending my time on is getting them all set up so we've got the right sequencing of the cash flows and we've got the right growth rates in but we're not then then i'm finding my own time is not actually being able to get the time spent on um i mean doing the important part so driving out what the key drivers of the model are driving out what the revenue drivers are picking up what seasonality is picking up how the key i guess more the key expenses the bigger bigger items are driven by sort of revenue if we're putting in more staff based on if we're going to grow it so building out the more stringent like scheduling parts of the model and i found it's good good ways just set this up like a forecasting bit of tool and that way you can get it for the next five years and you can make a lot of the changes so that's what i sort of arrived at and i was using um i just found a i found it quick and easy to use the dynamic array so the ones i'm going to use today in terms of doing this is i'm going to be looking at sort of using the filter function so the filter not sure if you've sort of had a lot of a lot of chance to play around with the new dynamic arrays but using filter sort and unique i found it sort of revolutionized or at least cut down huge amounts of my time when i was going through the data before and making sure i can get all my data lines up and pull out the right data so i love that i think a lot of people watching this video the the array formulas in xl are relatively new and a lot of people might not have built financial models using those array formulas so i think that's awesome i'm really looking forward to seeing how you're going to incorporate those into the model and how you work them in yeah and so what i found is actually the way i used to think about problems and where you used to think about modeling problems it's kind of changed a lot as well so i went back to the old school when you sat in year 10 math and you were looking at the matrices and arrays thinking about things in the arrays you think about a lot of the problems in terms of the matrix elements of them and it makes it a lot better when you're sort of when you're building more operational aspects than business so you're building up you know cost of goods schedules schedules and physicals and whatnot you can do a lot with that in terms of if you've got a fixed capacity of what you're transforming items in then when you're doing the lookups i use a lot of the time i use think about them in terms of the matrixes and it just makes the problems a lot quicker and easier to solve so whatever what i've done here is i'll set up so i've set up a very basic model here but i've set up our expenses i'll build out a bit of our revenue assumptions now so we'll sort of say price seasonality and i'm going to use the same similar format to what i've done here but the difference is i guess i'm using this format it's a lot easier particularly when building these models if you want to go in and you want to change particular quantities per month rather than just having a fixed having a fixed sort of yearly amount being able to go in and sort of sit there and someone can play with the monthly amounts and it becomes a lot more useful for operational purposes as well so you want to when you want to go back into your model and you want to start using it to compare your budgets and actuals and then we might do and i think that's gonna highlight for people watching the video what you did there i mean you you went and you you put in that um 30th of june 20 you linked it though to yourself and ether for expenses then you clearly you know you've clearly applied the format there of green text to show anybody working with the model that that over there is a link links to another cell so i think that that's some great modeling practice there just to highlight that to the viewers yeah yeah so generally and it's good for when someone needs to go through and actually audit this model figure out what's going on if they can sit there and say okay this one is actually pulling from another cell we can we can go through and more so this is a bit inconsistent as well so actually i should have really highlighted it red but it's really good with it when you're doing it as an inconsistent formula because then when someone's sitting there and going through and checking it it's almost like i'm going to have a look at this okay that's a formula but this one's actually different so you want to go through and make sure logic is correct for that and particularly if you're changing something in this cell it just allows anyone that has to pick this up and at the end of the day a lot a lot easier of a job to sit there and figure out what i've done as a modeler and then what i what i generally do is make it a bit cleaner because i'll clean up all the mapping cells and make them a light gray just so they blend into the background a bit so you know they're there but you're not sort of you're not your eyes aren't drawn towards them now what i'll do here i'll do a bit of a dodgy but we can play around with growth rates in a little bit i'll use this classic cpi growth right now for our expenses and our revenues a neat little trick i'm going to do here as you can see when i'm bashing around the keyboard i sort of find i do it a lot quicker if i'm using nothing but the keyboard not my mouse one of the key issues is when you're bashing around and you hit the wrong cell you end up in xfc so what i always do when i set up my format i'll just go and hide these cells and that way when i'm bouncing around it stops me and my sheet isn't jumping around every which way love that and i think that's a great modeling tip something i do as well just hide all the columns to the right that you're not using then if you do by mistake go control to the right and you go all the way to the right you certainly aren't all the way at the end you're you're still within your your nice work space over there so i think that's a great tip yeah yeah and so then even if you need to put any other columns in the back you never have to go and unhide them you can just add in a couple of extra columns there and then generally do that at the bottom so what i've set up here i've set up the base of our model now i'll ask you if you've got any you want to throw in any challenges here of things that i might have missed for our hypothetical business so let's sit here and let's say we've just got a hypothetical retail business where we've just got some rents and utilities some wages and you know some government taxes in there and then we've got quantities and we've got seasonality involved with that and then i'll say we've got a fixed percentage so i won't go won't go into depth here and build us out an inventory schedule and whatnot but i'll just say we our cost of goods are a fixed margin if there's any challenges you can sort of think of in in this business to throw in and we need to model there otherwise we'll go away and we'll build our three-way aspects of this and i can sort of talk about as we go through how we'll sort of convert this into a model and maybe it might be easier rather than doing different sheets i might do it all on the same sheet so we can refer back to what cells i'm using and whatnot so let's do that obviously i think a lot of people watching would know that you know correct generally correct or accepted modeling practice would separate out your input sheet your calculation sheet in your output sheet however for this demonstration purpose let's include them all in one sheet and then obviously um you know we could always go and separate it out into different worksheets at a later point in time yeah exactly and it's just going to be a lot easier for us to explain what's going on between jumping jumping between sheets and whatnot and generally um what i'll do when i first set up these models is i'll have um i'll set up all my links so i have a contents page here and i'll link back to a content so you can easily bounce around whatever sheets you're looking at as well which is always what i found a sort of healthy little line trick so what i've done is i've set up the basis of our p now and as we move through we can throw in some tips and tricks when we get to our balance sheet and whatnot but for this one let's just have a look at it we'll have a look at bimonthly i guess the thing what we'll do here is because we'll keep it on the same sheet i'll build it out build out our penile and we'll just do it on a monthly basis and we'll do it by month for the next five years inconsistent um so inconsistent formulas a slight typo there but we can always go and fix typo as a later stage so let's get going into the p l yeah let's jump in so first things first let's set up updates now it's kind of i guess i i probably don't do what the standard is how we'll sit there and we'll do um here as i'll chuck it out here but i kind of don't do i guess the standard where you do your your opening date and your closing date and the reason why i do that is i've just started a bit neater and cleaner and then i sort of if i'm ever looking at something that's in between a month i'll just based on the end of month and utilize the sort of date formulas in excel which are quite useful and then just reformatting and a neat little trick i guess for any of your viewers there how i get this neat little formatting and what we might actually do build our model here so what uh what i do in terms of the formatting of these cells is um whenever you've got a date if you want to change a date so if you pick 37 20 and you want to change it to that nice 30th of june it's just control shift 3 and then just makes a really nice little date formula so that's a handy little quick tip i'll pop in the end and then one thing we haven't done up here financial and just so if we were to go and do summary financials based on here we know we're summarizing the correct years so we'll say we've got a 30 june year and i'll just go if that equals month what our financial year end is up here anchor this cell then we'll just say whatever our year is plus one and if not we'll say it's that year so we can go through so i anchored the wrong cell there so we're actually going to look at if this month anchor this and let's go back to what our financial year end is and this across so we can get all our years in place so just for anybody that's not aware a couple of kind of keyboard shortcuts are using there and i think 200 correct right fastest way to build financial models fastest way to use exercise kind of throw your mouse away and use your keyboard just you know copying the formulas to the right control um control um shift r um you know to select the cells and then ctrl r to to get the formulas across so a couple of keyboard shortcuts using there and i think really useful stuff if you want to build a model quickly and essentially if we time limiting it like we are to be 30 minutes yeah when we sort of limited it ourselves so i guess that's the thing and when you set up your spreadsheet upper right and you're not bouncing off of it and xfd here it's a lot quicker to just go through and build your model and then we can get the right number of cells here so we can set it up so we can get the rest of the financial year there we go so we've got ourselves five financial years last thing we'll do is we'll just earn this one and we'll start out beginning one all day to the first 7 20 okay that's where i um oh yeah so we've got our p now ready to go now so let's start off with the first item go with our revenue calculations i might just draw out how we'll calculate our revenue generally i've found um depending who you're building for as well if you've got a client that's pretty excel savvy you can just build it all into one formula but it's always it's always good in best practice sort of fun to set out what we're doing for our calculations so what we might do is because our setup i'll show the first of the formula because what i've done here is i've set up a table so we can actually change what our pricing is per month we can change what our seasonality is so i'll put a seasonality percentage in there and we can also change what our quantity is for the revenue without the growth rate seasonality so that's not a thing but what we'll do here is i'm going to filter and use the growth rates that are used on the side so instead of having straight line growth i'm just going to pull down the basics of calculating growth rate one plus the growth rate in times altogether using product function and use the filter to be able to pull out what you specifically we're looking at so that way we can actually build um in terms of model we can build a pretty robust model even though it's just a forecast we can sit there and we can change our growth rates we can even take out an expense for a year or take it out for the rest of time if we need to based on a certain year in a very very easy way so in terms of our revenue i'll set up a price here and we'll say you know our trinkets would sell for ten dollars each and sell it across and we'll say because it's christmas time we can sell them for 15 dollars so we'll have a we'll have a different one there and then the other thing i'll do here is i'll set this up so that this is our first year and then this is going to be our growth rates to figure out the rest of the year and what i'm going to do here is i'm going to build the same seasonality into the entire model and how i'm going to do this is i'm going to x lookup so it's a great formula there x look up one of the newest formulas in excel um and it's a it's a really fantastic formula um just do for anybody's not aware of you know that it hadn't seen that x lookup formula before it's a really great formula it is it's great it saves a lot of time it's got some good functionality in there as well in terms of when you're looking at wild card matches and when you're looking at particularly if you want to search last first thing that you could sort of do similar things in the past and it's a lot quicker i sort of found i was always using index matches in the past just using typing in x lookup it saves a whole lot of time so what i'm going to end 100 and i think just to highlight to uh the the people watching the video as well if you've ever used an index match to get around the fact that vlookup can only look up to the right xbookup solves your problems for you yeah exactly x lookup will look left to right and right left and you're not going to sit there and have to have um offset formulas and whatnot and have to have address formulas to figure out what actual row you want to look up if you want to look up multi-multi-set data so what i'm going to do for the first bit of our formula here is i'm looking up what month we're in we're going to have look then it's going to pull what our price is for the month and the second part of the pricing is we're going to look at what the growth rate is so what i do for this is i'm going to use the product function and then i'm going to filter i'm going to go one plus using the new direct dynamic arrays one plus our filter and we're going to filter our array here and i'll switch these labels here in a minute and we'll take this as our price but i'm going to filter out growth rates right here and then i'm going to include it if this is less than or equal to what our current year is so by doing this we're actually only going to pick up it's going to use the new dynamic arrays and it's going to pick up what we're picking up so for this one here if we're looking at sort of 2022 we're going to pick up 1.8 for 1.84 then with that array it's going to add one plus to each of the numbers there and then we're going to be able to look at the product for that so i'll give it a quick little demonstration uh and there is no reason why we get the arrows because we don't have a zero um 2020 in there so i'll just throw a little if error in there so what this does and i'll just pull this formula out so i can sort of show to your viewers what we're doing here is we'll go through and we'll have a look at 2021 so what we want to be doing and what i want to pick up just here of these cells away what we're doing is it's going to pick up one 1.084 and then with that product function as i drag this across so we get to 2022 what it's going to look at is it's going to look at 1.08 oh one 1.0 1.84 and then we're going to times it together so we can get what the growth rate is going forward so then if i just look at that times that we've got 1.37 and what that allows me to do is then depending on what whatever products we're looking at so these labels here but whatever product we're looking at or whatever item we're looking at we can actually dynamically just adjust our growth rate so i could go five percent this year six percent this year and what this function is going to do is going to be able to pick up that um little intricacy in there so then we can grow out of cash flows while keeping the same sort of cash flow timeline as well or while we're keeping the same underlying timeline so we can grow it and we can keep the timeline it's just a sort of neat formula or a neat way of doing this i really like that that's um yeah it's a nice way of just building up a little bit of an index over there so you've got the price index going forward using the product and filter formulas that they're definitely um you know they're they're pretty new formulas so not everybody has necessarily played with them before played with them and they're really useful because what i found by doing these arrays is allows you to do a lot of your math that you're doing um to an entire array so if i sit there and i'm just picking up a filter for instance i'm filtering out just what it whatever it is for 2021 if i'm looking at 2022 it's looking at picking up both those growth rates and then using the new dynamic arrays you can actually basically build that into a function so i can go and plus one to both mine so it just allows you to play around with the arrays a lot quicker and a lot easier and a lot it saves a lot of time rather than setting out in the past that i have to set out multiple data tables doing my calculations being able to truncating that all into one formula saves a lot of time and then even if you're using data where you're using horizontal arrays and you're turning them to vertical using transpose it saves a lot of time you can start it's just that's what i'm sort of getting at the site you can start thinking about things in terms of the matrices element so then for here let's look at our price let's look at our quantity now i'm going to just drag this formula across down the page so i'm going to unlock all my cells in terms of these and this is just going to allow me to adopt the formula going down the page as well so now if we look at what our quantity is we're just going to have a zero in here but let's just chuck in for argument's sake 10 10 10 the other thing we'll fix up here as i put zero free referral but we'll just put it as one and then build that across so that way now we've got our price and our quantity for our revenue so we can just total our revenue sum this multiply rather yeah multiply had my mind said that there were two revenue items i would do that as well and there we go so we've got our revenue lines open and it's pretty dynamic now because i mean when we when we sit there and go back and play not only have we picked up our sort of seasonal aspects of it which will actually add in for our quantity we're adding a bit of seasonality there as well but we've also added in we can mess with the growth rates here so we can have different growth rates in different years or say we just want to cut out the price and but we'll do this for these expenses down here if we want to cut it out in a certain year we'll just zero out the growth rates or we'll just do a negative 100 and that's going to cut out the expensive going forward so we can cut out our costs as well quite easily hi everyone and welcome back adam and i to take a brief hiatus a couple of little family emergencies along the way but we're back now jumping straight into the model so just to refresh your memories what we're doing here is we're building a model um and this is sort of a speed challenge so adam has said you know he thinks he can build a model using all the latest excel functionality and of course there's some really cool excel formulas thrown in here as well in sort of 40 minutes or less and that's what we're busy doing and i think we're on track for time right adam um do you want to give us a quick recap of where we are and and where we got to yeah definitely so we've sort of set up our model we put all our input drivers in and we started building out our p l and the next bits is really finishing the p l build up build out the balance sheet build out the cash flow statement and then titles kevin built the summary and i think we can i think we can make it in 40 minutes we'll have to really bring out the new formulas touch the dynamic arrays here but i think we can do it awesome so so let's go and just to remind everybody as as well i mean you've applied some nice unconditional formatting here so all those gray cells are kind of your input cells over here and then and then obviously um and then the these cells but you are on the pnl these are the sort of calculation formulas yeah so we're running these all the calculations and just a bit of a recap what we actually did was we set up our tables last time so what we're effectively doing is we're doing a lookup we're pulling out what our price is based on what the month is and then we're applying whatever our growth factor is so we can dynamically adjust our growth factor you're going out five years so you don't have to have a stable you know three percent for the rest of your model you can actually dynamically adjust that if you're looking at a business that sort of has that um exponential growth or even a sort of flatlining growth um and even if you need to cut off costs so we've set that up the next we'll do is we'll set that up for our expenses i think where we're at last time is we um we've set up our price at quantity and now i'm moving on to the expenses here so we might continue using the same methodology there build out the expenses now what i'm going to do here is i'm just going to show off some of the new functionality in here because this is really useful i've started using this a lot of models that i'll be building is when you're getting all your account codes you run them off so you're pulling off your chart accounts or whatnot you pull your unique and so what this is doing is this is looking up the array and just pulling out the unique um unique characters out of here or unique words or whatnot and that way if you're going through and particularly if you're running lookups and you're pulling data into your spreadsheet you're might have to go through individually set up all your chart of accounts and think oh wait we've got two salaries we've got two wages very simple way and you can even then apply a sort function to that so we can alphabetize our um we can just go ahead and outfit as far as our expenses here so now what i'm going to do i'm going to do a little sneaky because i've already sort of set up our formula before so while while you're doing that i'll just comment um and don't stop while i explain background but yeah but before for anybody watching that used to the older array formulas you have to kind of press ctrl shift enter and then squiggly brackets appear around your formula and just hold the lava now at beautiful the ray formula is now you just press enter and it automatically fills the other cells with the outputs so so so really cool that excel have now updated that and of course incredibly useful what i'm going to do here matthew is i'm just going to put in some rand formulas so um we're just getting a random data set in here rather than going through and building out there we go we've got a bit of a data set that we can go we might pull out some of these expenses and just say this one here we'll only do yearly this one here we'll do you know every quarter and just give us a nice sort of um cash flow low sprawler i haven't set up my square brackets my nice formula is there but that's all right we'll run with it and there we go so we've just quickly built out our p now and it's a bit of a sense check let's go through and have a look so we've got our government tax the other thing we might want to do here is because we've actually ordered this based on that we'll go through and we'll filter so when we do the x lookup or just make this a bit quicker we won't go through and sort our data here which is unique but what i'd do if we weren't so tired pressed i set this up so i'd set a filter up in this cell here so we look at the entire so rather than doing an x match here i'd opt to sort of do a filter and filter out the entire array based on what we're looking at here and then you just simply set up a filter formula on the um the overall look up there so we've got our lookup based on the months here set the filter up so initially would filter this data set and would include if um just set this one up and then i'd include if that is equal to that and what that's going to do is that's going to pull out the array there and so i'd apply that array then bringing that into there so we could actually even just put that in here just give you a bit of demonstration here so where we've got this array here i replace that with a filter formula there and what's that's done is looked up our table over here and comb all my points in here [Music] and anchor that there and then that's going to go through and dynamically adjust so this way what we can actually do here is now we can apply our sort filter that we originally had bring that in and as you can see it just dynamically adjusts that so if we look here government taxes we've got nine seven five five five i'll just do a quick sense check pull out for other expenses these guys here for a year oh but what it's done there we've got our growth in here [Music] so there we go so we've got the exact exact matches there we've went through and filtered and we've dynamically adjusted there so that's pretty useful particularly if you're pulling in from zero or a chart of account or whatnot and you've got multiple entities and you're pulling in multiple sort of types of accounts that all have the same names and salaries and you just want to consolidate into one neat line pull it through you pull through your monthly cash flow then then you can apply your growth formulas here to grow it at different dynamic amounts and you know nine nine point two we go through the model and just quick sense check it go right to the back and we have where we started our model 9.7 so we've constantly grown that from july so we set that up let's put our ebitda in about this i'll leave dna if we get time what i'll do is i'll show you how i quickly set up a nice little dna schedule so we can implement putting in um putting in what the assets we currently have and then building out a bit of a sort of schedule depending if we get the time there put our ebit and finally we'll take our tax off this to get to our mpbt by our interest we might throw in a bit of debt into the model as well and then so now that we've got our end hat set up we'll go back and if we get time just lock it down don't forget to lock down that formula for tax oh yeah there we go we nearly got tripped up there pulling in any attacks into the model cool so we've set that up let's move down and get our balance sheet set up and then pull through our cash load matthew have you got any challenges you're looking to throw into the mix here or wait till the end uh you know i think that we can wait for the end and maybe have a couple of follow-up follow-up challenge um sections and maybe even put a lot of audience to see what else they would like us to to build in and how quickly we can do it but i think i think this is a great showcase of the new dynamic array formulas i think it's uh you know you come you're really you're really illustrating the power of the more x look up sort unique uh and i think i think the ability to input those into a financial model is not always that clear and then certainly this video is doing that and people watching and i think the thing i really find with them as well the speed so rather than previously if i'm looking up something i'd either be sitting there doing an x-map sorry in an index match match if i'm looking up a table now i'm using just an x lookup which is just one formula or even what i've found useful as well thinking about problems and i did it in another video was um when you want to look up multiple criteria so if you're sitting there and say you've got an array of data here we've got to we'll say this is the season if it's seven it's winter summer then you've got your dates and then obviously you've got two others what i ended up what i used to do is you'd build out a sun product which is obviously you probably know when you build some products into your sheets they drain all their all the power out of there you're getting massive spreadsheets then and it's taking ages to calculate what i ended up and started doing is building in filters filters and um filters and x lookups in there cut down and cut down quite a lot of the actual calculation time and made that made the actual spreadsheet a lot quicker as well what i found and we didn't have such a such a large file size doing that either um so just continue this we'll just go i'm just going to build out our balance sheet here we'll just put a very basic balance sheet with your accounts receivable tax payable and any fixed assets that we put in there or split it between current and non-current assets just for time being and get some liabilities in there generally generally in australia i'm not sure how it is in south africa if you've got sales tax there we will have um goods and services tax so one thing to be mindful particularly in a lot of the models i'll be building is you've got a 10 on your um revenue online and you've got a 10 cent on your expenses line that every quarter you've got to pay out to the government so keeping track of that and actually has depending on the type of business you got and depending on the life cycle you've got quite a big swing in there for the cash yeah definitely in in especially we see this in infrastructure finance models where people can't take into account that um on on their on purchases and sales and obviously um you know companies would pay out uh that on asset or failure tax and have to be able to claim that back from government but that doesn't that does mean a big working capital swing in the models and um you'll even have certain banks who will actually fund to tax on that facility specifically to pay for that on goods knowing that it's going to be a short-term facility once government refunds for that that facility can be repaid yeah yeah well exactly that and the other one the other one big one being iron d tax so what i might do in here i'll throw in a bit of curveball and i'll put an r d tax payable in there um and we have in australia so you have a lot of um banks and financial institutions actually fund the r d tax incentive because you'll get paid that out a year for all your expenses that you spend that full within that r d tax bracket is about 43.5 cents on the dollar you'll get back so quite a lot of early stage companies over here will use this incentive to obviously fund their operations and um i mean shareholders dilution in the early stages isn't isn't as diluted as otherwise would be so so while i mean while you said you know early stage companies and startups i think that's great that you actually highlight that because i think a lot of um you know startups and early stage companies don't build financial models for their businesses because they think it's so complicated but what you're showing here is that actually we can build one pretty quickly we can build one pretty easily quickly and less than an hour you can actually build a financial model for your business obviously the hard bit is is and and this is the part where businesses have to do a lot of thought is obviously the assumptions that go into their financial models but in terms of building out their financial model i think this video shows that a lot of startups and small businesses and small business owners can do one pretty quickly even people thinking about starting up a business can build them pretty quickly to understand the viability and the sustainability of their business yeah and even um we might just make this model that was sort of building on this video if anyone wants to download it we might just make it free for someone to sort of pick up along the way and just shows that once you set up your basic cash flow it's not very hard to actually set up a cash flow that runs through and then you can start thinking about you know if we're going to build out more assumptions around our price and our quantity our revenues or if anything here building out wages schedule but it's not particularly difficult to actually just sit there think about it logically build it out and then someone else pick it up and go [Music] you know what i've done hey what i've done i made a little boo-boo here so we're running our model starting over here and i've put all statement these borders okay well let's set this up let's go so we're gonna set up our cash flow statement first as the first line item in that balance sheet and then we're going to fill in all the rest and then set up our check formulas in here so we're just running our opening cache we're just going to set up our sub formulas and as our girls sort of talk through how we're going to calculate each of our items along the way we're going to net interest and then we'll go payment cash flow from our financing activities so we split our cash flow as you probably see just the usual cash flow statement set out we've got our finance activities we've got our operating activities and then we'll have our investing activities down here here sets up so obviously i mean most of the work comes in instead of a model finding all the inputs and we're still taking that into account yeah so we're i guess we'd be heading on for the 40 minutes here but i mean you've got a significant base of the financial model already set up and of course doing the formulas which you're going to do now is simply a matter of pulling from the inputs and escalating over time exactly that i think the key the key thing if we can leave anyone with anything is just running running your chest through your model as well so the first check you're just going to want to run is if that equals being out focused with total equity equals unknown assets and things like this you'd be surprised you pick up models and they just haven't got these simple checks running through or they do but they're just not balancing and like just a key fundamental thing if you bring people back to the accounting principles you know fundamentally what you've set up all the calculations are working and then you've got to go back through your inputs and go back through the actual mechanisms to make sure you've picked up reality but as a first front making sure you've got your three-way model of balancing as i think is uh one of the key things and what i what i'll show everyone today as well actually with doing these checks it's just setting up with conditional formatting so you've got your true here a lot of the time if i just go up here and i'll go format cells that are equal to true and the reason why i like to do true or generally zero one is it's very easy for excel pick up and then we're just going to fill it with a nice green color and we'll do the same with the font and then we've got a nice little green line and we'll do the same if we get a false and this way i'll just show you how i set up my cheese um sheet checks as well from this because it's very easy then doing um just with the conditional formatting so you know what if we run that out for the duration our model generally at the start of the model i'll freeze all the top panes um and just run this sheet check in the master check up here so tying back to um generally i'll have a cover sheet that's set up very easy to set up and you just have your and formula just copy our little formatting that we've got here using the formatting painting tool saves my life but then if we go through and say for instance here we'll put five equity then the whole sheet's gonna be out and we know we've got a problem in our balance sheet and then we can set up the same so generally they're not set up for working capital where i'm calculating particularly for a debt schedule where i'm calculating the net cash in itself and the debt schedule to be able to arrive at what we're drawing down or what we're repaying in the model i'll set up another check to make sure that the cash and the debt schedule where you keep the cash in our cash flow statement so that we whenever we're sort of calculating the same line items throughout the model we're just making sure that they're equal throughout the model we're not having different calculations throughout there um i'll just go through and just finish finishing off all the freeway elements but this is this is what i find um sort of this best practice so that's sort of fun putting in the model as well now one thing let's put up here but we'll put up here give our tax month save for instance attacks will get paid out of on the six months every year just run through here tax payable if [Applause] plus that i equals that plus whatever attacks the entire month so this model at this point in time is looking pretty comprehensive um obviously what would you say is it is the first step for somebody who's running their own business starting their own business maybe doesn't have a financial model to get going in terms of building a financial model would you say to download a template like this like you said we're going to make available to people or is the first step to rather go and see like what are the actual assumptions you know what are they going to input into the financial model how would you recommend a small business owner start building their own financial model for their business i would recommend not focusing so much on the p l and cash flow oh sorry the balance sheet in the cash flow means focus on the p l that's going to drive ultimately the cash then be downloading template or trying to build out this sort of obviously the three-way elements and cash statements but at least by because i guess being a small business owner if you're sitting there and thinking about what's driving the profit the revenue in your business you start unpicking the drivers and that's going to i guess help you look at a new business in a different lens looking in that strategy lens and stuff here and thinking you know my revenue is actually driven by my staff now do i need to think of another way to you know approach this because that's like obviously a lot of operational leverage in my business now maybe i'll look at having casual stuff so you start by unpicking your drivers and your business you can start unpicking i guess your strategy and your pain points in the business as well and then going through that that's um i guess my personal view on how i'd sort of recommend tackling it but yes um yeah if you have any other thoughts as well matthew yeah great i think while you wrap up the model i i i i think you're right and i think as a small business only thing it's also not to be too caught up in their accounting but to focus on cash because at the end of the day you can have profit sitting in your base profit sitting on your financial model but if that money is not selling in your bank account you're not going to be able to pay pay your staff and pay supplies so focus on your cash and i mean like you adam i would say even for small business owner don't even worry about a balance sheet at all for the moment just focus on on cash flow into your business like what is your cash flow actually look like um and and that will also help you identify funding shortfalls and the need for broken capital and and of course um for small business cash is king yeah well exactly you sort of wrap up because i guess getting into these elements here sorry i'm just trying to figure out why i've got a chat formula in here it looks like our numbers are spot on there um but not to worry but yeah i think i think that's probably the key takeaway in all of this as well is cash is king and focusing on your cash flow rather than the accrual elements getting into the level of complexity because i guess the complexity comes when you're starting to evaluate new projects and you start wanting to look at the whole whole picture but as a start getting a base cash flow forecast going and then building on that as you go through time and setting up the elements as you go but also keeping the back of your mind the right way to structure it that someone else can come in and pick it up because the worst thing is if you're going to go build it build a forecast you've got keyman risk there that you know nobody else is going to be able to decode what's going on it's going to take them a longer time so keeping in the back in mind someone's going to have to pick it up setting out what your inputs your assumptions are setting you up so it's a logical way to follow us i think is the best best way to approach it there absolutely so so so so while you're wrapping up the model and and bringing so close i mean on the unfortunately you've got three checks there and this is the beauty of excel the video financial modeling actually where it looks like your numbers are correct and it might be a few a cent that's out or something like that yeah or even a millisecond um microcenter but that's the that's the beauty yeah and there we can see that it starts in the in the act in the absolute um in the absolute decimals that starts not balancing and this is fine i don't think any financial model would be ashamed of of this is and and i can see you doing it there is putting in a round formula over here because obviously as in finance anyway we're not counting the decimals of sense um if your model balances to the center i think that that's pretty good enough and then we can see them on our balances balances and now we can even throw in a few like little um tricky elements let's throw in a bit of working capital as well obviously one of the key things for any businesses calculating absolutely yeah your networking capital and let's say for this business that's about days sales outstanding and i think i think this is key and i think this is a great element to add in here to end of the model is the working capital because as any small business the probably one of the first financial facilities you're going to require as a working capital facility well exactly it doesn't cost you anything and the way you i guess it better manage sort of working capital facility if you can reduce your sales terms so let's say for this business let's go 15 days and for this one let's go 45 days we pay our suppliers and i guess the other thing i should have done that was best practice here matthew is setting up what our unit counts are so what are the units we're looking at for each of these if you sort of just go through these and we'll put them in days and now we'll run over here a little calculation just to put this in the model we'll go one divided by times 365.

[00:59:31]around 365 minus one to get our percentage i think i think the late night hours is getting to me now matthew uh yeah we can we can we can cut this bit out of the video yeah so let's just run it i've got it set up now so we'll run so we're running four percent on our revenue working capital facility and then let's go i'm wouldn't even put in hey everyone here i'll just go 12 on all our expenses and maybe we can do a sensitivity to show um show a business owner i guess the the importance of working capital on your business so what i've done i pulled in the working capital here now let's have a look what the cash flow is so just running on a 15 day um sales outstanding if we have a look at the end of our year so we get a 30 june our cash balance is 7.23 say we just remove and we take everything in cash and we're just paying our vendors all the way out 727 so instantly the business is sort of pushing pushing out what they're paying their vendors we've got growing expenses but we return retaining all our cash so we've got the importance of working capital in there and just one one for i guess small business and quite easy to set up now that you've set the model up what i'm going to run down here very quickly is just an r d calculation because i think um this is particularly important for the businesses in the startup space is being able to run a little sensitivity on the side of your model so after you've built your model you can then build i guess additional bits generally i'd say build another column in there but we'll just run it off the side here what we're going to do is we're going to set up a few rows here um let's bring the four key bits what would this orange calculation look at so this is going to look up the expenditure so what we're going to do is we're going to apply basically what we're going to do is we're going to look at what the expenditure is have a look at how much of it can be applied to your ird tax and then apply r d rebate to that to sit there and yeah see what cash can we expect to get back in the business um by going out and spending i guess spending the expenditure here so generally generally in australia what we've got is we've got 43.5 cents that you can get on applicable expenditure so say i'm running um you know a tech startup here and my software other expenses here is completely software so i get 100 of that back and then wages i'm also good i can get 100 of this back what we're going to do and now i'll set up a formula down here that will actually go through and calculate and we'll say the r d manages this and then we'll have a payment date here so we'll go payment day or payment month here and basically what it's going to do it's going to have a look through the model and have a look what the expenditure was during the last financial year and then pay you back that cash rebate at the end so for that month hey now what i'm going to build out down here just build an r d tax um let's build in our cash flow so what we're going to have is the r d cap that will run through and we'll apply we'll just go into some product we'll lock that like that and then times this by what our r d percentage is up here so this is just going to go through and look and if we've got 100 there what we're looking at is we're just going to go nine times whatever our r d percentage is there to arrive at 3.195 and that's going to tell the business how much cash you can expect to get out and then obviously because we're in expense we want to reverse that because now it's an asset to the business and then much like much like our tax so this is going to build up through time as an r d item it's a bit of um it's an asset to the business because i guess you're going to recover it so you almost what you do and what i generally do when i'm running up these models is we'll have another line in our revenue item and it'll be our r d rebate and generally you just run it so the year whenever you get it paid out so our payment day will be and i'm just going to use some products or run this so i'm going to say i'm going to run it and i'm going to times it by what i'm doing here is i'm looking at the year and i'm going to go if that we're just going to run the entire array here equals whatever the year is we're currently looking at minus one so we're going to look at what last year was we're going to pull out what the applicable expenditure was during that year and we're just going to put an if statement here saying if the month is equals to this we're just going to go through and run through so what i've done here matthew is i've set up a little formula just to run what this is going to do and i'll just lock down all our cells so we're not jumping around when we go and pull it um it's going to look through i just want to make sure i get it all picked up correctly so now i should be able to run this to this year should we have a look at that and then i should pull so just have a look but what i'm what i'm effectively just doing here is i'm setting up so it's going to pull what our r d rebate is it's going to sum up or just do it a lot quicker with students so i generally do it as some products i'll run the entire formula at one we'll just go with some if criteria oh and see we've run it there so what it what it's going through and doing that minus one this guy and then we're going to sign up what we've calculated already down here so do i run it as some product because it's a lot easier just to run the whole thing through there hmm what we've done is we've um pulled out and there we go so now we've got it set up running right so what it's doing here is it's going to pull out it's going to look at so we've already applied that 43 and a half percent to whatever we put in here is 100 and i'll show you why i put it as percentages in here rather than sort of a toggle on and off switch but now it will go through and have a look at how much r d we're actually expecting to get back put that as a revenue item and now we're going to get that back through the model when it calculates out but i've set this up because generally what you sort of find in a lot of businesses is they'll go through and they'll say yeah the only 50 percent send my wages are applicable because i've got a ceo and you know i've got a lawyer and a general counsel they're taking care of the corporate side of the business and raising the capital we need so we actually only have 50 of our staff going through and running and so that's going to apply that percentage and then kick that back out there so then we've got our r t tax line so i guess these these are very easy things to set up and then build in bolt onto your model once you get there and you start thinking about the intricate things that you want to pull out for your cash flow items and then the other way to actually run this is i would set this up below the line and pull it through as a separate cash item down here just so you can see the impact of the r d um r d tax calculation on there fantastic adam thank you so much for for showing us this um build how to build a model in such a short space of time i'll we'll post the final time in the video and see where we got you but i think this is certainly a lot more of a comprehensive model than what we ended up intending to build so it was a brilliant um i think a brilliant display on how you can build a model pretty quickly and a great display on how you can build a model um using some of these new um excel uh dynamic array formulas so thank you very much for your time yeah and um and and and if you're watching post your comments on what other challenges you would like adam to do in the chat and then we'll do that in the next video yeah yeah put on put any any excel problem you face and we'll get on and we'll try and solve it 20 minutes or less with your host matthew matthew burnett you