3-dimensional matrix

[XL comp.]

I am trying to create a 3 dimensional matrix. The following is an example.

I am trying to distribute $100,000 amognst 234 employees equitably amongst 4
levels, (VP, AVP, Director, Manager) based on 4-Performance Ratings, (1-
Excellent, 2-Very Good, 3,-Good, 4-Fair) and based 4-Market Lag buckets (Over
-50% lag, -26% to -49%, -1% to -25%, No lag).

Any tips ?
Thanks

 

[XL comp.]

Please let me know how to create this 3-D Matrix. Thanks again

 

[John C]

Not knowing the breakdown, and assuming 234 employees are in the 4 levels you
have listed. Here is a possibility. Essentially, you need to assign point
values, weighted point values, to every person in 3 separate categories.
I assigned point values as follows:
VP: 130; AVP: 80; Director: 30, Manager: 10
No Lag: 7, -1% to -25%: 4, -26% to -49%: 2, -50% or more: 1
Excellent: 11, Very Good: 7, Good: 3, Fair: 1

so, a VP with no lag and Exc performance, would essentially get 148 shares
(148=130+7+11)
Whereas a manager with >50% lag and fair performance would only receive 12
shares (12=10+1+1)
You need to not ONLY weight each category amongst the 4 choices, but weight
each category against each other.
Once you have calculated everyone's share #, Divide $100,000.00 by the share
number. Then to determine how much each person gets, multiply the $/share
just calculated by each person's share.

I created some random data, where I thought 3VPs, 8 AVPs, 24 Directors, and
199 Managers, each with a random lag and performance grade. Just to see how
it would come out. In a random setting I got the following

max min avg
VP $2,311.49 $2,120.20 $2,210.53
AVP $1,514.43 $1,323.13 $1,438.71
DIR $1,036.19 $828.95 $921.28
Man $446.36 $191.30 $300.24

 

[John C]

Glad I could help