But there are ways to make the process easier. We’ll show you how, using Bench’s Income Statement Excel Template to get started. Show
First: What Excel can and can’t doMicrosoft 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. Here’s a summary:
Single-entry bookkeeping in ExcelBefore we get started on DIY bookkeeping, download a copy of Bench’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 Bench). What you need to use the Income Statement Template for DIY bookkeepingBefore you get started with Bench’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. Understanding the Income Statement TemplateThere are three sheets in Bench’s Income Statement Template: 1. The Chart of AccountsThis 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. 2. TransactionsThis contains details of every business transaction you perform—the date, a description, the amount, and the account. 3. The Income StatementThis 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. Customizing the Income Statement Template for your businessThe 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. 1. Add or remove accounts from the Chart of AccountsThe 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:
2. Clear the Transactions sheetBefore 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. 3. Add or remove accounts from the Income StatementBy 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. 4. Make copies of the Income Statement for each monthIn 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. 5. Optional—Add a sheet for tracking invoicesWhen 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, or get one for Google Sheets. 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. 6. Optional—Add a sheet for projecting cash flowExcel 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. To get started, check out our Google Sheets Cash Flow Projection Template. Copy and paste it into a blank sheet next to your Transactions. Keeping your books up to date in ExcelOnce you’ve customized the Income Statement Template for your business, you’ll mostly be using the Transactions sheet. Input and categorize transactionsEvery 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 Bench, your transactions are automatically imported and categorized for you. But for DIY bookkeeping in Excel, you’ll be doing it yourself.
Save income sheetsBy 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. Advanced accounting in ExcelIf 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. Outgrowing DIY bookkeeping in ExcelIf 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. What's Bench?We're an online bookkeeping service powered by real humans. Bench gives you a dedicated bookkeeper supported by a team of knowledgeable small business experts. We’re here to take the guesswork out of running your own business—for good. Your bookkeeping team imports bank statements, categorizes transactions, and prepares financial statements every month. Get started with a free month of bookkeeping. This post is to be used for informational purposes only and does not constitute legal, business, or tax advice. Each person should consult his or her own attorney, business advisor, or tax advisor with respect to matters referenced in this post. Bench assumes no liability for actions taken in reliance upon the information contained herein. How do I set up a small business bookkeeping in Excel?How to Create A Small Business Bookkeeping System in Excel. Keep your business and personal finances separate. ... . Decide what accounting method you want to use. ... . Create a chart of accounts. ... . Create an income and expense sheet. ... . Create optional bookkeeping reports.. Does Excel have an accounting template?These financial records will need to be constantly added into an up-to-date company ledger, which is essentially your master accounting template. Excel does offer a basic, pre-built company ledger template.
How do you create a bookkeeping sheet in Excel?How to Create a Bookkeeping System in Excel. Step 1: Start with a bookkeeping Excel sheet template. ... . 3 Necessary Parts of an Excel Bookkeeping System. ... . Step 2: Customize the chart of accounts within your template. ... . Step 3: Customize the income statement sheet. ... . Add a sheet for tracking invoices.. Where can I download free Excel templates?Go to Office.com. Click Templates at the top of the page. On the Templates page, click Excel. Tip: To see more templates, under BROWSE BY CATEGORY, click the category (like Calendars) that you want to see.
|