Building contingency into project plans

matttheba

What is the best way to manage tasks that have a high degree of uncertainty in the duration? An example might be resolving a design problem – sometimes they are quick, sometimes they take a while.

A previous software we used, allowed you to put in a best case and worst-case estimate for each task – effectively adding contingency for each task depending on the level of uncertainty for each one. The software then added all these little contingencies together and added a portion of this (default was 50%) to the end of the project as a contingency buffer.

 

The benefit of this was that you had a very accurate handle on how much contingency you needed, and you could track how much of this contingency buffer you had used up at any stage of the project and therefore, what the likelihood of hitting your target date was.

 

In R&D a lot of our tasks have a high level of uncertainty so giving a single date is tricky unless we just go conservative all the way, which is valid, but then people freak out that the project is too long.

Just_Chris

Hey : )

You could add a sort of timeline risk column which adds a buffer to the planned end of your projects - here a quick view of what it could look like

Just_Chris

Hey : )

You could add a sort of timeline risk column which adds a buffer to the planned end of your projects - here a quick view of what it could look like

matttheba

Great idea! Thanks Chris!

tplavac

I love this idea, but can anyone help me with the formula required to do it? I am a newbie with formulas 😐️

Genevieve P.

Hey @tplavac

Could you post a screen capture of your specific sheet set-up, explaining what you're looking to do? (But please block out sensitive data)

Cheers,

Genevieve

tplavac

Hi @Genevieve P.

I want to know how to multiply a date times a risk factor like the photo above in this thread. (picture below).

I assume to do this, you need to add formulaes?

Thanks!

Genevieve P.

Hi @tplavac

Yes, you're right! If your sheet has the same column names and set-up as this sheet, then you could write a Nested IF statement to combine all of the statements in the Days + Buffer column, like so:

=IF([Time Risk]@row = "Zero", [Plan Days]@row * 1, IF([Time Risk]@row = "Low", [Plan Days]@row * 1.25, IF([Time Risk]@row = "Med", [Plan Days]@row * 1.5, IF([Time Risk]@row = "High", [Plan Days]@row * 2))))

Then the last column is simply multiplying that cell by 4. Does that make sense?

tplavac

@Genevieve P. Thank you!

It's not quite working for me, but I am trying to figure it out. It does make sense, but I am getting an error of Unparseable. I used the duration column I had for the [Plan Days] column above.

Genevieve P.

Hi @tplavac

Can you post a screen capture of the formula open in your sheet?

tplavac

@Genevieve P. Sure, here it is.

Genevieve P.

Hi @tplavac

It looks like you have a period instead of a comma in the middle of the formula, right after "1.25"

Try this:

=IF([Time Risk]@row = "Zero", Duration@row * 1, IF([Time Risk]@row = "Low", Duration@row * 1.25, IF([Time Risk]@row = "Med", Duration@row * 1.5, IF([Time Risk]@row = "High", Duration@row * 2))))

Note that you don't need [square brackets] around the word Duration because there are no spaces in that column name 🙂

tplavac

https://community.smartsheet.com/discussion/comment/357457#Comment_357457

Thank you SO much for the help! I'm trying to learn and understand project management formulas. Would you recommend this course below or something else first?

https://smartu.smartsheet.com/plan/formulas-for-project-management

Genevieve P.

Hi @tplavac

Yes!

I would maybe first start with the general Formulas course: https://smartu.smartsheet.com/formula-basics

Then move on to the formulas for project management afterwards. 🙂