using excel in project management
TRANSCRIPT
Using Excel in project managementHoang Bao Long, M.D.
Clinical Research Coordinator
Oxford University Clinical Research Unit
Research project management
Pre-implementation
> Regulatory documents
> Human resources
> Facilities
> Coordination planning
During implementation
> Site checking visits
> Facilities (cont.)
> Data verification
> Data entry & management
> Monitoring
Post-implementation
> Inventory checking
> Data management
> Close-out procedures
2
Questions during implementation
Patients enrolled/followed
• How many per site?
• How many per month/week?
Facilities
• How many CRFs/files left?
• How many kits/lab consumables left?
Samples
• How many collected/transferred/stored?
• Any mismatches?
Progress update
• Anyone without training?
• Any monitoring visits to conduct?
3
Why to question?
• Progress update:
• Is the study running smoothly? Any sites behind?
• All samples transferred and stored? Any samples missing?
• Inventory check:
• Continuous provision of study facilities
• Limited capacities at study sites
• Better for management of study consumables/inventory
• Handover minutes (related to finance)
4
Example: Nga’s CRP study
• Research question: Effectiveness of CRP POC test on antibiotic
prescription rate in patients with mild-to-moderate acute
respiratory infection.
• RCT, 10 sites (9 OPCs & Ba Vi hospital), control:intervention 1:1,
adult:children 1:1
• Targets: 2000 patients (200/site)
• Monitoring visits: 1st – 20 pts; 2nd – 100 pts; 3rd – 200 pts
5
Example: Nga’s CRP study
CRP test (site)
Urine sample
CRP test (site)
DAY 0 DAY 3-5 DAY 14
Enrollment (site) Follow-up (site) Interview (OUCRU)
Update study
status
6
Example: Nga’s CRP study
• How many patients enrolled / come back for follow-up /
interviewed? (per site / per month)
• How many urine samples collected? Any mismatches (patients come
for follow-up but no urine samples, patients not come for follow-up but have urine
samples)
• How many consumables left? (CRFs, ICFs, urine containers, kits, lancets, tips)
• How many CRFs entered to CliRes?
• Any sites need training? Reached targets for monitoring?
7
Using database in project management
• 2-dimension table: the simplest way
• Concepts:
1. Field (columns)
2. Record (rows)
3. Key
• Parameters:
• Raw parameters: direct input from
files/CRFs
• Calculated parameters: based on raw
data
8
1
2
3
Excel in project management
STRENGTHS
• Good way to create 2-dimension tables
• Functions: rapid calculation of parameters
• Pivot Table & Pivot Chart: a good tool for summary
• Filter: quick access to a subset of records
• More visual than other biostatistical softwares
9
Excel in project management
10
Excel in project management
WEAKNESSES
• Unable to perform customized and complex summary
• Tricky to unexperienced users (requires knowledge about database
management and functions)
• Very limited reference to other databases
11
A new feature since Excel 2007: Tables
• Previous versions: “Area”
• Complicated reference
• Need redefining if the dataset is expanded
• Since Excel 2007: “Table”
• Simple reference: tables and fields have names
• Recognized in every sheet
• Automatically expand when a new field/record is added
• Formulas become universal
Demo: Academic Meeting Demonstration.xlsx
12
Designing database
STEP 1: PLAN YOUR MANAGEMENT
• Your questions output data
• Output data input data
• Examples:
• How many patients enrolled per site/month?
• How many CRFs left?
13
Designing database
STEP 2: CREATE TABLES
• Create fields for raw parameters (site, date of enrollment, number of
delivered CRFs)
• Create fields for calculated parameters
• Year of enrollment = YEAR([@Date of Enrollment])
• Month of enrollment = MONTH([@Date of Enrollment])
• Remaining No of CRFs = COUNTIFS(…) – COUNTIFS(…)
14
Generating reports
• Reporting tables:
• Making use the same principles with other source tables
• Visual (changes seen immediately)
• Can be customized using functions
• Pivot Table/Chart:
• A rapid way to generate reports
• Need to refresh when changing source tables
• Limited customization to some predefined functions
Demo: 05HN CRF Log.xlsx
15
Summary
• Resources management is important.
• Timely provision of facilities and support
• Avoid regulatory and financial issues
• Management has to be continuously done.
• Excel: a preliminary but good tool to manage research projects.
• Effective features: tables (since Excel 2007), functions, filter, pivot
table/chart.
• Users need to learn database management and Excel.
16
THE ENDThank you for listening!