Accounting can be incredibly rewarding. Really, it can. When done well – which is to say, when performed efficiently and effectively – accountants get to play a critical role as business partners to company leaders and cross-functional teams, helping produce positive and lucrative outcomes.
But this post is not about any of that. This is about the other side of accounting. The dark side, if you will. The challenges, pains, and frustrations that prevent accounting from being efficient and effective.
How do we know? Because we’ve been there. Among our ranks, we’ve got current and former CPAs, CFOs, and finance operations leaders. So come on in and commiserate with us for a few minutes. Even if just to remind yourself that you're not crazy or alone.
Here are 7 of the most feared accounting nightmares, with commentary from a CPA who has personally lived them.
1. Consolidating transaction data from multiple payment service providers (PSPs) and other data sources
Perhaps the most dreaded month-end close task for accountants is consolidating transaction data from all your disparate data sources, including multiple PSPs. Without the help of technology, this process is manual, tedious, and error-prone, leading to higher audit fees and scream-into-your-pillow frustration.
Let’s tediously unpack some of this tediousness.
- First, you have to know all your PSPs and have access credentials for each one so you can log in and get to your data.
- Then you have to download the transaction data, and it’s not going to be in one tidy, organized report. You may have to download individual reports for sales, refunds, fees, and any other transaction types you need.
- Then you’ve got to aggregate all your transaction data, which means pull it all into your master Excel file or database.
- Oh, right, you need to set up a master Excel file or database. And chances are good that the column headers and labeling from your PSPs won’t all match your master files’, so you get to have fun adjusting the imports for each report. Or better yet, get those fingers ready for manual copy and paste! Ctrl+C, Ctrl+V. Ctrl+C, Ctrl+V.
- Not done yet. Now you get to fix incorrect and mismatched labels, and then remove duplicate entries from your different PSPs.
Cody Leach, CPA shares,
“We have a customer that used to manage over 28 different Excel docs for every month-end close cycle. It would take them 25 days to close. Sometimes more. So they would get behind because it would actually take them longer to close a month than to conduct a month’s worth of business.”
- Cody Leach, CPA
- Director of product experience, Leapfin
2. Excel. Just… Excel.
Is Excel powerful? Yes. Is it still often the bane of your existence? Yes. There are two main reasons why:
- First, the tedious process of fixing formulas. Identifying and correcting errors in formulas can be a painstakingly manual process, especially in large spreadsheets with hundreds or thousands (or more) of cells, like your consolidated master file. And because many formulas include complex logic and/or dependencies, a mistake in one cell can propagate errors throughout the spreadsheet. Plus, fixing one formula reveals other underlying issues that need to be addressed, creating a seemingly endless loop of adjustments. You may actually prefer finding a needle in a haystack. Unfortunately that doesn’t pay as well as accounting. And by the way, the only people who hate this file more than the accountants who live in it every day are the auditors. Files like this are literally not possible to audit. OK, so maybe that’s not technically the right use of “literally,” but files like this are near impossible to audit in a practical sense.
- And second, Excel will crash. Especially when you’re dealing with large data sets. And it’ll crash more than once. You know this. How do you fix it? Well, if you’re relying on Excel, you don’t. That customer we referenced above – the one with 28 Excel files every month – their solution was dedicated spreadsheets for each transaction type. “Disputes 1,” “Disputes 2,” “Disputes 3”... No thank you. The honest answer to the question of how you solve this problem is to adopt a solution that replaces Excel as your source of truth for all your month-end close transaction data.
- One more bonus reason why Excel is a nightmare. It’s single-user focused. Meaning it’s not designed for collaboration, so you can’t easily or seamlessly work on the same file with a teammate to coordinate changes or check work.
A three-scary-clown-headed nightmare. 🤡🤡🤡 Yikes!
One last comment about Excel that’s important to say. Excel isn’t going away, and Leapfin isn’t aiming to put it out of commission. Accounting and finance pros rely on Excel for a variety of purposes. As a tool, Excel is good-to-great at many things, and accountants have become quite skilled with it.
Remember Cody, our Director of Product Experience who we just quoted so eloquently above? His wife is also an accountant. And by our measure, she’s absolutely an Excel power user. She’s currently taking a business modeling course that’s over 80 hours of training. But 30 of those hours alone are dedicated to mastering Excel’s nitty gritty functions to work files faster. So here, for ad-hoc or specialized analysis like modeling – Excel all the way! But when we think about accountants using Excel for large data management for journals? Where’s that barf emoji when you need it? Just kidding, I know exactly where it is… 🤮.
"When I was a public accountant, I would spend 99% of my time working in Excel, and the other 1% thinking about Excel in the bathroom."
- Cody Leach
3. Formatting and standardizing transaction data
Handling transaction data from various PSPs requires advanced Excel skills, especially of functions like VLOOKUP to help format and standardize transaction data. This process is highly error-prone and frustrating. And the risk of errors increases audit fees and prolongs the month-end close process. Here are some common examples of transaction data that needs to be formatted and standardized to ensure consistency, accuracy, and ease of integration into your accounting systems:
- Date formats like MM/DD/YYYY or YYYY-MM-DD. OKKKK?
- And make sure all your date vs text vs currency columns are the right data type so formulas can read them properly.
- Currency formats, including the correct placement of currency symbols and decimal points.
- Consistent transaction naming conventions or descriptions across all PSPs, for example “Refund,” “Chargeback,” and “Sale.”
- Removing unnecessary characters, like special characters, symbols, extra spaces, or irrelevant text.
- Aligning column headers from different PSPs to match your master Excel file or database and, ultimately, your accounting system(s) for easier importing and mapping. For example, renaming “Transaction ID” to “Trans_ID” or “Amount” to “Transaction Amount” and putting them in the order your accounting system prefers. Oh, you’ve got multiple accounting systems? That’s double the fun!
4. Hunting for missing (but necessary) transaction details
As an accountant, when dealing with non-standardized PSPs, you’ll often be at the mercy of what the engineering team allows you to access. This is incredibly limiting and it means you’ll likely be pulling down a variety of different reports and using the ever-reliable VLOOKUP to find the data you need.
But don’t worry, there’s only a couple of types of data that can possibly be missing when you’re scrambling at month-end. Let’s see, yep, here they are. Just a couple…
- Missing transaction IDs, descriptions, or references that link to other records.
- Missing or incorrect transaction dates.
- Missing customer IDs or names that are necessary to reconcile transactions with customer accounts.
- Missing billing addresses, contact details, or other customer-specific information that supports the transaction.
- Missing payment references or identifiers from different PSPs, making it hard to match payments with invoices.
- Missing dates for when payments were received or processed, essential for cash flow and reconciliation purposes.
- Missing invoices, receipts, or other supporting documents that validate the transactions.
- Missing copies of contracts, agreements, or terms of service that outline the conditions of sales or services provided.
- Missing bank statements or specific transaction details within statements that are necessary for reconciliation.
- Missing details on intercompany transactions that need to be reconciled between different entities within the same organization.
- Missing or incorrect tax rates and jurisdictions, which are vital for accurate tax calculation and reporting.
- Missing sales tax information, especially in transactions involving multiple jurisdictions.
- Missing vendor invoices that need to be matched with purchase orders and payments.
- Missing information on payment terms and due dates, which are essential for managing accounts payable.
- Missing information on inventory levels, especially for businesses that sell physical products.
- Missing details on COGS, which are necessary for accurate profit margin calculations.
- Missing expense reports from employees, which need to be included in the period-end expenses.
- Missing receipts that support claimed expenses, required for audit purposes.
- Missing journal entries or details that need to be posted to the general ledger.
- Missing accruals and prepayments that need to be accounted for in the correct reporting period.
- Ad-hoc, moment-in-time snapshot reports generated by engineering that can never be recreated. This is a super fun one!
- Phantom or ghost transactions or business activities for which engineering doesn’t even have a report. More of these, please!
- The random system report. Just try to figure out what this ties out to and how to document it. Impossible. Oh, and good luck getting John-the-23-year-old-engineer-who-was-given-that-lowly-request-by-his-boss to ensure there is a time stamp in the screen print of the query he gave you.
“When you're dealing with accounting from all these different systems, they all have different data availability and they require different levels of detail and complexity. Typically it means you're having to put together another Excel file that you then have to monitor and manage. You'll reconcile it... you hope. So that way you have your completeness checks for your internal controls.”
- Cody Leach
5. Auditor requests
Dealing with auditors can be – you guessed it – a nightmare! It can feel like a never-ending cycle of fulfilling PBC requests. You may often find yourself digging for details only to be asked for different information once you’ve provided what was initially requested.
“It’s a circle. They ask for it, you send it, it’s not what they wanted, so they ask again, you provide... ARGH!”
- Cody Leach
6. Surprise transactions
Another common nightmare for accountants? Discovering new types of transactions that haven't been accounted for properly. And when will you discover this? Right at the close of the month. Without fail. Now you’ve got to track down a salesperson to figure out what the hell they sold. But again, don’t worry, because at the end of the month, sales people aren’t usually that busy.
“Accounting is often the last to know about new things in the business. So you’ll get some sort of weird transaction and have to hunt down the salesperson to try and figure out what they are out selling. It slows you down and stresses you out at month-end, and that’s the best case scenario. Because the alternative is you hold up a marketing release so you can figure out how you’re going to account for it, and then it seems like everyone is mad at you.”
- Cody Leach
7. The cycle that never ends
The worst nightmare of them all is the recurring one. The relentless cycle of the month-end close. By the time one month is closed, the next is already here, leaving no respite for accountants.
That is, unless you finally motivate yourself and your team to create change and embrace accounting automation.
All of these nightmares above are rooted in the inefficiencies of traditional (see as: outdated) accounting processes. But in our professional opinion, the real problem in accounting isn’t the process. We know this because a process improvement won’t fix things or get you the outcomes you desire. You can only get so efficient at Excel. There is a bigger challenge to address.
The real accounting problem is a data problem. The outcome accounting and finance orgs seek is data transformation at scale. And that’s the value that automation brings to the table. By automating these data transformation tasks, you not only alleviate these pains but also enable yourself and your team to focus on more strategic, value-added activities. Adopting a data management perspective and leveraging accounting automation tools like Leapfin can transform your accounting function, and reduce errors, audit fees, and overall frustration.
Ready to wake up from your nightmare? Get a demo to see how Leapfin can help.
