Excel Automation To Create and Track Invoicing

To Create and Track Invoicing

The Client is one of the leading distribution company in India.

THE REQUIREMENTS

The accounting software used by client to track invoices and orders was incapable of summarizing weekly/monthly expenditure in a suitable way for the financial reporting processes of their accountants.

To work round this problem, the data was being exported to Microsoft Excel. Once all the data had been transferred to Excel, staffs were allocated to the task of creating a table by manually copying and pasting all the data. From this data table, pivot tables were created to produce the final report.

The time taken to complete the exercise was up to 4 man hours per month and the process was highly sensitive to human error, resulting in the need for additional quality checks to be performed.

We were asked automate the tabulation and reporting process in order to eliminate the manual work, eliminate errors and speed up the process.

THE SOLUTION

A program was written in Excel that was capable of carrying out the manual work at the click of a button, creating the final table and the pivot reports in a single action.

The solution worked by moving through each used cell in each worksheet and testing the cell contents to decide what type of data was being stored in the cell. If the data was found to be useful it was then automatically stored and reported in the correct format.

However, because the consistency of the exported data could not be guaranteed, an issues log was also automatically created by the program. This was used to report any data that could not be correctly categorized, enabling the user to manually check whether any unforeseen issues had arisen.

The software was set up so that it could run in any open and active workbook by the simple click of a button that was placed on the Standard Excel Ribbon.

RESULTS & BENEFITS

The time taken to create the report in the required format was reduced to less than a second whilst the issues report was able to indicate that the tabulation had been successful and thus create confidence in the solution.