Professional Services Dashboard for Performance Tracking

Mekko Graphics-Based Professional Services Dashboard David Goldstein January 2013

Purpose of this Presentation The following slides are a sample of a weekly dashboard that a professional services firm can use to track its performance. The dashboard was created by extracting data from our time management and CRM systems, analyzing it using Excel PivotTables, and presenting it in PowerPoint. The data are disguised and are based on a company with about 50 billable consultants. The charts are described in several blog posts in: http://www.mekkographics.com/category/charting-strategy/

Notes on Consulting Process Each consultant enters timesheet information weekly. Management forecasts hours for each consultant by project for the upcoming four weeks. The sales team divides opportunities into fours phases. We assign dollar values to opportunities in the last three (need identified, pre-proposal and proposal) and track these. When a project is started we add its estimated hours to our backlog and deduct from this as we complete work. From a technical perspective, data were collected using: Microsoft CRM to track opportunities Edison s Attic s Bigtime for project and time tracking A home grown Excel macro to extract data from CRM and Bigtime s SQL Server databases and populate Excel PivotTables

Utilization and Hours Worked actual budget Utilization is a good indicator of profitability. We compute it by dividing billable hours by available hours. We also include billable hours in the graph to tie this graph to upcoming graphs and so that employees can see the relationship between the two variables.

Consulting Hours by Client 1,257 1,348 1,392 1,392 1,387 1,354 1,370 1,411 100% National Energy Global Conglomerate 80 Regional Law Firm Large ASP National Cable 60 High Tech Mfr Boutique Retailer 40 Private Equity Regional Telecom 20 Local Bank Global Consumer Products 0 Local Hospital 8/17 8/24 8/31 9/7 9/14 9/21 9/28 10/5 actual budget With this 100% stacked bar, you can quickly see what percentage of your business comes from your biggest clients and how this is trending over a two month period.

Consulting Hours by Project Week of 9/7 Actual Office Productivity 10.0 321 Boutique Retailer: Sw Eval 0.5 199 263 187 181 143 98 Total = 1,392 100% Local Bank: Process Mgmt 40.0 Process Automation 65.0 Extranet 26.0 80 Intranet 86.0 National Cable: Enterprise Search 71.0 60 Forms Compliance Portal 187.0 Deal Tracking 143.0 Management 263.0 KM System 246.0 40 BI Dashboard 72.0 Large ASP: Workflow 87.0 BI Dashboard 95.0 20 0 Large CPG Other Regional Telecom National Energy Global Private Equity Local Hospital Conglomerate This marimekko chart provides information on the mix of clients and projects from the previous week. It can identify if we re spread too thin or are overly dependent on large projects of clients.

Billable Hours by Project Actual Versus Budget Week of 9/7 Row Labels Local Hospital: BI Dashboard Local Hospital: Extranet Large CPG: KM System Large CPG: Process Automation Large CPG: Office Productivity Local Bank: Process MGmt Regional Telecom: Forms Management Private Equity: Deal Tracking Boutique Retailer: Sw Eval National Cable: Enterprise Search Large ASP: Workflow Global Conglomerate: BI Dashboard Global Conglomerate: Intranet National Energy: Compliance Portal Grand Total ACTUAL BUDGET Variance 72 26 246 65 10 40 263 143 0.5 71 87 95 86 187 24 83 252 81 48 -57 -6 -16 10 -28 40 16 0.5 -2 0 68 223 127 0 73 84 109 62 183 1369 3 -14 24 4 1391.5 22.5 We use the stop lights in Excel to highlight differences between actual and budget for the previous week. This is a good check on our estimation process and it helps identify projects that could be behind or could go over budget.

Consulting Hours by Project Budgeted for Week of 9/14 398 201 187 176 110 315 Total = 1,387 100% Boutique Retailer: Sw Eval 32 Office Productivity 46 Intranet 20 Process Automation 65 80 Large ASP: Workflow 65 Local Bank: Process Mgmt 65 60 Forms Deal Tracking 187 Compliance Portal 176 Management 201 BI Dashboard 90 40 Local Hospital: BI Dashboard 71 KM System 287 20 National Cable: Enterprise Search 82 0 Large CPG Regional Telecom Private Equity National Energy Other Global Conglomerate Like the previous marimekko chart, this chart displays project/client mix. In this instance for the upcoming week.

Consulting Backlog By Project in Hours 4,571 326 100% Projects Under 80 Hours 325.5 Portals:CBR-Treasury 16.0 Bank Portals 94.0 SharePoint Intranet Phase 2 17.0 Bain & Company:MCM 129.0 Workflow & Document Reposit 21.5 Tech Slide Tool 191.5 80 Pharma SharePoint 200.0 Consulting BCX 40.0 Tech Office Auto 218.0 Tech MOSS 2007 Consulting 45.5 60 Financial Reporting 1,288.0 Financial MOSS Review 55.0 40 Pharma Request for Public Disclosure 55.5 Consulting Graphics 1,667.5 20 Pharma Extranet 63.0 0 Backlog Projects Under 80 Hours Backlog The 100% stacked bar provides a summary of backlog by project. The second bar is an explosion of the data for our smaller projects.

Sales Pipeline as of 9/14 SDPS / BVPS perf mgt 360 feedback solution $4,000 Inventive -- WSS tuning $5,000 Health - QDS Survey integration $6,000 $155,000 Micropower - SharePoint Work $5,000 $205,000 $374,500 Total = $734,500 100% Vistaview -- SharePoint consulting services $7,000 MOSS architecture $10,000 Pine Street Inn - Compliance Portal Pilot $10,000 College - custom dev web app (SQL) $12,500 API Enhacements $10,000 Compliance Portal $10,000 Long River SharePoint Consulting $20,000 Pharma BI $10,000 SDMC MOSS misc assists $14,000 Life Sciences - Sharepoint planning $25,000 80 ANC -- SharePoint consulting $12,000 Caprio MOSS 2007 consulting $17,500 BVPS $15,000 James Intl Project Server/Sharepoint Integration $15,000 Non-profit - SharePoint Info Architecture $41,500 60 WellPet SharePoint portal $20,000 40 PJ's follow up $300,000 Apex Partners SharePoint Consulting $25,000 Healthcare -- DACC Clinician Info Repository $100,000 20 MOSS intranet $40,000 0 Need Identified Pre-Proposal Proposal The Marimekko chart captures the each significant project in the pipeline, organized by stage. You can see the relative importance of both individual projects and the stage itself.