Yearly Salary Schedule Analysis

Yearly Salary Schedule Analysis

Version 3.4

December 2008

Yearly Salary Schedule Analysis

Version 3.4

December 2008

Written by Tom Kulmacz

Table of Contents

General

Security Settings...... 1

Copying the Files to Your Computer...... 2

Yearly Salary Schedule Analysis

IEA Yearly Salary Schedule Analysis...... 3

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

Using the Salary Model...... 9

Modifying the Model...... 14

Freezing Panes...... 14

Security Settings

You may need to adjust the security settings in Excel to allow the macros to run.

This is accomplished in Excel by:

  1. Click the Tools pull-down menu.
  1. Select Macro.
  1. Select Security.

  1. Select the Medium option on the Security tab.
  1. Click the OK button.

When you open the templates, you will be asked if you wish to enable or disable macros.

You need to select Enable Macros for the templates to be able to run.

Urgent Warning! When you allow macros to run, you are giving complete access and control of your computer to the program. You should only enable macros that come from people whom you completely trust with your computer and data!!

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. Click the Edit pull-down menu.
  1. Click the Paste Special item.

If pasting from an Excel spreadsheet:

  1. Select the Values option in the Paste Special window.
  1. Click the OK button.

If pasting from a Word document:

  1. Select the Text option in the Paste Special window.
  1. Click the OK button.

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. Click the Edit pull-down menu.
  1. Click the Copy item.
  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. Click the Edit pull-down menu.
  1. Click the Paste item.

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. Click the Edit pull-down menu.
  1. Click the Copy item.
  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. Click the Edit pull-down menu.
  1. Click the Paste item.

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 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. Click the Tools pull-down menu.
  1. Click the Protection item.
  1. Click the Unprotect Sheet item.

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. Select the cell that is below and to the right of the items you want frozen in place.
  1. Click the Window pull-down menu and select FreezePanes.

To remove the frozen panes:

  1. Click the Window pull-down menu and select Unfreeze Panes.

TRS Factors

From the TRS Employer Guide at:

Yearly Salary Schedule AnalysisPage 1