Instructions

This is a calculation assignment. You’ll use the tab labeled “U6 Assignment (CLO3)” in the spreadsheet.

• Answer all sections of the attached spreadsheet (questions a through h)
• Complete the calculations in the spreadsheet as appropriate
• Document your work and provide any rationale and explanations to support your work.

Mike’s Exhaust Works produces efficient exhaust systems used on custom motorcycles. They sell their systems to other manufacturers, who build customized bikes to clients. Kari is the sales manager, and she has created the following projected demand schedule for Tom, the production manager….

Assignment Files:

W6 Assignment (CLO3)

00

0

21000 21000

15000

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

Projected Demand 15000 19000

21000 22000 21000 21000 19500 18000 16200 15000 15000

? ? ? ? ? ? ? ? ? ? ? ?

20 20 20 20 20 20 20 20 20 20 20 20

15 15 15 15 15 15 15 15 15 15 15 15

ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! –> ERROR:#VALUE!

0 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

Projected Demand 15000 19000 20500 21000 22000 21000 21000 19500 18000 16200 15000 15000
Workers ? ? ? ? ? ? ? ? ? ? ? ?
Work days 20 20 20 20 20 20 20 20 20 20 20 20
Units/Worker day 15 15 15 15 15 15 15 15 15 15 15 15

ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE!

Workers ? ? ? ? ? ? ? ? ? ? ? ?

? ? ? ? ? ? ? ? ? ? ? ?

$ 19,000 $ 19,000 $ 19,000 $ 19,000 $ 19,000 $ 19,000 $ 19,000 $ 19,000 $ 19,000 $ 19,000 $ 19,000

ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE!

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Projected Demand 15000 19000 20500 21000 22000 21000 21000 19500 18000 16200 15000 15000
Workers ? ? ? ? ? ? ? ? ? ? ? ?
Work days 20 20 20 20 20 20 20 20 20 20 20 20
Units/Worker day 15 15 15 15 15 15 15 15 15 15 15 15
ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! –> ERROR:#VALUE!

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

? ? ? ? ? ? ? ? ? ? ? ?

50 50 50 50 50 50 50 50 50 50 50 50

ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE!

20 20 20 20 20 20 20 20 20 20 20 20

? ? ? ? ? ? ? ? ? ? ? ?

ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! –> ERROR:#VALUE!

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Equivalent Workers ? ? ? ? ? ? ? ? ? ? ? ?
Actual Workers 50 50 50 50 50 50 50 50 50 50 50 50
Variation ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE!
Workdays/month 20 20 20 20 20 20 20 20 20 20 20 20
Overtime Cost/day ? ? ? ? ? ? ? ? ? ? ? ?
Total Overtime cost ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! ERROR:#VALUE! –> ERROR:#VALUE!

?

Mike’s Exhaust Works produces efficient exhaust systems used on custom motorcycles. They sell their systems to other manufacturers, who build customized bikes to clients. Kari is the sales manager, and she has created the following projected demand schedule for Tom, the production manager.

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Projected Demand

15

0

19000

20

50

21000

22000

19500

18000

16200

15000

–>

223200

Mike’s Exhaust Currently has 50 tradesmen who each work 20 days a week, and can each produce 15 exhaust systems per day.

a) Assuming that the company is only concerned with aggregate yearly demand, should the labor force be changed

?

20500

Workers

Work days

Units/Worker day

ERROR:#VALUE!

b) Assuming that the company is concerned with aggregate monthly demand, how should the labor force be changed? (round to the nearest whole employee – exact monthly demand must me met, no over or underproduction allowed).

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?

Negative Change?

Cost

$ 19,000

Total Cost

d) During busy times, the tradesmen at Mike’s will happily take 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, use overtime to calculate the number of equivalent workers needed per day to fill demand. (Hint: like “b” but do not round)

e) What is the overtime wage cost in the above example (d)?

Equivalent Workers

Actual Workers

Variation

Workdays/month

Overtime Cost/day

Total Overtime cost

f) CCompare this overtime wage cost to the layoff cost in (c). Which is better?

g) 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?

Overtime %

h) In your opinion, would the union what to do this?