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.
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: 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. If you sold $100 of your inventory and received cash, since both cash and inventory are assets, your entries would look like this: 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. 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: 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.
