Need Resource Deficiency Formula corrected

OS23
OS23 ✭✭
edited 02/07/24 in Formulas and Functions

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.


Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin

    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
    OS23 ✭✭

    In my case, it is more.


  • Genevieve P.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin

    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
    OS23 ✭✭

    Sure. here you go!


  • Genevieve P.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!