Revenue Reporting & Invoice Generation Process for Shipping Industry

The client is the world’s largest independent feeder carrier company.

They have been in this industry for more than 50 years, serving globally and seek to provide the most reliable and cost-effective service solutions to support their customers’ feedering needs.

The scope of this project is as follows:

• Design, Develop, Document RPA solution to automate Revenue Reporting Process & automation of the Invoice Generation Process.
• Capturing the New Start Date/New Hire Date of each vessel voyage.
• Preparing Cut Off Report, Load Break Down Report, and SAP Report etc.
• Calculation of Total TEUs and Comparison of Total TEUs  in DFR with Total TEUs in LBD report for each voyage and update or highlight in the report.
• Calculation of Total Revenue and Comparison of Total revenue in the formatted SAP report with Total Revenue in DF report for each voyage and update or highlight in the report.
• Search for the HUB with pending PSF’s in internal  web portal.
• Reading and checking the associated Excel sheet for the Input Hub Regions.
• Capturing the pending PSF details as each PSF needs the Draft Invoice.
• Proceeding for Draft & Actual Invoice once customers Tables data is corrected.
• Prepare the Excel report with details of Actual Invoices and send mail to Business.

If there are any mismatches, a notification to be sent.

• Send the summary report so that users will be using it for further investigation.

The current processing involves a lot of manual extraction of data from multiple systems and repeated data preparation and processing steps by multiple people.

The current process is very manual and requires a lot of time and effort to complete the process end to end and there are chances for the occurrence of errors. 

And in the invoicing part, finding the departure report and collecting details such as shipment details, container type Voyage details, PO Number and Rate (Surcharges) for generating the invoice is time consuming and there are chances that some details can be missed out or even errors may occur when done manually.

1. Revenue Report Generation

The User will place the Hire Required files in the SharePoint inside the corresponding closing month folder.

The User will trigger the robot to run. The robot will pick up the files and get the services.  

For each service based on the report file, if applicable, the robot will update the new start date/end date of the vessel.

The robot will also pick-up the details from the port info file to access the folder.

The details captured from the Vessel Info and Port Info file for each service will be used to create the Cut-off Report.

The CSV file of the corresponding DFT for the service will be copied and pasted into a macro-enabled file which will then run the macro.

This will be the DFT Report and the robot will create a new worksheet on the Cut-Off Report and paste the DFT Report data.

The robot will login into SAP application and enter the input values such as service from Cut-off Report, from and to month as current and next month correspondingly, account numbers and execute the page to get the report and then from the layout section download the report in desired format and save this report as GL.

The robot will capture the generated report and then paste the data into a new worksheet on the Cut-off Report and rename the worksheet.

The bot will then perform revenue checking for each LBD report.

The robot will sum up the total TEU for each LBD report and compare it with the total TEU in the DFR report.

Also, it will sum up the total revenue from the SAP file and compare it with the total revenue from the DFR report.

If all the values match it will update the report file saying the values match for this vessel name, voyage#.

If the values don’t match it will update as values don’t match and the business user has to manually verify them.

The bot will then consolidate the report and files generated and place them in a shared folder and send an email to business users that the process is completed.

2. Invoice Report Generation

The User will create and provide Excel reference with details of Hubs for Draft Invoice to be generated for pending PSFs, Excel reference for calculations of rates in standard Format and store the Excel files into a shared drive, and trigger the Robot to start the Process.

Initially bot will check for the Input Hub list Excel file along with Company names in the shared folder. 

When the Input Excel is found, Robot will log into Arks website with respective credentials and key in the Input hub region based on the Input Excel.

Then the bot will search for the pending PSF Numbers and will extract into excel where they will be used as Transaction Items.

The Bot will validate the data of customers displayed in tables. The bots will verify the Tables data of the customer for discrepancies.

If there are no discrepancies in the data the bot will go to “Proceed Invoicing”. 

The bot will then generate the draft Invoice and then transfers to Actual. Then the Bot Extracts the Invoice details and saves them to excel report.

The bot repeats the process for all the pending Invoices of the listed Hubs and will send the completion mail and Invoice report to Business users.

Bot also performs the Second round of Freighting which is a separate process, to check if the discrepancies are corrected and proceed to Invoicing if it is corrected.

• No Human Intervention Involved
• 100% error free data
• Faster processing time
• Eliminated Manual and Repetitive Processes
• Enabled Self-service within the team
• Increased processing time, accuracy and Process Standardization
• Automated revenue reporting process
• Automated invoice generation process

UiPath