Modeling uncertainty: "Expected Value" for staff allocations on future potential projects?

My company is a consulting firm that must manage an uncertain project flow. We use one Smartsheet per project, creating a new project smartsheet whenever we submit a project proposal. However, there is always uncertainty as to whether we will actually get any particular project. We definitely don't have a 100% success rate, so we have a number of proposals for the same time period of work out at any given point. This means that we often have staff members appear as "over-allocated" in a future quarter.

What I would like is some sort of "expected value" function that would allow me to flag the allocations from different project sheets based on the percentage likelihood that the project comes to fruition. (E.g., If I think a project has 25% likelihood of going through, the 100% allocation of key staff during that project period would appear as 25% when I look at the resource view. This would give us a more realistic picture of future staff allocations and needs.

Has anyone had success with this or another approach to build uncertainty into their project planning with Smartsheet?

Thanks in advance!

Find more posts tagged with

Sheets

Formulas

Enhancement Request

Comments

J. Craig Williams

Kerry,

This a really interesting problem and I like your solution.

Here's what I did for my team.

I would not let any PM use a real person's name in their proposed schedule.

Instead, I had three fake engineers added to the users of Smartsheet, so the could be assigned to work and show up in the resource loading.

I only needed three because we didn't have lot of more projects coming in at once (potentially), and I assigned each of the three a skill set. If Engineer01 looked overbooked in Q4 but I had other engineers coming off other projects around that time, with that skill set, we were likely OK.

Weekly or as needed we would look at the resource loading of the team and see if there was a trough or wave coming (potentially or not).

Once the project was won, we'd assign real engineers and move on.

As the award date approached, we'd of course pay closer attention to the potential of the win. In my industry, a new hire wasn't typically fully self sufficient for at least six months. So we'd try to account for the steep learning curve during schedule baselines. That was part of the uncertainty too.

Hope this helps.

Craig

Kerry

Thanks for the quick response and sharing of experience, Craig! That solution makes a lot of sense. I think for us it's still an incomplete solution because we are dealing with a very small ~8 person team with varying degrees of skill overlap, so it's a little harder to swap in and out!

BUT, I actually just tried a work-around that worked and might be useful for people.

In our "pipeline" projects, I added anew column for "Percentage likelihood". Then I added a new column called "Expected Value Allocation", with cells set to == "Allocation %" (our typical resource management column)* "Likelihood". Then, for any pipeline projects, I have changed which of the columns is referenced for our resource management from "Allocation %" to "Expected Value Allocation".

Of course this is still a bit of effort, but now the likelihood of staff being need can be incorporated to make a more realistic view of future staffing needs.

And, of course, once a pipeline project is either won or lost, it will be crucial to update resource management settings and pull from the correct column.

J. Craig Williams

Kerry,

If I understand things, you have a column of "Expected Value Allocation" and a column "Allocation %".

For a potential win, you use "Expected Value Allocation", where "Expected Value Allocation" is calculated from "Allocation %" * "Percentage likelihood", otherwise you use "Allocation %" in other linked sheets.

The switch between whether to use "Allocation %" or "Expected" is manual.

Here's a suggestion to improve your workflow a bit more.

Do the switching on the original sheet.

Easiest might be to set "Percentage likelihood" equal to 100%. Then your expected will be actual. That might be confusing on the other sheets so instead, I would do something like add a column for "Won" (a checkbox) and another for "Allocation if Won" (or use "Expected Value Allocation" since that won't be need anymore)

"Allocation %" then becomes

=IF([Won]23, [Allocation if Won]23, [Percentage likelihood]23 * [Allocation if Won]23)

for row 23.

The switch is done when you check "Won".

You could also conditionally format the row or cell based on whether you have won it or not. Or the "Won" cell could be fed from another sheet.

Hope this makes sense and helps.

If not, I could do a quick mock up and post screen shots.

Craig