Question

LP Modeling. Model the following problem. Please add screenshot of how you solved it using solver in excel (with formulas displaying as text).

A 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

Answer 1

Below is the screenshot of data tabl

Below is the screenshot of formula applied -

Below is the screenshot of solver -

Below will be the desired result -