top of page

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


Accounting Software is money down the drain at the startup stage of your business
Accounting Software is money down the drain at the startup stage of your business

With my simple bookkeeping system ✨ you'll have your bookkeeping done FAST ✨ . . . and you'll enjoy doing it too 🤩 . . . and you can STOP WASTING MONEY on accounting software subscriptions month after dreaded month!


YOUTUBE VIDEO TIMECODES

00:00 - Intro

03:30 - Table of Contents

05:07 - A Quick Lesson in Excel

08:20 - Overview of the Bookkeeping Spreadsheet

12:27 - How to Personalise the Spreadsheet

13:32 - How to Input Your Sales

34:02 - How to Input Your Purchases

45:35 - Cash Flow Summary

47:45 - How to Take Money from Your Business

48:57 - Making a Loss in Your Business

49:40 - Lodging Your Tax Return

51:09 - Income Tax Calculator

55:13 - How To Pay Yourself if You Own a Company

55:49 - How to Pay Yourself if You're a Sole Trader or Partnership

56:53 - Work Out Your Living Expenses

58:02 - How to Set Up Your Business Bank Accounts

01:01:07 - How to Lodge Your BAS Online

01:02:54 - Budgeting and Forecasting for Small Business


YOUTUBE VIDEO TRANSCRIPT

Hello to you, and thank you for visiting my YouTube channel. This channel is

all about helping ladies small business owners with finance, business and marketing strategies to help these ladies build a strong brand.


This video is PART 2b of a two-part series on "How to do Bookkeeping the Easy Way" using my simple spreadsheet template . . ....so you can say goodbye to that shoebox full of receipts (that I know you do have) and the expense of monthly bookkeeping software subscriptions.

Celebrating doing their bookkeeping

Some of the ladies who have already downloaded the spreadsheet have now completed their bookkeeping for last financial year and have lodged their own tax online for the first time, thus saving them hundreds ,if not 1000s of dollars, on bookkeeper and accountants fees.



Now I'm not advocating that you don't use an accountant au contraire. What I am advocating, however, is knowing your numbers is an integral part of owning your own business.

  • If you don't know your numbers, then how on earth are you to make sound financial business decisions?

  • If you don't know your numbers, how will you really know your true cash flow?

  • If you don't know your numbers, then how will you really know if you're running at a profit?



If you watched my last video, that is Part 1 of this two-part series on bookkeeping for small business, you will have learned that by doing your own bookkeeping, you become more operationally efficient.




Operational efficiency is directly linked to long term financial business success. And by doing your own bookkeeping, especially in the startup stage of your business contributes to this operational efficiency.

Operational efficiency is directly linked to long term financial business success
Operational efficiency is directly linked to long term financial business success


And by doing your own bookkeeping, you are really feeding two birds with one scone, because .... . . ..not only are you developing efficiency habits, you also get to methodically analyse where all your income is precisely coming from and where exactly you are spending all your revenue.


So let's jump straight in now to the step by step instructions on how to use my spreadsheet so you can be your own bookkeeper in your beautiful business. The link to download the spreadsheet is in the description of this video. If you don't have Microsoft Excel, well, that's okay because the spreadsheet will open in Google Sheets.


You will see that there are FOUR spreadsheets now for you to choose from.

  • One is for ladies everywhere .... the world over. This spreadsheet excludes the months for the financial year, and it also excludes any sales tax .... so you can add in your own financial year, and you calculate your own sales tax or send me a request and I will add these in for you.

  • There's a spreadsheet for ladies in the USA.

  • Another spreadsheet is for ladies who are registered for goods and services tax in Australia.

  • And the last spreadsheet is for those who are not registered for GST, and this is the spreadsheet I will be mostly using in this video because .... from customer feedback, most of you are not registered for GST.

So first up a quick lesson on Excel. Then I will give you an overview of the newly updated spreadsheet, and you're gonna love it. And then, I will show you how to input your sales and purchases and explain your Year to Date Summary with your Profit & Loss and your Cashflow. And then I will show you a really awesome table that .... wait for it .... CALCULATES YOUR INCOME TAX FOR YOU. And within this section, I explain to you how to take money from your business if you're a sole trader and how to take money from your business if you're the director of your own company. And I will also show you how I set up my bank accounts for business. After that, for the ladies who are registered for GST, I'll show you how to fill in your Business Activity Statement online .... so you don't have to spend your precious money on a bookkeeper to lodge your BAS for you. And then I'm going to teach you how to do budgeting and forecasting to help you build a strong brand.


And to all the beautiful ladies in business, I thank you for being here and for taking the time out of your busy day to learn how to do bookkeeping, the easy way. And sure, there's a lot to cover and I suggest you go back and watch PART 1 of this two-part series if you haven't already, and then make yourself a nice strong coffee, download the spreadsheet and sit back and enjoy the show radio.


Let's jump into my computer.


Here we are inside a new sheet inside my bookkeeping spreadsheet. And a whole document like this isn't referred to technically as a worksheet; it is in fact, by definition, called a workbook. But that's just semantics, isn't it, I call it a spreadsheet and I think most people do.


HOW TO USE EXCEL - A BEGINNER'S GUIDE

Down at the bottom of this spreadsheet are all separate sheets that you will see inside the bookkeeping spreadsheet workbook. At the end here, I just added on a new sheet, and you do that by clicking there. There we go. There's another one.


Let's go to the top left-hand side of this spreadsheet, and across here, see where my cursor is going .... these are called tabs. And inside each tab, we will find a ribbon. But the real magic happens in Excel inside these little rectangles. These are called cells.


When we look at this spreadsheet, we have columns that go downward, and we have rows that go across.


Now down in the right-hand corner, see this little bar here; this is what we can zoom in to have a nice close look.


So let's say this cell here, this cell is in column C, highlight, see how I filled it in with colour. Okay, so we're in column C and row 6. And where they meet, is called C6.


if we want to put data inside the cell, because it's completely empty at the moment, except for the colour, of course, we can either type directly into the cell, so I'm just gonna type in there C6, and that also appears up here in the formula bar. So we can either type directly into the cell, or we can type into the formula bar. So I'll just do that again, C6 and voila, there it is there. Command:Z on my Mac keyboard and we'll take that away.


Excel is all about mathematics. It does some pretty powerful stuff. It does really powerful stuff, actually. You plugin that tool pack, and it goes on and on and on. But we don't need to use that.


We are just doing basic maths ... plus, minus, divided by and times, and of course, equals to.


Let's just do something really basic and you''ll see how it works. So let's just put the number 10 in that cell, and number five in that cell, and highlight those two columns. And at the top in this group, I'm going to hit centre to centre it. In this column I want to put the sum of 10 plus five, so we go equals and then we go be six plus five equals 15. That is very clever, isn't it so simple, but so clever.


So when you're filling in your spreadsheet with your sales with the invoices that you have received payment for, and when you are filling in the cells for your purchases, or the calculations are already done for you. So it's just a game of inputting and I tried to make it fun for you because really, if you're not having fun, you may as well go home.


Now this concludes my brief Beginner's Guide to excel. Let's jump straight in now to the bookkeeping worksheet so you can get out with your learnings and get on with your own bookkeeping and building your beautiful brand.


OVERVIEW OF THE SPREADSHEET

Here we are, when you open up your spreadsheet for the first time, you will land on the Notes Sheet. Up the top of this Notes Sheet, please click here and please subscribe to my YouTube channel. And the daughter is calling me because it's locked down. And she is home from school and she is always starving. What is that with kids? If you click on my logo, it will take you back to this video. And whilst in YouTube, I would just love it if you could subscribe to my Channel. It does help me out more than I can express.


If you click here, this will lead you to my website. And if you click here, you can email me any questions, and I'm happy to help you fill in your spreadsheet ....


..... but I'm trusting that this instructional video will show you everything you need to know to fill in the spreadsheet quickly and efficiently.

Profit and Loss Statement Example

Let's just do a little reconnaissance of the spreadsheet so you know where everything is and what you're in for. Okay, let's go down the bottom of this sheet, Quarter One ... and by clicking on that, we arrive at July, August and September of the financial year. Quarter Two is where you will input data for October, November and December. Quarter Three is for January, February and March. And yes, you guessed it, Quarter Four is for the last three months, April, May and June of the financial year. If we look down the bottom of the sheets, the next one across is the Year to Date Summary ... it picks up automatically all the data that you have inputted from the four Quarters. This entire sheet has been locked so we don't disturb any of these numbers, so at the top, you have your profit and loss statement. And down the bottom here, there's a lovely little graph that will show your sales and your purchases. I'll just open up the BAS spreadsheet where he said, I think it's behind Nope, nope, nope, nope, there she is. If you have the Registered for BAS Spreadsheet, when you go to the Year to Date Summary, the first thing you will see is a summary of your BAS obligations per Quarter. Let's just minimise that window.


This spreadsheet is for Budgeting and Forecasting. We will come back to this sheet later on in the video, but I just want you to have a quick look now. So at the top is where you put in your projections for next financial year. By projections, I mean your revenue goals . . . how much you want to sell. The next section here is your purchases . . . how much you will spend in each of your categories. And at the bottom is a summary. There we go. It looks good. I think that looks really good. I hope you do too. But this is not what we're up to yet. I just wanted to give you an overview.

Budgeting and Forecasting for Next Financial Year - Excel Template

So again, just to repeat, down the bottom, we have the four Quarters where you'll be inputting your sales and receipts. The next sheet along is your Year to Date Summary and your Budgeting. And the last sheet here, this is a little bit about my business, FINDHER MARKETING STRATEGY and, if you click on that, you will discover an offer that I have . But this video isn't a sales pitch, because that will just be too tacky. And I'll sound like one of those marketing gurus out there that are full of BS. So I'll let you discover that in your own time. Okay, let's get on with this by way of demonstration ....And when I do, you'll be amazed at how fun and bookkeeping actually is.


INPUTTING YOUR SALES

Here we are back in Quarter One. The four Quarters are exactly the same, except they're different colours to help stop confusion. Let's zoom out. And here we have zoom, which I usually go at one at 25%. And that zooms in nice and close. So let's just zoom out set to 50%. At the top of each quarters sheet, we have our income. And this is where we input payments received. Down the bottom of the sheet, we have the three months where we input our data for our expenses, then way down the bottom, I have a little box here to work out your income tax. But we'll get to that in a moment. Of course at any time if you get stuck, and you want to come back to this video, just click on the little icon here, "FindHer on YouTube', and you will find me and this demonstration video. So let's scroll back up at the top.

Karley Beadman on YouTube

Now that we've zoomed in nice and close, you will see here in cell D1 that you need to put in the financial year you are doing your bookkeeping for and your brand name. So we can either click on the cell D1, and we can highlight that content. And my brand name is .... no idea because I'll just make it up as I go along. But what has happened here is this information has automatically transferred over to the other sheets .... because what you type in will automatically transfer onto the other sheets that because it will just be a pain having to retype that all the time. So now that we've put in our brand name, let's go down into income. I'm going to zoom in just a little bit here. There we go. It's nice and big.


If you see a little red triangle in the corner of a cell, that means there's a Note within that cell. So here in July, I do have a Note that says "this document was updated in 2021". So the pre-formatted date within Excel is 2021. You can click on the cell to exchange the 21 to 20 if you're doing 20 - 2021 financial year or whatever year you need .... so I'm going to click on cell A6 so you'll see what I mean. And if you go up to the formula bar you'll see that it says the first of the seventh 2021.


If you're using this spreadsheet in the future (hello to the future) you will need to change that date to whatever year it is in that future. Changing the year date is really here nor there but it's the small things, right! l


Let's look at cell B5 where it says 'Sales Details' and the Note here says, 'write in who the money is from and what item they purchased." For example, your customer's name is Sally Smith, you have a jewellery store, and you sold her a gold bangle. Now, this is just an example I need for you to take ownership of the spreadsheet. If you don't want to put in Sally Smith - gold bangle then don't ... you may just want to put in your total sales for the week. Here's another example. I'm selling this bookkeeping spreadsheet on my eCommerce store and at the end of the month, I will download a CSV of all the sales. So I could just simply put in, let's say it's the end of the month ....so it's the 30th of July. And I would say eCommerce - bookkeeping spreadsheet and paid by Stripe. So I put an S for Stripe because that's the credit card provider I use and let's just say $500. Okay, let's delete that example. See how I put an S for Stripe when you go to Paid Via in cell E5. And in this Note, I have put a little abbreviation for different types of payment you might accept in your lovely business. Here are just some suggestions - BT (bank transfer), S (Stripe), C (cash) .... but don't forget to deposit the cash into your bank account so you can cross it off when you do your bank reconciliation. Yes, yes, this is very true .... bank that cash. We don't want to cheat the tax department. No, no, no ......because acting with full transparency is the only way to behave as a small business owner. And let me just throw in here ......when you owe the tax department money, I want you to think of that as a good thing. Because, if you owe the tax department money, you're making money honey, and isn't that the point at the end of the day ... to make money? Even if you run a non for profit, your goal as the custodian of your brand is to make money. So let's all act ethically and record all our income and all our expenses. Okay, that's my little prep talk there. Let's get back to the spreadsheet.


If you have the first edition of my spreadsheet, you will see that it is a little bit different because, from that customer feedback that I was talking about, I've augmented, which is the fancy word of saying tweaked, this new spreadsheet to satisfy the needs and wants of my customers. For those who have the first spreadsheet, you will have in your email inbox a message from me with a link to download this new spreadsheet for free. If I have gifted my spreadsheet to you, I don't have your email address. So please do reach out to me on Facebook Messenger and I will send you the link to download this new spreadsheet.


And on that note, when you do purchase this spreadsheet, you will receive automatically all updates in the future because the future is always uncertain.... tax laws change, things change, and as they do, I will be updating the spreadsheet and giving you the free updates indefinitely.

Excel Bookkeeping Spreadsheet

Okay, back to the spreadsheet. So on the first spreadsheet, if you do have it, you will only have one column to write your sales. In here we have three columns, and if we go to column H, and highlight across to column I, see in the middle where there's a dark line, that means there's more columns hidden. So right-click > scroll down to > unhide columns, and there is a reason for this madness. Let's explain this by way of example.


How about we use Yasmina Despot. Yasmina is a beautiful lady and she is one of my new members in my business and marketing club. Yasmina recently started a business and her product offering is luxurious ethically made soaps. It's called Lutheran blue. I will leave a link to her eCommerce store in the description below ... https://lutherandblue.com/


Let's just say I'm Yasmina sitting here doing my bookkeeping and Karley Beadman purchased a gift pack. The invoice number goes here and Karley paid by PayPal ..... and this is where we're getting back to why there are so many columns. Remember, it's your spreadsheet so you can opt just to have one column. Yasmina's business is the example here, so I will take a good guess and write soap bars in the first column. Yasmina also offers body lotions. Yasmina is also doing R&D into sensual body oils. So let's just write oils. And the following product that Yasmina is doing is ... bathroom accessories. And the next thing she sells is gift cards. And we'll just hide this last column. So there are her five product offerings. So then, on her eCommerce store on the second of the month, Susanna paid by Stripe and Susanna bought $37 worth of soap bars. She also bought a body lotion, body oils, and she bought some bathroom accessories. And you'll see at the end here in column L, it's the total of her invoice. Let's just pop back up here to my purchase. And let's just say I bought my girlfriend a $200 gift card. Now I'm going to make a few mock sales just so we can populate some graphs out of income for July.


Here we can see some quick examples of different purchases. Now let's just say at the gift fair in Melbourne, Yasmina recognised the opportunity that sensual body mists were in demand, so Yasmina decided to add in another product category ....And that is the 'sensual body mist sprays'. And then, all of a sudden, she started getting orders for said mist.


Oh, looky here, my future husband bought us the gorgeous soap bars and the sensual body mist. But where the real magic happens is not in my fantasy world. It's on this spreadsheet. I'm going to zoom back out; let's just go to 100%. We can see an overview here of Luther & Blue's turnover of $8,098. This figure is populated in here, and if we just scroll over here ...we can see the product breakdown by percentage. So 37% of Luther & Blue's sales have come from their signature soap bars. Then we have 26% in body lotions. The new sensual body mist is at 15%, and 4% of all Luther & Blue's sales are in gift cards. 12% for bathroom accessories and 6% in body oils from this rather impressive, albeit pretend, income for Luther & Blue.

How to input sales into the bookkeeping spreadsheet using Luther & Blue as the example

I will just open up the registered for BAS spreadsheet and copy and paste that data in to enable us to look at the figures for the lovely ladies who are using the Registered for BAS Spreadsheet. I'll just zoom in here and scroll over.


I will just open up the registered for BAS spreadsheet and copy and paste that data in to enable us to look at the figures for the lovely ladies who are using the Registered for BAS Spreadsheet. I'll just zoom in here and scroll over. In column M, we have the total of the GST, which is the total sum of the invoice divided by 11. Then when we move on over.... And doesn't that look good? In July, Luther & Blue made $8,098 and here in this cell will be our total purchases. But to date, Luther & Blue have collected $736 in GST. And from this table here ....it is telling us that Yasmina currently owes the ATO, that $736 she collected for the Australian Tax Office. And over here, I put a little note that says that we will put aside this amount, so we won't have to worry about finding the money when BAS is due. And of course there aren't any expenses yet. So Luther & Blue is running at a profit of $8,098 ....boom, boom.


Getting back to our income here, I need you to take this spreadsheet and make it your own. Whatever data you input to the spreadsheet, I want you to have a reason behind that. And I think that reason it shouldn't be not just to tell you the sum of your sales, but I want you to go back in and look and ......Study and draw conclusions out of these figures. This isn't about assumptions here. This is about the numbers telling you the facts. We can see, just by this example of me typing in some made-up figures and fantasy products for Luther & Blue, that when they did the trade show, they made $4,100 (which is actually not much to make for a trades how considering a trade show 3 x 3 stall is generally about $3500). But anyway ...So one would expect, and one would make the assumption, that following on from the trade show Luther & Blue would receive an influx of online orders from wholesalers, who discovered her beautiful products at the fair. Therefore Yasmina, can go back into her figures for July and the following month, and she can see if there was big orders from the wholesalers. And from this data comes knowledge and from knowledge, Yasmina can make wiser decision about whether doing a trade show is financially beneficial for her brand.


If your choice is to get really detailed with your sales and you have more than 29-30 sales for the month that you want to record ....Firstly, let me just show you here between rows 35 and 74 .....I have hidden those rows too. So again, you just highlight over those rows, right-click on your mouse and unhide. I'm sorry, that's gone out of the recording screenshot. There we go. And it gives you more rows.


And getting back to the titles of the different categories. You don't have to separate your sales into your product line extensions. Instead, you could do this. Let's just say I have ...let's just think of an example.


How about we use Sharon Watson. Sharon is also a member in my marketing club. Sharon is starting up a new online store selling products to people living in remote areas. So let's just say that Sharon is selling on her eCommerce store. She's using WordPress, so it's WooCommerce. And she'll also be selling some of her products on an Etsy store. And she will also be selling via social commerce. So she will be selling on Facebook, and Instagram and Pinterest. And let's just make something up here ....And she also sets up a market store every Saturday morning to test the new products that she's listing in her store with her local market to get their feedback.


We'll just use that date... the first of July. Let's just write sales, and Sharon sold $500 on her WooCommerce store. On that day, she sold a sum of goods through Facebook and she had another nice sale on her Pinterest store. Then on the next day, her total sales on her WooCommerce store were $800. She sold a couple of items on Etsy, and there was a customer on Instagram. And then the next day, more sales. It was a pretty slow one on her WooCommerce store. Etsy went rock and roll that day. And Pinterest, there was a really big order that day. Then on that same day, she did a market stall in her local area to test the new product she wants to put on her website. And on the market stall that day she made $800 so at the end of the month, or at the end of the week, or at the end of the day, let's just hide these rows, so we can see more clearly what is going on. So again, right-click Hide. So over those three days, Sharon made $1550 via her WooCommerce store, 500 from Etsy, $55 from Facebook, etc, etc with a total of $3,805. Having this knowledge also helps Sharon decide where is the best use of her time.


Clearly, from looking at these fake figures, her WooCommerce store is generating the most revenue. If I was Sharon, I'd think okay, so people are going to my WooCommerce store and spending more. So a good use of my time would be to perhaps A/B test my WooCommerce store to see which one converts to more sales. If I was Sharon, I would invest more time in blogging because these people are finding her organically. I would perhaps test out some Facebook Ads to see the increase in revenue ....And I'd continue doing the markets because this little bit of work on a Saturday morning is invaluable. So she'll keep on doing that because that $800 is giving her enough of return to buy more products to test in the market.

I'm giving you one more example, because it's always nice to have three examples. I will choose an example that's a service-based business to show you how this spreadsheet works brilliantly for both product-based and service-based businesses.


What can I use? What can I think of?


I know. I'll use Donna Hartland. Donna, and her husband Loui, have a photography business here on the Gold Coast, where I reside. Donna has joined my membership specifically so I can help her with SEO.


Donna's business is well established, but, because of unforeseen circumstances in the macro environment, namely COVID, weddings aren't exactly happening and weddings is what Donna Hartland's business specialises in. With the lack of income for weddings, Donna has been thinking creatively, like all entrepreneurs do, and seeking opportunities in the marketplace. Therefore, Donna has come up with ideas by jumping into other categories of photography.


Here, on the bookkeeping spreadsheet, Donna has her wedding photography side of her business. In the next column I'm typing in boudoir photography; she's also going to try personal branding. And I do know they got a contract recently for elite realist photography. And let's just say, just for the sake of filling up all these columns, that Donna moonlights doing photo and video editing. I actually know she's very good at that. And then let's just have a guess here that she's an affiliate marketer up for the camera gear that she uses. I'll just write products.


Okay, okay .... let's say on the first of October ... let's just use me as an example, Karley Beadman is getting personal branding photos because God knows I need them, and that is for a fee are $2,500. And then on that same day, the first of July, in the afternoon I'll pop in Yasmina Despot. She also wants personal branding photos, and Donna is giving Yasmina a discount because, yes, she's the nicest lady in the world. And then, the next day, Donna has a photoshoot with Chevron Luxury Homes. It's a pretend luxury real estate company up the road ...and one of their clients is selling a house, and it's $4,000 for that photoshoot. And whilst she was there, Chevron Luxury Homes also bought from Donna camera equipment because their in-house camera was RS.


There we can see ...this is just an example how you can use this spreadsheet if you're a service-based business and because we all like to share the love, I will drop down a Hartland website link in the description below (https://www.wildhartstudios.com/)


I really want you to get as much value out of this video tutorial. So right now, I'm going to fill the four quarters with mock sales so it can generate data to show you how it looks in the Year to Date Summary. I'm going to import this data, and when I drop it into my moviemaker, I will speed it up really quickly, and in 20 seconds, you will see this data come alive with all the sales.


This is terrific. Now we have some data in our spreadsheet. I'm going to grab this data and drop it into the GST spreadsheet so I can show the ladies who have the GST spreadsheet, how their GST calculations are looking


Let's jump to the year to date summary to have a look at the summary of our GST collected. And there we can see for each quarter what we owe the ATO ....because we have collected the 10% of all our sales and it does say here "if it is a positive (meaning these numbers) the ATO owes you and if this number is a negative you owe this amount to the ATO by your due date". So overall, we owe the Australian Tax Office $5,231 in round numbers for this financial year.


This concludes my demonstration of how to import your sales. That didn't take too long. Now we're going to jump into inputting your purchases for the year.


INPUTTING YOUR PURCHASES

Here we have arrived at our expenses for this Quarter. There are plenty of columns created for you, and I highly recommend you don't add in any more columns. If you do, you'll completely bugger up the spreadsheet and also ....when it comes time to lodging your tax, you will either want go to an accountant or lodge it yourself through the mygov portal.


When you lodge your tax online, it doesn't ask for a detailed breakdown of what you spent your money on. In fact, the ATO doesn't really care what you spend your money on as long as the money spent is spent on your business. So here ....there are enough columns for you to personalise for you to analyse to help reflect over your expenses and to help guide your decisions in the future.


I'm going to do this demonstration pretending I'm a ????? .....and of course I didn't write a script for this YouTube. So let's just list a few things about myself that I can generalise about the ladies who are not registered for GST ...... Okay ......I work from home and I have a cake business because I do know about fast-moving consumer goods.


Let's just say I make celebration cakes. I also make biscuits. I also make ??? ..... what else can I make??? .....pavlovas, because who doesn't love pavlova? Especially pavlova margarita. I had one of those one night in the West End in Brisbane in this funky little cafe that had a chandelier in the

Canva can turn a boring spreadsheet into a work of art

window with Barbie heads as the lights .... anyway. Okay, so what about what about ?? .... I've got celebration cakes, biscuits, pavlovas and ... muffins. These are my four product offerings. Let's write those up. So here, in my categories, I buy products to make the celebration cakes. I also buy products to make biscuits, and I make pavlovas and muffins. I don't really like muffins. So there are my four product offerings in column F, G, H and I. My product offering number one are the celebration cakes.


And you know what? I'm wasting time now let's go down here. And let's just copy those four categories Ctrl C and paste it into there. There we go. That has put the colours out. So being a stickler for perfection, I'm highlighting those four cells > going into home, picking up the format paintbrush and dropping those colours in there. I love that about Excel. Small things right?


I had to buy a lot of small things when starting my baking business.


And because I'm risk-averse, I invited the Local Council in to inquire about the Council laws and was told by the Council Inspector that I needed to instal new flyscreens in my kitchen. And this was a $600 expense. So let's scroll down ...my very first expense for my pretend business. So let's say that happened on the fifth of July. And looking at my pretend bank statement here. Yes, indeed, my very first purchase was for fly screens from Fly Screen Incorporated.


And what category should I put that under? How about ....let me find one. If there wasn't one that suited your expenditure, just claim one of these ones and personalise it for you. But I'm going to use that percentage of home office expense or factory and then I'm going to put in the $600.


Now scrolling back to the beginning. I want you to have a look in column C and there in cell C 237 you will see total purchases. This works similar to the income where this amount is a sum of the rows across. So I just inserted that $600, and it automatically calculates the total there.


Okay, moving on. I applied for an ABN because I needed the ABN to register my business name with ASIC. So on that same day, on the fifth of July, I pay ASIC for my business name registration. And that is a government fee of $37. I know that every year on the fifth of July, I need to give the government another $37 to keep my business name registered. And on that same day, I went into GoDaddy, and here looking at my pretend bank statement. I do see a charger for $6.95 to register at my domain name, www.karleyskitchen.com.au. And I'll put that under website and hosting.


And I'm just going to zoom out here because I can't see all the categories clearly. Let's see 90% and all that is sorted. And the next thing I did ... I go into my bank statement because it's jogging my memory. I because I know that perception is everything .... I set up a G Suite account and purchased a business email address.


The decision to purchase a business email address was made because the value discipline I chose to be able to compete in the marketplace is customer intimacy because my business would involve a lot of customer interaction. So therefore I chose the name karley@karleyskitchen.com.au because, for me, an email address such as karleyskitchen@gmail.com doesn't give the impression that I was really serious about my business .....and by having a professional business email address gives the impression to potential customers that I am actually a fair dinkum business. I didn't want my email to be info@ or enquires@ or sales @ because, as stated, my value discipline is customer intimacy. And I'd like my customers to think they were emailing me personally.


So let's just write up that expenditure. On the fifth of July, the same day I was doing all my admin work, and it was G Suite. And I will put that in ...let's say subscription,s because that is a reoccurring subscription that I need to pay every month. And I want to track that individually with all my other subscriptions that I will undoubtedly sign up for.


And because I know that making a profit is important, before we grow, I decided to bootstrap at the start-up stage of my business. So I jumped on Google and I searched for a coupon code, and I found one so instead of paying the recurring monthly subscription fee of $10.95, I only pay $7.95. A sweet $3 saving per month ..... remember small thing.


Great, I was all set up legally. And also the admin side of my business was done. Fantastic. Good job.


I then went and bought more equipment. And so on the sixth of July, I had a bit of a shopping spree at kitchen wholesale suppliers, and I bought a new kitchen whiz thing and looking across here at my different purchase categories....and I wanting to make this spreadsheet really personalised for me, I am going into that sale there. And in here, I'm going to write in equipment and chattels. You know, all those other little things that you need to buy, like utensils and things. So that day I spent, according to my bank statement, $1450 Ouch. Then I went over to Bunnings on that same day and bought shelving and storage boxes. I am going to put that expense in equipment and chattels and I spent $2,500 to set myself up.


Now that I've entered in those start-up costs, I see that I have spent four thousand five hundred and ninety-four dollars. And the great thing about this spreadsheet, let's scroll to the top. I can see here that I'm already at a loss of three thousand eight hundred and nine dollars.

Breakdown of our purchases

Let's just quickly jump into the Year to Date Summary. Here are our total purchases. See down the bottom here. And if we go now to the right-hand side of the screen, we will see this graph here. And there are all our purchases for our start-up. And down the bottom we can see that $600 quite clearly for those fly screens that the Council Inspector made me install. And there's that $37 for my business registration and for the subscription? ....there it is there. You can hardly see it because it's only $7.95.


Back here in Quarter One, Ill going to take a little bit of time inputting more expenses so when we go to the Year to Date Summary, you'll have a more realistic picture of what to expect in your own spreadsheet.


Now that I've inputted all my expenses for my beautiful home-baked goods business, let's go to the top of this Quarter, Quarter One. And across the top here, you will see a snapshot of this Quarter's cash flow. In July there are our expenses. Do you see there? And that's our income there. Our expenses were a lot more than our income. Now, going back to August, it pleases us to see, just at a glance like that, that our income is more than our expenses. And to the right here in September, we also made more money than what we spent. And that information for July, of course, is in this little table here. And scrolling down there, we see quite clearly in August, we made a profit ... and scrolling down to September .... and, yes, we've made a kind of sort of similar profit. And over in the pie chart, we can see a breakdown of where our profit is coming from. And just glancing at that, we can see that there's quite an even distribution of revenue coming from each of our product offerings.

Let's do the same in Quarter Two. So I'm clicking on Quarter Two sheet in these tables. And across the top, look at that, our income is going up and up and our expenses were pretty much the same across those first two months of Quarter Two and the expenses shot up in December. Now it's time to have a snapshot look at Quarter Three and look at that. Income, income, income. Profit, profit, profit. Quarter Four. Let's have a look at that again. Profit, profit, profit. Of course, these are any made-up figures, but I just wanted to put the sales and expenses in just to show you that with this bookkeeping spreadsheet, everything is laid out for you.

If you are using a cloud-based accounting software such as MYOB or Quickbooks, as soon as you enter data, it disappears into the software Grrrr! On my bookkeeping spreadsheet, you can see everything there in green and white. And here, on your Year to Date Summary, you will be able to see your sales and your purchases clearly. And scrolling down here, we have your total business cash flow in. This is your sales for the year. And here underneath, this shows you in red .... the cash going out of your business. And because we love color and shape, there's your income coming in and here's your money flowing out of your business. And over on the right, you made a profit here.

Cash flow graph for small business
Cash Flow

So as you're working through your bookkeeping, each time you come to the end of the week or the end of the month or the end of the Quarter, you can jump into this sheet and see how you're travelling. I've also included the amount of money you took out of the business for your personal use.


TAKING MONEY FROM YOUR BUSINESS

We're going to jump back into Quarter One now so I can show you how to record the money that you take out of the business for your personal usage ..... use ........ for your savings, for your holiday. And as a small business owner, you deserve a holiday. Running a small business is hard work. It's hard to make money when you first start a business. It's really hard.

I heard this great saying once, if it was that easy to be a millionaire, and why aren't we all millionaires?

And to help you get on that highway to millions, you would need to take personal drawings out of your business account to not only pay for your personal living expenses, if you don't have another source of income, but to invest part thereof those personal drawings into an investment that isn't directly linked to your small business.


You know, and I know you're saying "you shouldn't put all your eggs in one basket". I personally take 10 percent of all my personal drawings and I micro invested in RAIZ. And we all like free money. Right? If you sign up using my link, you get five dollars and I get five dollars. And yeah, love free money.






Let's get back into the spreadsheet and we'll go to Quarter One, and I'll show you how to write up your personal drawings. I've got personal drawings in its own category outside of your business expenses. It's just not going to make sense here to take drawings in the first Quarter because we really didn't make that much profit. So let's just jump over to Quarter Two. And here I'm going to take personal drawings of a thousand dollars in October and in the next month, let's just say two thousand dollars and in December, three thousand dollars. I'm popping that in willy-nilly, just so we can have a look in the Year to Date Summary, where we were. And there is that six thousand dollars. And when you look at this bar graph that the money flowing out of your business has that extra six thousand dollars there. But the money that you withdraw from your business account doesn't affect your profit or loss. It just would make sense if we added that six thousand dollars in here, because this figure here shows the health of your business and the money taken out of your business doesn't affect, in any way, your profit or loss.


If you do make a loss in your business, you are still required to lodge an income tax return for that year.


And if you do make a loss in your first year of business, I don't want you to think that your new business venture is doomed for failure. Businesses that run at a loss in their first year of trading generally spend more than they earn because of a myriad of reasons. For example, your R&D costs were high, or let's say you spent a lot of money on advertising to build brand awareness, or you invested in expensive equipment, or your shop fit budget blew out.


Think of your loss as one of the first steps to achieve your long term grand vision. Because, you know and I know, that great brands aren't overnight. They never have been and they never will.


YOU MUST LODGE YOUR TAX RETURN ON TIME

Let's talk about profitable businesses now. Your business profit (or your business loss) forms part of your taxable income. Your taxable assessable income includes all your sales in your business, as well as all other income that is not part of your business activities. For example, capital gains. Another example is wages from that nine to five or that casual job you hate. I don't want to feel anxious when it comes to tax time. YOU MUST LODGE YOUR TAX RETURN. Oh no ... I'm making you anxious. No no no :( But you must lodge your tax return. And the final date for lodgment is the thirty-first of October. And if you miss this deadline, you'll be hit with a fine. However, if you're owed money by the ATO or if you've had a good tax history, you can often get the fine withdrawn.


If you look into your mygov portal and click on the ATO link and follow the prompts, you'll quickly work out that it's not difficult to do your own tax return. If you call the ATO, they'll even walk you through the steps so you can rest assured that you won't make any mistakes. And if you do make a mistake, you can amend your tax return at any time.


Let's jump back into one of the four Quarters of this spreadsheet, because I've made you something. It's something that I use. And I thought it a great idea to include this in the spreadsheet for you. And here it is.


YOUR INCOME TAX CALCULATOR

I'm in Quarter Two, and this table is at the bottom of each of the four Quarters. The reason I jumped and zoomed in on this table is because, remember, we took drawing's in this Quarter, so we want to talk about that. But first, let's talk about this little table here. The reason for this table is so you can work out your assessable income at the end of each of the four Quarters. Actually, you can work at your assessable income at the end of each month. See, I've got it there in the first part of this table. We have our total sales for each month. So this is our income for our business. Right. But our taxable income does include all other income. Remember I said capital gains or wages. So I've put another column in here to include your gross wages.

Income Tax Calculator

Let's just say in October, I added up the four payslips. Let's just say there are four weeks in October. Right. And I got paid a thousand dollars a week. So let's just say four thousand. And then when I look at those payslips, the amount of PAYG my employer retained, let's just say fifteen hundred dollars, and then in the next month, let's just say it was the same. Hey. So down here, I'll change that to four thousand five hundred. And then in the next column here, I want you to put in any other income you received, which could be capital gains from a sale of a property, I invest in RAIZ, I said that before, so I took out some of my dividends. So let's just say by way of example, in November, I took out $2000.


Here we are in the total column. If we double click on it, that is the sum of our sales. It is the income from a job. And that's hiding that. And then I make no other income that month and so forth and so forth. So my total income I received in that Quarter was thirty-four thousand nine hundred ninety-four dollars. But I do have deductions, which are the purchases for my business. And then there could be other deductions that you claim. Whatever they are that aren't related to your business, I want you to pop them in here. So therefore, the estimated total income for October ...... that's all the money that I received less all my business expenses, less any other deductions is four thousand seven hundred ninety-four dollars and five cents. And right there is the total taxable income for this Quarter. Let's just have a close look at cell YZ43. Look up here in the formula bar.

I have taken my total taxable income and found 25 percent of it because I personally want to put that a separate bank account. Twenty five percent.

You might be more risk-averse than me and want to put in, say, 30 percent or 32.5%. As with everything in life, the choice is yours. You may choose to put away more than that, or you might only want to put away 10 percent. But do put a percentage of your personal drawing's away in a separate bank account at the end of each month so it's sitting there ready to pay the tax office at the end of the financial year.


So in this column, I do have a total amount, but this amount is the sum of the three months. And then if we look here, we'll see the formula ... it's the sum of October, November and December. But if we do have a job outside of our business, our employer does pay out tax for us. So hence why I've deducted that off from this table. I know I need to put away four hundred and forty-seven dollars into a separate bank account. So when it comes time to lodging my tax return, I know that I have money there to pay it.


Let's talk about personal drawings

Now let's talk about personal drawings. This might seem obvious, but I'm going to say it anyway. If you're the director of your own company, you shouldn't be taking money out of your business as personal drawings. If you need to take money out of your business you do so as a director's salary and then you pay the tax office your PAYG when it's due. If you'd need to take money out of your business other than your salary, you do so as a dividend. But the dividends are only taken from profits after tax.


If you're a sole trader or in a partnership, you don't pay yourself a salary or a wage. Okay, you don't do that. Instead, you take personal drawing's. You don't pay tax on your drawing's per say. You only pay tax on your profit. Hence, why this table is so useful.


And so is this bar graph on the Year to Date Summary, because as you take personal drawings out of your business account, you will see in black on this bar graph, it's getting bigger and bigger and bigger. And by looking at this, you know, when that black is getting close to the income above it, you know, it's starting to line up ... that you were taking too much money out of your business. So when you see your business running at a profit, I strongly advise you don't skim that money out of your business account. Instead, work out precisely how much money you need for your personal use. Knowing how much money you need to take as personal drawings should be one of your very first considerations before starting your business. So this is what I need for you to do, work at your budget. And on that Note, let's jump over into the budgeting spreadsheet and click arama.


And here we are with some bunting. Got to love Camva. Okay, I'm going to make the assumption because ... we know I'm good at that. Let's say you know exactly what your personal budget is. And if you don't have a personal household budget, I will upload a spreadsheet into my online store at www.findher.com.au. I will make a YouTube video explaining how to fill it in and how I .....and there's no word of a lie in what I'm about to say ...

....how I, as a single Mummy with no cushioning, i.e. financial support, never have to stress about paying for our day to day living expenses and life's little luxuries.

I'm not a rich woman, but with my personal budgeting spreadsheet, I darn well will feel like one.


SETTING UP YOUR BUSINESS BANK ACCOUNTS

And how I set up my business bank accounts contributes to these feelings of richness and how this simple system that I use takes away all my business, money management, stress and confusion, and I get to see precisely how much cash at bank I have every single day.


This is how I've set up my bank accounts to make my life more operationally efficient and stress free.


I have three business banking accounts:

  1. A business debit account;

  2. An American Express credit card; and

  3. A savings account.

The first account is for receiving all payments from sales. The second account, the American Express credit card, is for paying for my business expenses and the third account ... listen very closely. Now, this account is for transferring my elected 25% of my profits ....so I'm not stressing out at tax time. I am totally head over heels in love with this card .... the American Express Discovery credit card. It has no annual fee. And I earn point seven five Qantas Frequent Flyer points with every dollar spent. My referral link will be in the description of this video, because I really want you to have this card. And I explain more reasons why and I go into more detail on a separate video on how to set up your business bank accounts.



How to set up business bank accounts

The reason why I've put how to set up your business banking accounts in a separate video on YouTube is because the way I've set up my business bank accounts will help other ladies, you know, those other ladies that aren't using my spreadsheet but are paying for expensive software subscriptions and bookkeepers and for other ladies, too, who struggle with their business money management.


Bookkeepers are a Waste of Money

Picking up the topic again of paying and wasting money on a Bookkeeper, and at the same time having a look at the savings account that I use for tax and GST savings. Once upon a time, I was paying a bookkeeper to lodge my BAS for me. I had always assumed that a Bookkeeper needed to check over my bookkeeping. And I also assumed I needed a Bookkeeper to lodge my business activity statement. Well, the day came when my Bookkeeper got too busy with her other clients, so she postponed lodging my BAS. So this is what I did. I called the tax office and they helped me fill in my BAS form. Now, understanding back then in the olden days, there was a physical form that you filled in, but they helped me fill it in and it took no time at all. I realised that day that I had been wasting money on a Bookkeeper to lodge my BAS for me, because it literally takes a total of two minutes to do it. And because of the automatic calculations on my spreadsheet, I think I have reduced that time to under a minute.



How to Lodge Your BAS Online

If you are registered for GST here, let me show you how to lodge your bets on time in under a minute.


Step one. Open up your spreadsheet. I've opened up the Registered for BAS Spreadsheet. Right down the bottom of every Quarter's sheet is this table. And here you'll find these numbers are extracted from your sales and expenses, you know, that you typed in above. When I lodged my best last time through the mygov portal, I took a screenshot of the form that you fill in online. Let me just open that up. There it is. Look at that. Now see how I've got J1 here on the spreadsheet. There is one. So whatever that figure is, there you put it there. Now, here I've even included GST. So when it says does this include GST? Tick Yes. What's the next one on?GST on sales? That's one way. There it is there. And that one is there! Hit save and continue. Then on the next page, it will say whether you're getting a refund or whether you owe the ATO for this Quarter's BAS. And that is that figure there. That's it. Honestly, that would take under a minute, including login time. Very easy. So easy. And what is also super easy to do and super quick is take this money here and transfer it into your tax and GST bank account. .....ready to hand over to the ATO. When your BAS is due.


Budgeting & Forecasting for Next Financial Year

Now let's move our attention back to the task at hand, and that is budgeting and forecasting for next financial year. This is one of my favourite things to do in marketing, because this is where we get to think about and then articulate our marketing goals . . . when we don't have a formal marketing plan.


Well, here we are back to screen recording in this section at the top of this sheet. What do you want your sales to be for each month next year? This is step one ... and because this video is running way too long .... I've written in across here what I want my sales to be for the homemade baked goods that I'm making in my fantasy business. Step two is to scroll down to this section and here to adjust your expenses each month for each of your categories. Remember, though, your purchases are directly correlated with your sales. Here is an example. If I was to sell ninety-two pe cent more, say, celebration cakes, my cost of goods, also known as variable costs, will also increase. But you also have fixed costs that don't increase. An example of a fixed cost would be if you lease a factory, it will stay the same. And if you know that it's not going to, then you just adjust it in the month that it goes up. Scroll down now to this section. My forecast for next financial year. The expenses that you've just budgeted for in this section are now included in there, the totals for each month and your sales forecast are across this row. Yeah, you write in your personal drawings for each month. Remember how I took six thousand dollars in this second Quarter for my business? So I just copy and paste it..... ..Two thousand dollars per month across there. Now, let's look at this. Our projected cash flowing in is here. Our cash flowing out and our expenses are here. And here we have our estimated profit or loss for next financial year. And the $24,000 from our two thousand dollars every single month is here. So our estimated cash at bank at the end of next financial year is that much. Pretty cool. Let's shift to the top of the screen. Let's look at our marketing activities to help us achieve our revenue goal.


Marketing Planning for Next Financial Year

We've got a plan, right? It's really no point slugging away in your small business every single day if you don't actually write down what you're doing and focus on those activities.


But those goal, they should be SMART. If you click on "Why You Need Smart Goals", it will lead you to my blog page where I explain how to write SMART goals.


So if my revenue goal for next year is one hundred thirteen thousand two hundred dollars, which is a ninety-two percent increase in sales from last year .... Is a ninety-two percent increase in sales possible? Yes, it is ......if you focus on your marketing activities and here to help you out, I've given you three examples. Number one,


I will dedicate one full hour every day to writing blog posts. And I commit to publishing at least one blog post every Sunday for the next 12 weeks, starting on X date.

I can pretty much guarantee if you do that, you will start getting traffic to your website. And here are two more examples for you and here are some helpful tools that I use because you may find these tools useful, too.


Conclusion

And I think that's a wrap. And I hope you got tons of value out of this educational video to help you do your own bookkeeping using my simple Excel spreadsheet. And because most of the ladies watching this video are in the start-up stage of their business ... my bookkeeping spreadsheet is intended to not only help you keep track of your sales and your purchases, but it's to help you make informed financial business and strategic marketing decisions as you go about building a strong brand.


Please share some love by sharing this video and my website page with other small business ladies who want to be just like you and me and don't want to waste money on accounting, software subscriptions and bookkeepers in the start-up stage of our businesses. If you haven't already written a comment below, please do. And I kindly ask you to subscribe to my YouTube channel, because this shows YouTube you're getting value out of my teachings ...And YouTube will suggest this video to other small ladies in business.


My name is Karly Beadman, and I'm a Master of Marketing with Distinction and a Certified Practising Marketer with the Australian Marketing Institute. I am a mummy to a starving teenager in lockdown, and a purrratron of a cat named Eddie, who hogs my desk and purrs all the time ....so I have to re-record and record.


I created my business when Covid hit after a lifetime of entrepreneurship and with the desire to pass on my knowledge and skills to lady small business owners so they can build the business of their dreams and become financially independent. So they, in turn, can employ ladies in their business so we all have the financial freedom to walk to the beat of our own beautiful drum.


I'll be leaving our link in the description below to one of my product offerings. It's called FindHer Business Online Directory. Please pop your details on the form to claim your own free listing to help you get found on the World Wide Web. I invite you to reach out to me on the social media platform of your choice if you have any questions at all. I'm always happy to help. For now, I wish for you an amazing day. And thank you for taking some time to spend it with me ....And I wish you all the best in your endeavours to build a strong brand.


The End













コメント


blog.

bottom of page