Empower Youth

Excel Bookkeeping | How to Create a Bookkeeping System in Excel

Ryan Smith

fundera

Excel Bookkeeping, In Summary

In order to manage your basic bookkeeping using Excel, you’ll want to start with a template that includes a chart of accounts, transaction sheet, and income statement sheet. To create your own Excel bookkeeping system, you’ll customize this template, save it to your computer, and then record and categorize your day-to-day transactions within the document. Performing your business bookkeeping in Excel will be a manual and time-consuming process, and therefore, many business owners will want to consider investing in bookkeeping or accounting software solutions.

When it comes to your business finances, one of the most important tasks will be the day-to-day managing of your books—ensuring that you’re tracking your income and expenses, reconciling transactions, and entering everything accurately. As far as business accounting goes, bookkeeping is the core process that keeps you organized, up-to-date, and ready to handle any other financial tasks that may arise.

This being said, although some business owners to choose to utilize a bookkeeping software or employ a professional bookkeeper—if you’re a particularly small or new business, you may choose to use a tool you likely already have at your disposal, Microsoft Excel—and create an excel bookkeeping system.

Luckily, Microsoft Excel is very accessible and there are a variety of templates available to guide you if you do decide to perform your basic bookkeeping using Excel (or even using Google Sheets). Therefore, to help you get started, we’ll break down what it looks like to manage your small business bookkeeping in Excel, what the limitations of this spreadsheet system are, and finally, we’ll explain exactly how to create your accounting system in Excel.

Small Business Bookkeeping in Excel: What to Expect

As Microsoft Excel is a program you likely already have on your computer, deciding to do your small business bookkeeping with Excel seems like an easy way to set up and manage your books—especially if you’re just starting out and don’t have the budget to spend on third-party software or on a bookkeeping professional. As a spreadsheet-based program, Excel can be used for many purposes, including basic bookkeeping and keeping accounts—however, it does have limits, especially in comparison to a platform like QuickBooks Online or Wave.

This being said then, when creating an Excel bookkeeping system, you should be able to:

Keep an organized list of “accounts” (the types of transactions you’ll make)
Enter and categorize transactions
Generate income statements (also known as profit and loss statements) from transactions you’ve entered—you’ll be able to do this by either manually transferring data from your list of transactions, or using formulas to automatically import it.
Track invoices you’ve sent clients and cross-check them with the transactions you’ve entered

With this in mind, unlike automated bookkeeping software, Excel will not be able to:

Connect to your business bank accounts or credit cards and automatically import and sort transactions
Automatically generate typical financial documents based on the data from your books
Detect or notify you of data entry errors
Easily allow multiple users to work within your system

Single-Entry Excel Bookkeeping

Ultimately, as you’ll have to enter information and manage your basic bookkeeping within Excel manually, you’ll more than likely want to use the single-entry method—meaning you enter each transaction into your system once, categorizing it as either leaving your business (expense/loss) or entering (profit/income). Once these transactions are entered, you’ll be able to use them to generate an income statement, which summarizes your income and expenses for a specific period of time (usually a month) and informs how your business is making or losing money.

This system of accounting is different from the double-entry method, in which you enter every transaction twice, once as a debit, and once as a credit.

Although it is technically possible to create a double-entry accounting system in Excel, we wouldn’t advise doing so. Considering the manual nature of Excel bookkeeping, maintaining a double-entry system is going to be complex, time-consuming, and highly-error prone. As we’ll discuss later, if your business grows to the point where you think double-entry accounting is necessary, you’ll want to invest in a more automated software to manage your finances.

How to Create a Bookkeeping System in Excel

Now that we know a little bit more about what Excel bookkeeping will entail, let’s discuss what you’ll need to do to actually create your own system for your small business.

Step 1: Start with a bookkeeping Excel sheet template.

If you have previous Excel experience, you might be able to build your own bookkeeping Excel sheet from scratch. However, if you don’t have Excel experience or you’re just looking for the simplest, quickest way to get started, you’ll want to find and download an Excel bookkeeping template.

For the purpose of our discussion, we’ll use an example based on a template from Bench—this template breaks your Excel bookkeeping system into three sheets—a chart of accounts sheet, a transactions sheet, and an income statement sheet. Within this document and these three sheets, you’ll be able to enter and categorize your transactions and generate income statements.

On the other hand, if you’d prefer to use Google Sheets rather than Excel (they operate much in the same way), you can check out this basic profit & loss workbook from The Spreadsheet Alchemist. You can use this template by selecting “File,” then “Make a copy,” and save the Workbook to your drive. In this particular 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 on the P&L Summary tab.

3 Necessary Parts of an Excel Bookkeeping System

As we mentioned (and as you’ll see illustrated below), the template we’ll be discussing is separated into three parts: a chart of accounts, a list of transactions, and an income statement. This being said, regardless of what kind of template you use or create, these three elements will be essential to performing your basic bookkeeping using Excel.

Let’s explain:

First, the chart of accounts is the bookkeeping Excel sheet that will include all of 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.
Next, the list of transactions will 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.
Finally, the income statement sheet will be where the numbers from your list of transactions are copied over, either automatically or by hand. The income statement contains the calculations that summarize what you’ve spent and what you’ve earned, and also shows you your net income. Without this summary, your books won’t actually inform you about how your business is performing.

Step 2: Customize the chart of accounts within your template.

So, with these essential pieces of your Excel bookkeeping system in mind, let’s discuss how you can take one of these templates, customize it, and use it to maintain your books.

This being said, the first thing you’ll need to do is customize your chart of accounts. As you can see in the example below, your chart of accounts sheet will serve as your point of reference—reminding you how to categorize different transactions.

Example of an Excel chart of accounts. Photo: Bench

As this screenshot shows, you’ll want to make a list of every type of expense or income your business uses, and create an account type for each. You’ll list these account types in the chart of accounts, and once again, you’ll use this bookkeeping Excel sheet for reference when you enter and categorize transactions later.

It’s important to note that your chart of accounts doesn’t have to look exactly like this one—after all, every business has different income and expenses. For instance, if you travel for work, you might have an account for “Gas and Auto.” If you telecommute, on the other hand, you might have one for “Monthly Internet.”

Ultimately, however, to keep your accounts in Excel, each account should be categorized as one of the following:

Income: Any account you use to track money coming into your business
Expense: Any account you use to track money leaving your business
Cost of goods sold (COGS): The money you spend creating the product you sell your customers or clients

Step 3: Customize the income statement sheet.

Once you’ve customized your chart of accounts, you’ll next want to work on customizing your income statement sheet. Within this sheet, (as you’ll see in the image below) you’ll enter the information from your chart of accounts—making sure to include all accounts, but keeping them separated according to whether they’re income, expenses, or COGS.

Example of Excel income statement sheet. Photo: Bench

After you’ve edited the income statement Excel bookkeeping sheet, you’ll want to make sure to duplicate this sheet (if your template only has one). In order to generate monthly income statements, you’ll need separate sheets for each month. You can make 12 copies of the original income statement, one for each month in the year—don’t forget to label each one and enter the date range in the appropriate cell.

With regard to the income statement, one of the benefits of using a pre-made template is that the formulas are already in place to calculate your total expenses and net profit (as shown above) based on the information from the other sheets. If you create your own bookkeeping Excel sheet, on the other hand, you’ll either have to manually perform these calculations or input the accounting formulas yourself.

Step 4. Add a sheet for tracking invoices.

Although this step isn’t required in order for you to do your basic bookkeeping in Excel, it can be a helpful step in terms of organization. To explain, when you enter income from paid invoices into your transactions sheet, you’ll want to include the invoice number. By doing this, you’ll be able to cross-reference it to 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.

This being said, you can add a tab within your bookkeeping Excel document, or, once again, download an invoice tracking Excel template. If you download a template, you can copy and paste the contents into a new sheet within your master bookkeeping Excel document.

If you do decide to add an invoice tracking sheet, you’ll want to keep this sheet next to your transactions sheet within the Excel document—this will make it easier to check for reference.

Step 5. Add a sheet for projecting cash flow.

Once again, this step isn’t required for you to manage your small business bookkeeping in Excel. In fact, a spreadsheet-based program like Excel isn’t going to be effective for creating a proper cash flow statement that you could share with potential investors. This being said, however, for the sake of your own business planning, you can still use a simple spreadsheet to plan your cash flow from month to month.

Just like the tracking invoice sheet, you can simply create a new sheet within your master document and paste a cash flow tracking template or, of course, make your own. To get started on this route, you can download Fundera’s cash flow template here.

Step 6. Save the file to a secure location on your computer.

After you’ve edited and added all of the different sheets within your master Excel bookkeeping document, you’ll want to be sure to save the file in a secure location on your computer or drive.

As we mentioned earlier, unlike many cloud-based software programs, which house your information and allow you to log in and out of the platform via any web browser—by doing your basic bookkeeping using Excel, you’ll only have access to your system through this one file.
Therefore, you’ll want to make sure the file is secure, yet accessible when you need it to perform your day-to-day bookkeeping tasks.

How to Use Your Excel Bookkeeping System

At this point, you’ve succeeded in creating your own accounting system in Excel and now you need to actually use it. Let’s explain what that process looks like:

Inputting and Categorizing Transactions

As you may have noticed, when we discussed how to create a bookkeeping system in Excel, we didn’t mention customizing the transaction sheet. Why’s that? Unlike the other sheets, you won’t actually need to edit the transaction sheet until you’re using your system.

In fact, inputting and categorizing transactions will be a major part of managing your basic bookkeeping in Excel. This being said, every time a transaction takes place, you’ll record and categorize it using the following process:

Get a record of the transaction: Every time a transaction takes place, you’ll need a record to refer to for the relevant information that you’ll record within your Excel bookkeeping system. This record may be a credit or bank statement, the information in your PayPal account, or a receipt from a cash payment.
Enter the date: Based on the record you have, you’ll enter the date the transaction was recorded under the appropriate column.
Describe and categorize the transaction: Next, you’ll enter the relevant account under the appropriate column (as shown below). You’ll fill in what the transaction was, how much it was, and then the category based on the ones you’ve established within your chart of accounts. You’ll want to make sure the category appears exactly as it’s written in the chart of accounts.

Example of transactions recorded. Photo: Bench

Generating Income Statements

Using this process of inputting transactions, you’ll manage your small business bookkeeping in Excel on a day-to-day basis. At the end of every month, however, you’ll want to use the income statement sheet we described above to get insights on your income, expenses, and profit.

Once again, in this case, a pre-created template will be helpful, as most of these templates will already have programmed the formulas to generate your income statement (also called a profit and loss statement). With these templates, the information will be pulled from your transactions sheet based on the date—so it’s all the more important to make sure you’ve input this data accurately.

On the other hand, if you’re not using a template with prebuilt formulas, you’ll have to manually calculate the information to populate your income statement. To do this, you’d review your transactions for the time period and then add totals for each account. For example, if you have three expense transactions categorized as “Computers or Equipment,” you would take the dollar amounts from those three transactions and add them together—resulting in your “Computers or Equipment” total for the month. It may be helpful to create a separate calculations sheet within your master document where you can track and perform these calculations (the digital equivalent of a scrap piece of paper for calculations).

Through this process, you should be able to calculate and fill in all of the pieces of your income statement—revenues, cost of sales, and expenses.

Once you’ve completed your income statement for the month, you’ll want to make sure to save your Excel bookkeeping file. You might also decide to save a separate file on your computer with the income statement for the month—this will be especially helpful for an accountant to reference when it comes to business taxes or other financial planning.

When You Need Bookkeeping or Accounting Software

As you can see, creating and maintaining an Excel bookkeeping system is an extremely manual process. You’ll not only need to find a template or make your own, but you’ll also need to manage the file on a day-to-day process—inputting transactions based on external records and perhaps even making the calculations necessary to generate a monthly income statement. Therefore, regardless of the size of your business, doing your basic bookkeeping in Excel is going to be time-consuming and very error-prone.

This isn’t to say, however, that you can’t create your accounting system in Excel and manage it effectively. If you’re a have a very small business or side hustle, an Excel bookkeeping system may be all you need. On the other hand, if you plan on growing or expanding your business in any real capacity, you’re going to want to invest in bookkeeping or accounting software.

How do you know when you need to start using a bookkeeping software platform? Generally, we’d recommend that all business owners choose this method to manage their finances (especially considering that there are free accounting software solutions out there), however, if you’re unsure of whether or not this type of software is right for you, you might ask yourself some of the following questions:

Are you finding that it takes too much time and effort to do your bookkeeping in Excel?
Do you find that the data-entry involved is tedious and that you make mistakes?
Would you prefer to manage your bookkeeping within a system that automatically pulls in transactions and categories them?
Would you like to be able to access your bookkeeping system wherever you have internet access?

Essentially, if your answer to any of these questions is “yes,” it’s worth looking into an automated bookkeeping or accounting software. Any software you choose is going to end up being more accessible, more accurate, and easier to manage and use. Plus, as your business grows, it will become more and more difficult to manage your Excel bookkeeping system.

As we mentioned briefly above, if you think your business will benefit from a double-entry bookkeeping system, you’ll definitely want to look for an alternative solution. Once again, although it is possible to create a double-entry accounting system in Excel, it would be extremely troublesome and time-consuming to manage and maintain.

Top Alternatives to an Excel Bookkeeping System

Therefore, if you decide that creating a bookkeeping system in Excel is not the right path for you, or if you outgrow your system, you might consider these top alternatives:

Wave Accounting

If you’re hesitant about a bookkeeping accounting software platform because of cost, accessibility, or complexity, Wave accounting is going to be the best platform for you. Wave, in fact, is one of the only truly free business accounting software systems and it will allow you to perform all of your essential bookkeeping tasks without the unnecessary or overwhelming features found in some other platforms.

You can sign up for a Wave account quickly and easily online—and as web-based software, you’ll be able to use your Wave account wherever you have internet access. Additionally, with Wave, you’ll be able to connect your business bank and credit card accounts and transactions will appear in your system automatically. From this point, you’ll be able to manage your transactions, track your income and expenses, as well as invite collaborators, create and send customized invoices, and more.

Although you can use this platform completely free, Wave also offers paid add-on options for accepting payments and running payroll.

Ultimately, even if you don’t utilize all of the features Wave has to offer, this free system is going to allow you to manage your business bookkeeping much more quickly, easily, and accurately, in comparison to an Excel bookkeeping system.

Paid Accounting Software

Although Wave is going to be your only real option if you’re looking for a free Excel alternative, there are a variety of low-cost paid bookkeeping and accounting software options as well. This being said, many of these platforms will offer multiple different plans—with a range of included features and pricing structures. If you plan to grow or build your business substantially, you’ll definitely want to consider investing in one of these solutions, even if you just start with a basic plan. In particular, you might look into:

QuickBooks Online: One of the most popular accounting software solutions for small business owners, QuickBooks Online has four different plans (starting at $20 per month) and can accommodate a variety of types of businesses—especially those that are product-based.
FreshBooks: FreshBooks is a great paid option for service-based businesses that could benefit from a system that focuses on invoicing capabilities. With FreshBooks, you can choose from three different plans, starting at $15 per month.
Xero: A top alternative to QuickBooks Online, Xero also has the ability to accommodate a wide variety of businesses in terms of shape and size. Notably, Xero also includes unlimited users, regardless of which of their three plans you choose.

Xero pricing starts at $9 per month for their basic plan.

Buy QuickBooks Online Now for 50% Off

Excel Bookkeeping: The Bottom Line

So, as we’ve shown, by following a few simple steps, any business owner can create their own accounting system in Excel. This being said, if you’re planning to manage your basic business bookkeeping using Excel, we’d recommend starting with a template that contains a chart of accounts, transactions sheet, and income statement sheet and customize this document to function for your business.

It’s important to remember, however, that managing your Excel bookkeeping system will be a manual and time-consuming process. Therefore, if you’re looking for a simpler and more efficient way to manage your business finances, we’d suggest exploring different bookkeeping or accounting software solutions and finding the one that will work best for you.

Despite the seemingly easy accessibility and familiarity of Excel, a simple and free software like Wave is very likely going to be easier for most business owners to use to maintain their bookkeeping on a day-to-day basis.

CONTRIBUTING WRITER AT FUNDERA

Ryan Smith

Ryan Smith writes for Bench, the online bookkeeping service that pairs you with a dedicated bookkeeping team and elegant software to do your books for you.

Read Full Author Bio

ORIGINAL Blog

Excel Bookkeeping | How to Create a Bookkeeping System in Excel

Related Topic

Excel Bookkeeping, In Summary