Home Insect Page Layout General 5 Wrap Text Sort Filte A A Call Styles Format - X 19 Calibri (Body) 11 $ - 96 co Conditional Format Formatting as Table Marge & Center - A 1 B Paste U- 0 P 1 223200 PB Axfx L M K N B H D F E G 1 Background Information Mile's bhaust Worla produces efficient exhaust systems used on custom motorcycles. They sell their systems to other manufacturers, who build customized bikes to clients. Karl is the sales manager, and she has created the 2 following projected demand schedule for Tom, the production manager. 3 4 lan Feb Mar Apr May Jun lul Aug Sep Oct Nov Des 5 Projected Demand 15000 19000 20500 21000 22000 21000 21000 19500 18000 16200 15000 15000 6 7 Mike's Exhaust Currently has 50 tradesmen who each work 20 days a month, and can each produce 15 exhaust systems per day For Each problem sil in the cells with the RED "7" The GREEN CELLS are calculated cells. The BLACK CELLS are given data 223200 a) Asuming that the company is only concerned with aggregate yearly demand, should the labor force (workers) be changed? Enter the number of workers for each month until the yearly demand (223,200) equals the yearly 9 production you calculate by changing the number of workers. Note that the number of workers need to be the same for all months in this case when you are calculating the yearly production 10 Nov Feb 11 Sep Dec May Ma lan Aug Oct Apr 15000 12 Projected Demand 21000 19000 22000 20500 21000 15000 21000 15000 19500 16200 18000 ? 13 Workers ? ? 2 1? ? ? ? ? 7 ? 14 Work days 20 20 20 20 20 20 20 20 20 20 20 20 15 Units/Worker day 15 15 15 15 15 15 15 15 15 15 15 15 16 Units Produced #VALUE! #VALUE! HVALUE WVALUES UVALUE! #VALUE VALUE! IVALUE! #VALUE! VALUEI #VALUE! VALUE! 17 ? WVALUE b) Assuming that the company is only concerned with aggregate monthly demand, how should the labor force be changed to meet the monthly demand?(round to the nearest whole employee-monthly demand must be met, no 18 underproduction allowed), Enter the number of workers for each month to match each month's demand with the monthly production. Note that each month could have a different number of workers 19 20 0 Jar Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 21 Projected Demand 15000 19000 20500 21000 22000 21000 21000 19500 18000 16200 15000 15000 22 Workers ? ? ? ? 7 ? ? ? ? ? 23 Work days 20 20 20 20 20 20 20 20 20 20 20 20 24 Units/Worker day 15 15 15 15 15 15 15 15 15 15 15 15 25 Units Produced #VALUE VALUE VALUE WVALUE VALUE #VALUE! HVALUE HVALUE #VALUE! #VALUE! HVALUE #VALUE! 26 27 28 Sep Each time the company lays off an employee, the expenses for the layoff are $19,000. In this year, how much are the costs of layaffs? Enter the number of negative changes in workers when you lay off workers each month. This will 29 calculate the monthly and then the yearly cost to lay off workers. Feb Mar Apr May Jun Jul Aug Oct Noy Dec 31 Workers ? ? 2 ? ? ? ? ? ? ? ? W6 Assignment (CL03) + Ready ES 2 MacBook Pro Unit 6 Assignment Template Home Insert Page Layout Formulas Data Review View 2+ Share Calibri (Body) 11 A A Wrap Text General Insert - Paste 29 T Delete Merge & Centar - $ - % % ) Conditional Format Cell Formatting as Table Styles Format Sort & Filter P60 fx o P a B D E F G H 1 M N c) Each time the company lays off an employee, the expenses for the layoff are $19,000. In this year, how much are the costs oflayoffs? Enter the number of negative changes in workers when you lay off workers each month. This will 29 calculate the monthly and then the yearly cost to lay off workers 30 Jan Feb Mar Apr May Jun Jul Aug Oct Nov Des 31 Workers ? ? 7 ? ? 7 2 7 ? 7 32 Negative change in Workers from previous month? ? 2 ? ? 7 2 2 ? > 7 33 Cost $ 19,000 $ 19,000 $ 19,000 $ 19,000 S 19,000 $ 19,000 $ 19,000 $ 19,000 S 19,000 $ 19,000 $ 19,000 $ 19,000 34 Total Layoff Cost AVALUE! VALUE VALUE! #VALUE! HVALUE VALUE HVALUE VALUE VALUE HVALUE #VALUE! VALUEI 35 ? 2 VALLEI VALUE d) During busy times, the tradesmen at Mike's will happily work up to 50% overtime each month. Each worker makes $140 a day and overtime is 50% above standard rate. Using a fixed staff of 50 workers who are able to work 36 overtime calculate the number of equivalent workers needed per day to fill demand. (Hint:like "b" but do not round the number of equivalent workers, go to the second decimal point X.XO) 37 38 lan Feb Mar Apr May Jun Aug Sep Oct Nov Dec 39 Projected Demand 15000 19000 20500 21000 22000 21000 21000 19500 18000 16200 15000 15000 40 Equivalent Workers" ? > ? 2 2 ? ? ? 7 7 2 41 Work days 20 20 20 20 20 20 20 20 20 20 20 20 42 Units/Worker day 15 15 11 15 15 15 15 15 15 15 15 15 43 Units Produced IVALUE WALUE LINAVE WVALUE WVALUE VALUEI #VALUE! #VALUE! AVALUE #VALUE VALUE! VALUE! 44 45 e) What is the overtime wage cost in the above example Id)? 46 47 Jan Feb Mar Apr May Jun lul Aug Sep Noy Dec 48 Equivalent Workers from problem b'above 2 7 50 50 SO 50 50 49 Actual Workers 50 SO 50 50 50 50 SO VALUE WALDE INALUL VALUE VALUE VALUE HVALUL So Variation in Actualto Equivalent Works VALUE VALUE IVALUE VALUE 20 20 20 20 20 20 51 Workdays/month 20 20 20 20 20 20 2 2 7 2 52 Overtime Cost/day per worker above the $140/day VALUE WALDE HVALUE VALUE VALDE VALUE HUNLUR WALUE WALUE 53 Tatal Overtime cost VALUE HALUE VALUE 54 55 ) Compare this overtime wage cost to the layoff cost incl. Which is better for the employer, Mike Mufflers? Explain why. 56 S7 Total Lay-off Cost to Mrs >> VALUE VALUE 58 Total overtime Cost to the WALUE b) The tradesmen at Mike's are in a union, and would like to eliminate the cycle of temporary hires and layoffs. They are will to negotiate their overtime wage so that it does not benefit the company to continue with this process. The 59 current overtime wage is time +50% What should the union negotiate to change it to? 60 W6 Assignment (CLO3) + TE Ready 100 MacBook Pro Unit 6 Assignment Template Home Insert Page Layout Formulas Data Review View X Calibri (Body) - 11 A A- Wrap Text General Insert - Paste B I U- WD 49 Merge & Center $ % 2 Dalete - 4.0 .00 . 19 X Conditional Format Formatting as Table Call Styles Format Sort & Filter G H 1 1 K L M N o P B C D E F Compare this overtime wage cost to the layoff cost in (c). Which is better for the employer, Mike Mufflers? Explain why. Total Lay-off Cost to Mike's Mufflers > #VALUE! Total overtime Cost to Mike's Mufflers => #VALUE! 2) The tradesmen at Mike's are in a union, and would like to eliminate the cycle of temporary hires and layoffs. They are will to negotiate their overtime wage so that it does not benefit the company to continue with this process. The current overtime wage is time +50%. What should the union negotiate to change it to? Jan Jul ? Sep ? Equivalent Workers from problem 'b'above Actual Workers Wariation in Actualto Equivalent Workers Norkdays/month Overtime Cost/day per worker above the $140/day Cotal Overtime cost 2 50 EVALUE 20 Feb ? 50 VALUE 20 ? IVALUE! Mar 2 50 IVALUE! 20 Apr ? 50 UVALUE 20 7 UVALUE Mary 2 50 #VALUE! 20 ? VALUE Jun ? 50 #VALUE! 20 7 50 #VALUE! 20 ? VALUEI Aug 2 50 #VALUE! 20 ? EVALUE 50 #VALUE! 20 Oct 2 50 AVALUE! 20 ? VALUE Nov Des 2 50 50 ITVALUE IVALUE 20 20 ? ? ? #VALUE! VALUE VALUE HVALUE VALUE IPVALUE VALUE Total Lay-off Cost to Mike's Mufflers => New Total Overtime Cost to Mike's Mufflers-> AVALUE INVALUE Type your answer => Jin your opinion, would the union what to do this? Type your answer-> W6 Assignment (CLO3) + sady Q- Search Sheet Unit 6 Assignment Template 9+ Share Data Formulas View Review Homd Page Layout Insert P O 223200 15000 P4 c N D B M E 1 T G H L K Feb Apr Jun Mary Ma Aug Dec Oct See Nov 19000 20500 5 Projected Demand 21000 22000 21000 21000 19500 18000 15000 16200 15000 6 7 Mile's Exhaust Currently has 50 tradesmen who each work 20 days a month, and can each produce 15 exhaust systems per day, B For Each problem fill in the cells with the RED "7"The GREEN CELLS are calculated cells, The BLACK CELLS are even data a) Assuming that the company is only concerned with aggregate yearly demand, should the labor force (workers) bechanged? Enter the number of workers for each month until the yearly demand (223,200) equals the yearly 9 production you calculate by changing the number of workers. Note that the number of workers need to be the same for all months in this case when you are calculating the yearly production 10 11 Jan Feb Mar M Jun Jul AUE Sep Oct Nov Des 12 Projected Demand 15000 19000 20500 22000 21000 21000 19500 18000 16200 15000 15000 13 Workers ? 2 2 2 7 ? > 2 ? ? ? 14 Work days 20 20 20 20 20 20 20 20 20 20 20 15 Units/Worker day 15 15 15 15 15 15 15 15 15 15 15 16 Units Produced HVALUE VALUE WALUERIA #VALUE! VALUE VALUE HVALUE #VALUE! VALUE! HVALUE 17 223200 Apr 21000 20 -> #VALUE! VALUE b) Assuming that the company is only concerned with pregate monthly demand, how should the labor force be changed to meet the monthly demand?round to the nearest whole employee monthly demand must be met, no 18 underproduction allowed) Enter the number of workers for each month to match exch month's demand with the monthly production. Note that each month could have a different number of workers. 19 20 0 Jan Feb Apr May Jun AUE Sep Oct Now Des 21 Projected Demand 15000 19000 20500 21000 22000 21000 21000 19500 18000 16200 15000 15000 2 22 Workers 7 ? 2 7 ? 2 2 2 23 Work days 20 20 20 20 120 20 20 20 20 20 20 20 24 Units/Worker day 15 15 15 15 15 15 15 15 15 15 15 25 Units Produced WVALUE #VALUES AVALUE VALUE KVALUE VALUE HVALUE! HVALUE! VALUE HVALUE VALUE VALUE 26 27 28 c) Each time the company lays off an employee the expenses for the layoff are $19.000. In this year, how much are the costs of layoffs? Enter the number of negative changes in workers when you lay off workers each month. This will 29 calculate the monthly and then the yearly cost to lay off workers 30 lan Feb Mar Apr May ANE Nov Dec 31 Workers 7 ? 7 7 7 32 Negative change in Workers from previous month? 2 2 7 7 ? 7 2 33 Cost 19,000 5 19 000 $ 19.000 5 19.000 S 19.000 $ 19 000 $ 19.000 19.000 5 19,000 5 19,000 $ 19,000 $ 19,000 34 Total Layoff Cost LUL VALUE WALUE WALUL WALUL WALU VALUE VALUE 35 2 WALUE VALUE d) During busy times, the tradesmen & Mike's will happily work up to 50% overtime each month Each worker makes 5140 a day and overtime is 50% above standardate. Using a fixed staff of 50 workers who are able to work 36 overtime calculate the number of equivalent workers needed per day to fill demand. Hintlike but do not round the number of equivalent workers, go to the second decimal point XXX 37 Ost Feb Mar lan 38 AU Apr May un Sep Des wa RA W6 Assignment (CL03) + Ready BH , + 100% MacBook Pro P73 X fx M N 0 0 K P R D L H E F G IVALUE! c) During busy times, the tradesmen at Mike's will happily work up to 50 overtime each month. Each worker makes $140 aday and overtime is 50% above standard rate. Using a fixed staff of 50 workers who are able to work 36 overtime calculate the number of equivalent workers needed per day to fill demand. (Hint:like "b"but do not round the number of equivalent workers to the second decimal point X.XX) 37 38 lan Feb MAT Apr May lun Jul Aug Sep Nov Dec 39 Projected Demand 15000 19000 20500 21000 22000 21000 21000 19500 18000 16200 15000 15000 40 Equivalent Workers ? ? 7 13 ? ? 7 7 2 7 ? ? 41 Work days 20 20 20 20 20 20 20 20 20 20 20 20 42 Units/Worker day 15 15 15 15 15 15 15 15 15 15 15 15 43 Units Produced VALUEI FVALUE! HVALUE! WVALUES HVALUE! #VALUE! #VALUE! VALUE! #VALUE VALUES IVALUE! #VALUE! 44 45 eWhat is the overtime wage cost in the above example id ? 46 Jan Feb Mar Apr May Jun lu! Aug Sep Oct Nov Dec 48 Equivalent Workers from problem above 7 2 ? 2 2 2 M ? 2 49 Actual Workers 50 50 SO 50 50 50 SO 50 50 SO 50 So Variation in Actualto Equivalent Workers VALUE! #VALUE! VALUE HVALUE! VALUE! VALUE #VALUE! VALUE VALUE #VALUE! VALUE 51 Workdays/month 20 20 20 20 20 20 20 20 20 20 20 20 52 Overtime Cost/day per worker above the $140/day 7 ? ? 2 7 2 ? 53 Total Overtime cost VALUE #VALUE! #VALUE! #VALUE! VALUE! IVALUE VALUE HVALUE VALUE IVALUE HVALUE #VALUE! 54 55 Compare this overtime wage cost to the layoff cost inc). Which is better for the employer, Mike Mufflers? Explain why 56 57 Total Layoff Cost to Mike's Muffler-> #VALUE! 58 Total overtime Cost to Mike's Muffin WVALUE som INVALUE ? 2 e) The tradesmen at Mike's are in a union, and would like to eliminate the cycle of temporary hires and layoffs. They are will to negotiate their overtime wage so that it does not benefit the company to continue with this process. The 59 current overtime wage is time +50% What should the union negotiate to change it to? 60 61 Feb Mac Apr May Jun jul ALE Serg Oct Nov Dec 62 Equivalent Workers from problem b'above 7 2 7 2 63 Actual Workers 50 SO 50 50 SO 30 50 50 50 64 Variation in Actualto Equivalent Workers WALUE WVALUE VALUE VALUE VALUR MALU HVALUE WALUE #VALUE! #VALUE! 65 Workday/month 20 20 20 20 20 20 20 20 20 66 Overtime Cost/day per worker above the $140/day 7 67 Total Overtime cost VALUE VALUE WALUL HVALUE UVALUE VALLE WVALUE VALUE VALUE VALUE 68 69 TotalLay-off Cost to Mike's Mures INALUE 70 New Total Overtime Cost to Mike's Mutflers => VALUE 71 72 Type your answer 73 #VALUE! W6 Assignment (CL03) + Ready 100 Q- search sheet Unit 6 Assignment Template 9+ Share N o P M H 1 1 K G Home Insert Page Layout Formulas Data Review View p94 4 x fx B D E F 55 Compare this overtime wage cost to the layoff cost in (d. Which is better for the employer, Mike Mufflers? Explain why 56 57 Total Layoff Cost to Mike's Mufflers #VALUE! S8 Total overtime Cost to Mile's Mufflers > #VALUE! ? VALGE #VALUE! ) The tradesmen at Mike's are in a union, and would like to eliminate the cycle of temporary hires and layoffs. They are will to negotiate their overtime wage so that it does not benefit the company to continue with this process. The 59 current overtime wage is time +50%. What should the union negotiate to charge it to? 60 Feb May Mar Jun Oct Aur Apr Sep Nov 61 Dec 29 ? ? 7 2 62 Equivalent Workers from problem 'b'above ? 50 50 50 50 SO 50 SO 50 50 50 50 SO 63 Actual Workers WALDE #VALUE SVALLEI HVALUE WALUE #VALUE! UVALUFI VALUE VALUE VALUEI #VALUE! 64 Variation in Actualto Equivalent Workers 20 20 20 20 20 20 20 20 20 20 20 20 65 Workdays/month 7 ? 2 2 2 ? 2 66 Overtime Cost/day per worker above the 5140/day WALLELLE. VALUE VALUEI VALUE VALUE #VALUE AVALUE VALUE VALUE WVALUE VALUE 67 Total Overtime cost 68 69 Total Layoff Cost to Mike's Mufflers > VALUE 70 New Total Overtime Cost to Mike's Mufflers >> VALUE 71 72 Type your answer >> 73 74 h) in your opinion, would the union what to do this? 75 Type your answer >> 76 77 78 79 80 81 B2 83 84 85 86 87 88 89 90 91 92 93 W6 Assignment (CLO3) + Ready Home Insect Page Layout General 5 Wrap Text Sort Filte A A Call Styles Format - X 19 Calibri (Body) 11 $ - 96 co Conditional Format Formatting as Table Marge & Center - A 1 B Paste U- 0 P 1 223200 PB Axfx L M K N B H D F E G 1 Background Information Mile's bhaust Worla produces efficient exhaust systems used on custom motorcycles. They sell their systems to other manufacturers, who build customized bikes to clients. Karl is the sales manager, and she has created the 2 following projected demand schedule for Tom, the production manager. 3 4 lan Feb Mar Apr May Jun lul Aug Sep Oct Nov Des 5 Projected Demand 15000 19000 20500 21000 22000 21000 21000 19500 18000 16200 15000 15000 6 7 Mike's Exhaust Currently has 50 tradesmen who each work 20 days a month, and can each produce 15 exhaust systems per day For Each problem sil in the cells with the RED "7" The GREEN CELLS are calculated cells. The BLACK CELLS are given data 223200 a) Asuming that the company is only concerned with aggregate yearly demand, should the labor force (workers) be changed? Enter the number of workers for each month until the yearly demand (223,200) equals the yearly 9 production you calculate by changing the number of workers. Note that the number of workers need to be the same for all months in this case when you are calculating the yearly production 10 Nov Feb 11 Sep Dec May Ma lan Aug Oct Apr 15000 12 Projected Demand 21000 19000 22000 20500 21000 15000 21000 15000 19500 16200 18000 ? 13 Workers ? ? 2 1? ? ? ? ? 7 ? 14 Work days 20 20 20 20 20 20 20 20 20 20 20 20 15 Units/Worker day 15 15 15 15 15 15 15 15 15 15 15 15 16 Units Produced #VALUE! #VALUE! HVALUE WVALUES UVALUE! #VALUE VALUE! IVALUE! #VALUE! VALUEI #VALUE! VALUE! 17 ? WVALUE b) Assuming that the company is only concerned with aggregate monthly demand, how should the labor force be changed to meet the monthly demand?(round to the nearest whole employee-monthly demand must be met, no 18 underproduction allowed), Enter the number of workers for each month to match each month's demand with the monthly production. Note that each month could have a different number of workers 19 20 0 Jar Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 21 Projected Demand 15000 19000 20500 21000 22000 21000 21000 19500 18000 16200 15000 15000 22 Workers ? ? ? ? 7 ? ? ? ? ? 23 Work days 20 20 20 20 20 20 20 20 20 20 20 20 24 Units/Worker day 15 15 15 15 15 15 15 15 15 15 15 15 25 Units Produced #VALUE VALUE VALUE WVALUE VALUE #VALUE! HVALUE HVALUE #VALUE! #VALUE! HVALUE #VALUE! 26 27 28 Sep Each time the company lays off an employee, the expenses for the layoff are $19,000. In this year, how much are the costs of layaffs? Enter the number of negative changes in workers when you lay off workers each month. This will 29 calculate the monthly and then the yearly cost to lay off workers. Feb Mar Apr May Jun Jul Aug Oct Noy Dec 31 Workers ? ? 2 ? ? ? ? ? ? ? ? W6 Assignment (CL03) + Ready ES 2 MacBook Pro Unit 6 Assignment Template Home Insert Page Layout Formulas Data Review View 2+ Share Calibri (Body) 11 A A Wrap Text General Insert - Paste 29 T Delete Merge & Centar - $ - % % ) Conditional Format Cell Formatting as Table Styles Format Sort & Filter P60 fx o P a B D E F G H 1 M N c) Each time the company lays off an employee, the expenses for the layoff are $19,000. In this year, how much are the costs oflayoffs? Enter the number of negative changes in workers when you lay off workers each month. This will 29 calculate the monthly and then the yearly cost to lay off workers 30 Jan Feb Mar Apr May Jun Jul Aug Oct Nov Des 31 Workers ? ? 7 ? ? 7 2 7 ? 7 32 Negative change in Workers from previous month? ? 2 ? ? 7 2 2 ? > 7 33 Cost $ 19,000 $ 19,000 $ 19,000 $ 19,000 S 19,000 $ 19,000 $ 19,000 $ 19,000 S 19,000 $ 19,000 $ 19,000 $ 19,000 34 Total Layoff Cost AVALUE! VALUE VALUE! #VALUE! HVALUE VALUE HVALUE VALUE VALUE HVALUE #VALUE! VALUEI 35 ? 2 VALLEI VALUE d) During busy times, the tradesmen at Mike's will happily work up to 50% overtime each month. Each worker makes $140 a day and overtime is 50% above standard rate. Using a fixed staff of 50 workers who are able to work 36 overtime calculate the number of equivalent workers needed per day to fill demand. (Hint:like "b" but do not round the number of equivalent workers, go to the second decimal point X.XO) 37 38 lan Feb Mar Apr May Jun Aug Sep Oct Nov Dec 39 Projected Demand 15000 19000 20500 21000 22000 21000 21000 19500 18000 16200 15000 15000 40 Equivalent Workers" ? > ? 2 2 ? ? ? 7 7 2 41 Work days 20 20 20 20 20 20 20 20 20 20 20 20 42 Units/Worker day 15 15 11 15 15 15 15 15 15 15 15 15 43 Units Produced IVALUE WALUE LINAVE WVALUE WVALUE VALUEI #VALUE! #VALUE! AVALUE #VALUE VALUE! VALUE! 44 45 e) What is the overtime wage cost in the above example Id)? 46 47 Jan Feb Mar Apr May Jun lul Aug Sep Noy Dec 48 Equivalent Workers from problem b'above 2 7 50 50 SO 50 50 49 Actual Workers 50 SO 50 50 50 50 SO VALUE WALDE INALUL VALUE VALUE VALUE HVALUL So Variation in Actualto Equivalent Works VALUE VALUE IVALUE VALUE 20 20 20 20 20 20 51 Workdays/month 20 20 20 20 20 20 2 2 7 2 52 Overtime Cost/day per worker above the $140/day VALUE WALDE HVALUE VALUE VALDE VALUE HUNLUR WALUE WALUE 53 Tatal Overtime cost VALUE HALUE VALUE 54 55 ) Compare this overtime wage cost to the layoff cost incl. Which is better for the employer, Mike Mufflers? Explain why. 56 S7 Total Lay-off Cost to Mrs >> VALUE VALUE 58 Total overtime Cost to the WALUE b) The tradesmen at Mike's are in a union, and would like to eliminate the cycle of temporary hires and layoffs. They are will to negotiate their overtime wage so that it does not benefit the company to continue with this process. The 59 current overtime wage is time +50% What should the union negotiate to change it to? 60 W6 Assignment (CLO3) + TE Ready 100 MacBook Pro Unit 6 Assignment Template Home Insert Page Layout Formulas Data Review View X Calibri (Body) - 11 A A- Wrap Text General Insert - Paste B I U- WD 49 Merge & Center $ % 2 Dalete - 4.0 .00 . 19 X Conditional Format Formatting as Table Call Styles Format Sort & Filter G H 1 1 K L M N o P B C D E F Compare this overtime wage cost to the layoff cost in (c). Which is better for the employer, Mike Mufflers? Explain why. Total Lay-off Cost to Mike's Mufflers > #VALUE! Total overtime Cost to Mike's Mufflers => #VALUE! 2) The tradesmen at Mike's are in a union, and would like to eliminate the cycle of temporary hires and layoffs. They are will to negotiate their overtime wage so that it does not benefit the company to continue with this process. The current overtime wage is time +50%. What should the union negotiate to change it to? Jan Jul ? Sep ? Equivalent Workers from problem 'b'above Actual Workers Wariation in Actualto Equivalent Workers Norkdays/month Overtime Cost/day per worker above the $140/day Cotal Overtime cost 2 50 EVALUE 20 Feb ? 50 VALUE 20 ? IVALUE! Mar 2 50 IVALUE! 20 Apr ? 50 UVALUE 20 7 UVALUE Mary 2 50 #VALUE! 20 ? VALUE Jun ? 50 #VALUE! 20 7 50 #VALUE! 20 ? VALUEI Aug 2 50 #VALUE! 20 ? EVALUE 50 #VALUE! 20 Oct 2 50 AVALUE! 20 ? VALUE Nov Des 2 50 50 ITVALUE IVALUE 20 20 ? ? ? #VALUE! VALUE VALUE HVALUE VALUE IPVALUE VALUE Total Lay-off Cost to Mike's Mufflers => New Total Overtime Cost to Mike's Mufflers-> AVALUE INVALUE Type your answer => Jin your opinion, would the union what to do this? Type your answer-> W6 Assignment (CLO3) + sady Q- Search Sheet Unit 6 Assignment Template 9+ Share Data Formulas View Review Homd Page Layout Insert P O 223200 15000 P4 c N D B M E 1 T G H L K Feb Apr Jun Mary Ma Aug Dec Oct See Nov 19000 20500 5 Projected Demand 21000 22000 21000 21000 19500 18000 15000 16200 15000 6 7 Mile's Exhaust Currently has 50 tradesmen who each work 20 days a month, and can each produce 15 exhaust systems per day, B For Each problem fill in the cells with the RED "7"The GREEN CELLS are calculated cells, The BLACK CELLS are even data a) Assuming that the company is only concerned with aggregate yearly demand, should the labor force (workers) bechanged? Enter the number of workers for each month until the yearly demand (223,200) equals the yearly 9 production you calculate by changing the number of workers. Note that the number of workers need to be the same for all months in this case when you are calculating the yearly production 10 11 Jan Feb Mar M Jun Jul AUE Sep Oct Nov Des 12 Projected Demand 15000 19000 20500 22000 21000 21000 19500 18000 16200 15000 15000 13 Workers ? 2 2 2 7 ? > 2 ? ? ? 14 Work days 20 20 20 20 20 20 20 20 20 20 20 15 Units/Worker day 15 15 15 15 15 15 15 15 15 15 15 16 Units Produced HVALUE VALUE WALUERIA #VALUE! VALUE VALUE HVALUE #VALUE! VALUE! HVALUE 17 223200 Apr 21000 20 -> #VALUE! VALUE b) Assuming that the company is only concerned with pregate monthly demand, how should the labor force be changed to meet the monthly demand?round to the nearest whole employee monthly demand must be met, no 18 underproduction allowed) Enter the number of workers for each month to match exch month's demand with the monthly production. Note that each month could have a different number of workers. 19 20 0 Jan Feb Apr May Jun AUE Sep Oct Now Des 21 Projected Demand 15000 19000 20500 21000 22000 21000 21000 19500 18000 16200 15000 15000 2 22 Workers 7 ? 2 7 ? 2 2 2 23 Work days 20 20 20 20 120 20 20 20 20 20 20 20 24 Units/Worker day 15 15 15 15 15 15 15 15 15 15 15 25 Units Produced WVALUE #VALUES AVALUE VALUE KVALUE VALUE HVALUE! HVALUE! VALUE HVALUE VALUE VALUE 26 27 28 c) Each time the company lays off an employee the expenses for the layoff are $19.000. In this year, how much are the costs of layoffs? Enter the number of negative changes in workers when you lay off workers each month. This will 29 calculate the monthly and then the yearly cost to lay off workers 30 lan Feb Mar Apr May ANE Nov Dec 31 Workers 7 ? 7 7 7 32 Negative change in Workers from previous month? 2 2 7 7 ? 7 2 33 Cost 19,000 5 19 000 $ 19.000 5 19.000 S 19.000 $ 19 000 $ 19.000 19.000 5 19,000 5 19,000 $ 19,000 $ 19,000 34 Total Layoff Cost LUL VALUE WALUE WALUL WALUL WALU VALUE VALUE 35 2 WALUE VALUE d) During busy times, the tradesmen & Mike's will happily work up to 50% overtime each month Each worker makes 5140 a day and overtime is 50% above standardate. Using a fixed staff of 50 workers who are able to work 36 overtime calculate the number of equivalent workers needed per day to fill demand. Hintlike but do not round the number of equivalent workers, go to the second decimal point XXX 37 Ost Feb Mar lan 38 AU Apr May un Sep Des wa RA W6 Assignment (CL03) + Ready BH , + 100% MacBook Pro P73 X fx M N 0 0 K P R D L H E F G IVALUE! c) During busy times, the tradesmen at Mike's will happily work up to 50 overtime each month. Each worker makes $140 aday and overtime is 50% above standard rate. Using a fixed staff of 50 workers who are able to work 36 overtime calculate the number of equivalent workers needed per day to fill demand. (Hint:like "b"but do not round the number of equivalent workers to the second decimal point X.XX) 37 38 lan Feb MAT Apr May lun Jul Aug Sep Nov Dec 39 Projected Demand 15000 19000 20500 21000 22000 21000 21000 19500 18000 16200 15000 15000 40 Equivalent Workers ? ? 7 13 ? ? 7 7 2 7 ? ? 41 Work days 20 20 20 20 20 20 20 20 20 20 20 20 42 Units/Worker day 15 15 15 15 15 15 15 15 15 15 15 15 43 Units Produced VALUEI FVALUE! HVALUE! WVALUES HVALUE! #VALUE! #VALUE! VALUE! #VALUE VALUES IVALUE! #VALUE! 44 45 eWhat is the overtime wage cost in the above example id ? 46 Jan Feb Mar Apr May Jun lu! Aug Sep Oct Nov Dec 48 Equivalent Workers from problem above 7 2 ? 2 2 2 M ? 2 49 Actual Workers 50 50 SO 50 50 50 SO 50 50 SO 50 So Variation in Actualto Equivalent Workers VALUE! #VALUE! VALUE HVALUE! VALUE! VALUE #VALUE! VALUE VALUE #VALUE! VALUE 51 Workdays/month 20 20 20 20 20 20 20 20 20 20 20 20 52 Overtime Cost/day per worker above the $140/day 7 ? ? 2 7 2 ? 53 Total Overtime cost VALUE #VALUE! #VALUE! #VALUE! VALUE! IVALUE VALUE HVALUE VALUE IVALUE HVALUE #VALUE! 54 55 Compare this overtime wage cost to the layoff cost inc). Which is better for the employer, Mike Mufflers? Explain why 56 57 Total Layoff Cost to Mike's Muffler-> #VALUE! 58 Total overtime Cost to Mike's Muffin WVALUE som INVALUE ? 2 e) The tradesmen at Mike's are in a union, and would like to eliminate the cycle of temporary hires and layoffs. They are will to negotiate their overtime wage so that it does not benefit the company to continue with this process. The 59 current overtime wage is time +50% What should the union negotiate to change it to? 60 61 Feb Mac Apr May Jun jul ALE Serg Oct Nov Dec 62 Equivalent Workers from problem b'above 7 2 7 2 63 Actual Workers 50 SO 50 50 SO 30 50 50 50 64 Variation in Actualto Equivalent Workers WALUE WVALUE VALUE VALUE VALUR MALU HVALUE WALUE #VALUE! #VALUE! 65 Workday/month 20 20 20 20 20 20 20 20 20 66 Overtime Cost/day per worker above the $140/day 7 67 Total Overtime cost VALUE VALUE WALUL HVALUE UVALUE VALLE WVALUE VALUE VALUE VALUE 68 69 TotalLay-off Cost to Mike's Mures INALUE 70 New Total Overtime Cost to Mike's Mutflers => VALUE 71 72 Type your answer 73 #VALUE! W6 Assignment (CL03) + Ready 100 Q- search sheet Unit 6 Assignment Template 9+ Share N o P M H 1 1 K G Home Insert Page Layout Formulas Data Review View p94 4 x fx B D E F 55 Compare this overtime wage cost to the layoff cost in (d. Which is better for the employer, Mike Mufflers? Explain why 56 57 Total Layoff Cost to Mike's Mufflers #VALUE! S8 Total overtime Cost to Mile's Mufflers > #VALUE! ? VALGE #VALUE! ) The tradesmen at Mike's are in a union, and would like to eliminate the cycle of temporary hires and layoffs. They are will to negotiate their overtime wage so that it does not benefit the company to continue with this process. The 59 current overtime wage is time +50%. What should the union negotiate to charge it to? 60 Feb May Mar Jun Oct Aur Apr Sep Nov 61 Dec 29 ? ? 7 2 62 Equivalent Workers from problem 'b'above ? 50 50 50 50 SO 50 SO 50 50 50 50 SO 63 Actual Workers WALDE #VALUE SVALLEI HVALUE WALUE #VALUE! UVALUFI VALUE VALUE VALUEI #VALUE! 64 Variation in Actualto Equivalent Workers 20 20 20 20 20 20 20 20 20 20 20 20 65 Workdays/month 7 ? 2 2 2 ? 2 66 Overtime Cost/day per worker above the 5140/day WALLELLE. VALUE VALUEI VALUE VALUE #VALUE AVALUE VALUE VALUE WVALUE VALUE 67 Total Overtime cost 68 69 Total Layoff Cost to Mike's Mufflers > VALUE 70 New Total Overtime Cost to Mike's Mufflers >> VALUE 71 72 Type your answer >> 73 74 h) in your opinion, would the union what to do this? 75 Type your answer >> 76 77 78 79 80 81 B2 83 84 85 86 87 88 89 90 91 92 93 W6 Assignment (CLO3) + Ready