Report in Excel like an OAP

When I work with clients on their management reporting I usually advise them to separate out the process into three, using my OAP approach.

OAP stands for Obtain, Analyse, Present:

Obtain - obtain the data. This may be through, say, an input sheet, but more likely from a database such as your accounting system or ERP package. Typically you would pull this in through an ODBC link. It should be pulled in in a format that is easy to analyse, i.e. in columns with unique headings.

Analyse - Analyse the data. The best way to do this is usually through Pivot Tables, particularly when combined with formulae, particularly lookup formulae, alongside the data.

Present - Consider whether the Pivot Table can be amended to present the data in an acceptable format, alternatively use the GETPIVOTDATA formula to populate any report, such as Management Accounts.

By treating each of these steps as separate, you will find that you can create far more flexible reports, simply.

Have a look at my video training course on Pivot Tables. Alternatively if you want to look at this whole approach in a lot more detail and you can get to Sunderland for 21st January, book yourself on my workshop.