Build a Startup Financial Model | The ULTIMATE Guide With Free Template

in this video we're going to create a financial model like this for a startup to determine whether it's worth getting started with this business idea so first we'll model out our revenue assumptions considering things like how our customers are going to find us then we'll go on to the cost assumptions looking at things like our ad campaign or our office rent then we'll move on to a full income statement to determine how profitable this business idea could be and finally we'll create some visuals to support the data here's the excel file we'll be working with

which you can download in the description below so as you can see as of now it's for a total of 12 months and feel free to extend this out further if you need so starting with the revenue assumptions and at the very top is how we're going to get customers and generally there's two main ways one is through organic traffic and the other one is through paid traffic so organic is basically maybe you write an article maybe you have some social media posts and that's how they discover you organically whilst on the other hand with

paid traffic this is paying for social media ads google ads etc if we look at some of the assumptions that we've made here for page traffic so we have the organic side and we've got the paid side and the sum of those two is going to be the total traffic feel free to adjust these numbers depending on what you think is gonna it's gonna do so i'm just gonna press the shift and then the right arrow and then i'm gonna press alt equals that's gonna sum all of these for me so if you go and

on the first one press the f2 key you're gonna see that the sum formula just auto populated there from there obviously not everyone that sees the website is going to buy the product so there's going to be some kind of a conversion rate now intuitively i'd imagine that organic traffic is going to have a higher conversion as the customer already knows you and they've seen your products or your services as opposed to paid traffic where it's kind of forced on and it's a bit more artificial so if we look over here at our assumptions you

can see that our organic traffic has a two percent conversion while the paid one has about one percent now depending on what product you sell the conversion rate is going to vary so for instance something very cheap is probably going to have a lot higher a conversion so let's go ahead and calculate this average conversion just by going to equals average press the tab key there once you find the formula and then we'll select these two close the brackets and hit enter and then we're just gonna drag this across like so now that we've calculated

the total traffic and the conversions we can go ahead and find the total number of orders so let's go ahead and type for organic it's just going to be equals to the organic percentage multiplied by the total page traffic for organic the same thing goes for the one below so i'm just going to ctrl c ctrl v from here let's select both of them so shift down arrow then i'm just going to go shift right and just press the control r and that's going to auto fill it for you all the way till the end

from here for the total orders just alt equals hit enter there and same thing shift right arrow and then ctrl r now we know that for the first month we're expecting to get 40 orders now how much are those order is going to be for so that's when the order details comes into play and first we're going to have the average item value so as you can see over here we assumed a price of 25 dollars for our startup product and from there around mid mid year sorry month 7 here we're going to go ahead

and increase the price hopefully as the quality of our product improves then right below that we have the number of items per order so typically when say you buy something on amazon you might buy more than one product and so the items per order are going to actually increase as you can see first we assume just one as people are not going to be that familiar with our product but as hopefully they start to like it more and more then they might buy some more of the same thing or some different products that we have

up on offering so from there the average order value is just going to be equals to the average item value times the number of items per order and i'm just going to drag that across so shift right arrow and ctrl r awesome that covers our revenue assumptions so let's get started with the cost side so if you scroll down over here you're gonna find all our cost assumptions and generally we're gonna have a big split between fixed costs and variable costs so the difference here is that the variable ones are going to depend on output

so the more you produce the higher that cost is going to be well the fixed ones are going to remain the same for instance office rent is going to remain the same regardless of how much you produce looking at the numbers over here you can see that on the variable side we have manufacturing order fulfillment and paid traffic let's say that we work with a third party and they're the ones that manufacture it and as you can see this is on a per order basis now as you move along towards month 7 you can see

that that the cost is going to drop the reason being that hopefully we can negotiate a better deal as we move along and our volume increases same thing goes with the fulfillment so maybe this is the shipping side of things which we hope will also decrease around there and lastly we have the paid traffic this is on a cost per click basis so suppose you're paying for instagram ads and every time somebody clicks on that ad then that's when instagram is going to charge you that's a variable side and then on the fixed side you

can see that we have the warehouse rent then we've got the office rent salaries and other as you can see for salaries we're going to give ourselves a very modest 1000 and then hopefully in month seven either we hire a new person and that's why it increases maybe you want to pay yourself a bit better and lastly down over here we have a standard tax rate of 21 and again feel free to modify these assumptions depending on what you think is most suited to your business and speaking of financial modeling if you want to learn

more about the topic i recommend checking out financial edge which provides certified online finance courses and is also sponsoring this video their best-selling investment banker course goes over all the relevant skills to succeed in an analyst role these include creating a three statement financial model the key valuation methods such as a discounted cash flow and much more and if you're interested in other areas of finance they also have courses on private equity trading portfolio management and more so if you're interested in checking them out go to a link in the description below where you can

get 25 off using code kenji25 all right back to the startup great now that we have our cost and our revenue assumptions we can start working on an income statement to determine whether this business is going to be profitable in the first 12 months so over here just scroll down towards where it says income statement firstly we've got the revenue line item which is simply going to be the price times the quantity so to us it's going to be equals to the total orders so it's going to be right over here that 40 there multiplying

that by our price which is going to be our average order value over here and hit enter then the cogs stands for cost of goods sold and to us that's going to be our manufacturing cost so we'll go equals and we're going to go to manufacturing and multiply that on a per order basis so multiply by orders so that's going to be 2.40 and hit enter there then the gross profit simply one minus the other so equals revenue minus the cogs hit enter there and the gross profit margin as you can see the unit is

percentage here simply gonna be equals to the gross profit divided by the revenue and hit enter there to extend this across we're just going to select them so shift down arrow here and then shift right arrow all the way till the end there and hit control r that's going to extend it out for you as you can see over here our hope is that the revenues are going to scale quite significantly especially towards the end and so will our gross profit margin then we need to account for all our other costs so if you go

down a row over here you can see that first we have a split by variable costs so first off we have the marketing cost here so this is basically the ad expenditures and to us that's going to be equals let's go ahead and find the number for our ads per click so pay traffic cost per paid click this is going to be the relevant metric for us and we'll multiply that by the total traffic it's paid traffic here so we'll go and find the page traffic and just hit enter there great then we have fulfillment

so equals that's gonna be the order fulfillment here which is going to be this figure and multiply that by the total number of orders so that 40 over here hit enter there and then that's going to be our total variable costs so we're just going to go alt equals and hit enter now just below that we have the contribution margin which is simply the total revenue minus the variable costs it does look a bit trickier over here as we have the cost of goods sold up over here and this is most likely going to be

a variable cost as well and so we're going to account for it in there too so we'll go equals then firstly we're going to have the we would basically have the revenue minus the cogs which is the same thing as a gross profit minus the total variable cost over here and hit enter then for the contribution margin percentage it's equals to the contribution margin divided by the revenue for us and hit enter over there and again let's move this across so ctrl shift up arrow and then shift right all the way to the end there

and ctrl r awesome let's now work on our fixed costs so go to down arrow over here and you can see that first we have general and administrative so here we're going to sum a few different line items so we'll go equals sum that's the formula there hit the tab key and what we want to sum let's go under assumptions this is general and administrative so we're gonna sum the warehouse rent office rent and salaries into that close those brackets and hit enter and other we're just gonna put other on its own so we're just

gonna link it equals other like so total fixed alt equals there that didn't quite work as you can see so we're just gonna go equals this one plus this one over here hit enter and now let's move this across so shift down arrow shift right arrow and ctrl r if we keep scrolling down over here you can see that we have a total costs so equals that's going to be our fixed plus our variable which is this one here plus our cost of goods sold as well let's not forget that one here and then just

below we have the ebt which stands for earnings before taxes so over here this is simply going to be equals to the contribution margin minus the fixed cost which we haven't quite accounted for yet and hit enter there as you can see we have a negative number as of now that's quite common in a startup so shift down arrow and then shift right again and ctrl are great now moving on to the taxes if you recall we had an assumption for a 21 tax rate but this really is only gonna apply if you have something

to be taxed on so if you have losses like we do in these first six months then there's nothing to be taxed on so for this we're going to create a formula of sorts which says that hey if you have a negative figure then you shouldn't be taxed so we'll go equals if this is going to be a conditional statement press the tab key there the logical test for us is that the ebt here has to be greater than zero comma if that is the case then we want to multiply the ebt times the tax

rate which is that 21 right over here coma now you can see it says value if false so if that's not the case then what what should you do and we're just going to say in quotations to put an a sign we're gonna close those brackets and hit enter and so naturally when it's negative it should say n a let's move that across so we can see if it's working okay ctrl r and that seems to be about right in that here it starts to get triggered and it shows the actual number for taxes now

for the profit or the loss if we try to do equals the ebt earnings before taxes minus the taxes it's not quite gonna work and that's because this here is a letter and so we're gonna create another formula here press the f2 key to get it inside of there inside of that tab and at the very front here where there's the equal sign we're just going to go if error press the tab key you can see here the instructions if you want to read them so i'm going to press the tab and the value is

this one so this is what we want but if there is an error what we want to do is just link it to the ebt close those brackets and hit enter this way as you can see we had an error here and it's just going to show the profit or the loss the loss in this case is the same as the ebt then shift right arrow again and ctrl r great now we have a full income statement over here where as you can see in the first six months we're actually not gonna be making any

money but from there slowly we're gonna start to scale and in month 12 we're actually gonna be making 5k which is pretty decent for one month and finally to visualize these findings we can create two different charts if you scroll down over here they should be auto populated and you can see on the one hand we have the traffic source so if you remember there's a split between organic and paid traffic and the overall trend that we're seeing here is that the organic traffic is gonna start to scale quite a bit and so by the

time it's so high we probably won't need to be paying and investing in paid traffic so that's why this is gonna slowly start to drop off which is a positive for us and then over here we have the revenues versus the costs as you can see the revenues are gonna scale uh quite similarly to the organic traffic you could say while the costs are actually gonna remain um pretty much the same or slowly just increase there the reason for that is that hopefully we're gonna strike better deals with our suppliers and the shipping companies and

so that's what's gonna allow our costs to stay relatively the same if you want to learn to make visuals like these you can check out this video over here where i go over it more detail or this other video over here to learn how to do a discounted cash flow on a real company hit that like and that subscribe button if you liked it and i'll catch you in the next one