How to Create a Bookkeeping System in Excel

Doing your own bookkeeping in Excel makes sense if your business is just starting out, and you don’t have a professional bookkeeping solution in place. Before your business expands, you may find you can handle the general bookkeeping tasks needed to stay organized and maintain your business’s finances.

But if using Excel intimidates you (you’re not alone!), this article will make it manageable with ready-made templates and simple directions. Here’s what you need to get started doing your own books.

What Bookkeeping in Excel Means

When you do your bookkeeping in Excel (or any other spreadsheet software), you’ll be doing things like:

• Keeping an organized list of “accounts” (the types of transactions you’ll make)
• Entering and categorizing transactions
• Generating income statements (also known as profit and loss statements) from transactions you’ve entered. You do this by either manually transferring data from your list of transactions, or using formulas to automatically import it.
• Tracking invoices you’ve sent clients, and cross-checking them with the transactions you’ve entered

Single-Entry Bookkeeping in Excel

This article will focus on the single-entry method of bookkeeping. It’s pretty straightforward—every transaction is entered once and categorized as either leaving your business (expense/loss) or entering it (profit/income).

The transactions you enter are used to generate an income statement, which summarizes your income and expenses for a specific period of time (usually a month). That way, you can understand how your business is making or losing money.

A quick aside about double-entry: The double-entry method of bookkeeping is standard for larger, more complex businesses.

Getting Started With an Excel Bookkeeping Template

Not everyone is confident in their Excel skills and you might not have the time to build bookkeeping sheets from scratch. To get started with single-entry bookkeeping, your best bet is to download a template.

Bench’s Income Statement Template for Excel is a good place to start. It’s got everything you need to enter and categorize transactions and generate income statements. All your transactions—both income and expenses—are recorded on one sheet. Then the income statement sheet uses that info to generate a summary.

If you’d prefer to use Google Sheets rather than Excel, check out this basic Profit & Loss Workbook from The Spreadsheet Alchemist. Make a copy of the Workbook for your own use by selecting “File > Make a copy…” and save it to My Drive.

In this workbook, expenses and income are recorded on separate sheets. And, in order to create an income statement, you’ll have to manually add up some transactions, and input them into the income statement.

Understanding Your Bookkeeping Spreadsheet

Regardless of what template you use, you need to be sure it has each of the following:

1. A chart of accounts: This includes all the accounts that make up your books. You can think of each account as a category. Every transaction you record needs to get sorted into one of these categories.

2. A list of transactions: This contains details of every business transaction you enter—the date, a description, the amount, and the account. Transactions may be divided into separate income and expense sheets, or all included in one big sheet.

3. Income statements: This is where the numbers from your list of transactions are copied over, either automatically or by hand. The income statement crunches the numbers, summarizing what you’ve spent and what you’ve earned, and shows you your net income. Without a summary, your books won’t tell you much.

Setting up a Bookkeeping Spreadsheet for Your Business

Here are the main steps for how to create a bookkeeping system in Excel.

1. Customize the Chart of Accounts

Make a list of every type of expense or income your business uses, and create an account type for each. List those account types in the chart of accounts. You’ll use this sheet for reference when you enter and categorize transactions later.

Every business has different income and expenses. For instance, if you travel for work, you might have an account for “Gas & Auto.” If you telecommute, you might have one for “Monthly Internet.”

Each account should be categorized as one of the following:

• Income is any account you use to track money coming into your business.
• Expense is any account you use to track money leaving your business.
• Cost of goods sold (COGS) is the money you spend creating the product you sell your customers or clients. Learn more about calculating cost of goods sold.

2. Customize the Income Statement Sheet

Next, enter the info from your chart of accounts into your income statement sheet. Include all your accounts, but keep them separated according to whether they’re income, expenses, or COGS. Your template should have separate sections for each.

3. Make Copies of the Income Statement Sheet

Your template only comes with one income statement sheet. In order to generate monthly income statements, you’ll need separate sheets for each month. Make 12 copies of the original income statement, one for each month in the year. Label each one, and be sure to enter the date range in the appropriate cell.

4. Add a Sheet for Tracking Invoices—Optional

When you enter income from paid invoices into your transactions sheet, you should include the invoice number. That way, you can cross-reference it, and avoid errors—like forgetting to enter invoice payments, or entering the same payments twice. It’s much easier to cross-reference 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, create a new sheet in your template. Copy and paste the contents of the invoice template you downloaded into the new sheet.

Keep the tab for your invoice tracker next to your transactions sheet—it’ll be easier to check for reference.

5. Add a Sheet for Projecting Cash Flow—Optional

Spreadsheets aren’t up to the task of creating a proper cash flow statement that you can share with potential investors. But, for the sake of your own personal planning, you can still use a simple spreadsheet to plan your cash flow month to month.

To get started, download our cash flow template. You can copy and paste it into a new blank sheet next to your transactions.

Keeping Your Books Up-to-Date in Excel

Once you’ve customized your bookkeeping template for your business, you’ll mostly be using the transactions sheet to keep everything up-to-date. Here’s how:

Input and Categorize Transactions

Every time a transaction takes place, you need to enter it into the Transaction sheet, in its own row. Then you need to categorize it.

1. Get a record of the transaction. That could be a credit or bank card statement, info in your Paypal account, or receipt from a cash payment.
2. Enter the date the transaction was recorded under the appropriate column.
3. Categorize the transaction. Enter its relevant account under the appropriate column (“Category,” “Income Type,” “Account,” etc.) Be sure to type it exactly as it appears on the chart of accounts.
4. If there’s a column for it, enter a description of the transaction, or its source. This is just a note to help you remember the exact nature of the transaction. When listing income from paid invoices, be sure to include the invoice number.

Generate Income Statements

Some templates, like the Bench one mentioned at the beginning, will automatically generate an income statement for you, by pulling your transactions into the income statement tab. If you’re using a different setup, you’ll need to do it by hand.

At the end of each month, review your transactions for that time period. Then add totals for each account.

For instance, let’s say you have three expense transactions categorized as “Computers or Equipment.” Take the dollar amounts from those three transactions, and add them together—you’ll get your “Computers or Equipment” total for the month.

You might want to create an extra “scrap” spreadsheet where you can take note of these numbers for the time being.

Once you’ve totaled each account, copy that info to your income statement sheet for the month.

***

As your business grows, you’ll find DIY bookkeeping becomes more complex and time-consuming. Eventually, you’ll need to invest in a professional solution. Until then, with the help of this article, you know how to create a bookkeeping system in Excel.

The post How to Create a Bookkeeping System in Excel appeared first on Fundera Ledger.

from Fundera Ledger https://www.fundera.com/blog/how-to-create-a-bookkeeping-system-in-excel/