Sometimes life forces you to use spreadsheets. If you're planning to do your small business accounting with Excel, this is one of those times. But there are ways to make the process easier. We'll show you how, using Accracy's Income Statement Excel Template to get started.
Microsoft Excel (and its simpler, online cousin Google Sheets) is adaptable. The better you know how to use it, the more you can get done. That being said, even if you're good enough at Excel to compete internationally, there's only so much you can do with it in a DIY bookkeeping context.
Before we get started on DIY bookkeeping, download a copy of Accracy's Income Statement Template. Later, you may prefer to use a different template, or even create your own. But when you're getting started, the Income Statement Template is tidy and approachable. And it has everything you need to get a grasp of single-entry bookkeeping in Excel.
Single-entry bookkeeping is a good choice if you run a small, simple business with a low volume of transactions. Every time money enters or leaves your business, it's recorded once, as a positive (income) or negative (expense) value.
A quick heads up about double-entry bookkeeping. The double-entry method of bookkeeping is standard for larger, more complex businesses. It's more effective than single-entry for tracking cash flow and protecting against errors and fraud. However, it's also more work to maintain—and difficult to set up in Excel. If you're at the point where you believe your business needs double-entry bookkeeping, it's time to use accounting software. Or, think about hiring a bookkeeper (like Accracy).
Before you get started with Accracy's Income Statement template, make sure you understand the differences between single-entry and double-entry bookkeeping. You'll also need access to spreadsheet software such as Excel or Google Sheets.
There are three sheets in Accracy's Income Statement Template:
This lists all the accounts that make up your books. Think of each account a category. Every transaction you record gets sorted into one of these categories.
This contains details of every business transaction you perform—the date, a description, the amount, and the account.
This pulls info from your Transactions and performs some basic calculations. Then it summarizes how much income your business has earned and spent within a defined date range.
Are you currently doing your own books for your business?
The info in the copy of the Income Statement Template you downloaded is only there as a placeholder. Here's how you adapt it to your business.
The Chart of Accounts includes an account for "Gas & Auto." But if you don't drive for work, you don't need this row—so you can delete it. The same follows for any other account that doesn't apply to your business.
Likewise, if there are any transaction categories you need for your business that are missing from the Chart of Accounts, you should add them.
A note about account types:
Before you can start tracking your own transactions, you need to clear the placeholder data.
Select everything in the Date, Description/Transaction, and Category columns, and delete it.
By default, the Income Statement includes data for every account listed in the Chart of Accounts. If you've added or removed accounts from the Chart of Accounts, make the same changes to the Income Statement. For instance, if you don't track "Gas & Auto" as an expense, delete the row.
In order to generate monthly Income Statements, you'll need separate sheets for each month. Make twelve copies of the original income statement, one for each month in the year.
Then, on each Income Statement sheet, change the Date Range (Beginning Rate and End Date) to cover the relevant month. Name the sheet after the month it covers.
When you enter income from paid invoices into your Transactions sheet, include the invoice number in the description. That way, you can cross-reference it, and avoid errors—like forgetting to enter invoice payments, or entering the same invoice twice. This is a lot easier if you've got a separate sheet to track your invoices.
First, download a free invoice template for Excel. Then, add a new sheet to your Income Statement Template. Copy and paste the data from the invoice template you downloaded into the new sheet.
Keep the tab for your invoice tracker to the right of the Transactions sheet, but to the left of your monthly Income Statements. It'll be easier to check for reference.
Excel spreadsheets aren't up to the task of creating a proper Cash Flow Statement that you can present to investors. However, for your own personal planning, you can still plan your cash flow month by month in a simple spreadsheet.
Once you've customized the Income Statement Template for your business, you'll mostly be using the Transactions sheet.
Every time a transaction takes place—whether you're moving money, cash, or credit—you need to enter it into the Transaction sheet, in its own row. Then you need to categorize it.
When you use Accracy, your transactions are automatically imported and categorized for you. But for DIY bookkeeping in Excel, you'll be doing it yourself.
By the end of every month, the Income Sheet should have all the data it needs to summarize your bookkeeping for that period. Make sure to keep the Income Sheets separated, organized by month.
You may want to make a copy of each one at the end of the month, and either save it or upload it to the cloud. Come tax season, your accountant will need your income sheets for the year.
If you want to get beyond bookkeeping and start understanding your finances in deeper ways within Excel, we recommend starting with pivot tables.
Pivot tables allow you to visualize and summarize your accounting info.
In our Income Statement template, we select Data > Summarize with Pivot Table
After we select all the categories we want to see summarized, here's what we get.
This pivot table gives us a simple summary of how much we spent from each vendor and how often we bought from them.
There's much more you can do with pivot tables. If you want to learn more, check out the Microsoft Office training page for pivot tables.
And if you just want some good old fashioned Excel tips to level up, check out this in-depth blog article from Hubspot.
If your business keeps growing, you'll eventually need to move on from Excel.
Signs you're ready for a bookkeeper:
Sound like you? Learn how to dump spreadsheets and outsource your bookkeeping.
We are offering free 1 Month Basic Bookkeeping to all new customers so you can experience Accracy's seemless and professional services.
Getting ready to start your own business? Our comprehensive, actionable guide covers everything from pre-launch planning to post-launch strategies.
While an LLC is typically your safest bet when it comes to your business and personal asset protection, DBAs also have their place.
Back taxes can be an expensive and challenging issue for many small businesses. Learn how to gather documents and put together back tax returns, even when you don't have financial records.
