Startup Financial Model Demo

Startup Financial Model Demo

hey guys and welcome to the first installment of fun with financial modeling with sumit it might be the last installment who knows but today I'm going to walk you through building a basic financial model using a template that's on my website sumit Kapoor comm basically a bunch of friends had asked for a basic template and I'd put it together and I thought I might post it on my own website as well so we'll go ahead and get started start up by just opening the financial model and enable the macros there's only one macro and it's pretty simple and very useful and go ahead and just make as big the first thing I would advise you do is go into preferences and click on error checking and make sure that all these are unchecked otherwise you get a bunch of green triangles that are marginally useful at best so right away you're in the main page this main page has a lot of the assumptions and some of the high-level results but let's go right into the guts the model which is these three tabs is the s and C F which is income statement balance sheet and cash flow statement the purpose of this model is to project your income statement balance sheet and cash flow statement which are the three main financial tools financial items that are projected in most financial models or the main financial statements the two that you really care about our income statement and balance sheet the income statement shows basically your income which is flows over a period of time so let's say for 2014 this is the total sales that occurred in that period of time revenue cost of revenue operating expenses and other expenses the balance sheet on the other hand is the snapshot at a point in time so this is your assets your liabilities in your equity at the end of 2014 end of 2015 or end of any given month and the way a balance sheet works is your assets are what you have of value your liabilities of what you owe to other people of value and equity is what's left over so assets equal liabilities plus equity is the basic financial equation the cash flow statement is important but basically it's just arrived from values in the income statement and balance sheet so realistically the two main financial statements are income statement and balance sheet and the way these two relate is that the income statement gives you net earnings and net earnings net income here flows into retained earnings so your change in retained earnings from one period to the next is the previous retained earnings plus the net income from the from from the income statement so they're very interrelated and these are the two main guys you're going to care about let's go back to the main tab the main tab has high-level results this is what you're projecting for key metrics total revenue EBIT earnings before interest taxes depreciation and amortization it's a key value used a lot of multiples for valuation net income and cash at the period end and then there's a bunch of assumptions down there ignore this saved and summary – saved we'll get into that later but the assumptions that we'll go through will drive the entire financial projections you'll have revenue assumptions expense assumptions and other assumptions down there below let me take a quick step to go over some high-level things you know I'm going to be trying to teach you how to fish and teach you how to use the model and teach you how to financial model in general so I'll be giving tips the first is I always try to make sure that my model looks nice and the reason for that is if there is questions around consistency and formatting if there's questions around things looking sloppy then people start to question the content so it's very important to make sure things look nice and to that end I have a few Styles very few header 1 header 2 row header 1 row header 2 and row header 3 very basic stuff and I use indents so here you'll see you know on your revenue I use this indenting button or D denting button to put sub items underneath theirs to make things easier for the user to follow through second try to make things as simple as possible and understandable the reason for that is if you if people don't understand what's going on and they start to question the results as well so you know Einstein said make things as complex as necessary but not one bit more so try to use that in your financial modeling and the third thing is try to make your models as dynamic as possible which means that if you change yellow cells which all inputs are in yellow so really you should only be mucking with the yellow cells until you want to change the structure of the model which I highly advise you get into and understand what's going on in the formulas but if you change the yellow cells it'll change the results and if you make the model dynamic then you can see how your business strategy would affect your financial results so let's get into it and start building a financial model for a company so first we'll choose a name for a company today we will make Bob's company which will be Bob CO which will be kind of a software as a service platform as a service buzzword compliant company first model month end make sure you choose a month end for this but we'll start in the beginning of 2014's the first month end would be 1 31 14 and we'll go ahead and start filling in some of our revenue assumptions here Bob is going to be charging ten dollars for each sale or subscription whatever you want to call it and a direct call given that a free software you don't have a whole lot of raw material you might have some payroll of payment processing fees so go ahead and put three percent in there as you can see no revenue is coming in yet and that's because we haven't done any marketing we don't really have any sales so now we'll go ahead and change that and we'll fill in outbound marketing inbound marketing channel partner marketing we might fill in ad sales revenue and we might fill in these user base assumptions down here so I've already read done a bunch of these before on this Bob code tab so I'm just going to be copying and pasting you don't have to do that obviously you can just fill in whatever you want Mugello sells so first we'll fill in outbound marketing so you'll be spending this amount in any given quarter so for example in 2014 third quarter you spend five thousand dollars a month and they would buy you a certain number of impressions at this CPM those certain number of those users would click through to your website and then a certain number of those would convert into actual sales we'll go ahead and also get inbound marketing because Bob's no dummy he knows that today it's all about being social and spending some money on getting users engaged we'll also go ahead and create a partner marketing channel and these partners will market your product on your behalf and you'll pay them a certain fee dollars per sale here and this is the number of active partnerships so a partnership would ramp up to over three months 500 sales and then it would continue to grow at 20% so within this whole section you're going to start to understand how it is to model things and we'll show you I'll show you exactly where the where these flow through to and second we'll grab some ad sales Bob's going to have a little bit of ad revenue here some models obviously have all ad revenue some models have none Bob is kind of a hybrid operator and he's also going to have a certain number of sales to existing users who at write or leave at 10% per year but 1/4 of them will sell will will buy in any given year and then he's also going to have viral market which is super important as you will see it's a very sensitive assumption and actually what happens with viral marketing is each new user sends out invitations some portion of those invitations convert and the product of those is the coefficient of reality anything above one will start to give you massive growth exponential growth effect and if you want more details you can just google David's coke eske okay he's done some really good write-ups on this but now you'll see we all of a sudden have a bunch of revenue we have seven million dollars in revenue in 2017 over the projection period we have ten million dollars in revenue we have EBIT da we have net income we have cash the way this happened is all of these assumptions drove sales and on this sales page you'll see marketing driven sales from each of your channels outbound inbound and Channel Partner marketing you see user base driven sales user day sales to user base and morality and then you'll see total sales I've reached channel I'd calculate the customer acquisition cost I know I'm moving really fast here but feel free to go into each one of these cells and understand what's going on some of the very important functions that you're going to want to know are vlookup which looks up and it looks up a certain value so for in this case it would look up how much marketing is spent in this given year in this given quarter another function to get to know would be index index is very similar to vlookup H lookup is an important function some if and some ifs are important functions those are things to get familiar with in Excel but here your sales are now growing as a result of the marketing you're doing and in fact you're getting all these new sales two hundred thirty thousand and sixteen six hundred sixty six thousand and two thousand seventeen and it's driving all sorts of revenue and you'll see there are yellow sections within each of one of these individual tabs feel free to put whatever you'd like there those are also inputs you might have other revenue streams might have other costs of revenue here you'll see direct cost from that three percent we put on the main page or coming in but there's still no operating expense and the balance sheet is balance sheet is flowing through as well you'll see cash is now starting to come into the balance sheet as a result of the sales that we're making so we'll go back to the main page and now we filled in a lot of these revenue assumptions I'm going to now show you one of the tools in this sheet if you click this button summary to saved what happens is Excel will start to convulse and actually do a little macro that will save down the results from this particular projection so you have these two sheets here summary and saved summary is basically high-level details from your income statement and your balance sheet it just literally pulls these from the income statement the balance sheet in just a more high level format with less granularity the reason this is important is it's very nice to be able to see this as a at a glance and it's often useful for pasting into power points etc saved is just the exact same thing as summary but just pasted values so that you can keep track but per to other scenario and that's useful because then you're going to be able to see how you change yellow assumptions and see the impact they have in summary – saves you see right now summary and saved or the exact same thing so we'll go ahead and use that because now we have to have some expenses because we haven't we haven't hired anybody or done anything in terms of hosting or anything else that is required in order to sell something so we will go ahead and take these except expense assumptions from this page as I mentioned I've already filled in some of this stuff before and all I'm doing is pasting values but you don't have to do that here this is total number of Engineers the total number of engineers in any given quarter and and the ratio of junior to senior engineers here you're going to be looking at the business analysts and managers just random guys to have around you'll also want some biz dev guys that's going to be driven a little bit differently it's going to be based on the number of sales and the the customer support staff is also going to be driven off of the number of sales and I have listed salary levels here that I'd already filled in other payroll assumptions payroll tax this is what employers pay employers actually have to pay a portion of taxes on top of what the poiice pay we also have IT phones rent travel and entertainment insurances and I'm going to fill in a minimum here for insurances you also have hosting fees hosting fees obviously scale as you grow so this functionality here enables that to happen so you have a starting point you have a point at ten thousand monthly sales ten thousand dollars for instance here but each additional ten thousand only cost you twenty five hundred because you're scaling and now that you have departments you also have budgets so we will go ahead and specify the budgets for each of the different departments that's above and beyond what would come up in other areas for instance marketing that spend up there but they also might have some other budget that they're looking to spend so now we've gone ahead and filled in all these expenses sumption x' and scroll up and you see he okay as a result of what happened our EBIT da fell by 4.5 million in 2017 our revenue didn't change right net incomes drop significantly cash dropped significantly so what happened is we started to actually have some expenses and that that influenced our financial projections so if we go over here the balance sheet for example will see cash starting to drop from from day one because you're not really cause generating enough revenue to cover your cash burn as any business you know would have to make the initial investment so what we'll do is we will go ahead and rectify that by raising some capital so again the balance sheet has assets it has liabilities it has equity the capital that you might raise initially would be company debt let's say and so Bob is going to go ahead and raise three hundred thousand of company debt right away from angels or you know his own credit card Bob's a big hitter so he's got plenty of cash so he puts that money to work here and you need to keep that if you if you would have three hundred thousand here then zero here the model would assume you paid that debt back so don't do that and you see now the amount of cash actually is positive positive positive positive and then it goes to the right so Bob is going to need to go into raising venture capital so let's say Bob out here raises you know five million in venture capital and carry that through Bob does this really fast bob is one of the most well-connected people out there and then after that five million dollars let's say out here he raises another five bringing the total number of total amount of equity to ten million and that would be Bob's series B so take that all the way through equity is obviously not paid back most cases you do see this line here assets minus liabilities and equity that should be 0 that means the balance sheet is balancing it's not then your something wrong with your model so I've just grade that out but now we do have you know a lot of the a lot of the main assumptions filled in as well as a capital raise one thing we didn't do is fill in corporate employees this staff expense line is pulling in employees from that main assumptions page it's a product development for instance we had specified two engineers right away here they are and here's the cost for those guys and then bizdev and other guys will come in in accordance with those main Assaf main tab assumptions you also might have these guys all this page is yellow so you can do whatever you want this yellow section here that I'm mocking with right now is not going to be there in your model but it's just there for me to make this kind of quick and he's there so now what's happening is we have a CEO starting in we're going to start paying back in July so you paid works for six months for free we start hire we hire CEO Oh at the beginning of 2015 these guys that are one one twenty start and one 120 and the model is just going to say look they never really they don't they don't exist for the purposes of this modeling Cruise so none of that will flow through but this will use all the different assumptions from the main page in terms of payroll texts and benefits to fully load these employees so now let's come back and take a look at how things are looking with the new capital we raised now the cash situation is is pretty healthy right because Bob had raised all that venture capital and actually Eva dies you know in the beginning its negative but he starts to turn a profit in 2017 both EBIT dialyze and net income wise and so we'll go ahead and save that as a saved set of assumptions so this is a good base case for Bob you know feel free again to go through these assumptions understand what's going on and and and look at what's happening on the individual test income statement balance sheet at cash flow statement where's the money going and try to understand what's going on in these individual you know formulas you know here's an example index main you know blah blah blah blah blah which basically means it's looking up what's happening in 2014 for the legal budget for 2014 which was listed here it the legal budget so all those assumptions are going through now again to try to tweak things a little bit let's just see what happens when we change some assumptions so for ality i said was something that's super sensitive let's say instead of sending out five you get the new users to send out six invitations boom all of a sudden your revenues up by 2.8 million in 2017 you know EBIT does up by 2.2 million you know things are things are starting to look really good for Bob one thing we didn't look at too much was the cash so just look at you can look at the trend line for cash you see obviously you raise the five million and it goes down down down but then he raises another chunk and there it is and it starts going down but he turns it around and it starts going back so and you can see that as well on the cash flow page you can see where net income is your base for the cash flow and then the other operating accounts would would cause other changes in cash investing cash flow would be for instance fixed assets so fixed assets you know you could specify that on the balance sheet page here if you would for instance need to buy some machinery or something like that and then financing cash flow you can see the impact of those capital raises here debt financing 300 in here's five million dollars from the equity raised series a and over here should be another five million dollar series so all these cells are being pulled dynamically at the whole model it just dynamically just based on what you do in those yellow tabs and those yellow cells so you know we did this one change and all of a sudden you know Bob is you know jamming he's got nine point seven million dollars revenue he's got a real business on his hands you know another another change we might make is rather than just selling them once let's say it's a subscription that that lasts twelve months so first we'll just save down this assumption so we can see the impact of just that one change like that but let Excel convulse and what we're going to do is we're going to say you know that they're going to pay ten dollars every month for 12 months and boom Bob is now at a 73 million dollar company obviously it was going to it went up by 63 million dollars in terms of revenue so obviously selling someone $10 one-shot deals is not is not nearly as a larger company is telling them a 12-month subscription at $10 a month so that's what we did there we'll go ahead and do that and down here you know I'll go through a couple other things ratio of junior to senior this is used to calculate how much these engineers cost based on the salary levels that are down here and the annual increases you also have a couple of other assumptions that are a little bit more complicated they are at month end as a percent of revenue ap at month end at center gravity that's accounts receivable and accounts payable accounts receivable is means that you don't get all the sales in that in a month that you sell so you're going to be collecting and you know over some time period accounts payable means that you don't have to pay all your expenses in the must of in the month that they approve so that's used in order to calculate and forecast the balance sheet accounts here and which are which imply changes in cash flow again all this stuff is dynamic here some yellow cells you can feel free to put whatever you want there and also on the main tab is the interest rate on company debt we raise that $300,000 a company that we're paying percent interest on that and you have the corporate tax rate taxes are all calculated in here and what happens is initially you really start to build up a tax asset so we're losing money and we have negative taxes so what happens is you start to build up this net operating losses this deferred tax assets meaning in the future when you do start to make money you don't you don't have to pay taxes right away because you can claim that all those losses from the past so this asset builds up over time and then eventually starts to go down as you really start to you didn't do it because you started making money so that's the high level on on this financial model you know feel free to experiment you know if you have any questions you know feel free to reach out to me don't be offended if I don't respond bobko is a busy place and you know next steps are now go ahead and build out your company your startup your dreams and you know eventually actuals and things like that will be integrated into a model like this but for now hopefully this is a useful tool for you to project and be able to convey the ideas about what your business is and could be through financial protection good luck

25 thoughts on “Startup Financial Model Demo

  1. I love this but it seems a bit too detailed for what I need. @Sumit, do you happen to have a simplified version of this?

  2. Dear Sumit, I really love your model. But there is no makro in the xlsx file. Can you send me the xlsm version?

  3. Thanks for the video! Here is a ready-to-use Excel Financial Model for Mobile Apps:

  4. Hi Sumit, very helpful. One question though, where do I input the cost for development for website-mobile app?

Leave a Reply

Your email address will not be published. Required fields are marked *