How to Use Excel for Small Business Accounting

Microsoft Excel can make simple accounting tasks more accessible. Explore the features and benefits of using Excel for small business accounting.

Accountants are known for wearing pocket protectors and spending their days working in Microsoft Excel. I can vouch for the latter stereotype.

Many small businesses swear by Excel for all of their bookkeeping needs. While accounting software has its place in every industry, you can benefit from introducing Excel into your life.

Three benefits of using Excel for accounting

Excel is a powerful tool that can help anyone working with numbers.

1. Streamlined data entry

I’m not taking anything away from the ease of use that’s become standard in small business accounting software, but you’d be hard-pressed to find a better-suited program to mass data entry than Excel.

Excel power users know that macros can churn out hours of a human’s work in moments. Business owners can create macros to automate tasks, such as formatting, filtering, and running fundamental analysis.

You don’t need to know how to set up macros to benefit from Excel’s functionality. As we’ll explore below, Excel remains one of the best ways to import large transaction datasets into your accounting software.

2. Visual financial analysis

The financial analysis doesn’t have to be a list of accounting ratios that assess your company’s profitability. If you’re a visual learner, use Excel to create pie charts that show where your business’s money is going.

Nonprofit annual reports often use visual aids to communicate where they spend their money. For-profit companies might prepare similar reports when pitching investors.

3. Easy sharing

Excel is the universal currency of accounting. When you need to share financial data with a lender or investor, you’ll want to send them either a PDF or Excel file.

Three most valuable features for Excel small business accounting

I could go on for eons about the Excel features that small businesses can leverage for accounting. Here are my three favourites.

i. Templates

Microsoft has thousands of free Excel templates on its website. Rather than having to design your own invoices and expense reports from scratch, you can download and tweak someone else’s creation.

Templates are handy for those who aren’t comfortable navigating Excel or haven’t yet aced Accounting 101. Designing your own accounting templates requires 100% precision; if one cell isn’t working correctly, you might wind up with errors in your accounting records. Templates are plug-and-play with pre-filled formulas.

ii. Formulas

Excel formulas significantly speed up business expense tracking and analysis.

Say you’re looking at the bank statement below and want to know how much the business spent on inventory purchases. Rather than adding up a list of the applicable expenses using a calculator, you can use the “SUMIF” formula, instructing it only to add the costs marked as inventory purchases.

Using Excel formulas can increase your productivity many times over. Source: Microsoft Excel software.

You can find the substantial library of Excel functions by clicking on the formula button at the top of any Excel page. It will bring up a side panel where you can search and learn how to use the function.

Click on the formula button at the top of any Excel window to learn how to use functions. Source: Microsoft Excel software.

iii) PivotTables

I’ve saved the best for last. PivotTables, the most powerful Excel feature, reorganizes data to present it more logically. Accountants use the tool to process bank statements and other financial data.

For example, bank statements typically list transactions chronologically, which doesn’t help you understand where your money went that month.

I made a Pivot Table below to categorize income and expenses on the adjacent bank statement. It collapsed the order and inventory transactions into one line to delineate how much the company earned in revenue and spent on inventory purchases. The total row reflects the company’s net income for the month.

PivotTables organizes data more logically. Source: Microsoft Excel software.

The example shows just a month’s worth of transactions, but imagine your spreadsheet lists of transactions for an entire year. You could use a PivotTable to separate your transactions by month to understand your company’s cash flow better. That would take hours by hand, but it’s only moments with PivotTables.

How to use Excel for your small business accounting needs

Excel can help you with countless aspects of your small business accounting. Here’s how I’ve used Excel as an accountant.

Budgeting

Although many accounting software platforms have budgeting features, I’d choose Excel every time.

Start your business budget by exporting an income statement from your accounting software to Excel. Clear out the numbers and add columns for each month of the period for which you’re budgeting. Then start entering your budgeted numbers, and voila! You have a budget.

As your business’s finances get more involved, you can create granular sub-budgets that roll up into your business’s master budget. Excel can handle it.

Invoices

If your accounting software doesn’t have an invoicing feature, you can easily create professional bills through Excel.

Microsoft Word also has invoice templates, but I wouldn’t recommend using them because Word templates can’t do any arithmetic for you. Excel invoice templates automatically sum invoice line items, reducing the risk of human error. You wouldn’t want to be responsible for underbilling a client because of an arithmetic mistake.

Expense reports

Every time you reimburse an employee for mileage or another business expense, you should ask for receipts and an expense report that tallies all costs in one document. There are hundreds of expense report templates to choose from online.

Accounts receivable and accounts payable

Many small businesses track who owes them and whom they owe with an Excel spreadsheet.

Businesses on the cash accounting method only record revenue and expenses when money comes in or out. Excel is the perfect repository for a list of outstanding client invoices and your business’s unpaid bills since they have no place in your accounting records until cash changes hands.

Accrual-basis businesses record receivables and payables in their accounting software when they earn revenue and incur expenses, respectively. They shouldn’t need to track their receivables and payables anywhere else.

Bank reconciliations

Bank reconciliations, one of the bookkeeping basics, compare your company’s accounting records with its bank statements. You can put both sides by the side in Excel and quickly scrutinize any discrepancies.

Many accounting software solutions have bank reconciliation tools, but I prefer to use Excel to perform monthly reconciliations because of its no-frills interface. Start the bank reconciliation by downloading Excel versions of your company’s general ledger and bank statement. Then, copy and paste the data into one spreadsheet and start reconciling.

Three best practices when using Excel accounting software

Avoid creating more work for yourself by following these best practices.

i. Let formulas do the work.

The best advice I’ve received about using Excel for accounting is to avoid “hardcoding” numbers whenever possible.

Imagine you’re building your company’s budget for next year. You plan to pay all employees the state minimum wage of $12 per hour. You later learn that the minimum wage is going up to $15 next year. If you created the budget using formulas the first time, this fix should take no more than 30 seconds.

Hardcoding a number means entering an actual number, not a formula, into a cell. The only numbers I hardcoded below are the $12 minimum wage and the monthly hours for each employee. Formulas calculate the employees’ monthly gross pay.

Avoid “hardcoding” numbers so you won’t create more work for yourself later. Source: Microsoft Excel software.

Bridget’s monthly payment is calculated by multiplying her monthly hours by the state minimum wage. Had I individually bore 12 and 30 in every cell, I would have to redo the entire payroll budget. Thanks to the formula, I can change the 12 to a 15, and all the monthly payroll amounts will automatically update.

A bonus tip: The dollar signs in the formula “lock” the row and column you’re referencing, so if you copy and paste the formula from one cell to another, it will continue to reference the same spot instead of adjusting for the new cell’s location.

ii. Save Excel accounting files securely.

Anywhere you store financial information should be secured by a password that only a privileged few know—password-protect Excel files with sensitive information, such as employee Social Security numbers.

Don’t forget to back up files that aren’t stored in a cloud storage system.

iii. Create template files

Save your invoice and expense report templates as template files. When you open a template file, it creates a new document that won’t overwrite the original template.

After you download and tweak your expense report template, click “Save As Template” and save it to an accessible place for you and your employees. When someone clicks on the template file, you can be sure they won’t inadvertently ruin all your hard work in creating it.

Excel with Excel

Excel can be intimidating at first, but the feeling fades quickly. It’s like meeting your in-laws for the first time (hopefully).

Start exploring Excel’s capabilities by downloading an expense report or invoice template and tweaking it to fit your business. Soon enough, you’ll gain the know-how to implement it more widely in your company.

Source: www.fool.com