Question

Company AAA produces only one product which other manufacturers purchase as a component for their final products. The operations manager wants to plan the production and inventory quantities of the product for the first six months of the next year. The monthly demand is forecasted as follows.

January	February	March	April	May	June
1200	1400	1800	2400	2600	2200

The company has three production options: regular production, overtime production, and subcontracting. Production cost per unit during regular time is $80 per unit. The overtime cost is 50% more than the regular time cost. A unit of product procured from a subcontractor costs $130. The cost to hold one unit of the product from one month to the next is $20.

The regular production capacity is 1,800 units. The minimum production using regular time per month is 1,400 units. Any production beyond 1,800 units in a month is considered as overtime, which cannot be more than 400 units.

The inventory level at the beginning of next year is planned to be 400 units. The minimum inventory at the end of each month is 200 units and the maximum inventory cannot be more than 600 units at the end of each month. The inventory at the end of June must be at least 300 units. a) Formulate the problem as a mathematical (algebraic) model to find the optimal production plan that minimizes the total cost.

1) Formulate the problem as a linear program on a spreadsheet to find the optimal production plan that minimizes the total production and inventory cost. Use proper colors and range names

Answer 1

(a)

Algebraic Model:

Let,

R_j = Regular production in month-j
O_j = Overtime production in month-j
S_j = Subcontracted units in month-j
E_j = Ending inventory of month-j,

for j=1,2,...,6

Minimize Z = total cost = 80*Σ_j=1(1)6 R_j + 120*Σ_j=1(1)6 O_j + 130*Σ_j=1(1)6 S_j + 20*Σ_j=1(1)6 E_j

Subject to,

R₁ + O₁ + S₁ + 400 - E₁ = 1200
R₂ + O₂ + S₂ + E₁ - E₂ = 1400
R₃ + O₃ + S₃ + E₂ - E₃ = 1800
R₄ + O₄ + S₄ + E₃ - E₄ = 2400
R₅ + O₅ + S₅ + E₄ - E₅ = 2600
R₆ + O₆ + S₆ + E₅ - E₆ = 2200

R_j >= 1400 for j=1,2,...,6
R_j <= 1800 for j=1,2,...,6

O_j <= 400 for j=1,2,...,6

E_j >= 200 for j=1,2,...,5
E₆ >= 300
E_j <= 600 for j=1,2,...,6

R_j, O_j, S_j, E_j >= 0 for j=1,2,...,5

(b)

Spreadsheet model

Solver inputs:

Solution:

Month	Demand	Regular production	Overtime production	Subcontracted units	Ending Inventory	Total output	Output + B.I - E.I
Dec					400
Jan	1200	1400	0	0	600	1400	1200
Feb	1400	1400	0	0	600	1400	1400
Mar	1800	1800	0	0	600	1800	1800
Apr	2400	1800	200	0	200	2000	2400
May	2600	1800	400	400	200	2600	2600
Jun	2200	1800	400	100	300	2300	2200
Totals	11600	10000	1000	500	2500
Marginal costs		$80	$120	$130	$20	Total cost
Cost		$800,000	$120,000	$65,000	$50,000	$1,035,000