Create a Cash Bill Format in Excel for Facilities with Ease

Cash bill format in excel for Facilities

[Music] hello there this is the bookkeeping Master on YouTube in this video I'm going to show you how to create a basic cash flow annual forecast um so to start off with let's merge those sales together to create our title cash flow forecast and we'll do 2014 to 2015 and I've made it in bold and underlined merge sales again products to you that's our business title put in a business title your business name column A we're going to make um slightly bigger than the rest about 13 to 14 there as we're going to have to put some wording in that column let's do April hyphen 14 the beginning of the tax year and drag that across to get our whole year obviously you can do January December or whatever your business year is if we highlight all the sales and click the center button just make everything look a bit more tidy if I'm going too fast so you just pause the video and go back you know you can watch this as many times as you like so in line five let's put in opening balance you might need to make column A a bit bigger if you're struggling to fit this wording in or make the text smaller excuse me next we're going to put money in couple of lines down and let's underline that cuz that's going to be a a sub heading for us so all money into the business we have owner's Capital to the money the owner is put into the business we can put bank loan sales and then other any other money that will be coming into the business and then we can put total money in and this is going to be a total box so let's format sales put a border in at the top so just click border click the where you want the border and click okay and your template should look like this I've made that all bold let's make all the opening balances bold too make them stand out if we highlight all the sales excuse me click format sales number accounting we can have the pound sign or the dollar sign to two decimal places this way we'll have money sales rather than just digit sales let's put in another subheading this time is do money out first of all we want to put in our liabilities um an rset so things like our loan repayment paying off that loan it seems like a a priority and then an asset goods for resale so I'm selling products so Goods I need to buy to sell on then equipment asset then other overheads we can put down the down the bottom here advertising website costs and so forth obviously put as many expenses and assets and liability rows as you like to fit your business I've added Merchant payments posted in stationary and then down the bottom here we want Total Money out we'll make that all bold and put a border in so once again format cells border put that across the top highlight the row and click the Bold button so your template should look like this let's put a formula in equals some Open Bracket add all the money in close bracket enter so that'll be our total money in and we can drag that across so everything's added up together and let's do the same for money out so equals some Open Bracket all the money out totals close bracket enter and if we drag that across then we won't won't have to do that formula for each month now let's put in close and balance make that bold and we can put in other border here so equals our opening balance plus our total money in minus our total money out so opening balance plus our money in minus our money out then just drag that across so do it for each month so opening balance for our first month this is a new business it's going to be zero for every other month it's going to be the closing balance of the month before obviously and then drag that across so let's start filling these sales in now it's best to do row by row you know do the whole year for each row instead of doing month by month so the owner's put £500 in and he's going to put £30 in let's say for the first six months so 1 2 3 four five six first six months of the business this is a new business so that's his plan bank loan I'm getting a ,000 bank loan let's just make these columns a bit bigger struggling for space you'll soon realize why I'm doing this row by row throughout the whole year instead of month by month so Sals we should have a sales forecast um I'm not going to go into this in this video um but we should have a sales forecast so say buy month three hoping to sell one unit which is 20 and then we going to go up to two units then three units so 60 four units 80 and then five units a month for our last two months that's our sales forecast and I can do another video at another time on how to to do a sales forecast and you'll see these totals are working all our total money in it's working well loan repayments we won't have any loan repayments the first month but say we have a fixed payment you'll get this schedule from the bank when you take the loan out and say our fixed amount is £38 a month and there we go that's that done goods for resale so this is what our loans for let's put 800 in there we're buy an 800 of stock and we're going to buy some equipment too where £450 now it's really good to explain what's going on where this money is going to so if you right click and click insert comment you can then add a comment so this £800 is say 100 units at 8 pound including postage obviously these figures are not figures we've plucked out of the air there they should be specific figures and there's reasoning behind them and we can put that reasoning by inserting comments putting notes on so right click insert comment so 450 of equipment is a laptop and printer and you'll see when we hover over the sales those comments appear and we'll remind us and you can edit comments just by right clicking and click edit comment you can adjust the size and the text obviously if you're going to show this to some sort of manager you're going to want reasoning behind the Valu you've values you've got so advertising is £8 a month say it's Google AdWords I'll put a comment in we have a fixed fee of 8 a month we're going to put into advertising our website we might have like a a six monthly fee with our website hosters or designers £ 35 say every six months like so and once again another comment could just write give some more details about this so I've just put the our website designers company name so Merchant fees this will be in line with our sales so say it's 50p per sale per 20 lound so those two months obviously going to have 50p and that's going to go up as we're selling two units and then three units so 150 four units2 with5 units which is £250 postage once again this will be variable depending on the units we're selling so it's £150 per unit so 150 then we're sell in two units and then three units so £450 4 units6 and five units which is £750 and then stationary £15 we can put a comment of what that is like printer paper pens pencils you know whatever you need to buy when that money is allocated to once again if I'm going too quick just pause and take this slow so all our comments are in you'll see that the spreadsheets working well we're getting Total Money outs and closing balances down at the bottom let's put in interest our loan interest of we've taken loan you should get a schedule from your bank about loan interest and depending on what loan you've got it will probably go down as your loan's being repaid so it's going down by 10 P each month so now that's in so everything's looking good you know we have positive amounts for our closing balances and our opening balances throughout the year so it suggests we have sufficient cash to get through our first year business if everything goes to to plan but say we need to buy some more equipment um you know let's give an example that the cash flow is not going to be that great £200 you'll see some minus figures now appearing down the bottom of the spreadsheet and at the top for our opening closing balances insert a comment say you have to buy some sort of scanner and photocopier some piece of equipment that could damage our cash flow okay so see all these minus figures show a negative cash flow so from doing our cash FL Floor cast we know we're not going to have enough cash um so it might be that we have to put more into advertising to increase sales and if we can have higher sales throughout the year our cash flow will be okay but it could be as simple as we need to set up an overdraft facility so we can survive throughout this first year of trading so I've put overdraft interest in and then it's going to be £5 a month every time we're in a minus balance like so like an overdraft fee Okay so we've started off with nothing we've ended the year with plus 860 obviously a cash flow forecast isn't a profit and loss statement but it's helping you to know that you're going to have sufficient cash over the The Next Period of trading this been a year but you could have as many much period as you like just to sure you have enough cash to get through that period I'm just hiding right click hiding hiding all those sales just so that opening balance total money in and total money out and closing balance is now showing you know might just be a bit handy to to break things down if you just wanted to have the the fundamental figures showing but thank you for watching if this is too complicated and you like us to do this for you just go over to our website . bpfs online.com and we can do it for a small fee we also offer online stage training probably the cheapest online £45 let's unhide these now but please you can contact if you need some help with this we'll be happy to to give you a hand