« Lost and Found - Struggling | Main | Free PMBOK Download. »

Peanut Butter Sandwich Scheduling

Recently I questioned if it is possible to make scheduling tools and processes "friendly" so they would not be resisted. Today I'm going to show one easy way to do this using MS Project and Excel.

The analogy here is that the technique I'm going to show is about as simple as you can get. As far as project management utility is concerned it is the equivalent of the peanut butter sandwich. Filling and somewhat nutritious but most importantly easy to fix and acceptable by a large number of finicky eaters. You can draw conclusions about how project server is a daily dose of brussels sprouts if you desire, but I didn't say that.

Anyway the basic principle is this. The easiest way to tell when you are going to get somewhere is to measure how far from the finish line you are. Measuring how far from the starting line is less useful because you might not be heading directly towards the finish. You may have just gone in a circle for the past month. So the best indicator of when you are going to finish is based on the remaining tasks. In this case we are going to go the whitebread route and even ignore work (The HORROR!). You can add it quite simply if you like.

Now, a nutritionist will insist that you measure calories and weigh your portions, but in this case we can figure that out later. It is trivial to determine this to the first order by counting the number of people working on the project and then multiplying how long the project is. We don't need to spend a lot of time doing this when we are just trying to run out the door.

So what is this technique? Here are the basic steps.

  • Build a schedule model. Do all the right things here - limit constraints, use dependencies to sequence activities. Get the durations right and keep the durations to a week or three.
  • Save a baseline. This is your starting point, your map so to speak. There is an additional step here I'll explain later.
  • Work for a week.
  • Adjust the Remaining Duration on all of the tasks to match your current projections. If the task is done then set remaining duration to 0. Start with in progress tasks and then selectively check any forcast tasks that might need adjusting based on what you have learned doing the week.
  • Set the status date to the appropriate reporting date.
  • Click the magic "Update Project" button. DID HE REALLY SAY THAT?!!
  • Export current and new forecast data to Excel using a macro. The previous two steps can be combined into this as a single button click.
  • Work some more and repeat.

    Now it can't be that easy. Didn't I just cheat? How does this tell me how my project is doing? The answer is that well, it doesn't. This part is just the bread. You need to add peanut butter. Let's let Excel=peanut butter and use a bit of VBA as the knife.

    First the VBA. What we want to do with the VBA is to get certain data out of the project so we can see it in excel. You can do all these steps manually, but it is a bit repetitive and so a macro is easier. I'm keeping it as simple as possible. Here is the code. Lines beginning with an apostrophe are comments and are not executed.

    Sub burnDown()
    Dim t As Task
    Dim remDur As Long
    Dim stat As Date
    Dim xlRow As Excel.Range

    Set xlApp = New Excel.Application
    xlApp.Visible = True
    AppActivate "Microsoft Excel"

    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets.Add
    xlSheet.Name = "foo"
    Set xlRow = xlApp.ActiveCell

    remDur = 0
    stat = ActiveProject.StatusDate
    With ActiveProject
    numweeks = (.ProjectFinish - .ProjectStart) \ 7 + 1
    End With
    For i = 0 To numweeks
    UpdateProject All:=True, UpdateDate:=stat, Action:=1
    For Each t In ActiveProject.Tasks
    If Not t Is Nothing Then
    If Not t.summary Then
    If t.Flag1 Then
    remDur = remDur + t.RemainingDuration
    End If
    End If
    End If
    Next t
    remDur = remDur / (ActiveProject.HoursPerWeek * 60)
    xlRow.Value = stat
    Set xlRow = xlRow.Offset(0, 1)
    xlRow.Value = remDur
    Set xlRow = xlRow.Offset(1, -1)
    stat = stat + 7
    Next i
    End Sub

    What this does is export the remaining duration to excel. It steps through the project week by week and recalculates what would be remaining if all went well. Then when you have this data in hand you paste it into excel in the proper place for your chart. Let's move onto the chart now.

    The chart has three data series which are interesting. The first is the baseline. This is your plan. We are always measuring against the plan. Note that the baseline remaining work goes to zero at a certain point in time. When there is no remaining work you are done. The intersection between baseline and the x axis is your finish date. This baseline gives you some important feedback. What shape is it? If is is fairly flat that indicates that you expect to make steady progress. If there is a steep drop somewhere can you explain how the people you have working on this are going to accomplish that? Is it the result of multiple tasks going on at the same time? If so, how are you going to get that all done in a week? Are there appropriate flat spots where holidays or vacations are going to interrupt work? This is a first check on your schedule model. If you take the next step and use work rather than simple remaining duration you will get even better visibility. If the curve of the baseline is acceptable and understandable then go on. If it looks wierd, then check your model.

    The next important series is what I call the "Actual" remaining duration. It is the remaining duration at the date of the report. Each week you add another data point to this series. This is important because it tells you how you actually performed. By looking at the difference between remaining duration from last week and the current week you can see how much closer you have gotten to your goal. Sometimes you may have MORE remaining duration. This can happen if tasks are added or re-estimated. Now you will say that people will always say that a task is on track until the end, at which time they say that it will be done "next week". You have a point there. However, the tasks in your model should be a week to three long, so in the worst case it will just be a couple of weeks before you see remaining duration go flat. In the best case you see it the same week you are reporting. It can't hide for very long. At this point eyeball the chart. How far are you from baseline? If the two are diverging is the gap getting wider? If so, draw a line connecting the "Actual" remaining duration datapoints that you have. Extend it down and to the right until it hits the x axis. That is about when you will finish if you don't fix things. If it never hits the x axis going forward, you should probably call for help.

    The third series on the chart is one which the macro derives for you. It shows your current forecast of remaining duration. If your "Actual" remaining duration is trending later then baseline then this series is probably your best case prediction. If you are under baseline, then you can reasonable expect to do a bit better than this (barring the case in which you have been ditching tasks each week) The important thing to do here is to make sure it is not unrealistic. Compare it to the baseline and eyeball it against your "Actual" line. If it is similar then you can feel confident. If it doesn't have a family resemblence to the baseline or your "Actual" line and if it drops like a rock just before the target finish date you are lying to yourself. It is too good to be true.

    How does it match up with that line you extrapolated from the "Actual" data? Some variation is OK. The best estimate for when you finish is likely to be somewhere between where the two lines intersect the x axis, the point where no work remains to be done.

    So you can see, this technique quickly and easily projects a finish. Project itself gives you a single projected finish date without going through the additional steps of exporting and charting the data, but the value here is that the series on the chart show you whether your plan is realistic. They show you your weekly progress in a very simple form. They give you a trend which can be used to make a projection. They give you a forecast which you can compare in a glance with both your original plan and your actual progress. For the amount of time spent, this is the most efficient schedule forecast method I can think of.

    Now once you have graduated from soft foods and want to try something more exotic it is simple to extend this indicator. Assign resources to the tasks and track work rather than duration. This gives an incremental improvement in the accuracy. Primarily it has the effect of straightening out the lines. Want a deeper look into the workings of the project? Use an outline code or text field to categorize specific tasks in your schedule and extend the code to produce a series for each of the categories of tasks. Want a bit more detail in the historical data? Instead of just updating the remaining work, update the actual work too. This are incremental steps you can take when you and your team are ready. Steps you can take if you find that additional data to be useful. Sometimes a peanut butter sandwich is all you need though.


    Comments (4)


    I tried copying this VBA code into Project 2007 and I'm getting an error code.

    "Compile Error: User-defined type not defined" and part of the code is highlighted: "xlRow As Excel.Range".

    Can you please help me to understand why this may be happening and what I can do to fix it? This type of scheduling report would be very beneficial.


    In the Project Visual Basic Editor (VBE) go to the tools menu, select "references" and make sure that the Excel library check box is checked. - Jack

    John Gillen:

    When I run this in Project Standard 2003, I get an error message "Run time error 13: type mismatch" referencing the line stat = ActiveProject.StatusDate.

    Any ideas?



    In Project, go to tools->project properties and set the status date.


    Hi i got the same error as the first comment

    "Compile Error: User-defined type not defined" and part of the code is highlighted: "xlRow As Excel.Range".

    tried to change the reference but it the option was not highlighted and would not allow me to open references.?

    Anyone help.?

    It won't let you change references until you are out of debug mode. Click the button with a square on it to stop execution, then with the module open go to the tools menu / references and set the reference. Save. Then it should work.


    Post a comment

    (Comments are moderated to fight SPAM and will be published after I have a chance to approve them. Thanks for waiting.)


    The previous article is Lost and Found - Struggling.

    The next article is Free PMBOK Download..

    Current articles are in the main index page and you can find a complete list of articles in the archives.

    Creative Commons License
    This weblog is licensed under a Creative Commons License.
    Powered by
    Movable Type 3.34