How to Use Excel Spreadsheets for Small Business Accounting

•••

Updated November 05, 2020

Excel is a Microsoft Office program designed to help calculate, tabulate, store, chart and compare data for current and future reference. Its features are robust and can be used with as little or as much complexity as you prefer. Small business owners can do all of their bookkeeping in Excel.

Cash Basis Accounting in Excel

If you are using cash basis accounting (most small businesses do), start a new worksheet, and enter column headers for the date, transaction description, and a transaction number. Include column headers for income, expense, and account balance.

Cash Basis EntriesNumberDateDescriptionIncomeExpenseAccount Balance00110/10/2020Flo's Plastics
$300.00$1500.0000210/10/2020Joe's Parts$50.00
$1550.00

This is very similar to entering your transactions in a checkbook register. If you purchase something, you enter an expense for that amount. If a customer pays for a product, you enter income. For each type of transaction, you either add or subtract the amount from the account balance.

You can make different sheets for each month, or continue to use one sheet to track all of your transactions.

Accrual Basis Accounting in Excel

If you are using the accrual basis accounting method or double-entry accounting, you'll need to prepare different accounts. The accounting equation is the guideline for all transactions.

Assets = Liabilities + Shareholders' Equity

In this context, it means that the total of your asset accounts must equal the total of your liability and equity accounts. In other words, if your assets increase, your liabilities and equity should increase.

First, you'll need to make a chart of accounts. The different categories of accounts are asset, liability, equity, revenue, and expense accounts. Small businesses may have equity accounts if they have investors or use a type of equity financing.

Each category of account has different accounts in it. For example, 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 could list them by account type (asset, liability, etc.) to make it easier to understand. In the next column, assign a number to each account.

Chart of AccountsNo.Account TitleHow to IncreaseType101CashDebitAsset102Accounts ReceivableDebitAsset103Accounts PayableCreditLiability104Advertising ExpenseDebitExpense

In a new worksheet, create an account labeled Cash, for example. Next, make a column for debit, and a column for credit. Every time you record a debit, another account is credited, and vice-versa. You might need 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, your entries would look like this:

CashDebitCreditBalance

$1000$100
$1100InventoryDebitCreditBalance

$10,000
$100$9,900

The key concept to remember is that you are transferring value from one account to another in 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 is possible to increase two different accounts. If you purchase a piece of equipment on credit, you would increase your asset account (equipment) with the value of the item, and increase your liability account (account payable for that supplier).

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

For example, if you allowed a business to purchase 100 items on credit, you'd create an account named after that business in your accounts receivable, under your asset accounts. It is an asset account because it is owed to you.

If you charged one dollar per item, you'd enter $100 debit in the accounts receivable for that business, and enter a $100 credit in inventory (remember to use the account type chart to help you increase and decrease different accounts).

InventoryDebitCreditBalance
$100$9,800Joe's Parts (Accounts Receivable)DebitCreditBalance$100
$100

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

Joe's Parts (Liability Account)DebitCreditBalance$100
$100Raw Materials Inventory (Asset Account)DebitCreditBalance$100
$100

Equity

Equity accounts are usually owners' or stockholders' equity accounts, but small businesses may not have any shareholders. If this is the case, it is generally called owners' equity. Retained earnings are included in the equity accounts because it is the profit a company has earned over its lifetime after paying any dividends (if there are stockholders). These earnings are usually saved, or reinvested in the company.

If you took $100 from your retained earnings and put it into your cash account, you would debit your retained earnings account, and debit your cash account.

Formulas and Formats For Accounting

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

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

=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 you 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

This tutorial touched on very basic accounting concepts and Excel uses that should provide you enough information to get your accounting procedures started for your small business.

There are many other functions for accounting purposes in Excel. As you become more familiar with the program and accounting, you will be able to use it to generate reports, forecast expenses, and design your own financial sheets for reporting and analysis.

ORIGINAL Blog