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