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

 

[smartin]

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

Sounds like fun!

I think you need to create a 3D matrix of allocation factors based on
your three criteria (level, performance rating, and lag). These
allocations are entirely subjective and in your court.

Perhaps you could start by assigning weights to each category such that
the sum across totals unity, then figuring the cross products.

 

[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

 

[XL comp.]

Thanks John. This is very helpful. I was able to find a solution.

 

[John C]

Glad I could help