Collaborate on Monthly Bills Template for Organizations with Ease Using airSlate SignNow

Monthly bills template for organizations

hi everyone Eric here today we're gonna build a monthly budgeting model for a company first I'll show you how to create a template to give out internally so that people can submit budgets to you then I'll show you how to integrate that budget into a full monthly forecasting financial model also feel free to download the monthly budgeting excel template in the description below if you want to follow along ok let's start by creating a template that a department head or someone internally could build their budget in that they can give right back to us so first off we will just name this budget template and we're going to start with a few things so we want this to have a couple pieces of information they're important we want to have department of course department then account so this is the expense and description with these three pieces of information anyone could basically you know submit us line out up and say ok I'm in marketing the account is consultants and the description is we need a search engine consultant in March to do a project then we need to basically put in you know 12 months generally you're budgeting on a 12 month cycle or sometimes shorter but at least for this model we will use 12 months we can Center this up here we can put some underlines if you highlight these three and double click the edge of a column it automatically spaces it ok but now here's a great trick so we want to create a drop-down list so that each person can just choose an item from a drop-down so let me show you what I mean this is something we're going to do with the function called the data validation function in the data tab so we're going to create a data validation drop-down now one of our dropdowns is going to be department as we saw because we want to create one template for everyone and then just send the same file out to everyone so we're gonna have one for Department in one for account which is the type of expense so let's say we have marketing engineering and let's just zoom in a little so that we can make sure that we're getting a good look here IT customer service customer service human resources sources finance / accounting and executive and then for the accounts let's say we have our personnel paid advertising consultants travel lodging so that's like hotels meals and entertainment Internet software and maybe miscellaneous okay so check this out here's how you create a drop-down in your budget template so you first need to go to wherever you want the drop-down to be so let's say you want to create a budget drop-down for department so make your active cell in this square now go to data finally data validation and then when you click this you want to select list you're going to create a validation criteria list and this source is going to be this list over here of departments so just highlight that list and then just click OK now you'll see that when you click this box you can actually select if you just copy and paste this down you can create this drop-down list where you can just select the department so when you send this out people can just select their own Department finally let's do the same for expenses so active cell here where we want the list now go to data validation again now here we want lists and if we go to the data validation drop-down and we just highlight this list and click ok now we have a drop-down list so if you pass this out to customer service they can just copy their department down and then they can basically copy down this other list and they can just select ok this is for meals this is for this and then you always want descriptions so that when you're looking through this data later on they can say ok you know this was for the you know dinner with you know I don't know managers in March bla bla bla so that you can know going forward as you actually go through the year what people are planning to do ok so now you have a template that you can send out to departments and then they can send it back to you filled out with their annual budgets so in our example I've created a submitted budget where I've consolidated all the departments all the accounts and I put in some data here so now I'm going to show you how to integrate these budgets easily with a quick formula into a full monthly forecasting model so let's say we have the page where we have our income statement income statement and we know that we have these different accounts ctrl C copy and we'll just copy them over here of course we are following a 12-month period so I'm going to hit shift ctrl right arrow ctrl C that's copy ctrl page up and here ctrl V ok and I'm going to build the rest of the P&L but first I'll show you how to pull that budget in with a really quick formula operating expenses so in this case this is going to be a sum if formula so we'll go equal let's zoom in a little here we'll equal sum if first thing is range okay so the range is basically all the data that we're going to look at to pull the data from so if we go to our submitted budget we can look at range and we're looking at the account name so we are going to start here and we are going to hold shift and go all the way down you could also do this with your mouse but I like to use Excel without the mouse in this case you can see we've highlighted b2 to be 33 but we need to lock these cells what does that mean this means that we're going to put dollar signs in front of the cells you can do this with f4 and these dollar signs will prevent these cells from moving so if we hit copy and paste the formula somewhere else it will still be locked this section be to be 33 no matter where we copy the formula so now we're going to hit the comma and the next part of the formula is going to be criteria okay so within that group of data what are we looking for we are looking for this account name and in this case we're going to lock the column if you need help with locked cell references and and more about this inside of a formula I'll link one of my other videos below that does a deeper explanation but this will prevent us even if we copy the formula to the right it'll prevent the column from moving okay and then the third part of the formula we'll hit comma we see is the sum range so where are we actually trying to sum so if we go back to the budget we will go up to the top control up arrow and we will highlight this column just this one column because this is January and we're trying to grab January in this case we'll hit f4 a few times and we want to lock the row row 2 through 33 not the column because as we paste it to the right we're gonna want it to actually move column by column so here's what I mean we'll hit enter now and you'll see that when we paste this to the right it actually grabs each month so 35 just to spot-check this in June if we go back to this budget that if we go to here in human resources oh let's Lock freeze these panes freeze panes okay so if we go to June you can see that we actually have 35,000 so we know that that formula is working but the great thing about this formula is that we can copy this formula because we locked the cell references correctly across this entire thing and we will grab all of the data so that's how you can give people a budget and pull it into the pl and now let's build out the rest of this monthly forecasting model so first I want to go to view freeze panes freeze panes this will keep our basically first row locked so that we can go and do whatever we want now we're going to have a few sections we're obviously going to have revenue new and returning why new and returning because I'm gonna build a model that represents us having a subscription business where each month customers are ordering a product and some of them cancel each month but some of them continue on the subscription and then keep buying so new returning and we'll have net revenue and what else will we have here we will have cost of goods sold okay cost of goods sold and let's insert a few more rows here we will have keep it simple we'll have product cost and merchant services and merchant services are just basically the fees that the credit card company charge and so businesses have to pay that basically on all their revenue unfortunately and that's total CEO gs then you're gonna have a gross margin and gross margin percent which will make italicize and then down below we'll have operating income taxes net income net income net income so this is a basic monthly forecasting model net income percent and now we just need to fill it out with some more assumptions here here we'll put in total total operating expenses so let's go down below so we're going to need to create an assumption with every financial model you need to have a section of assumptions and this is where you put all of your inputs like price and units sold and all this sort of stuff and you put it in one spot so that you can play around with the model so if we're thinking about assumptions let's start with units or let's start with revenue the revenue section so we're going to need our units and new as well as returning and we can indent those slightly just to kind of make them pop out and then we'll have total so how would you calculate the number of units do we have any information here that we can use absolutely we have paid advertising from the amount that we spend in advertising we should generate a certain amount of sales so down here I'm going to create a marketing section and this is going to be called acquisition for customer acquisition and so we'll have our paid advertising number of course and then we have the we're gonna put in a customer acquisition cost customer acquisition cost customer acquisition cost is the amount that you invest in marketing to get one new customer so if we spend a hundred dollars and we got two customers it would be $50 customer acquisition so customer acquisition cost in this case we'll say is $75 and every assumption that you do like this you always want to put it in a nice dark blue that is a industry standard format across business so make sure that assumptions are always in blue formulas are always in black so $75 then we're going to put a churn rate this is the percent of people that cancel every single month and so we are going to put that as let's say 15% ideally you want it to be as low as possible so 15% and so with these two things let's just extend these out whoops 15% we will be able to calculate the number of units so take a look new units so if we have our marketing paid advertising of $7,500 and it costs us $75 to get one new customer if you take 7,500 and divided by 75 that is 100 customers so in this case our new customers each month we can just use the marketing budget to calculate that but returning customers here's where we are going to use the turn rate so we're going to say okay of our let's say customers last month that would be new and returning the total customers 15% of them cancelled so that's going to be 1 minus 15 so it's going to be 85% returned so that'll be 85 out of 100 of course but in the next month it's the full 100 plus 85 and so 85% of these two groups basically returned in the next month so if we forecast this forward then you'll get the total customers and here I'll just put a quick bottom border and then we'll have number of units okay so next we will need to calculate the the revenue so we have the units but now we need the price and let's just say that our price is in this case it's two hundred and fifty dollars a month so we have two hundred and $50 a month price and next we have let's put in one row here we're gonna have our cost of goods sold so we're gonna have Co Co GS and we only have product cost and merchant services merchant merchant with an e and in this case we're going to use percent of revenue just to make things easy so we're gonna say that the product cost is thirty percent of revenue and the merchant services are three percent of revenue which is pretty standard three and so at this point we can pretty much calculate everything probably at the very end here we'll need a tax rate and we'll say that the tax rate is twenty-one percent which is what it currently is in the United States okay so let's finally just copy these over and then we can fill in our income statement really quick here build the monthly forecasting model with the integrated budget so let's go up to the top so new revenue new we're gonna take our new units here 100 times price and in this case we're going to lock the row because we're gonna use this formula again for returning okay so 25,000 next we'll copy that down to returning you'll see in this case that you get the sell but because we locked the row it still times 250 so that's perfect so we can take this formula and we can copy it all the way across in this case I will format our financial statement at the very end and I'll show you you know basically some industry standard formats so let's just put a quick bottom border here now we know that our net revenue is right here and we can just copy that across control C shift right arrow control V okay next product cost so with product costs we know this is a percent of revenue so we'll take revenue and let's hit F for a few times to lock the row times product cost so where's the product cost okay here it is 30% okay so we have our product cost and now we have merchant services but we can just copy the formula right down and it and it will do all the work for us okay now we can put the bottom border on and we can sum up these two cells and we can copy this cross great always make sure to save your work okay next we're going to take we're going to calculate our gross margin take revenue subtract your cost of goods sold we have the gross margin pretty easy let's take that and divide it by revenue if you want to get a gross margin percent and again I will calculate I will do all the formatting at the end here so you can get sort of an idea of what is sort of real financial model for formatting look like across the whole income statement and then finally here at the bottom we will total up all of our operating expenses so some grab this stuff total it across operating income is going to be our gross margin minus operating expenses and then our net income well in this scenario it's going to be operating income minus taxes but let's take a look at if we even did generate any operating income for the year so let's create one last column here if you highlight this column can hold control highlight the other column you can adjust them at the same time and and we'll just create a total column this will represent the whole year so we're gonna sum across basically all of these different areas and so that we can get everything basically in one place so there's our gross margin and we're just summing everything sideways and finally our operating profit okay and so if you look at our operating profit we pay taxes we don't pay taxes on a monthly basis companies pay on an annual basis so you'll take the operating profit and you multiply that by you know we'll make it a negative number and we'll multiply that by the taxes to the tax rate let's say twenty one percent okay so taxes are going to be that so let's do some formatting because this model looks horrible the first thing that I want to do is hit the comma style but then reduce the amount of decimals so we can make these little skinnier probably so now we have that another thing about financial models is in the first row of each section you want to put dollar signs so you can hit the accounting number sign but again reduce the decimals you don't want to see you know 25 cents because that it really doesn't make the model look good it makes it look really messy and it doesn't help you so at the top of sections in the bottom of sections generally you're going to put the dollar sign so we may need to adjust these columns a little more so here again top of the section dollar sign adjust the columns and one trick for this is that if you just highlight a section that already has the format that you want you can click format painter and you can highlight another part and then yeah it's all done so the gross margin again you know we're gonna want to put dollar signs on that because it's an important metric and usually when you have these sort of calculations like gross margin or operating profit you'll put a top and double border on it just sort of call it out and then the percent you see gross margin percent and again this section you want the dollar sign on it because it's the top part of a new section so we'll just do format painter here looks like this is missing a little bottom border and here you know again you want some more formats on this and so now you can see that our total operating expenses are a little over a million total gross margin is 1.3 million total revenue of about 2 million then you have your operating income and your net income so again we'll do the same thing here and your net income will be the operating income plus this taxes so your final net income for the year is 208 so the business did actually turn a profit one quick thing to note is that generally you know you want to give budget templates out to departments of course but in a normal model you would try to figure out okay what is the revenue and gross profit that we can generate you know on a certain marketing spend because then if you know your you have at least a forecast for the amount of gross profit then you can give at but give departments out budgets and you can say okay you know Human Resources you have 200,000 and engineering you have 50,000 because you need to give them guidance on sort of what the budget of the company is because if we only have 1.3 million of total gross profit you know we can't spend 10 million dollars in the operating expenses so I did this little backwards but I hope this content is really really valuable for you if it was please subscribe to my channel leave me a comment if you have any questions or just give me a like if you want to learn more about excel I have some courses I have a course about mastering Excel for business I have another course about learning keyboard shortcuts in Mouse for Excel I have discounts in the description below and thank you very much for watching again if you want to download the file I also have that in the description below download it for free use it for your own company thanks for watching