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

Free pro forma template excel for Finance

and welcome back to made simple today we're going to be talking a little bit about the pro forma balance sheet in the previous video we had gone over this income statement uh a pro forma income statement and we're going to be using that to create the pro forma balance sheet before again please subscribe smash that like button i would really appreciate that so with a balance sheet we're talking about assets liabilities and equity an important thing to note is that assets always equal liabilities plus equity so those two always need to equal each other remember that moving forward so we're going to start out here with assets and the first asset we're going to be talking about is going to be cash then we're going to talk a little bit about accounts receivable inventory if i could spell inventory property plant and equipment or pp and e and the last thing we're going to put in the assets column is accumulated depreciation and this can be really tricky so we're going to talk pretty in-depth about depreciation um i think it's more of it's one of the more confusing topics within the balance sheet or items it's one of the more confusing items within the balance sheet all right so starting out here with cash again here we have this blank column and then we have these months we'll just take this we'll copy that and we'll just put those here and for now we're going to leave this cash column blank or this cash row blank for within our balance sheet and we're going to come back to that at the end all right so when doing since we don't have any actual data since this is a pro forma balance sheet we need to make a few assumptions with accounts receivable as well as with inventory so with accounts receivable we need to determine how many days it takes for us to receive that cash or to turn over this accounts receivable so accounts receivable is basically like saying well we're you know a lot of people don't pay in cash um and they might you know pay with a credit card or something like that and that takes a few days to transact so we don't get the money or that revenue right away and if you remember from the income statement previously we use the examples of selling stocks right and and stocks are pretty cheap you know it's not a huge expense so we're going to say that our accounts receivable is or that turnover is going to be pretty low and we'll say that it's you know let's say five days and then our inventory we need to know how many days our inventory is in stock on general so for inventory we need to know how many days that our inventory sits in a warehouse or or wherever it is before it's sold and so these are socks let's say that you know we sell them pretty quick so we don't keep a huge inventory but let's say that takes an average of let's just say 10 days for that inventory to to be sold so here's where the calculations come to play for this first month we're gonna do five or you know five days to receive that that payment or that accounts receivable right we're gonna take that and we're gonna times that um in parentheses by using our revenue for that month that we determined in our income statement so that total revenue for the first month but that's in months so we need to put that into units of days right so we're going to divide that by 30 to get the average per day and then we're going to times it by 5 days to get this and we're going to do the same thing here for inventory all right so we're going to calculate inventory in a similar way but we're going to do it by taking that 10 and we're actually going to lock that and we're going to times that by the next month's cost of goods sold so month 2 and then we're going to times that or divide that by 30 to put it in units of days and i'm going to go ahead and lock this one as well so we can drag it across here and we'll just drag it across and we will sum up this and drag that down as well all right so for property plant equipment this is kind of just a guess but since we're selling socks you know and our products aren't super expensive we're not we're gonna need minimal equipment let's say we you know we put logos on the socks or something so we're gonna need like a stamp to put our logos um and we're gonna need you know that press um and maybe we have you know something that puts tags on the socks or something like that but what we could do is you know go back to our third sheet here and i've kind of already set it up but we have you know a label maker for the socks and then other you know that could be the stamp or or to put tags on or something like that so let's say that our total property plan equipment is 550 dollars all right um and we're just going to use that in month one and that's just going to remain the same because we're not adding to it every month for the total there all right so here with accumulated depreciation it gets a little tricky and we're going to use a formula so slow the video down and stay with me on this one but before you before we go into that please subscribe smash that like button again i would really appreciate that so accumulated depreciation what you would do we're going to use an if statement actually here um and we're going to do a logical test so depreciation is based off our property plant equipment and what depreciation is is it's basically saying you know my my equipment that i bought isn't going to last forever and eventually it's not going to be worth anything so and it's kind of a made up number and so we need to determine the useful life of our property plan equipment and it makes things simple let's just say that the useful life of our equipment is 12 months so then at the end of 12 months we will hit zero dollars or essentially 550 dollars and that equipment will be on paper worthless even if it's still working or not it will have been depreciated fully so the logical test will be the previous month depreciation if that is less than the property plan equipment amount then we're going to continue on to our calculations but we are going to lock that cell so if that's true what we're going to do is take the property plan equipment again lock that divided by the useful life which we said was 12 months plus last month's depreciation all right and if it's false so if it's already reached 550 we're just going to display the 550 and we'll go ahead and lock that hit enter and you'll see let's do some quick math here you know if we take 550 divided by 12 that's rounded up that's 46 dollars a month right for our accumulated depreciation so we drag this out and we know that we did it correctly because here on month 12 we have 550 and we said it would fully depreciate in 12 months so let's say for example that you know our useful life is actually 10 months we'll go ahead and try that so our depreciation is going to our accumulated depreciation is going to increase per month but you'll see here at month 10 is when we reach our depreciation and then the next months we just level out there but we'll go ahead and switch that back to um undo that and switch it back to 12 months so moving on these are all the assets we're going to be talking about today if you had questions of these please leave a comment and i'll try to explain the best i can but here we have total assets so now we need to sum up our total assets but something that's tricky we should probably type this in here is you subtract the depreciation so technically this is negative number right so it's less the accumulated depreciation so what we're going to do is total assets we're going to sum those and then we are going to subtract the accumulated depreciation and go ahead and drag that across all right and i'm going to put this in italics here now we move on to our liabilities and stakeholders or shareholders equity right and the first account we're going to talk about here is accounts payable and this is another one we need to make another um assumption like we did with the accounts receivable inventory so accounts payable is basically saying like hey we owe people money but we're not paying it right away you know we we have time to pay that back for maybe our supplier or or something like that so let's say that on average we take let's just say same with our accounts receivable we'll take we it takes us five days to pay back what we owe all right and the formula for this is gonna be the so in quotes what we're gonna do is we're gonna take this number that we just assumed lock that and we're gonna divide it by 30 days in a month and then we're just going to multiply that by this month's total cost of goods sold or total cogs here all right and then we can go ahead and and then we can go ahead and drag this across um bring this down delete those all right so then moving on from accounts payable we're gonna do a couple other payables wages payable rent payable interest payable income tax payable long term debt and then that will get us to our total liabilities so with wages payable what we're going to do is take the previous month um and since we don't have months there we're just going to take this month since this is the first month and we're just going to take from our income statement the labor all right and then what we're going to do is take the previous month's labor or wages payable plus this month's labor and we're just going to continue to do that for every month and so with rent payable we're going to do a very similar thing here from our income statement we're just going to take last month's rent payable plus this month's rent expense and we will just drag that across as well interest payable we're going to hold off from doing that for now but let's just say on our interest let's just say that on our long-term debt our interest is let's just say five percent and then we will come back to that so income tax payable is also the same so go to our income tax expense and then we will take our income tax payable plus this month's income tax expense and we can drag that across and actually um realizing now i made a little bit of a mistake these this is not correct this totals column i generally will just be that since this is kind of more like a running tally it's it's generally this is just this 12 months so we're just going to delete this column in general um so then our long term debt we're going to come back to that as well and we need to total our liabilities so we just add all of these up and we can drag that across as well all right so then for our equity section um we have paid-in capital we have retained earnings or re as well and then what we're going to do is total those for our total equity for this example we're just going to assume paid in capital as zero um for a pro forma balance sheet not super important especially in this example so our retained earnings um since this is the first month we're just going to select that net income but the formula for retained earnings is the previous month's net income or retained earnings plus this month's net income and then totaling these super simple whoops we just total these two rows and voila hopefully you're sticking with us if you're still here please subscribe smash that like button um we're almost through the pro forma balance sheet here so then this last column will be the total liabilities and shareholders equity and that is simply calculated by the total equity plus the total liabilities that we we had there and there we go our total liabilities and equity but as you'll recall looking back at our total assets it's a lot lower than our total liabilities and equity if you recall in the very beginning of the video i mentioned that assets always equal total liabilities and equity so and but you'll also remember that we've left a couple of these columns blank interest payable long-term debt as well as our cash here so let's go back let's say to start our business i mean we needed 550 for the property plan equipment let's just say we took out a 1200 loan um and that will just remain the same we'll just take that one-time loan before this this first year all right so we have our long-term debt so interest payable is a little tricky now you might think that interest payable oh it's just the interest i'm paying on my my loan on my debt well interest if you recall here in the the income statement we did the interest expense we left at zero percent just to reduce complications but that is calculated off of our operating profit and so this actually needs to be zero percent um to keep everything universal or the same throughout both both balance sheet and income statement so this essentially is the same formula is the same formula as these payables that we did essentially it's you know the last month's interest payable plus this month's interest expense but we know that this will just be zero all the way across and so i saved this for now just to kind of explain the correlation that it is not correlated with your long-term debt but rather with your operating profit all right so now that we have our long-term debt we'll notice that our total liability and equity increased even more and it's much difference than our cash here so what we're gonna do is we're gonna down here we're gonna make a um just a little a little row called difference so what we're gonna do is take the total liabilities and equity and we're going to subtract that from total assets and that's going to give us this number and what we're going to do is then put this these numbers into our cash so essentially we want this difference to equal zero and then we know that that uh liabilities equal assets well liabilities and equity equal assets so we're going to put that 9036 up here in the cash column and we see that i did my formula incorrect and forgot to include that line so we'll go ahead and do that and drag this across again and we'll see down here that it comes to zero so then basically you just need to do that for each month this is kind of the most time consuming part because you manually have to enter it but stay with me while i speed it up and plug these numbers in all right so we have filled in this whole cash column and this and then we get get zeros here and some of these are in parentheses because there's a couple decimals i didn't bother with the decimals i just put in nice whole numbers here um so we'll see that we have quite a lot of cash that we could be using to maybe use in other areas so that's why this pro forma balance sheet is super useful in conjunction with the income statement because you can see well i have all this cash instead of holding on to it where can i put that into maybe i'm going to increase r d or maybe i'll increase labor and so what you can do is essentially you know play around with it maybe i want to increase r ninety dollars a month you know and then i have to go back and change this number but i'll have less cash right so as you can see from this example i mean we we pretty good we do pretty good selling socks all right so that is the pro forma balance sheet in a nutshell if you have questions please leave a comment and before you leave subscribe smash that like button thank you much and have a great rest of your day bye