How To Use Excel Spreadsheets for Small Business Accounting

Excel is a Microsoft Office program that's designed to help calculate, tabulate, store, chart, and compare data for current and future reference. Its features are robust. Using Excel for accounting can provide an excellent tool for performing these tasks for small businesses.

Excel can be used with as little or as much complexity as you prefer. Small business owners can do all their bookkeeping in Excel.

Cash Basis Accounting in Excel

Most small businesses use cash basis accounting. Start a new worksheet if this is your method of accounting and enter column headers for the date, transaction description, and a transaction number. Include column headers for income, expense, and account balance.

This is very similar to entering your transactions in a checkbook register. Enter an expense for that amount if you purchase something. You'll enter income if a customer pays for a product. Either add or subtract the amount from the account balance for each type of transaction.

Accrual Basis Accounting in Excel

You'll have to prepare different accounts if you're using the accrual basis accounting method or double-entry accounting. The accounting equation is the guideline for all transactions:

Assets = Liabilities + Shareholders' Equity

The total of your asset accounts must equal the total of your liability and equity accounts. Your liabilities and equity should increase if your assets increase.

You'll first have to make a chart of accounts. The different categories of accounts are asset, liability, equity, revenue, and expense.

Each category of account has different accounts within it. Assets accounts can contain accounts such as cash, accounts receivable, inventory, fixed assets, or other assets. Accounts receivable are payments owed to you for purchases from you using credit.

Liability accounts for small businesses usually have accounts payable, wages payable, or any other payable expenses. An account payable is money you owe for purchases on credit.

Create your chart of accounts in the first worksheet of the workbook. You can list them by account type such as asset or liability to make it easier to understand. Assign a number to each account in the next column.

Create an account labeled "Cash" in a new worksheet. Now make a column for debit, and a column for credit. Another account is credited, and vice versa, every time you record a debit. You might have to reference the account type chart you made to help you discern when to debit or credit an account and what each action does to an account.

If you sold $100 of your inventory and received cash, since both cash and inventory are assets, your entries would look like this:

You would debit cash, since you debit an asset to record an increase. Similarly, you would credit inventory, since you credit an asset while recording a decrease.

The key concept to remember is that you're transferring value from one account to another with this method. If you make one entry, you must make another entry in a corresponding account.

One concept that confuses many people is that it's possible to increase two different accounts. You would increase your asset account (equipment) with the value of the item if you purchase a piece of equipment on credit, and you would increase your liability account (account payable for that supplier).

A debit in accounts payable decreases the account value, while a debit increases the account value in an account receivable. You would therefore debit that account (a liability account) and debit your cash account (an asset account) if you paid one of your credit accounts.

You'd create an account named after that business in your accounts receivable under your asset accounts if you allowed a business to purchase 100 items on credit. It's an asset account because it is owed to you. You'd enter a $100 debit in the accounts receivable for that business if you charged one dollar per item and enter a $100 credit in inventory. Remember to use the account type chart to help you increase and decrease different accounts.

The liability account is named after that business if you'd purchased on credit 100 in raw materials needed to make your products from another business. It's a liability account because you owe them money. Assuming the price was the same as in the previous example, you would enter a $100 debit in the account payable for the company you purchase from and a $100 debit in your raw materials inventory account. You debit both accounts because you're increasing an asset and a liability.

Formulas and Formats for Accounting

You can use Excel's built-in formats and formulas to help you with your accounting. Highlight the cells you're working with then left-click on them so you can bring up a menu. Choose the "Format" option and choose "Accounting" under the "Number" tab. This places the cells you highlighted in an accounting format, automatically placing a dollar sign in them.

It also places parentheses around negative numbers, which you could enter when you're decreasing any accounts. Enter the following formula (assuming the cells are A15 through B15) to have Excel automatically calculate balances for you:

=Sum(A15:B15)

This will add the values of cells A15 through B15 and display the result.

You can use the sum function to add your total assets, total liabilities, and total equity. This will help ensure that your assets equal the sum of your liabilities and equity, balancing your finances using double-entry, accrual-based accounting.

Expand Your Microsoft Excel Knowledge

These are very basic accounting concepts and uses of Excel that should provide you with enough information to get your accounting procedures started for your small business. But there are many other functions for accounting purposes in Excel. You'll be able to use it to generate reports, forecast expenses, and design your own financial sheets for reporting and analysis as you become more familiar with the program and with accounting.

Frequently Asked Questions (FAQs)