Collaborate on Excel Pro Forma Template for Operations with Ease Using airSlate SignNow

Excel pro forma template for Operations

welcome to made simple today we're going to be talking about how to create a pro forma income statement from scratch now pro forma income statement is basically an estimated income statement this is super useful in kind of getting idea of possible revenues or expenses or possible gross profit for an existing business for the future or maybe you're starting a new business either way it's really useful in kind of getting an idea of how that business will go right all right so the first thing you need starting a pro forma income statement is you need to know a couple of assumptions so in this example we're going to just say that we sell socks and we have three different types of socks that we sell in our business so we need to know so we'll just label these product one product two um and product three right so we need to know the dollars collected per unit um we need to know the sales tax collected on each item the revenue per unit or sales per unit we also need to know the cost per unit the gross profit per unit and the gross margin percentage here and an additional assumption that we're gonna need to know is weekly growth rates or um depending on how we do this income statement we're gonna do weekly or monthly um we'll just we'll just change this to actually monthly we'll do monthly growth rates all right so so here for here for our first product dollars collected let's say that we sell this type of sock for ten dollars and we're actually going to format this real quick so let's say we sell that first product for ten dollars the second for 12 and this one let's say 15 bucks all right so um let's just assume a six percent sales tax rate um per item a super simple mistake with sales tax is that you'll just take that six percent and times it by the ten dollars to get um the 60 cent um sales tax collected right but that would be incorrect so actually what you need to do is for revenue per unit you need to first calculate that so revenue per unit would be the ten dollars divided by one plus the six percent and you're going to want to um you're going to want to lock this as well and you go ahead and you get 9.43 of revenue per unit after taxes so basically what you do is you take the ten dollars minus the 943 whoops minus the 943 to get 57 cents instead of 60 cents now that is the correct way of calculating the sales tax per unit super common mistake again just make sure you're not taking the ten dollars times the six percent to get sixty cents all right and then what we can do is just um drag these formulas across since we locked that um and we can get the sales tax collected per per per product here all right so the next thing we need to assume is the cost per unit all right and let's just say to make i mean we're making socks so let's say it costs two dollars to make this sock 250 to make this sock and another 250 to make that sock right so here i'll highlight in yellow the the assumptions that you need to know the rest is kind of math right all right so the gross profit per unit is simply the revenue minus the cost per unit all right so per pair of socks that we sell our gross profit is 7.43 go ahead drag that across all right so then for our gross margin percentage we basically just want to take the gross profit divided by the revenue per unit and we'll drag this across changes to percentage and that's our gross margin percentage and these are pretty good margins especially in like a retail business you're not going to see these type of margins normally all right next we're going to want to know a monthly growth rate for each product and to keep it simple we're just going to keep it stagnant it's not going to change month to month we're just going to do a one year forecast so 12 months and it's going to have the same growth rate for each month let's just say we expect these stocks to grow 10 percent um product number two is going to grow 12 because we're marketing that a little bit more and six percent for this one let's say so now we have our assumptions now we need to do a forecast of the amount of units that we're going to sell per product so we'll go ahead and do product one product 2 and product 3. and then we're going to go ahead and put in the months here so we'll say this will be for month 1. month 2 and all the way to month 12. all right and then so this is kind of all assumed we need to just kind of predict for this first month how many stocks we're going to sell and then we'll use these growth rates to adjust this for the following months all right so for month one we basically just need to determine the uh we need to predict the units sold um based off of month one so for product one let's say we sell a thousand sub pairs of socks for product two let's say we sell 500 and we sell zero for product three because we don't have it yet but we're going to release product three in month three and let's say that third month we predict to sell 700 socks pairs of socks for product three all right so now what we can do now that we have our estimates we can take these growth rates and just take um the previous month and times that by the growth rate um and we'll go ahead and lock this as well um i will do that real quick for both of these for some reason all right and for this last one all right so then we can go ahead and drag these across and here we'll get some nasty decimals but we'll just kind of get rid of those um so here we have our predicted um and we're also going to make a totals a totals column over here so let's do the sum function for all of these and these are the amount of socks we will be selling um 42 677 pairs through our three different sock products throughout the year all right a lot of socks all right so now we can move on to the actual pro forma well the actual income statement um i'll go ahead and label that expand this so we're going to copy these again we'll just copy them down here all right so if you're familiar with income statements this will be pretty simple for you and if you're not just pay close attention and hopefully you'll kind of learn how to actually create an income statement and learn how to read certain things all right so um we have starting out we have our revenues um for each product we have our three products um go ahead and paste that there all right so we have our revenues for each product and basically what we're going to want to do for this we're going to want to go ahead and take the predicted sales of 1000 and we're just going to want to times that by the revenue per unit that we assumed above here and we're going to want to go ahead and lock that as well we'll get rid of the decimals here and we're just going to want to do that for each of these products um so 500 and we're going to want to times that by this revenue right go ahead and lock that again and for this last product we're going to want wanna go ahead and lock that again as well all right um and so we'll go ahead and drag this across um we're gonna go ahead and get rid of these decimals um and make that a little bit nicer to read all right so as you can see obviously we didn't sell any product three so no revenues for that product um and then we're gonna go ahead and write a totals column for each month and we'll just do the sum function of these and drag that across and then we're going to want to do a sum function here and there we go all right so here we have the revenues for our three products and the total revenues for the year of 46 468 000 close to half a million all right so after revenues what you're going to want to do is you're going to want a new cost of goods sold or cogs for the three products again and we're gonna go ahead and paste that and then for month one basically we do something similar to what we did up here we take the cost per unit and we're gonna wanna again lock this lock this cell and we're just going to want to times that by again the predicted units sold for that month all right and we're going to want to do that for each product um and then sum these again all right um so just doing that real quick again to kind of show you you go ahead and lock this and times that by the product number two and you do the same for this product as well and lock that cell all right so by locking the cells up a locking that cell it allows us to drag this across um and kind of get the totals here so we're going to go ahead and get rid of all these decimals again um and just do the sum function across here again all right um perfect so we have our cost of goods sold we have our revenues and now the next step of the income statement is the gross profit and the gross margin percentage all right well hopefully you guys are still here it's getting a little bit boring i know but super important stuff especially when starting a new business or just being able to read income statements is is important all right make sure you hit that subscribe button it really helps me out smash that like button add a comment if you like this video or if you have any questions please let me know alright so moving on here gross profit is just the revenues minus the the cost of goods sold for the total of the three products and we have a gross profit of almost 12 000 right and so then our gross profit margin is you take the gross profit and you divide it by the revenues um and you get a percentage here and we'll go ahead and drag these across and sum this up and this is our gross predicted gross profit for the year not too bad all right just selling socks all right so now we get on to the more um assumptive part of an income statement if you're doing a pro forma and this is the expenses right so we need to know the operating expenses here um and these can come in tons of different ways we're just going to cover a couple that's not going to include all of operating expenses in a normal business we're just going to list a few here to kind of get you guys a good idea of that so we have marketing ex we have the marketing expense we have um research and development you know we gotta find out we got labor um let's say we um we also rent a building so we have a rent expense here and generally here you would also include a depreciation expense and this gets a little bit more complicated all right so depreciation gets a little bit tricky because you need to have information on your balance sheet which we're not going to be covering in this video so we're just going to leave depreciation blank for now but hopefully in a future video i'll do a pro forma balance sheet statement using the same income statement and we'll be able to calculate depreciation all right so we'll just leave that here but put that as zero um so let's say for our marketing we're going to spend let's say 300 bucks a month on marketing um and and all of this is kind of assumptive right so you just kind of predict what you think this will be to your best estimate um i'll go ahead and put this in in currency here um we're sorry we have the rest in accounting so we'll go ahead and put this in accounting format um and so research and development let's say that's not a huge thing so we're just going to spend like 30 bucks a month you know testing new socks and and doing that all right so with labor here we're just going to create a new worksheet real quick to kind of go through some simple math for um to predict our labor so a way we could do this let's just say we pay our employees 15 an hour um and we have two employees running the store um they work monday let's just say monday through saturday we'll be closed on sundays um so they work 48 hours a week we have more than two employees but we only have two employees at a time so 48 hours so we'll do 48 hours times two um eight hours a day um just to keep this simple so we'll do 15 times 96 so they're we're paying about 14 um 40 in wage salaries and wages or in labor expense per um per week so we'll take that and times that by four to get about 5 5760 in labor per per month so we'll go ahead and put that down here um rent let's say that's pretty fixed we're paying let's just say a thousand dollars in rent um not a hundred one thousand and not ten thousand um we're paying one thousand dollars in rent per month all right so we're also going to say each month our marketing increases by five percent all right so we're gonna take 300 times this five percent um and we're going to want to go ahead and lock this as well oh whoops we're going to do one plus here all right um our research and development will say that stays the same labor stays the same rent stays the same so we're gonna go ahead and say that that equals this um month two month three and so on right and then we can just go ahead and drag this across and then for our total expenses we'll just do another sum function of these um and get that and we'll go ahead and make a total column here for total operating expenses here and that's just going to be the sum function of all of our expenses dragged across right all right so in total we're spending eighty six thousand dollars in order to get all these socks sold for our operating expenses all right so now we're going to want to know our opera our operating profit which is also known as ebit ebit however you want to pronounce that if i can spell and that is just earnings before interest and taxes that's what that stands for all right so also very simple you just take our previous gross profit and you subtract our total operating expenses to get our operating profit so this is how much operations after our operating expenses how much money we're bringing in after we pay for all of our expenses right all right so then after finding our our operating profit we're going to want to know our operating margin um percentage as well and that's just taking this our operating profit divided by the total revenue which is here for month one um and we can drag this across get a percentage here all right so we're almost done i know if you're sticking with me i hope you're still here um super important but make sure you subscribe hit that like button please do that for me all right so then we have our uh our interest expense we're almost done the income tax expense and then we will be able to find our net income which is the goal here all right so for our interest expense um we're going to say that we haven't we not going to mess with that right now because we need to kind of dive into the balance sheet so when i talk about the balance the pro forma balance sheet in an upcoming video we'll be able to kind of understand this a little bit more but as the interest expense for now we're just going to say that is 0 all right our income tax expense let's just say um to keep it simple we'll just say thirty percent all right all right so far interest expense we're just going to go ahead hit our operating profit and multiply that by the interest expense obviously we know that's going to be zero because we said zero percent all right but we can do the sum formula across here for the year and so this is just the total column for the year all right so for our income tax expense we just want to take our operating profit and we want to times that by the 30 percent make sure again to lock this super important all right and then we're going to drag this across go ahead get rid of those decimals and get another sum here all right so now we calculate what we want right our net income so basically that's just taking our operating profit you subtract interest expense and you subtract the income tax expense and that gets you our net income and we'll go ahead and do that do a sum function and voila after all of our expenses we will make around two hundred thousand dollars as a business in net income selling socks voila all right so this is a hopefully a detailed enough overview of how to make a pro forma income statement hopefully this is helpful kind of gets you to understand what revenues cost of goods sold how to calculate profit where expenses come in and everything needed to get to your net income make sure you subscribe hit that like button hopefully you were able to stay tuned for this whole video and stay tuned for the upcoming pro forma balance sheet video that we'll be releasing soon