Discover Our Free Pro Forma Template Excel for Enterprises to Simplify Your Document Workflow

Free pro forma template excel for enterprises

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