Microsoft Excel is a productivity tool that’s designed to plan budgets, record expenditures, chart data, and calculate mathematical formulas. You can manage all of your financial data through this spreadsheet and perform general accounting and financial analysis calculations. For small businesses who are looking for an efficient way to maintain their records, Excel may just be the software you need.
Beginners may find Excel quite complicated due to its vast functionality, but with a little bit of guidance, you’ll be able to utilise it to its full potential. Here’s how to use Excel spreadsheets for small business accounting. If you’re interested in taking a 1 on 1 Excel course in Perth contact us today for a chat.
Cash basis accounting
Most small businesses use the cash basis method to record their accounting transactions for their income and expenses. To start with, fill in the column headers with the transaction number, date, and product description. Also, make sure to add column headers for the income, expense, and account balance.
This process is quite similar to placing your transactions in a checkbook register. Once you purchase a product, you enter the amount in the expense column. Similarly, if a customer purchases a product, you enter the amount in the income column. For each transaction you make, you will need to add or subtract the amount from your account balance.
If you want, you can create different sheets each month or just use one sheet for tracking all of your transactions.
Accrual basis accounting
Accrual basis accounting is a method of accounting that reports revenues earned on the income statement. If you’re interested in implementing this method, you’ll need to prepare multiple accounts. Here’s the accounting equation that will serve as a guideline for all of your transactions:
Assets = Liabilities + Shareholder’s Equity
This means that your asset account totals must be equal to your liability and equity account totals. If your assets increase, so does your liabilities and equity.
First things first, you should create a chart of accounts. The different account categories are as follows:
- Assets
- Liabilities
- Equity
- Revenue
- Expense
Each account category has different accounts written on it. For example, asset accounts can contain accounts pertaining to fixed assets, cash, inventory, accounts receivable, and other assets. The liability accounts on the other hand, can contain accounts pertaining to wages payable, accounts payable, and other payable expenses.
Keep in mind that accounts receivable are payments owed to you using credit purchases while accounts payable are money you owe using credit purchases.
In the first worksheet of the book, create your chart of accounts. To make it easier to understand, you can list them based on the account type. Don’t forget to assign a number to each account.
Open a new worksheet and create a column named Cash. Create a column for the debit and the credit. Each time you record a debit, another account is created. Same thing goes for credit. You may need to refer the chart you created to help you figure out when to debit or credit. For example, if you received cash by selling $300 of your inventory, your entries will be as follows:
The key takeaway is to transfer the value from one account to another. If you make an entry, you must also enter that into the corresponding account. Most people get confused by the possibility of increasing two different accounts. For example, if you purchase materials on credit, you should increase your asset account (materials) with its value and increase your liability account (account payable for the supplier of the materials).
A debit in accounts payable leads to a decrease in the account value whereas in an account receivable, a debit leads to an increase in the account value. If you paid off one of your credit accounts, you’d debit that account (which is a liability account) and debit your cash account (which is an asset account).
Say you allowed a business to purchase 200 items on credit. You will then need to create an account named after that business in your accounts receivable (under your asset accounts). Since the money is owed to you, it is considered an asset account.
If you charged a dollar per item, you’d enter $200 debit in the accounts receivable for that business and also enter a $200 credit in your inventory. Make sure to reference the chart of accounts to help you identify whether to decrease or increase an account.
If on the other hand, you’ve purchased 100 raw materials as credit, then you will have to create a liability account named after the business you’ve purchased the materials from. Since you owe them money, it is then considered a liability account. Assuming the price was similar to our previous example, you would enter $200 debit in the account payable from the business you purchased the materials. Also, you would enter a $200 debit in your raw materials inventory account since you increased both your assets and your liability.If on the other hand, you’ve purchased 100 raw materials as credit, then you will have to create a liability account named after the business you’ve purchased the materials from. Since you owe them money, it is then considered a liability account. Assuming the price was similar to our previous example, you would enter $200 debit in the account payable from the business you purchased the materials. Also, you would enter a $200 debit in your raw materials inventory account since you increased both your assets and your liability.
This guide covers basic accounting concepts and Excel functions that should provide you with enough information to get your accounting procedures rolling for your small business.There are plenty of other accounting functions for Excel and as you familiarize yourself with the spreadsheet, you’ll be able to create your own financial sheets, generate reports, and even forecast your expenses. Continue expanding your knowledge on Microsoft Excel and you’ll quickly master the art of macro programming your business accounting.