Question

STAR Co. provides paper to smaller companies whose volumes are not large enough to warrant dealing directly with the paper mill. STAR receives 100-feet-wide paper rolls from the mill and cuts the rolls into smaller rolls of widths 12, 15, and 30 feet. The demands for these widths vary from week to week. The following cutting patterns have been established:

	Number of:
Pattern	12ft.	15ft.	30ft.	Trim Loss
1	0	6	0	10 ft.
2	0	0	3	10 ft.
3	8	0	0	4 ft.
4	2	1	2	1 ft.
5	7	1	0	1 ft.

Trim loss is the leftover paper from a pattern (e.g., for pattern 4, 2(12) + 1(15) + 2(30) = 99 feet used resulting in 100-99 = 1 foot of trim loss). Orders in hand for the coming week are 5,670 12-foot rolls, 1,680 15-foot rolls, and 3,350 30-foot rolls. Any of the three types of rolls produced in excess of the orders in hand will be sold on the open market at the selling price. No inventory is held.

Optimal Solution:

(a)	Formulate an integer programming model that will determine how many 100-foot rolls to cut into each of the five patterns in order to minimize trim loss. If your answer is zero enter “0” and if the constant is "1" it must be entered in the box.
	Min x1 + x2 + x3 + x4 + x5 s.t. x1 + x2 + x3 + x4 + x5 - Select your answer -≤≥=Item 11 12-foot rolls x1 + x2 + x3 + x4 + x5 - Select your answer -≤≥=Item 18 15-foot rolls x1 + x2 + x3 + x4 + x5 - Select your answer -≤≥=Item 25 30-foot rolls x1, x2, x3, x4, x5 are integers
(b)	Solve the model formulated in part a. What is the minimal amount of trim loss?
	Trim Loss:  feet
	How many of each pattern should be used and how many of each type of roll will be sold on the open market? If your answer is zero enter “0”.
	Pattern Number Rolls Used 1 2 3 4 5

Please show how to solve using excel if possible, thanks.

Answer 1

Answer:- Date: 18/03/2019 Let xi, x,x, and x, be the decision variable or rolls used with pattern of 1, 2, 3, 4, and 5 respectively. The objective is to minimize the trim loss thus, objective function is written as follow: Minimize Z-10x + 10x2 + 4x, + x4 + xs Write constraints as follow: 0x++8,+2*,+7',25670 6x, +0x2 +0x, +x, +x; 21680 0x +3 0x +2x, +0x, 23350 Enter the above data for demand of widths varying each week in excel as shown below:

Enter the above data for demand of widths varying each week in excel as shown below: 1 Pattern Decision variables 12 15ft 30ft Trim Loss 10 10 3,670 1,680 3,350

Use following formula to find decision variables and objective function: 12ft 15ft Trim Loss 10 10 1 Patten Decision variables 30ft SUMPRODUCT(B2:B6,C2:C6) SUMPRODUCT(B2:B6,D2:D6) -SUMPRODUCT(B2:B6.E2:E6) 5670 1680 3350 10ZSUMPRODUCT(B2:B6,F2:F6) Now select solver from data option in excel. Click on solver and add constraints, decision variable and objective function as shown below:

The result is shown below: Pattern 12ft 15ft 30ft Trim Loss 10 10 3 1,675 332 5 5674 2007 3350 5,670 1,680 3,350 10 2,007 Thus, Pattern Number of rolls used 1675 332 Trim loss is 2007 ft.