Modeling help

[matthewdete]

I need help on this task. The task is below.

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.

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.

Can anyone think of another approach. My programming skills are not up for this.


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

[Richard Buttrey]

Hi, and welcome to the forum.
Unfortunately your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

You might also like to upload the workbook to save us all the effort of parsing your text.

[matthewdete]

how do i upload?