Spreadsheets point to automation opportunities

Spreadsheets have long been a staple component of the accountant’s automation toolkit; the ultimate utility application filling in for virtually every unique information need not being met by one of our existing business programs. Firm owners, partners and administrative personnel create custom spreadsheets for a variety of reporting needs such as due date tracking, summarizing collections, PTO reporting, staff schedules, tracking CPE, etc. The difficulty with relying on spreadsheets is the data is often manually keyed from other sources, which creates a need to compare to the original source totals and to correct any input errors. Also, if the spreadsheet is not updated timely, the information gets out of synch, creating frustration and time loss reconciling the information to get back into balance. When focusing on firm automation optimization, these spreadsheets are one of the first places to look to see if the firm can capture data natively within an existing program or transition to another application that is better suited to capture data. Below are five places where spreadsheets can be replaced with automated tools.

• Internal reports: Most firms create a series of spreadsheets highlighting financial metrics or Key Performance Indicators (KPIs) which they utilize to manage their practice and sometimes for income allocations. One of the questions firm managers should ask annually is how each report is being utilized, by whom, and if it is still necessary. In many cases, traditional monthly reports continue to be created even though the original person requesting them is long gone, or the purpose of that report has been superseded by other applications. For critical KPIs, firms should identify the root source of the report and explore if it can be automated into a dashboard to provide real-time data rather than being processed after month end. Report automation using tools such as Microsoft Power Business Intelligence (BI), Tableau, and Domo can replace spreadsheets by automatically generating the information. Today’s accounting programs also have enhanced reporting capabilities and third-party reporting tools (Qvinci, Girav, etc.) which can be utilized.
  

• PTO reporting: Tracking vacation, sick and comp time has long been a frustration in firms as there are variable and fixed plans depending on longevity, staff level, and benefits. While more and more firms are going to fixed, or even unlimited PTO, there are still those that continue to reconcile PTO on a spreadsheet. Today’s payroll and practice management applications can calculate PTO inside the program allowing the firm manager to review the information in the native application and firm personnel can lookup their PTO status on their own.

• CPE tracking: Continuing professional education (CPE) comes from a variety of sources including AICPA, State Societies, vendor training, and internal training and needs to be accurately tracked for peer review reporting and to ensure that licensed professionals meet their requirements. Many firms stored this information on spreadsheets because they were easy to use and manipulate for different reporting cycles and unique needs (Yellow Book for auditors). However, today’s practice management applications can track necessary detail if appropriately setup and monitored with digital certificates stored in document management by each professional. There are also applications such as Thomson Learning, CCH CPE Link, Micron CE and Prolaera designed specifically to track multiple certifications, states, and unique requirements including storage of the certificates.

• Tax tracking: When inquiring about spreadsheets utilized with tax production, we often identify administrative personnel that use them to record receipt of source documents from clients, to track due dates and deadlines for items they are personally responsible for, as receipt of signed engagement letters and even as tax lead sheets tracking documents requested from the client. In each of these instances, today’s dedicated workflow tools (CCH XCM and Workstream, Thomson FirmFlow, Doc-It workflow, OfficeTools, etc.) can capture and track this type of data within the application to eliminate the spreadsheet and provide access to all authorized users. Some of the practice management applications also have “project” capabilities to do the same but typically firms are more successful with utilizing dedicated workflow tools.

• Assurance spreadsheets: Within audit practices, spreadsheets are the most dominant tool utilized for creating schedules particularly for firms with less than 50 personnel on the designated schedule. Larger firms tend to utilize dedicated tools (ProStaff, StaffTrak, Retain) which require an administrator to manage, and firms with less than five personnel on the schedule tend to utilize a calendar (Outlook). While the middle group has had some success with utilizing practice projects, the majority are still using spreadsheets with one notable exception. Firms utilizing CCH’s XCM product are finding that their scheduling add-on can be configured to provide both a calendar and spreadsheet view from within the application, thereby eliminating the need for a scheduling spreadsheet. Firms should inquire if their workflow or practice management tool also has enhanced their scheduling capability as new features are constantly being added.

In areas where there is not a more effective application to replace a spreadsheet, firm managers should inquire if data can be exported and automatically manipulated with tools such as Application Program Interfaces (APIs), Robotic Process Automation (RPA), Machine Learning (ML) or Artificial Intelligence (AI) which are all becoming integral components of firm technology stacks that are helping replace spreadsheets.