Driving Duration as a formula from NRE hours and % allocation while keeping dependencies?

Scotty Kawak

Hello,

I am working on a way for my company to view resource allocation and availability based on planned projects that are managed in Smartsheets. Currently, the company uses a basic template for our projects doe duration, predecessor, and enables the default Smartsheet dependencies. To work around the experienced true flow time due to resources rarely being allocated 100% of the time, the schedule uses additional flow in the predecessor to create buffers. This works, but is not the best approach.

To improve the accuracy of our projects and provide visibility for resource availability, I am wanting to add two columns; NRE Hours and % Allocation. The NRE Hours would represent the estimated or planned number of hours the specific task is expected to take a single resource to accomplish. The % allocation would be the percentage of a resources time that would be worked on this task.

I wish to drive the duration for schedule and Gannt chart by the NRE hours and the % allocation.

Example: an 8 hour task at 50% allocation would drive the Duration to be 2 days, assuming an 8 hour work day.

Every attempt I have made at this removes or disrupts the build in dependencies and does not properly drive the schedule. Is this possible to do in Smartsheets or must I drive backwards to calculate the % allocation from the duration and NRE hours?

Brian_Richardson

Hi Scotty - if you are using Dependencies in the sheet then Smartsheet takes over a few columns and prevents you from applying your own formulas to them. That's the % complete, Start, Finish, Duration.

The reason is, these items are rolled up to parent level tasks on the backend, so they don't want you disrupting that process. Also the dates are locked down so that the Predecessors can appropriately affect them. It can be frustrating when you want to do your own work on the values.

You could place NRE next to the Duration and ask your PMs to use it as a gude. You can also leverage lead/lag time in the Predecessors to manage the positioning of tasks relevant to each other in a more fine-grained way than just simple dates. But you won't be able to do formulas.

I also would recommend taking a look at the built-in Resource Heatmap functionality. If you enable Resource Management in the Gantt View project settings, the Resource panel will show you your resources across all sheets that have been enabled, with their capacity. You can edit tasks and view everything in aggregate in one spot. It's pretty nice and it's included.

https://help.smartsheet.com/articles/765727-enabling-dependencies-using-predecessors

https://help.smartsheet.com/articles/2483101-workload-tracking-in-smartsheet

Scotty Kawak

Brian,

To date, the company has never looked at resources in the terms that Smartsheets automatically applies for keeping dependencies functionality. The hours of direct effort are evaluated and the an allocation percentage is applied to determine total flow for any particular task.

Additionally, adding lead/lag time is how we are currently working around this and it is poor at best. This creates complications when trying to pull many projects together to show resource allocation.

The company is not willing to invest more into Smartsheet's Resource allocation tool when it does not function in a manner that supports our current business models.

Brian_Richardson

Ok. Then if you want to turn off the Dependencies you can apply your own formulas to the dates and durations and % complete columns. It's one or the other, unfortunately for your case.

The Resource Heatmap is free…you don't have to invest further to leverage it. If it doesn't work for you, then you don't have to use it. We do use the extended, for-pay, Resource tool for non-traditional resource management (wholly separate internal billing not synced to our project plans). Smartsheet is also working on releasing Resource reporting directly in Smartsheet. But none of that will help your current issue, and it sounds like the standard resourcing model isn't really what you need anyway. Just FYI

Scotty Kawak

I will work on the Resource Heatmap again.

Thank you for the help, Brian.