Resource Management: Can we roll up utilization to the group level?

Chad Neufeld

In order for us to justify getting our whole team into Smartsheet, we need to be able to measure utilization. Is there a way to roll up utilization to the group level, in the same way that we can roll up utilization to the user level? Essentially, the goal is to know how much our utilization for a group is for a specific time period (ie. the studio is at 95% utilization for the month of June, but the project management team is only at 75% utilization). 

If there isn't a native way to do this within resource management, are there any reports/API integrations that anyone is using that will help me kick out these numbers?  

Thanks internet friends!!

J. Craig Williams

Eagerly awaiting the rest of the Community.

I have had to build these myself.

 

(but you don't want to get me started on utilization - or rather, I don't want to get started on utilization, I'll lose the whole day writing a post about it

- "don't type angry" - Craig Williams)

 

Craig

Chad Neufeld

From Samuel at Smartsheet Support:

"We don’t have a method to show the resource allocation of the Group as a total. In the resource view you would need to expand the Group and see the individuals. I will add your vote for this to be considered in future development."

This seems absolutely crazy to me. How was that not a critical deliverable when they built out Resource Views? Isn't the ability to decide whether to bring in more resources the single most important deliverable when looking at resourcing?

Really hoping other members have solutions that are somewhat elegant. 

Thanks!

Richard Rymill SBP

Chad

I think the simple answer to what you ask is that within Smartsheet as it is, you can only get so far, but that is easily achieved at no additional cost. 

My colleagues JamesR and CraigW have built solutions for specific clients to achieve what you seek using a range of methodology suitable to the user, but it takes time to set it up and costs money, so the Pain needs to be worth the Gain from your point of view., But the optimum use of Resources can produce an excellent ROI. 

 

I am happy to set up a free consultation with you to explore a costed solutiuon if that is what you seek. 

 

Regards

 

RichardR

Chad Neufeld

Thanks Richard, I appreciate you connecting. I've built out a series of Excel formulas that will calculate what we're looking for from an export, but it just seems so strange that this functionality isn't baked into the tool.

If I can calculate the numbers I'm looking for using pretty basic formulas, then there's probably other utilization-focussed apps that should be able to integrate into Smartsheet and do the same, but less manually, right?

J. Craig Williams

Chad,

 

If you can do it in Excel, it can probably be done in Smartsheet.

The time spent exporting and massaging the data is spent on the setup in Smartsheet.

 

I avoid third party solutions if at all possible, but sometimes they are the right solution.

 

The issue for resources is that they are an aggregate of data from sheets that are enabled to provide the data. And just like reports, once the data is aggegated, we (the Smartsheet end user) can't  manipulate it.

Which mean we have to aggregate it ourselves.

Once the process is in place (for every task list and schedule) then the task becomes trivial for new projects.

 

Craig

Chad Neufeld

Hey Craig,

Do you have some tips about building that aggregation process? Below is the kind of thing we need to be able to set up within Smartsheet. Because I can't manipulate data in Reports, as you mentioned, I can't get my head around how to do this without requiring exports.

Any tips would be very greatly appreciated. 

J. Craig Williams

Chad,

 

First, devote a whole bunch of rows at the top of each project sheet to the task.

 

Second, you'll need a column for back calculating the task effort (based on duration * allocation)

 

Third, use COUNTIFS and SUMIFS to cross reference the person with the effort.

 

It is going to get pretty ugly pretty quickly.

I was looking at gross values, not down to the day like you are.

 

What I was thinking was Joe is working 100 hours on all projects this month and Joe is a member of the software team, of which there are 10 members.

And so on.

 

You are approaching the problem from a different angle than I am, so I'll need to think about it some more. My solution won't work without modification.

 

Have you looked Groups and using them in the Resource View? It doesn't give you the hard values you are looking for, but it can give you a good feel for the peaks and valleys of the loading.

 

Not much help, I know.

If something occurs to me, I'll be back.

 

Craig

 

 

Chad Neufeld

Craig,

 

I really appreciate the thought you've put in.

And you're totally right, looking at Groups in the resource view does give you a snapshot of who is over utilized and who is light, but it doesn't give me the kind of information that would allow me to see 2, 3, 6 weeks out that I need to schedule a contractor for a specific team for a period of time and be able to justify that to management. 

I contine to appreciate your thoughts on the topic!

J. Craig Williams

I'll keep thinking about it.

Some problems won't go away until they are solved.

 

I've got solutions that calculate EV, SV, CV, and the like.

I need to go back to them and see if there is something to be drawn out.

 

Craig

nancy_phillippi

New to SS and I can't believe that after 3 years since this was posted we still can't rollup groups.  Super disappointed.