Question

A manufacturing firm is determining the production and inventory plans for the next three months to maximize profits. The production facility can produce at most 6500 units in one month. The cost of producing one unit in the next three months are respectively 52, 57, and 80 dollars. The cost of carrying one unit of inventory in the next three months are respectively are 3, 5, and 8 dollars. The selling price for the products in the next three months are 80, 72, and 64. Assume that the firm has a starting inventory of 431 units. The demand for the next three months are respectively 5000, 6000 and 7300 units. Build a mathematical formulation that will maximize profit. HINT: Note that this question is different than the previous one. Both the objective function and some of the constraints will need to be modified.

a. Define the decision variables using complete sentences

b. Write down the objective function in words

c. Write down the constraints using complete sentences

d. Express the objective function mathematically using the decision variables defined in part a

e. Express the constraints mathematically using the decision variables defined in part a; see slides for an example.

f. Input the mathematical formulation into an Excel worksheet in an organized fashion. Once done organizing the worksheet then copy paste the table from Excel into Word showing the setup of your worksheet. DO NOT turn in the EXCEL worksheet. All homework answers should be in one file formatted as pdf.

g. What is the optimal solution? (Use solver to determine the solution) h. What is the optimal objective function value? (use solver to determine the optimal objective function value)

Answer 1

a) Decision variables are:

P1, P2, P3 = Quantity to be produced in month 1, 2 and 3 resp.

S1, S2, S3 = Quantity to be sold in month 1, 2 and 3 resp.

V1, V2, V3 = Quantity carried in inventory in month 1, 2 and 3 resp.

b) Objective function: Maximize total profit, which is the obtained by subtracting the cost of production and cost of carrying inventory from the sales revenue.

c) Constraints:

1. Production in each month is limited to 6500 units

2. Sales in each month is limited to demand, which is 5000, 6000 and 7300 in respective months

3. Sum of opening inventory and production is equal to sales and closing inventory of each month.

4. Starting inventory is 431 units

d) Objective function in mathematical form is following:

Max Z = 80S1+72S2+64S3-52P1-57P2-80P3-3V1-5V2-8V3

e) Constraints in mathematical form are following:

P1 <= 6500

P2 <= 6500

P3 <= 6500

P1-V1-S1 = -431

P2-V2+V1-S2 = 0

P3-V3+V2-S3 = 0

S1 <= 5000

S2 <= 6000

S3 <= 7300

Pi, Vi, Si >= 0

f) Excel model is following:

EXCEL FORMULAS:

	P1	P2	P3	V1	V2	V3	S1	S2	S3
Max:	-52	-57	-80	-3	-5	-8	80	72	64	=SUMPRODUCT(B3:J3,$B$16:$J$16)
	1			-1			-1			=SUMPRODUCT(B5:J5,$B$16:$J$16)	=	-431
		1		1	-1			-1		=SUMPRODUCT(B6:J6,$B$16:$J$16)	=	0
			1		1	-1			-1	=SUMPRODUCT(B7:J7,$B$16:$J$16)	=	0
	1									=SUMPRODUCT(B8:J8,$B$16:$J$16)	<=	6500
		1								=SUMPRODUCT(B9:J9,$B$16:$J$16)	<=	6500
			1							=SUMPRODUCT(B10:J10,$B$16:$J$16)	<=	6500
							1			=SUMPRODUCT(B11:J11,$B$16:$J$16)	<=	5000
								1		=SUMPRODUCT(B12:J12,$B$16:$J$16)	<=	6000
									1	=SUMPRODUCT(B13:J13,$B$16:$J$16)	<=	7300
Result:	6500	6500	0	1931	2431	0	5000	6000	2431

g) Optimal Solution is following:

Production in month 1 = 6500

Production in month 2 = 6500

Production in month 3 = 0

Quantity sold in month 1 = 5000

Quantity sold in month 2 = 6000

Quantity sold in month 3 = 2431

Ending Inventory of month 1 = 1931

Ending Inventory of month 2 = 2431

Ending Inventory of month 3 = 0

Total profit (objective function value) = $ 261,136