Hi, I manage a small team and each of my direct reports owns around 8 projects at any given time. Projects vary in duration but are generally around 3 months long. They start/end at different times throughout the year, and as they drop off, I assign more. Project information is housed in a SharePoint task list and includes basic info like project start date, target end date, actual end date, project status (active, complete, on hold, etc), and who owns the project. Each of my direct reports' current workload is simply the sum of how many active projects he or she has at any given time.


I have an Excel file that syncs to the SharePoint task list where all project information is entered and housed. I've already created a great dashboard using pivot tables/charts to show current workload per direct report (simply the sum of his/her active projects), current workload for my team (simply sum of all my team's active projects), and some other basic charts.

To improve forecasting and my ability to distribute projects evenly to my direct reports, I'd like to create a simple pivot chart line graph that shows projected workload for each direct report, say, for the next sixth months. For example, John Doe currently has 8 projects, all ending at different times over the next several months. I need to assign him more, based on what he can handle. I just want to see what his workload looks like, for example, if I assign him a 3 month project starting 10/1/15, a 2 month project starting 10/15/15, and a 4 month project starting 10/25. The good thing is all the projects are in a queue months in advance, and I know their target start and end dates. It's really just about figuring out how to best assign them to my team.

So, I just want to look at a simple linegraph and see what John Doe's workload looks like week by week for the next, say, six months. I don't need a Gantt chart showing each project's start/end date, rather a simple line graph with his projected workload by all weeks for the next six months.

The hard part is that I don't know how to calculate varying workload as projects start/end. Could some one point me in the right direction? Thanks!