Financial Status Report Automation
The Accounting Unit at a Large Manufacturing Company creates a financial status report each week displaying all quarterly and year-to-date orders, shipments, backlog, and shippable backlog in order to keep track of the company’s deferred revenue. This report is critical to the company’s accounting operations; it provides a detailed view of the organization’s financial health and is reviewed by the CFO each week.
Since this report is so important to the company’s accounting operations, the stakeholders wanted to begin creating it on a daily basis instead of weekly. Manual creation of the financial status report takes three to four hours, and mainly consists of copying and pasting data and formulas from different spreadsheets and balancing the final Status Data File. To save hours of manual drudgery and eliminate the risk of human error, Accelirate developed an automation to collate all the data, apply formulas and perform calculations, then create and balance the final Status Data File.
The Bot first logs into the company’s ERP system with assigned credentials and selects the date range, then extracts the sales, backlog, shipments, and shippable backlog data. The Bot creates an Excel sheet and transfers the date range from the ERP to the Excel file. The ERP data populates and the Bot saves the sheet.
The Bot then creates Deferred Revenue files. It does this by applying all filters to the data in Excel that match the list of Deferred Revenue Item Classes, then copying and pasting all the matches into a new Excel sheet for filtered data that applies to deferred revenue. This data is used to create the RAW File needed for the maintenance agreement section of the report. In the RAW File, the Bot uses a table supplied by the business indicating which line items are hardware agreements and which are software agreements, then pastes formulas into the appropriate rows to calculate deferred revenue. After this, the Bot builds the Status Data file and the Balancing File in Excel. The Balancing File has formulas built into it to determine if all data in the file balances to zero.
If the file does not balance to zero, the Bot flags it as an exception and notifies an employee that manual review is required. If the file balances to zero, the Bot moves on to building the Status Data File, which provides a summary view of all data within the report. Once complete, an employee uploads the file to Hyperion where it can be accessed by the CFO and the Accounting Unit.
The Bot takes 180 minutes to create the financial status report, reducing process time by 25% while eliminating human error. This automation now runs daily, making it easy to measure and track the company’s financial health while also providing richer and more precise data for the Accounting Unit and CFO. Since the automated report format matches those used by the company’s international divisions, it can be applied to parallel subsidiaries across the globe for higher-quality business insights.