KPI Compilation system
One of our clients had the task of compiling and summarising the
monthly Key Performance Indicators for the company, which has over
200 sites across over 30 countries grouped into 5 regions. This used
to take about 4 days to compile, but after 3 days of re-creating the
workbooks and a bit of programming, it
now takes about 4 hours.
The person responsible for this KPI report was receiving over 200
worksheets each month, and having to combine them into one master
workbook. Not just a simple 'Open Workbook, copy sheet into master
workbook, close workbook' scenario, but the individual site sheets
had to be combined into a book for the Country, each Country
workbook had a summary sheet which needed to be copied into a Region
workbook and the Region summaries had to be incorporated into a
Global workbook.
The solution consisted of 3 master workbooks; one which had a
template for the sheet each site would prepare, one for the Country,
Region and Global books, and the final one which had a list of all
Sites, their related Countries and Regions (so that any changes to
the sites or regions could be made easily without changing the
programming) and all the macros required to run the system.
The final result was a system which had every sheet in a
consistent format, could be easily modified if required and
significantly reduced the work required to complete the monthly
report.