Rule of Thumb

Organizational changes are natural part of projects. By definition, projects are changing the state of things and almost always, this includes processes and procedures and consequently how people do things in their day-to-day business.

There are many theories an models but Kübler-Ross model applied to change management is fundamental one.

In its original form, Elisabeth Kübler-Ross wrote in her 1969 book “On Death and Dying” about the “Five Stages of Grief”:

• Denial: Example – “I feel fine.”; “This can’t be happening.”‘Not to me!”
• Anger: Example – “Why me? It’s not fair!” “NO! NO! How can you accept this!”
• Bargaining: Example – “Just let me live to see my children graduate.”; “I’ll do anything, can’t you stretch it out? A few more years.”
• Depression: Example – “I’m so sad, why bother with anything?”; “I’m going to die . . . What’s the point?”
• Acceptance: Example – “It’s going to be OK.”; “I can’t fight it, I may as well prepare for it.”

• Denial: Example – “This isn’t relevant to my work.”
• Resistance: Example – “I’m not having this.”
• Exploration: Example – “Could this work for me?”
• Hope: Example – “I can see how I make this work for me.”
• Commitment: Example – “This works for me and my colleagues.”

This happens to all of us. It is important to understand, that this is a natural reaction and should never be taken personally. Project manager should avoid at all costs bringing this conflict to a personal level as it makes it difficult or impossible to recover from it later.

It is important that project manager keep the team involved during denial and resistance phases, especially trying to understand their current position. Translated into project management, this is the time to do requirements analysis. This can open the vents of piled up unresolved issues and problems.

Tipping point is where negotiation starts and scope crepe occurs. Project manager really needs to be tough in following the goals and objectives.

Exploration is where training can occur. People in exploration phase accepted that the change is imminent and are gaining interest in it. It is futile to do any training before this stage.

When in hope stage are empowered to accept the change. They’re comfortable doing user testing and self exploration.

During commitment phase, they’re believers. They know why this is good for them and the team. Now is the time to write new processes and procedures.

I saw a movie Changing Lanes (2002) the other day and these words kind of resonated with me:

God grant me the serenity to accept the things I cannot change,  courage to change the things I can, and wisdom to know the difference!

Think about it.

PMI has recently conducted a research (Researching the Value of Project Management) on whether what they’re doing is of any use and surprisingly found out that there is an added value in project management.

I think this conclusively proves I need a raise.

I found this interesting criteria list on PM Hut to consider when deciding about project priorities:

• Urgency - When must this initiative be complete?
• Alignment – How well does this initiative supports the Organization’s Goals and Objectives?
• Productivity – How much will this initiative increase productivity?
• Cost Savings – How much cost will this initiative save over the next 3 years?
• Income Increases – How much with this initiative increase income?
• Morale – What is this initiatives morale improvement potential?
• Time To Complete – How long will it take to complete this initiative once started?
• Competitive Position – How will this initiative improve competitive position?
• Customer Service Levels – How will this initiative improve customer service levels?

When running tens of projects in parallel, you’re sure to face colissions. It is usually about resources, of course. How to resolve them? Man in charge might feel what needs to be done in such cases, but it’s not easy to communicate the decision without a proper explanation as every project manager, resource manager and business manager has his or her own view. This is where decision support systems come into play and Analytic Hierarchy Process (AHP) as defined by Thomas L. Saaty in the 1970s is one them.

AHP is a typical divide and conquer solution to tackling complexity. The idea is to divide a complex decision into smaller, manageable evaluations, which are then recombined into the final metric, which helps you make a decision.

This is not a one way process. AHP might propose a different solution than you expect and it enables you to backtrack the calculation to help you understand the model by which the solution was proposed. You can then either agree with the model or correct it, to better reflect the real situation.

As written on Wikipedia, AHP provides a comprehensive and rational framework for structuring a problem, for representing and quantifying its elements, for relating those elements to overall goals, and for evaluating alternative solutions.

There’s an excellent explanation with a step-by-step example on Wikipedia. Another one can be found on Microsoft site.

An interesting article appears on Robust Decission: Why pairwise comparisons are a waste of time for finding criteria importance. David G. Ullman presents a faster approach, especially usefull with more criteria.

Following our constant resource issues I’ve been Googling around for best practices in this area and stumbled across Michael Greer’s article What’s Project Portfolio Management (PPM) & Why Should Project Managers Care About It?.

He proposes a self assessment check list, to see whether you’re ripe for establishing Project Portfolio Management process in your company. It goes like this:

• Frequent difficulty finding enough people to put together a solid project team.
• Excessive project delays due to “not enough resources”.
• High turnover due to “burn out” of key project contributors because they are working on too many projects and spending too many overtime hours.
• Frequent change of status of projects (i.e., moving from “active” to “on hold” to “top priority” and back).
• Completion of projects that, when all is said and done, don’t really meet a strategic need.
• Intense competition, rather than cooperation, among departments and sub-organizations when staffing and funding projects.

It’s all more or less true for our organization. It’s not extreme yet, but something needs to be done before we get into a gridlock.

Solution? Start with documentation, says Michael Greer:

• Create “high resolution” project plans that accurately spell out, in vivid detail, the resources required to complete each task and activity.
• Capture the actual hours spent by all project players in completing project tasks and activities.
• Create summary tables showing planned and actual time spent by each person in your organization on every project to which he or she is assigned in order to demonstrate who’s overloaded.
• Document all incidents of resources that are “stolen” across projects, excessive overtime, large-effort-but-ultimately-useless projects, and so on.
• By conducting project “post mortem” evaluations, gather information about how systematic PPM might have prevented problems and encouraged successes.

As description on Brighton-Webs beautifully explains:

The Triangular Distribution is typically used as a subjective description of a population for which there is only limited sample data.  It is based on a knowledge of the minimum and maximum and an inspired guess as to what the modal value might be.  Despite being a simplistic description of a population, it is a very useful distribution for modeling processes where the relationship between variables is known, but data is scarce (possibly because of the high cost of collection).

Data about task durations is scarce and usually difficult to collect. Tasks are rarely the same as projects are by definition never the same. So why complicate with difficult distributions like Beta, when Triangular is just as good. Most of the error will come from distribution parameters and not its shape.

Probability density function of Triangular Distribution (source: Wikipedia)

Triangular distribution is completely described by minimum (a), maximum (b) and most likley (c) value (mode). Get all the formulas on Brighton-Webs or Wikipedia.

How do we get these three points? Is minimum equal to optimistic or best case? Is maximum really pessimistic enough? Is worst case really the worst? Are we really so good, that we can provide best case and worst case estimates for ALL tasks? I haven’t seen this yet.

Steve McConnell asks the reader of his book Software Estimation: Demystifying the Black Art: How Good an Estimator Are You? He presents the reader with ten quiz like questions:

• Surface temperature of the Sun,
• Latitude of Shanghai,
• Area of the Asian continent,
• The year of Alexander the Great’s birth,
• Total value of U.S. currency in circulation in 2004,
• Total volume of the Great Lakes,
• Wordwide box office receipts for the movie Titanic,
• Total length of the coastline of the Pacific Ocean,
• Number of book titles published in the U.S. since 1776 and
• Heaviest blue whale ever recorded.

Then he asks the reader to provide lower and upper bounds for each question, so that there is a 90% chance of including the correct value. Looking at this from other direction this means that out of these 10 questions exactly one answer will fall outside of estimated range.

According to Steve McConnell, most of quiz takers are able to guess 1 to 3 answers (average 2.8), which tells us that there’s no way we can provide 100% accurate bounds of the estimates. This is really worrying.

The good thing is that we can measure this. Just check your last project where you used 3-point estimates and count how many of them hit the bounds. If you didn’t use 3-point estimates, provide them afterwards prefferably for the project you didn’t manage, but without looking at original estimates.

So in the real world, we cannot provide usefull bounds with 100% accuracy, but if we try hard, we may be able to provide 10th and 90th percentiles, which gives us more down to earth 80% accuracy. McConnell claims that if you’re good and try hard enough, you can only go up to 70%, which then leaves you with 15th and 85th percentiles. As said in the prevous paragraph, go and measure your accuracy and use the result in estimating distribution parameteres as described below.

Glen Alleman says that best case – worst case estimates are biased by personal risk avoidance factor and is also proposing 10th and 90th percentile.

Getting triangular distribution out of mode, lower/upper percentiles requires some number crunching. Luckily, Samuel Kotz & Johan René van Dorp have done this for us in BEYOND BETA: Other Continuous Families of Distributions with Bounded Support and Applications (2004).

You can find implementation of algorithm as part of my Monte Carlo Simulation for MS Project package on SourceForge, or browse the source of TriDist.bas directly.

In the previous post I showed how to designate a field in Microsoft Project to hold cost type information and how to enter this information. Now I’ll show you how to get this information into a nice Microsoft Excel pivot table.

As always, we’ll work with Microsoft Project data stored in Microsoft Access database.

We’ll create two queries in Microsoft Access, first one to get cost types and secons one to combine it with assignments data into final table ready to turn it into pivot table.

To create Task Cost Type query, copy the following query into the Microsoft Access query’s SQL view.

SELECT
    MSP_TEXT_FIELDS.PROJ_ID,
    MSP_TEXT_FIELDS.TEXT_REF_UID AS TASK_UID,
    MSP_TEXT_FIELDS.TEXT_VALUE
FROM
    MSP_TEXT_FIELDS
        INNER JOIN MSP_CONVERSIONS ON
            MSP_TEXT_FIELDS.TEXT_FIELD_ID = MSP_CONVERSIONS.CONV_VALUE
WHERE
    MSP_CONVERSIONS.CONV_STRING="Task Text1";

Cost type, if stored in tasks Text1 field, is stored in MSP_TEXT_FIELDS.TEXT_VALUE, but it has to linked to MSP_CONVERSIONS through MSP_TEXT_FIELDS.TEXT_FIELD_ID = MSP_CONVERSIONS.CONV_VALUE and filtered so that MSP_CONVERSIONS.CONV_STRING="Task Text1".

Next, create a new query, Resource Costs By Cost Type, based on Resource Costs query by left joining Cost Type query.

SELECT
    MSP_PROJECTS.PROJ_NAME AS Project,
    [Task Cost Type].TEXT_VALUE AS [Cost Type],
    [Resource Groups].TEXT_VALUE AS [Resource Group],
    MSP_RESOURCES.RES_NAME AS [Resource Name],
    [Resource Rates].RR_RATE_TABLE AS [Rate Table],
    [Resource Rates].RR_STD_RATE AS [Standard Rate],
    Sum((MSP_ASSIGNMENTS.ASSN_WORK/MSP_PROJECTS.PROJ_OPT_MINUTES_PER_DAY/1000)) AS [Work],
    Sum((MSP_ASSIGNMENTS.ASSN_COST/100)) AS Cost
FROM
    (((MSP_PROJECTS
        INNER JOIN (MSP_ASSIGNMENTS
        INNER JOIN [Resource Rates]
            ON (MSP_ASSIGNMENTS.PROJ_ID = [Resource Rates].PROJ_ID)
            AND (MSP_ASSIGNMENTS.RES_UID = [Resource Rates].RES_UID)
            AND (MSP_ASSIGNMENTS.ASSN_COST_RATE_TABLE = [Resource Rates].RR_RATE_TABLE))
            ON MSP_PROJECTS.PROJ_ID = MSP_ASSIGNMENTS.PROJ_ID)
        INNER JOIN MSP_RESOURCES
            ON (MSP_ASSIGNMENTS.PROJ_ID = MSP_RESOURCES.PROJ_ID)
            AND (MSP_ASSIGNMENTS.RES_UID = MSP_RESOURCES.RES_UID))
        LEFT JOIN [Resource Groups]
            ON MSP_RESOURCES.RES_UID = [Resource Groups].TEXT_REF_UID)
            LEFT JOIN [Task Cost Type]
            ON (MSP_ASSIGNMENTS.PROJ_ID = [Task Cost Type].PROJ_ID)
            AND (MSP_ASSIGNMENTS.TASK_UID = [Task Cost Type].TASK_UID)
GROUP BY
    MSP_PROJECTS.PROJ_NAME,
    [Task Cost Type].TEXT_VALUE,
    [Resource Groups].TEXT_VALUE,
    MSP_RESOURCES.RES_NAME,
    [Resource Rates].RR_RATE_TABLE,
    [Resource Rates].RR_STD_RATE,
    MSP_ASSIGNMENTS.RES_UID
HAVING
    MSP_ASSIGNMENTS.RES_UID>=0
ORDER BY
    MSP_PROJECTS.PROJ_NAME,
    [Resource Groups].TEXT_VALUE,
    MSP_RESOURCES.RES_NAME,
    [Resource Rates].RR_RATE_TABLE;

You can download queries here:

• Cost Type.sql
• Resource Costs by Cost Type.sql

From here, it’s only a matter of creating a query from Microsoft Excel and building a pivot table out of it to suite your needs.

Now that you know how get resource workload and costs out of Microsoft Project, you can go further and add some custom groupings for further slicing and dicing.

In my case, we have two distinct phases on projects:

• feasibility study / project definition phase and
• implementation phase.

We have to prepare and report separate budgets for each phase.

Next, there are different type of costs on the project. They can be booked either as operative expense (OPEX) or capital expense (CAPEX).

These and all other attributes can be configured in Microsoft Project and exported to a nice Microsoft Excel pivot table.

Here’s how. Microsoft Project has 8 groups of customizable fields:

• Cost
• Date
• Duration
• Finish
• Flag
• Number
• Start
• Text

You can use them for whatever you want. By default they’re just numbered, like Text1, Text2, etc. but you can rename them, assign value lists, define default values, define behaviors of summary tasks, define graphical display of the values, etc.

We’ll use Text1 field and turn it into Cost Type with predefined value list:

1. Switch to Gantt View, then select Cost Table from View, Table.
2. Bring up Customize Fields dialog box by right clicking on any of column headings and selecting Customize Fields or through menus Tools, Customize and Fields.
3. Choose Text in the Type dropdown box.
4. Select Text1.
5. Click Rename, type in Cost Type and confirm it by clicking OK.
6. Now click Value List and type OPEX into the Value column of the first row and CAPEX into the second row.
7. You can define default value by checking Use a value from the list as the default entry for the field, selecting default value and clicking Set Default.
8. Confirm the settings for Value List with OK.
9. Click OK to finish field customization.

Field is now defined. You still need to add it to the Cost table.

1. Right click on a column heading, before which you want to add Cost Type field.
2. Select Insert Column.
3. Find Text1 (Cost Type), define optional attributes and confirm with OK.

You can now define each task’s cost type with drop down box in the task sheet or in Task Information dialog box on Custom Fields page.

Getting this new information into Microsoft Excel pivot table will be a subject of the next post.