How to Use Excel for Bookkeeping
Microsoft Excel is a program that allows the user to create custom spreadsheets. That the design and use of the spreadsheets can be tailored to the user's needs makes it a useful program to keep track of simple bookkeeping tasks. For example, the user can record cash receipts in one spreadsheet, cash disbursements in another and a summary of receipts and disbursements in a third. Monthly bank reconciliations, a must for any business, can also be formatted. The financial and statistical functions of Excel allow analysis to be conducted within the spreadsheet.
Other People Are Reading
Instructions
-
-
1
Set up cash receipts and cash disbursements spreadsheets. Necessary information will include the date, received from or paid to, amount of the transaction, a description of the services received or paid for and a tax classification. The spreadsheet should include totals for the amounts received and collected.
-
2
Create a spreadsheet for monthly bank reconciliations. When the format is customized to fit your business' needs, data entry for the bank reconciliation will take minimal time. Bank reconciliations may be required by the business owner and accountant to verify the cash on hand at a specific date other than the month's end. Creating a bank reconciliation spreadsheet will allow the bookkeeper to quickly perform bank reconciliations at any date requested.
-
-
3
Analyze the information you compiled in your spreadsheets. Using the sort function, arrange the data by date, payee, description or tax classification. The tax classification will be especially useful when it comes time to prepare income, payroll or sales tax returns. Use the formulas in Excel to calculate the average, median, minimum and maximum for amounts paid or received. These calculations can make your business more profitable. You can trim expenses in categories where you are spending more than you need or increase sales if you see a trend.
-
4
Create a template for financial statements, ie: balance sheet, proft and loss statement and statement of cash flow. If your company does not routinely require financial statements, setting up a template is still a good idea. It will be make sure you're ready to prepare financial statements when the need arises.
-
5
Search the Internet to find bookkeeping templates that are already created. See the Resources section for a link to cash receipts, accounts receivable, accounts payable, profit and loss statement and bank reconciliation spreadsheets.
-
1
Tips & Warnings
While Excel is useful for a small or start-up company, a bookkeeping program should be used when Excel cannot provide all the reporting functions your business requires. Use the import function in Quicken to import transactions from Excel so you do not lose original data.
