Data for these worksheets
will come from the personnel department, or from your personnel
records. Except for the rates for statutory insurance, there is
no data which comes from outside the organization.
More than any of the other templates, this template is merely
the outline of a structure. Each employment situation is different,
and probably will require some modification of the basic template.
Items may be deleted, new items added, or existing entries renamed.
If you add new items, remember to add them into the appropriate
subtotals and grand total calculations.
The first worksheet enters current expense information into the
spreadsheet (Figure 1). It is a straightforward description of
payroll and fringe benefit expenses. The worksheet is set up for
three categories of employees, based on wage rate. If your organization
is more complex, additional groups may be added. The worksheet
will calculate totals for each entry, subtotals for each section,
total payroll, total employment costs, and historical rate of
"add on" costs.
CURRENT COST INFORMATION NUMBER NUMBER NUMBER DOLLAR TOTAL EMPLOYEES HOURS WEEKS RATE EARNINGS 1. STRAIGHT TIME REGULAR WEEK GRP 1 ...... ...... ...... ...... xxxxx GRP 2 ...... ...... ...... ...... xxxxx GRP 3 ...... ...... ...... ...... xxxxx S.T. OVERTIME GRP 1 ...... ...... ...... ...... xxxxx GRP 2 ...... ...... ...... ...... xxxxx GRP 3 ...... ...... ...... ...... xxxxx TOTAL STRAIGHT TIME xxxxx 2. OVERTIME PREMUM GRP 1 ...... ...... ...... ...... xxxxx GRP 2 ...... ...... ...... ...... xxxxx GRP 3 ...... ...... ...... ...... xxxxx SUBTOTAL xxxxx 3. SHIFT PREMIUM GRP 1 ...... ...... ...... ...... xxxxx GRP 2 ...... ...... ...... ...... xxxxx GRP 3 ...... ...... ...... ...... xxxxx SUBTOTAL xxxxx 4. HOLIDAYS GRP 1 ...... ...... ...... ...... xxxxx GRP 2 ...... ...... ...... ...... xxxxx GRP 3 ...... ...... ...... ...... xxxxx SUBTOTAL xxxxx 5. VACATIONS GRP 1 ...... ...... ...... ...... xxxxx GRP 2 ...... ...... ...... ...... xxxxx GRP 3 ...... ...... ...... ...... xxxxx SUBTOTAL xxxxx 6. SEVERANCE PAY GRP 1 ...... ...... ...... ...... xxxxx GRP 2 ...... ...... ...... ...... xxxxx GRP 3 ...... ...... ...... ...... xxxxx SUBTOTAL xxxxx 7. TOTAL ANNUAL PAYROLL xxxxx * * * * * * * * * * * * * * * RATE #COVERED COST 8. STATUTORY INS. WORKERS COMP. ...... ...... xxxxx SOCIAL SECURITY ...... ...... xxxxx UNEMPLOYMENT ...... ...... xxxxx SUBTOTAL xxxxx 9. HEALTH INS. ...... ...... xxxxx 10. GROUP LIFE INS. GRP 1 ...... ...... xxxxx GRP 2 ...... ...... xxxxx GRP 3 ...... ...... xxxxx SUBTOTAL xxxxx 11. PENSION PLAN ...... ...... xxxxx * * * * * * * * * * * * * * * TOTAL EMPLOYMENT COSTS xxxxx HISTORICAL "ADD ON" COSTS xxxxx Figure 1
The second worksheet calculates the cost of changes proposed in
the bargaining process (Figure 2). It begins with a table for
entering the demands that have been built into the worksheet.
This table is solely for documenting what is being done elsewhere;
the spreadsheet does not use any of the information from this
table. The rest of the worksheet duplicates the Current Cost Information
worksheet, and adds two new items. While the CURRENT EARNINGS
column is brought forward from the Current Cost Information worksheet,
it is moved over one column, and now is bracketed by a column
for PROPOSED EARNINGS and for INCREASED COSTS. The information
in all the other columns is brought forward from the Current Cost
Information worksheet. In Figure 2, this is indicated by a string
of asterisks (***) Changes from the current contract are made
by typing the proposed value into the appropriate location(s)
in the worksheet. The worksheet will recalculate the item and
enter the new value in the PROPOSED EARNINGS column. The INCREASED
COSTS column will reflect the difference between the proposed
and current earnings. If no changes are made to the components
of an item (rate, number of hours, etc.), PROPOSED and CURRENT
EARNINGS will be identical and INCREASED COSTS will be "0."
The Worksheet will calculate the total increase in costs as well
as the total cost of the new contract and the actual rate of "add
on" costs.
Contract Cost Calculations DEMANDS: 1 .................. 2 .................. 3 .................. 4 .................. 5 .................. COST OF DEMANDS: NO. NO. NO. $ PROP CURNT INCR EMPLOY HOURS WEEKS RATE EARN EARN COST 1. STRAIGHT TIME REGULAR WEEK GRP 1 **** **** **** **** xxxx **** xxxx GRP 2 **** **** **** **** xxxx **** xxxx GRP 3 **** **** **** **** xxxx **** xxxx S.T. OVERTIME GRP 1 **** **** **** **** xxxx **** xxxx GRP 2 **** **** **** **** xxxx **** xxxx GRP 3 **** **** **** **** xxxx **** xxxx TOTAL STRT. TM. xxxx **** xxxx 2. OVERTIME PREMIUM GRP 1 **** **** **** **** xxxx **** xxxx GRP 2 **** **** **** **** xxxx **** xxxx GRP 3 **** **** **** **** xxxx **** xxxx SUBTOTAL xxxx **** xxxx 3. SHIFT PREMIUM GRP 1 **** **** **** **** xxxx **** xxxx GRP 2 **** **** **** **** xxxx **** xxxx GRP 3 **** **** **** **** xxxx **** xxxx SUBTOTAL xxxx **** xxxx 4. HOLIDAYS GRP 1 **** **** **** **** xxxx **** xxxx GRP 2 **** **** **** **** xxxx **** xxxx GRP 3 **** **** **** **** xxxx **** xxxx SUBTOTAL xxxx **** xxxx 5. VACATIONS GRP 1 **** **** **** **** xxxx **** xxxx GRP 2 **** **** **** **** xxxx **** xxxx GRP 3 **** **** **** **** xxxx **** xxxx SUBTOTAL xxxx **** xxxx 6. SEVERANCE PAY GRP 1 **** **** **** **** xxxx **** xxxx GRP 2 **** **** **** **** xxxx **** xxxx GRP 3 **** **** **** **** xxxx **** xxxx SUBTOTAL xxxx **** xxxx 7. TOTAL ANNUAL PAYROLL xxxx **** xxxx * * * * * * * * PROPOSED CURRENT INCREASED RATE #COVERD COST COST COST 8. STATUTORY INS. WORKERS COMP **** **** xxxx **** xxxx SOCIAL SECURITY **** **** xxxx **** xxxx UNEMPLOYMENT **** **** xxxx **** xxxx SUBTOTAL xxxx **** xxxx 9. HEALTH INSURANC **** **** xxxx **** xxxx 10. GROUP LIFE INS GRP 1 **** **** xxxx **** xxxx GRP 2 **** **** xxxx **** xxxx GRP 3 **** **** xxxx **** xxxx SUBTOTAL xxxx **** xxxx 11. PENSION PLAN **** **** xxxx **** xxxx TOTAL INCREASE IN COSTS xxxx TOTAL COSTS UNDER NEW CONTRACT xxxx ACTUAL "ADD ON" COSTS xxxx Figure 2
The third worksheet (Figure 3) is used to set goals for the bargaining
team and to evaluate their achievements at the end of the negotiations.
It deals with economic and non-economic items separately. The
worksheet allows for eight items under each major division, but
the worksheet may be easily expanded or contracted. Each item
is listed and assigned a weight which reflects its relative importance
among the issues which will be on the table. The negotiating team
that decides what would be a realistic objective to be achieved
on that issue. The team would also decide a worst-case and best-case
position. It is important that the "optimistic" and
"pessimistic" positions be positions that the team is
willing to defend and can accept (or get accepted) as part of
the bargaining process. The team then decides on its initial position
going into the bargaining. The initial position need not be within
the range of objectives previously set (one is permitted some
posturing going into the negotiations). This information is all
recorded on the worksheet.
Performance Evaluation Schedule ECONOMIC ITEMS OBJECTIVES INITIAL RESULTS ITEM WEIGHT PESSMIST REALIST OPTMIST POSITION P R 0 SCORE 1 ...... ...... ...... ...... ...... .. .. .. xxxx 2 ...... ...... ...... ...... ...... .. .. .. xxxx 3 ...... ...... ...... ...... ...... .. .. .. xxxx 4 ...... ...... ...... ...... ...... .. .. .. xxxx 5 ...... ...... ...... ...... ...... .. .. .. xxxx TOTAL xxxx SUMMARIES x x x xxxx NON-ECONOMIC ITEMS OBJECTIVES INITIAL RESULTS ITEM WEIGHT PESSMIST REALIST OPTMIST POSITION P R 0 SCORE A ...... ...... ...... ...... ...... .. .. .. xxxx B ...... ...... ...... ...... ...... .. .. .. xxxx C ...... ...... ...... ...... ...... .. .. .. xxxx D ...... ...... ...... ...... ...... .. .. .. xxxx E ...... ...... ...... ...... ...... .. .. .. xxxx TOTAL xxxx SUMMARIES x x x xxxx Figure 3
After the negotiations are complete, the team evaluates its performance (or someone else does it for them) on each issue. The results of the negotiation are scored as being closest to the pessimistic ("P"), realistic ("R"), or optimistic ("O") objective originally set. A "1" is entered in the appropriate column of the worksheet. The spreadsheet then tallies a score for each item, multiplying the issue weight by the position achieved. A "pessimistic" position is scored as a "3'" a "realistic" position is scored as a "2," and an "optimistic" position is scored as a "1." The worksheet supplies a tally of the sum of the weights for each issue (this is also the lowest possible performance score), the number of issues resolved under each of the three conditions, and a total score. This process is the same for both Economic and Non-economic issues.