How to Do Small Business Bookkeeping in Excel Like a Boss

anaging your day-to-day finances is one of the most important tasks of any small business. Bookkeeping is the process of recording daily business transactions. Having a good system for bookkeeping will help keep your business finances organized year-round.

Some businesses choose to hire a bookkeeper or utilize accounting software for their bookkeeping. If you’re already a small business owner or just starting your own business, you can save money by using Excel to create a bookkeeping system. Chances are you already have access to the computer program. Learn how to do small business bookkeeping in Excel like a professional, and get access to our free Google Sheets and Excel bookkeeping template for small businesses.

Why use Excel for your small business bookkeeping

Microsoft Excel has been around for decades but is still used by businesses today because of its accessibility and robustness. Excel has a number of useful applications, including bookkeeping. Here are a few reasons you might want to do your small business bookkeeping in an Excel spreadsheet:

• You probably already have it installed on your computer. If not, it’s available to download for a small fee. You can also use Google Sheets, a similar program powered by Google that is completely free to use.
• Using Excel is likely cheaper than hiring a bookkeeper or investing in popular accounting software programs.
• Excel has several helpful features and functions that can help you create a small business bookkeeping system that works. You can enter and categorize transactions through Excel, create various bookkeeping templates, track invoices, and much more.
• Training on using Excel is also readily available online (often free of charge) to get you and your employees up to speed if required.

‍

How to create a small business bookkeeping system in Excel

To create a bookkeeping system for your business, you can start with a blank Excel spreadsheet or use our spreadsheet template. Depending on your experience level, using a template may be an easier way to get started. Use the following steps to set up your bookkeeping system in Excel.  

1. Keep your business and personal finances separate

Before you set up your bookkeeping spreadsheet, an essential first step is to ensure you can isolate your business transactions with ease. The easiest way to do so is to keep your business and personal finances separate with a business checking account. If you keep all of your finances in the same account and commingle business and personal funds, you risk making your finances very confusing. It can be hard to differentiate which transactions are personal and which are for your business, which can make bookkeeping difficult and create undue stress and confusion during tax time.

With a separate business account, you can easily track your business cash flow. This will help you claim tax deductions, protect your assets from liability, and gain access to certain funds that are business-only. Having a business checking account will also legitimize your business with your customers.

Novo offers one of the most popular business checking accounts for small businesses. With no hidden fees, no minimum balance requirements, a free invoicing tool, and dozens of business software integrations, Novo is built to help your business thrive.

Learn more about Novo business checking and why it’s the preferred platform for more than 200,000 businesses.

2. Decide what accounting method you want to use

There are two primary accounting methods that businesses use — cash accounting and accrual accounting. The difference lies in when you record company revenue and expenses. With cash accounting, you record revenue and expenses when money is received or paid. Accrual accounting records revenues and expenses when they are earned or incurred.

Cash accounting gives you a narrower, less accurate picture of your business finances than accrual accounting does, since you’re not tracking income and expenses as they occur. Additionally, not all businesses are permitted to use cash accounting – namely, businesses with more than $25 million in annual sales. That said, it’s preferred by some small businesses due to its simplicity.

Accrual accounting gives you a more accurate financial picture and conforms with GAAP (Generally Accepted Accounting) principles. And while you may not be making more than $25 million annually now, the accrual accounting method is set up to grow as your business grows. However, it requires more resources than cash-basis accounting and doesn’t give you a picture of how much money you actually have in your bank account at a given moment. If you adopt the accrual accounting method, it’s important to be extremely meticulous in your bookkeeping practices.

3. Create a chart of accounts

The first essential spreadsheet in your bookkeeping system is the Chart of Accounts (COA). This is a comprehensive list of all accounts used in your financial tracking. Each account serves as a specific category that transactions are assigned to, enabling thorough and organized financial monitoring.

While a template may come pre-populated with some standard accounts, you should customize any COA template to fit your unique business needs. Each account listed in the COA should include an account type (income, expense, etc.), an account name, a unique number for easy identification, and an optional brief description to aid in the correct allocation of transactions. The COA is a dynamic tool that can be updated and modified as your business evolves and its financial complexity grows.

4. Create an income and expense sheet

Next, you’ll need to create an income and expense sheet. These can either be set up as separate sheets (or tabs on the same spreadsheet) or set up in one comprehensive sheet. As transaction receipts come in, record them on this sheet.

Receipts typically come in paper or electronic form. It’s a good idea to create filing systems for both. For paper receipts, either keep them filed (either by date or alphabetically) or snap a photo of them and store them electronically with your other e-receipts. Find a system that works best for you and stick with it. 

Keeping accurate records is the key to a good bookkeeping system, and this sheet is imperative for reaching that goal.

5. Create optional bookkeeping reports

Depending on your type of business, you may want to take your bookkeeping a step further by creating some extra Excel spreadsheets to help you assess your company’s financial health. Dozens of business reports exist, but here are three reports you should consider adding to your Excel bookkeeping system first.

• Income statement sheet: Also known as a profit and loss (P&L) statement, this report shows company profitability over a period of time. The report is broken down into different aspects of your business and allows you to see which ones are the most or least profitable.
• Balance sheet: Consider a balance sheet as an overview of business assets, liabilities, and equity over a period of time. Our Excel bookkeeping template includes a balance sheet template.
• Cash flow statement sheet: A cash flow statement lets you know how much cash you received over a period of time. While your income statement relies on accrual accounting, this statement reports actual cash flow from paid invoices and expenses.

‍

Depending on your business model, you may also want to add a sheet to track inventory or invoice payments. Our free bookkeeping template includes sheets for tracking both.

‍

Download our Excel bookkeeping template

If you don’t want to start from scratch, here’s a free Excel bookkeeping template your business can utilize. It includes:

• A Chart of Accounts
• An income tracking sheet
• An expense tracking sheet
• An inventory tracking sheet
• An invoice tracking sheet
• A balance sheet

This bookkeeping template is in the form of a Google Sheet. To use it in Excel, navigate to File >> Download and select Microsoft Excel (.xlsx). Once downloaded, you will be able to open this template in Microsoft Excel.

Depending on the specifics of your business, you may need to customize this bookkeeping template to suit your business needs. This template serves as a starting point for setting up a bookkeeping system in Excel.

Download our bookkeeping template

How Novo can help with bookkeeping

One of the most important aspects of bookkeeping is the day-to-day recording, tracking, and reconciling of all of your business transactions. With Novo Business Banking, you can add details to every transaction, including categories, notes, images, documents. You can also export all of this data in a CSV file for easier bookkeeping in Excel.

Excel may or may not be the best program for generating these types of reports, so play around and see if it allows you to do what you need. Having a solid bookkeeping system in place and using it daily will help you manage your business more efficiently, give you a better sense of your company’s future, and save time.

‍

Novo Platform Inc. strives to provide accurate information but cannot guarantee that this content is correct, complete, or up-to-date. This page is for informational purposes only and is not financial or legal advice nor an endorsement of any third-party products or services. All products and services are presented without warranty. Novo Platform Inc. does not provide any financial or legal advice, and you should consult your own financial, legal, or tax advisors.

Novo is a fintech, not a bank. Deposit account services provided by Middlesex Federal Savings, F. A.; Member FDIC.