How to Use Excel for Accounting
Spreadsheet programs such as Microsoft Excel can be customized to suit your needs. Templates can be created and saved for every day accounting tasks. Uses for spreadsheets include bank reconciliations, receipts, disbursements, receivables, payables, and financial statements. Excel has both financial formulas and statistical formulas that allow for a wide variety of analysis.
Things You'll Need
- Microsoft Excel program
Create spreadsheets to record cash receipts, cash disbursements, and bank reconciliations. These will be the most commonly used spreadsheets. Include dates, who the payment was received from or paid to, the dollar amount, the appropriate classification for tax purposes, and a description column. The description column can be used to include internal notes about the entry. Also include formulas to calculate the totals of each spreadsheet, where applicable.
Create spreadsheets for receivables and payables. When sales are made but payment not yet received, record the amount of the sale, the date of the sale and who the sale was made to in the accounts receivable spreadsheet. When bills are received but not yet paid for by the business, record the amount of the bill, the date the bill was received and who payment needs to be made to. Customize these spreadsheets as needed for your business to include descriptions of sales or notes regarding customers.
Create financial statements in Excel using individual spreadsheets for the balance sheet, the profit and loss statement and statement of cash flow. These spreadsheets can be linked to one another as well as other accounting spreadsheets you have set up. For example, the cash on hand reported in the balance sheet can be linked to the most recent bank reconciliation. The accounts receivable and accounts payable can be linked to those spreadsheets.
Analyze the spreadsheets using the formulas in Excel. Useful accounting formulas include sum, average and median. You can use the sort and subtotal functions to analyze data within spreadsheets. For example, you can sort the accounts receivable spreadsheet and subtotal the receivables by customer. Using these Excel functions will enable you to quickly see ways to improve the business’s bottom line.
Tips & Warnings
- Creating templates in Excel for schedules not needed in the normal course of your business is not time wasted. Rather, instead of creating schedules from scratch, the templates will allow more time to be spent on analysis of the data instead of data entry.