Financial Reconciliation — How we made the Auditors Happy

Our Finance team at HotelTonight, like many finance teams at mid-size companies, used to struggle with the whole accounting reconciliation process and dreaded the thought of not being able to fully reconcile their accounts at the end of each period. When our company was small and the transaction volume was low, the process of manually assembling data from different sources and reconciling in Excel was easy. However, as HotelTonight grew and the number of payment processors, transactions, and integrations increased, manual reconciliation using Excel became unmanageable and error-prone, resulting in a lot of rework at the end of every month.

As long as you classify the transactions as credit/debit and assign them to the correct ledger accounts, it should be easy, right? Well, not so fast …

What is Financial Reconciliation?

Financial reconciliation is the process of accurately accounting for all transactions (revenue, payments, expenses, liabilities etc.) that have occurred for a business for each reporting period — typically a month. At HotelTonight (HT), we separate the reconciliation process into two main segments, each with its own unique set of challenges:

1. Transactions between the Company and its customers (Cash In)
2. Transactions between the Company and its partners (Cash Out)

Critical operational decisions rely on accurate financial data — both actual and forecasted numbers. Revenue forecasting, in turn, depends on actual data being correct. To instill and inspire confidence in our key metrics, we need to validate each of our transactions against our bank cash activity and audit the trail of money for completeness and accuracy. To meet Generally Accepted Accounting Principles, financial data that has been historically reconciled should be robust, and should not fluctuate once the reporting period has been closed.

Why is it so hard?

Payment transactions from customers typically flow from HotelTonight through a Payment Gateway, then to the various Credit Card Processors, before ultimately winding up in our bank account. Simultaneously, we kick off the “Cash Out” process, which handles paying our hotel partners.

To put together an end-to-end picture of these transactions, we have to integrate data from multiple sources (HT, Financial institutions, Banks, Gateway Processors, and third-party data providers) into our database in various different formats, and we need to standardize that data and store it at a regular frequency. We’ve automated these data pulls where possible, though some extracts are still manual processes.

Matching transactions from various data sources is complex. Transactions in different data sources are stored at different levels of detail, and different business rules may apply to each data source. One transaction may spawn a lot of child transactions, so special allocation rules often need to be applied to reconcile correctly. In addition, since the data comes from numerous sources at various times, a transaction may be unreconciled for a period a time before ultimately becoming reconciled later. The margin of error is small due to the sensitive nature of financial data, with the risk of failing audit. Errors identified may lead to re-statement of revenue for prior periods. Needless to say, this tends to leave a bad taste in the auditors’ mouths.

Cash-In customer transactions follow the same flow for any given transaction, and we have control over them. However, the cash-out process has many error-prone stages/steps — mostly due to the nature of virtual credit cards. A virtual credit card, as the name suggests, isn’t a physical card that you hold in your hand; it’s an automatically generated credit card number that changes every time the real credit card account is used.

We use virtual credit cards (VCCs) in the travel industry to pay our hotel partners. These VCC transactions are processed by humans, who unfortunately have been known to make a mistake or two. For example, hotel front-desk agents can charge the VCC for the wrong amount, key in the wrong card, or misplace the card information, resulting in VCCs that never get charged.

Original Approach and Challenges

HotelTonight transactional data flows into our Snowflake Data Warehouse from MySQL using Alooma (Data Pipeline as a Service).

We manually downloaded transaction reports from banks, the gateway, and processors as either Excel or CSV files. We then compared and validated our data in Excel using custom macros and scripts.

This process was very slow and error-prone due to the sheer volume of data involved in the reconciliation process. Changes to hotels, hotel brands, contracts, bookings, credits, refunds, and cancellations were issues that caused cascading problems for us and resulted in frequent rework of our financial reports.

New Strategy and Solution

We felt these reconciliation problems at the highest levels of our company, and thus we were all committed to coming up with a solution. The Data and Strategy team, in collaboration with the Finance team, weighed the pros and cons of out of the box solutions versus building an in-house solution like other successful start-ups. We ultimately decided to build the automated reconciliation in-house, as this gave us maximum flexibility and control. We defined the standards and rules for tracking transactions.

We set the following goals and milestones for this project:

• Tie transactions all the way from the Customer to the Hotel, with the vision of building a “Big Table” that can trace a given transaction from booking, to gateway, to processor, to bank, and to the Hotel payment
• Automate data pulls from each processor and tie transactions together between sources
• Establish a process for formalizing contract management using a CMS, and set up a protocol to re-negotiate and sign contracts with existing Hotel partners
• Accurately report our Due to Hotel Liability (the amount HotelTonight owes to Hotels) for any given month
• Generate transactions that can be posted to General Ledger on a monthly basis
• Get through the financial statement audit with no issues

Cash In

“Cash In” refers to the incoming transactions — ie the money we collect from our customers. Our first goal was to reduce manual transformations and errors in Excel for transactions already in HotelTonight tables.

Our Finance team used to pull data from the various transactional tables and stitch them together in Excel to produce a Bookings Summary, Detail and Journal Entry report every month. We quickly consolidated all booking-related transactions in a table, by keeping the grain at the lowest level (the “room night” level, ie, one record for each night in a stay). This data model is easy to aggregate and allows us to create a journal entry dimension with definitions of accounts. To track transaction changes daily, we built history tables for every table that contained financial information in our Data Warehouse.

Our second goal was to tie transactions from the gateway to the processor to the bank programmatically, by extracting data from FTP servers or via APIs when available. To achieve this, we commissioned a team to build a Proof of Concept to decipher Audit Command Language logic, and reverse engineer the logic to SQL.

Our third goal was to automate and schedule all of the above processes. We use Airflow to handle the jobs workflow and its scheduling. We leveraged our current QA framework to validate data daily and built a rich library of tests to monitor and log issues. Reconciliation categories were added to each transaction in our final reconciliation table, and we built a history table to keep track of these changes at a daily level. By doing this, we can not only provide reports as of a particular date but also understand reconciliation trends across time. To make sure our ETL jobs run fast, and keep a history of changes, we incrementally load the changed data.

Finally, we let the jobs run for a period of time and manually validated existing reports with the output from the new process. As is often the case, this surfaced a number of issues that we had not anticipated. Once we fixed them, Cash-In was fully automated — Woohoo!!!

Cash Out

“Cash Out” refers to outgoing transactions — -The amount HotelTonight owes to our hotel partners. As mentioned previously, it’s standard practice in the industry to issue virtual credit cards to pay hotel partners. The challenge in using VCCs as a form of payment is the unpredictable nature of when, if, and for how much the card is going to be charged. As a result, a robust process of tracking each issued card and calculating the balances on those cards is required.

The contracts between HT and the hotel partners dictate what action is required for any VCCs left incorrectly charged for a specified period of time. Further adding to the complexity, a contract may be renegotiated to cover different hotels at different times as a result of ownership, management, or brand changes. Key terms of the contract may also change during the course of re-negotiations, such as the details around how much time the Hotel has to charge the VCC. The reconciliation must factor in all the contract changes over the lifetime of the partner relationship to accurately calculate HT’s liability.

Using VCCs to pay hotel partners also comes with operational risks. We must fund the card with the right amount and ensure that hotels do not use the card in nefarious ways. Hotels may under- or overcharge the card, and liability periods may not end for 180 days. We undertook a company-wide effort to ensure that all the contracts are properly uploaded in a robust repository, leveraging Salesforce and Ironclad as operational tools.

With these considerations in mind, we identified a set of requirements:

• Accurately report Due to Hotel liability balances for each booking per hotel by reporting period (month)
• Easily drill-down to the transactions associated with each balance
• Report accurately based on Hotel Contract History, as contracts can change over time

To meet these requirements, we ensured history is captured for all the data sources. Since the liability period can last longer than 180 days for many contracts and keeping a snapshot of all bookings at a daily level can result in a huge volume of data, we decided to build a time-span transactions history fact table. Rules for liability, write-offs, and balances were implemented in SQL and version controlled. We built out a balances table by month from our transactions history fairly easily.

In order to prove that the DTH data can be populated incrementally with changing contract terms, we built a backfill program that populated data one month at a time. After each month’s data was populated, we put in our unit tests as part of the data pipeline to capture all errors and store them in log tables. We then scheduled all of these processes to run daily using Airflow.

Audit

The financial statement audits in previous years were very painful for the Finance team, as they had to chase down a lot of detailed, hard to find data to answer audit questions and clarification requests. Proving that the data was reliable and of high quality had not been very easy. To address these concerns, the Data and Strategy team developed a robust monitoring and QA framework for all data pipelines. This framework came in very handy for us to build QA checks (SQL validations) and fix issues in an agile fashion.

Using the output of the cash-out reconciliation, the Finance team has created a “roll forward” report by month showing the Due to Hotel liability. With our balances table, this was easily accomplished in a couple of queries, instead of downloading many reports and combining them in Excel. To help the auditors gain comfort over the roll forward schedule that was generated by the system, the Finance team put together documentation of the various use cases and walked the auditors through how the transactions can be tracked from end to end. From there, we were able to pull a complete set of transactions from which the auditors validated against the GL and selected their audit samples. From the selected samples, we were able to show the unit tests and their results by querying the log tables in Snowflake. In addition, the cash-out reconciliation gave us the ability to query which contract terms applied to any given booking, and from there we were able to pull the contract document supporting the transactions. Needless to say, we passed the audit with flying colors.

Conclusion and Looking Ahead

Having been through one audit and being able to respond to questions about data quality and accuracy fairly quickly gives us a lot of confidence that we are in a better place. In this process, we have also been able to backfill data and show that the data pipelines are resilient and can be quickly altered to fix issues found in business rules or bugs in transformation. We made the Auditors Happy and so can you!!!