Hello.

I am trying to figure out a way to forecast how many tech/support people i need. The way I am looking at it is that we have 2 types of accounts we can add. For each account type, I would then need to add 1 of 2 types of tech/support.

Accross row A I have time broken into quarters. In row B & C, I have the two types of accounts and how I anticipate them to grow during the next 5 years of quarters.

What I am having trouble doing is in the next rows for headcount. What i need to do for each accoutn type separately (rows B&C) is say "when we add 2 accoutns, regardless of quarter, hire 1 tech person. When we add 3 accounts, regardless of quarter, hire 1 support person."

So for example if in the first 4 quarters we add 9 accounts, I would need to hire 3 support people and 4 tech people. But i need to find a way to break this out to show which quarter they would be hired in.



This problem has been haunting me for the last few work days on a large, highly visible project I am working on. Any help is GREATLY appreciated. Also let me know if more info/details are needed.

You say there are two types of accounts. To determine headcount, are the two types simply added together? Using your example of 9 new accounts, is this the sum of the B and C accounts added?

No. The two types of accoutns are independant. So I would be looking at 9 new adds to account type 1 (row B), then the remainder of my example above for rules to adding heads. I would then duplicate this for account type 2 in row C.

Sorry for any confusion.

No need to apologize. It's early Monday and I haven't had coffee yet.

So if you added 9 Type 1 and 7 Type 2, then you would only need to add 4 + 3 = 7 techs?

I appreciate the early effort on your end.

So if we add 9 type 1 acctouns, I would need 3 techs (since i add 1 every 3 accounts) and 4 supports (since i add 1 every 2 accts).

Then if we have 7 type 2 accts, I would need 2 techs (1 every 3 accts) and 3 supports (1 every 2 adds).

Plus the big problem is having this regarless of quarter. So in Q1 i could add 4 type 1 accounts (meaning i'd need 1 tech and 2 support), then add 5 more in Q2, but i would then have to hire 2 techs (since ive added 3 accts since my last hire) and 2 more supports.

Hope this helps.

To be clear, it's 1:3 for additional techs and 1:2 for additional support. Because in your first post, you wrote: "add 2 accounts...hire 1 tech person, add 3 accounts...hire 1 support person" which would seem to imply 1:2 for techs and 1:3 for support.

This aside, I think I have a bead on it. I'll have something for you to look over soon.

Sorry. I think in my reply i mixed up the examples. But regardless of what you call them, one hire is a 1:3 ratio and the other is 1:2.

Thanks again.

OK...so here's what I have. I start with 3rd qtr 2014 (3Q14) and continue for four quarters total. The equations for 3Q14 are a bit different, reasons for which I hope will be clear upon further explanation.

The equations for the Tech adds in the following quarters go something like this:
* Sum all Type 1 accounts added for this and previous quarters, divide by 3, round down
* Sum all Type 2 accounts added for this and previous quarters, divide by 3, round down
* Add these together
* Sum all Tech hires for all previous quarters (i.e. not including this quarter)
* Subtract this number from the sum in step 3

e.g. =SUM(ROUNDDOWN(SUM($B2:C2)/3,0),ROUNDDOWN(SUM($B3:C3)/3,0))-SUM($B4:B4)

The equations for Support hires are the basically same only the divisors above are 2, not 3.

i.e. =SUM(ROUNDDOWN(SUM($B2:C2)/2,0),ROUNDDOWN(SUM($B3:C3)/2,0))-SUM($B5:B5)

See attached.

Attached Files

Headcount.xlsx (8.4 KB, 11 views)