Question

Mr. Alex is the head of operations at TractParts Pvt. Ltd., a well known Indian manufacturer of pumps, engines, electric motors and transformers. The company is one of India’s earliest industrial groups established in the 1980 which has grown to be a big player in the Indian manufacturing industry. TractParts under the supervision of Mr. Alex has been supplying modern tractor engines to the leading tractor manufacturing firms like Sonalika and John Dear. The bulk of demand for tractors comes during the months from October to March. As a result of this seasonality in demand, the demand for the tractor engines also keeps fluctuating throughout the year as shown in Exhibit 1. Exhibit 1 shows the orders placed with TractParts by the firms Sonalika and John Dear in advance based on their own forecasting models.

Exhibit 1:

Month	Sonalika	John Dear	Total
April	500	400	900
May	300	200	500
June	100	150	250
July	125	100	225
August	200	150	350
September	300	350	650
October	1500	1450	2950
November	3000	3200	6200
December	3200	3500	6700
January	3800	3500	7300
February	2200	2150	4350
March	2200	2400	4600

To meet the demands, the company can follow chase strategy or level strategy In the chase strategy, the monthly production takes place as per the total demand in that month and to follow this strategy, the company can hire new employees during higher demand and fire the employees during lower demand. This strategy basically saves on the inventory carrying cost and hence the total cost. In the level strategy, the company produces the average demand in a month and the excess units are stored as inventory and all stock outs are backlogged and supplied from the following month’s production. This strategy particularly saves on the employee hiring and firing cost. Moreover, the backorder quantity is also directly proportion to the demand placed by the tractor manufacturing firms in order to give equal treatments to them.

At the beginning of the period, there are 100 workers in the TractParts manufacturing facility and a total of 200 working hours (8 hours/day * 25 days/month) are available per worker per month. Due to the strict government policies, the company canny allow overtime.

Mr. Alex is concerned about the fluctuating demand and understands that these companies can change their demand pattern based on the promotion and discount offered to them. In order to enhance the total profit, Mr. Alex wanted to optimize costs while meeting the demand pattern. He had therefore asked to one of his associates to find out the associated costs and the cost structure. The associate presented cost structure for the engine manufacturing line as is shown in Exhibit 2.

Exhibit 2:

Component	Cost	Unit
Material Cost	140	$/unit
Inventory Cost	15	$/unit/month
Stock-Out Cost	20	$/unit/month
Hiring Cost	450	$/worker
Firing Cost	750	$/worker
Labor Hours Required	4	per unit
Regular Labor Rate	4	$/hour
Beginning Inventory	750	units
Desired Closing Inventory	400	units
Selling Price	280	$/unit

Mr. Alex had asked the associate to find out the possibility of discount that could be offered to the tractor manufacturers. Mr Alex was informed that at max 10% discount could be given to them and as per company policy, discount would be offered for only one month. The associate also pointed out that whenever such kinds of discounts are offered, the tractor manufacturers have a tendency to order more in that month. However, the order size for following months is significantly reduced compared to the previous month.

After going through some previous data, Mr. Alex has made some observations. For Sonalika, 10% decrease in price for a particular month results in 45 % increase in the demand for the same month followed by 15% decrease in demand for the next two months. And in the case of John Dear, 10% decrease in price for a particular month results in 80% increase in the demand for the same month followed by 25% decrease in demand for the next two months. However, Mr. Alex also observed that if the TractPart offers a discount in the last 2 months of the financial year, there is no change in the demand pattern since there is a very fixed demand of tractors in the end of season.

Mr. Alex faced a unique problem as the TractParts had proven itself over the years and was considered a symbol of quality, reliability and accountability. Being a part of this value system, Mr. Alex knew that any unfair treatment with the tractor manufacturers would not be tolerated. Mr. Alex has a meeting with the company’s Sr. VP the next morning. He is concerned about the production policy to be adapted and discount to be given offered to increase the total profit. Please help Mr. Alex to take the decision focusing on the following questions.

Questions:

1. Estimate the profit made by TractParts when it follows the level strategy and no discount is offered to the tractor manufacturing firms.

2. Estimate the profit made by TractParts when it follows the chase strategy and no discount is offered to the tractor manufacturing firms

3. Estimate the profit made by TractParts when it follows the level strategy and a 10% discount is offered to the tractor manufacturing firms in October.

4. Find out the month having the peak demand in which 10% discount can be offered to the tractor manufacturing firms, when TractParts follows the level strategy. (Hint: Estimate the profit made by TractParts when it follows the level strategy and a 10% discount has been offered to the tractor manufacturing firms in various months.)

5. Estimate the profit made by TractParts when it follows the chase strategy and a 10% discount is offered to the tractor manufacturing firms in October.

6. Find out the month having the peak demand in which 10% discount can be offered to the tractor manufacturing firms, when TractParts follows the chase strategy. (Hint: Estimate the profit made by TractParts when it follows the chase strategy and a 10% discount has been offered to the tractor manufacturing firms in various months.)

I REALLY NEED HELP WITH 4 AND 6. PLEASE INCLUDE EXCEL SHEET. THANK YOU IN ADVANCE.

Answer 1

Chase Strategy
Month	Demand	Units requied	Labour hour required	Hiring cost	Firing cost	Total recrutiment cost	Material cost	Regular Labor cost	Total cost	Total Revenue
April	900	150	600	0	72750	72750	21000	2400	96150	252000
May	500	500	2000	3150	0	3150	70000	8000	81150	140000
June	250	250	1000	0	3750	3750	35000	4000	42750	70000
July	225	225	900	0	375	375	31500	3600	35475	63000
August	350	350	1400	1125	0	1125	49000	5600	55725	98000
September	650	650	2600	2700	0	2700	91000	10400	104100	182000
October	2950	2950	11800	20700	0	20700	413000	47200	480900	826000
November	6200	6200	24800	29250	0	29250	868000	99200	996450	1736000
December	6700	6700	26800	4500	0	4500	938000	107200	1049700	1876000
January	7300	7300	29200	5400	0	5400	1022000	116800	1144200	2044000
February	4350	4350	17400	0	44250	44250	609000	69600	722850	1218000
March	4600	5000	20000	5850	0	5850	700000	80000	785850	1288000
									5595300	9793000
Available hours	20000						Profit	4197700
							Profit after discount	3777930

Formula

Chase Strategy
Month	Demand	Units requied	Labour hour required	Hiring cost	Firing cost	Total recrutiment cost	Material cost	Regular Labor cost	Total cost	Total Revenue
April	900	=900-750	=C3*4	=IF(D3>B17,(D3-B17)/200*450,0)	=IF(D3<B17,(B17-D3)/200*750,0)	=E3+F3	=C3*140	=D3*4	=SUM(G3:I3)	=B3*280
May	500	500	=C4*4	=IF(D4>D3,(D4-D3)/200*450,0)	=IF(D4<D3,(D3-D4)/200*750,0)	=E4+F4	=C4*140	=D4*4	=SUM(G4:I4)	=B4*280
June	250	250	=C5*4	=IF(D5>D4,(D5-D4)/200*450,0)	=IF(D5<D4,(D4-D5)/200*750,0)	=E5+F5	=C5*140	=D5*4	=SUM(G5:I5)	=B5*280
July	225	225	=C6*4	=IF(D6>D5,(D6-D5)/200*450,0)	=IF(D6<D5,(D5-D6)/200*750,0)	=E6+F6	=C6*140	=D6*4	=SUM(G6:I6)	=B6*280
August	350	350	=C7*4	=IF(D7>D6,(D7-D6)/200*450,0)	=IF(D7<D6,(D6-D7)/200*750,0)	=E7+F7	=C7*140	=D7*4	=SUM(G7:I7)	=B7*280
September	650	650	=C8*4	=IF(D8>D7,(D8-D7)/200*450,0)	=IF(D8<D7,(D7-D8)/200*750,0)	=E8+F8	=C8*140	=D8*4	=SUM(G8:I8)	=B8*280
October	2950	2950	=C9*4	=IF(D9>D8,(D9-D8)/200*450,0)	=IF(D9<D8,(D8-D9)/200*750,0)	=E9+F9	=C9*140	=D9*4	=SUM(G9:I9)	=B9*280
November	6200	6200	=C10*4	=IF(D10>D9,(D10-D9)/200*450,0)	=IF(D10<D9,(D9-D10)/200*750,0)	=E10+F10	=C10*140	=D10*4	=SUM(G10:I10)	=B10*280
December	6700	6700	=C11*4	=IF(D11>D10,(D11-D10)/200*450,0)	=IF(D11<D10,(D10-D11)/200*750,0)	=E11+F11	=C11*140	=D11*4	=SUM(G11:I11)	=B11*280
January	7300	7300	=C12*4	=IF(D12>D11,(D12-D11)/200*450,0)	=IF(D12<D11,(D11-D12)/200*750,0)	=E12+F12	=C12*140	=D12*4	=SUM(G12:I12)	=B12*280
February	4350	4350	=C13*4	=IF(D13>D12,(D13-D12)/200*450,0)	=IF(D13<D12,(D12-D13)/200*750,0)	=E13+F13	=C13*140	=D13*4	=SUM(G13:I13)	=B13*280
March	4600	=4600+400	=C14*4	=IF(D14>D13,(D14-D13)/200*450,0)	=IF(D14<D13,(D13-D14)/200*750,0)	=E14+F14	=C14*140	=D14*4	=SUM(G14:I14)	=B14*280
									=SUM(J3:J14)	=SUM(K3:K14)
Available hours	=200*100						Profit	=K15-J15
							Profit after discount	=0.9*I17

Level strategy
Month	Demand	Cumulative demand	Production level	Cumulative production	Inventory	Inventory Holding cost	Material cost	Labor cost	Total cost	Total revenue
April	900	900	2886	2886	2736	41040	404040	46176	491256	252000
May	500	1400	2886	5772	5122	76830	404040	46176	527046	140000
June	250	1650	2886	8658	7758	116370	404040	46176	566586	70000
July	225	1875	2886	11544	10419	156285	404040	46176	606501	63000
August	350	2225	2886	14430	12955	194325	404040	46176	644541	98000
September	650	2875	2886	17316	15191	227865	404040	46176	678081	182000
October	2950	5825	2886	20202	15127	226905	404040	46176	677121	826000
November	6200	12025	2886	23088	11813	177195	404040	46176	627411	1736000
December	6700	18725	2886	25974	7999	119985	404040	46176	570201	1876000
January	7300	26025	2886	28860	3585	53775	404040	46176	503991	2044000
February	4350	30375	2886	31746	2121	31815	404040	46176	482031	1218000
March	4600	34975	2886	34632	407	6105	404040	46176	456321	1288000
									6831087	9793000
	Beginning inventory			750			Profit	Profit after Discount(10%)
	Ending inventory			400			2961913	2665722
	Total Demand			34975
	Production Level			2885

Formula

Level strategy
Month	Demand	Cumulative demand	Production level	Cumulative production	Inventory	Inventory Holding cost	Material cost	Labor cost	Total cost	Total revenue
April	900	=B4	2886	=D4	=E4-C4+750	=F4*15	=D4*140	=D4*4*4	=SUM(G4:I4)	=B4*280
May	500	=B5+C4	2886	=D5+E4	=D5+F4-B5	=F5*15	=D5*140	=D5*4*4	=SUM(G5:I5)	=B5*280
June	250	=B6+C5	2886	=D6+E5	=D6+F5-B6	=F6*15	=D6*140	=D6*4*4	=SUM(G6:I6)	=B6*280
July	225	=B7+C6	2886	=D7+E6	=D7+F6-B7	=F7*15	=D7*140	=D7*4*4	=SUM(G7:I7)	=B7*280
August	350	=B8+C7	2886	=D8+E7	=D8+F7-B8	=F8*15	=D8*140	=D8*4*4	=SUM(G8:I8)	=B8*280
September	650	=B9+C8	2886	=D9+E8	=D9+F8-B9	=F9*15	=D9*140	=D9*4*4	=SUM(G9:I9)	=B9*280
October	2950	=B10+C9	2886	=D10+E9	=D10+F9-B10	=F10*15	=D10*140	=D10*4*4	=SUM(G10:I10)	=B10*280
November	6200	=B11+C10	2886	=D11+E10	=D11+F10-B11	=F11*15	=D11*140	=D11*4*4	=SUM(G11:I11)	=B11*280
December	6700	=B12+C11	2886	=D12+E11	=D12+F11-B12	=F12*15	=D12*140	=D12*4*4	=SUM(G12:I12)	=B12*280
January	7300	=B13+C12	2886	=D13+E12	=D13+F12-B13	=F13*15	=D13*140	=D13*4*4	=SUM(G13:I13)	=B13*280
February	4350	=B14+C13	2886	=D14+E13	=D14+F13-B14	=F14*15	=D14*140	=D14*4*4	=SUM(G14:I14)	=B14*280
March	4600	=B15+C14	2886	=D15+E14	=D15+F14-B15	=F15*15	=D15*140	=D15*4*4	=SUM(G15:I15)	=B15*280
									=SUM(J4:J15)	=SUM(K4:K15)
	Beginning inventory			750			Profit	Profit after Discount(10%)
	Ending inventory			400			=K16-J16	=0.9*H20
	Total Demand			=SUM(B4:B15)
	Production Level			=(E21+E20-E19)/12

Please comment if having any doubt