A Step-By-Step Guide To High Volume E-Commerce Reconciliations

As Bob Dylan wrote, The Times They Are A-Changin’.

Shifts in our economy and market landscape have driven organizations to adopt a new normal. This change has accelerated the pace of innovation and investment in many areas, such as e-commerce and retail. Where traditional retail companies may have received only part of their revenue from e-commerce transactions and the bulk from brick and mortar purchases, that dynamic has flipped and forced e-commerce into the front of the pack. Today overall, eCommerce accounts for under 10% of total retail sales in the United States. In ten years, studies expect that number to be closer to 50%.

With change comes different customer expectations of what and how we buy. Even as things return to a bit of normalcy, our buying habits will likely remain impacted. Expectations that consumers will continue to have is that they can order something online, pick it up curbside or have it delivered, thus saving themselves a trip into the store.

As e-commerce becomes a large portion of traditional retail’s financial operations, this adds complexity into the financial close process. For example, a traditional restaurant serving diners in the restaurant would take credit cards and cash as payment. This would make up roughly 95% of their revenue and 5% would come from delivery services like Grubhub© or Uber Eats©. This is manageable within the existing framework because the transaction volumes are low, and while fees for those services can be high, they have not accounted for much of the overall revenue, so not much focus had to be given to them.

However, consider how things have changed. Many restaurants are, or have been, closed to in-person dining, or restricted to capacity limits, so they are now operating 80% curbside/remote dining and 20% in-house. Restaurants are now required to seek additional sources of revenue – via services like online ordering, curbside pickup, and food delivery apps. These different order and payment sources are additional to the traditional diners in the restaurant, but still need to be reconciled. Food delivery companies require a fee for offering their services, which will vary from provider to provider, adding additional variability to reconciliations. What happens when someone requests a refund? With no way to best capture that transaction, it becomes a write-off.

Necessity is the mother of innovation and with this period of change comes a great opportunity for companies to evaluate how they operate and to best fit their business model to the changing customer dynamics and expectations. To help streamline the financial close, they can look to a few steps:

1. Simplify data access
2. Automate for scale
3. Enhance visibility

Simplify Data Acquisition

Your team’s time is best spent analyzing results and ensuring that you have timely and accurate financial results. You want to shift them from spending all their time obtaining, cleaning, and loading data. This can be done a few ways:

Automated Export From Your Source Systems

If your internal IT team is well adept at creating exports and reports from your source systems, like ERPs, this is certainly an option for you. Keep in mind that building your own reports does require not only initial setup but also ongoing maintenance as accounts and business requirements change, so it’s good to have long-term buy-in from the organization and not just a one-time investment.

This data could then be loaded manually into your close process.

Prebuilt ERP Connectors

Depending on your ERP and financial close solution, you may have an option to use a pre-built ERP connector that is certified by that ERP provider. This should provide the most robust integration method for obtaining your source data needed in reconciliations. All ERP connectors are not created the same, so it’s important to understand the detail of how that connection will be made and the options you have for scheduling, filtering and transforming that data in flight. This could be something as simple as in my ERP my Accounts are all padded to 10 characters with leading 0’s, but none of my other systems are like that, so I want to strip those when I’m preparing my reconciliations.

iPaaS or API Integration

If your biz apps / internal IT team is a bit more advanced, they may ask you to integrate with all external / cloud applications through middleware or an iPaaS provider, like Dell Boomi© or Mulesoft©. This provides nice flexibility for integrations, and often times allows you to also integrate via API, eliminating the need to manually upload files or use SFTP.

Automated Bank / Credit Card Data Aggregation

As restaurant groups or e-commerce businesses expand, their financial banking and credit card footprint typically expands with it. This could mean a bank and account near every location or additional credit card or POS systems to integrate with too. If you’ve ever struggled to obtain or format a fixed-width credit card file then you will know just one challenge to tackle as you continue to grow – growth is a good problem, but processes left manual will cause problems down the line.

There are options for automating the retrieval of bank data. If you have a large treasury organization you can piggyback on their TMS and banking relationships to try and automate daily (prior day) bank file, though this doesn’t typically give you an automated, end of month PDF bank statement for a rec. Credit cards become a bigger sticking point though, where needing detailed credit card information formatted and validated. That is usually best left to teams who manage that type of data on a daily basis, such as financial data aggregators, like Adra’s Data Flow Solution.

At Adra, we have designed our integration strategy to meet our customers where they are today. If you have an export of GL balances, we have an easy way to automatically load those files. If you have multiple store locations and disparate banks or have trouble obtaining, cleaning, and using credit card data, Adra has hundreds of customers using our Data Flow Services to automate bank and credit card data acquisition and aggregation. If you prefer one of our prebuilt ERP connectors for NetSuite, Sage Intacct, or Visma.net, those are great options to fully automate data access, giving your team more time back in the day, reducing data transformation risk and the burden on your IT team for ongoing maintenance.

Automate for Scale

Once you can pull clean, consistent data into your close process, it’s time to ramp up the automation for scale. This means automating as much of the manual matching as possible, only reviewing exceptions, and auto-reconciling low risk accounts automatically. Thinking back to our e-commerce example, this means the company will now have additional sources to match from Door Dash©, Uber Eats©, and more that all have different fee structures and timing. Managing a new level of complexity will not be easy, but it is possible with a bit of help.

Foundational Matching

As quickly and easily as your team can match off items, the easier it will be for you to substantiate your reconciliations. Matching can be done manually, in spreadsheets typically, but that generally leads to poor and inconsistent results. A better solution is financial close automation to routinely match transactions in a way that provides the least amount of exceptions to manually review. Having a way to build in thresholds, variances and tolerances on dates and amounts will help drive up that automated match rate and reduce the time to manually match later on.

Complex Matching

Now what happens when you add in credit cards, POS and the bank? This becomes a 3-way match that will be very difficult in spreadsheets with larger volumes. Or, this could be multiple sources on one side matching against one source on the other. Either way, these examples become hard to achieve without an automated solution to help.

At Adra, we’ve worked with many retail and e-commerce companies to help them achieve superior automated matching results for foundational and complex matching scenarios.

Enhanced Visibility

Once you have obtained your source data, automated the bulk of transactions, focused on the exceptions, and now are monitoring the overall close, it’s important to consider the holistic cycle and evaluate based on your organizational goals.

This is best done across your entire close process, starting with days to close, and looking at things like quality of reconciliations (number of recs done first time right). Once you have a benchmark, you can then continue to tweak inputs and evaluate the outcome across key metrics.

With Adra we bring all financial close information into one place, facilitating a summary view across the organization with everyone working from the same single point of truth. This will then provide in-depth analysis across the financial close with appropriate decision points along the way.

Conclusion

As you consider how your organization is adapting to the changing business climate, also look at how your existing systems and infrastructure is helping or hindering that progress. As you look to best serve customers’ new expectations there will always be a need to adapt to new complexities.

The Adra Suite of Solutions by Trintech delivers products that work together to automate and streamline work, improve accuracy and reduce risk, and better manage the detailed process of the financial close. The suite seamlessly integrates with many financial systems, such as ERPs, allowing you to close faster with confidence.

To learn more about how Adra can meet your organization’s needs, talk to an expert.

Written by: Michael Uram