Excel Financial Modeling Tutorial (+ free download)

hey guys what's up uh so our financial model template has become a pretty big reference for a lot of companies so far the free template has been downloaded over 10 000 times a lot of that is because we've talked about it in our youtube videos a little bit because our people are starting to find it through google and and google search the point is our financial model template has become quite popular uh if you're watching this maybe it is because you've already downloaded it but anyway the idea for this video is to kind of go through the whole financial model itself to understand you know all the settings pretty much all the numbers that we've added in there and all the functions that the financial model can do for you so this video is going to focus on our kind of general template uh we're going to go through every single step of what a financial model usually has we also have templates that are specific that are kind of pre-built to a specific type of business so for example a subscription business or an ad based business or a marketplace we've built templates for each one of those and those are available for purchase on our website so you know we've made specific videos for each one of those templates but this is the first one that you should watch uh before getting into the details of any of these other templates that i'm talking about all right so let's get started we've built this template in google sheets and that we felt was a more universal format but if you need to download an offline version you can do that and we have an excel version of it all right so on the homepage uh all you're going to get is some promotional stuff you can book a call with our team so we have a team of experts available if you need kind of specific questions or if you need some formulas built for you on the model you can book a quick 30-minute call with them and they can help you out um like i said you can download this or make a copy uh to keep it on your own as google sheet or download it to keep it as excel and again links to all the other templates and to all the other tutorials are also available on this home page all right so the first thing that you are kind of the most basic thing that you want to understand on the template is uh my or our nomenclature for uh the color coding uh blue is gonna be any input on the sheet so anything that you are supposed to change or that you should change is gonna be highlighted in blue and any black number those are gonna be numbers that are based on formulas so numbers that that are the results of a calculation maybe it comes from the same sheet where you are or from a from a different sheet but those are going to be black so if you need to if you need to reference something else you should do it in black and if you need to you know input an assumption something that you can change that you should change you should stick to blue uh and then we're gonna do a breakdown of all these sheets here so there's a summary of what each one of these does and that's what i'm gonna be doing in this video um so let's go to settings first okay so in the settings section you'll be able to change your company name so let's keep that as slight bean uh the currency of your model so this is going to be you know the model doesn't use a currency and formulas uh so you can just change the symbol here i don't know if you want to do euro you can just change that and then the model start date so we we're allowing you to start this model uh 2019 2020 or 2021 um and that's for example if you want to bring in some historical data so if your business has been operating and you want to bring some numbers from 2019 you can set the model back to that year or if you're really not going to be doing anything in 2020 you can start the model in 2021. okay uh initial cash balance that's going to let you add up any money that's been invested into the company already or any money that's in your bank accounts today if you don't have any cash balance now or if you're still looking to raise money then that number should remain at zero then we have taxes and inflation so this is the income tax rate that's going to be based on your country and then the inflation so the reason why these numbers are important is because this model is going to let you estimate what your business valuation is um to estimate the valuation we're using uh uh there are several ways to do that but the model uses uh what we call discounted cash flow so this kind of cash flow is in summary uh a prediction of how much revenue your company is going to generate over the next few years and then we're telling investors well this company is going to generate we predict that it's going to generate this much revenue and because you're investing early on we're going to give you a discount on that future cash flow for the company in a nutshell there's an article there from investor pedia that kind of goes over the details but this section here will let you estimate that valuation so for example um there's a valuation box here in the financial statement annual we'll get to this sheet in a second but this is the net present value so that that is arguably a number that you can use with to negotiate with investors as to what your business valuation is today assuming that of course the predictions the projections that you've added are accurate um but there's a discount rate again for the discounted cash flow model there's a multiplier for your ebitda so whatever number of profits your business is going to be making uh you're estimating that your business could be acquired by a multiplier of that for example in sas companies um sas companies are often acquired for five or ten times their evita um so that's that's a reference as to a potential exit for the business and therefore useful for this uh discounted cash flow valuation estimate uh then we have your then we have your debt capital ratio and your debt interest rate again these are both related to this math like the inputs required for this discounted cash flow math all right then for capital expenses um so the the capital expenses kpex sheet is not is rarely used uh we at slightly don't use it very much because it lets you estimate um the capital like the assets that your company has so for example in accounting if you're buying computers or if you're buying i don't know a large server who does that anyway but whatever uh so that's not really that's an expense so it's it's taking away from your cash flow but it's not but it's still an asset that the company owns uh you know for tax purposes that's not an expense as much as it is an asset that your company still has it still has value it's still in your books and it that the asset is going to depreciate over time so the kpix cheat is mostly focused on those assets and then the depreciation for them is estimated here so for example you're saying that you know any stuff that you do to your office is going to have a 10-year depreciation time uh office furniture is going to be depreciated in five years computers depreciated in three years and so on and so forth i mean at the early stages at least we didn't use this very much uh it's there like the model provides that functionality depending on your business this stuff might be more important um so that that's going to vary but functionality is there and then operating cache this might be useful for to other types of companies because it lets you estimate your accounts receivable so for example you could estimate that your business is going to charge customers but they're not going to pay you right away maybe that's going to go into accounts receivable and you're going to be paid on a net 30 or net 60. so you know your revenue is delayed a few months and that's really important because you need to cover the cash flow for those months so the capex sheet lets you do that i'm not going to go into that many details because most of these companies and most the companies that are using our model don't really need to dig deep into this stuff and if you need to dig dip into this stuff you probably know your way around it i hope all right so next up is teams and salaries and this is one of my favorite cheats because we've come up with a nice system to let you estimate how your team is going to scale over time so we've we've classified your team into operations product marketing and sales and customer support and the you know these positions notice that the numbers here are all blue that means that you can go ahead and change those those numbers and those names you know ceo might be i don't know chairman or whatever um so use them as a guide like keep the operations staff inside the operations section the product staff inside the product section and so on so forth uh also don't you know try to keep this as simple as possible uh we noticed that a few people were needing more than the five original positions that we were adding like from cto to junior developers for example people were coming up with new positions that's fine but try to keep it as simple as possible just so that you're not overwhelmed by the model um but yeah you can add say i don't know a mid mid level designer or mid-level developer here and kind of estimate their salaries based on that all right so notice that the next column here is the expense category so the model is going to let you do this the model has this functionality that's going to let you estimate what your expenses are so if you're building a pitch deck one of the most common questions is like how's your cap where's your capital going where are you going to be spending it so the model by classifying people into these categories that lets you kind of tell investors or estimate well how much of your uh round how much of the money that you're raising is going to go into product development into r d uh into marketing or into operations i think that those are the top three categories i would expect operations growth and r d but you can do others if you want so these are you know this is a drop down but you can change them in uh if you want to go to data data validation and change the list of items but don't do that if you don't need to so notice that all these are classified in in the category with dave law all right so now we have your currency we have your monthly cost to company so what the way we're estimating that notice that this number is black so the number being black means that it's a formula and you shouldn't change it you should change the blue numbers and those affect the black numbers so in here you have annual salary and that's the one that's affecting your your uh your black numbers here so say the cto you know these are placeholders but say you know you're going to hire a top tier cto and that guy's going to make that guy or girl is going to make i don't know 150 000 a year so that's the salary that you that you offer we're offering them 150 000 a year and then there's a there's an there's a number here for the payroll taxes or other benefits if you're doing insurance or if you're doing i mean just the payroll cost there's a cost on top of what you offer the employee so you can play around with that number say it's 15 and that's going to change on the monthly cost of company so there's a salary this is the salary that you tell them uh you're offering and this is the monthly cost of companies that's what the employee is going to cost you that number is the same if you don't have any overhead payroll cost so you can do that zero then we have the first hire so when does this employee join the company and then normally you'd have to go into a sheet and kind of you know estimate you know or add these numbers which which really gets cumbersome and you're going to see this reflected in a second but you know you can go ahead and say well i'm going to start with the cto in well in january 2021 and then there's going to be a vp of product but i'm not gonna need that guy yet i'm gonna wait and i'm gonna i'm gonna need that you know that guy higher one year later uh say senior developers so i'm gonna need senior developers as soon as i hire a cto maybe two months later i'm gonna start with the first one so that's march and i'm going to hire two senior developers every quarter uh all the way up to 10. uh and then there's the annual increase so there's the salary increase if you want to do adjustments or if you don't so you can set that to zero let's say so here we have i don't know maybe their salary is gonna be 100 100 a year so this is going to be estimated automatically and it's pretty beautiful i'm going to go into the into the detail of the sgna sheet later but just to show you what what that did is we have here your head count and you have your senior developers so as you can see we've added two and then next quarter we add two more and then next quarter we are two more and so on so forth all the way to 10 which is our maximum so in in the course of a year and some you're you're moving from two to ten developers but that gets calculated automatically um so all of that is controlled from here and we feel that this is a much easier way to do it than having to go and scroll through these uh you know in a month-to-month basis and kind of understanding uh where each one of these get hired you can do it of course so if you don't like our teams and salaries section and you want to do it manually you can absolutely so instead of doing two here you want to say well i want to do three this month and i want to forget the formula so make sure that you just keep this in order make that number blue so you know that it is an input and not a formula but yeah you can totally come in here into the sdna and to headcount and just add those numbers manually but at least in the early stages as you're estimating the size of your team and the scale doing it through here is probably easier and easier to manage and control you can of course also hide these others so if you don't need them sorry about oh that oh i'd say it's got these so if you don't need the others you can just select them and hide them and that way they won't they won't bother you but that's that for the team so the team is really important in the model you know if you're using this model to build a pitch deck or to kind of estimate your company expenses making sure that uh the stuff in here really matches your growth projection so if you wanna i don't know if you wanna if you expect to launch the product in six months well how how big of a team do you need to develop and launch a product in six months uh so make sure that those numbers reflect that and also timelines right so you know you're probably if you're building a product you're hiring your first product team you're probably going to have to hire them in x month and then only launch the product until six months later or something like that all right so next up is the projection sheet so this sheet is empty in this model because this is the kind of the free sample template um the idea is for you to come in here and build formulas yourself so you're going to say you know you know you you can add different variables here so an example i'm just going to do a sorry i'm just going to do a quick example of how that would look let's say i want to do i don't know monthly revenue and i'm just going to copy the format from here i'm going to say i don't know a thousand and then i'm going to say monthly revenue growth and that's going to be uh let's let's not use a number let's use a percentage and it's getting it's going to grow like 10 so this is not a you know this is not a great way to run a model you know by just saying that your revenue is going to grow 10 month over month is just unrealistic because what drives that revenue growth there should be a marketing campaign or an ad or a sales campaign that kind of boosts that revenue and makes it grow month over month but the point is that all these uh variables you want to keep in here so this is going to be your main input sheet so all the variables all the stuff that you want to control as to how your business scales you should add those variables here and by adding them here you keep them in one place and then you can go to the respective sheet in this case revenue and connect them and say well this is the revenue source one which we're calling sample and i'm going to come in here and connect and say well our revenue is going to grow you know i added a thousand dollars so i'm just going to grab the number from here 1000 and then i'm just going to multiply that by the growth which is this and there we go oh oh sorry that's times one plus that there you go so that's ten percent growth and you can potentially extend that formula all you know all over say projections there you go just extend it and then your revenue is going to scale 10 month or month again that's not necessarily realistic but it's a quick way to connect your revenue sources and you can say well what happens if i grow by 20 then those numbers automatically change so this projection sheet becomes kind of like a like a control center for everything that happens in your model now i don't want to go into details as to how to build different models here because that that's just complex we have some videos about them we've you know i've did a webinar on how to build a sas model and we we build it all the way from scratch but most importantly most useful we've built those templates already um so for an ad based but for an ad based business for an e-commerce business we've added all these variables in here and you can just buy that model and use them or maybe you already have all right okay so next up is the charts and kpi sheet now this is a great sheet because it's a summary of everything that you're doing so right now based on these little changes that we made notice that we still don't have any revenue we only have expenses we have this team that we created remember we created a uh senior developers team over here and that team is growing by two people all the way to ten so since that team is growing your expenses are growing and we're still not generating a revenue or at least we haven't added it to the model so this just looks like negative negative numbers as that team scales your cash in the bank assuming it starts from zero your initial cash balance that just goes negative and never grows that's that's okay we haven't added revenue um but this is kind of like the most like the summary of everything right normally when i'm working on this i look at projections i make the projections that i want to make here and then i go to charts and kpi and see uh what those projections look like if the business is going to profit or you know how when you when do you need to raise money uh and then the use of funds which is great this is the stuff that i told you about here on the teams and salary sheet you know whatever you classify this for example if you classify these senior developers as operations then that's going to automatically change here and then your operation is going to be your biggest expense um so it's a good summary of you know where your money is going this is only taking it took on the first 18 months into the model by the way and then capital required well what this is doing is it's saying well in order to support this business for the whole period of time here you're going to need 10 million and this business is not reaching profitability um again when let me do a very nasty example let's say that you're going to start making i don't know a million dollars a year a month not maybe less a hundred thousand uh hundred thousand dollars a month um and that's going to grow by 15 again don't do it like this because this is just a sample just to show you how this gets reflected so we have the sample here sample model uh uh-huh we're saying that the first month we're gonna make a hundred thousand and then that's gonna grow by one plus projections fifteen percent great uh there we go 115. so let's lock this number uh let's do the summary of all the revenue here there we go and we can just extend copy and extend this all over there we go so that's just going to come up with a crazy random crazy number but what i wanted to show you is how this business just scales crazy uh since it's growing so fast you never need money so let's maybe make something less of a bold projection let's start at ten thousand um there you go so this is you know this is more closer to a classic startup story where uh in the first few months you are cash flow negative so you need some money first uh until your kind of sales and your growth kicks in and after that you become profitable so you need more money so this capital required here is uh this gap here in your cash in the banks wherever that's zero we're estimating what this number is and that's the number here so that's potentially the amount of money that you would need to raise around 600 000 so don't normally investors want to see this they want to see what your expenses look like they want to see what your growth and revenue curve looks like and estimate um you know what what the amount of money needed to kind of cover that gap some businesses are not profitable this fast again this is just a random very non-educated sample of a business um so it doesn't it's not necessarily bad if you're not profitable right away um or you know within within your time period just know that if you're if you're raising money if you're only funding part of this curve uh you're gonna need to raise money again in x amount of months all right so let's delete this and be done right so i'm going to do the financial statement month first so this is just a summary of everything that's happening on the sheet i'm actually going to do this last let me let me skip this let's go to revenue uh well we talked about revenue already uh revenue lets you estimate uh different sources of revenue so we've added four blocks for different blocks what i would recommend here is not to do is is to use one block per business model so if you're doing a subscription for example any subscriber coming to your business should be a single block and you should use an average of your different subscriptions to estimate scale but for example if you're if you're doing subscriptions and at the same time you're doing some consulting then yes that consulting part should be another revenue block um again try to keep it to as fewer blocks as possible even if you have i don't know five different plans in your platform instead of using one blog per plan use kind of merge them all into one you can use an average of the mrr or the revenue per plan to estimate this all right cost of goods sold what that does is it lets you estimate the direct cost to providing your service on a sas company those costs are going to software companies those costs are probably going to be amazon web services i don't know intercom for support mailchimp uh there's the tools that you need for your product or your business to operate uh in an e-commerce platform the cogs sheet is much more complex because when you're when you're in e-commerce you're buying products you're buying goods and then you're reselling them so purchasing those goods that those numbers go here uh so the margins the gross the the cogs she lets you estimate the gross margin for the company uh in a sas product that's probably going to be 90 something any software really 90 percent 80 because the server costs to to provide that service are very small on an e-commerce platform or even i don't know a supermarket you know those margins gets to get a lot smaller probably 20 30 uh and really varies um of course it needs to be a positive number otherwise you'll be losing money um but that's just a margin on the services so we have a few categories like preset categories that you can totally change we have web services uh this one's empty and then we have payment processing so for example if you're using stripe to charge people on your credit card there's a 2.9 fee for any charge for any revenue so you can literally take your total revenue and multiplied by 2.9 percent to estimate the cost of actually processing those payments assuming that you're going to be doing that with credit cards um but that's what cogs does notice that all the numbers here are blue except for the like this the summary so these are really for you to come in and change you can do aws and say well i'm gonna be paying i don't know 40 40 a month for aws and then intercom for support i don't know maybe that's 150. and right now the formula just gets extended forever know that it's not going to be extended forever that easily so probably as your number of users scales that number is going to scale but that's just very different company companies right now we just added blue for everything and you can come in and come in here and estimate for your for your own business all right so where most of the work is going to happen is in the sgna sheet so this is the sheet that's going to estimate it's called sales general and administrative expenses so pretty much any marketing cost sales your business your operations your office all of that stuff is going to go here it's the sheet where most of the work goes and most of the expenses are locked so this sheet has a payroll section marketing growth advisory and professional services rent tech support and services insurance utilities and other so payroll notice that the numbers on payroll are all black why that's because we're estimating those numbers on the teams and salaries sheet so this sheet is connected to this section and that way you don't have to go to each one of these and estimate them so notice that the expense category is just being replicated from the other one and we have two sections on payroll we have the actual payroll costs which is the salaries and the salary raises for everybody and we have the headcount so these numbers here as to how many people we have those are being just those are just being pulled from the other sheet so remember these these are the same developers that we estimated they scale from two to eight we have one cto forever and so on um so yeah so you don't you don't want to come in here and change these numbers probably better if you if you work from the teams and savvy section but for the other ones yes these notice that these are blue so you want to be working with them so you want to be using your marketing and growth to estimate on your mailchimp or hubspot or whatever tools you're going to be using so these are the tools that the marketing team uses we've created a few sections for campaigns but you can change them so you can make this i don't know digital ads and you can make this offline ads if you want so anything that's blue you can change um so i don't know if you want to do billboards or anything like that experiment campaigns as well so anything that's blue you can change and then you can just come in here and add the numbers and that gets estimated automatically ideally uh you want to connect these numbers to growth so you know out of x amount of revenue spent on sorry x amount of ad budget spent on this group uh what's the acquisition cost i don't know 100 so maybe that's 15 customers so if you can you can go to the revenue sheet and connect this expense to your cost of acquisition and then estimate how many customers you're getting like i said we've built models that already do this but this is this is the blank canvas that you can customize any way you want all right so we have uh advisory and professional services i added two sections for the recurring versus the one time so i don't know if you want to do an accounting firm that's going to handle your books maybe that's i don't know a thousand bucks a month that's just forever but if you want to do one legal expense which is incorporation you can do it once and then you don't need to to spend it again usually we don't need to keep a law firm on retainers so there's no there's no need to play a lot with this but um yeah i see a lot of companies kind of outsourcing part of their development which you i think you never should do there's a video on that uh but yeah you can add it here and say well we're going to outsource the app development and it's going to be on 150k that's a one-time expense and you can add it whenever you need to okay so notice again these are blue up for change uh we have rent and then the office this is cool uh notice that the projections does have this so we're saying that what's the cost of having a desk this is based on like a ballpark wework estimate let's say 350. so what we're doing here very easily is taking your head count that's coming from your teams and salaries estimation two four eight and then we're multiplying that by 350. so that gives you the cost of an office assuming that it's 350 percent again doesn't mean this is a co-working space of some sort um otherwise you can come in and change it right and then actually be by my rules and this is a book that i have to fix now this number should be black and let's just do that so we keep keeping correct so that's black because it's it's a formula it's a result of something else okay uh tech support and services this is a section i use for like general tools that the team need needs to to further day-to-day i'm talking slide bean for example i'm talking gmail um you know whatever whatever day-to-day tools project management stuff um insurance so if you're not estimating your insurance as part of your teams and salaries here the overhead the taxes and benefits maybe you can do it here if you want to do it like a percentage of the employee but if not you can just do it here and say well maybe i'm getting i don't think so maybe you have a flat cost of employment insurance maybe that's i don't know 400 and you can just multiply that by your headcount and that should give you the right estimate and then of course the stretch formula all the way right so and that that should be black because it's a formula you know assets you know we pay some company insurance like errors on emissions and that sort of thing so you can add those there utilities if you want to go that deep into calculating your power and your internet costs it's probably not going to be too much it's probably not going to affect your model too much but or your profitability but it's there if you want to estimate it eventually that cost is going to come and then finally others so i usually keep travel meals bag fees and all the other stuff here these are all blue again up for you to change and modify as you want so normally what we do with this model we use this this is a tool that we use uh pretty much every week twice a week and probably even working on this sheet why because we connect all of these expenses to reality so for example if we're buying a new you know if we expect to be hiring three new sales people in the next few months that is that number of sales the size of the sales team is connected for example to the hubspot costs so we know that the hubspot cost is going to increase as we increase our team or for example the you know our mailing system or our email sending system which is intercom is connected to the number of customers the number of people that's active on the platform so we know that if we estimate that the company is going to scale this much over the next few months that means that intercom or any other costs associated with with operating the business that's going to scale as well and that's the magic of a model that it's it's it's not it's alive it's fed by the real data that you're collecting as you evolve in the business but also gives you pretty accurate projections of what's going to happen later in the end you know balancing that profitability is is the core of our job as ceos um all right so the last sheet in in a standard model is this one which is the working sorry the kpix and working capital uh as i mentioned there's the account receivable stuff which i'm not really going to touch on um but most importantly here what we have is these capital expenditures so uh if you remember from the settings sheet there is a setting that will let you estimate automatically estimate new computer purchases for new employees you can say yes um that means that the that whenever new people are hired you're going to have to buy computers for them and that that's just happening automatically um so that's happening here if you remember we set it as two you know two new developers every quarter and as you hire those people in here uh their new computers are automatically added and estimated so again these are not expenses these are assets that the company has you can't necessarily write them off in your taxes right away but what you can write off is here your depreciation so those computers those assets that you have are depreciating depreciating over time and that depreciation that is something that you can write off and that gets accumulated automatically even actually when computers completely depreciate so when their value reaches zero the model automatically estimates a new purchase assuming that that computer died or you know it's of course an average of all the all the assets so again these are the sheets that a normal financial model has most of the work usually happens in revenue cogs and sg a just to keep that ordered i mean sorted and organized you you want to do most of the variables and projections so you have to be jumping between sheets to kind of control them last but not least i want to talk about the financial statement sheets so these are pretty much a summary of everything that we've just talked about they range from revenue to cost of goods sold and sdna so these are all classified in your income statement and this is going to be combined with your balance sheet so the balance sheet like i said expenses that are logged in the working capital sheet and the capex sheet work a little differently they're not expenses they're assets so they're part of your balance sheet technically so if you own cars they're part of your books which is very different from expenses in software for example so that's why they belong in different categories and that gives you a final cash flow which is the actual cash in hand that the company has right now it's all negative because we have uh we haven't enabled any revenue we haven't programmed any revenue in here and that initial cash balance which started at zero is you know is going down as your expenses kind of piled up one for month or month so technically the number that you you you potentially want to raise is the the lowest number here um so for example if you want to raise capital for 18 months of runway uh you you're going to be looking for the lowest uh ending cash balance number here which is 1.4 million dollars again this is a company that generates no revenue but this company costs this much to operate if there's revenue here uh if you've enabled any revenue in this company let's in this model let's see if we still have that yeah so if we if we've enabled revenue uh in the company then that number is going to change of course if we go to financial statement month you can see that you know there's a negative cash flow as revenue takes some time to scale but as that revenue scale suddenly this number becomes positive so the number that you need to raise that you're looking for is the smallest number here in this line which we're estimating automatically here in total capital requirements and that's the number that we're giving here in charts and kpi um so you know right now we're reporting 18 months to reach profitability and that's all visible here because you know your cash flow uh becomes positive on month uh what's that month 18. here there you go ending cash balance month 19. uh all right so compared to that we have the financial statement annual which is pretty much the same thing except on an annualized basis um the you know one thing to look at here is the gross margin so remember gross margin is the comparison between the revenue and the cost of goods sold right now we haven't had anything so it's a hundred percent uh like i said for a sas business this is probably going to be in the uh in the 80 90 range for an e-commerce maybe it's going to be in the 30 range uh sgna that's that just comes after that gross margin and then balance sheet again for the annual uh the other number that i want to point your attention to here is this net present value so that's the valuation that we talked about earlier on on on the settings so if your valuation is a multiplier of your ebitda well in this case let's just make it one because otherwise this number's just becoming astronomically big because of our no it's still pretty big let's keep our revenue growth smaller all right ten percent all right so if you go to statement annual so this is the net present value as you can see and that's based on you know the revenue that the company is generating and this discounted cash flow formula that i mentioned earlier on and the exit value that we're assuming as a multiplier of your ebitda now we've added two bonus sheets in this model which are the funding uh equity calculators which are pretty slick pretty self-explanatory um so we have two uh approaches which are the equity and the convertible note um so for equity assuming this is you know first investor that's coming in they're getting stocks it's not a convertible node they're getting stock right away so the inputs here are how many shares of stock have been issued normally companies should be incorporated with 1 million or 10 million so this is a standard um then how many shares do each one of these founders have maybe you guys are doing i don't know 40 40 20 something like that so that means that you have 4 million shares of stock based on this 10 million number here then you're saying well how much money are you looking to raise maybe that's 500 000. uh this number is not connected to the rest of the sheet by the way this is just a quick calculator to let you estimate like how shares would look so let's say it's 750 and then you're raising on a five million dollar free money valuation that means that the post money evaluation is 5 million and 750 that means that you the company will need to issue one and a half million new shares of stock to the investors and it'll have now a total of 11 million shares of stock and the investors are going to get 30 13 of that so notice uh common misconception is that uh shares change hands which which is not the case shares rarely change hands and it's actually a big legal and tax mess to sell or sell shares what's usually done in fundraising is the company issues new shares so in this case notice that the number of shares that each one of the founders had at the beginning has remained the same the difference is we've now added or issued one and a half million new shares so the total number of shares in the business is 11 million so founder 1 still has 4 million shares it's just a smaller percentage because now the company has issued more shares in total and there's a nice chart if you want to visualize it with convertible notes it's rather simple uh remember that a convertible note is kind of bridge funding that that doesn't have a valuation it's kind of waiting for a new round of funding to provide to answer the question of how much the business is worth um there's a whole video we made on how convertible notes work so you can go and check that out if you want uh but you know let's do the same math we have 10 million shares at the beginning founders with 40 40 20 share distribution um how much money are you looking to raise in this convertible note let's say it's 500k for the convertible node uh what are the terms so there's going to be a discount on on the future valuation uh there's going to be an interest rate if you're doing a save then the interest rate is zero and there's a cap on the maximum valuation at which this node will convert again details on the video i mentioned uh and then you you're going to need to so this is going to be temporary but what you expect is that you're going to use this bridge funding to raise a series a so let's assume that series a is at a 10 million valuation that it happens 18 months after the original node that's in case there are interests so i'm going to add that back here just to demonstrate and then the investment size or the new series a round is 2 million so what's going to happen is the convertible node will convert first with these terms so there's some accrued interest from the 18 months that passed there is the applicable valuation so in this case it's the cap six million if the cap would were larger then the the applicable valuation would be not the cap but the evaluation of the series a minus the discount and then the shares issued to the convertible investors total shares after the convertible note converts that means that the convertible investors get eight point two twenty two percent and then um i mean they get eight twenty two percent eight point two percent now but then there's the series a converting uh so the zeros a comes in that's an extra what's that two million dollars in capital there's a post-money valuation total company stock of their series a percentage owned and then this is the new distribution right so the real loan investors get this series a get this and then here's the distribution again notice that the number of shares has not changed the only thing we've changed is the total number of shares in the company and how that distribution gets affected so again these are mostly to provide a little bit of an exercise of to understand what your shared distribution could look like in the future all right so that's that um like you said we've we've sort of built this build this template and made available for free we know for a fact that at least 10 000 people have downloaded it and we know that our there are a few investors and firms uh recommending our model as a template so we figured that we should make a proper video on how the financial model works um most of this stuff you can build on some very basic uh spreadsheet excel skills um like i said if you don't want to kind of build your own models from scratch we have a model for a sas company subscription business we have a model for the marketplace business where you connect buyers and sellers uh a model for an e-commerce platform and a model for an app-based business which are pretty much some which pretty much summarize most of the companies out there so those are pre-built you know all the variables and all the projections are already built in there for example you can take a look at this one which is the subscription model where you know we automatically let you estimate uh let you estimate for example the revenue coming from self-service subscriptions assuming your customers come in and subscribe on their own or the outbound sales model in which you have sales agents or sales development reps that are doing outbound calls rather than reach out to customers so all of those formulas have been built and you know you can enable and disable the models here again all the projections are there and they're connected to the revenue sheet to kind of estimate how your business skills in the future so it's it's really like a white canvas where you can come in and add your um your other stuff your actual pricing your scale size your your market size the size of your team and so on and estimate how that business is going to perform without having to kind of build the spreadsheet from scratch so if you've purchased any of those models this is going to be part one of that tutorial there's definitely going to be a second part that you're going to be able to see on that on your model itself where you can access the details as to how that model specifically works but again this template that i just used today is available for free i'm going to link that in the description so i hope you find it useful best of luck with your financial modeling and best of luck with your business we'll see you next week you

Channel: Slidebean

Views: 188,637

Keywords: excel, excel financial modeling, financial modeling, financial model slidebean, startup, startup fundraising, revenue projection model excel, revenue projection, revenue projection template, financial analysis, startup funding explained, financial modeling course, financial modeling for beginners, excel model, financial model, excel tips, microsoft excel, financial model excel, what is financial modeling, financial modeling excel, caya slidebean, financial modelling

Id: rwUxqjnksAc

Length: 40min 34sec (2434 seconds)

Published: Wed Jul 29 2020