Question

The east division of a company produces only one type of product. The vice president for the division wants to plan the production and inventory quantities of the product for October through March. The forecasted monthly demand is as follows.

October	November	December	January	February	March
6,000	7,000	9,000	12,000	10,000	11,0000

Production cost per unit during regular time is $15 per unit. The overtime cost is 50% more than the regular time cost. The cost to hold one unit of the product from one month to the next is $1 (compute the inventory cost by the ending inventory level.)

To keep the facility running and keep the core workforce stable, the minimum production per month is 3,000 units. The regular production capacity is 9,000 units. Any production beyond 9,000 units in a month is considered as overtime, which cannot be more than 2,000 units.

The inventory level at the beginning of October is planned to be 2,000 units. The minimum inventory at the end of each month is 1,000 units and the maximum inventory cannot be more than 3,000 units at the end of each month.

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.
2. Create a table with the regular production, overtime production and ending inventory of each month.
3. What is the total cost? What is the total overtime cost? What is the total inventory cost?
4. Formulate the problem as a mathematical (algebraic) model to find the optimal production plan that minimizes the total production and inventory cost.

Answer 1

Linear program as a spreadsheet model is following:

Ranges are named as below:

Range	Name
B3:B8	Demand
C3:C8	regular
D3:D8	overtime
E3:E8	Inventory
C3:D8	Decision_variables
B12	Total_cost

On the Solver parameter window, click on Solve button to generate the solution:

------------------------------------------------------------------------------------

Table of regular, overtime production and ending inventory is following:

Month	Forecasted Demand	Regular production	Overtime production
Initial
October	6000	5000	0
November	7000	9000	0
December	9000	9000	0
January	12000	9000	1000
February	10000	9000	1000
March	11000	9000	2000

------------------------------------------------------------------------------------

Total cost = $ 850,000

Total overtime cost = 4000*22.5 = $ 90,000   [unit cost for overtime production = 15*(1+50%) = 22.5 ]

Total inventory cost = 10000*1 = $ 10,000

------------------------------------------------------------------------------------

Mathematical model is following:

Let Ri be the regular production in month i,

Oi be the overtime production in month i

Vi be the ending inventory of month i

Minimize 15*(R1+R2+R3+R4+R5+R6)+22.5*(O1+O2+O3+O4+O5+O6)+1*(V1+V2+V3+V4+V5+V6)

s.t.

R1+O1-V1 = 6000-2000

R2+O2-V2+V1 = 7000

R3+O3-V3+V2 = 9000

R4+O4-V4+V3 = 12000

R5+O5-V5+V4 = 10000

R6+O6-V6+V5 = 11000

Ri <= 9000

Oi <= 2000

Vi <= 3000

Vi >= 1000

Ri, Oi, Vi >= 0