Yearly Salary Schedule Analysis

Yearly Salary Schedule Analysis

Version 3.5

April 2009

Yearly Salary Schedule Analysis

Version 3.5

April 2009

Written by Tom Kulmacz

src="C:\ExportImg\c7166fd8514822ba664a1020a45b65f1\Aspose.Words.1b3c5fc7-df9b-4393-9add-f3ba9d957e14.003.png" width="393" height="85" alt="" style="margin-top:83.51pt; margin-left:173.85pt; -aw-left-pos:172.15pt; -aw-rel-hpos:margin; -aw-rel-vpos:margin; -aw-top-pos:43.8pt; -aw-wrap-type:none; position:absolute">

Table of Contents

General

Installing the Digital Certificate...... 1

Copying the Files to Your Computer...... 5

Yearly Salary Schedule Analysis

IEA Yearly Salary Schedule Analysis...... 6

Using the IEA Yearly Salary Schedule Analysis Template...... 7

Using the Salary Model...... 11

Modifying the Model...... 15

Freezing Panes...... 15

TRS Factors...... 16

Installing the Digital Certificate

You will need to install the digital certificate to allow Excel 2007 to run the macros used in the template. This is accomplished in Excel by:

  1. In the Security Warning box,
    click the Options button.

  1. Click the Show Signature Details option.

  1. Click the View Certificate button.
  1. Click the Install Certificate button.
  1. Click the Place all certificates in the following store option to select it.
  1. Click the Trusted Root Certification Authorities location to select it.
  1. Click the OK button.
  1. Click the Finish button.
  1. If you receive a security warning,
    click the Yes button.
  1. Click the OK button.

  1. Click the OK button.


  1. Click the OK button.

  1. Click the Trust all documents from the publisher option to select it.
  1. Click the OK button.
  1. You may need to close the template and reopen it for the macros to be enabled.

Copying the Files to Your Computer

You may find it easier to copy the files to your computer and run them from there.

You never want to run Excel files from a diskette or CD. You should always copy them to your computer and run them from there.

To copy the salary schedule analysis files to your computer:

  1. Insert the diskette into the drive.
  1. Double-click the My Computer icon on the Desktop screen.
  1. In the My Computer window, double-click the CD Driveitem to display the contents of the CD.
  1. Click and hold down the mouse on the Yearly Salary Schedule Analysis folder.
    Without letting go of the mouse, drag it to the Desktop screen.
    Release the mouse when you reach the Desktop screen.
  1. A copy of the Yearly Salary Schedule Analysis folder is placed on the Desktop screen.

IEA Yearly Salary Schedule Analysis

Detailed Instructions

The IEA Yearly Salary Schedule Analysis Template will create Excel spreadsheets based on an initial salary schedule that can be used to see the effect of various percentage increases will have on the total cost of a contract. The template can create multiple years for the contract and can automatically increment the step assignment each year.

The template contains two sheets:



The Base Schedule and Base Scattergram must have the same format, including the same headers in Row 1 and the same Step levels in Column A.

Using the IEA Yearly Salary Schedule Analysis Template

Open the Template

  1. Double-click the Yearly Salary Schedule Analysis folder to open it.
  1. Double-click the Yearly Salary Schedule Analysis Template file to open it.
  1. Enter the salary schedule information into the Base Schedule sheet.
    Row 1 must contain the job titles or some other type of category description.
    Column A must contain the different Step or seniority levels.
    Be sure not to leave any blank salary amounts as the program will treat them as zero, resulting in incorrect totals!

The information can be typed in or copied from another source.

If copying and pasting from a different source, be sure to use the Paste Special command by:

  1. On the Home tab, click the down-arrow button located at the bottom of the Paste button.
  1. Click the Paste Values item.

Entering the Scattergram Amounts

An easy way to ensure the headings on the Base Scattergram sheet match those on the Base Schedule sheet is to copy and paste Row 1 and Column A from the Base Schedule sheet onto the Base Scattergram sheet.

  1. On the Base Schedule sheet, click the Row 1 column header to select the entire row.
  1. On the Home tab, click the Copy button.
  1. Click the Base Scattergram sheet tab to switch to the Base Scattergram sheet.
  1. Click the Row 1 column header to select the entire row.
  1. On the Home tab, click the Paste button.

Repeat the process for Column A by:

  1. Click the Base Schedule sheet tab to switch to the Base Schedule sheet.
  1. Click the Column A header to select the entire column.
  1. On the Home tab, click the Copy button.
  1. Click the Base Scattergram sheet tab to switch to the Base Scattergram sheet.
  1. Click the Row 1 column header to select the entire row.
  1. On the Home tab, click the Paste button.

Creating the Salary Model

Once the Base Schedule amounts and Base Scattergram counts have been entered, the model can be created by:

  1. Click the Add Ins tab.
  1. Click the Analyze This button.

From the Options window:

  1. In the Name of Contract box, enter a name for the contract.
  1. In the Number of Years to Project, enter the number of years you wish to model.
  1. In the How is the Salary to be Increased, select whether the amount should be percentage of the previous salary or a flat-dollar amount.
  1. In the Method of Increase, select if you wish the increase amount to be applied to all of the cells or only to the base cell. If only the base cell is increased, the other cells will be increased based on their index to the base cell.
  1. In the Scattergram section, check whether you wish to have people moved up one step for every year of the model or not.
  1. Click the OK button when finished.
  1. It may take a few minutes for the model to be created.
  1. Click the OK button when told the processing is complete.

Using the Salary Model

The Summary sheet displays the total amounts for the entire contract along with the totals for each contract year.

You can enter a percent of increase for each year of the model in the % Increase row. TRS, THIS and OTHER amounts can also be specified for each year of the contract.

% Increase

For each year of the contract, you can specify how much the salaries should increase as a percentage.

The Total For Contract column is the average for all of the years in the model.

Number of Staff

Displays the number of staff included in the model.

Cost of Step Increase

Displays the Step cost of the contract: how much it is costing solely from people moving up a step or seniority level each year. This amount does not include any Percent Increase that may be specified.

Cost of Percent Increase

Displays the cost of providing the staff a specified percent increase in salary.

Total Salary Cost

Displays the total for just the salary amounts. This amount does not include TRS, THIS or Other amounts.

Cost of TRS Increase

Displays the total cost of the TRS amount.

Cost of THIS Increase

Displays the total cost of the THIS amount.

Cost of Other Increase

Displays the total cost of the Other amount.

Total Increase Cost

The difference between the selected year’s total cost and the previous year’s.

Total Cost For Year

Displays the total cost of the contract for the year.

Summary Sheet

  1. The salary increase amount, TRS, THIS and Other amounts can be specified for each year.
  1. Detail pages can be displayed by either clicking the Year heading at the top of the sheet or by using the Sheet Tabs at the bottom of the page.

Salary Schedule Sheet

The Salary Schedule sheet for each year will show the amount the salary has increased and what the new salary amount is with the increase.
You can return to the Summary sheet by clicking any of the % Increase headings at the top of the page.

Schedule Analysis Sheet

Displays the percentage of increase from step to step in the salary schedule.

This percentage of increase value is arrived at by:

  1. Calculate the index amount for each step by dividing the current step’s value by the first step, or base amount, of the schedule.
  1. Subtracting the previous step’s index from the current step gives the percent of increase.

Scattergram Sheet

Displays the number of people for each category at each step.
By default, the model will automatically move people up one step in each year of the model.

Compensation Sheet

The Compensation sheet for each year will show the total compensation which includes the previous salary amount, the amount of salary increase, TRS, THIS and the Other amount.

Total Cost Sheet

Displays the cost of the schedule, which is calculated by multiplying the number of people in the Scattergram sheet against each of the categories on the Compensation sheet..

You can return to the Summary sheet by clicking any of the % Increase headings at the top of the page.

Cost Analysis Sheet

For each cell, displays its percentage of the total cost.

This percent of the total cost is calculated by dividing the cell’s cost amount by the total cost for the year.

Modifying the Model

The spreadsheets created by the model are protected in an effort to prevent the formulas and links from being changed by an errant, unintentional misclick of the mouse. Be default, the only items you can change are the percentage amounts on the Summary sheet.

You must turn off the protection before you can modify any of the items on a sheet. This is accomplished by:

  1. On the Review tab, click the Unprotect Sheet button.

You will want exercise caution as changing some items may cause the model to not function or to incorrectly report amounts.

Remember, any changes you make will need to be manually applied to the following years.

Freezing Panes

The Freeze Panes option allows you to lock the first row in place so it remains at the top of the screen regardless of how far down the sheet you scroll.

To freeze items in place:

  1. From the View tab, click the Freeze Panes button.
  1. Click the FreezePanes item.

To remove the frozen panes:

  1. From the View tab, click the Freeze Panes button.
  1. Click the UnfreezePanes item.

TRS Factors

From the TRS Employer Guide at: http://trs.illinois.gov/subsections/employers/pubs/employerguide/guide.htm

Yearly Salary Schedule AnalysisPage 1