Compensation model help

mattdete

New Member
Joined
Feb 7, 2014
Messages
17
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.

DivisionDeptUG SCHFY2011UG SCHFY2012UG SCHFY2013MA Grad SCHFY2011MA Grad SCHFY2012MA Grad SCHFY2013PhD Grad SCHFY2011PhD Grad SCHFY2012PhD Grad SCHFY2013Instructor FTEFY2011Instructor FTEFY2012Instructor FTEFY2013Faculty FTEFY2011Faculty FTEFY2012Faculty FTEFY2013
DIVISION A1.0011,780.0010,873.0010,210.00592.00593.00459.000.000.000.001.631.001.0024.5025.5024.50
DIVISION A2.007,383.006,936.006,249.00386.00364.00313.000.000.000.0011.008.0010.0012.0013.0012.00
DIVISION A3.006,183.006,780.006,650.00342.00115.00207.0081.00140.0089.000.500.501.0011.0011.0012.00
DIVISION A4.0026,045.0024,761.0021,768.00925.00878.001,066.00613.00585.00471.003.003.004.0043.5043.5041.50
DIVISION A5.006,980.006,940.006,660.0093.0036.0060.000.000.000.000.500.000.009.0011.0010.00
DIVISION A6.009,340.008,413.007,515.00135.00162.00160.00130.00142.00136.005.014.344.3411.5010.509.50
DIVISION A7.0011,415.0010,508.0010,919.00288.00222.00165.000.000.000.007.006.007.0011.6711.0010.00
DIVISION A8.0022,721.0021,231.0020,846.00367.00220.00338.00318.00378.00399.001.001.001.0029.5030.5030.50
DIVISION A9.005,165.004,637.003,135.000.000.000.000.001.001.001.002.332.002.00
DIVISION A10.0020,295.0018,146.0016,984.00447.00399.00324.00238.00210.00259.004.683.344.3423.8322.8322.50
DIVISION A11.005,115.004,748.004,535.00229.00123.00113.000.000.000.000.000.000.008.508.509.50
DIVISION A12.0018,089.0017,216.0015,276.00526.00528.00495.00205.00183.00182.0011.0011.0011.0015.0015.0015.00
DIVISION A13.0012,087.0011,818.0011,865.00511.00522.00533.00149.00136.00188.006.006.507.5015.0014.0013.00
DIVISION A14.000.000.000.000.000.000.000.000.000.000.000.000.001.001.001.00
DIVISION A15.0023,585.0025,710.0023,991.0015.0036.0015.000.000.000.0051.1744.8438.674.004.005.00
DIVISION A16.000.000.000.000.000.000.000.000.000.001.001.001.001.001.001.00
DIVISION A17.000.000.000.00160.00165.00172.00158.00135.0095.000.000.000.000.000.000.00
DIVISION B18.0014,708.0015,759.0016,957.00533.00568.00536.00544.00548.00585.005.005.003.0017.0016.0017.00
DIVISION B19.007,872.009,122.009,109.00422.00459.00493.00356.00321.00291.001.001.001.0017.0017.0018.00
DIVISION B20.008,332.007,520.006,541.00510.00305.00330.00567.00607.00538.001.001.001.008.508.508.00
DIVISION B21.0027,674.0027,629.0028,874.001,382.001,360.001,577.001,269.001,353.001,435.004.303.803.8037.5038.5038.25
DIVISION B22.0020,379.0020,558.0020,824.00358.00210.00258.00533.00605.00564.002.502.503.5021.5020.5019.50
DIVISION B23.004,528.004,910.005,282.00703.00399.00451.00219.00181.00203.001.001.001.004.505.006.00
DIVISION B24.0013,741.0013,246.0012,441.00739.00384.00433.00466.00463.00416.000.000.000.0017.8317.8317.50
DIVISION B25.0010,486.0010,762.009,964.00506.00472.00482.00332.00356.00391.000.750.750.7518.0016.5016.50
DIVISION B26.0017,742.0018,430.0018,536.00553.00559.00532.00233.00257.00233.009.009.009.0015.0015.0015.00
DIVISION B27.0028,616.0029,451.0031,157.00784.00596.00631.00523.00443.00448.003.505.005.5026.0024.0024.00
DIVISION B28.008,305.009,844.0010,275.00302.00357.00296.00558.00489.00496.002.803.003.0025.0023.5023.50
DIVISION B29.0024,947.0025,165.0026,716.00732.00766.00579.001,952.002,037.002,089.001.001.501.5048.4046.6047.10
DIVISION B30.0035,242.0034,050.0031,348.00718.00682.00721.00817.00941.00923.005.005.006.0037.5036.5036.50
DIVISION C31.0016,834.0017,032.0015,164.00383.00254.00161.00408.00456.00508.000.670.671.0014.8314.8314.00
DIVISION C32.0014,227.0016,072.0018,212.00457.00538.00455.00445.00338.00310.002.002.002.2515.0014.0015.00
DIVISION C33.0029,829.0031,008.0031,004.000.000.000.001,392.001,308.001,293.002.002.002.0025.8326.3326.00
DIVISION C34.003,987.004,343.004,597.00108.0010.0026.000.000.000.001.001.001.0010.0010.009.00
DIVISION C35.003,984.004,142.003,989.000.000.000.000.000.000.001.001.501.752.001.501.50
DIVISION C36.004,268.004,256.004,110.00511.00695.00645.00328.00394.00385.000.500.500.507.507.507.50
DIVISION C37.0022,407.0021,046.0020,535.003.000.004.001,155.001,111.001,153.002.172.172.1726.0024.5026.50
DIVISION C38.006,056.006,427.005,874.002,339.002,448.002,509.00310.00452.00363.006.004.007.5010.9011.0011.00
DIVISION C39.0028,735.0028,143.0027,320.00268.00354.00274.00792.00574.00677.003.003.003.5018.5019.5018.50
DIVISION C40.003,987.003,265.002,811.0066.00135.0081.000.000.000.000.000.000.005.504.505.50
DIVISION C41.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00
DIVISION D42.000.000.000.000.000.000.000.000.005.004.005.001.001.001.00
DIVISION D43.000.000.000.000.000.000.000.000.005.675.344.841.001.001.00
DIVISION D44.000.000.000.000.000.000.000.000.004.504.504.501.001.001.00
DIVISION D45.000.000.000.000.000.000.000.000.004.334.504.671.001.001.00
DIVISION D46.000.000.000.000.000.000.000.000.000.001.501.501.001.001.00
DIVISION D47.000.000.000.000.000.000.000.000.001.001.002.671.001.001.00
DIVISION D48.000.000.000.000.000.000.000.000.000.000.000.000.001.001.00
DIVISION D49.000.000.000.000.000.000.000.000.000.000.000.000.000.001.00
DIVISION D50.000.000.000.000.000.000.000.000.001.001.001.000.500.501.00
DIVISION D51.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00

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

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,299
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 7, 2014
Messages
17
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

New Member
Joined
Feb 7, 2014
Messages
17
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

Well-known Member
Joined
Oct 17, 2005
Messages
1,299
Office Version
  1. 365
Platform
  1. Windows
Hi Matt,

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

Regards,

Koen
 

Watch MrExcel Video

Forum statistics

Threads
1,122,519
Messages
5,596,635
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top