this is a bargaining spreadsheet assignment . Sorry, the pictures are little blurry, the second picture is continuation of picture 1

Final ($20he average salary $ 4,000,000.00 Ours 1 2 3 4 43,680.00 131,400.00 82,560.00 189,360.00 447,000.00 health total 37,200.00 111,240.00 70,320.00 160,920.00 379,680.00 cheaper health total 1 Benefit and compensation negotiation project Group member names 2 Presume that you have 3 100 employees working 40 hours per week earning an average of $40,000 per year 4 Very competitive market for compensation and benefits Monthly cost of benefits 6 Core insurance plan is 75% employer paid and 25% employee paid 7 Medical Ins. Costs Premium Empir Emplee 8 Monthly Employee only $243.00 $ 182.00 $ 9 90/10 in network Emp/Spouse $ 486.00 $ 385.00 $ O prescrip $10/20/30 Emp/Children an $450.00 $ 344.00 $ 11 5500/1K ded Family $702.00 $ 526.00 $ 12 13 Cheaper Employee only $206.00 $ 155.00 5 4 80/20 in network Emp/Spouse $412.00 $ 309.00 $ 15 prescrip$15/30/45 Emp/ChildrenFarr $390.00 $ 293.00 $ 6 S1k2k ded Family $596.00 $ 447.00 $ 17 No medical How much company pays employee mnthly to not take any ins 18 19 Dental Ins eo Delta Dental Core Employee only $ 20.00 $ 15.00 $ 1 check ups covered Emp/Spouse $ 40.00 $ 30.00 $ 2 cavities 85/15 Emp/Children an $ 44.00 $ 33.00 $ 23 orthodontics $ 84.00 $ 48.00 $ 24 25 Buy Down Delta Employee only $ 16.00 $ 12.00 $ 28 check ups covered Emp/Spouse $ 32.00 $ 24.00 $ 7 cavities 50/50 Emp/ChildrenFar $ 34.00 $ 25.00 $ 28 no orthodontics Family $ 50.00 $ 38.00 $ 19 No dental How much company pays employee mnthly to not take any ins. 80 1 Costs of possible add ons to above benefits Proposed 12 Termife insurance $10,000 13 Long-term disability $50,000 4 Extra vacation day sick day $20.000 per day 5 Current vacation is 2 weeks after 1 year, 3 weeks after 3 years: 4 weeks after 7 years 36 Keep 100 employees for every employee out your company saves $56,000 7 EAP available $10.000 18 Free coffee/sodas $15.000 29 Flextime around core Extra temps needed $50,000 0 In-house exercise center with staff $250,000 31 Dallas sports events $50,000 2 Legal Insurance $10.000 #3 Pet Insurance $10,000 4 Increase match in 401k $200,000 35 S2K child care subsidy 1/2 would use $100,000 6 Educational tuition reimbursement increase $10,000 37 Bonus plan with trip for 10 employees $80,000 Sheet1 Projected emp 61.00 20 S 121.00 30 $ 115.00 20 $ 176.00 30 $ credit S 51.50 $ 28.00 20 S 103.00 $ 58.00 30 S 97.00 $ 52.00 20 S 149.00 $ 80.00 30 S 250.00 total Emp # 5.00 Projected 20 $ 10.00 30 $ 11.00 20 $ 16.00 30 $ S 4.00 $ 3.00 20 S 8.00 $ 6.00 30 $ 9.00 $ 20 S 12.00 $ 10.00 30 $ 20.00 total Family 3,600.00 10,800.00 7,920.00 17.280.00 39,600.00 dental total 2,880.00 8,840.00 8,160.00 13.680 00 33.360.00 cheaper dental total 7.50 Teams agree to Life Ins LT Disability Vacation change in employee EAP Beverages Flestime Fitness Fac Cultural Mem Legalins Petins 401k match Chid care Tuition inc Bonus plan 51.50 $ 103.00 $ 97.00 $ 149.00 $ 28.00 56.00 52.00 80.00 250.00 total 20 $ 30 $ 20 $ 30 $ 37,200.00 111,240.00 70,320.00 160.920.00 379.680.00 cheaper health total Emp # 5.00 Projected 20 $ 10.00 30 $ 11.00 20 $ 16.00 30 $ $ 4.00 $ 3.00 20 $ 8.00 $ 6.00 30 $ 9.00 $ 7.50 20 $ 12.00 $ 10.00 30 $ 20.00 total 3,600.00 10,800.00 7.920.00 17.280.00 39,600.00 dental total 2.880.00 8,640.00 8,160.00 13,680 00 33.360.00 cheaper dental total Teams agree to 13 Cheaper Employee only $206.00 $ 155.00 $ 14 80/20 in network Emp/Spouse $412.00 $ 309.00 $ 15 prescrip $15/30/45 Emp/ChildrenFarr $390.00 $ 293.00 $ 16 $1k/2k ded Family $596.00 $ 447.00 $ 17 No medical How much company pays employee mnthly to not take any ins 18 19 Dental Ins 20 Delta Dental Core Employee only $ 20.00 $ 15.00 $ 21 check ups covered Emp/Spouse $ 40.00 $ 30.00 $ 22 cavities 85/15 Emp/ChildrenFarr $ 44,00 $ 33.00 $ 23 orthodontics Family $ 64.00 $ 48.00 $ 24 25 Buy Down Delta Employee only $ 16.00 $ 12.00 $ 26 check ups covered Emp/Spouse $ 32.00 $ 24.00 $ 27 cavities 50/50 Emp/ChildrenFarr $ 34.00 $ 25.00 $ 28 no orthodontics Family $ 50.00 $ 38.00 $ 29 No dental How much company pays employee mnthly to not take any ins. 30 31 Costs of possible add ons to above benefits Proposed 32 Term life insurance $10,000 33 Long-term disability $50,000 34 Extra vacation day sick day $20,000 per day 35 Current vacation is 2 weeks after 1 year: 3 weeks after 3 years, 4 weeks after 7 years 36 Keep 100 employees for every employee cut, your company saves $56,000 37 EAP available $10,000 38 Free coffee/sodas $15,000 39 Flextime around core Extra temps needed $50,000 40 In-house exercise center with staff $250.000 41 Dallas sports events $50,000 42 Legal Insurance $10,000 43 Pet Insurance $10,000 44 Increase match in 401k $200,000 45 $2k child care subsidy 1/2 would use $100,000 48 Educational tuition reimbursement increase $10,000 47 Bonus plan with trip for 10 employees $80,000 48 Company holiday party $10,000 49 As HR manager, your benefits include a 10% share of company's profits 50 Total potential benefits add ons $855,000 total 51 52 Employer cost of health insurance Take yellow or pink above 53 Employer cost of dental insurance if any Take yellow or pink above 54 Total cost of all benefits in case 55 56 Total costs of labor if $40,000 each for 100 employees 57 Costs of pay increase multiplied by number of people employed 58 Any labor savings from firing employees state as negative number 59 Total costs of contract as negotiated Life Ins LT Disability Vacation change in employee # EAP Beverages Flextime Fitness Fac Cultural Mem Legal ins Pet ins 401k match Child care Tuition inc Bonus plan Party $0.00 total benefits $0.00 Aqua- benefits Purple - Labor - $ 4,000,000.00 $4,000,000.00 Orange - Compensation total Final ($20he average salary $ 4,000,000.00 Ours 1 2 3 4 43,680.00 131,400.00 82,560.00 189,360.00 447,000.00 health total 37,200.00 111,240.00 70,320.00 160,920.00 379,680.00 cheaper health total 1 Benefit and compensation negotiation project Group member names 2 Presume that you have 3 100 employees working 40 hours per week earning an average of $40,000 per year 4 Very competitive market for compensation and benefits Monthly cost of benefits 6 Core insurance plan is 75% employer paid and 25% employee paid 7 Medical Ins. Costs Premium Empir Emplee 8 Monthly Employee only $243.00 $ 182.00 $ 9 90/10 in network Emp/Spouse $ 486.00 $ 385.00 $ O prescrip $10/20/30 Emp/Children an $450.00 $ 344.00 $ 11 5500/1K ded Family $702.00 $ 526.00 $ 12 13 Cheaper Employee only $206.00 $ 155.00 5 4 80/20 in network Emp/Spouse $412.00 $ 309.00 $ 15 prescrip$15/30/45 Emp/ChildrenFarr $390.00 $ 293.00 $ 6 S1k2k ded Family $596.00 $ 447.00 $ 17 No medical How much company pays employee mnthly to not take any ins 18 19 Dental Ins eo Delta Dental Core Employee only $ 20.00 $ 15.00 $ 1 check ups covered Emp/Spouse $ 40.00 $ 30.00 $ 2 cavities 85/15 Emp/Children an $ 44.00 $ 33.00 $ 23 orthodontics $ 84.00 $ 48.00 $ 24 25 Buy Down Delta Employee only $ 16.00 $ 12.00 $ 28 check ups covered Emp/Spouse $ 32.00 $ 24.00 $ 7 cavities 50/50 Emp/ChildrenFar $ 34.00 $ 25.00 $ 28 no orthodontics Family $ 50.00 $ 38.00 $ 19 No dental How much company pays employee mnthly to not take any ins. 80 1 Costs of possible add ons to above benefits Proposed 12 Termife insurance $10,000 13 Long-term disability $50,000 4 Extra vacation day sick day $20.000 per day 5 Current vacation is 2 weeks after 1 year, 3 weeks after 3 years: 4 weeks after 7 years 36 Keep 100 employees for every employee out your company saves $56,000 7 EAP available $10.000 18 Free coffee/sodas $15.000 29 Flextime around core Extra temps needed $50,000 0 In-house exercise center with staff $250,000 31 Dallas sports events $50,000 2 Legal Insurance $10.000 #3 Pet Insurance $10,000 4 Increase match in 401k $200,000 35 S2K child care subsidy 1/2 would use $100,000 6 Educational tuition reimbursement increase $10,000 37 Bonus plan with trip for 10 employees $80,000 Sheet1 Projected emp 61.00 20 S 121.00 30 $ 115.00 20 $ 176.00 30 $ credit S 51.50 $ 28.00 20 S 103.00 $ 58.00 30 S 97.00 $ 52.00 20 S 149.00 $ 80.00 30 S 250.00 total Emp # 5.00 Projected 20 $ 10.00 30 $ 11.00 20 $ 16.00 30 $ S 4.00 $ 3.00 20 S 8.00 $ 6.00 30 $ 9.00 $ 20 S 12.00 $ 10.00 30 $ 20.00 total Family 3,600.00 10,800.00 7,920.00 17.280.00 39,600.00 dental total 2,880.00 8,840.00 8,160.00 13.680 00 33.360.00 cheaper dental total 7.50 Teams agree to Life Ins LT Disability Vacation change in employee EAP Beverages Flestime Fitness Fac Cultural Mem Legalins Petins 401k match Chid care Tuition inc Bonus plan 51.50 $ 103.00 $ 97.00 $ 149.00 $ 28.00 56.00 52.00 80.00 250.00 total 20 $ 30 $ 20 $ 30 $ 37,200.00 111,240.00 70,320.00 160.920.00 379.680.00 cheaper health total Emp # 5.00 Projected 20 $ 10.00 30 $ 11.00 20 $ 16.00 30 $ $ 4.00 $ 3.00 20 $ 8.00 $ 6.00 30 $ 9.00 $ 7.50 20 $ 12.00 $ 10.00 30 $ 20.00 total 3,600.00 10,800.00 7.920.00 17.280.00 39,600.00 dental total 2.880.00 8,640.00 8,160.00 13,680 00 33.360.00 cheaper dental total Teams agree to 13 Cheaper Employee only $206.00 $ 155.00 $ 14 80/20 in network Emp/Spouse $412.00 $ 309.00 $ 15 prescrip $15/30/45 Emp/ChildrenFarr $390.00 $ 293.00 $ 16 $1k/2k ded Family $596.00 $ 447.00 $ 17 No medical How much company pays employee mnthly to not take any ins 18 19 Dental Ins 20 Delta Dental Core Employee only $ 20.00 $ 15.00 $ 21 check ups covered Emp/Spouse $ 40.00 $ 30.00 $ 22 cavities 85/15 Emp/ChildrenFarr $ 44,00 $ 33.00 $ 23 orthodontics Family $ 64.00 $ 48.00 $ 24 25 Buy Down Delta Employee only $ 16.00 $ 12.00 $ 26 check ups covered Emp/Spouse $ 32.00 $ 24.00 $ 27 cavities 50/50 Emp/ChildrenFarr $ 34.00 $ 25.00 $ 28 no orthodontics Family $ 50.00 $ 38.00 $ 29 No dental How much company pays employee mnthly to not take any ins. 30 31 Costs of possible add ons to above benefits Proposed 32 Term life insurance $10,000 33 Long-term disability $50,000 34 Extra vacation day sick day $20,000 per day 35 Current vacation is 2 weeks after 1 year: 3 weeks after 3 years, 4 weeks after 7 years 36 Keep 100 employees for every employee cut, your company saves $56,000 37 EAP available $10,000 38 Free coffee/sodas $15,000 39 Flextime around core Extra temps needed $50,000 40 In-house exercise center with staff $250.000 41 Dallas sports events $50,000 42 Legal Insurance $10,000 43 Pet Insurance $10,000 44 Increase match in 401k $200,000 45 $2k child care subsidy 1/2 would use $100,000 48 Educational tuition reimbursement increase $10,000 47 Bonus plan with trip for 10 employees $80,000 48 Company holiday party $10,000 49 As HR manager, your benefits include a 10% share of company's profits 50 Total potential benefits add ons $855,000 total 51 52 Employer cost of health insurance Take yellow or pink above 53 Employer cost of dental insurance if any Take yellow or pink above 54 Total cost of all benefits in case 55 56 Total costs of labor if $40,000 each for 100 employees 57 Costs of pay increase multiplied by number of people employed 58 Any labor savings from firing employees state as negative number 59 Total costs of contract as negotiated Life Ins LT Disability Vacation change in employee # EAP Beverages Flextime Fitness Fac Cultural Mem Legal ins Pet ins 401k match Child care Tuition inc Bonus plan Party $0.00 total benefits $0.00 Aqua- benefits Purple - Labor - $ 4,000,000.00 $4,000,000.00 Orange - Compensation total