Small businesses have more options than ever for bookkeeping, from leather-bound journals to sophisticated accounting software and plenty in between. For many companies, one of those “in-between” options may be the perfect fit: spreadsheets. These easy-to-use software programs are more powerful than they may seem. Advancements in spreadsheets such as Microsoft Excel, Google Sheets, and Apple Numbers – plus the many available accounting templates – have made them versatile tools for small businesses.
Compared to pen and paper, digital accounting spreadsheets can ensure more accurate records, quicker turnarounds on payables, and a low-stress tax season. When used well, these tools can do much of the time-consuming and error-prone number crunching that makes small-business accounting a challenge – allowing entrepreneurs to spend less time hunched over calculators and more time on growing the business.
The first thing many spreadsheet accountants do is set up a chart of accounts (COA), which serves as a comprehensive framework of categories (accounts) through which accountants can group together records of similar transactions. The main COA categories are revenue, expenses, assets, liabilities, and equity. A small business should consider listing as many subcategories in each of those main categories as needed to reflect the nature of its operations.
With a well-thought-out COA, spreadsheets can improve accounting throughout an organization, but there are three main small-business spreadsheet use cases:
- Accounts payable.
- Payroll.
- Cash flow.
1. How to Use Spreadsheets to Track Accounts Payable
When a business commonly purchases goods and services on credit, it’s crucial to have a working accounts payable process to control when creditors are paid, prevent missed payments and late penalties, and optimize cash flow. Accounting spreadsheets can make accounts payables easier to track. With built-in calculation features, for instance, spreadsheets can automatically recalculate outstanding balances in an instant. You can even link data between different sheets (in other words, different “tabs” within a larger workbook) so that updated financial data is reflected in all relevant locations without manual reentry.
Tips for Using Accounts Payable Spreadsheets
When you're using accounts payable spreadsheets, consider:
- Picking a period to cover. Some businesses use a different spreadsheet for each month, others every quarter or year. Ensuring each spreadsheet is “timebound” in this way can make relevant financial data more easily accessible. It can also be wise to maintain consistent formatting across all sheets for easier comparisons.
- Planning the spreadsheets’ scope. Some spreadsheets may serve as high-level overviews, while others drill down into more detailed data, such as outstanding payments, new invoices, and fully paid bills. As businesses grow, a single spreadsheet can likely become too complex to efficiently track everything. Effective compartmentalization can help you quickly find the information you need to ensure no payments are forgotten.
- Automating with formulas. Many accounting formulas may only require simple addition and subtraction between cells, such as “=B4+F4-E4” (spreadsheets use “=” to denote that a cell contains a formula rather than text or a numerical value). Using formulas for even the simplest calculations can save a lot of time and reduces errors. Try combining formulas with other automation features, such as flagging cells that indicate late payments.
2. How to Use Spreadsheets to Track Payroll
Payroll can often use repetitive and predictable calculations, making it a perfect process to streamline with spreadsheet accounting. Payroll spreadsheet templates, for example, tend to come with pre-set formulas for tax deductions, 401(k) contributions, overtime pay, year-to-date totals, and more. Templates with preset formulas can reduce payroll to a simple process that automatically adjusts gross pay in an easy-to-understand way for both employers and employees – especially if the spreadsheet software makes it possible to print individual paystubs. And some businesses send spreadsheets themselves, via email, as their employees’ pay stubs.
Tips for Using Payroll Spreadsheets
- Try to understand the process. Payroll can be a burdensome process because there is no room for error. Try to know exactly what you need to track. Are you looking to simply track gross pay, or do you also want to account for payroll taxes and other deductions?
- Consider the rules. In addition to employee status and pay scale, reporting requirements can vary by jurisdiction. States and localities have different rules for what must be tracked and for how long records must be kept. Carefully adhering to payroll laws can help keep you out of trouble with the Department of Labor (DOL), as well as federal and state tax agencies. And remember, there are different payroll rules for contractors whose compensation is reported to the IRS on a 1099 than for employees with W-2s.
- Try not to forget other benefits and factors. Accounting spreadsheets can also be used to track benefit costs and other contributions, such as health insurance. If you plan to track this data in spreadsheets, try to correctly set up the formulas.
- Usually, a small business’s tax accountant can provide the information you need to set up payroll spreadsheet structures and formulas.
3. How to Use Spreadsheets for Cash Flow
Spreadsheets can be a useful tool to track cash flow. For instance, they can simplify the recording of transactions, making it easier to track money flowing into and out of a business. Spreadsheets can track income from multiple sources, including sales, investments, and financing, and then validate the data to ensure consistency and accuracy. And when cash is transferred between accounts, they can adjust balances automatically. Furthermore, spreadsheets can quickly generate customizable charts and graphs, offering an quickly digestible visual presentation of numerical data.
Additionally, spreadsheets can be customized to help project future budgets and cash-flow needs. Say a company allocates 2% of its revenue for a year-end bonus to employees. Spreadsheets can automatically account for this 2% expense with each revenue entry, maintaining a running tally of the “bonus fund” and adjusting usable cash reserves throughout the year to reflect said fund.
Tips for Using Cash-Flow Spreadsheets
- Try to compartmentalize cash flows. For companies that have many different sources of incoming and outgoing cash, such as financing and investment options, it may be helpful to have individual spreadsheets for each account/cash source and one overview document that links them all together.
- Consider tracking both sides of the exchange. Every time cash changes accounts, try to properly apply that debit or credit to both its source and destination accounts. Spreadsheets can use formulas to automatically update the appropriate balances when changes are made.
Downsides to Spreadsheets
Spreadsheet accounting may be simpler, faster, and more powerful than pen-and-paper processes, but it’s not perfect. As a business grows, it will likely need a more robust and scalable solution to keep up with the increasingly complex accounting needs that stem from higher transaction volumes, more employees, adding benefits, or complying with regulations.
More sophisticated systems, such as dedicated accounting software or an enterprise resource planning (ERP) system can integrate a business’s financial systems to create one streamlined platform that does more than just accounting. Many of these systems can generate financial statements and insightful analyses faster, more accurately, and in more detail than spreadsheets.
But these systems can often require a much larger investment – both in dollars and in time to integrate or upgrade existing systems and bring staff up to speed. Small-businesses spreadsheets can be more than capable of handling all their accounting needs. But if late payments, errors, or accounting bottlenecks are becoming commonplace, especially when it’s time to close the books, it may be time to consider an upgrade.
The Bottom Line
For small businesses, spreadsheets can provide valuable and user-friendly accounting tools that make it easier to track financials without drowning in calculations. By using built-in formulas, tools, templates, and visualizations, businesses can build a system of accounting spreadsheets that helps them better manage accounts payable and payroll, and monitor cash flow – at least until finances grow large and complex enough to warrant an upgrade to more sophisticated accounting software.
A version of this article was originally published on March 12, 2020.
Photo: Getty Images
