How to use MSP for Department Planning (Multiple Projects and Activities)


  • I'm wondering if I could use the power of Microsoft Project to help with managing my department.

    We have several (10-20) projects that the team (10 people) work on over the course of the year.  Many of the projects are done in parallel, some with all of the team and some with only a subset of the team.  We also have regular, ongoing activities like meetings, support, and administration.

    At the beginning of the year, I need to estimate how much work we can do based on the long list of project requests, and then each month update the forecast for the rest of the year based on what we did the previous month.  I don't need it broken down to the hour, but a fairly reasonable estimate of project timelines.

    I've attempted to do this in Excel and then in MSP.  Excel tends to be very tedious and time consuming, so it's not worth it.  MSP is better, but still not quite right.  I think maybe I'm not setting it up right.

    Has anyone done something similar within MS Project?

    Monday, February 11, 2013 10:37 PM

All replies

  • MikelBee --
    What you aim for can certainly be done in Microsoft Project 2010 or 2013, as opposed to Microsoft Excel.  Here is what you would need to do:
    • Create a shared resource pool file that lists all of your team members who might work in any project.
    • Create each of those 10-20 projects which you are requested to do and then connect each of them to the shared resource pool file.
    • Completely plan the tasks in each project, including setting task dependencies and setting a Duration value for each task.
    • For each task in each project, assign the resources needed to do the task.
    • Insert all 10-20 projects in a master project so that you can see the total volume of task work across all projects.
    • Use the Resource Usage view in the master project to determine the utilization and remaining availability for each resource.  In this special view, you can quickly see where you have overallocations, either within a single project or across multiple projects.
    • Use the Resource Usage view to level resource overallocations across projects to determine more realistic finish dates for each project.
    Above is the broad overview of what you will need to do to make it happen.  I want to warn you, however, that Microsoft Project is NOT an intuitive tool.  If you have no experience with this software, I would strongly recommend that you take a training class from somehow who knows how to use the software in the real world, as opposed to an instructor who read it out of a book an hour before class.  If you are interested in books or online self-paced training class, refer to our company web site at:
    Hope this helps.

    Dale A. Howard [MVP]
    VP of Educational Services
    "We write the books on Project Server"

    Tuesday, February 12, 2013 12:53 AM
  • Thank you for your response.

    I am fairly experienced at both project management (have PMP) and MS Project.  And maybe that's why I'm struggling with this.

    What I don't want to do, and in fact can't do, is break the projects down to that level at the "portfolio" planning stage.  I have to make broad guesstimates as to the amount of time each of the dept staff will work on each of the different projects.  I have found I have a good idea on how much work effort based on experience.  This is how our company operates, so I do the best I can.

    That is why I must update the plan each month, or really when we get to the actual work of the project, where we can do the detailed planning work.  The guesstimates become more exact, and based on the resource demands, I can adjust the other projects in the portfolio.

    I just can't believe that there are no off the shelf programs that do this, as it seems that it is a common task of department managers at many companies.  And that's why I try to do it with the tool I'm most comfortable with, MS Project.

    Tuesday, February 12, 2013 2:41 AM
  • Mike --
    In your situation, why not plan projects at a very high level until you need to plan the detailed work?  Just a thought.  Hope this helps.

    Dale A. Howard [MVP]
    VP of Educational Services
    "We write the books on Project Server"

    Tuesday, February 12, 2013 1:07 PM
  • I agree with Dale. Excel will do just as well and probably much quicker at the high level.

    There isn't an off the shelf solution because everyone wants to put different things on that "shelf" and there isn't one "shelf" that fits enough needs to make it worthwhile. That's why you have tools like Project Server and others that are highly customisable so as to fit as many organisational needs as possible. However the process of producing your "shelf is long and requires people, processes and tools. So push Excel as far as it will go. The next step involves a lot of work to go another level of detail down.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Wednesday, February 13, 2013 8:02 AM