Designing a KPI Reporting app within Excel

hi all,

I apologise for the vagueness of the thread title (&/or posting in the wrong forum) & will understand if I get a wrap over the knuckles for it - please amend (or ask me to) as you see fit.

My main goal from this post is to save myself a lot of time & effort by Planning thoroughly first & then developing second.
I've just started working on the biggest Excel project I've had so far in my short working life (Yeehah! ). I'm hoping that the more experienced members of the forum will please give some comments on the suitability/effectiveness of the below "Conceptual" approaches to developing a KPI Reporting application within Excel?

Project Aim:
To provide a "manual portal"* for multiple users to view analysed KPI Warehousing data within Excel in a graphical or suitable tabular format (I'm leaning heavily towards PT's) with the ability to summarise info relating to 100+ Warehouses for a range of time periods and at a range of levels (National, hub, or store; potentially at the same time as differentiating between Factory, Company or Third Party).
There is already upto 3 years of weekly/monthly^ historical data from various sources~ for each of the KPI's (10ish so far & more measures likely to be requested as the time goes by) and there is a requirement that the portal can have new data added on an ongoing basis (ideally "at the press of a button or two").

*Resource may then be provided to develop this from a Excel based format to a web-based or other specific app.
^May present some challenges matching/consolidating periods eg Payroll Runs are fortnightly and Tonnes Moved can be fortnightly but intention is that both are compared in a monthly "Productivity" measure.
~eg Data Warehouse, Oracle, SAP to name a few with historical extracts now all in xls or csv files.
(I'm trying to identify exactly which data can be taken from a single source so I get closer to "one version of the truth".)

Possible Designs (that I'm considering - in no particular order):

1) Single file with data tabs; PT tabs (if tables are appropriate); & Graphs aka Charts (possibly based on the PT's) for each KPI (ie 30 plus tabs) that are:
- hidden until "requested" on a "Front page".
- flexible through use of dropdowns, dynamic expanding ranges & then some complicated (?) dynamic ranges to change the charts (ie formulae driven).
- PT Charts.
A positive for this that it would be "all in one".
The downsides I can think of are increasing file size, calculation speed, update delays &/or the need for detailed coding to keep PT Charts consistently formatted.

2) Same as above with a single file but macro driven "on the fly" chart creation using data from either PT's or autofiltered data tabs (possibly using array's read into the memory for speed???).

3) Multiple files with a Reporting/Selection or "Front Page" file that accesses separate "database structured files" for each KPI & manipulates the data before graphing, through PT's which are either:
- based in the Front Page file, (ie separate from their source data?).
- based on a separate tab in the database file.

4) Something completely different...


If anyone has the time I would greatly appreciate any comments you have that about what design approach would help me make this Reporting app' as userfriendly & robust/maintainable as possible.


Thanks in advance,
Rob

You may find this an interesting read.
http://www.datapigtechnologies.com/Books_edar4d.html

I'm actually in the mist of reading this myself. I have only got as far as chatper 3 but already I think most of the points you raise have be mentioned.

Thanks Andy

It does look interesting, I'll chew through the "sample files" over the weekend, see how much is transferable to Excel 2002-03 & then may try to talk the boss into paying for the whole book

Others,
Can you please continue adding comments?

Thanks
Rob

Hi,
I am also very interested in this subject, but the reference is very limited. You can share documents as well as experience? Thanks!

I found some references on this subject, please refer to everyone here: Graphic designer KPI
Best regards.