RPA and Smart Automation Case Studies
The joint venture accounting department for a major oil and gas company compares all open cost items (ventures) with data from SAP and is responsible for filtering out which department expenses are complete and need to be closed. Similar to closing a tab at a restaurant, the various departments request money for items throughout the month and when the transaction is complete, the venture needs to be closed. This process is important as it allows the accounting department to finalize the cost and ensure the department received the money and items they requested.
- Bot Checks the Date – Depending on the day of the week and the date, the bot determines if it will run the full process or partial process. If it is the 5th, 6th, or 7th, day of the month and it is a Business Workday (Monday-Friday) the bot does a full process run. Every other day, the bot will do a partial run of the process, with the exception of the last two workdays of the month. The only difference between the partial run and full run is that during the full run, which happens towards the beginning of each month, the bot adds a step to create a new sheet for monthly ventures. Each partial run looks to close ventures but only the full run creates new ventures.
- Bot Downloads Deactivation Sheet – The accounting team stores the deactivation sheet in a shared drive, the bot downloads the most recent deactivation sheet, updates it at the beginning of every month to add new monthly cost centers, and updates the sheet daily after items are closed and deactivated.
- Bot Sorts Ventures – To accurately label active and inactive ventures, the bot sorts through multiple finance sheets in SAP and looks through various business objects to locate each venture. The automation is responsible for sorting through 3,000 rows of data at a time, evaluating 3,000 cost centers simultaneously. On average, the bot sorts through 2500-3500 cost centers per day.
- Bot Enters Account Number – For each SAP transaction the bot enters the venture account number and retrieves the export sheet created from the query. This sheet is then added to the master list (main worksheet) and using V-Lookup Formulas, we check if the cost centers exist for these ventures in the exported data. If they do not exist, the bot labels them ‘NO’ if they do exist, they are labeled ‘YES’.
- Bot Searches Each Cost Center – Each venture is attributed to a call center, or department and/or individual, the bot looks to see if any of the ventures have additional company codes, meaning multiple people attributed to the cost. This is a critical step because some ventures may not be able to be fully closed but can be closed for some cost centers within it; if there are additional cost centers attributed, the bot makes new rows for the additional company codes under the same venture.
- Bot Verifies Cost Center – Bot checks to see if any of the cost centers fit a mix of criteria for being marked ‘YES’ or ‘NO’ within each of the ventures extracted. If they fit the specific criteria to be replaced, they will then need to be closed using a winshuttle add-in for excel.
- Bot Extracts Data Sheets – Once the ventures are closed, the bot extracts more data sheets that are required to be sent to other business teams along with the main sheet that must be uploaded to the department shared drive. Once uploaded to the shared location, a secondary bot that creates additional venture spending reports to finalize the process.
This process is crucial in allowing the finance planning team to budget for future projects and plan out internal company expenditures. Along with automating the logistics of this process, the increased accuracy has allowed the major oil and gas company to better manage their balance sheet.