Labor Agreements: Calculations

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.

609