Get Your Free Pro Forma Template Excel for Accounting and Tax

Free pro forma template excel for Accounting and Tax

if you've got a fairly small and simple business that you'd like to do the bookkeeping for without having to spend money on software like QuickBooks or zero you might like to try our free bookkeeping spreadsheet template all you need to do is click on one of the links in the description below to download the template you want and then watch the rest of this video for instructions on how to use it there's links to both Microsoft Excel and Google Sheets versions of the template and you can also choose whether you want the us date format or the International Date format so there's separate links for each one of those options okay so I'll take you for a little tour of how the template works now I'll use the Excel version but the Google Sheets version works just the same way so you can watch this video no matter which version you want okay let's go into Excel now and see how it works okay I've got the Microsoft Excel version of the spreadsheet open and I'll just take you for a little tour so you know how to use it first of all you can see it's laid out with three tabs so there's the settings tab down the bottom there and then there's the transactions Tab and then there's the reports tab so we'll just start off over here in the settings tab and I'll just show you that what you do is you put your information into the yellow cells and that goes for the transactions tab as well just into the yellow cells so you can start off by putting your business name in there and when you put it in it it will show up at the top of the report over here and it's the same thing for the report name so some people like to call it the profit and loss report some people like income statement so you can just choose which one you want over there and that will flow through over here um up the top there and then finally you've got your date range over here as well so I've got a date range in there for One Financial year in Australia and then that date range will flow in to the top here but the date range is also important because there's some error checking over here in the transactions page that I'll show you a bit later that will help you to not enter dates that are outside of the date range it will actually alert you if you try to do that so make sure you get that date range right as well okay so the next thing you need to do is you've got these category types and category names over here and this is how you categorize your transactions and they are over here in your income statement so for example you've got trading income at the top there and we've got sales revenue and then refunds to customers so over here there's three places that you can put trading income category names over here there's the sales revenue and the refunds to customers and whatever you put in there in the yellow sales that's what will show up over here and it's the same thing for your cost of sales and it's the same thing for your expenses and if we scroll down it's also the same thing for other income and also taxes paid so if I just go back over to the settings tab you can see I've got those set up as category types over here and then also because sometimes you spend money on things that aren't uh for your profit and loss report they Capital purchases such as buying a motor vehicle or some computer hardware or something like that I've just got this Capital purchases report over here that you can use as well and if we go to settings you can see that you've got five places down there to put category names for your Capital purchases and just over here there's also instructions just telling you to enter the information that I put in and it also tells you how many category names over here that you've got for each category type just to help you out a bit over there okay so that's how these category names flow through over here to your report so you've got full control over what you want to call your different category names for your transactions the next thing you need to do is actually put your transactions in if I scroll to the top I've got a bit of instruction over here so you've got 1,000 lines to put transactions in so you've got a lot you can see that that just goes on and on down there like that up to 1,000 transaction lines the date that you put in must be within the date range on the settings tab as I said before so that's the date range over there and then the description can be anything you like the amount must be positive numbers so don't put your expenses in as negatives put them in as positives and then you need to choose a category name for each line so you can see over here each line when you go to choose your category name a little drop- down box comes up and that drop- down box comes from here it's all the category names over here okay so the best thing to do right now would be for me to actually just do an example for you so what I'll do is we'll come back over to settings and I'll just create a category name that's an expense type and I'll just call it legal fees and we'll see what happens to it so if we come over to reports and scroll down to where we've got all our expense categories over here you can see legal fees has come through now and there's no transactions in there yet so that's how that works whatever you type in over here you can change these if you like these are just in here for examples you can just put whatever you like in there you don't need to keep those in there and then they flow through over here and they're ready for you to put your transactions in so let's put a transaction in so we'll come over here I'll just scroll down a bit and I'll just type in a date and I'll just put the description in and then an amount okay and you can see when I put the amount in this little box has come up red and we've got this error message up here that says that there's an amount somewhere down there that has no category name and the little red box here helps you find where that is so when you've got up to 1,000 lines here these little red boxes over here here and here will help you to find where these error messages are coming from this one in particular is because we've put an amount in there and we don't have a category name so let's go and choose a category name so we want it to be our new one legal fees like that you can see the error message is gone it's $200 and if we come over to reports you can see that's flowed through there to legal fees okay so that's how it works that's how you can put in a category name put in a transaction and then it flows through to your report and that goes for everything that's not just expenses that's everything on here okay so one final thing that I want to show you you did see one error message come up I'll just show you a few others so if you type in a date that's outside of the date range so if I put that in the next year you can see we get a little date out of range error message up here so when you get the error message you just need to scroll down through your transactions and find any of these little red boxes and that will show you where the error actually is so for example if we had another one you can see we've still got the error message at the top and what we would do is we would scroll down here and see that these two need to be fixed so I'll just fix those like that and then the error message is gone okay and the last error message I wanted to show you is what happens if we actually rename a category name over here so if I call that legal costs instead like that come over here so you can see that this doesn't automatically change over here to Legal costs so if you change a category name you've got to go through and you've got to find all of your transactions and you've got to choose the right one again so we'd come down here and say legal costs and you can see the error message is gone now and that's come through over here as legal costs so that's why it's a good idea to make sure you've got these set up first and named correctly before you go and put a whole bunch of transactions in over here okay and then once you've got all your transactions in you'll see you've got a nice income statement or profit and loss report over here so it'll show you your gross profit which is your trading income minus your cost of sales and then you subtract expenses to get to your operating profit down here and then there's a spot for other income that's not your trading income so if you've got some interest income or something like that dividend income you can put that in here and then you've got taxes paid down here and your profit and loss after tax and you've also got your profit and loss before tax there okay and like I said earlier on you've also got a little Capital purchases report over here for things that you spend money on that are not for your income statement things like Motor Vehicles or computers etc so that's it for the tour of the template and like I said earlier you can go ahead and download whichever version you like without having to sign up to a mail list or anything like that so thanks for watching and I'll see you in another video