Resource Planning - How to Model?
 

Please HELP....or tell me if excel cannot handle this....

Hi, I am trying to comeup with a simple solution to plan my team resources. I have 2 teams with different rates/location and a fixed budget.
I have created 4 columns
Team1, Team 2, total hours and Total

Each team will have x number of resources. total worked Hours and Total budget is Fixed (for example) $100000
I need help with the formula so I can increase numbers under team one and it auto adjust team 2 so it matches the total. i.e. I may have 1 full time resource working for 2080 hours (full year) at $10 per hour under team 1..so my team 2 should auto adjust the number required 2.55 resources @ $15/hr for 2080 hours. (so changing team 1 number auto changes team 2 numbers so the total remains constant at 100000)

Any suggestions..I want to play out few scenarios

Thank you,

Resource Planning - How to Model?
 

Anasir -- If I understand you correctly, this is how I'd do it

A B C D E F
G
1 --------Team 1----------<-------Team 2--------
2 Rate Hrs Ttl Rate Hrs Ttl TOTAL
3 a3*b3 f3/d3 g3-c3 $100k

So cell F3 always computes what's left over after you pay team 1, and cell
E3 divides that leftover by your rate (which I assume to be more fixed than
the number of hours). The total, G3, always remains the same.

Or some variation of formulas working back from the total rather than
reaching. Of course, Excel does have goal-seeking 'Solver' functions that
you can ADD-IN and then invoke. They do the same thing, but more
automatically.

"anasir" wrote:


Please HELP....or tell me if excel cannot handle this....

Hi, I am trying to comeup with a simple solution to plan my team
resources. I have 2 teams with different rates/location and a fixed
budget.
I have created 4 columns
Team1, Team 2, total hours and Total

Each team will have x number of resources. total worked Hours and Total
budget is Fixed (for example) $100000
I need help with the formula so I can increase numbers under team one
and it auto adjust team 2 so it matches the total. i.e. I may have 1
full time resource working for 2080 hours (full year) at $10 per hour
under team 1..so my team 2 should auto adjust the number required 2.55
resources @ $15/hr for 2080 hours. (so changing team 1 number auto
changes team 2 numbers so the total remains constant at 100000)

Any suggestions..I want to play out few scenarios

Thank you,


--
anasir