How to Design the Perfect Audit Information Request List (and status tracker)

Any consultant or auditor will tell you that the most difficult part of the job is getting the right information from clients. That is why designing an effective information request list (a.k.a. PBC List) is so important. Oddly enough – it is also a skill that is never formally “taught” to new associates. So here’s a beginner’s (or advanced!) guide to designing the perfect information request list (IRL).

1. Data Design: Decide what elements you need for your information request list

The first step to designing the perfect IRL/PBC List is to design what data elements you want to include for YOUR OWN tracking purposes and YOUR CLIENTS gathering purposes. Remember, that a good IRL can serve as a project management tool (see step 4 below), but also has to be concise enough that clients aren’t bogged down with the details. I usually start out with what I need to manage the project then hide a few cells before I send it on to the client.

Tip: Besides those columns below a few common fields are testing phase (if you are doing interim and roll forward), locations, and framework guidance (for example if you are doing and ISO or SOC audit).

Click to enlarge.

Click to enlarge.

2. Dummy Proofing: Create drop-down lists and conditional formatting

The next thing you want to do is “dummy proof” your IRL. This is most easily done by eliminating text options by making everything possible drop-down only. The less ambiguity a client (or other teammates) can throw into the mix the easier your job becomes as a project manager.

I am also a very visual person so I like to use conditional formatting in excel to change colors based on status (i.e., Received turns green).

Click to enlarge.

Click to enlarge.

3. Simplify Before Sending: Filter list by owner and hiding columns

Remember that a lot of the information you included in your IRL was for your own benefit. This can be confusing to clients so it is important to simplify the IRL before you send it out. I usually do this by hiding non-vital columns and filtering by owner, phase, location, etc.

For example, if I have 5 owners who will send information I usually filter the IRL by owner and send each individual a personalized email.

Click to enlarge.

Click to enlarge.

4. Create an automated status report and dashboard

If you are managing a project and have hundreds of requests you probably know how difficult and time consuming it can be to send out daily or weekly status emails begging clients to send data (and keeping your boss up-to-date). Instead of manually counting or simply putting data into a boring table create a beautiful chart that updates automatically!

You can easily utilize pivot tables to create status charts (shown below) and quick lists to drop into status reports. Keep this in mind when you are designing your IRL (step 1) and be sure to include information you might want to track data.

Click to enlarge.

Click to enlarge.

9 thoughts on “How to Design the Perfect Audit Information Request List (and status tracker)

Leave a Reply