3-dimensional matrix

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

"smartin" wrote:

> XL comp. wrote:
> > 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.
>
> --
> Smartin
> (Plays an actuary on TV)
>

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


"XL comp." wrote:

> Please let me know how to create this 3-D Matrix. Thanks again
>
> "smartin" wrote:
>
> > XL comp. wrote:
> > > 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.
> >
> > --
> > Smartin
> > (Plays an actuary on TV)
> >

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

"John C" wrote:

> 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
>
>
> "XL comp." wrote:
>
> > Please let me know how to create this 3-D Matrix. Thanks again
> >
> > "smartin" wrote:
> >
> > > XL comp. wrote:
> > > > 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.
> > >
> > > --
> > > Smartin
> > > (Plays an actuary on TV)
> > >

Glad I could help
--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"XL comp." wrote:

> Thanks John. This is very helpful. I was able to find a solution.
>
> "John C" wrote:
>
> > 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
> >
> >
> > "XL comp." wrote:
> >
> > > Please let me know how to create this 3-D Matrix. Thanks again
> > >
> > > "smartin" wrote:
> > >
> > > > XL comp. wrote:
> > > > > 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.
> > > >
> > > > --
> > > > Smartin
> > > > (Plays an actuary on TV)
> > > >