Below is a short case study of a company I helped automate calculations for reports without spending any extra cash.
Situation at the outset
The company produced consultancy reports for clients. The reports were created entirely in MS Word. One report could contain a total of approximately 110-160 pages. Numerical source data were entered manually into various tables across the document. Some summary tables at the start of the report contained numbers and comments from tables in the core of the report, which in turn were calculated from tables in the last third of the document.
Report-writing was not only a lengthy process, but also flawed by the inherent risk of introducing human error every time results were manually calculated and then entered elsewhere in the document. It required great attention to detail and concentration. Getting new staff up to scratch quickly was challenging.
To reduce the risk of sending false results to clients, a colleague along the reporting chain routinely verified the calculated data. This meant that additional staff time was invested and staff felt frustrated by the cumbersome process of manually calculating results from figures scattered across many pages.
My solution was to automate the calculation whilst staying aligned with the report format.
I created an MS Excel file in which staff entered the source data. All calculations were automated through formulas. Alerts were built in to highlight false results due to errors in data handling, e.g. missing a compulsory step. Macros were used to increase user-friendliness. A bar chart was automatically created and all results tables were readily formatted in Excel to be pasted as-is into the MS Word report (the data link option was dismissed as potentially too unreliable). The 20 MS Excel tabs followed the same flow as the sections in the MS Word report.
Savings in staff time and nerves were significant.
- Calculations that used to take at least 2 hours per report with constant checking back and forth across a long document were virtually eliminated.
- Verification of calculations, which took almost just as long, has also been eliminated.
- It has become easier to train new staff in report-writing because calculations are automated.
- The slicker process flow has made reporting more pleasant for staff.