‘Can you’ and ‘should you’ are two very different things?
Yes, it is possible to do bookkeeping in excel and there are many excel bookkeeping templates out there for small business owners that make the process easier.
You can handle most anything in excel, such as a chart of accounts, transactions sheet, and income statement sheet for all of your small business bookkeeping needs.
But you will also need to manually input everything to do with your business accounting and bank accounts into the excel bookkeeping system that you’re using.
Below we’ll explore why Microsoft Excel works great for some small business owners‘ business finances, and why other methods might work best for others.
On this page
Getting started with a Microsoft Excel bookkeeping system
The first thing you’ll need to do is look for an excel bookkeeping template online. Excel bookkeeping templates are everywhere, so just look around for the one that looks most promising to you.
Focus on what it’s offering, the design, and how easy it is for you to use. Try out a few different excel bookkeeping templates before settling on the perfect one.
What can my new excel bookkeeping template help me with?
Below is a list of the typical things Microsoft Excel can help you with as your new small business bookkeeping system.
- It can help you manage business cash flow
- Chart of accounts
- Transactions sheet (where you can monitor every business transaction, including accounts receivable and accounts payable)
- Bookkeeping reports (including proper cash flow statement, balance sheets, profit-and-loss statement, and income statement sheet)
- Track invoices (with the help of invoice templates to track invoice payments and paid invoices)
- Create financial statements such as monthly income statements
- Track every business expense
- Categories transactions (categorising transactions such as expense transactions and income transactions)
- Much, much more
As you can see, excel spreadsheets can help you monitor all sorts of business finances as a business owner. Tracking invoices, creating separate sheets for every aspect of your business and keeping up to date with your bookkeeping tasks all become much easier with Excel accounting. And it’s completely free too with OpenOffice or Google sheets!
Now you know what excel spreadsheets can help with as you run your own business, it’s probably a good idea to learn how to set one up and use it!
Setting up an excel bookkeeping system for small business bookkeeping
Small business accounting is made much easier with an excel template. Here’s everything you need to know about setting one up for your business.
Step one: Find an excel template for bookkeeping
This first step is simple enough. Simply find an excel template that works for you and your business needs.
Make sure, as a minimum, the template includes a chart of accounts, a transactions sheet, and an income statement sheet. Everything else you can add later – but these 3 things are the cornerstone of daily bookkeeping.
Step two: Setting up the chart of accounts
Your chart of accounts essentially works like a big reference table that tells you how to categorise transactions as they happen for your business.
Each business’ chart of accounts will look different because each business has different transactions, expenses, and incomes.
Every transaction to do with your business needs to be included in your chart of accounts so you know how to categorise future data as you input it.
They should be categorised either as:
- Income OR
- Expense OR
- Cost of goods sold (the money you spend making products before you sell them)
Step three: Setting up the income statement sheet
Your income statements are where you put all of your monthly income, expenses, and costs of goods sold, as per your chart of accounts.
Your income statement should separate each category out, so you end up with 3 sections showing what’s coming in (income), what’s going out (expenses), and what you’re spending to do your job (costs of goods sold).
You’ll also want to make 12 copies of this sheet to generate monthly income statements that you can monitor throughout the year.
If you’ve used a template, then the formulas will be in place to show net income, profit and loss, and provide information about cash flow.
Step four: Adding additional sheets
You may need additional sheets for your business to help with your bookkeeping. These may include:
- A sheet to track invoices (where you input invoice reference numbers to make sure you have included everything in your transactions sheet)
- A sheet for projecting cash flow (to help with business planning overtime)
Using your new bookkeeping system
With the above steps complete, your new bookkeeping system is ready to go. Every day, you will need to manually input your business transactions to track money leaving and money coming into your business bank accounts.
Here are the major ways you’ll be using bookkeeping in excel every day.
As a single-entry bookkeeping system, you’ll only need to input each transaction once (as opposed to when you use a double-entry bookkeeping system which inputs all the info twice).
For every transaction you’ll need:
- A record of the transaction: such as credit or bank statements, PayPal receipts, or paper receipts from a cash payment.
- To record the date.
- To describe and categorize transactions in the excel template.
Your transaction sheet will need to be filled out each day.
Monthly income statement
By setting up the income statement sheet as described in step three above, you’ll be able to look over your income statement each month to see if you have a net profit or loss, etc. These will be filled out automatically if you have used a template as the excel formulas should already be set up for the income sheet to take the info from your transaction sheet.
A final word on bookkeeping software
Using excel is fine, but it’s a long task that requires you to input data daily, and it’s important that you’re accurate.
By using accounting software or online bookkeeping service, automated software is able to take care of everything for you. By using this method of automated bookkeeping, small businesses no longer have to deal with the time-consuming process of entering all the info into excel.
So, yes, you can do bookkeeping in excel, but you might find bookkeeping and accounting software a little easier.
Still, excel is a great place to start as a small business to save money, so getting to grips with it is important before you move to accounting software to help as your business grows.
Image source: Freepik Premium