top of page

How To Do Bookkeeping for Small Business the Easy Way! (Excel Template) PART 1




TRANSCRIPT OF VIDEO


It's time to say goodbye to that box full of receipts.


It's time to say goodbye to stressing over your tax obligations.


And it's time to say goodbye to expensive bookkeeping or accountant's fees.


. . . And it's time to say hello to the bookkeeper and responsible business owner within your lovely self.


And, of course, who doesn't want to outsource their bookkeeping.


But the thing is …. it is empirically proven that FIRMS WHO MAKE A PROFIT BEFORE THEY GROW, IMPROVE THEIR CHANCES OF LONG-TERM BUSINESS SUCCESS. And how will you truly know if you're running at a profit (or loss) if you're not all over your bookkeeping all the time?


My bookkeeping spreadsheet is designed to be fun and quick, and easy to use, too (unlike expensive accounting software subscription systems).

"WOW!! Karley, you have made my life so much easier!!I honestly didn't know where to begin & you have now taken away one less stress in running my new business." Melissa Bingham via YouTube

In this instructional video, I teach you how to become operationally efficient in tracking your profit or loss by the month, the Quarter, and the year, so you not only meet your tax obligations but also know for sure if you're MAKING A PROFIT BEFORE YOU GROW.


In the first part of the video, I show you the layout of the bookkeeping spreadsheet. Then I explain how to edit the spreadsheet to suit your personal business needs. Then I demonstrate how to edit the sheets to suit your individual business. I show how easy it is to input your sales and expenses into the spreadsheets so you can see how easy it is for you to be your own bookkeeper. Then I will show you how to quickly see your profit and loss and tax obligations. These spreadsheets are suitable for product-based businesses and service-based businesses.


Okay, let's begin.


When you download the Excel spreadsheet, you will land on the Notes page. When you look down to the bottom left-hand corner, you will see the Notes tab: then to its right is Quarter One, Two, Three and Four. And the last sheet is the Year to Date Summary. This last sheet summarises your sales and expenses. These are automatically calculated from the four Quarters.


Now we're going to edit the document to suit your needs in your business. But before we go on to editing, I need you to save this original document as your own. So go to file in the top left-hand corner of your computer screen, click on Save As, and save this spreadsheet within a folder called "Bookkeeping" on your computer. This way, you'll have this original spreadsheet saved to your Downloads, plus you'll also have your own spreadsheet within your folders.


Let's click on Quarter One. At the top of the sheet, we have our income section. The following section down is for our expenses. Then at the bottom of the page is a summary of your sales and expenses. And in this bottom section, we have your total GST obligations too. And then, in this cell here, it automatically calculates for you the amount you owe to the Australian tax office or the amount that is owed to you.


When we jumped to the Year to Date Summary, we will be able to see, at a glance, whether we're running at a profit and loss.


INPUTTING YOUR SALES

The first thing you need to do is go into Quarter One. The first thing I would do would be to drag your logo onto this sheet because it's always nice to see your own branding.


To help you fill in your spreadsheet, I'm going to enter income or sales for a pretend business so you can see clearly how the GST is calculated. Please follow along as I quickly enter in some mock sales.


Now let me digress for a moment and picking up the topic of making more training tutorials. I need you to be aware, please, that I am not a bookkeeper. Bookkeeping is simply a skill that I have picked up over the years. So I am not trained in bookkeeping. Oh, hang on a minute. I did a bookkeeping course at Tafe many years ago because I wanted that skill to get temp work when travelling through England. Okay, I'm getting off-topic here. What I'm trying to get at is that I'm a Marketer. And if there is one thing that I know within marketing, you've got to know your numbers. So if you would like me to create video tutorials on: how to calculate channel margins; how to calculate the cost of goods; or I could even do a conjoint analysis for you to work out which product offering your customers are more likely to buy. I can even do video pieces of training on breakeven point. The list goes on. So if you require that for your lovely small business, please do let me know. And I'm happy to create those videos for you.


Okay. Let's go in and just delete these rows because we want it to look nice for you. And now we can see the total of July sales, August sales, and September sales with the GST calculated automatically for you.


And if we scroll down the bottom to our BAS Reconciliation, we can see that our total sales are $103,000. And the amount that we owe the tax department for our GST collected is just over $10,000. But we haven't put it in our purchases yet. Have we? No, no, no. Okay. Let's pop on over to our Year to Date Summary. And if we scroll down to our Profit & Loss Statement, so we can clearly see our cashflow (because this is the whole aim of doing this bookwork) ... so we know how much cash at bank we have at any one time, we can see here that we have made a profit of over $103,000. And of course, we haven't done our expenses yet, but by showing you the total income of July, August, and September, you can see at a glance how much you've made in each month. And then you can reflect back on these numbers and say, well, what happened in September? Why did we make so much? And why didn't we make much money in August? We can look back at our marketing activities, work out what worked and what didn't work, which will help guide our decisions in the future.


It's time now to get out that big box of receipts. And I need for you to access all your bank statements for the previous year, going back to the 1st of July. Print off the bank statements and, with a highlighter, highlight the months and put them in order in a manila folder.


You should say, "well done you" to yourself because you're one step closer to finishing your bookkeeping!!


Let's have another look at Quarter one (July, August, September) and let's scroll down to the expenses section. The first thing to do, to save you time, is .... I want you to think of all your reoccurring monthly expenses and write them up quickly in column B. I'm just going to be quiet here now. And I'm going to fill in some expenses for a made-up business. Let's just say it's ... a ..... okay, I know ..... I have a home business. I mean, I work from home. I have an e-commerce store on, say, Shopify and I rent my home. So that's sort of the basic things about me. Okay. I'm just going to input those recurring monthly expenses associated with my pretend business.


Now I'm going to input the amounts in the different columns. It is recommended you don't insert more expenditure columns. There are enough columns already created here for you. So instead of adding in more columns, simply rename the columns to meet your needs and enter the gross amounts. Please note that the gross amounts include the GST paid ... which is one 11th of the gross amount. The GST is calculated for you in this column.


Here you will see the rent I paid. I've said rent is 10%. by way of an example because my pretend business is run from a home office .... and I'm renting. I've calculated that I use 10% of my floor space for my home office and storage for all those widgets I'm selling on Shopify. So, therefore, I'll claim 10% of my gas and electricity too. So over here, let's say I pay $5,000 a month in home rent. So I'm claiming 10% of that as a home office percentage of expenses. Now with my utilities, I'll do the same. Let's just say it's a hundred dollars a month for my utilities. So I'll claim $10. When I input my public liability insurance ... let's put what I pay in the insurances column, and let's say it's $250. I'll pop the market store fee in miscellaneous. The market stall fee is $130. I have a business loan, so I could buy all that stock and I'm going to put that in miscellaneous too. My business loan repayment is, say, $3,000 a month.


Let's go over now to column T and our GST. Even if you're not registered for GST, and you are not required to submit a Business Activity Statement every Quarter, you still pay GST on your purchases, right? But not all of them. So for those expenses, you need to go into the GST column and delete the calculation within that cell. For example, repayment of a business loan, there is no GST on that. So I'll click on the GST column, delete the equation and insert in a zero.


If you're unsure whether or not GST is included in the purchase, just have a look at the invoice. If the GST on the invoice is different from what these spreadsheets have calculated it to be, firstly, double-check that invoice and then go into the GST cell and adjust it accordingly.


Please know the sky isn't going to fall in if you make a mistake on your spreadsheets because you can always make an adjustment in the next Quarter. And if you're really, really, really confused and worried, just call the taxation office. In my experience, when I call the taxation office, and I used to call them a lot, the person that answers the phone really wants to help you. If you're stuck, if you're worried, just call the ATO. Okay. Got it. Good.


Okie Dokie. Now that the first step is done in our expenses, where we typed in our reoccurring expenses and adjusted our GST accordingly, I want you to copy and paste those reoccurring expenses into the rest of the months.


Thank you for sticking with me all this time. I think it's time for you to go and get a cup of coffee or go and hug your children. And then . . . when you come back and sit down all refreshed, I want you to grab that bag of receipts. And whilst you're doing that, I'm going to fix up this because I realized I deleted those two expenses. Okay. I'm just going to fix up that, and I'll see you soon.


Hello again. You have your receipts ready, and you'll also have your bank statements close by, I'm sure. Let's just jump back to Quarter one and, oh, I've just got to insert more rows here and note to self: When you download these spreadsheets, there will be plenty more rows for you ...


I'm going to role play again and pretend that I have that Shopify store and my widgets; my product offering is .......Let's say I sell silver jewellery with semiprecious stones. Okay. That's my business. That sounds like a nice business. I'd like to have this type of business in my fantasy life. Okay. I'm just going to input my big pile of fantasy receipts. And again, just please follow along.


Remember, these templates are yours, so they need to be personalized for you, so you feel special about them .... and ....You have some sort of ownership about them because you are the new bookkeeper in your new business. I want you to really look closely at the categories across here for your expenditures. You'll see that I changed this expenditure category to say, Contractor, because I forgot about Valerie. Valerie's my VA, and who wouldn't want a VA named Valerie? One thing that I will tell you now, just to repeat myself, and I'm sorry that I'm repeating myself (just like my mother does all the time). Just look closely at the GST component of your invoice. If there is a discrepancy, you just need to adjust it in the last column, the GST column.


If you have any concerns, just ring the tax office or just leave it until the next Quarter. At the end of the financial year, your accountant can fix anything. Any other general questions that you have regarding the spreadsheets, how they work, please do make a comment below, and I'll reply, or there'll be someone else in this channel ... Perhaps they could help you out with your question. Let's all help each other.


Now it's time to bring your attention to that box of receipts in front of you. And please grab 12 envelopes too, and write the month and the year on each one. Make those A4 envelopes if you find yourself with a huge box of receipts. Don't sort the receipts; that's just a waste of your valuable time. Treat the box, say like a lucky dip, put your hand in, pull out one, and then input the details into your spreadsheets. Then pop the receipt into the envelope for that month. Just pause this video while you input all your receipts. And if you have a lot of receipts, just save this video and come back to it later.


I bet you that feels like a weight off your shoulders, having those receipts written up.


Now it's time to open up that manila folder that has all your bank statements within it and:

Pull out the first statement from May-June of last year. Get a ruler and draw a line underneath the last transaction of May and then go down to the last transaction of June and draw a line under that transaction. Now with your ruler, go back up to the first transaction of June and place your ruler underneath it and have a look at what it is. As I look at my pretend statement in front of me ....My first purchase for the month of June is for my public liability insurance. And that's on the 1st of July, and I should be an excellent little bookkeeper here and write who the, uh, payment was made to. And we're going to make up QBE insurance. (There we go. Little things, all the little things, they take things, right? Just like this is a little thing doing your bookkeeping. It's going to lead to bigger things, such as expensive accounting software and a team of bookkeepers. And yeah, we can have a discussion about that at another time).

I'm checking that the amount deducted by QBE insurance is, in fact, $250. And look at that, it is. So with my lovely fine liner pen here, I'm putting a line through that $250 on my bank statement. And on the spreadsheet in column C, you will see that it says Bank Rec..... see this column there. So what we're doing here is we're cross-referencing our bank statement withdrawals with our spreadsheet expenses. They need to correlate, right? They've got to be the same. The next transaction is a withdrawal for Officeworks, which I forgot to put in because I lost the receipt. And that was way back at the beginning of the month. So I'm going to type in Officeworks and, searching my memory, I bought a box of fine liner pens, and they were $4.95, which I'm putting in the office supplies category. This purchase was on the 2nd of July. I crossed that off my bank statement, and I bold it out on my spreadsheet.


I'm just going to snuggle Eddie the cat because he's always in the way.


And welcome back. Your spreadsheet should look very similar to this now, and your bank statement, every single transaction, should have a little line through it, including your deposits for your wonderful sales, which you will repeat the same process in the income section. And you can file those bank statements and those receipts away for the next five years. And yes, you must keep them for five years.


Next step. See here in column A, grab all of them and then sort by date, so they're in chronological order. And if you don't know how to sort .....go up to the top to this ribbon here and go to Data and you will see, see how it says Sort, yeah, just click on the A to Z, and it will automatically sort your dates in order . . . because we like pretty and we like things to be a little bit perfect.


And if you like, highlight the rows you haven't used, delete those, and do the same with the rest of the months.


I want to show you now the BAS Reconciliation section and how these expenditures will look on the Year to Date Summary.


Bring your attention back to July and have a look at the total you paid for your expenses for that month. Then I want you to scroll to August and have a look at how much you spent in that month. And same with September. Now, scroll to the bottom here and here are your total purchases; that is the sum of those three months. And just underneath that, here is your GST, which is the sum of the GST you paid. And this figure here is pulled from the GST column. Now bring your attention here to the due amount or refund due for your tax obligations. This amount here ... if it's a positive, this amount is owed to you from the Australian tax office. If this number is a negative, it will show up in red. This is the amount you owe to the tax office.


Just to help you that one step further, here is the due date of your BAS. So common sense kind of tells us that we should get out our calendar and mark in that date. And perhaps put a reminder for two weeks before just to giddy you up to get your BAS Statement lodged on time. The day that your BAS is due is included in the BAS reconciliation section of each Quarter.


As you can see here, when I lodge my Business Activity Statement through the myGov portal, I take a screenshot of the lodgement and I drag that screenshot into here. You don't have to be so pedantic, but if you want to . . . then I guess that's a great thing for you to do.


Let's jump over into our Year to Date Summary so you can clearly see your tax obligations for your BAS.


And there we have it, there's that $1,350 and it's rounded up because the tax office is not interested in the cents after the decimal point .... which actually contradicts what my mother always told me, "Karley, look after the pennies, and the pounds will take care of themselves." I kind of like that saying. That's why I've got a piggy bank full of change. One day we'll smash that open. I digress again. I'm so sorry.


We'll just go back now into Quarter One to check that that this amount is correct.


So ladies, well done. You've made it this far, and in all honesty, please tell yourself that it really wasn't that hard. And then I want you to tell yourself that you are now the bookkeeper of your own beautiful business. Congratulations.


Now, the last thing to do, as promised, we are going to jump into the Year to Date Summary and have a really good look at this. And I'll give you a couple of hints and tips. Thank you.


When you look at your Year to Dat4e Summary, you'll see all your glorious numbers are just there ... just like magic. Now let's bring your attention to the Business Activity Statement Summary and, at a glance, you can see your refund or your payment due to the tax office for each Quarter. And then the total refund or payment made by you to the tax office.


Let's scroll on down to the Profit & Loss Statement, and here is where you will spend most of your time analyzing your marketing efforts. You know now how quickly and efficiently it is to input your receipts and your purchases. So that's a quick job. And because you now know that you must be operationally efficient and make a profit before you grow ... I want you to spend a lot of time looking at the figures in here, looking over all their expenses by the month, by the Quarter and your total figure for the year.


And when, and if, you write a business plan, you can include this Profit & Loss Statement in the financial section. And another great thing about this Profit & Loss Statement ... you can just highlight this section here, okay? Then down to the bottom of your spreadsheet where there is a + sign ... add a new sheet and rename it to budget and forecasting .... and paste your Profit & Loss Statement in here. Then delete all these figures, and estimate how much you believe you will be paying in the next financial year. So when you look at these different categories of expenses, let's just look at the top one that I have here: subscriptions, website, and hosting. I know when I look ahead that those expenses will pretty much stay the same. My government fees and charges will increase by 20% because I expect a 20% increase in sales over the forthcoming year. And why 20%? Because that is one of the SMART goals, I wrote in my marketing plan. My drawings will increase. I will increase my advertising. My materials will go up because the purchase of my materials correlates with that growth in sales over the next financial year. I'll be employing more Contractors. I'm looking at bringing in a business manager. Now, these are just examples of what I've already articulated in my SMART goals. So from these examples, you can see now why I said that you'll be spending most of your time in the Profit & Loss section .... analyzing, and budgeting and forecasting for the future.


And another thing .... If you are seeking investors in the future, or if you want to get a bank loan, the investor or bank manager will want to see your Profit & Loss Statement for the preceding financial year. And they will always request from you your budget and forecast for the future.


And that's a wrap.


My name is Karley Beadman, and thank you sincerely for giving me more than half an hour of your time. I really appreciate it. I understand that time is precious when you're running a small business. Running a small business takes a lot of work, and for you to stop and learn how to do your bookkeeping ...Well, it's a really great achievement.


I hope you enjoyed watching and learning. I certainly did enjoy making this video. It's my first instructional video. It was a bit glitchy. I'm sorry for that, but I'm sure I'll get better as time goes on, as you will get better with your bookkeeping.


As always ... practice makes perfect, right?


I wish you all the best in your endeavours to build a strong brand, and leave a comment below if you enjoyed this and got value out of it. And please do ask questions if you need any help. Thank you.


Oh, and just one more thing, because with me, there always seems to be one more thing .....


Please do share this video with your small business friends because they may be struggling under a pile of receipts and need help with their bookkeeping. Cheers.


blog.

bottom of page