Resource Planning - How to Model?

[anasir]

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,

 

[Tom Ogilvy]

If you want to use formulas, you can only make adjustments to one set of
data. For example

if column A represents Team1 and column B represents Team2 and column C
represents total resources, a simple example would for B2

=C2-A2

so changes in A2 would reflect new levels in B2

Certainly many more cells and constraints can be involved in your formulas.

Other approaches are available under the tools menu - Goalseek and solver.
Solver is an addin shipped with Excel, so you need to install that if not
already installed. You can look at Excel help under "What if" for some
ideas as well as looking at individual help topics. There should be a
sample file shipped with excel that gives examples of types of problems that
solver is particularly suited to. Look for

SolvSamp.xls

for xl97 it is found at a location like: C:\Program Files\Microsoft
Office\Office\Examples\Solver