Executive leadership just gave the go ahead for the PMO to fire-up another project but it feels like staff is already over-capacity to take on more work. Here’s how I built a lightweight, easy-to-sustain project labor demand solution using SharePoint and Excel to measure and prioritize project labor demand.
Does this sound familiar?
Let me take a shot at reading your mind:
Your executive leadership team just gave the go ahead to fire-up another project but it feels like your staff is already over-capacity to take on more work. That’s right—feels—a squishy, subjective term. The reality is you don’t currently have a compelling way to show leadership your project labor demand. You’d prefer to wait two months (or more) before taking on more projects but that pushback won’t fly without hard facts.
- Your organization doesn’t do project management very well because people are assigned to projects using full-time equivalent (FTE) estimates, not bottoms- up work breakdown structures. It’s not uncommon to hear, “We need a Business Analyst for three months” or “This project will consume 50% of a Project Manager’s time for six months.” Obviously these estimates change as the project progresses.
- Not all of your organization’s members are 100% available to work on projects. Functional Managers may only allocate someone on their team for, say 50% of one’s available time, while the other 50% is allocated to non-project operations work.
- You don’t require perfection because you understand the trap of false- precision. A maintainable solution that’s 80% accurate is better than one that’s 98% accurate but requires far too much energy to sustain.
- Your organization doesn’t have the budget or appetite for new project management information systems (PMIS) or project portfolio management (PPM) systems.
How’d I do? On target, right?
Yes, I too live this reality every day and that’s why I’m about to show you how I built a lightweight, easy-to-sustain solution in a few hours to help measure and prioritize project labor demand.
- Functional Managers spend ten minutes per month to maintain labor supply allocation
- Project Managers spend ten minutes per week to maintain labor demand estimates
How the solution works
The solution uses Microsoft SharePoint to collect and display user input while connected to Microsoft Excel via an OData Data Feed connection for data presentation. This provides the power of Excel’s drill-down pivots and charts embedded directly within SharePoint.
The entire end-user experience is accomplished using only four webpages on a SharePoint community site.
(Note: I stripped out the SharePoint Quick Launch bar and top menu navigation bars within the images of this article to help visually separate my application from SharePoint.)
- The first page consists of brief end-user directions. I find this useful for those users who access the tool only once or twice monthly.
- The navigation bar consists of an HTML list item that display hyperlinks, styled with CSS, that redirect users between the four pages: Directions, Supply, Demand, and Utilization
Maintaining project labor supply
The second page consists of the supply table. Users quickly navigate the datasheet view like Excel by using the keyboard arrow keys and CTRL-C and CTRL-V shortcuts to copy and paste data between cells.
- Each Functional Manager within an organization updates a table in SharePoint to allocate each of their staff to a standardized role and assign a percentage of that individual’s availability to work on projects. This exercise takes only ten minutes per month!
- In the image here I’ve assigned unnamed resources, as this is useful for tracking labor demand against not yet approved project requests or roles that may need to be filled by external resources.
- Functional Managers simply filter the first column on their name and their list of employees will appear. He enters a percentage of availability. For my purposes I’ve coded 1 FTE = 168 hours per month.
Maintaining project labor demand
The third page consists of the demand table. Much like the supply table, users find navigation easy when using the keyboard arrow keys and CTRL-C and CTRL- V shortcuts to copy and paste data between cells.
- Each Project Manager within an organization updates a table in SharePoint to indicate project demand, whether that’s a named or unnamed resource, required or assigned to work on their projects. This exercise takes only ten minutes per week!
- In the image here I’m working with only one project, and I’m requesting three developers over a period of three months.
- Project Managers (or even individual employees) simply filter the project name, employee name, or period. He enters a percentage of demand in terms of FTEs. Here 3 FTEs for 1 month at 168 hours per month identifies the need of 504 development hours in the month.
Visualizing demand and utilization
The fourth page consists of the utilization table(s) and chart(s). Since these are embedded Excel pivot tables and charts, full functionality of drill-down and filter criteria is enabled. In the pivot table I applied conditional formatting to highlight cells yellow when they are 100% – 110% over-allocated, and red when they are 120%+ over-allocated.
Finally one individual who serves as the System Administrator adds/removes roles and periods as well as provides overall end-user troubleshooting and support. This exercise takes approximately thirty minutes per week.
Graph and pivot table refresh
Getting the Microsoft Excel pivot tables and graphs to refresh requires a little trickery without local access to the SharePoint server to properly configure Excel Web Access services.
Although the Excel file sits on the same SharePoint community within a document library, the data doesn’t refresh unless the file is physically opened and saved. That’s where this small Visual Basic script comes in. I dropped this script into my local PC’s “Startup” folder to run once at startup, but I have also scheduled it to run once hourly while my machine is online.
This file silently opens the Microsoft Excel file (no visual cue the file is open), refreshes the data connection, and saves the file again and closes. The process takes about 15 seconds.