Need Resource Deficiency Formula corrected

OS23

Hi there, Im wanting to track resource deficiency on my projects. I'm using Capacity planning templates for smartsheet and the deficiency formula is not giving me a correct formulation. Given that

1. Total project Demand (HRS) = Sum of all hours per week from Level of Effort column
2. Utilization Rate is Level of effort (HRS/Week) / Work Hours per Week *100 = in % (Averaged %)
3. Effective capacity (HRS) = [Total Employees]# * [Work Hours per Week]# * [Utilization Rate (%)]#
4. Potential Work Capacity=[Total Employees]# * [Work Hours per Week]#
5. Resource deficiency formula i used is =ROUNDUP(IF([Total Project Demand (Hrs)]# > [Effective Capacity (Hrs)]#, ([Total Project Demand (Hrs)]# - [Effective Capacity (Hrs)]#) / ([Work Hours per Week]# * [Utilization Rate (%)]#), 0), 0)

If my work capacity (available) is 200 hrs and total projet demand is 100 hrs. Why is the Resource deficiency field showing I need 15 more staffers?

Really appreciate a quick turnaround on this matter. Thanks.

Genevieve P.

Hi @OS23

You're seeing this number because of the Utilization Rate %.

You would need 15 more resources at a rate of 13% utlized in order to meet your 200 hours of work.

It sounds like you would rather see a number based on if the new employees were at an assumed rate of 100% utilized, is that correct? If so, you can adjust the formula like this:

 =ROUNDUP(IF([Total Project Demand (Hrs)]# > [Effective Capacity (Hrs)]#, ([Total Project Demand (Hrs)]# - [Effective Capacity (Hrs)]#) / [Work Hours per Week]#, 0), 0)

Let me know if that makes sense and now works for you!

Cheers,

Genevieve

OS23

Thanks. I am having an issue when my utilization rate is at 100% or above. My formula is giving me 0 deficiency.

Genevieve P.

Hi @OS23

Is that because your Total Project Demand hours are either the same as or less than your Effective Capacity hours?

The first part of the formula is an IF statement where it checks the following:

=ROUNDUP(IF([Total Project Demand (Hrs)]# > [Effective Capacity (Hrs)]#, ([Total Project Demand (Hrs)]# - [Effective Capacity (Hrs)]#) / ([Work Hours per Week]# * [Utilization Rate (%)]#), 0), 0)

So if the Total Project Demand hours is LESS than your Effective Capacity, then you don't have a resource deficiency.

OS23

In my case, it is more.

Genevieve P.

Great! Exactly! 🙂

This means you have 440 hours on demand and 572 possible capacity hours, so you have 0 Resource deficiency, as you have over 100 extra hours that your current resources can provide.

OS23

Sorry for the confusion. the effective capacity hour by functional group shows me less than project demand and the deficiency shows 0. I meant to paste the functional manager view.

Genevieve P.

Hey @OS23

Can you paste the formula that this sheet is using? Is it possible that the > and < are swapped around in this formula?

OS23

Sure. here you go!

Genevieve P.

Hi @OS23

This formula is still looking at the [Total Project Demand (Hrs)] etc, when it sounds like you want to be referencing your new fields instead.

Change out the references and it should work for you!

 =ROUNDUP(IF([Wicker Project Demand (Hrs)]# > [Wicker Effective Capacity (Hrs)]#, ([Wicker Project Demand (Hrs)]# - [Wicker Effective Capacity (Hrs)]#) / [Wicker Work Hours per Week]#, 0), 0)

OS23

Thanks. I corrected that. But I still get 0 deficiency when my effective capacity is more than project demand. see below:

Formula used for resource deficiency: =ROUNDUP(IF([Gasbarro Project Demand (HRS)]# > [Gasbarro Effective Capacity (HRS)]#, ([Gasbarro Project Demand (HRS)]# - [Gasbarro Effective Capacity (HRS)]#) / ([Work Hours per Week]# * [Gasbarro Utilization Rate (%)]#), 0), 0)

Genevieve P.

Hi @OS23

This looks like it's working as expected!

• Your project demands 120 hours.
• You have a capacity of 180 hours.
• This means you don't need any extra resources, as your current resources could give 60 more hours if needed!

This is why you see 0 for Resource Deficiency, since you don't need any extra resources to get this done. Does that make sense?

OS23

You are correct.. thank you for breaking it down as I keep reading my numbers wrong. Thank you so much for the help.

Genevieve P.

Haha no problem at all! Formulas and logic structure can be confusing, I'm glad I could help.

I hope you have a good week!

Cheers,

Genevieve