Masum Mahdy
This article will show how to do bookkeeping for a small business in Excel.
The daily management of your books—ensuring that you’re tracking your income and expenses, reconciling transactions, and accurately inputting everything—will be one of the most crucial responsibilities when it comes to your business finances. In terms of business accounting, bookkeeping is the fundamental procedure that maintains you in order, current, and prepared to handle any additional financial responsibilities that may develop.
Although some business owners opt to use bookkeeping software or hire a professional bookkeeper, if your company is particularly tiny or new, you can decide to construct a bookkeeping system using a tool you already have at your disposal: Microsoft Excel.
Download Practice Workbook
You can download the following practice workbook to practice while reading this article.
Steps to Do Bookkeeping for Small Business in Excel
1st Step: Decide Which Accounting Method You Will Use
- Businesses primarily employ two accounting techniques: Accrual Accounting and Cash Accounting.
- The Cash Accounting method requires you to record income and outlays as they are paid or received.
- Revenues and costs are tracked using accrual accounting when they are earned or incurred.
- In our case, we have chosen the Cash Accounting method.
2nd Step: Choose a Suitable Entry System
While choosing the Entry System, you need to be careful. Because it affects the way you operate your economic affairs and your bookkeeping procedure.
- There are 2 Entry systems you can choose from- Single and Double entry systems.
- In a Single Entry system, you will record all your transactions only once as an expense or an income. This approach is simple and appropriate for smaller companies without substantial inventories or capital equipment investments.
- To “balance the books” between accounts, the Double Entry system records each transaction twice, once as a debit and once as a credit. It can avoid mistakes in transaction recording despite being more challenging.
- For a small, straightforward business with few transactions, single-entry bookkeeping is an excellent option.
Read More: How to Make Trial Balance in Excel (with Easy Steps)
3rd Step: Create a Chart of Accounts
- A chart of accounts is the very first spreadsheet you need to build. The accounts that are set up in your bookkeeping system are listed here.
- A chart of accounts makes it possible to keep track of all financial operations.
- As you enter transactions, they are classified into one of the categories represented by each account mentioned on this sheet.
- The accounts in a template are typically already stated, but you will probably need to make some adjustments to make it work for your business.
- With each account, you should identify the kind (revenue, expense, or cost of items sold) and a number.
- You can also include a succinct description that will be useful once you begin recording.
- Create a Helping Sheet and create a list of types of accounts. The account types here are Income, Cost of Sales, and Expense.
- Create a separate sheet and make a detailed list of different types of transaction items. This list will be called the Chart of Accounts.
- Create 2 more columns named: Type and Explanation. We will assign the types of accounts in a drop-down list from the helping sheet and give a brief explanation of the items in the 3rd column.
- To create a drop-down list, select a cell in the Type column and follow the steps below:
Data tab ⇒ Data Tools group ⇒ Data Validation drop-down ⇒ Data Validation button
- From the Data Validation window, go to the Settings section, and then select List in the Allow box.
- Then select the source data. Here the source data is:
='Helping Sheet'!$B$3:$B$5- Then press OK.
- As a result, a drop-down list will be created like the following image.
- Now, just drag the fill handle icon down to copy this drop-down list in the cells below.
- Now, choose a suitable account type for each of the categories, and add your own explanation. This may look like the following image.
Note that these categories are not fixed, modify this column according to your business types and other things suitable for you.
Read More: How to Fix Formula in Excel (9 Easy Methods)
Similar Readings
- [Fixed!] Excel Links Not Working Unless Source Workbook Is Open
- How to Fix Formula in Excel (9 Easy Methods)
- Move Data from Row to Column in Excel (4 Easy Ways)
- How to Insert WordArt in Excel (2 Simple Methods)
- Count Words in Excel (6 Easy Methods)
4th Step: Create a Transaction Sheet
- Now, we will create a new sheet, and name it Transaction sheet.
- In this sheet, we will list all the transactions we have performed in a certain time span.
- For example, We have performed the following transactions from Dec 20, 2022, to Jan 4, 2023.
- Record them well and create 3 more columns: Category, Type, and Amount.
- In a similar fashion as in Step 3, create another drop-down list for the category column. This time, the source data will be:
='Chart of Accounts'!$B$5:$B$28
- Now, go to cell E5 of the Type column and enter the following formula:
=IFNA(VLOOKUP(D5,'Chart of Accounts'!$B$5:$C$30,2,FALSE),"")This formula will automatically assign the account type when you select a Category from the drop-down list.
- After selecting all the Categories and Types, the sheet will look as follows:
- Now, record the amount of the transactions in the Accounting format of Excel.
5th Step: Create a Revenue Sheet
- Now, create another sheet, and name it a Revenue sheet.
- In this sheet, set a date range in cell E5 and E6.
- Then write the following formula in cell B9.
=FILTER(Transactions!B5:F20,(Transactions!B5:B20>=Revenue!E5)*(Transactions!B5:B20<=Revenue!E6)*(Transactions!E5:E20='Helping Sheet'!B3))
- This formula will return all the data (Description, Category, Type, and Amount in this date range) from the Transactions sheet.
- Now, create another column for Total Income, and enter the following formula in cell H9.
=SUM(F:F)
Read More: How to Calculate Cost of Funds in Excel (with Easy Steps)
6th Step: Create Cost of Sales and Expense Sheets
- In a similar manner, create two more sheets: Cost of Sales and Expense sheets.
- For the Cost of Sales sheet, the formula will be:
=FILTER(Transactions!B5:F20,(Transactions!B5:B20>=Revenue!E5)*(Transactions!B5:B20<=Revenue!E6)*(Transactions!E5:E20='Helping Sheet'!B4))
- And for the Expense sheet, the formula will be:
=FILTER(Transactions!B5:F20,(Transactions!B5:B20>=Revenue!E5)*(Transactions!B5:B20<=Revenue!E6)*(Transactions!E5:E20='Helping Sheet'!B5))
- Just like the 5th step, add them up using the SUM function.
Read More: How to Create Money Management Excel Sheet for Trading
Last Step: Create a Sheet to Calculate Profit or Loss
- Now, add one more sheet to calculate Profit or Loss.
- The Gross Profit or Loss = Revenue–Cost of Sales
Excel Formula:
=Revenue!H9-'Cost of Sales'!H9- The Net Profit or Loss = Gross Profit or Loss–Expense
Excel Formula:
='Profit or Loss'!C4-Expense!H10
Read More: How to Mark Workbook as Final in Excel (with Easy Steps)
Conclusion
Hopefully, this article will help you in doing bookkeeping work for a small business using Microsoft Excel. If you have any further queries, leave us a comment. For more such articles, visit our blog ExcelDemy.
Related Articles
- Create Double Entry Bookkeeping in Excel
- How to Overline Text in Excel (4 Easy Ways)
- Do Bookkeeping for Small Business in Excel (Step-by-Step)
- How to Write a Paragraph in Excel Cell (5 Easy Ways)
- Add Signature in Excel (3 Quick Ways)
- Use of Task Pane in Excel (Detailed Analysis)
- How to Show All Text in an Excel Cell (2 Easy Ways)
Thank you for the information it is useful but hope to continue to learn from you more
Hi, Bosco!
Thanks for your appreciation. To get more informative contents stay in touch with ExcelDemy.
Regards
ExcelDemy