Apportion Values on addition of resource

[Thomas Kuriakose]

Respected Sirs,

Please find attached a resource calculation sheet based on number of resources and the time available for each resource.

We need to change the utilization of resource by addition of a resource in row A8. This should apportion the values in utilization of man hour cells from C17 to I25.

Kindly help how to resolve this, currently when we add a resource all the values are increasing.

Thank you so much,

with regards,
thomas

 

[Shteven]

What is your expected results? The way your table is setup, adding a resource will add more total hours per year, which will increase the amount of Hours Per LE. We will need to know more to solve your issue.

 

[Thomas Kuriakose]

Respected Sir,

Sorry, if I have not clarified the requirements correctly.

1. L10= 6176 hours is the total available man-hour based on the resources.
2. There are 7 activities to be carried out by these 4 resources, with the highest priority on PI activity and the second highest on P7 activity.
3. The other activities are of lesser priority.
4. Based on the resources and activity percentage values, the hours per LE and total number of units are arrived at.
5. The possibilities of addition of a resource and the corresponding changes of the activity and hours per LE needs to be derived.

Thank you very much,

with regards,
thomas

 

[Shteven]

I'm sorry Thomas, I'm still having trouble understanding your goal. Can you answer these questions for me?
1) If you add a resource, why/how should the Activity Percentage change?
2) Does LE just mean Resource? If so, why is cell H17 currently allocating Hours Per Day by 3 Resources instead of 4?
3) Your formula in I17 is basically Units = Activity Percentage * 720, meaning that 720 is the total units at 100% Activity. Is 720 Units a constant, or are you looking to produce more Units by adding more Resources?

Cheers.

 

[Thomas Kuriakose]

Respected Sir,

Apologies

1) If you add a resource, why/how should the Activity Percentage change? - There are two priority activities P1 & P7 which consume time and are very important to the process, the other activities are of lesser importance and the addition of resource should decrease the percentage proportionately with primary emphasis on P1 and P7 respectively.
2) Does LE just mean Resource? If so, why is cell H17 currently allocating Hours Per Day by 3 Resources instead of 4? - This was a typographical error, we tried to simulate with different resources and the division value was not changed. The denominator should be 4.
3) Your formula in I17 is basically Units = Activity Percentage * 720, meaning that 720 is the total units at 100% Activity. Is 720 Units a constant, or are you looking to produce more Units by adding more Resources? - Yes if the number of resources are directly proportional to the number of units produced.

Thanks you very much,

with regards,
thomas
thomas

 

[Shteven]

OK! The picture is getting clearer now, thank you for explaining.

Are there any other constant values in this calculation that you haven't told us yet? I may be missing something, but I don't see how you can have a changing Allocation Percentage that the rest of your table relies on. Some ideas I had:
Total Units per Resource per Year
Priority Percentage (different from Allocation Percentage)
Time needed to complete each Activity
etc.

 

[Thomas Kuriakose]

Respected Sir,

There are no constants with respect to the percentage allocation, units per resource and time as each activity is variable.

However to get started, kindly find attached the sheet with fixed percentages with priority on activity P1 and P7 with 35% and 20% respectively.

Alternately we can have 3 scenarios -

1) Fix Total Units per resource by altering all other parameters
2) Fix Time for completion of each activity by altering all parameters
3) Fix percentages by altering all parameters.

We need to check how the values vary if an additional resource is included.

Thank you very much,

with regards,
thomas

 

[Shteven]

Previously, I noted that your Units total is 720; that was for 3 Resources, so 240 Units per Resource. Adding a resource with 1544 Usable Man Hours will automatically increase Units by 240, and removing a resource decreases Units by 240. This is because your Units formula is (5 days) * (4 weeks) * (12 months).

If you say that Units produced will be variable, even with Resources and Hours changing, then we will need to understand more to come up with the correct Units formula. What else can you tell us about Units production? Should Units for all Activities be equal for the end of the year (P1 total = P2 = P3 etc.)? Is there a ratio we should consider (Example: You might need 2 P4s for every P1) ?