Question

The Specialty Steel Products Company produces items, such as machine tools, gears, automobile parts, and other specialty items, in small quantities to customer order. Because the products are so diverse, demand is measured in machine-hours. Orders for products are translated into required machine-hours, based on time standards for each operation. Management is concerned about capacity in the lathe department.

Assemble the data necessary to analyze the addition of one more lathe machine and operator.

Historical records indicate that lathe department demand varies from week to week as follows:

Weekly Production Requirements (hours)	Relative Frequency
200	0.05
250	0.06
300	0.17
350	0.05
400	0.30
450	0.15
500	0.06
550	0.14
600	0.02
	1

To gather these data:

- Weeks with requirements of 175.00 - 224.99 hours were grouped in the 200-hour category.

- Weeks with 225.00 - 274.99 hours were grouped in the 250-hour category, and so on.

Employees in the lathe department work 40 hours per week on 10 machines. However, the number of machines actually operating during any week may be less than 10. Machines may need repair, or a worker may not show up for work. Historical records indicate that actual machine-hours were distributed as follows:

Regular Capacity (hr)	Relative Frequency
320 (8 machines)	0.30
360 (9 machines)	0.40
400 (10 machines)	0.30

Resource and Costs

Maximum Overtime	100 hours
Lathe Operations	$10/hr
Overtime Cost	$25/hr
Subcontracting Cost	$35/hr

The company has a policy of completing each week's workload on schedule, using overtime and subcontracting if necessary.

To justify adding another machine and worker to the lathe department, weekly savings in overtime and subcontracting costs should be at least $650.

Management estimates from prior experience that with 11 machines the distribution of weekly capacity machine-hours would be

Regular Capacity (hr)	Relative Frequency
360 (9 machines)	0.30
400 (10 machines)	0.40
440 (11 machines)	0.30

Please show your work in Microsoft Excel with all formulas involved.

Answer 1

Given the question we need to determine the Additional Money Saved by incorporating 1 more Lathe Machine

Solution

So to solve this question we proceed by generation Randon Number between (175-625) which is the working hours in a week

Having generated 100 Random number of working hours in week we determine the following parameter of consideration

A> Extra Hours of Overtime required to complete the order in the week= (Hours - 40Hr/week *no of Machines required)

B> Overtime Cost= Overtime Hours*(10+25)

C> Subcontracting Hours= Extra Hours- Max. Overtime(100 Hrs)

D> Sub contracting Cost =( Subcontacting Hours *35/Hr)

E> total cost additional = Overtime cost + Subcontacting Cost

F> Cost Saving with 11 Lathe Machine= Additional Cost with 10 Machines- Additional with 11 Machines

We compute all the above parameters by taking 10 Lathe Machines and 11 Lathe Manchines & Find Savings

If the Savings >= $ 650 Then it is profitable to have 11 Lathe Machine

Sample Simulation of 25 randomly generated Working Hours with 10 & 11 Lathe Machines with cost Savings

So we observe out 25 random Simulations 15 which have overtime & Sub contracting

Out this 15 Overtime+ Subcontracting 13 instances have cost savings over $650 while 2 instances have Cost Savings <= $650

So it is preferable to have 11 Lathe Machines as cost savings probability of (>=$650) = 13/25

End of solution