Need Resource Deficiency Formula corrected
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
 Total project Demand (HRS) = Sum of all hours per week from Level of Effort column
 Utilization Rate is Level of effort (HRS/Week) / Work Hours per Week *100 = in % (Averaged %)
 Effective capacity (HRS) = [Total Employees]# * [Work Hours per Week]# * [Utilization Rate (%)]#
 Potential Work Capacity=[Total Employees]# * [Work Hours per Week]#
 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.
Answers

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

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

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.

In my case, it is more.

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.

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.

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

Sure. here you go!

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)

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)

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?

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

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
Help Article Resources
Categories
Check out the Formula Handbook template!