What tips for keeping small business books with Excel?
Using Excel for small business accounting is easy, but it will take some time to post all the transactions into Excel. You have to create a spreadsheet to enter your daily transactions. But it would be best to have different spreadsheets for all your purchases and sales. You cannot mix those entries and create one spreadsheet because if you want to know, the profit you made in your business will not work out.
I will give you an example below. Please have a look at it, which is a sample cash book showing only the expenses. – Cash basis accounting in Excel
| Date | Total | Yoga expenses | Telephone | Insurance | Advert | Cleaning | Vehicle Expenses | Travel |
| Apr-16 | 140.03 | 80.03 | 36 | 24 | ||||
| 251.49 | 121.3 | 37.19 | 93 | |||||
| 249.47 | 142.8 | 36.47 | 36 | 34.2 | ||||
| 189.76 | 115.86 | 25.9 | 48 | |||||
| 391.74 | 262.46 | 37.19 | 57.89 | 34.2 | ||||
| 165.78 | 37.15 | 92.63 | 36 | |||||
| 258.28 | 228.08 | 30.2 | ||||||
| 426.13 | 50.66 | 250.26 | 36 | 89.21 | ||||
| 414.69 | 273.79 | 37.19 | 27.96 | 36 | 39.75 | |||
| 260.82 | 188.14 | 26.93 | 36 | 9.75 | ||||
| 261.47 | 31.34 | 85.21 | 36 | 108.92 | ||||
| 317.31 | 206.53 | 69.88 | 36 | 4.9 | ||||
| 0 | ||||||||
| 248.94 | 83.29 | 27.4 | 102.25 | 36 | ||||
| 261.11 | 186.12 | 38.99 | 36 | |||||
| 155.23 | 80 | 75.23 | ||||||
| 221.61 | 148.43 | 35.18 | 38 | |||||
| 121.86 | 85.86 | 36 | ||||||
| 288.29 | 193.3 | 38.99 | 36 | 20 | ||||
| 273.58 | 177.45 | 38.83 | 36 | 21.3 | ||||
| 256.6 | 119.56 | 65.04 | 72 | |||||
| 5154.19 | 2812.15 | 443.87 | 250.26 | 409.56 | 681 | 352.55 | 204.8 |
Therefore, learn to categorize expenses in columns and do the same for your income so you can get the totals to work out your profit at the end of the month. This type of accounting using Excel spreadsheets is good for business that sells goods in cash. Accommodation first column, your total should tally with the sum of all the items along the last line of this spreadsheet.
It appears simple and easy to do, but it is not ideal if your business grows as it takes too much time, might miss accuracy, and will not be easy to produce financial statements. That leads to problems when you do the calculations for your tax liability.
Accrual basis accounting
Accrual Basis Accounting in Excel
When you sell goods on credit, you need to use the accrual basis accounting method also prepare different accounts.
The accounting equation is the guideline for all transactions.
Assets = Liabilities + Shareholders’ Equity
Here you can see the total of the assets is equal to the sum of all liabilities and equity accounts; when your asset increases, your weakness and equity also increase.
So, prepare this Chart of accounts for different categories
- Asset
- Liability
- Equity
- Revenue
- Expense accounts.
The Asset has all these accounts
- Cash
- Accounts receivable – The payments receivable from your debtors.
- Inventory – The stocks you use in your business
- Fixed assets – Machinery, Furniture, and more
- Other assets
Liability has all these accounts
- Accounts payable – Cash payable to your suppliers
- Salaries to employees
- Administration expenses.
- Other expenses
It would help if you need a workbook, so create your chart of accounts on the first sheet of your workbook.
Chart of Accounts
| Number | Account Title | How to increase | Type |
| 101 | Cash | Debit | Asset |
| 102 | Accounts receivable | Debit | Asset |
| 103 | Accounts payable | Credit | Liability |
| 104 | Advertising Expenses | Debit | Expense |
| 104 | Wages to staff | Debit | Expense |
I will show you an example of how to create accounts, say for cash, and then create two columns, one for debit and the other for credit. When you record a debit, then another account is credited.
Cash
| Date | Details | Debit | Credit | Balance |
| $ | $ | $ | ||
| April 21 | Balance B/f | 1000 | ||
| Cash received | 100 | 100 | ||
| Total | 1100 |
Inventory
| Date | Details | Debit | Credit | Balance |
| $ | $ | $ | ||
| April 21 | Balance B/f | 9000 | ||
| Goods sold | 100 | 100 | ||
| Total | 8900 |
I hope you understand the concept; if you make one, you must complete the corresponding entry to another account.
In this way, you have to record all transactions using the double-entry system to the accounts you had your transactions related to a particular account.
When you buy a piece of equipment on credit, you will debit the asset account and credit the accounts payable account (liability), which increases liability and the asset account.
Accounts payable account is in credit, and when you pay your supplier, you debit that account, decreasing that account.
In the same way, accounts receivable is a debit account, and when you receive cash from the customer, you will credit that 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.
Inventory
| Date | Details | Debit | Credit | Balance |
| $ | $ | $ | ||
| April 21 | Balance B/f | 9000 | ||
| Goods sold | 100 | 100 | ||
| Total | 8900 |
Peter ( Accounts Receivable)
| Date | Details | Debit | Credit | Balance |
| $ | $ | $ | ||
| April 21 | Goods sold | 100 | 100 |
It shows Peter owes you $100 because he bought some inventory from your business.
Equity
Generally, small businesses do not have shareholders, so the equity will not be there, but it is the owner’s equity. Retained earnings are in the profit equity after deducting the dividends payments if the business has stockholders. The contained earnings are saved or reinvested in the industry.
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.