Every company with many employees on business trips or many field staff knows the problem: At the end of each month, the statement from the respective credit card provider arrives on time. The corresponding receipts (fuel bills, train tickets, etc.) only gradually trickle in. The subsequent comparison of the statement with the receipts and the booking in the ERP system used ties up enormous time resources and thus also personnel costs in the back office and is also often heavily paper-based. In this article, we will show you our approach to this problem and present our solution approach using Robotic Process Automation.
The initial situation
Before the implementation of our bot, most of the credit card billing was done by hand. The invoice from the credit card provider (in the case of CTI Consulting: Airplus) came in the mail as a printout. On this printout, a member of our back office staff then noted all information relevant to the booking (Fig.1):
- Assignment of the account
- Assignment of the project abbreviation
- Breakdown of invoice totals according to different tax rates (e.g. hotel breakfast and hotel overnight stay)
- Check whether a corresponding document exists
The information thus created must then be posted in the DATEV accounting system (Fig. 2). In travel-intensive months, this can involve hundreds to thousands of individual bookings, each with nine mandatory fields to be filled in.
This highly manual process was time consuming and prone to errors. Therefore, the processing of the credit card statement was usually reserved for an experienced accountant. In case of vacation or illness, the process was difficult to delegate. In addition, the process is monotonous, but at the same time very tiring due to the care required.
Solution: Partial automation of credit card accounting with RPA (UiPath)
In order to relieve the accounting department of the monotonous, highly tiring credit card accounting steps mentioned above, various scenarios for the partial automation of the process were evaluated. It quickly became apparent that a complete automation of the process is difficult or even impossible, since the knowledge of the accounting department about current customer projects or the individual travel preferences of the employees is enormously helpful in processing. Furthermore, the formats of receipts, e.g. hotel invoices, are so different that reading them in with conventional OCR technologies (e.g. splitting the invoice amount according to different VAT rates) was not expedient.
Instead, the aim was to provide the accounting department with tools via bots to assist them in their work:
On the one hand, there was a changeover from paper-based to Excel-based processing. To do this, the bot independently downloads the current credit card statement for all company credit cards in a package in csv format from the credit card provider’s portal on a fixed key date and transforms it into a processing Excel. All information available in the settlement is transferred and entered in the corresponding cells. In some cases, the values are already adapted to the formats required for processing or posting in DATEV, for example, the credit card number is shortened to the last four digits. In addition, fields are generated that are required for posting, such as the posting text (month + credit card provider + employee’s ID + type of document).
To further accelerate processing, business rules have been defined for frequently occurring cases, according to which the processing Excel is pre-filled by the bot. In this way, the processing effort can be significantly reduced once again. For example, the account for fuel receipts can be prefilled automatically, e.g. 948808 if ARAL appears in the column “service provider”.
These rules were created in text files so that they can be adapted and extended by the accounting department itself without having to adapt the bot itself or tie up development capacity. Accordingly, the degree of automated filling increases with increasing knowledge about travel habits and typical service providers. The accountant is therefore more likely to play the role of a data analyst than to book dull data.
As soon as the accountant has finished processing a line in the processing Excel and has assigned the corresponding receipt, he sets the release flag. Based on all released entries, the bot creates a batch import in the target format of DATEV and executes it. In principle, the solution is independent of the ERP system, only the import format has to be adapted. An import via API’s would also be conceivable. If the booking was successful, the bot writes an entry with the exact booking time back into the processing Excel. This means that the accounting department can process the credit card statement step by step. This is particularly helpful because practice shows that employees often submit receipts late. In addition, the processing Excel is a good reporting tool for the status of the monthly statement, the traceability of the postings and the document discipline of the employees.
Through the partial automation of credit card accounting with RPA and the rule-based prefilling of booking-relevant data, the workload of the accounting department could be drastically reduced, especially with regard to the monotonous transfer of values. The solution created was proactively driven forward by the accounting staff and many other ideas were introduced during the development process. In summary, the following results were achieved:
- The processing time for the entire monthly credit card statement was reduced by approximately 70%.
- Amortization of the investment in less than 5 months with assumed 200 bookings per month (in reality much more).
- The accounting department was noticeably relieved and the dependence on individual employees was reduced.
- The traceability of bookings was significantly increased by replacing the paper-based approach.
- The created solution can be extended by other/other credit card providers (e.g. American Express, Master Card etc.) with manageable effort.
- The created solution can be adapted to new target systems, e.g. SAP ERP etc. with manageable effort.
- And most importantly: The fantasy for further RPA solutions has been awakened, in keeping with the proverb: “Appetite comes with eating!
Our Service Offer
CTI Consulting supports its clients in achieving their full automation potential along the IT value chain. Our support ranges from process selection (workshops) and evaluation of the RPA usage (CTI RPA Fit Framework) to the analysis of the systems involved and an agile implementation of the RPA software robots. With our many years of experience in the areas of Enterprise Architecture Management and Business Process Management, we will lead your RPA project to success. In addition, we support customers in the selection of RPA tools and in setting up an RPA Competence Center in the company.
If you are interested, please contact us at firstname.lastname@example.org.