Question

Case-2

Farquhar Industries, Inc.

Farquhar Industries, Inc., is a medium-size producer of custom metal products. The company recently got a contract to make the chassis for a well-known microcomputer. This part will be produced using dedicated, highly automated equipment. But the company frequently gets contracts to make special orders of custom products. These are usually produced on general- purpose equipment and involve a high degree of skilled labor. For medium-range planning purposes, the company divides its products according to the type of process required-job shop or line flow. Further, demand requirements for the job shop are stated in terms of hours because of the large number of different products produced, each requiring various amounts of processing time. On the other hand, the line-flow product demand is stated in teams of units because the production rate is relatively constant for these items. The forecasted demand in each area for the coming year is shown below.

Month	Manual Operation Demand Forecast (Hours)	Working Days	Automatic Operation Demand Forecast (Units)
January February March April May June July August September October November December	1,800 1,500 1,900 1,600 2,000 2,200 2,000 1,800 1,700 1,800 2,000 1,500	21 19 22 22 20 22 22 21 21 22 20 22	6,000 4,000 5,000 6,000 5,000 6,000 7,000 6,000 5,000 6,000 8,000 6,000

Normally, both operations work eight hours per day, five days per week. The automatic operation produces at an average rate of forty units per hour. Any time that the process is operating, five employees who earn $12.50 per hour must be present. Work on products requiring manual operations is a little different. Each employee there earns an average of $16.00 per hour, and there are currently ten employees. Extra employees can be hired in that area, but the cost of advertising, interviewing, and so on is about $500 per employee hired. Any employees laid off receive one month's pay as compensation. Overtime work is paid at a 50 percent premium and is limited to two hours per day on weekdays and four hours on Saturday.

Bill Dixon is production manager for Farquhar. He is working on developing an aggregate plan for the coming year and has two major concerns. First, the company's relations with its employees have been good, but there is some talk of unionizing. Too many layoffs could lead to more than talk. Second, the cost of carrying inventory has been increasing. Custom-made products are not inventoried, but high-volume products are inventoried at an average cost of $1.50 per unit per month. Carrying cost is a major concern with 2,000 units now in stock.

Suppose you are Bill Dixon. Develop an aggregate plan that meets Farquhar's company objectives, and determine the total costs associated with that plan.

Tips:

(Manual production) Vary the workforce by hiring/layoffs. No over time

(Manual production) Do not vary the workforce, i.e. no hiring or layoffs, only 10 labor to be used. Use overtime at the mentioned rates to fulfill remaining demand.

(Automatic production) Use full capacity of machine to run throughout the months. Use inventory to fulfill the demand in the peak periods.

(Automatic production) Do not run machine at full capacity. Run according to the monthly demand. Fulfill demand in the peak periods through over time.

Answer 1

a. Manual operation No Overtime

Hiring will happen when required no of workers are less than the available workers

To calculate the required no of workers first calculate the available hours in a month i.e no of working days* 8 hours/day

Then to find workers required for that month demand divide demand hours by available hours

The calculation for firing cost = No of employees fired* No of days in that month* 8 Hours* rate per hour(16 for manual operation)

The calculations are done in excel sheet shown below

Month	Manual Operation Demand Forecast (Hours)
		Working Days	Available hours per month per worker	Workers reuired	Hiring cost	Firing cost	Total Cost
Current period				10
January	1,800	21	168	11	500	0	500
February	1,500	19	152	10	0	2432	2432
March	1,900	22	176	11	500	0	500
April	1,600	22	176	10	0	2816	2816
May	2,000	20	160	13	1500	0	1500
June	2,200	22	176	13	0	0	0
July	2,000	22	176	12	0	2816	2816
August	1,800	21	168	11	0	2688	2688
September	1,700	21	168	11	0	0	0
October	1,800	22	176	11	0	0	0
November	2,000	20	160	13	1000	0	1000
December	1,500	22	176	9	0	11264	11264

Formula

Month	Manual Operation Demand Forecast (Hours)
		Working Days	Available hours per month per worker	Workers reuired	Hiring cost	Firing cost	Total Cost
Current period				10
January	1800	21	=C4*8	=ROUNDUP(B4/D4,0)	=IF(E4>E3,(E4-E3)*500,0)	=IF(E4<E3,(E3-E4)*C4*8*16,0)	=SUM(F4:G4)
February	1500	19	=C5*8	=ROUNDUP(B5/D5,0)	=IF(E5>E4,(E5-E4)*500,0)	=IF(E5<E4,(E4-E5)*C5*8*16,0)	=SUM(F5:G5)
March	1900	22	=C6*8	=ROUNDUP(B6/D6,0)	=IF(E6>E5,(E6-E5)*500,0)	=IF(E6<E5,(E5-E6)*C6*8*16,0)	=SUM(F6:G6)
April	1600	22	=C7*8	=ROUNDUP(B7/D7,0)	=IF(E7>E6,(E7-E6)*500,0)	=IF(E7<E6,(E6-E7)*C7*8*16,0)	=SUM(F7:G7)
May	2000	20	=C8*8	=ROUNDUP(B8/D8,0)	=IF(E8>E7,(E8-E7)*500,0)	=IF(E8<E7,(E7-E8)*C8*8*16,0)	=SUM(F8:G8)
June	2200	22	=C9*8	=ROUNDUP(B9/D9,0)	=IF(E9>E8,(E9-E8)*500,0)	=IF(E9<E8,(E8-E9)*C9*8*16,0)	=SUM(F9:G9)
July	2000	22	=C10*8	=ROUNDUP(B10/D10,0)	=IF(E10>E9,(E10-E9)*500,0)	=IF(E10<E9,(E9-E10)*C10*8*16,0)	=SUM(F10:G10)
August	1800	21	=C11*8	=ROUNDUP(B11/D11,0)	=IF(E11>E10,(E11-E10)*500,0)	=IF(E11<E10,(E10-E11)*C11*8*16,0)	=SUM(F11:G11)
September	1700	21	=C12*8	=ROUNDUP(B12/D12,0)	=IF(E12>E11,(E12-E11)*500,0)	=IF(E12<E11,(E11-E12)*C12*8*16,0)	=SUM(F12:G12)
October	1800	22	=C13*8	=ROUNDUP(B13/D13,0)	=IF(E13>E12,(E13-E12)*500,0)	=IF(E13<E12,(E12-E13)*C13*8*16,0)	=SUM(F13:G13)
November	2000	20	=C14*8	=ROUNDUP(B14/D14,0)	=IF(E14>E13,(E14-E13)*500,0)	=IF(E14<E13,(E13-E14)*C14*8*16,0)	=SUM(F14:G14)
December	1500	22	=C15*8	=ROUNDUP(B15/D15,0)	=IF(E15>E14,(E15-E14)*500,0)	=IF(E15<E14,(E14-E15)*C15*8*16,0)	=SUM(F15:G15)
							=SUM(H4:H15)

2. No hiring firing

Overtime only

Month	Manual Operation Demand Forecast (Hours)
		Working Days	Avaialble workers	Available hours	Overtime required	Overtime capacity weekdays	Overtime cost
Current period
January	1,800	21	10	1680	120	420	2880
February	1,500	19	10	1520	-20	380	0
March	1,900	22	10	1760	140	440	3360
April	1,600	22	10	1760	-160	440	0
May	2,000	20	10	1600	400	400	9600
June	2,200	22	10	1760	440	440	10560
July	2,000	22	10	1760	240	440	5760
August	1,800	21	10	1680	120	420	2880
September	1,700	21	10	1680	20	420	480
October	1,800	22	10	1760	40	440	960
November	2,000	20	10	1600	400	400	9600
December	1,500	22	10	1760	-260	440	0
						Total cost	46080

Formula

Month	Manual Operation Demand Forecast (Hours)
		Working Days	Avaialble workers	Available hours	Overtime required	Overtime capacity weekdays	Overtime cost
Current period
January	1800	21	10	=C4*8*D4	=B4-E4	=C4*2*10	=IF(F4>0,F4*16*1.5,0)
February	1500	19	10	=C5*8*D5	=B5-E5	=C5*2*10	=IF(F5>0,F5*16*1.5,0)
March	1900	22	10	=C6*8*D6	=B6-E6	=C6*2*10	=IF(F6>0,F6*16*1.5,0)
April	1600	22	10	=C7*8*D7	=B7-E7	=C7*2*10	=IF(F7>0,F7*16*1.5,0)
May	2000	20	10	=C8*8*D8	=B8-E8	=C8*2*10	=IF(F8>0,F8*16*1.5,0)
June	2200	22	10	=C9*8*D9	=B9-E9	=C9*2*10	=IF(F9>0,F9*16*1.5,0)
July	2000	22	10	=C10*8*D10	=B10-E10	=C10*2*10	=IF(F10>0,F10*16*1.5,0)
August	1800	21	10	=C11*8*D11	=B11-E11	=C11*2*10	=IF(F11>0,F11*16*1.5,0)
September	1700	21	10	=C12*8*D12	=B12-E12	=C12*2*10	=IF(F12>0,F12*16*1.5,0)
October	1800	22	10	=C13*8*D13	=B13-E13	=C13*2*10	=IF(F13>0,F13*16*1.5,0)
November	2000	20	10	=C14*8*D14	=B14-E14	=C14*2*10	=IF(F14>0,F14*16*1.5,0)
December	1500	22	10	=C15*8*D15	=B15-E15	=C15*2*10	=IF(F15>0,F15*16*1.5,0)
						Total cost	=SUM(H4:H15)

3. Running automatic operation in full capacity i.e 8 hours /day will produce 8* 40 = 320 /day

When we multiply with the no of working days available per month we get the quantity produced in that month in full capacity

The excel sheets shows the calculation

Month	Automatic Operation Demand Forecast (Units)	Working Days	Quantity produce in regular hours
				Inventory in hand at the end of period	Inventory cost
				2000
January	6,000	21	6720	2,720	4080
February	4,000	19	6080	4,800	7200
March	5,000	22	7040	6,840	10260
April	6,000	22	7040	7,880	11820
May	5,000	20	6400	9,280	13920
June	6,000	22	7040	10,320	15480
July	7,000	22	7040	10,360	15540
August	6,000	21	6720	11,080	16620
September	5,000	21	6720	12,800	19200
October	6,000	22	7040	13,840	20760
November	8,000	20	6400	12,240	18360
December	6,000	22	7040	13,280	19920
				Total Cost	173160

Formula

Month	Automatic Operation Demand Forecast (Units)	Working Days	Quantity produce in regular hours
				Inventory in hand at the end of period	Inventory cost
				2000
January	6000	21	=C5*8*40	=D5+E4-B5	=E5*1.5
February	4000	19	=C6*8*40	=D6+E5-B6	=E6*1.5
March	5000	22	=C7*8*40	=D7+E6-B7	=E7*1.5
April	6000	22	=C8*8*40	=D8+E7-B8	=E8*1.5
May	5000	20	=C9*8*40	=D9+E8-B9	=E9*1.5
June	6000	22	=C10*8*40	=D10+E9-B10	=E10*1.5
July	7000	22	=C11*8*40	=D11+E10-B11	=E11*1.5
August	6000	21	=C12*8*40	=D12+E11-B12	=E12*1.5
September	5000	21	=C13*8*40	=D13+E12-B13	=E13*1.5
October	6000	22	=C14*8*40	=D14+E13-B14	=E14*1.5
November	8000	20	=C15*8*40	=D15+E14-B15	=E15*1.5
December	6000	22	=C16*8*40	=D16+E15-B16	=E16*1.5
				Total Cost	=SUM(F5:F16)

4.

Month	Automatic Operation Demand Forecast (Units)	Working Days	Regular production	Required units	No of units more to produce			Overtime cost
						Available OT hours	Required overtime hours
January	6,000	21	6720	4,000	0	42	0	0
February	4,000	19	6080	4,000	0	38	0	0
March	5,000	22	7040	5,000	0	44	0	0
April	6,000	22	7040	6,000	0	44	0	0
May	5,000	20	6400	5,000	0	40	0	0
June	6,000	22	7040	6,000	0	44	0	0
July	7,000	22	7040	7,000	0	44	0	0
August	6,000	21	6720	6,000	0	42	0	0
September	5,000	21	6720	5,000	0	42	0	0
October	6,000	22	7040	6,000	0	44	0	0
November	8,000	20	6400	1,600	1600	40	40	750
December	6,000	22	7040	6,000	0	44	0	0
							Total cost	750
Inventory	2,000

Month	Automatic Operation Demand Forecast (Units)	Working Days	Regular production	Required units	No of units more to produce			Overtime cost
						Available OT hours	Required overtime hours
January	6000	21	=C5*8*40	=B5-B18	=IF(B5>D5,(B5-D5),0)	=C5*2	=IF(F5>0,F5/40,0)	=IF(H5>0,H5*12.5*1.5,0)
February	4000	19	=C6*8*40	=IF(B6>D6,B6-D6,B6)	=IF(B6>D6,(B6-D6),0)	=C6*2	=IF(F6>0,F6/40,0)	=IF(H6>0,H6*12.5*1.5,0)
March	5000	22	=C7*8*40	=IF(B7>D7,B7-D7,B7)	=IF(B7>D7,(B7-D7),0)	=C7*2	=IF(F7>0,F7/40,0)	=IF(H7>0,H7*12.5*1.5,0)
April	6000	22	=C8*8*40	=IF(B8>D8,B8-D8,B8)	=IF(B8>D8,(B8-D8),0)	=C8*2	=IF(F8>0,F8/40,0)	=IF(H8>0,H8*12.5*1.5,0)
May	5000	20	=C9*8*40	=IF(B9>D9,B9-D9,B9)	=IF(B9>D9,(B9-D9),0)	=C9*2	=IF(F9>0,F9/40,0)	=IF(H9>0,H9*12.5*1.5,0)
June	6000	22	=C10*8*40	=IF(B10>D10,B10-D10,B10)	=IF(B10>D10,(B10-D10),0)	=C10*2	=IF(F10>0,F10/40,0)	=IF(H10>0,H10*12.5*1.5,0)
July	7000	22	=C11*8*40	=IF(B11>D11,B11-D11,B11)	=IF(B11>D11,(B11-D11),0)	=C11*2	=IF(F11>0,F11/40,0)	=IF(H11>0,H11*12.5*1.5,0)
August	6000	21	=C12*8*40	=IF(B12>D12,B12-D12,B12)	=IF(B12>D12,(B12-D12),0)	=C12*2	=IF(F12>0,F12/40,0)	=IF(H12>0,H12*12.5*1.5,0)
September	5000	21	=C13*8*40	=IF(B13>D13,B13-D13,B13)	=IF(B13>D13,(B13-D13),0)	=C13*2	=IF(F13>0,F13/40,0)	=IF(H13>0,H13*12.5*1.5,0)
October	6000	22	=C14*8*40	=IF(B14>D14,B14-D14,B14)	=IF(B14>D14,(B14-D14),0)	=C14*2	=IF(F14>0,F14/40,0)	=IF(H14>0,H14*12.5*1.5,0)
November	8000	20	=C15*8*40	=IF(B15>D15,B15-D15,B15)	=IF(B15>D15,(B15-D15),0)	=C15*2	=IF(F15>0,F15/40,0)	=IF(H15>0,H15*12.5*1.5,0)
December	6000	22	=C16*8*40	=IF(B16>D16,B16-D16,B16)	=IF(B16>D16,(B16-D16),0)	=C16*2	=IF(F16>0,F16/40,0)	=IF(H16>0,H16*12.5*1.5,0)
							Total cost	=SUM(I5:I16)
Inventory	2000