Compensation model help

[mattdete]

I need help on this task. Are there any templated sheets one can recommend?

Using the data contained on the second tab "data" create a financial model that the Deans of the college can use to determine how the chairman ("chair) of a department should be compensated for the administrative role of managing the department. Currently the chair stipend is 21% of the chair's base salary, but the college wants to consider alternate methodologies for calculating a chair's stipend.

Division	Dept	UG SCHFY2011	UG SCHFY2012	UG SCHFY2013	MA Grad SCHFY2011	MA Grad SCHFY2012	MA Grad SCHFY2013	PhD Grad SCHFY2011	PhD Grad SCHFY2012	PhD Grad SCHFY2013	Instructor FTEFY2011	Instructor FTEFY2012	Instructor FTEFY2013	Faculty FTEFY2011	Faculty FTEFY2012	Faculty FTEFY2013
DIVISION A	1.00	11,780.00	10,873.00	10,210.00	592.00	593.00	459.00	0.00	0.00	0.00	1.63	1.00	1.00	24.50	25.50	24.50
DIVISION A	2.00	7,383.00	6,936.00	6,249.00	386.00	364.00	313.00	0.00	0.00	0.00	11.00	8.00	10.00	12.00	13.00	12.00
DIVISION A	3.00	6,183.00	6,780.00	6,650.00	342.00	115.00	207.00	81.00	140.00	89.00	0.50	0.50	1.00	11.00	11.00	12.00
DIVISION A	4.00	26,045.00	24,761.00	21,768.00	925.00	878.00	1,066.00	613.00	585.00	471.00	3.00	3.00	4.00	43.50	43.50	41.50
DIVISION A	5.00	6,980.00	6,940.00	6,660.00	93.00	36.00	60.00	0.00	0.00	0.00	0.50	0.00	0.00	9.00	11.00	10.00
DIVISION A	6.00	9,340.00	8,413.00	7,515.00	135.00	162.00	160.00	130.00	142.00	136.00	5.01	4.34	4.34	11.50	10.50	9.50
DIVISION A	7.00	11,415.00	10,508.00	10,919.00	288.00	222.00	165.00	0.00	0.00	0.00	7.00	6.00	7.00	11.67	11.00	10.00
DIVISION A	8.00	22,721.00	21,231.00	20,846.00	367.00	220.00	338.00	318.00	378.00	399.00	1.00	1.00	1.00	29.50	30.50	30.50
DIVISION A	9.00	5,165.00	4,637.00	3,135.00	0.00		0.00	0.00		0.00	1.00	1.00	1.00	2.33	2.00	2.00
DIVISION A	10.00	20,295.00	18,146.00	16,984.00	447.00	399.00	324.00	238.00	210.00	259.00	4.68	3.34	4.34	23.83	22.83	22.50
DIVISION A	11.00	5,115.00	4,748.00	4,535.00	229.00	123.00	113.00	0.00	0.00	0.00	0.00	0.00	0.00	8.50	8.50	9.50
DIVISION A	12.00	18,089.00	17,216.00	15,276.00	526.00	528.00	495.00	205.00	183.00	182.00	11.00	11.00	11.00	15.00	15.00	15.00
DIVISION A	13.00	12,087.00	11,818.00	11,865.00	511.00	522.00	533.00	149.00	136.00	188.00	6.00	6.50	7.50	15.00	14.00	13.00
DIVISION A	14.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	1.00	1.00	1.00
DIVISION A	15.00	23,585.00	25,710.00	23,991.00	15.00	36.00	15.00	0.00	0.00	0.00	51.17	44.84	38.67	4.00	4.00	5.00
DIVISION A	16.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	1.00	1.00	1.00	1.00	1.00	1.00
DIVISION A	17.00	0.00	0.00	0.00	160.00	165.00	172.00	158.00	135.00	95.00	0.00	0.00	0.00	0.00	0.00	0.00
DIVISION B	18.00	14,708.00	15,759.00	16,957.00	533.00	568.00	536.00	544.00	548.00	585.00	5.00	5.00	3.00	17.00	16.00	17.00
DIVISION B	19.00	7,872.00	9,122.00	9,109.00	422.00	459.00	493.00	356.00	321.00	291.00	1.00	1.00	1.00	17.00	17.00	18.00
DIVISION B	20.00	8,332.00	7,520.00	6,541.00	510.00	305.00	330.00	567.00	607.00	538.00	1.00	1.00	1.00	8.50	8.50	8.00
DIVISION B	21.00	27,674.00	27,629.00	28,874.00	1,382.00	1,360.00	1,577.00	1,269.00	1,353.00	1,435.00	4.30	3.80	3.80	37.50	38.50	38.25
DIVISION B	22.00	20,379.00	20,558.00	20,824.00	358.00	210.00	258.00	533.00	605.00	564.00	2.50	2.50	3.50	21.50	20.50	19.50
DIVISION B	23.00	4,528.00	4,910.00	5,282.00	703.00	399.00	451.00	219.00	181.00	203.00	1.00	1.00	1.00	4.50	5.00	6.00
DIVISION B	24.00	13,741.00	13,246.00	12,441.00	739.00	384.00	433.00	466.00	463.00	416.00	0.00	0.00	0.00	17.83	17.83	17.50
DIVISION B	25.00	10,486.00	10,762.00	9,964.00	506.00	472.00	482.00	332.00	356.00	391.00	0.75	0.75	0.75	18.00	16.50	16.50
DIVISION B	26.00	17,742.00	18,430.00	18,536.00	553.00	559.00	532.00	233.00	257.00	233.00	9.00	9.00	9.00	15.00	15.00	15.00
DIVISION B	27.00	28,616.00	29,451.00	31,157.00	784.00	596.00	631.00	523.00	443.00	448.00	3.50	5.00	5.50	26.00	24.00	24.00
DIVISION B	28.00	8,305.00	9,844.00	10,275.00	302.00	357.00	296.00	558.00	489.00	496.00	2.80	3.00	3.00	25.00	23.50	23.50
DIVISION B	29.00	24,947.00	25,165.00	26,716.00	732.00	766.00	579.00	1,952.00	2,037.00	2,089.00	1.00	1.50	1.50	48.40	46.60	47.10
DIVISION B	30.00	35,242.00	34,050.00	31,348.00	718.00	682.00	721.00	817.00	941.00	923.00	5.00	5.00	6.00	37.50	36.50	36.50
DIVISION C	31.00	16,834.00	17,032.00	15,164.00	383.00	254.00	161.00	408.00	456.00	508.00	0.67	0.67	1.00	14.83	14.83	14.00
DIVISION C	32.00	14,227.00	16,072.00	18,212.00	457.00	538.00	455.00	445.00	338.00	310.00	2.00	2.00	2.25	15.00	14.00	15.00
DIVISION C	33.00	29,829.00	31,008.00	31,004.00	0.00	0.00	0.00	1,392.00	1,308.00	1,293.00	2.00	2.00	2.00	25.83	26.33	26.00
DIVISION C	34.00	3,987.00	4,343.00	4,597.00	108.00	10.00	26.00	0.00	0.00	0.00	1.00	1.00	1.00	10.00	10.00	9.00
DIVISION C	35.00	3,984.00	4,142.00	3,989.00	0.00	0.00	0.00	0.00	0.00	0.00	1.00	1.50	1.75	2.00	1.50	1.50
DIVISION C	36.00	4,268.00	4,256.00	4,110.00	511.00	695.00	645.00	328.00	394.00	385.00	0.50	0.50	0.50	7.50	7.50	7.50
DIVISION C	37.00	22,407.00	21,046.00	20,535.00	3.00	0.00	4.00	1,155.00	1,111.00	1,153.00	2.17	2.17	2.17	26.00	24.50	26.50
DIVISION C	38.00	6,056.00	6,427.00	5,874.00	2,339.00	2,448.00	2,509.00	310.00	452.00	363.00	6.00	4.00	7.50	10.90	11.00	11.00
DIVISION C	39.00	28,735.00	28,143.00	27,320.00	268.00	354.00	274.00	792.00	574.00	677.00	3.00	3.00	3.50	18.50	19.50	18.50
DIVISION C	40.00	3,987.00	3,265.00	2,811.00	66.00	135.00	81.00	0.00	0.00	0.00	0.00	0.00	0.00	5.50	4.50	5.50
DIVISION C	41.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
DIVISION D	42.00	0.00	0.00		0.00	0.00	0.00	0.00	0.00	0.00	5.00	4.00	5.00	1.00	1.00	1.00
DIVISION D	43.00	0.00	0.00		0.00	0.00	0.00	0.00	0.00	0.00	5.67	5.34	4.84	1.00	1.00	1.00
DIVISION D	44.00	0.00	0.00		0.00	0.00	0.00	0.00	0.00	0.00	4.50	4.50	4.50	1.00	1.00	1.00
DIVISION D	45.00	0.00	0.00		0.00	0.00	0.00	0.00	0.00	0.00	4.33	4.50	4.67	1.00	1.00	1.00
DIVISION D	46.00	0.00	0.00		0.00	0.00	0.00	0.00	0.00	0.00	0.00	1.50	1.50	1.00	1.00	1.00
DIVISION D	47.00	0.00	0.00		0.00	0.00	0.00	0.00	0.00	0.00	1.00	1.00	2.67	1.00	1.00	1.00
DIVISION D	48.00	0.00	0.00		0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	1.00	1.00
DIVISION D	49.00	0.00	0.00		0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	1.00
DIVISION D	50.00	0.00	0.00		0.00	0.00	0.00	0.00	0.00	0.00	1.00	1.00	1.00	0.50	0.50	1.00
DIVISION D	51.00	0.00	0.00		0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00

<colgroup><col><col span="16"></colgroup><tbody>
</tbody>

 

[Rijnsent]

Hi Mattdete,

that's an impressive amount of data, but I'm wondering what you are looking for? What kind of "template" are you looking for? An analysis tool for correlations? I'd happily give some pointers/options, but have a hard time understanding what it is you envision?

Cheers,

Koen

 

[mattdete]

Hi Koen:
Thanks for your help. The purpose is to create a financial model that the Deans of the college can use to determine how the chairman ("chair) of a department should be compensated for the administrative role of managing the department. Currently the chair stipend is 21% of the chair's base salary, but the college wants to consider alternate methodologies for calculating a chair's stipend. I need to consolidate this info and then be able to work with variables to come up with estimates.

 

[mattdete]

i can email you the file too. just to see it. i am trying use a fixed amount to allocate compensation based on percentage, $1M. the issue i am having is i am over-allocating because I am using a base and inflation %. so, i inflate by 1.05% and I am allocating that fixed amount also, which i don't necessarily want to do.

 

[Rijnsent]

Hi Matt,

bring it on, my email is my forum name followed by @hotmail.com.

Regards,

Koen