excel salary calculator by liwenting

VIEWS: 31 PAGES: 7

									    ?   Form Type:      ON SCALE
        Enter Information in the shaded areas below:                                                                                                                ?                                     Annual       Monthly
                                                                                                                                              FY 2000 NIH Awards, Jan-01 to Dec-01                    6   145,100      12,091.67                VERSION 0708.1
                                                                                                                                                                                                                                                 AS OF 10/10/07
                  Employee ID:                                                                                                                FY 2001 NIH Awards, Oct-00 to Dec-00                    7   157,000      13,083.33
                                                                                                                                              FY 2001 NIH Awards, Jan-01 to Dec-01
                           PCN:                                                                                                                                                                       8   161,200      13,433.33
                                                                                                                                              FY 2002 NIH Awards, Oct-01 to Dec-01
                                                                                                                                              FY 2001 NIH Awards, Jan-03 to Dec-02
                     Last Name :                                                                                                              FY 2002 NIH Awards, Jan-02 to Dec-02                    9   166,700      13,891.67
                                                                                                                                              FY 2003 NIH Awards, Oct-02 to Dec-02
                                                                                                                                              FY 2003 NIH Awards, Jan-03 to Dec-03
                     First Name :                                                                                                                                                                    10   171,900      14,325.00
                                                                                                                                              FY 2004 NIH Awards, Oct-03 to Dec-03
                                                                                                                                              FY 2004 NIH Awards, Jan-04 to Dec-04
                                                                                                                                               FY 2005 NIH Awards, Oct-04 - Dec-04                   11   175,700      14,641.67
                                                      RESET FORM
                                                                                                                                              FY 2005 NIH Awards, Jan-05 to Dec-05                   12   180,100      15,008.33
                                                                                                                                               FY 2006 NIH Awards, Jan-06 - Dec-06                   13   183,500      15,291.67
                                                                                                                                               FY 2007 NIH Awards, Jan-07 - Dec-07                   14   186,600      15,550.00


                                                                                                                                                                   Is Salary over low NIH Salary Cap?       NO

        Enter Annual Salary Target for FULL TIME APPOINTMENT

                        Enter Target Annual Salary:                        ?



                                                                                                                                                                                                                       ?


            Total Percent Time:                                            ?                                                                        FULL-TIME COMPENSATION BY DOS TYPE                                                  MONTHLY FUNDING PRIOR TO ADDING EXTRAMURAL FUNDS
                          Scale :                                          ?                                                            MEG                                               MBY              Total                             MEG                                                   MBY                Total
           Instructor (enter "x"):                                         ?                                                                                                                                           SELECT ONE                                                                                             -
          Assistant (enter step):                                                                                                                                                                                      SELECT ONE                                                                                             -
         Associate (enter step):
         Professor (enter step):
                                                                                                            Total Annual by % Time                                                                                     TOTAL                       -            -                   -                      -                  -
                                                                                                           Total Monthly by % Time
?                                                                                                          Percentage by DOS Code                                                                                      ?
                                 Enter Funding Information Below (only enter % for extramural funding)                                                     DISTRIBUTION                                                                  MONTHLY NIH SALARY CAP DIFFERENTIAL


                                                                               Extra-                                      % of Full-
          Account                                        Account Name or        Mural   Target Dollars   Derived Dollars     Time
          Number        OP Fund         Fund Type          Description         Fund%      (Monthly)        (Monthly)        Effort      MEG                                               MBY             TOTAL              BYR                                            BYY                   Total
                                     SELECT ONE
                                                                                                                                                                                                                   -
                                     SELECT ONE
                                                                                                                                                                                                                   -
                                     SELECT ONE
                                                                                                                                                                                                                   -
                                     SELECT ONE
                                                                                                                                                                                                                   -
                                     SELECT ONE
                                                                                                                                                                                                                   -
                                     SELECT ONE
                                                                                                                                                                                                                   -
                                     SELECT ONE
                                                                                                                                                                                                                   -
                                     SELECT ONE
                                                                                                                                                                                                                   -
                                     SELECT ONE
                                                                                                                                                                                                                   -
                                     SELECT ONE                                                                                                                                                                    -
        Total compensation distribution                                                            -                 -                        -               -                 -                -                 -                -              -            -                   -                      -
?                                                                                                                                                                                      Differential Account Names:
Comments:                                                                                                                                                           ?
                                                                                                                                                                                                                       Date Processed:
                                                                                                                                                                  GENERATE DISTRIBUTION SUMMARY
                                                                                                                                                                                                                       Approved by:
                                                                                                                                                                                                                                                       created by UCSF SOM
                                                                                                                                                                                                                                                       Mfd for use by UCD SOM/DSS ckp




                Printed: 10/7/2011 @ 8:58 AM                                                                                                                                                                                                                               7763ed0e-5029-4732-aa79-fa8514ea8395.xls
     ?    Form Type: OFF SCALE
          Enter Information in the grey shaded areas below:
                                                                                                                                                                         ?                                  Annual       Monthly
                                          Employee ID:                                                                                            FY 2000 NIH Awards, Jan-01 to Dec-01                  6   145,100      12,091.67
                                                                                                                                                                                                                                                         VERSION 0708.1
                                                 PCN:                                                                                             FY 2001 NIH Awards, Oct-00 to Dec-00                  7   157,000      13,083.33
                                                                                                                                                                                                                                                          AS OF 10/10/07
                                                                                                                                                  FY 2001 NIH Awards, Jan-01 to Dec-01
                                            Last Name :                                                                                                                                                     161,200      13,433.33
                                                                                                                                                  FY 2002 NIH Awards, Oct-01 to Dec-01                  8
                                                                                                                                                  FY 2001 NIH Awards, Jan-03 to Dec-02
                                           First Name :                                                                                           FY 2002 NIH Awards, Jan-02 to Dec-02                      166,700      13,891.67
                                                                                                                                                  FY 2003 NIH Awards, Oct-02 to Dec-02                  9
                                                                                                                                                  FY 2003 NIH Awards, Jan-03 to Dec-03                      171,900      14,325.00
                                                                                                                                                  FY 2004 NIH Awards, Oct-03 to Dec-03                 10
                                                                                                                                                  FY 2004 NIH Awards, Jan-04 to Dec-04
                                                                                                                                                                                                                         14,641.67
                                                                                                                                                   FY 2005 NIH Awards, Oct-04 - Dec-04                 11   175,700
                                                                                                                                                                                                       12   180,100      15,008.33
                                                                                                                                                   FY 2005 NIH Awards, Jan-05 - Dec-05
                                                                                                                                                                                                                         15,291.67
                                                                                                                                                   FY 2006 NIH Awards, Jan-06 - Dec-06                 13   183,500
                                                                                                                                                   FY 2007 NIH Awards, Jan-07 - Dec-07                 14   186,600      15,550.00


                                                                                                                                                                    Is Salary over low NIH Salary Cap?        NO

          Enter Annual Salary Target for FULL TIME APPOINTMENT

                      Enter Target Annual Salary:                             ?


                                                                                                                                                                                                                         ?

            Total Percent Time:                                               ?                                                                    FULL-TIME COMPENSATION BY DOS TYPE                                                       MONTHLY FUNDING PRIOR TO ADDING EXTRAMURAL FUNDS
                        Scale :                                               ?                                                     MEG           OSC                              MBY                       Total                             MEO                                        MBY                             Total
            Instructor (enter "x"):                                           ?                       Instructor (enter OSC):                                                                                            SELECT ONE                                                                                           -
                                                                                                                                                                                                                         SELECT ONE
           Assistant (enter step):                                                                    Assistant (enter OSC):                                                                                                                                                                                                  -
          Associate (enter step):                                                                     Associate (enter OSC):
          Professor (enter step):                                                                     Professor (enter OSC):
                                                                                                         Total Annual by % Time                                                                                          TOTAL                     -           -           -               -                  -               -
                                                                                                        Total Monthly by % Time
?                                                                                                                                   (MEO Total)                                                                          ?
                                 Enter Funding Information Below (only enter % for extramural funding)                                                            DISTRIBUTION                                                               MONTHLY NIH SALARY CAP DIFFERENTIAL
                                                                                           Target                      % of Full-
    NIH    Account       Fund                                                  Target      Dollars     Derived Dollars   Time
    CAP    Number       Number         Fund Type               Name           Percent     (Monthly)      (Monthly)      Effort      MEO                                                      MBY            TOTAL                BYO                                                 BYY               Total
                                      SELECT ONE

                                      SELECT ONE

                                      SELECT ONE

                                      SELECT ONE

                                      SELECT ONE

                                      SELECT ONE

                                      SELECT ONE

                                      SELECT ONE

                                      SELECT ONE

                                      SELECT ONE

          Total compensation distribution                                                                          -                      -                         -              -               -                 -                  -                      -           -               -                  -

?         Comments:                                                                                                                                        ?                             Differential Account Names:

                                                                                                                                                                                                                         Date Processed:
                                                                                                                                                               GENERATE DISTRIBUTION SUMMARY
                                                                                                                                                                                                                         Approved by:




                   Printed: 10/7/2011 @ 8:58 AM                                                                                                                                                                                                                                7763ed0e-5029-4732-aa79-fa8514ea8395.xls
    ? Form Type: ABOVE SCALE
      Enter Information in the gray shaded areas below:                                                                          ?                                                   Annual       Monthly
                                     Employee ID:                                                                           FY 2000 NIH Awards, Jan-01 to Dec-01         6           145,100        12,091.67                    VERSION 0708.1
                                              PCN:                                                                                                                       7           157,000          13,083.33                   AS OF 10/10/07
                                                                                                                            FY 2001 NIH Awards, Oct-00 to Dec-00

                                       Last Name :                                                                          FY 2001 NIH Awards, Jan-01 to Dec-01         8           161,200          13,433.33
                                                                                                                            FY 2002 NIH Awards, Oct-01 to Dec-01
                                                                                                                            FY 2001 NIH Awards, Jan-03 to Dec-02
                                       First Name :                                                                         FY 2002 NIH Awards, Jan-02 to Dec-02         9           166,700          13,891.67
                                                                                                                            FY 2003 NIH Awards, Oct-02 to Dec-02

                                                          RESET FORM                                                        FY 2003 NIH Awards, Jan-03 to Dec-03         10          171,900          14,325.00
                                                                                                                            FY 2004 NIH Awards, Oct-03 to Dec-03
                                                                                                                            FY 2004 NIH Awards, Jan-04 to Dec-04
                                                                                                                                                                         11          175,700          14,641.67
                                                                                                                             FY 2005 NIH Awards, Oct-04 - Dec-04
                                                                                                                             FY 2005 NIH Awards, Jan-05 - Dec-05         12          180,100          15,008.33

                                                                                                                             FY 2006 NIH Awards, Jan-06 - Dec-06         13          183,500          15,291.67
                                                                                                                             FY 2007 NIH Awards, Jan-07 - Dec-07         14          186,600          15,550.00


                                                                                                                                                Is Salary over low NIH Salary Cap?     NO

      Enter Annual Salary Target for FULL TIME APPOINTMENT

                 Enter Target Annual Salary:                     ?


                                                                                                                                                                                                  ?

                               Total Percent Time:               ?                                                                   FULL-TIME COMPENSATION BY DOS TYPE                                            MONTHLY FUNDING PRIOR TO ADDING EXTRAMURAL FUNDS
                                           Scale :               ?                                                    MEG                                        MBY                  Total                             MEG                                MBY                                  TOTAL
                                                                                                                                                                                                  SELECT ONE
                              Instructor (enter "x"):                                                                                                                                                                                                                                               -
                                                                                                                                                                                                  SELECT ONE
                            Assistant (enter step):                                                                                                                                                                                                                                                 -
                            Associate (enter step):

                      Professor (enter REG total):
                                                                                           Total Annual by % Time                                                                                 TOTAL                      -                -                  -                   -              -
                                                                                          Total Monthly by % Time
?
                       Enter Funding Information Below (only enter % for extramural funding)                                                     DISTRIBUTION                                                       MONTHLY NIH SALARY CAP DIFFERENTIAL
                                                                               Target         Derived    % of Full-
NIH    Account     Fund                                            Target      Dollars        Dollars      Time
CAP    Number     Number        Fund Type            Name         Percent     (Monthly)      (Monthly)    Effort      MEG                                               MBY          TOTAL              BYR                                               BYY                Total
                              SELECT ONE
                                                                                                                                                                                              -                                                                                      -
                              SELECT ONE
                                                                                                                                                                                              -                                                                                      -
                              SELECT ONE
                                                                                                                                                                                              -                                                                                      -
                              SELECT ONE
                                                                                                                                                                                              -                                                                                      -
                              SELECT ONE
                                                                                                                                                                                              -                                                                                      -
                              SELECT ONE
                                                                                                                                                                                              -                                                                                      -
                              SELECT ONE
                                                                                                                                                                                              -                                                                                      -
                              SELECT ONE
                                                                                                                                                                                              -                                                                                      -
                              SELECT ONE
                                                                                                                                                                                              -                                                                                      -
                              SELECT ONE                                                                                                                                                      -
      Total compensation distribution                                                               -                       -               -                -                -               -                -             -                -                  -                   -

?                                                                                                                                ?                                  Differential Account Names:
Comments:
                                                                                                                                                                                                  Date Processed:
                                                                                                                                         GENERATE DISTRIBUTION SUMMARY
                                                                                                                                                                                                  Approved by:
                                                                                                                                                                                                                                   created by UCSF SOM
                                                                                                                                                                                                                                   Mfd for use by UCD SOM/DSS ckp




             Printed: 10/7/2011 @ 8:58 AM                                                                                                                                                                                                            7763ed0e-5029-4732-aa79-fa8514ea8395.xls
     You will see that the unprotected fields in                If you hold the mouse over the red arrows                          In most cases, the Tab key is used to move
     the left half of the spreadsheet are                       that appear in the corners of certain fields,                      from cell to cell in this spreadsheet;
     highlighted in grey. These are the cells in                you will see a comment box with                                    however the Enter key is used to move
     which you may enter information.                           information about that particular field or                         from one line to the next when entering
                                                                item. Click on the ? to get to this linked                         funding information. Once done with
                                                                worksheet of instructions for that item.                           entering data and generating the output
                                                                Click within the instruction cell to return to                     report, select the RESET button to begin
                                                                the Calculation spreadsheet.                                       entering funding for another faculty
                                                                                                                                   member.




                                                                                                                                   After the Annual Salary or X Factor has been
                                                                                                                                   entered, the field, 'Is Salary over low NIH
                                                                                                                                   Salary Cap?', will display either a 'Yes' or a
                                                                Tab down to enter the Annual Salary or the X
                                                                                                                                   'No'. A 'Yes' indicates that if any funding
                                                                Factor (where X = % of REG total, e.g. 200%
     Enter the faculty member's Employee ID                                                                                        sources are subject to the NIH salary cap,
                                                                entered as 2, if Annual Base REG is 52,300, X
     Number, PPS Position Control Number, Last                                                                                     enter a number 1-11 to the left of the capped
                                                                factor of 2 would equal total annual salary of
1    and First Name and the inclusive period               2                                                                   3   account/fund number (i.e., in the column
                                                                104,300. ) Note that if you enter the Annual
     covered by the funding information you are                                                                                    headed 'NIH Cap') to indicate which NIH cap
                                                                Salary, the X Factor is calculated and
     going to enter.                                                                                                               you plan to use for each fund. Please note,
                                                                displayed, and if you enter the X Factor, the
                                                                                                                                   however, that a 'Yes' in that field does not
                                                                Annual Salary is calculated and displayed.
                                                                                                                                   necessarily mean that any of this individual's
                                                                                                                                   salary is NIH funded, only that his/her total
                                                                                                                                   salary is greater than the lower of the two caps.

                                                                                                                                   Tab down to the appropriate rank for this
                                                                                                                                   individual and enter either an 'x' (for an
                                                                                                                                   instructor) or the appropriate step (for faculty at
                                                                                                                                   the assistant, associate or full professor level).
                                                                Tab down to the 'Scale' field and enter the
                                                                                                                                   The fields headed 'Full-Time Compensation by
     Now Tab down to enter Total Percent Time                   number of the compensation plan salary scale
4                                                          5                                                                   6   DOS Type' will now be populated with the
     (1.00 is 100%, .50 is 50%, etc.).                          (i.e., Scale 0 through 9) on which this faculty
                                                                                                                                   applicable REG (i.e., Fiscal Year Base), HBT,
                                                                member is paid.
                                                                                                                                   HBY, HBx, and Total dollar amounts. These
                                                                                                                                   derived amounts are based on what you
                                                                                                                                   entered as Annual Salary or X Factor, Total
                                                                                                                                   Percent Time, Scale, Rank and Step.


                                                                Now Tab down to enter funding information.
                                                                When a General Compensation or Med Comp                            Page down until the 'Total Compensation
     Enter the MONTHLY totals for each DOS code
                                                                Fundtype is selected, the targeted salary                          Distribution' line at the bottom of the 'Funding
     into the first line (refer to the full-time
                                                                values entered previously will automatically                       Information' section is visible. You will see that
     compensation by DOS type to the left for the
                                                                appear under the Targeted salary - there is no                     'Derived Dollars' and 'Actual Percent' fields are
     values), including up to 10 digits of precision (if
                                                                need to edit these lines other than to include                     now populated by fund; the 'Distribution'
     necessary) for non-terminating fractional
                                                                account info. When entering any extramural                         section's REG, HBT, HBY, HBx and TOTAL
     values such as 1250.6666666667. If the
                                                                funding, be sure to include the percentage of                      fields are populated with appropriate percents
     position has been allocated a general funded
                                                                salary that the funding will cover, and all                        by fund, and the NIH Salary Cap Differential
7    FTE, make sure to include a second line, with         8                                                                   9
                                                                medcomp and gencomp values will                                    fields, where required, are populated with dollar
     GenFunds selected from the drop down menu,
                                                                automatically be adjusted to account for the                       amounts. The amount shown in the 'Total
     and enter the monthly amount under the REG
                                                                extramural funding. Remember: if a faculty                         Derived Dollars' field (plus any NIH Salary Cap
     column (if a fractional FTE, apply same ratio to
                                                                member's salary exceeds the lower NIH cap                          Differential that is required) should equal the
     the total monthly REG amount, with the
                                                                rate and your are using a fund source subject                      Total Monthly salary indicated above. If there is
     remainder under the MedComp line above).
                                                                to the cap, you must enter a number between 1                      a differential amount, enter the account to
     No other DOS code other than REG can be
                                                                and 11 (1-5 hidden to preserve space) in the                       charge the differential to in the cells below
     funded by 19900 funds.
                                                                NIH cap field in order for the NIH salary                          each differential amount.
                                                                differential dollars to be calculated correctly.*



                                                                * UCD uses the same DOS codes for the differentials as for
                                                                the components under the salary limits. Any differential
                                                                salary is automatically included in the non-extramural funds
                                                                when the output screen is generated, using the same ratios
                                                                and accounts by component as the non-differential pay.
                                                                (NOTE, that if entering the differentials manually, they may
                                                                never be charged to other extramarual funding, federal,
                                                                state, county or city funds that are non-discretionary).
     At the bottom of this page, there are additional
                                                                Once the form is complete, select the
     optional fields in which the department may
                                                                GENERATE OUTPUT to generate the
10   enter comments, approval initials or                  11
                                                                summary report that will display all of the
     signatures, and/or the date on which the form
                                                                distributions to be entered.
     was created or processed.
1. 19900 Funds (in fact, any 199xx funds) may ONLY be used to pay Fiscal Year base, i.e., may be used only on
lines with DOS codes REG.

2. Ladder rank faculty in clinical departmnet may receive full time university appointment with not less than 50% of
base salary provided by 19900 funds along with the corresponding fractional FTE allocated to the position. The
remaining portion of the appointment will be in the Professor in Residence, will be paid from non-19900 funds and will
not obligate an FTE commitment for that percentage. (see APM Section UCD-220I - School of Medicine: Use of Fractional
FTEs for Full time appointments ).


3. For non-ladder rank faculty with a full or partial general funded FTE: at least 50% of the FY base salary, pro-rated
by percent of the FTE assigned, must be paid from 19900 (e.g., the formula to be applied for a faculty member
assigned a 0.25 FTE: 50% of FY base dollars x .25 FTE = minimum dollars that must be paid from 19900).



4. For faculty in the Adjunct or In Residence series: 19900 funds may not be used to pay more than 50% of the FY
base; this amount, which must be pro-rated by a faculty member's total appointment percent. Similarly, they will also
be allocated the corresponding fraction of the General Funded FTE, never exceeding .5 FTE. (see APM Sections 220I
(ucd),270 and 280) .

5. Salaried Clinical or Clinical X series faculty may be paid up to 100% of their FY base from 19900 funds and be
assigned the corresponding General Funded FTE allocation. (see APM Section 275 ).
The instructions for on-scale salary calculations also apply for off-scale calculations, with the following additional requirement:
Enter the approved off-scale annual fiscal year base rate (OSC) at the appropriate rank in the column headed 'OSC' under 'Full-
Time Compensation by DOS Type' (you will notice that this column is grey in the off-scale spreadsheet, to indicate that you may
enter data in these fields). This will be the additional off-scale increment added to the REG base to comprise the total off-scale
base compensation.




1. A faculty member's salary is designated 'off-scale' if the fiscal year base rate is higher than the rate associated with her/his rank and step in the published
UC Salary Scales. Requests for off-scale salaries must be reviewed and approved in advance, both at the School level and by the Vice Chancellor, Academic
Affairs. They are approved as flat dollar amounts that, like the annual fiscal year base rates, must be rounded to the nearest 100 dollars. (NOTE: The EDB
record for a faculty member with an off-scale FY base rate reflects the individual's approved step on each distribution line, and in addition, an 'O' is recorded in
the Off/Above Scale field immediately to the right of Step).

2. Only the fiscal year base component of salary is 'off-scale' which is comprised of the funding under REG and OSC; the OSC DOS code is also referred to
as the Off Scale Increment - the difference between on-scale base and the total off-scale base rates. All other components of covered compensation (i.e.,
HBT and HBY) remain at the on-scale rate associated with the individual's rank and step in the published UC Salary Scales.
The instructions for on-scale salary calculations also apply to Above Scale calculations, with the exception of the following: After entering 'Total Percent Time'
and 'Scale', enter the approved Above Scale fiscal year base rate in the field labeled 'Professor (enter FY Base)'.




1. Above Scale salaries result when a faculty member at the full professor level is approved for a merit advancement beyond the highest step on the UC
faculty fiscal year salary scale; at the present time, this would apply to any merit advancement beyond Professor, step 9. (NOTE: When this occurs, the Step
field in the individual's EDB record is blank, but an 'A' is recorded in the Off/Above Scale field).

2. The spreadsheet will calculate all components of the faculty member's salary (i.e., REG, HBT, HBY and HBx) based on the approved Above Scale base
that you have entered.

								
To top