Using Spreadsheets for Small Business Accounting

It’s 2020, yet many small businesses still use antiquated methods for bookkeeping. The digital tools available today are more powerful and varied than ever, and advancements in platforms like Microsoft Excel, Google Sheets and Apple Numbers have made the use of accounting spreadsheets a must.

Digital accounting spreadsheets can ensure more accurate records, quicker turnarounds on payables, and a stress-free tax season. When used well, tools like Excel accounting spreadsheets can do all of the number crunching for you—ensuring that your time is spent on business needs like sales and marketing.

How to Use Spreadsheets to Track Accounts Payable

When credit-based purchases and services are the norm for your business, it’s crucial to have a working accounts payable sheet. What makes a platform like Excel so valuable for this purpose is its built-in arithmetic functions: All you need to do is enter the correct numbers and let your accounting spreadsheet do the rest.

While different businesses will want different styles of accounts payable spreadsheets, this invoice tracker template is a good example of how even the most simple setup can save you time. This year-to-year balance sheet template shows how you can use Excel accounting spreadsheets to track how the revenue from your accounts payable changes over time.

Tips for Using Accounts Payable Spreadsheets

● Ensure that your Excel accounting spreadsheets have time bounds. Some businesses use a different spreadsheet for each new month, some operate by quarter and others by year.

● Decide whether you want to track all transactions, just outstanding payments, or both. Use a different Excel spreadsheet for each.

● Use formulas to automate accounting spreadsheet calculations. Most formulas involve simple addition and subtraction, such as “=B4+C4+F4-D4-E4”

How to Use Spreadsheets to Track Payroll

Payroll was made to be tracked using Excel accounting. Much like accounts payable, spreadsheet-based payroll calculators often use simple functions to save significant headaches.

If you use an accountant or online payroll service, a simple spreadsheet that keeps track of basic salary, hours worked, and so on should be plenty. Businesses that handle more accounting responsibilities in-house might want to augment their accounting spreadsheets with tools like payroll calculators, which can track deductions and even print individualized paystubs.

Tips for Using Payroll Spreadsheets

● Know from the start what you want to track. Are you just looking at pay, or are you hoping to stay atop FICA and other withholding taxes as well?

● Excel accounting spreadsheets can also be used to track benefits costs. Take the total cost of compensation into account when calculating payroll expenses.

● Understand how reporting requirements vary by jurisdiction. States and localities have different rules for what must be tracked and for how long records must be kept.

How to Use Spreadsheets for Cash Flow

Spreadsheets are incredibly helpful for tracking cash flow, particularly in terms of accrual accounting and cash basis accounting. Using a spreadsheet for accrual accounting (when a company “counts” revenue or expenses when a transaction is made, rather than when a payment is made) enables you to track transactions in this way so you can follow the matching principle as payments are made. 

Cash basis accounting is the opposite approach, recording revenue and expenses when they occur. The key is deciding whether you want to mark when money changes hands or when work is completed. Spreadsheets can be helpful, regardless of which route you go.

When credit-based purchases and services are the norm for your business, it’s crucial to have a working accounts payable sheet.

Tips for Using Cash-Flow Spreadsheets

• If you’re using an accrual approach, make a chart of accounts. The categories you’ll want to account for are assets, liabilities, equities, revenues and expense accounts.
• When using accrual accounting, ensure that every time a debt is recorded, another account is credited. Make an account type chart to reference discern when debiting or crediting an account.
• Cash basis is more common among businesses. To manage it well via a spreadsheet, set up your categories like a checkbook. Different sheets for each month of the year makes tracking easier.

How to Use Spreadsheets for Taxes

Tax season is always stressful, but an Excel accounting spreadsheet can be a big help. As with payroll and accounts payable spreadsheets, tax-specific Excel accounting templates can do much of the work for you.

This tax expense template can make calculating a running total of your expenses a breeze, while this IRS audit info tracker allows you to itemize any requested documents during a tax audit.

Tips for Using Tax Spreadsheets

● As with payroll, the law might dictate exactly what you can and can’t do from an accounting spreadsheet regarding your taxes. Take stock of your restrictions before you jump into Excel accounting.

● If you think your calculating formulas might have errors, use the “Trace Precedents” feature contained under the “Formulas” tab. This allows you to see the cells being used by a given formula and makes error-tracking much easier.

Using Excel accounting or another accounting spreadsheet tool is a great way to stay atop your numbers without drowning in them. With these tools and templates, you can build an accounting spreadsheet system that works for your business—and take money matters into your own hands.

Photo: Getty Images