Question

A Kensington Company is planning production for the next 4 quarters. They want to minimize the cost of production. The production cost is stable but demand and production capacity vary from quarter to quarter. The maximum amount of inventory which can be held is 12,000 units and management wants to keep at least 3,000 units on hand. Quarterly inventory holding cost is 3% of the cost of production. The company estimates the number of units carried in inventory each month by averaging the beginning and ending inventory for each month. There are currently 5,000 units in inventory. The company wants to produce at no less than one half of its maximum capacity in any quarter.

	Quarter
	1	2	3	4
Unit Production Cost	$ 300	$ 300	$ 300	$ 300
Units Demanded	2,000	9,000	12,000	11,000
Maximum Production	8,000	7,000	8,000	9,000

Let	Pi = number of units produced in quarter i, i = 1, ..., 4
	Bi = beginning inventory for quarter i
MIN:	300 P1 + 300 P2 + 300 P3 + 300 P4 +
	9(B1 + B2)/2 + 9(B2 + B3)/2 + 9(B3 + B4)/2 + 9(B4 + B5)/2
Subject to:	4000 ≤ P1 ≤ 8000
	3500 ≤ P2 ≤ 7000
	4000 ≤ P3 ≤ 8000
	4500 ≤ P4 ≤ 9000
	3000 ≤ B1 + P1 − 2000 ≤ 12000
	3000 ≤ B2 + P2 − 9000 ≤ 12000
	3000 ≤ B3 + P3 − 12000 ≤ 12000
	3000 ≤ B4 + P4 − 11000 ≤ 12000
	B2 = B1 + P1 − 2000
	B3 = B2 + P2 − 9000
	B4 = B3 + P3 − 12000
	B5 = B4 + P4 − 11000
	Pi, Bi ≥ 0

	A	B	C	D	E	F
1			Quarter
2			1	2	3	4
3	Beginning Inventory		5,000	11,000	9,000	5,000
4	Units Produced		8,000	7,000	8,000	9,000
5	Units Demanded		2,000	9,000	12,000	11,000
6	Ending Inventory		11,000	9,000	5,000	3,000
7
8	Minimum Production		4,000	3,500	4,000	4,500
9	Maximum Production		8,000	7,000	8,000	9,000
10
11	Minimum Inventory		3,000	3,000	3,000	3,000
12	Maximum Inventory		12,000	12,000	12,000	12,000
13
14	Unit Production Cost		$300	$300	$300	$300
15	Unit Carrying Cost	3.0%	$9.00	$9.00	$9.00	$9.00
16
17	Quarterly Production Cost		$2,400,000	$2,100,000	$2,400,000	$2,700,000
18	Quarterly Carrying Cost		$72,000	$90,000	$63,000	$36,000
19
20					Total Cost	$9,861,000

a) What formula should be entered in cell C18 in the accompanying Excel spreadsheet to compute the quarterly carrying costs?  (Click to select)  =C15*(C3+C6)  =C15*(C3+C6)/2  =C15*C3/2  =C15*C3+C6

b) What formula should be entered in cell C6 in the accompanying Excel spreadsheet to compute ending inventory?  (Click to select)  =C3+C4-C5  =C3-C4+C5  =C3-(C4-C5)  =C5-C4-C3

c) What formula could be entered in cell F20 in the accompanying Excel spreadsheet to compute the Total Cost for all four quarters?