Not more spreadsheets to analyse and chart…
Always a regular cry in the office. Reports produced on a regular basis need to be embellished, with charts that involve some fairly complicated calculations – cross-reference look-ups, previous month comparisons, and all that sort of thing.
Probably, now this is done largely by hand. The data sits in several spreadsheets and/or databases, and is extracted, filtered, pivoted and everything else that was necessary to get it ready, before feeding into a chart. When the chart was ready, it is pasted into the report.
Wouldn’t it be great with the data is all in one place, and with a single front-end, the necessary criteria set from drop-downs. Once these selections have been made (locations, benchmarks, which dataset to use, and so on) you click a button and go and make coffee. Actually, you haven’t really got time to make coffee as the process takes just a couple of minutes – if that.
In this instance, any number of queries can be run; sometimes one query, sometimes several to generate the data. Each one automatically loading a into an Excel workbook, in to the right columns, in the right order to aid presentation. Then, your screen explodes into a fresh multi-coloured set of charts based on the information just collated! And is that the pivot table I slave over every month appearing before my very eyes!
In the past that has meant spending anything between a couple of hours to a couple of days for more complex reporting every period. Multiply that by twelve, and it becomes a not insignificant amount of time in a year m to get these twelve reports, graphs and charts prepared.
Say it was half a day of someone’s time, at the average salary of £26,000 – that’s a cost of around £60+ to the business for each report, or chart. Each and every time.
If reporting is weekly, that’s going to be more than £3,000 every year – more likely a lot more than that. If it now takes 30 minutes to do the whole job, that’s a total of just over 4 days for the whole year’s worth (at one report a week). The four days will cost under £500. So you get to keep £2,500 to do something else with.
What would you like to do with it?
Excel automation by Bob Lewis-Basson – 07802 441 728
